Chapter 11
Working With Remote Data

VFP 9 adds a number of features to the CursorAdapter to make it work better with remote databases. In addition, enhancements to SQL Pass-Through, ADO, and the OLE DB provider give you improved control when accessing remote data.

Since the transition from FoxPro to Visual FoxPro, VFP has provided easy access to remote data using remote views and SQL Pass-Through. VFP 7 introduced the OLE DB provider to extend VFP’s capabilities as a remote data provider. VFP 8 introduced the CursorAdapter which gave us a unified, object-oriented means to access both local and remote data.

VFP 9 adds several new features to the CursorAdapter to improve remote data access, such as auto-refresh of identity and default values, timestamp support, conflict checking, and delayed memo fetching. The OLE DB provider can now return results from stored procedures, making it much more useful as a remote data provider. Finally, a number of minor SQL enhancements make it easier to handle connection management and delayed fetching of remote data.

SQL Pass-Through enhancements

VFP’s SQL Pass-Through functions have been enhanced to give new options when disconnecting from a remote database, and to provide more information on the results of remote operations.

Temporarily disconnecting from a database

One of the first rules you learn about accessing remote databases is to limit the number of connections. Each active connection to a back-end database consumes resources on the server, and the total number of concurrent connections may be limited by licensing. On the other hand, continually closing and reopening connections requires extra coding and takes time.

VFP 8 introduced two changes to help minimize the number of connections you need. SQLCONNECT() can use an existing shared connection, and you can open a remote view using an existing shared connection by specifying a statement handle in place of the cConnectionName parameter. In our applications, we normally open a single shared connection based on the user’s login information, and use that connection for the duration of the application.

However, there are situations where you may need to open additional connections for short periods of time. For example, you may need to retrieve data periodically from a different database, or execute an occasional long-running query in the background using a separate connection. In the past, you needed to reissue SQLCONNECT() or SQLSTRINGCONNECT() each time you wanted to reconnect to the remote data source, receiving a new statement handle on each reconnection.

The new SQLIDLEDISCONNECT() function temporarily disconnects from the
remote database, but preserves the VFP statement handle and original connection parameters. The syntax is:


If your application attempts to use the statement handle again, VFP automatically reconnects to the remote database using the original connection parameters. If the connection cannot be reestablished, error 1526 (“Connectivity error”) occurs. As a result, you can reuse the connection at any time without worrying about whether it is still active, and without reissuing SQLCONNECT() or SQLSTRINGCONNECT().

Note that the parameter passed to SQLIDLEDISCONNECT() is a statement handle. Beginning in VFP 8, SQL functions use statement handles rather than connection handles. A connection handle represents a unique connection to a database engine. If the connection is marked shareable, it can be used by multiple statement handles within an application. SQLIDLEDISCONNECT() disconnects the statement handle, but does not release the connection to the database server until all its statement handles have been released. After calling SQLIDLEDISCONNECT(), you can use SQLGETPROP() to determine whether the connection has been released. The ODBChstmt property is 0 if the statement handle has been disconnected; the ODBChdbc property is 0 if the connection to the back end has been released. (See “Determining what connections are open” later in this chapter for a way to determine which statement handles share a given connection.)

SQLIDLEDISCONNECT() returns 1 if successful, or -1 if it cannot disconnect.
You cannot disconnect a connection if it is busy executing a query or if it is in manual transaction mode.

Here’s an example that demonstrates how two shared connections become idle, and then automatically reconnect to the database. It displays the statement and connection handle for each connection at each stage. A third non-shareable connection is opened midway through that grabs the connection handle originally assigned to the first two connections. The idle connections then get a new connection handle when they reconnect. This demonstrates how, once all statement handles on a connection become idle, the physical connection to the database (represented by the connection handle) is dropped.





LOCAL lcDir, lcDbc, lcConnStr, lnConn1, lnConn2, lnConn3, ;

  lnHandles, laHandles[1], n


*-- Connect to Tastrade sample database using ODBC.

lcDir = HOME(2) + "tastrade\data\"

lcDbc = lcDir + "tastrade.dbc"

IF ! FILE(lcDbc)

  MESSAGEBOX("The VFP sample database Tastrade.dbc cannot be found in " ;

  + lcDir, 16, "Sorry, I need to use the Tastrade sample database.")




*-- Open first connection to Tastrade database, mark sharable

lcConnStr = [Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=] + ;

   lcDbc + [;SourceType=DBC;Exclusive=No;BackgroundFetch=No;Collate=Machine;]

lnConn1 = SQLSTRINGCONNECT(lcConnStr, .T.)

IF lnConn1 < 1

  MESSAGEBOX("Could not connect to Tastrade.dbc. Reason: " + MESSAGE(), ;

    16, "Sorry, could not connect")




*-- Open second statement handle on same connection

lnConn2 = SQLCONNECT(lnConn1)

IF lnConn2 < 1

  MESSAGEBOX("Could not connect to Tastrade.dbc. Reason: " + MESSAGE(), ;

    16, "Sorry, could not connect")




DO showhandles WITH "Opened 2 connections", lnConn1, lnConn2


*-- Idle the second statement handle


DO showhandles WITH "Connection 2 idled", lnConn1, lnConn2


*-- Idle the first statement handle


DO showhandles WITH "Connection 1 idled", lnConn1, lnConn2


*-- Open another connection handle to the database. This is

* done to demonstrate that reconnecting an idle connection

* may create a new connection handle to the database.


? "Connection 3 was assigned ODBC connection " ;




*-- Execute a query on idle statement handle 2

SQLEXEC(lnConn2, "SELECT * FROM customer")

DO showhandles WITH "Executed query on connection 2", lnConn1, lnConn2


*-- Execute a query on idle statement handle 1

SQLEXEC(lnConn1, "SELECT * FROM customer")

DO showhandles WITH "Executed query on connection 1", lnConn1, lnConn2


*-- Use new ASQLHANDLES() function to close open connections

lnHandles = ASQLHANDLES(laHandles)

FOR N = 1 TO lnHandles






PROCEDURE showhandles(tcMsg, tnConn1, tnConn2)


LOCAL lnCHnd1, lnCHnd2, lnSHnd1, lnSHnd2


*-- Get Connection handle for each connection

lnCHnd1 = SQLGETPROP(tnConn1,"ODBChdbc")

lnCHnd2 = SQLGETPROP(tnConn2,"ODBChdbc")

*-- Get Statement handle for each connection

lnSHnd1 = SQLGETPROP(tnConn1,"ODBChstmt")

lnSHnd2 = SQLGETPROP(tnConn2,"ODBChstmt")

? tcMsg

? "Connection 1: ODBC connection = " + TRANSFORM(lnCHnd1) ;

  + ", ODBC statement = " + TRANSFORM(lnSHnd1)

? "Connection 2: ODBC connection = " + TRANSFORM(lnCHnd2) ;

  + ", ODBC statement = " + TRANSFORM(lnSHnd2)



Text Box: "

The Developer Download files for this chapter, available at, include this code in SqlIdleDisconnect.PRG.

You will see the following results (the numbers will differ on your system, but the basic results should be the same):

Opened 2 connections

Connection 1: ODBC connection = 29562256, ODBC statement = 29564872

Connection 2: ODBC connection = 29562256, ODBC statement = 29567904


Connection 2 idled

Connection 1: ODBC connection = 29562256, ODBC statement = 29564872

Connection 2: ODBC connection = 29562256, ODBC statement = 0


Connection 1 idled

Connection 1: ODBC connection = 0, ODBC statement = 0

Connection 2: ODBC connection = 0, ODBC statement = 0


Connection 3 was assigned Connection handle 29562256


Executed query on connection 2

Connection 1: ODBC connection = 29568920, ODBC statement = 0

Connection 2: ODBC connection = 29568920, ODBC statement = 29564800


Executed query on connection 1

Connection 1: ODBC connection = 29568920, ODBC statement = 29571312

Connection 2: ODBC connection = 29568920, ODBC statement = 29564800

Rollback on disconnection

VFP 9 gives you control over handling manual transactions when a connection is disconnected. The standard syntax for starting a manual SQL transaction in VFP is:

SQLSETPROP(lnConn, "Transactions", 2)

The transaction remains in effect until SQLCOMMIT() or SQLROLLBACK() is issued. In previous versions of VFP, if the connection terminates unexpectedly before you issue one of these commands, the transaction is automatically committed. This can happen if there is a pending transaction and you call SQLDISCONNECT(), quit the app, or the app crashes.

In VFP 9, SQLSETPROP() and DBSETPROP() now include a DisconnectRollback property. (Use SQLSETPROP() to change the setting for an active connection and DBSETPROP() to change the default setting for a named connection in a database.) If DisconnectRollback is set to .T., any pending manual transaction on that connection is rolled back automatically if the connection terminates unexpectedly. If DisconnectRollback is .F. (the default), the transaction is automatically committed, as in previous versions. If a connection is shared, automatic rollback does not occur until the last statment handle associated with the connection is disconnected.

Get count of records affected by SQL Pass-Through

SQLEXEC() and SQLMORERESULTS() have a new optional parameter that provides more information on the number of records affected by a SQL command. The syntax is:

SQLEXEC( nStatementHandle [, cSQLCommand [, cCursorName [, aCountInfo ] ] ] )

SQLMORERESULTS( nStatementHandle [, cCursorName [, aCountInfo ] ] )

aCountInfo is the name of an array to populate with row count information. If the array doesn’t exist, it’s created. The array contains one row for each SQL command you issue that doesn’t return a record set, and one row for each record set returned. The contents of each row depend on what happened. Table 1 shows the possible values.

Table 1. aCountInfo results. The array contains one row for each SQL command that does not return a result set and one row for each result set returned. These are the possible values for each row.


Column 1

Column 2

Command succeeded and does not return a result set (e.g., INSERT, UPDATE)

Empty string

Number of records affected, or -1 if not available

Command failed



Command returned a result set

Cursor name

Number of records affected, or -1 if not available


If you execute a command that returns multiple result sets and the BatchMode property of the connection is .T., the array contains one row for each result set. If BatchMode is .F., each call to SQLMORERESULTS() replaces the previous contents of the array with a single row describing the new result set.

Here are a couple of ways to use the aCountInfo parameter to simplify your code. In the past, to determine the number of rows affected by an Insert, Update, or Delete command on a remote table, you needed to follow it immediately with a second command to retrieve the number of rows affected (assuming the backend supports a rowcount function). Here’s an example of how to do this in SQL Server:

SQLEXEC(lnConn, "UPDATE products SET price = price * 1.05 where price < 10")

SQLEXEC(lnConn, "SELECT @@rowcount AS nchanged", "tmpCount")

Using aCountInfo makes it unnecessary to execute this secondary query to get the count:

SQLEXEC(lnConn, "UPDATE products SET price = price * 1.05 where price < 10",


Second, when a command is executed in batch mode and returns multiple result sets, VFP automatically creates the cursor name for result set 2 and above by adding “1”, “2”, etc. to the name of the first cursor. With aCountInfo, you can get a list of the cursor names without needing to guess at them.

Here is an example that shows several of the possible results for aCountInfo:





LOCAL lcDir, lcDbc, lcConnStr, lnConn, laCount[1], ;

 lnRes, lcCmd


lcDir = HOME(2) + "tastrade\data\"

lcDbc = lcDir + "tastrade.dbc"

IF ! FILE(lcDbc)

  MESSAGEBOX("The VFP sample database Tastrade.dbc cannot be found in " ;

    + lcDir, 16, "Sorry, I need the Tastrade sample database.")




lcConnStr = [Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=] + ;

  lcDbc + [;SourceType=DBC;Exclusive=No;BackgroundFetch=No;Collate=Machine;]


IF lnConn < 1

  MESSAGEBOX("Could not connect to Tastrade.dbc. Reason: " + MESSAGE(), ;

    16, "Sorry, could not connect")




*-- Query returns 2 result sets

? "Two queries returning 2 result sets:"


SELECT * FROM customer WHERE country = 'USA';SELECT * FROM customer WHERE country = 'Mexico'


lnRes = SQLEXEC(lnConn, lcCmd, "tmpCust", laCount)


IF lnRes < 1

  ? "Error in query: " + MESSAGE()




*-- One query updates records, second query returns results

? "(1) Update records for some customers, (2) Query returns customers in UK:"



UPDATE customer SET min_order_amt = min_order_amt + .01 WHERE min_order_amt <= 100;SELECT * FROM customer WHERE country = 'UK'


lnRes = SQLEXEC(lnConn, lcCmd, "tmpCust", laCount)


IF lnRes < 1

  ? "Error in query: " + MESSAGE()




*-- Query is successful but did not update any records

? "Attempt to update records but do not return a result set, no records are updated:"



UPDATE customer SET min_order_amt = 1000 WHERE country = 'LalaLand'


lnRes = SQLEXEC(lnConn, lcCmd, "tmpCust", laCount)


IF lnRes < 1

  ? "Error in query: " + MESSAGE()




*-- Query failed; no column named 'countryname'

? "Query fails due to erroneous column name:"



SELECT * FROM customer WHERE countryname = 'UK'


lnRes = SQLEXEC(lnConn, lcCmd, "tmpCust", laCount)




Text Box: "

The Developer Download files for this chapter, available at, include this code in ACountInfo_vfp.PRG. They also include ACountInfo_sql.PRG, which uses the SQL Server Northwind database and demonstrates how the ACountInfo parameter is affected by asynchronous queries.

Other SQL enhancements

VFP 9 adds new functionality to handle progressive fetching, delayed memo fetching, Unicode mapping, and connection management. While you may never have needed the first three features, the last one is something we have been wanting for a long time.

Detecting delayed memo fetching

If a remote view includes large memo fields, it can take a long time to return the results. To work around this, VFP has always supported delayed memo fetching. DBSETPROP() and DBGETPROP() include a FetchMemo property that invokes delayed memo fetching when
set to .F. (The setting has no effect on local views.) In addition, the Advanced Options dialog in the View Designer has a Fetch Memo checkbox you can uncheck to turn on delayed
memo fetching.

When delayed memo fetching is in effect, the contents of memo fields are not retrieved initially with the view results. Instead, VFP only retrieves the contents of a memo field for a given row the first time it is needed, such as for display in a form, in a memo edit window, or for use in an expression or command. The tradeoff is a small delay each time the memo field is first needed for a given row, versus a long delay when the view is initially queried if delayed memo fetching is off. Also, delayed memo fetching avoids retrieving the contents of memo fields never actually used by the application.

In VFP 8 and previous versions, when delayed memo fetching is in effect, there is no way to determine whether a memo field in a given record has been retrieved yet. Even a simple reference such as LEN(“notes”) causes the memo field’s contents to be fetched. VFP 9 introduces a new function to give you this information. The syntax is:

ISMEMOFETCHED( cFieldName | nFieldNumber [, nWorkArea | cTableAlias ] )

ISMEMOFETCHED() returns .T. if the specified memo field in the current record of the cursor has been retrieved, .F. if it has not. If the cursor is at BOF() or EOF(), it returns .NULL. Calling ISMEMOFETCHED() does not cause the memo field to be fetched, so it can be used safely to determine whether a delay may occur before you try to access the memo field’s contents. We haven’t come up with a practical use for this yet; usually you either need the contents of the memo field or you don’t. It’s probably a case of the VFP team exposing previously internal functionality, but there’s a good chance someone else will find it useful.

Monitoring fetch progress

CURSORGETPROP() has two new properties to help monitor the fetch status of an ODBC or ADO-based cursor. RecordsFetched returns the number of records fetched into the result cursor so far. FetchIsComplete returns .T. if the fetch is complete, .F. if it is still in progress. If you attempt to retrieve these properties for a local view or table, you get an error 1467 (“Property is invalid for local cursors”).

There are several situations where these properties are useful. The first is when progressive fetching is in effect for a remote view. This happens when the view’s FetchSize is less than the total number of records to be returned, and FetchAsNeeded is .F. In that case, VFP retrieves FetchSize number of records into the cursor, returns control to the application, and continues to retrieve the remaining records in the background. Previously, there was no straightforward way to know for sure whether progressive fetching was still in progress, or if all records had been retrieved. Now, you can check the value of FetchIsComplete; it continues to return .F. until all records have been retrieved.

Another situation is when fetch-on-demand is in effect for a remote view. This is done by setting the cursor’s FetchAsNeeded property to .T. In this case, VFP does not employ progressive fetching in the background after retrieving the initial batch of records. Instead, it waits until the user or application attempts to access a record not yet retrieved into the cursor, such as when the user scrolls down in a grid. When that happens, VFP retrieves as many additional records from the back end as needed, in increments of FetchSize. Previously, there was no way to know whether all records had been returned. Evaluating RECCOUNT() in any way at all, such as in an expression like


would cause VFP to immediately retrieve all records matching the query to determine the
final RECCOUNT(), the same as if you issued GO BOTTOM. The new RecordsFetched property returns the number of records fetched so far, without triggering the retrieval of any additional records.

If a query is issued in asynchronous mode using SQLEXEC(), VFP retrieves a number of records equal to the current SQLGETPROP() FetchSize setting, and then stops. To retrieve the remaining records, you must keep repeating the SQLEXEC() command until it returns 1, indicating all records have been returned. The new RecordsFetched and FetchIsComplete properties can be used to determine whether there are additional records to retrieve. While useful, they are not essential here, because SQLEXEC() returns a value indicating whether the query is complete, and RECCOUNT() can be used on the cursor without forcing the retrieval of all records.

Finally, if you appended records to the local cursor, RecordsFetched still returns the original number of records retrieved from the remote database, where RECCOUNT() returns the current number of records in the cursor including appended records.

There are a couple of things to be aware of when using these new properties. If the MaxRecords property of the cursor is set to a positive value, and that value is less than the number of records matching the query, the fetch will stop once the cursor contains MaxRecords records. In that case, FetchIsComplete will return .T., even though the full query would have returned additional records. Also, if SET ESCAPE is ON, the user can press ESCAPE at any time to cancel the query. The cursor will contain the number of records fetched up to that point, and FetchIsComplete will be .T. even though the query was cut short. As far as we can tell, there is no way to determine whether the fetch was cut short by the user pressing ESCAPE.

Map remote Unicode data to ANSI in memo fields

The new SYS(987, lExpr) function determines whether remote Unicode data is mapped to ANSI in memo fields. If lExpr is .T., Unicode data is mapped to ANSI when retrieved into a memo field by SQL Pass-Through, remote views, and CursorAdapters. If lExpr is .F. (the default and the VFP 8 behavior), the memo field contains Unicode data.

Some SQL databases provide data types to store Unicode data, such as SQL Server’s nChar and nVarchar data types. In Unicode data, every character is stored as two bytes (Unicode is not the same thing as double-byte characters, which VFP has always supported). By default, VFP retrieves each byte into a memo field as a separate character, producing odd-looking results. For example, assume a SQL Server table Unicodetest has a field named Notes, defined as nVarchar(1000), and you execute this code:

SQLEXEC(lnConn, "update unicodetest set notes = 'It looks like this' where testid = 1")

SQLEXEC(lnConn, "select notes from unicodetest where testid = 1", "tmpResults")

MODIFY MEMO tmpResults.notes

The results are shown in Figure 1. The square boxes are CHR(0), which is the second byte of each two-byte Unicode character. If you execute SYS(987, .T.) prior to the query, the memo field will contain the ANSI string “It looks like this” instead.

Figure 1. Remote Unicode data retrieved into a memo field. The square boxes are CHR(0), the second byte of each Unicode character. Use SYS(987, .T.) to convert the Unicode data to ANSI when it is retrieved.

If you call SYS(987) without the lExpr parameter, it returns the current setting. Once a cursor is opened, changing the SYS(987) setting does not affect future fetches by that cursor. This setting is global to all data sessions.

Determining what connections are open

The new ASQLHANDLES() function provides an often-requested means to determine what statement handles are active. The syntax is:

ASQLHANDLES( ArrayName [, nStatementHandle ] )

This populates a one-dimensional array with a list of all active SQL statement handles, and returns the number of handles in use. If the array does not exist it is created; if it exists it is redimensioned appropriately. The optional nStatementHandle parameter allows you to supply an existing statement handle. In this case, the array is populated with a list of statement handles that share the same connection handle, including nStatementHandle itself.

This is a welcome addition, because prior versions of VFP don’t provide any way to determine what handles are active, or the number of the highest handle in use. You probably have written cleanup code like this to make sure all connections are closed:

FOR n = 1 TO 200  && Hopefully high enough






ASQLHANDLES() provides a more precise and much faster solution:

lnHandles = SQLHANDLES(laHnd)

FOR n = 1 TO lnHandles



ADO enhancements

Prior to VFP 8, many if not most VFP developers avoided using ADO as a data access method, because it required a good deal of coding and because it did not fit into the VFP cursor model. The introduction of the CursorAdapter in VFP 8 made it possible to access ADO data directly using an updatable cursor. VFP 9 provides two more improvements when using ADO.

ADO bookmark support

CURSORGETPROP() supports a new ADOBookmark property that returns the ADO bookmark for the current record in an ADO-based cursor. An ADO bookmark uniquely identifies a single row in a recordset, much like the RECNO() function in a FoxPro cursor. The recordset can be positioned to a specific row by setting its Bookmark property to a previously obtained bookmark. This is particularly useful when saving the current position, executing a Find, and then returning to the previous position:

loBookmark = loRs.Bookmark

loRs.Find("country = 'Burkina Faso'")


   loRs.Bookmark = loBookmark


So why would you need to know the ADO bookmark for a record in a cursor if the cursor has its own RECNO() property? When a CursorAdapter uses an ADO recordset as its DataSource, moving the record pointer in the cursor does not move the row pointer in the recordset. If you need to access the row in the recordset that corresponds to the current record in the cursor, the ADOBookmark property provides a convenient way to do this:

loBookmark = CURSORGETPROP("ADOBookmark")

loRS.Bookmark = loBookmark

Not all databases support bookmarks, and they are usually supported only for keyset, static, and client-side cursors. To determine whether a recordset supports bookmarks, query its Supports method:

llBookmarks = loRS.Supports(adBookmark)  && adBookmark = 8192

If you call CURSORGETPROP(“ADOBookmark”) and the recordset does not support bookmarks, you get error 2188 (“Cursor doesn’t support ADOBookmark property”). Calling it for a local cursor gives you error 1467 (“Property is invalid for local cursors”).

Cancel ADO fetch

You can cancel a lengthy query in a CursorAdapter by pressing Escape, provided you SET ESCAPE ON prior to calling CursorFill or CursorRefresh. In VFP 8, this works for ODBC, XML and Native data sources, but not for ADO. If the DataSourceType is ADO, you must wait until the fetch process is complete, or else take drastic steps like releasing the CursorAdapter in order to cancel a runaway query.

In VFP 9, pressing Escape works with the ADO DataSourceType as well. Although this is a welcome change, you will find pressing Escape may not stop the query right away. That’s because there are actually two steps involved in fetching data into a CursorAdapter through ADO. First, VFP tells ADO to execute the query, which brings the data into the ADO recordset. Then VFP converts the data in the recordset into a cursor. You can tell the second step has begun when the cursor name and record count appear on the status bar, if it is turned on. If you press Escape during the first step, the process will not stop until that step is complete, which is usually the longer of the two steps. If you press Escape while the second ADO-to-cursor step is underway, the fetch stops as soon as the current batch of records (determined by the cursor’s BatchSize property) has been retrieved from the recordset.

Allowing the user to cancel the CursorAdapter’s fetch is a great idea in development mode, but may not be such a good idea in an application. There appears to be no way to determine whether CursorFill or CursorRefresh completed normally or were cut short by the user pressing Escape. As a result, you have no way to determine whether the result set contains all records matching the query.

The CursorAdapter Builder adds code to the Init method of the CursorAdapter to set the recordset’s CursorLocation to 3 (adUseClient). In most cases you can reduce the time needed to open the recordset by changing the CursorLocation to 2 (adUseServer). Not all databases support server-based cursors.

CursorAdapter changes

Prior to VFP 8, we had several powerful technologies for accessing local and remote data: local views, remote views, SQL Pass-Through, ADO recordsets, and XML. Unfortunately, each had a different syntax, and only ADO provided an object-oriented interface. When the CursorAdapter was introduced in VFP 8, it provided a consistent object-oriented interface for accessing data, no matter what the source.

As exciting as it was, the CursorAdapter had some limitations when accessing remote data. If the remote database used identity columns or default values, it was necessary to add custom code to retrieve their values after an Insert. Conflict checking worked automatically if you let VFP generate the insert and update commands, but not if you overrode them. Conflict checking via timestamp fields did not work at all. VFP 9 removes all these limitations and provides several other improvements that make CursorAdapters work more smoothly with remote data.

Conflict checking

If you don’t override a CursorAdapter’s update or delete commands, the WhereType setting determines how VFP enforces conflict checking. The settings, shown in Table 2, are the same as for a remote view.

Table 2. WhereType settings for CursorAdapters and remote views.


Foxpro.H constant

When an update conflict occurs



If a key field has been changed in the local cursor



If any of the fields marked as updatable have been changed in the remote table



If any of the fields changed in the local cursor have been changed in the remote table



If the timestamp in the remote table has changed


VFP constructs the WHERE clause of the auto-generated update or delete statement in a way that ensures the update will succeed only if the WhereType conditions are met. (For a full explanation, see the topic “Managing Updates by Using Views” in Help.)

In a CursorAdapter, you can override the auto-generated commands either by entering your own commands in the UpdateCmd and DeleteCmd properties, or by overriding the BeforeUpdate and BeforeDelete methods and changing the cUpdateInsertCmd or cDeleteCmd parameters. Once you do this, the WhereType setting is irrelevant, because VFP no longer auto-generates the update and delete commands.

It’s unlikely you’ll go through the tedious process of creating a WHERE clause that handles all four WhereType conditions. As a substitute, two new properties, ConflictCheckType and ConflictCheckCmd, have been added to simplify this task. (Note that these were new to VFP 8 SP1, not VFP 9.) They are ignored unless you override the auto-generated update or delete commands and set ConflictCheckType to a value other than zero. The five possible settings are shown in Table 3.

Table 3. The possible values for the ConflictCheckType property. Note that, even though the values 1-4 are used, these do not have the same meanings as the corresponding WhereType values.




Do not perform conflict checks.


If no records are updated or deleted, generate an error 1585 (“Update conflict...”).


Verify that no more than one record is affected by an update or delete command. If more than one record is affected, generate error 1495 (‘The key defined by the KeyField property for table “alias” is not unique.’)


1 + 2. In other words, make sure one and only one record is affected by the update or delete command.


Appends any custom command specified in the ConflictCheckCmd property to the end of the command in the UpdateCmd or DeleteCmd properties.


To understand how these settings work, suppose you override UpdateCmd with this:

UPDATE customer SET custname=?cursor1.custname WHERE custid=

   ?cursor1.custid AND custname=?OLDVAL('custname','cursor1')

This is similar to the auto-generated code VFP produces with WhereType=3, assuming only the Custname field changed. It succeeds only if VFP finds a record in the remote data source with the same key value in Custid and the previous value of Custname. If another user changed Custname for this same record in the meantime, no records will match and the update fails. Without conflict checking, the update fails, but you do not know it. With ConflictCheckType set to 1, VFP generates a 1585 error because no records are updated in the remote data source.

Now consider the case where you override the Delete command with this:

DELETE FROM orders WHERE orderid=?cursor1.orderid

The choice of ConflictCheckType depends on how you want to handle two possibilities: another user deleted the order in the meantime, or more than one order has the same Orderid by accident. With ConflictCheckType=1, if another user already deleted the order, you get an error and the update fails. However, if more than one order has the same Orderid, the update succeeds and all matching orders are deleted. With ConflictCheckType=2, you don’t get an error if another user already deleted the order, but you get an error if more than one order has this Orderid. With ConflictCheckType=3, you get an error in either case.

There are a couple situations where conflict checking may not give you the results
you expect. First, the UpdateCmd and DeleteCmd properties can contain more than one command. In this case, conflict checking is only based on the number of records affected by the last command.

Also, if the CursorAdapter’s BatchUpdateCount is set to a number greater than 1 (the default), ConflictCheckType settings of 1, 2, and 3 are ignored. When BatchUpdateCount is set to the default of 1 and table buffering is in effect, VFP sends a separate SQL command to the remote data source for each updated or deleted record in the cursor. If BatchUpdateCount is set to a number greater than 1, VFP concatenates multiple update or delete commands into a single string, separated by semicolons. The string is sent to the remote data source as a single SQL command, in order to increase efficiency. In this case, there is no way to know the number of records affected by each individual command in the concatenated string, so conflict checks based on the number of records affected do not work.

The Advanced Update Properties dialog of the CursorAdapter Builder has a new Conflict page (see Figure 2) that lets you select the ConflictCheckType and, optionally, enter a ConflictCheckCmd if the ConflictCheckType is set to 4.

Figure 2. The CursorAdapter Builder has a new Conflict page that lets you select the value for the ConflictCheckType property and, optionally, enter the ConflictCheckCmd when ConflictCheckType is set to 4, “Check based on a custom command.”


When a record is inserted or updated in a remote database, the remote database may change the contents of one or more columns based on autoincrement values, default values, or the results of triggers. When a CursorAdapter sends an insert or update command to a remote database, it only receives back a result code and/or error message. As a result, the CursorAdapter is not aware of changes made by the remote database, and the VFP cursor will not reflect those new values. In VFP 8, one workaround was to add code to the CursorAdapter AfterUpdate or AfterInsert methods to retrieve the new values and insert them into the cursor (this example assumes the DataSourceType is ODBC):

* Retrieve autoincrement value from SQL Server after an insert

SQLEXEC(this.Datasource, "select @@identity as keyval", "tmpKey")

REPLACE custid WITH tmpKey.keyval in (this.alias)

VFP 9 introduces six new CursorAdapter properties to automate this process. InsertCmdRefreshFieldList is a comma-delimited list of fields in the cursor that are refreshed automatically with values from the remote database when an insert is performed. InsertCmdRefreshKeyFieldList is the name of the key field used to query for these values. Immediately after a successful insert, VFP automatically queries the remote database for a record matching the key values in InsertCmdRefreshKeyFieldList and uses the results to refresh the cursor fields specified in InsertCmdRefreshFieldList.

In many cases those two properties are sufficient, but in other situations you must supply the command VFP uses for this query. The most common example is when the key field in the remote table is an Autoincrement column. In this case, VFP cannot perform an automatic auto-refresh query after the insert, because it does not know the key value the remote database just generated and inserted. Instead, you must supply a SQL command in the InsertCmdRefreshCmd property VFP can use to retrieve values from the new record. For example, assume a SQL Server table has an Autoincrement primary key column Custid and supplies a default value for the Dateadded column. The following property settings enable VFP to retrieve the new values for Custid and Dateadded after a successful insert:

.InsertCmdRefreshFieldList = "custid,dateadded"

.InsertCmdRefreshCmd="select custid,dateadded from customer where custid = @@IDENTITY"

An experienced SQL Server developer might question why @@IDENTITY is used to retrieve the new Autoincrement value rather than SCOPE_IDENTITY(). Usually, SCOPE_IDENTITY() is indeed the better choice because of a limitation of @@IDENTITY. The @@IDENTITY function returns the last Autoincrement value generated on the current connection. If the table you are inserting a record into has a trigger that inserts a record in a second table, and the second table also has an Autoincrement column, @@IDENTITY will return the Autoincrement value from the second table, which is not what you want. The SCOPE_IDENTITY() function returns the last Autoincrement value from the “scope” of your command, which by definition does not include the actions of the trigger. Unfortunately, the SQL Server ODBC driver executes parameterized inserts using the sp_executesql stored procedure. Once this stored procedure is completed, its “scope” is gone, and SCOPE_IDENTITY() returns NULL rather than the Autoincrement value from the insert. Because @@IDENTITY simply returns the last Autoincrement value on the connection, it works in an auto-refresh command.

The properties UpdateCmdRefreshFieldList, UpdateCmdRefreshKeyFieldList, and UpdateCmdRefreshCmd serve the same purpose for updates. Usually when performing an update, VFP already knows the key value, so a separate UpdateCmdRefreshCmd is not needed. One case where it is useful is for a database designed to only be accessed by stored procedures. You need to supply a stored procedure call in UpdateCmdRefreshCmd to return the values needed to refresh the cursor.

The CursorAdapter Builder has a new Refresh button on the Auto-Update page that brings up a Refresh Properties dialog (see Figure 3). The Refresh After Insert page allows you to enter the InsertCmdRefreshCmd and to select fields for the InsertCmdRefreshFieldList and the InsertCmdRefreshKeyFieldList. The Refresh After Update page allows you to enter the UpdateCmdRefreshCmd and to select fields for the UpdateCmdRefreshFieldList and the UpdateCmdRefreshKeyFieldList.

Figure 3. The CursorAdapter Builder has a new Refresh Properties dialog that lets you set values for the auto-refresh properties, using pages 3 and 4. This dialog is accessed by clicking the Refresh button on the Auto-Update page.

Text Box: "

The Developer Download files for this chapter, available at, include four forms, a database, and a readme file that demonstrate how auto-refresh works. Autorefresh_sql.SCX, Autorefresh_connect.SCX, and Autorefresh_add.SCX create a new table in the SQL Server Northwind database and use that. Autorefresh_vfp.SCX and Autorefresh_add.SCX use two tables, Autorefresh.DBF and Nextid.DBF, in the Autorefresh.DBC database. The file Readme_autorefresh_sql.TXT explains how the SQL demo works and what permissions may be required.

Timestamp support

When you create a remote view with the WhereType property set to 4 (Key and timestamp), VFP automatically determines which field in the remote table is timestamp-compatible and saves this information in the view definition. When a record in the view is changed and updated, VFP compares the value of the timestamp column in the cursor to the value of the timestamp column in the remote database. If they are different, error 1585 (“Update conflict...”) results.

In VFP 8, the CursorAdapter allows you to set WhereType to 4, but does not have the capability to determine which column in the remote table was a timestamp. As a result, WhereType=4 does not work in CursorAdapters in VFP 8.

The new TimeStampFieldList property allows you to specify which field in the cursor is considered a timestamp for conflict checking when WhereType is 4. If you set WhereType to 4, but do not supply a TimeStampFieldList value, conflict checking is not performed. The timestamp field name must also be included in the UpdateNameList.

The RefreshTimeStamp property can be used to indicate which timestamp fields are refreshed automatically when performing an insert or update. This has the same effect as including these fields in the InsertCmdRefreshFieldList and UpdateCmdRefreshFieldList, and is not necessary if you have done so.

The CursorAdapter Builder has a new Timestamp fields textbox on the Auto-Update page that allows you to set the TimeStampFieldList property. It also has a Refresh timestamp checkbox on the Fields Refresh page of the new Refresh Properties dialog (see Figure 3) that allows you to set the RefreshTimeStamp property.

On-demand record refresh

A RecordRefresh method has been added to the CursorAdapter to perform the same function REFRESH() performs for views. The syntax is:

.RecordRefresh( [ nRecords ] [, nRecordOffset ] )

The nRecords parameter specifies the number of records to refresh, in physical order. If it is omitted, only the current record is refreshed. The nRecordOffset parameter specifies the number of records prior to the current record where the refresh begins. If it is 0 or omitted, refresh begins with the current record. For example, if you pass (2,3) for the parameters, two records are refreshed, beginning with the 3rd record prior to the current record. If you pass (3,0), three records are refreshed beginning with the current record.

The return value is the number of records refreshed. If no records are refreshed but there is no error, 0 is returned. If there is an error, RecordRefresh returns -1 less the number of records successfully refreshed. For example, if 3 records are refreshed before the error occurs, -4 is returned; if no records are refreshed but there is an error, -1 is returned.

If you inserted records into a cursor with table buffering on, but did not update them via TABLEUPDATE(), they are ignored and not counted in the return value. If you modified a record, the buffered changes are preserved and only the CURVAL() values are refreshed. If the target record in the cursor cannot be located in the local or remote table(s), it is marked Deleted. This is based on the assumption the record must have been deleted from the remote table after it was retrieved into the cursor. In view of this, it is important to make sure
you specify a valid KeyFieldList before calling RecordRefresh, to avoid accidentally
deleting records.

The RefreshIgnoreFieldList property is a comma-delimited list of fields to ignore when refreshing. BeforeRecordRefresh(nRecords, nRecordOffset) receives the same two parameters passed to RecordRefresh. If it fails or returns .F., RecordRefresh isn’t executed. AfterRecordRefresh(nRecords, nRecordOffset, nRefreshed) receives the two parameters passed to RecordRefresh, plus a third parameter nRefreshed that is the return value from RecordRefresh.

The RefreshCmd property allows you specify an alternative to the automatically generated refresh command. The command must return a single record with fields in the same order as those in the cursor, excluding any fields in the RefreshIgnoreFieldList. RefreshCmdDataSourceType and RefreshCmdDataSource allow you to specify an alternative data source type and command for the RefreshCmd. RefreshAlias contains the name of the read-only cursor opened temporarily by VFP during a RecordRefresh to hold the data retrieved from the data source.

Here’s an example using the SQL Server Northwind database. It creates a CursorAdapter and sets the properties needed for RecordRefresh, opens a connection to the database, and fills the cursor. It then changes the company name in the remote database and calls RecordRefresh to refresh five records in the cursor.


LOCAL lcConnStr, lnConn

*--  You may need to adjust the Server name:

lcConnStr = "driver={sql server};server=(local);database=northwind;" ;

  + "Trusted_Connection=yes;"


goCA = CREATEOBJECT("CursorAdapter")


  .ALIAS = [cursor1]


  .SELECTCMD = [select CustomerID, CompanyName, Country, Fax,] ;

    + [Phone from Customers]

  .CURSORSCHEMA = [CustomerID C(5), CompanyName C(40), Country C(15),] ;

    + [ Fax C(24), Phone C(24)]

  .KEYFIELDLIST = [CustomerID]

  .TABLES = [Customers]

  .UPDATABLEFIELDLIST = [CustomerID, CompanyName, Country, Fax, Phone]

  .UPDATENAMELIST = [CustomerID Customers.CustomerID, CompanyName ] ;

    + [Customers.CompanyName, Country Customers.Country, Fax Customers.Fax,] ;

    + [Phone Customers.Phone]



  .REFRESHCMD = [select CompanyName, UPPER(Country), Fax, Phone from ] ;

    +[Customers where CustomerID = ?EVAL(this.RefreshAlias + ".CustomerID")]





*--  Open a shared connection to the database


? "Attempting to connect to the SQL Server Northwind database"

lnConn = SQLSTRINGCONNECT(lcConnStr, .T.)






*--  Set the DataSource and the RefreshCmdDataSource to this connection


goCA.RefreshCmdDataSource = lnConn

*--  Fill the cursor and browse it






SELECT cursor1



*--  Put "*" on the end of each company's name in the remote database.

SQLEXEC(lnConn, "UPDATE customers SET CompanyName = " ;

  + "LEFT(RTRIM(CompanyName)+'*',40)")


*--  Move to record 10 and call RefreshRecord to refresh

*  the current record and the 4 previous records

GO 10

MESSAGEBOX("Press OK to see the refreshed CompanyName for rows 6-10", ;

  48, "About to refresh")



*--  Remove the "*" from each customer

SQLEXEC(lnConn, "UPDATE customers SET CompanyName = " ;

  + "REPLACE(CompanyName, '*', '')")


There are several things to note in this example. The RefreshIgnoreFieldList includes CustomerID because it’s the primary key and cannot change on an existing record, so there is no reason to retrieve it. As a result, CustomerID is not included in the RefreshCmd, whose field list must match the field list in the SelectCmd in order, excluding any fields in the RefreshIgnoreFieldList Property. The RefreshCmd selects UPPER(Country) so we can see it works; in most cases you would not want to do this. It isn’t really necessary to set the RefreshCmd here, because the CursorAdapter would accomplish the same thing by default. The RefreshCmdDataSourceType and RefreshCmdDataSource properties are shown for illustration purposes, but do not have to be specified if they are the same as the DataSourceType and DataSource.

Text Box: "

The Developer Download files for this chapter, available at, include this code as RecordRefresh_Sql.PRG. They also include a companion program, RecordRefresh_Vfp.PRG, that uses the VFP Tastrade database and does not require SQL Server.

The CursorAdapter Builder’s new Refresh Properties dialog (see Figure 3) provides a place to set these record refresh properties. The Record Refresh page allows you to enter the RefreshCmd, to select the RefreshCmdDataSourceType, and to specify the RefreshCmdDataSource. The Fields Refresh page allows you to select fields for the RefreshIgnoreFieldList. Be aware that the fields you pick on this page are the ignored fields, not the refreshed fields.

In VFP 8, calling REFRESH() on a cursor opened by a CursorAdapter not only does not work, but generates an error. This is because the CursorAdapter does not set certain properties of the cursor such as Tables, KeyFieldList, and UpdatableFieldList needed for the REFRESH() function. In VFP 9, this is fixed; if REFRESH() is called for a cursor opened by a CursorAdapter, VFP actually calls the RecordRefresh method of the CursorAdapter instead.

Set default values for CursorFill

The CursorFill method of the CursorAdapter includes the parameters lUseCursorSchema and lNoData, which are .F. by default. In VFP 8, if you want to change either of these to .T. you must override the CursorFill method. This is particularly annoying in the case of lNoData that you often need to override as follows:

LPARAMETERS luseCursorSchema, lNoData, nOptions, Source

RETURN DODEFAULT(luseCursorSchema, .T., nOptions, Source)

VFP 9 adds two new properties to CursorAdapter, UseCursorSchema and NoData. These are used as the default values for the CursorFill parameters lUseCursorSchema and lNoData, so you no longer need to override that method to change this behavior. If you do pass values for the lUseCursorSchema and lNoData parameters when you call CursorFill, the UseCursorSchema and NoData properties are ignored.

The CursorAdapter Builder has a new checkbox in the Schema section of the Data Access page, Use CursorSchema when filling cursor, to set the UseCursorSchema property. The Data fetching section of the same page has a new checkbox, Initially open with no data, to set the value of the NoData property.

Delayed memo fetch

In VFP 8, delayed memo fetching did not work in CursorAdapters. If the FetchMemo property was set to .F., the cursor was retrieved without the memo contents as expected. However, if you attempted to access the memo field, you received an error 1491 (“No update tables are specified. Use the Tables property of the cursor.”). VFP 9 fixes this and the contents of the memo field are retrieved as soon as you attempt to access it.

In addition, VFP 9 provides a way for you to override delayed memo fetching. DelayedMemoFetch(cMemoName) is a protected method called when you try to access the contents of a memo, general, or blob field not fetched yet. If you override this method,
your code must return text for VFP to put into the memo field in the cursor. In the case
of a blob field, DelayedMemoFetch must return blob data rather than text by using the CAST() function:


You might need to override delayed memo fetching to format or clean up the memo data before displaying it in VFP, to retrieve the contents from an alternate source, or if a stored procedure must be called to retrieve it. The FetchMemoDataSourceType and FetchMemoDataSource properties allow you to specify an alternative data source for DelayedMemoFetch.

When DelayedMemoFetch is called, a copy of the local cursor is opened temporarily, positioned on the current record. The name of this temporary cursor is found in the RefreshAlias property. The cursor referenced by RefreshAlias contains the original values of each field; the cursor referenced by Alias contains buffered changes. Neither of these cursors contain the actual contents of the memo field yet.

Because DelayedMemoFetch is a protected method, it can not be called from outside the class. In fact, it is not meant to be called directly at all. Calling it from another method in the class generates an error 2077 (“Operation is not allowed for CursorAdapter at this time.”).

The FetchMemoCmdList property provides another way to override delayed memo fetching. It is a comma-delimited list containing memo field names and the corresponding commands to retrieve the memo field contents. The commands must be placed in angle brackets. For example:

.FetchMemoCmdList = "notes <select notes from customer where custid =

  ?EVAL(this.refreshalias + '.custid')>, followup <select followup from

  customer where custid = ?EVAL(this.refreshalias + '.custid')>"

FetchMemoCmdList is ignored if the FetchMemoDataSourceType is ADO.

The CursorAdapter Builder has a new Memo button in the Data fetching section of the Data Access page. This brings up a Memo Fetching Properties dialog (see Figure 4) that allows you to enter values for the FetchMemoCmdList, FetchMemoDataSourceType, and FetchMemoDataSource.

Disable automatic transactions

By default, the CursorAdapter tells ODBC and ADO to manage transactions automatically during Insert, Update, and Delete operations. In VFP 8, if you turn on manual transactions
for a CursorAdapter’s cursor, VFP still tells ADO or ODBC to use automatic transaction handling as well.

VFP 9 adds a UseTransactions property to the CursorAdapter. When .T. (the default), VFP tells ADO and ODBC to use automatic transaction handling, as in VFP 8. When
.F., automatic transaction handling is disabled, allowing you to use manual transactions
without any undesired interaction with automatic transactions. The Auto-Update page
of the CursorAdapter Builder has a new
Use transactions checkbox to set the
UseTransactions property.


Figure 4. The CursorAdapter Builder has a new Memo Fetching Properties dialog that lets you set values for FetchMemoCmdList, FetchMemoDataSourceType, and FetchMemoDataSource. This dialog is accessed by clicking the Memo button on the Data Access page.

Specify code page for XML data

The CursorAdapter Flags property now supports a value of 32768. The Flags property only applies when the DataSourceType property is “XML,” and is passed when constructing an XML UpdateGram. The 32768 value indicates a code page should be used. For more information on the effect of this setting, see the “Multi-language support” section in Chapter 10, “Managing XML.”

CHECK constraints and default values for all data sources

The CursorSchema property of a CursorAdapter defines the structure of its cursor. While you may primarily think of a cursor’s schema as defining field names and data types, the CREATE CURSOR command also supports CHECK constraints (field and record validation rules) and default values. Although it was undocumented, in VFP 8, you could include CHECK constraints and default values in the CursorSchema property:

loCursorAdapter.CursorSchema = "CUSTID I, NAME C(40) CHECK !EMPTY(name)

ERROR 'You must enter the customer name', STREET C(40), POBOX C(40),

CITY C(25), STATE C(2) DEFAULT 'CO', ZIP C(9), CHECK ! (EMPTY(street)

and EMPTY(pobox)) ERROR 'You must enter either a street or PO Box'"

However, these CHECK constraints and default values were only enforced if the DataSourceType was “XML.” VFP 9 enforces constraints and default values for all four data source types (Native, ODBC, ADO, and XML).

If you create CHECK constraints or default values in the local or remote table, the CursorAdapter Builder does not automatically add them to the CursorSchema when you click the Build button on the Schema section of the Data Access page. You must edit the CursorSchema property and add them manually, or set them programmatically before calling CursorFill. Note that any NULL constraints you add to the CursorSchema are ignored, and the NULL constraints from the actual local or remote table are used instead.

MapBinary and MapVarchar

The MapBinary and MapVarchar properties determine whether certain ADO and ODBC data types map by default to the new VFP 9 Blob, Varbinary, and Varchar types in CursorAdapters. Table 4 shows the default mapping behavior for the MapBinary property setting and Table 5 shows the default mapping behavior for the MapVarchar property setting.

These two properties are ignored if UseCursorSchema is set to .T. or if the lUseCursorSchema parameter passed to CursorFill is .T.; in that case the type conversion defined in CursorSchema is used. The CursorAdapter Builder has two new checkboxes on the Data Access page, Map Varchar and Map Binary, to set these properties. However, the CursorAdapter Builder itself ignores these property settings and builds the CursorSchema as if both were set to .F. The SET VARCHARMAPPING setting is ignored by CursorAdapters.

Table 4. Effect of the MapBinary property setting on default mapping of remote data types to VFP data types in CursorAdapters.

Data source

Remote data type

MapBinary = .T.

MapBinary = .F.




Memo (binary)


SQL_BINARY (precision <= 254)


Memo (binary)


SQL_BINARY (precision > 254)


Memo (binary)


SQL_VARBINARY (precision <= 254)


Memo (binary)


SQL_VARBINARY (precision > 254)


Memo (binary)


adVarBinary (precision <= 254)


Memo (binary)


adVarBinary (precision > 254)


Memo (binary)


adBinary (precision <= 254)


Memo (binary)


adBinary (precision > 254)


Memo (binary)

Table 5. Effect of the MapVarchar property setting on default mapping of remote data types to VFP data types in CursorAdapters.

Data source

Remote data type

MapVarchar = .T.

MapVarchar = .F.


SQL_WVARCHAR (precision <= 254)




SQL_WVARCHAR (precision > 254)




adVarChar (precision <= 254)




adVarChar (precision > 254)



Logical data type conversions

VFP 9 allows greater flexibility when mapping between remote data types and the VFP Logical data type in CursorAdapters and remote views. Previously, you could only map the remote data type Bit to a VFP Logical field. This caused problems if the remote database did not support the Bit data type (like Oracle), or if the database designer chose to store logical data as 1 and 0 in an integer or numeric column.

In VFP 9, you can map the ODBC and ADO data types listed in Table 6 to the VFP Logical data type. Numeric and decimal types can be mapped to VFP Logical only if the scale is zero.

Table 6. ODBC and ADO data types that can be mapped to the VFP logical data type in VFP 9.

ODBC data types

ADO data types




















When VFP retrieves data from the remote data source, it considers 0 to be .F. and all other values to be .T. When updates are sent to the remote database, the conversion is backend specific as determined by the ODBC driver, although in most cases .F. is mapped to 0 and .T. to 1.

Increased size of property values

In VFP 9, the Property Sheet can accept up to 8,191 characters for a property value; previously this limit was 255 (see the “Specifying property values” section in Chapter 2, “Controlling the Properties Window”). The previous limit caused problems with CursorAdapters, because many of the properties need to be more than 255 characters. To work around this, in VFP 8 the CursorAdapter Builder inserted code into the Init method to set the values of certain properties programatically (the 255 character limit only applied when setting property values in the Property Sheet). Unfortunately, the CursorAdapter Builder did not do this in the case of CursorSchema. As a result, if you tried to close the Builder with a large number of fields selected, you received the error message “The CursorSchema property must be no longer than 255 characters.”

With the Property Sheet limit increased substantially, you no longer receive the CursorSchema error message. In addition, the CursorAdapter Builder now sets the values of most properties directly in the Property Sheet rather than inserting code into the Init method.

VFP OLE DB enhancements

The VFP OLE DB provider was introduced in VFP 7 because Microsoft was shifting emphasis from ODBC to OLE DB as its primary data access model. It has become much more important since then, because Microsoft has made it clear the VFP ODBC provider will not be updated to handle new database features introduced in VFP 7, 8, and 9 such as database events and Autoincrement, Varchar, Binary, and Blob fields. If your database contains any of these newer features, attempting to access it via ODBC will produce the error “File was created in a later version of Visual FoxPro than the current version.”

Fortunately, the VFP OLE DB provider has been updated in both VFP 8 and 9 to handle all these new features, making it the preferred means to provide other applications access to your databases. In addition, VFP 9 introduces a few other improvements that make the OLE DB provider even more powerful.

XML support

The VFP 9 OLE DB provider now supports the CURSORTOXML(), XMLTOCURSOR(), and XMLUPDATEGRAM() functions, with one limitation. In an application, the _VFP.VFPXMLProgID property can be set to the name of a COM component that overrides the internal functionality for these functions. The OLE DB provider does not support the _VFP system variable, so there is no way to override the internal functions.

EXECSCRIPT() support

The EXECSCRIPT() function is now supported in the VFP OLE DB provider, but of course generates an error if you attempt to execute lines of code not allowed in the provider. For a complete list of these, see the Help topic “Unsupported Visual FoxPro Commands and Functions in OLE DB Provider.”

Return ADO recordset from a stored procedure

In VFP 7 and 8, the OLE DB provider could only return an ADO recordset to another application based on a SQL command passed in from that application. While another application could call a stored procedure in your VFP database, the stored procedure could not return the results in the form of a recordset. Instead, it returned an ADO recordset with a single row containing a single column named Return_value. That column contained whatever scalar value the stored procedure returned via a RETURN statement, or True if the stored procedure did not contain an explicit return value. The most common use of stored procedures in SQL databases is to return result sets, so this seriously limited the usefulness of VFP stored procedures when called from OLE DB enabled applications

The VFP 9 OLE DB provider includes a major enhancement, the ability to return ADO recordsets from stored procedures. The new SETRESULTSET(nWorkArea | cTableAlias) function allows you to mark an open cursor in a stored procedure as a result set. If the stored procedure is called using the VFP OLE DB provider, the marked cursor is returned in the form of an ADO recordset. If SETRESULTSET() is called more than once during a stored procedure, each time it is called it returns the work area number of the previously marked cursor, and then marks the new cursor as the result set. Only one cursor can be marked as the result set at a time, which means a VFP stored procedure can only return a single recordset.

Calling SETRESULTSET() with an invalid alias produces an error 11 (“Function argument value, type, or count is invalid”). Calling it with an empty work area number does not produce an error. If no cursor is marked, or if the marked cursor is closed before the stored procedure ends, a recordset with one row and one column named Return_value is returned, similar to the pre-VFP 9 behavior. If the stored procedure is called directly from VFP, marking of result sets is ignored.

CLEARRESULTSET() clears the marked cursor and returns its work area. If no cursor is currently marked, it returns 0. GETRESULTSET() returns the work area number of the marked cursor, or 0 if no cursor is currently marked as the result set. All three functions are scoped to the current DataSession.

Text Box: "

The Developer Download files for this chapter, available at, include a form (Recordset.SCX) and a database (Recordset.DBC) that demonstrates how to return an ADO recordset from a VFP stored procedure. The Help button on the form explains how it works.


In VFP 8, the CursorAdapter had a few missing features that made it difficult to use in certain situations. VFP 9 not only fixes those problems, but it also gives the CursorAdapter additional features that now make it the ideal method for remote data access in VFP. The other new remote data features in VFP 9 are icing on the cake for the best data-centric language there is.



Updates and corrections for this chapter can be found on Hentzenwerke’s website, Click “Catalog” and navigate to the page for this book.