You are currently offline, waiting for your internet to reconnect

How to Create Updatable Views by Using SQL Passthrough

This article was previously published under Q138094
This article has been archived. It is offered "as is" and will no longer be updated.
You can create an updateable view cursor using SQL Passthrough by settingproperties with the CURSORSETPROP() function.
Remote views provide the most common and easiest way to gain access to andupdate remote data. You can also use SQL passthrough technology to send SQLstatements directly to a server. By default, a SQL passthrough query alwaysreturns a non-updateable snapshot of remote data, which is stored in anactive view cursor. You can make the cursor updateable by settingproperties with the CURSORSETPROP() function. An updateable remote view, incontrast, usually does not require that you set properties before you canupdate remote data.

To update remote data, use the CURSORSETPROP() function to set thefollowing five properties:

  • Tables
  • KeyFieldList
  • UpdateNameList
  • UpdatableFieldList
  • 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 theVisual FoxPro Professional Features Guide outlines the differences.

The following program is an example of how to create an updateable viewCursor.

Sample Code

************************** Beginning of code ******************   ** 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", "authors")     ** The next property must include every remote field matched with the     ** view cursor field.     =CURSORSETPROP("UpdateNameList", "au_id authors.au_id, au_lname;       authors.au_lname, au_fname authors.au_fname, phone;, address authors.address,;       city, state authors.state,;       zip, contract 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 you to send updates.     =CURSORSETPROP("SendUpdates", .T.)     BROWSE     USE     =SQLDISCONNECT(handle)   ENDIF   ************************* End of code ******************************				
Any changes made to the data in the Browse window will update the table onthe remote server.NOTE: If you are unsure of how to set the above properties you can create aremote view. Please refer to the REFERENCES section for information oncreating a remote view.

When you run the view, use the CURSORGETPROP() function in the Debug Windowto determine what the five properties are set to. For example, putCURSORGETPROP("UpdateNameList") in the Debug window to see how the propertyis set in the CURSORSETPROP() function.
For more information about creating an updateable remote view, please seethe following article in the Microsoft Knowledge Base:
130413 How to Create an Updatable Cursor (View) to Update Table
VFoxMac VFoxWin pass-through

Article ID: 138094 - Last Review: 02/24/2014 08:22:42 - Revision: 2.2

Microsoft Visual FoxPro 3.0 Standard Edition, Microsoft Visual FoxPro 6.0 Professional Edition, Microsoft Visual FoxPro 3.0b for Macintosh

  • kbnosurvey kbarchive KB138094