Data Access Management Using CursorAdapters

  • Article

In Visual FoxPro, you can retrieve data from local and remote data sources that have the following data source types using cursor adapters:

  • Native

  • Open Database Connectivity (ODBC)

  • ActiveX Data Object (ADO)

  • Extensible Markup Language (XML)

The CursorAdapter class expands support for working with different data source types as native Visual FoxPro cursors. CursorAdapter objects provide the following capabilities:

  • Use different data sources dynamically.

  • Use either the data source of the CursorAdapter object or the data environment.

  • Share data sources within the limits of the data source technology.

  • Optionally define the structure of the cursor associated with a CursorAdapter object.

  • Control the loading of data from the data source into a Visual FoxPro cursor associated with a CursorAdapter object.

  • Render data from different data sources into Visual FoxPro cursors based on the data source type.

  • Control how data is added, updated, and deleted with CursorAdapter properties and methods.

  • Add CursorAdapter objects to containers other than the data environment, such as forms, form sets, and other containers.

  • Use the CursorAdapter class as a standalone class without an associated data environment.

With CursorAdapter objects, the data source is only a pipe to the translation layer, which renders data from the data source into a Visual FoxPro cursor.

Note

Visual FoxPro does not support using relations with the CursorAdapter objects. However, you can use relations with cursors associated with cursor adapters.

For more information about the CursorAdapter, DataEnvironment, and Cursor classes, see CursorAdapter Class, DataEnvironment Object, and Cursor Object.

Interaction with the TABLEUPDATE( ) and TABLEREVERT( ) Functions

The TABLEUPDATE( ) function recognizes and can work with CursorAdapter objects. TABLEUPDATE( ) delegates its operations to the cursor adapter associated with the cursor. TABLEREVERT( ) operates on CursorAdapter objects in the same way as other buffered cursors.

For more information about how CursorAdapter objects affect the way the TABLEUPDATE( ) and TABLEREVERT( ) functions behave, see TABLEUPDATE( ) Function and TABLEREVERT( ) Function.

Automatic Updating and CursorAdapters

Visual FoxPro automatically generates the SQL INSERT, UPDATE, and DELETE commands for local and remote views. When working with CursorAdapter objects, you can customize and control how Visual FoxPro generates these SQL INSERT, UPDATE, and DELETE commands.

When the CursorAdapter InsertCmd, UpdateCmd, and DeleteCmd properties are empty, Visual FoxPro generates the corresponding SQL commands automatically. You must determine whether automatic generation of these commands is appropriate for the data source you are using. To generate the SQL INSERT, UPDATE, and DELETE commands automatically, you must set the following CursorAdapter-specific properties:

The following general rules apply for the Tables and UpdateNameList properties:

  • Tables

    To enable automatic updating, you must provide a list of table names in the exact order you want them to appear in the SQL INSERT, UPDATE, and DELETE commands.

  • UpdateNameList

    You must specify a comma-delimited list consisting of pairs of local and full remote field names. Each pair of names consists of the local field name followed by the full remote field name. The full remote field name appears as <remote table name>.<remote field name>, where <remote table name> matches the name from the Tables property.

You must also set the appropriate key fields if you set the following CursorAdapter properties to True (.T.):

For more information about the SQL commands used for automatic updating, see INSERT - SQL Command, UPDATE - SQL Command, and DELETE - SQL Command.

Batch Updates

CursorAdapter objects use batch updating if the value of CursorAdapter BatchUpdateCount property is greater than 1, and one of the following conditions are true:

  • The CursorAdapter object is set up to use the same ODBC statement handle for all permitted operations, namely, INSERT, UDPATE, and DELETE, as defined by the AllowInsert, AllowUpdate, and AllowDelete properties.

  • The CursorAdapter object is set up to use the same ADODB Command object for all operations permitted.

  • The CursorAdapter object uses "XML" as the data source type for all permitted operations.

If batch updating is used, the following CursorAdapter events do not occur:

  • BeforeInsert

  • AfterInsert

  • BeforeUpdate

  • AfterUpdate

  • BeforeDelete

  • AfterDelete

If an update fails for a batch, Visual FoxPro attempts to send a separate update for each row in the batch; however, the listed events still do not occur.

For more information, see BatchUpdateCount Property.

Automatic Updating and ActiveX Data Objects (ADO)

When working with ADO, you can send updates using two different methods:

  • Use the CursorAdapter object to send updates with the ADO RecordSet object used by the CursorAdapter CursorFill method.

    When performing automatic updates using an ADO RecordSet, the following rules apply when setting the CursorAdapter InsertCmdDataSource, UpdateCmdDataSource, and DeleteCmdDataSource properties:

    • The ADO RecordSet must be read/write and have bookmarks available. Bookmarks are always available when using a client-side RecordSet object. Bookmarks might be available when using Keyset or Static server-side cursors when supported by the OLE DB Provider.

    • Updates are performed on a field-by-field basis. For every updated record, the CursorAdapter object locates the original record in the ADO RecordSet, changes the values for updatable fields, and calls the ADO RecordSet Update method. The CursorAdapter object does not call the ADO RecordSet UpdateBatch method; therefore, your application should explicitly call UpdateBatch when appropriate.

    When using this method, you should configure the CursorAdapter object as follows:

    THIS.DataSourceType="ADO"
THIS.UpdateCmdDataSourceType=""

    You can also use the DataEnvironment and CursorAdapter builders to build an updatable form or DataEnvironment and CursorAdapter class library and use the AutoUpdate tab in the CursorAdapter Builder to specify key fields, update fields, and other required information.

  • Use the CursorAdapter object to send updates directly to the database using a custom or automatically generated update command.

    When performing automatic updates directly, the CursorAdapter object needs an ADO Command object with its ActiveConnection property set to an open ADO Connection object.

    When using this method, you should configure the CursorAdapter object as follows:

    THIS.UpdateCmdDataSourceType="ADO"
THIS.UpdateCmdDataSource=NewADODBCommandObject

    Setting THIS.DataSourceType="ADO" does not apply when using this method.

Automatic Updating and XML

CursorAdapter objects do not automatically generate SQL INSERT, UPDATE, or DELETE commands as they do for other data sources when the data source is XML because of the numerous ways that exist for generating XML. However, you can use the CursorAdapter object to generate an XML UpdateGram and assign it to the CursorAdapter UpdateGram property.

Though the CursorAdapter object can generate the XML UpdateGram, you must implement the actual update operation using the appropriate protocols, such as SQL XML using HTTP, SQL XML OLE DB, or XML Web service to .NET.

The following rules apply when working with XML data sources and generating XML UpdateGrams:

  • You must specify command text for the CursorAdapter InsertCmd, UpdateCmd, and DeleteCmd properties that Visual FoxPro can execute for the appropriate insert, update, or delete operation. If using batch updating, that is CursorAdapter BatchUpdateCount property is greater than 1, UpdateCmd is executed only once per batch.

  • The following CursorAdapter properties must be set to "XML":

    If these properties are not set to "XML", Visual FoxPro executes the command text in the UpdateCmd, InsertCmd, or DeleteCmd properties but does not generate an XML UpdateGram.

  • To construct the XML UpdateGram correctly, CursorAdapter requires that the Tables, UpdatableFieldList, and UpdateNameList properties contain valid values. CursorAdapter can generate a multiple table UpdateGram by specifying the tables and fields in the Tables, UpdatableFieldList, and UpdateNameList properties as appropriate. Therefore, you can update a cursor that represents a multi-table join using the XMLUPDATEGRAM( ) function. For more information, see XMLUPDATEGRAM( ) Function.

  • CursorAdapter uses the WhereType property to generate the before section for the XML UpdateGram. Therefore, when you perform an update or delete operation, the KeyFieldList and UpdatableFieldList properties must contain the appropriate key fields. For more information, see WhereType Property.

  • If row buffering is enabled, or BatchUpdateCount is 1, Visual FoxPro creates an XML UpdateGram for each update, insert, or delete operation.

    If table buffering is enabled, and if using batch updating, that is, BatchUpdateCount is greater than 1, Visual FoxPro creates an XML UpdateGram for the entire batch of changes. In this mode, you must call the TABLEUPDATE( ) function as expected to initiate the update and generation of the XML UpdateGram.

See Also

Reference

CursorAdapter Class
CursorAdapter Object Properties, Methods, and Events

Other Resources

Importing and Exporting Data