Having successfully included a database and its tables into the compiled .exe of my application (see separate thread), I have now encountered a problem which appears to be a bug.

When I run an SQL query normally - e.g. SELECT COUNT (*) FROM table INTO ARRAY laTest - it works fine.

But when I run the same query using the EXECSCRIPT function - e.g. = EXECSCRIPT ("SELECT COUNT (*) FROM table INTO ARRAY laTest") - I get an error message of the nature "cannot find drive\directory\subdirectory\table.dbf".

In both instances the table is already open before the query is executed. But for some reason when the query is run through the EXECSCRIPT function it attempts to find the table in its original location (relative to the application) rather than using the open table.

Does anyone have any idea why this is, and is there a workaround (apart from not using the EXECSCRIPT function to execute SQL queries)

Re: Include tables bug?


May be EXECSCRIPT() is not able to see path for files already included in project exe file.

Re: Include tables bug?


No it is not a bug. It is the way how execscript() works. Instead of execscript() you could write your code to an external file, compile and run it (at runtime).

Re: Include tables bug?


Thanks CetinBasoz. Please could you elaborate - what it is about the way EXECSCRIPT() works that means that you cannot use it to run queries on included tables I have used EXECSCRIPT extensively and have never encountered any issues with it before.

Thanks for the suggestion of copying the codes to an external file (I didn't realise that you could compile at runtime). Unfortunately, I don't think that this will be fast enough for my purposes. What I am considering doing instead is copying each of the tables to a local cursor at startup and then using that for any subsequent queries.

Re: Include tables bug?


>>what it is about the way EXECSCRIPT() works that means that you cannot use it to run queries on included tableswhat it is about the way EXECSCRIPT() works that means that you cannot use it to run queries on included tables

It's a scope issue. EXECSCRIPT() creates a temporary program, compiles it and executes it in memory. It exists outside of your EXE environment and so cannot access things that are defined inside the EXE. This limitation does not apply to programs that are created on disk.

>> Unfortunately, I don't think that this will be fast enough for my purposes

Try it - you will be amazed! I do this all the time in Web applications - I have class definitions stored in memo fields that, at run time, are located, read into a memory variable, exported to a PRG file, compiled and then instantiated. This is almost instantaneous for VFP and the code looks like this (stripped of error checking etc)

IF SEEK( 'classname', 'classdef', 'classes' )

*** Read the memo field and instantiate the object ***

STRTOFILE( ALLTRIM( classdef.classcode ), 'temp.prg', 0 )

COMPILE temp.prg

oOperationObj = NEWOBJECT( 'classname', 'temp.prg' )

*** Call the Operational Code here ***


*** Clean up ***

CLEAR CLASS classname

DELETE FILE temp.prg


Two things..

First, you must compile the program, even though to refernce it you use the PRG file name.

Second if you do this, you must track the temp file name and you must use CLEAR CLASS to release the cached definition so that you can delete the temporary program file.

Why do I do this Well, the first use for this was in an application for a very large hospital that we cannot take it off line - it must run 24 hours a day, 7 days a week. So to make changes, or add new functionality, all we have to do is to add records to the metadata table and everything works continuously.

Re: Include tables bug?


>>Try it - you will be amazed!

Colour me amazed! I've tried it and there's no noticeable impact on performance (I always thought that anything that involved file operations had a considerable overhead over operations solely in memory). I've now used your method to knock up a 'RunScript' UDF as an alternative to the EXECSCRIPT() function, to be used for executing queries on included tables, and it works perfectly.

Thanks again!

Re: Include tables bug?


>> Colour me amazed!

Remember, FoxPro is, and always has been, F A S T in a way that .NET languages and their ilk can only dream about!

Re: Include tables bug?


Andy already provided details how execscript() use its own memory space.

You don't think it would be fast enoughSmile Actually it's way faster than ExecScript() approach. The reason is that with ExecScript() the content is never cached and a recompilation is done. Behind the scenes it looks like:

compile-execute (and I'm bypassing ExecScript() writes to disk or not aside)

Now consider this:

for ix=1 to 10



It means 10 compilations of the same routine. OTOH if you:

StrToFile(m.lcSomeCode, 'myOnTheFly.prg')

compile myOnTheFly.prg

for ix=1 to 10



Then it's a single disk write and a single compilation. Benchmark yourself a very simple code with ExecScript() vs external write,compile,run approach. You'd see which one is faster.

BTW the ability for compile/run at runtime was gifted since VFP6/SP3 and it's not only for prg files, you can compile other items like menu,class,form ... at run time.