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,
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:
SQLIDLEDISCONNECT( nStatementHandle )
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.
CLOSE DATABASES ALL
SET TALK OFF
CLEAR
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.")
RETURN
ENDIF
*-- 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")
RETURN
ENDIF
*-- 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")
SQLDISCONNECT(lnConn1)
RETURN
ENDIF
DO showhandles WITH "Opened 2 connections", lnConn1, lnConn2
*-- Idle the second statement handle
SQLIDLEDISCONNECT(lnConn2)
DO showhandles WITH "Connection 2 idled", lnConn1, lnConn2
*-- Idle the first statement handle
SQLIDLEDISCONNECT(lnConn1)
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.
lnConn3 = SQLSTRINGCONNECT(lcConnStr)
? "Connection 3 was assigned ODBC connection " ;
+ TRANSFORM(SQLGETPROP(lnConn3,"ODBChdbc"))
?
*-- 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
SQLDISCONNECT(laHandles[n])
NEXT
RETURN
**********************************************
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)
?
RETURN
The Developer Download files for this chapter, available at www.hentzenwerke.com, 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.
|
Result |
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 |
“0” |
-1 |
|
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",
aRowsUpdated)
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:
CLOSE DATABASES ALL
SET TALK OFF
CLEAR
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.")
RETURN
ENDIF
lcConnStr = [Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=] + ;
lcDbc + [;SourceType=DBC;Exclusive=No;BackgroundFetch=No;Collate=Machine;]
lnConn = SQLSTRINGCONNECT(lcConnStr)
IF lnConn < 1
MESSAGEBOX("Could not connect to Tastrade.dbc. Reason: " + MESSAGE(), ;
16, "Sorry, could not connect")
RETURN
ENDIF
*-- Query returns 2 result sets
? "Two queries returning 2 result sets:"
TEXT TO lcCmd NOSHOW
SELECT * FROM customer WHERE country = 'USA';SELECT * FROM customer WHERE country = 'Mexico'
ENDTEXT
lnRes = SQLEXEC(lnConn, lcCmd, "tmpCust", laCount)
DISPLAY MEMORY LIKE laCount
IF lnRes < 1
? "Error in query: " + MESSAGE()
ENDIF
?
*-- One query updates records, second query returns results
? "(1) Update records for some customers, (2) Query returns customers in UK:"
CLOSE TABLES ALL
TEXT TO lcCmd NOSHOW
UPDATE customer SET min_order_amt = min_order_amt + .01 WHERE min_order_amt <= 100;SELECT * FROM customer WHERE country = 'UK'
ENDTEXT
lnRes = SQLEXEC(lnConn, lcCmd, "tmpCust", laCount)
DISPLAY MEMORY LIKE laCount
IF lnRes < 1
? "Error in query: " + MESSAGE()
ENDIF
?
*-- Query is successful but did not update any records
? "Attempt to update records but do not return a result set, no records are updated:"
CLOSE TABLES ALL
TEXT TO lcCmd NOSHOW
UPDATE customer SET min_order_amt = 1000 WHERE country = 'LalaLand'
ENDTEXT
lnRes = SQLEXEC(lnConn, lcCmd, "tmpCust", laCount)
DISPLAY MEMORY LIKE laCount
IF lnRes < 1
? "Error in query: " + MESSAGE()
ENDIF
?
*-- Query failed; no column named 'countryname'
? "Query fails due to erroneous column name:"
CLOSE TABLES ALL
TEXT TO lcCmd NOSHOW
SELECT * FROM customer WHERE countryname = 'UK'
ENDTEXT
lnRes = SQLEXEC(lnConn, lcCmd, "tmpCust", laCount)
DISPLAY MEMORY LIKE laCount
SQLDISCONNECT(lnConn)
The Developer Download files for this chapter, available at www.hentzenwerke.com, 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
IF RECNO() = RECCOUNT()
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
TRY
SQLDISCONNECT(n)
CATCH
ENDTRY
NEXT
ASQLHANDLES()
provides a more precise and much faster solution:
lnHandles = SQLHANDLES(laHnd)
FOR n = 1 TO lnHandles
SQLDISCONNECT(laHnd[n])
NEXT
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'")
IF loRs.EOF
loRs.Bookmark = loBookmark
ENDIF
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.
|
Value |
Foxpro.H
constant |
When an update
conflict occurs |
|
1 |
DB_KEY |
If a key field has
been changed in the local cursor |
|
2 |
DB_KEYANDUPDATABLE |
If any of the fields
marked as updatable have been changed in the remote table |
|
3 |
DB_KEYANDMODIFIED |
If any of the fields
changed in the local cursor have been changed in the remote table |
|
4 |
DB_KEYANDTIMESTAMP |
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.
|
ConflictCheckType |
Description |
|
0 |
Do not perform conflict checks. |
|
1 |
If no records are updated or deleted, generate an error 1585 (“Update conflict...”). |
|
2 |
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.’) |
|
3 |
1 + 2. In other words, make sure one and only one record is affected by the update or delete command. |
|
4 |
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.”
Auto-refresh
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.
The Developer Download files for this chapter, available at www.hentzenwerke.com, 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.
PUBLIC goCA
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")
WITH goCA
.ALIAS = [cursor1]
.DATASOURCETYPE = [ODBC]
.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]
.USECURSORSCHEMA = .T.
.SENDUPDATES = .F.
.REFRESHCMD = [select CompanyName, UPPER(Country), Fax, Phone from ] ;
+[Customers where CustomerID = ?EVAL(this.RefreshAlias + ".CustomerID")]
.REFRESHCMDDATASOURCETYPE = [ODBC]
.REFRESHIGNOREFIELDLIST = [CustomerID]
ENDWITH
*-- Open a shared connection to the database
CLEAR
? "Attempting to connect to the SQL Server Northwind database"
lnConn = SQLSTRINGCONNECT(lcConnStr, .T.)
IF goCA.DATASOURCE < 1
MESSAGEBOX(MESSAGE())
RELEASE goCA
RETURN
ENDIF
*-- Set the DataSource and the RefreshCmdDataSource to this connection
goCA.DATASOURCE = lnConn
goCA.RefreshCmdDataSource = lnConn
*-- Fill the cursor and browse it
IF ! goCA.CURSORFILL()
MESSAGEBOX(MESSAGE())
RELEASE goCA
RETURN
ENDIF
SELECT cursor1
BROWSE NOWAIT
*-- 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")
goCA.RECORDREFRESH(5,4)
*-- Remove the "*" from each customer
SQLEXEC(lnConn, "UPDATE customers SET CompanyName = " ;
+ "REPLACE(CompanyName, '*', '')")
SQLDISCONNECT(lnConn)
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.
The Developer Download files for this chapter, av