SQL ANY and ERROR 1814 2

[sashaDG]

Good afternoon. There is a project on the local network, I made an Exe file, in which there is a SQL query with ANY. I don't include any libraries. When I run it on my PC, everything works. When I run on others error SQL: Queries of this type are not supported (Error 1814).
I read that the EXE file does not require foxpro, which is logical. I have suspicions that all the same, the EXE file is connected to foxpro (there is no error on 1 PC, there is an error on 3 others)have old version

If connects, and how to see, thanks!

 

[Chris Miller]

Regarding Foxpro you need the ffoxpro runtimes.

But our error is about SQL queries. Well, look into ODBC drivers available, the 1 PC must have a wrong version.

Chriss

 

[GriffMG]

I would strongly recommend you try the OLEDB driver for your connection - I think you are probably using the ODBC one, which is not
multithreaded and prone to errors...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Mike Lewis]

Are you running this query within VFP? In other words, do you actually execute a SELECT statement in your VFP program?

Or are you sending the SELECT statement to a back-end server, via SQLEXEC().

If the former, the error message is simply telling you that the ANY operator is not supported by VFP. You will have to find another way to write your query.

I'm not sure how run-time libraries come into the picture. If you didn't have the correct run-time libraries, you would not have been able to run the EXE, and you would never have reached the point where it executes the SQL.

If you are sending the SELECT to a back end, then the problem presumably lies in the language supported by the back-end server (for example, T-SQL if the back-end is Microsoft SQL Server), or possibly the ODBC driver. But that doesn't make much sense, given that Error 1814 is a native FoxPro error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

the error message is simply telling you that the ANY operator is not supported by VFP

Sorry, I might be wrong about that. I think ANY (and also ALL) is supported, although as far as I can see it is not documented in the Help. (Someone correct me if I am wrong.) But it could be that there is some error in your syntax.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[sashaDG]

I don't have a server side. Do I still need to watch ODBC drivers?

 

[Mike Lewis]

I don't have a server side

Just to be completely clear. Do you mean that you are running a native SELECT command directly in VFP, as opposed to using SQLEXEC()?

If so, then forget about ODBC drivers.

In which case, given that it is working in your development environment but not in your run time, that points to your distributing older versions of the runtime library to the users of your EXE. The ALL, ANY and SOME clauses were introduced in VFP 9.0 (I found that in the Help, under What's New in Visual Foxpro / SQL Language Improvements). So you are presumably using VFP 9.0 as your development environment, but not 9.0 run times with your EXE.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Sorry, I was thinking along the lines of a remote database called SQLAny, perhaps short for SQL Anywhere. I didn't realize this VFP error, as I never came across it, if you have an error in your query it's typically a wrong field name or a syntax error of any kind that leads to error 10.

Well, ANY is really not documented, but I got a simple ANY query to run, your query syntax seems to be special in that VFP recognizes it but still can't execute it with the capabilities of the VFP SQL engine.

Chriss

 

[sashaDG]

I don't use SQLEXEC().

In which case, given that it is working in your development environment but not in your run time, that points to your distributing older versions of the runtime library to the users of your EXE. The ALL, ANY and SOME clauses were introduced in VFP 9.0 (I found that in the Help, under What's New in Visual Foxpro / SQL Language Improvements). So you are presumably using VFP 9.0 as your development environment, but not 9.0 run times with your EXE.

I also read about adding ANY to one of the foxpro versions. The most interesting thing is that this happens in the built application(exe file) in a non run-time. And then the question is: the relationship of an already built application with the computer on which it runs?

 

[Chris Miller]

The most interesting thing is that this happens in the built application(exe file) in a non run-time.

I don't know what you mean with that. an EXE only works "at runtime", there is no design time use of an EXE. And an EXE only works with runtime DLLs. vfp9r.dll is one of them. You surely have them installed on the PC without Foxpro or your EXE wouldn't start at all and error about not finding the runtime DLLs it needs.

If your query works while you program in the IDE but not in the EXE, you should make sure you're using the runtime that matches your VFP IDE version, which you can precisely find out by VERSION(4).
The runtime DLLs of several versions all have the same name, but their version attribute in file properties tells what exact version they are. Make sure you install the exact same version.

Besides that, a major downgrade of VFP capabilities occurs, if you SET ENGINEBEHAVIOR 70, but I doubt you could do that accidentally, you'd first need to know this setting even exists. It would activate the SQL engine and behavior of VFP7 within VFP9.

Chriss

 

[Mike Lewis]

is: the relationship of an already built application with the computer on which it runs?

If an EXE is being run on the same computer that on which VFP itself is installed, it is guaranteed to use the same runtime files as VFP, and so should work identically. That includes the case where both VFP and the EXE are installed on a server on a network (a file server, not a database server).

But if you install the EXE on a machine that does not have, and never has had, Foxpro installed, then you also need to install the VFP runtime libraries on the same machine. If you don't, then the EXE simply won't run. It's not the case that it will run but generate an error. Without the runtimes, you would never get as far as the code that causes the error.

With the above in mind, your most likely scenario is as follows: on the one machine where the EXE works OK, either you have FoxPro installed on that machine, or you have installed the correct runtime libraries, or both the EXE and the libraries are run from the same server. On the other three machines, the runtime is installed, but it is an older version - one that doesn't support the EXE clause (or, conceivably, you have an older version of FoxPro itself installed on that machine.)

So, first check that you are building the EXE from the same version of VFP that you were using to test the application. That's likely to be the case (there would be no reason to do otherwise). So, how are you distributing the EXE to the machines on which it doesn't work? Are you creating a SETUP.EXE? If so, what tool are you using to do that? (InstallShield? Inno Setup?) If not, are you manually copying the runtimes to the machines in question? And, if so, are you sure they are the correct versions of the runtimes?

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[sashaDG]

Hello everyone, with the help of the VERSION(), I found out that in the working VFP - 09.00.0000.5815 version 2007, in the non-working - 09.00.0000.2412 version 2005. By adding the DLL to the folder with the library file, the error disappeared !!! Thank you all for your help!

 

[Mike Lewis]

Good to see you have got it working, Sasha. Both versions are clearly VFP 9.0, with 5915 and 2412 being the respective build numbers, and the version that is working being a later build than the other one. That makes sense.

The AS clause is available in all 9.0 builds (it's not the sort of thing that they would introduce between builds of the same major version). But it looks like there was some sort of bug in the earlier build that caused it throw an error. And that that bug was fixed in the later build. At least, that's my guess.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

I just want to point that version 5815 is not the last version of VFP. You should add the hotfixes that will bring you to 7423, which is the final version.

Tamar

 

[Chris Miller]

Tamar is right, the best overview of the VFP versions I think has been made by Sergey Berezniker here:

The Bereznikers

It points to the KB articles (haven't checked if any of them got orphaned), and points out the hotfixes are cumulative, so you don't need to upgrade with hotfix 1 and 2 first, you can go straight to hotfix 3.
You can also see 2412 was the RTM version, or in other words VFP9 without any service pack or hotfix, the initially released VFP9.0 version. So the client that had this runtime version was installed early on - I think - and was never updated.

Some companies setup the VFP runtimes once for all their software, especially when much of their in-house software is VFP based. That saves setups and makes it easy to provide installations and updates with siple XCopy deployment. Maybe, your company does it that way, too. You still should upgrade, even if that means upgrading all clients with the newer VFP runtimes. It's just a one off job doing that.

As you experienced yourself, EXEs compiled with a different version than the runtime libraries version can cause errors. So if you do an all-company upgrade of runtimes all EXEs should also be provided for that runtime, so be recompiled in an IDE with that version. That might get complicated if you don't have sources when some EXE were bought from external vendors. It's possible to put in the runtimmes of each EXE into its own folder, provided the EXEs has their own folder. A VFP EXE does also look for the runtime libraries there, so that's a way out of needing different runtime versions for different EXEs and you could make it a solution for all EXEs instead of registering DLLs of VFP at all.

But some of the runtimes are not optional to register. For the HELP command to work, the vfp9hhelp.exe has to be registered. I don't remember whether the vfp9t.dll has to be registered for MTDLLs to work, but I think the help will tell about all that in detail. If

http://fox.wikis.com/

will work again which as you'll see has a very delicate prerequisite, you could find a topic about vfp9 runtimes that's very informative about what to do with each of the DLLs, also regarding the C++ runtime DLL and gdiplus.dll, which also once had a security hotfix.

So bare in mind, the runtimes have to be handled with care, as the working of EXEs and security depends on that, that's worth the hassle of bringing all or at least most EXEs to the latest hotfix version of the runtimes and let the latest and greatest VFP runtimes do their job. It's not possible to register more than one vfp9hhelp.exe, even when they differ by SP and hotfix KB number, they all have the same name, so that means consolidating all EXE versions to the latest version is the best solution.

A reference to this about one SP version and a botched version of the merge modules for InstallShield I remember was on Rick Schummer's blog. See

http://rickschummer.com/blog2/2009/04/vfp-9-sp2-hotfix-released/.

In the end, the latest hotfix has anything fixed that could be fixed, I can't think of a known bug not fixed, but it was said anything else had a workaround or could be fixed by the community as it were bugs in the components programmed in VFP themselves and open sourced as they always were in xsource.zip, which is freely available, if not already part of the VFP setup.

And several tools like GenDBC have a community-enhanced version, GenDBCX in that case, see

https://github.com/VFPX/GenDBC,

it provides both the Microsoft gendbc.prg and gendbcx.prg.
I think all of these things are pulled together in the VFPX Github repositories about VFP at

https://github.com/VFPX

plus many more things extending VFP to something that's worth calling it VFPX or 10.

Chriss

 

[sashaDG]

Thanks for the detailed answer Chris, for now I just added the DLL files (posted earlier). And I don’t know if I will update, although you are right, this is a one-time action.