HOWTO: Editing the SQL Statement of a Remote or Local View

• Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a
• Microsoft Visual FoxPro for Macintosh, version 3.0b

SUMMARY

Sometimes a programmer may want to create a remote or local view that the View Designer cannot produce. Unfortunately, there is no way to directly edit the SQL Statement generated by a remote or local view in the Database Designer. You can paste it into a .prg file and then edit the code. However, this will not save changes back to the database container. You can programmatically create a view using the CREATE SQL VIEW, but then you must set all the properties using the DBSetProp() function. An easier method is to design as much of the view as possible within the Database Designer and then run Gendbc.prg to obtain the code. You can then modify the SQL statement, add the necessary changes, and write the new view back to the database.

MORE INFORMATION

First, create as much of the view as possible with the View Designer. Then run Gendbc.prg to produce code that re-creates the database and its contents. Copy only the part of the Gendbc code that creates the local or remote view (the CREATE SQL VIEW command) and sets its properties (the DBSetProp functions) into another program. Now, modify the CREATE SQL VIEW command to reflect the necessary changes. Finally, with the original database open, run the second program file, which contains the new CREATE SQL VIEW syntax and DBSetProp functions, to re-create the view with the desired changes. This technique is useful with both local and remote views.

The example below uses a simple view created from the Customer table. It modifies the view to return only customers from the USA:

1. In the Command window or program file, type the following lines of code and run them:

         MD HOME()+"..\ztest"
         USE HOME()+"samples\data\customer.dbf"
         COPY TO HOME()+"..\ztest\customer.dbf"
         CD HOME()+"..\ztest"
         CLOSE DATABASE ALL
         CREATE DATABASE Ztest
         MODIFY DATABASE Ztest NOWAIT   && NOWAIT only if in a program
   
   

2. Add the Customer table located in the ztest folder to the database.

3. Right-click on the database container and click New Local View from the Shortcut menu. In Visual FoxPro for Macintosh, Control+Click instead of right-click.

4. From the New Local View dialog box, select the New View option, then add the Customer table to the view, and add all the fields to the view. Save the view as Ztest and run the view. Note that the view has records from all countries in the Browse window. Close the Browse window, the View Designer and the Database Designer window.

5. Type the following two lines of code in the Command window:

         CLOSE TABLES ALL
         SET DATABASE TO Ztest
         DO HOME()+"tools\gendbc\gendbc" WITH "Ztest"
   

6. Open the Ztest.prg file and locate the CREATE SQL VIEW command that creates the SQL view in the database container. Copy the CREATE SQL VIEW command and all the DBBSETPROP commands into a program file called Ztest2.prg. Change the following line from the following:

         CREATE SQL VIEW "ZTEST" ;
         AS SELECT * FROM ztest!customer
   

   to the following:

         CREATE SQL VIEW "ZTEST" ;
         AS SELECT * FROM ztest!customer WHERE country ="USA"
   

   This creates a view that lists only the customers whose country field contains "USA." Copy only the code that creates the SQL View into the Ztest2 program. If you copy all the code from Ztest.prg, it will re-create both the table and the SQL view contained within the database. However, all the data for the Customer table is lost.

7. With the Ztest database still open, run the Ztest2 program file. If SAFETY is set off, a message appears that asks if you want to overwrite the Ztest view. Answer "Yes" to this dialog box. This re-creates the SQL View with the changes and write it to the database container. Retain the Ztest2.prg program in case further changes are needed for the SQL View.

8. Modify the Ztest database and double-click the view. Notice that the view now shows only customers from the United States.