Browse DevX
Sign up for e-mail newsletters from DevX


Introducing the CursorAdapter Class : Page 4

One of the most exciting new features of Visual FoxPro 8 is the CursorAdapter class, which provides a common interface for working with data from many different sources. Chuck takes you with him on an adventure in exploring how to use CursorAdapter to change the way you relate to data in VFP 8, whether native tables, ODBC, OLE DB, or XML.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

CursorAdapter with ODBC

You can use TableUpdate() to send the changes to the base table, and VFP automatically builds the necessary Update, Insert or Delete statements to carry out the modifications.
Now that you've seen the basics, let's move forward to see how things change when using SQL Server as the back end instead of VFP. We'll start with using the ODBC driver from VFP to access the Northwind database on SQL Server. Also, let's build this CursorAdapter "from scratch" so that every aspect of the class is visible.

First, create a new class in the class designer with the following command:

   CREATE CLASS caODBC OF tests as CursorAdapter
The most important property to set at this point is the DataSourceType property. Since we're attempting to connect to SQL Server via ODBC, set this property to ODBC. When set in this fashion, the DataSource property expects a valid connection handle, which can be created through the SQLConnect or SQLConnectString functions. In either case, these functions should be invoked through the Init method of the CursorAdapter class using the following code:

   LOCAL lcConnStr, lnConn 
   ** string assumes trusted connection (integrated security)
   lcConnStr = "Driver=SQL Server;Server=(local);DATABASE=Northwind"
   lnConn = SQLSTRINGCONNECT(lcConnStr)
   IF lnConn > 0 THEN
     ** unable to connect
The connection string assumes that you are using a trusted connection to SQL Server; if you are using SQL Server security instead, add the "uid=" and "pwd=" strings to the connection string to specify the username and password for the connection. In either case, the return value is the connection handle, or a negative value if an error occurred. This connection handle is assigned to the DataSource property so that the CursorAdapter knows where to pass statements.

The next step is to build a SelectCmd so that the CursorAdapter knows what data it is acquiring from the data source. The best place to do this is also in the Init method, since the property sheet does have limitations on how long a string you can provide. Add the following line to the Init method, after the code that sets the DataSource property, to retrieve the list of Customers that have a Company Name that starts with "C":

   This.SelectCmd = "SELECT " + ;
     "CustomerID, CompanyName, ContactName, " + ;
     "Address, City, Region, Country " + ;
     "FROM Customers WHERE CompanyName LIKE 'C%'"
Next, you need to tell the CursorAdapter to fill the associated cursor with a call to the CursorFill method. You could leave out this call and invoke it manually from outside of the class, or place it in the Init method so it automatically fills upon instantiation. Simply call This.CursorFill() after the SelectCmd is populated in the Init method.

Finally, you should add a bit of code to the Destroy method of the class, to drop the server connection once the object is removed from memory. Without this code, every new instance will create a new connection to the server, and never release it:

   IF this.DataSource > 0 THEN 
With these changes, you have a functional CursorAdapter class that produces a non-updatable cursor. Still, it may be a good time to test the class, to ensure that it can be instantiated and that it retrieves data properly, before allowing it to be updatable. Test it with the following code:

   lo = NEWOBJECT("caODBC","tests")
Note that you didn't have to invoke an OpenTables method like you did with the Data Environment. This is because you added the CursorFill method directly to the Init method, causing the class to automatically fill the cursor upon instantiation.

Updating ODBC Data
To make this class updatable, you have to provide correct values for the Tables, KeyFieldList, UpdatableFieldList, and UpdateNameList properties. Also set the AllowInsert, AllowUpdate, and AllowDelete properties to True, to ensure that the automatic updating feature is properly activated. Once again, the best place to make these changes is through code in the Init method. The modified version of the Init method appears in Listing 2.

Before closing the class designer, you may also want to change the BufferModeOverride property to "5", "Optimistic table buffering" so automatic updates do not occur when moving the record pointer.

To test the updatability of the CursorAdapter, instantiate it, browse the cursor, make a change, and then issue TableUpdate. To ensure the changes were applied, call the CursorRefresh method of the CursorAdapter object and browse again.

Handling ODBC Errors
As with the native CursorAdapter, most errors are trappable in the "traditional" way—test the return value of TableUpdate and, in case of failure, use AError to determine the cause. Unfortunately, the detection of an update conflict is also a problem for the ODBC type CursorAdapter.

While the solution for the native CursorAdapter was to raise an error in the AfterUpdate method, this won't be as effective for the ODBC CursorAdapter since we're not expecting VFP errors, but ODBC errors, when an update fails. Therefore, the best answer is to either use a stored procedure (covered later) or add a little more code to the update statement as it is sent to the server.

Recall that the solution for the native CursorAdapter was checking _TALLY to see if any records were updated. The solution here for ODBC is similar, but we can't use _TALLY since it isn't reliably correct for remote data. Instead, we can use SQL Server's @@Rowcount system function to determine if any records were updated.

If you were writing a T-SQL batch of statements to update a record, you might write code similar to the following:

   --@custID and @oldContact set by earlier code or parameters 
   UPDATE customers 
     SET ContactName = @newContact
    WHERE CustomerID = @custID
       AND ContactName = @oldContact
   IF @@ROWCOUNT = 0 
     RAISERROR('Update failed.',16,1)
The RaisError T-SQL function causes VFP to receive an ODBC error (number 1526), passing the error message as specified in the first parameter (the other two parameters indicate the severity and state of the error). In this case, RaisError is invoked when @@Rowcount = 0, meaning that the previous T-SQL statement did not affect any records.

Where this all fits into the current discussion is that you can use the BeforeUpdate method of the CursorAdapter to modify the statement that is sent to the server on an update. While the BeforeUpdate method receives five parameters, the last two (cUpdateInsertCmd and cDeleteCmd) are interesting in that they are passed by reference. This allows you to change the commands before they are sent to the data source.

In our case, we'd like to use this method to append the test for @@Rowcount and subsequent call to RaisError. This can be done with the following code in BeforeUpdate:

   LPARAMETERS cFldState, lForce, nUpdateType, ;   
     cUpdateInsertCmd, cDeleteCmd
   IF nUpdateType = 1 THEN 
     cUpdateInsertCmd = cUpdateInsertCmd + ;
       " if @@ROWCOUNT = 0 "+ ;
       "RAISERROR('Update Failed due to update " + ;    
Now, for every row that is sent to the back end, this code will test to see if the row was updated. If not, VFP will receive the error, TableUpdate will fail, and AError will show the usual 1526 error with the message text as specified.

There are two problems with this approach. First, this is a specific fix for SQL Server; for other ODBC data sources (such as Oracle), this code will not work. Second, this error message is very generic as it always generates the same VFP error number, and makes proper error handling from VFP a bit difficult. This issue can be mitigated somewhat by creating custom error messages on the SQL Server, each with their own unique error number.

Another way to improve upon this solution is to use Stored Procedures to perform the updates instead of letting VFP build and pass an ad-hoc query to the server. Of course, the tradeoff of adopting the stored procedure approach is that you lose the benefit of having VFP automatically handle the updates.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date