How to Create Updatable Views by Using SQL Passthrough

Article translations Article translations
Article ID: 138094 - View products that this article applies to.
This article was previously published under Q138094
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

SUMMARY

You can create an updateable 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-updateable snapshot of remote data, which is stored in an active view cursor. You can make the cursor updateable by setting properties with the CURSORSETPROP() function. An updateable remote view, in contrast, usually does not require that you set properties before you can update remote data.

To update remote data, use the CURSORSETPROP() function to set the following 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 the Visual FoxPro Professional Features Guide outlines the differences.

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

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;
       authors.phone, address authors.address,;
       city authors.city, state authors.state,;
       zip authors.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 on the remote server. NOTE: If you are unsure of how to set the above properties you can create a remote view. Please refer to the REFERENCES section for information on creating a remote view.

When you run the view, use the CURSORGETPROP() function in the Debug Window to determine what the five properties are set to. For example, put CURSORGETPROP("UpdateNameList") in the Debug window to see how the property is set in the CURSORSETPROP() function.

REFERENCES

For more information about creating an updateable remote view, please see the following article in the Microsoft Knowledge Base:
130413 How to Create an Updatable Cursor (View) to Update Table

Properties

Article ID: 138094 - Last Review: February 24, 2014 - Revision: 2.2
APPLIES TO
  • Microsoft Visual FoxPro 3.0 Standard Edition
  • Microsoft Visual FoxPro 6.0 Professional Edition
  • Microsoft Visual FoxPro 3.0b for Macintosh
Keywords: 
kbnosurvey kbarchive KB138094

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com