How to create a dynamic SELECT-SQL statement in an executable file in FoxPro

MORE INFORMATION

This code example below uses the Clients table found in the \Sample\Organize\dbfs folder of FoxPro 2.x. The code assumes the Clients table resides in the current folder. If you use this code with Visual FoxPro, then it is assumed that the Clients table from FoxPro 2.x was copied into the current folder.

The following program, which can be compiled into a stand-alone executable file, allows the user to dynamically change the SELECT-SQL statement as needed:

   USE CLIENTS.DBF
   GETEXPR to x
   GETEXPR to y
   SELECT * FROM clients WHERE &x HAVING &y INTO CURSOR Test
   BROWS
 
   PROCEDURE notcalled
   SELECT * FROM clients WHERE state="CA" HAVING city="Los Angeles"
				

When the .EXE file is run, two expression boxes appear in succession.

1. In the first, choose STATE from the Field Names box.
2. Type the following, and choose OK.

   ="TX"

3. In the next expression box, choose CITY from the Field Names box.
4. Type the following, and choose OK.

   ="Lubbock"

This query will produce one record.

NOTE: Because macro substitution is used, the stand-alone executable file may not have the proper code included to perform the required function so an error may occur. To avoid this problem, the notcalled procedure includes code for a SELECT-SQL statement. When the executable file is compiled, FoxPro will include the necessary source code to execute the macro substitution. The notcalled procedure is not executed by the program.

In Visual FoxPro 7.0 and later versions, you can use the following code example.

1. Create a project, add a main program file, and add this code:

   CLOSE ALL
   
   DELETE FILE temp.dbf
   CREATE TABLE temp (City c(20),state c(2))
   INSERT INTO temp (city,state) VALUES ("Charlotte","NC")
   INSERT INTO temp (city,state) VALUES ("Seattle","WA")
   INSERT INTO temp (city,state) VALUES ("Dallas","TX")
   INSERT INTO temp (city,state) VALUES ("Atlanta","GA")
   
   x=INPUTBOX("Enter city name" )
   y=INPUTBOX("Enter state")
   SELECT * FROM temp WHERE city=&x  having state=&y INTO CURSOR test
   
   BROWSE
   CLOSE ALL
   DELETE FILE temp.dbf

2. Save the project and build an executable.
3. Run the executable created in Step 2.
4. Type this in the InputBox entitled "Enter city name" and press the OK button:
   
   

    "Charlotte"

5. Type this in the InputBox entitled "Enter state" and press the OK button:"
   
   

    "NC"

6. One record appears for Charlotte, NC. Press the close box of the form to exit the executable.