Dynamically building an SQL statement

[martinmcc]

Hi,
I am writting a small program which I need to dynamically create an sql statement from the contents of a list box i.e.

list box contains 't', 'y', 'x'

sql statement -
select * from table where col1='t' or col1='y' or col1='x'

The problem I am having is how to create this staement and run it. Is there a function in FoxPro that will take a string argurment and run it as a command (like in javascript etc), or is there some other solution that would work.

BTW, it is a pretty large table and a select staement takes about 10 mins to run, so if anyone knows of a better (quicker) way, please let me know.

Cheers,
Martin McCann

 

[weedz]

lcSelect = "SELECT * FROM myTable WHERE myCondition INTO CURSOR MyCursor"

&lcSelect

HTH,

Weedz (Wietze Veld)
My private project:

http://www.crowncap.demon.nl\info\crwnbase

CrownBase source code can be downloaded !!

 

[itflash]

Functions in FP help file index
--------------------------------
&
Listbox -> See Also -> Example (bottom of screen)
Selected Property

Then build up your text string

 

[ThomC]

<<list box contains 't', 'y', 'x'

sql statement -
select * from table where col1='t' or col1='y' or col1='x'
>>

I'm not sure what you want. Your example implies

SELECT * ;
FROM MyTable ;
WHERE Col1 IN(&quot;T&quot;, &quot;Y&quot;, &quot;X&quot ;
INTO CURSOR MyResults

which is pretty comprehensive, but it looks like you want:

LOCAL lcListVal, lcSQL
m.lcListVal = ALLTRIM(THISFORM.lstPicker.Value)

m.lcSQL = &quot;SELECT * FROM MyTable WHERE Col1 = '&quot; + m.lcListVal + &quot;' INTO CURSOR MyResults&quot;

&lcSQL

Don't forget the single quotes around the listbox memvar.

Regards,
Thom C.

 

[Scott24x7]

Martinmcc,
I do this kind of thing all the time. Fox has something called Macro Substitution that makes this possible. I'll make an example below, with a couple of items.

Usually what I do, is set my &quot;Seed&quot; value to something that will be constant, generic, and &quot;Add-able on-able&quot;.

lcSelectStatement = &quot; * FROM MyTable INTO CURSOR&quot;
*
* then, I might add logect to see if I have anything to add
*
IF NOT EMPTY(lcCriteria1) OR NOT EMPTY(lcCriteria2)
lcSelectStatement = lcSelectStatement + &quot; WHERE &quot;
ENDIF
*
* then, tack on the criteria...
* in the example below, assume lcCriteria1 is &quot;Customer.CUID = lcCUID&quot;
*
IF NOT EMPTY(lcCriteria1)
lcSelectStatement = lcSelectStatement + lcCriteria1
ENDIF
*
* then, tack on some more, if it exists...
* assume lcCriteria2 is &quot; AND lcLname = 'Smith'&quot;
*
IF NOT EMPTY(lcCriteria2)
lcSelectStatement = lcSelectStatement + lcCriteria2
ENDIF
*
* Now this next bit is subtle, but significant.
* when macro-subbing on a Select, I still like to &quot;Show&quot;
* the main &quot;Command&quot; in code, so that it is easy to follow.
* So, instead of making the next statment a Macrosub
* holding the whole value, I prefer this syntax:

SELECT &lcSelectStatement

*
*
This way, you can *EASILY* see what is really taking place. You are using a SELECT, with the criteria substituted. When I see a Macro Sub like this as well, it lets me know that I have a dynamic criteria on that Select statement.

Play with this. There is virtually no bounds by which you can customize, modify it to your needs/liking.


Thanks,
-Scott

Please let me know if this has helped! s-)