How to Create Updatable Views by Using SQL Passthrough

• Microsoft Visual FoxPro for Windows, version 3.0
• Microsoft Visual FoxPro for Macintosh, version 3.0b

SUMMARY

You can create an updatable view cursor using SQL Passthrough by setting properties with the CURSORSETPROP() function.

MORE INFORMATION

Remote views provide the most common and easiest way to gain access to and update remote data. You can also use SQL passthrough technology to send SQL statements directly to a server. By default, a SQL passthrough query always returns a non-updatable snapshot of remote data, which is stored in an active view cursor. You can make the cursor updatable by setting properties with the CURSORSETPROP() function. An updatable remote view, in contrast, usually doesn't require that you set properties before you can update remote data.

To update remote data, use the CURSORSETPROP() function to set five properties: Tables, KeyFieldList, UpdateNameList, UpdatableFieldList, and SendUpdates. The update properties in a view definition (set by using DBSETPROP() function) and an active cursor vary slightly. The table on page 60 in the Visual FoxPro Professional Features Guide shows the differences.

The following program is an example of how to create an updatable view cursor:

   ** This example is using SQL Server as the back-end.
   ** Your back-end server may vary.
   Handle = SQLCONNECT("SQL421","sa","")
   IF handle<0
     WAIT WINDOW "connection not made"
     CANCEL
   ELSE
     =SQLEXEC(Handle, "select * from authors")
     =CURSORSETPROP("Tables", "dbo.authors")
     ** The next property must include every remote field matched with the
     ** view cursor field
     =CURSORSETPROP("UpdateNameList", "au_id dbo.authors.au_id, au_lname;
       dbo.authors.au_lname, au_fname dbo.authors.au_fname, phone;
       dbo.authors.phone, address dbo.authors.address,;
       city dbo.authors.city, state dbo.authors.state,;
       zip dbo.authors.zip, contract dbo.authors.contract")
     =CURSORSETPROP("KeyFieldList", "au_id")
     ** The next property specifies which fields can be updated.
     =CURSORSETPROP("UpdatableFieldList", "au_lname, au_fname, phone,;
       address, city, state, zip, contract")
     ** The next property enables the ability to send updates.
     =CURSORSETPROP("SendUpdates", .T.)

     BROWSE
     USE
     =SQLDISCONNECT(handle)
   ENDIF

NOTE: If you are unsure of how to set the above properties you can create a remote view. For more information about creating an updatable remote view, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q130413
   TITLE     : How to Create an Updatable Cursor (View) to Update Table