Working with Remote Data Using SQL Pass-Through
After you retrieve a result set using SQL pass-through, you can view and control the properties of your result set cursor using the Visual FoxPro functions CURSORGETPROP( ) and CURSORSETPROP( ). These are the same functions you use to set properties on an active view cursor.
Note Cursors aren't objects and aren't tied to the object model. However, you can view their properties, or attributes, with CURSORGETPROP( ) and set their properties with CURSORSETPROP( ).
Setting Cursor Properties for Remote Data
The following table lists the Visual FoxPro cursor properties that support working with views and connected result sets, grouped according to task categories.
Visual FoxPro Cursor Properties
| Task | Property | Purpose |
|---|---|---|
| View cursor definition | SQL | Contains the SQL statement from which the cursor was created. |
| Control interactions between Visual FoxPro and ODBC | ConnectHandle | Handle to remote connection that's used by cursor. |
| ConnectName | Name of connection used by the cursor. | |
| Prepare | Specifies whether the query for the view is prepared before it's executed. | |
| FetchAsNeeded | Specifies whether rows are fetched automatically during the idle loop or only on an as-needed basis. | |
| CompareMemo | Specifies whether Memo and General fields participate in the WHERE clause of an UPDATE statement, regardless of the setting of the UpdateType property | |
| FetchMemo | Specifies whether Memo and General fields are fetched automatically with result sets, or fetched later, on demand, as the Memo or General field is opened. | |
| UseMemoSize | Specifies the minimum column size (1 to 255) in result sets for which columns are returned in Memo fields. | |
| FetchSize | Specifies the number of rows that are fetched at one time from the remote result set. | |
| MaxRecords | Specifies the maximum number of rows fetched when result sets are returned. | |
| Update data | SendUpdates* | Specifies whether updates to the cursor are sent to the tables on which the cursor is based. |
| BatchUpdateCount | Specifies the number of update statements sent to the back end for buffered tables. | |
| Tables* | Comma-delimited list of table names on the data source; used to define scope for UpdateNameList and UpdatableFieldsList properties. | |
| KeyFieldList* | Comma-delimited list of Visual FoxPro fields that represent the primary key of the result set used for updates. | |
| UpdateNameList* | Comma-delimited list pairing Visual FoxPro fields in the cursor with the table and column names of fields to which you want to send updates. | |
| UpdatableFieldList* | Comma-delimited list of the Visual FoxPro fields for which updates are sent. | |
| Buffering | Specifies the type of buffering being performed on the cursor. | |
| UpdateType | Specifies whether updates should occur using UPDATE, or DELETE and then INSERT commands. | |
| WhereType | Specifies what should be included in the WHERE clause for updates to table data. |
* Properties that must be set before you can update data.
You use these properties to control the way your application interacts with remote data, such as establishing the number of rows retrieved during progressive fetching, and controlling buffering and updates to remote data.
Using the Remote Data Tab in the Options Dialog Box
Some cursor properties inherit their initial values from the environment; other properties only become available at the cursor level. Some properties are available to cursors representing remote views and ODBC or SQL pass-through connected tables.
You can control some cursor and connection property settings through the Remote Data tab of the Options dialog box. When you display the Remote Data tab, the values in the dialog box represent the cursor settings for the current session and the Visual FoxPro global default settings for the connection. When you change values in the Remote Data tab and choose OK, the new values are saved to the cursor's current session and the connection's global default settings. If you choose Set As Default, the values are written to the configurable system settings on your machine. The following diagram illustrates these interactions.
View and set global and session settings with the Options dialog box
.gif)
Setting Properties with SQL Pass-Through
When you create a cursor, the cursor inherits property settings, such as UpdateType and UseMemoSize, from the environment cursor, or cursor 0 of the current session. You can change these default property settings by using the CURSORSETPROP( ) function with 0 as the cursor number.
After you create a view cursor with SQL pass-through, you can change the active cursor's property settings by using the CURSORSETPROP( ) function for the view cursor. Changes you make with CURSORSETPROP( ) are temporary: the temporary settings for the active view disappear when you close the view, and the temporary settings for cursor 0 go away when you close the Visual FoxPro session.
Connections inherit properties in a similar fashion. Default properties for connection 0 are inherited when you create and store a named connection in a database. You can change these default property settings for connection 0 with the SQLSETPROP( ) function. After the connection has been created and is stored in a database, you can change connection properties with the DBSETPROP( ) function. When you use a connection, the property settings stored for the connection in the database are inherited by the active connection. You can change these properties on the active connection using the SQLSETPROP( ) function for the connection handle.
Both SQL pass-through view cursors and named connections can use a named ODBC data source. If you use an ODBC data source in a SQL pass-through view cursor, the connection inherits properties from the session defaults.
The following diagram illustrates property inheritance for cursors and connections created with SQL pass-through. The gray lines represent the flow of property inheritance; the black lines represent Visual FoxPro commands.
SQL pass-through (SPT) connection and cursor property inheritance
.gif)
See Also
Controlling Transactions Manually | Updating Remote Data with SQL Pass-Through | Implementing a Client/Server Application | Designing Client/Server Applications | Upsizing Visual FoxPro Databases | Creating Views