Q174807: HOWTO: Edit the SQL Statement of a Remote or Local View

Article: Q174807
Product(s): Microsoft FoxPro
Version(s): 
Operating System(s): 
Keyword(s): kbDatabase kbHWMAC kbSQL kbvfp300 kbvfp500 kbvfp600 kbSQLProg
Last Modified: 27-JUL-2001

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a, 6.0 
- 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"

        *-- In Visual FoxPro 6.0:
        *-- USE HOME(2)+"data\customer.dbf"
        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.

Additional query words: gendbc view

======================================================================
Keywords          : kbDatabase kbHWMAC kbSQL kbvfp300 kbvfp500 kbvfp600 kbSQLProg 
Technology        : kbHWMAC kbOSMAC kbVFPsearch kbAudDeveloper kbVFP300bMac kbVFP300 kbVFP300b kbVFP500 kbVFP600 kbVFP500a
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1986-2002.