Data Access Management Using CursorAdapters

Article
11/14/2006

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 CursorAdapterInsertCmd, 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 CursorAdapterBatchUpdateCount 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 CursorAdapterCursorFill method.

When performing automatic updates using an ADO RecordSet, the following rules apply when setting the CursorAdapterInsertCmdDataSource, 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 updateable 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 updateable 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 CursorAdapterUpdateGram 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 CursorAdapterInsertCmd, UpdateCmd, and DeleteCmd properties that Visual FoxPro can execute for the appropriate insert, update, or delete operation. If using batch updating, that is CursorAdapterBatchUpdateCount 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.

Partager via