Browse DevX
Sign up for e-mail newsletters from DevX


Introducing the CursorAdapter Class : Page 5

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

As a side note, you have now seen one way to parameterize the commands for a CursorAdapter class. In essence, every event that occurs in the class has a set of Before and After methods, such as BeforeUpdate and AfterUpdate. However, there is no BeforeSelect or AfterSelect—instead, these are called BeforeCursorFill and AfterCursorFill, since the cursor is filled with the result of the SelectCmd.

The BeforeCursorFill method receives three parameters, and expects a Boolean return value. The first parameter, lUseCursorSchema, specifies whether the CursorSchema property controls the construction of the resultant cursor or not. The second parameter, lNoDataOnLoad, is similar to the NODATA clause on views, where the schema is retrieved but no data is actually passed from the data source.

For the current discussion, the third parameter, cSelectCmd, is of primary interest. It is also passed by reference (like the cUpdateInsertCmd parameter of BeforeUpdate) and is initially populated with the current setting of SelectCmd. However, if you change the value of this parameter, it does not change the value of the SelectCmd property; instead, it modifies what is passed to the data source, for as long as the object exists.

For example, imagine that you have set a CursorAdapter object's SelectCmd to the following statement:

   SELECT CustomerID, CompanyName, ContactName, City,
   Region, Country FROM Customers
Upon calling the CursorFill method of the CursorAdapter, the cSelectCmd parameter of the BeforeCursorFill method would contain this value. Now imagine that you have the following code in this method:

   cSelectCmd = cSelectCmd + ;
     " WHERE CompanyName LIKE '" + ;
     this.cCompanyName + "%'"
This would cause the actual Select command to always contain the WHERE clause as specified by the code and the current value of this.cCompanyName (a user-defined property). And since it doesn't modify the original value of SelectCmd, you don't have to include any special coding to ensure that you don't get two WHERE clauses in the submitted select command.

Parameterization, Part II
If you have used views or SQL Pass Through in the past, then you are probably familiar with parameterization by using the "?" character in front of a variable. As you might suspect, this feature still works in the CursorAdapter. The following example code shows how you can use a parameter in the SelectCmd property of a CursorAdapter:

   This.SelectCmd = "SELECT * FROM Customers " + ;
     " WHERE CompanyName like ?lcMyVar "
   lcMyVar = 'C%'
It is critical to ensure that the variable "lcMyVar" is populated before the CursorFill method is invoked. Otherwise, you are prompted for the value by VFP, something a user should never see.

You can also use a property of the CursorAdapter as the parameter, instead of a local variable. The advantage, of course, is that the property will persist as long as the object does, and you could even provide a set of Access/Assign methods to ensure the assigned value meets certain criteria.

Using Stored Procedures
Above, it was suggested that using stored procedures would be a good way to get around the limitations of handling errors. With that in mind, let's explore the approach of using stored procedures with an ODBC-based CursorAdapter so we can get a feel for how much work is involved in manually handling the updates for a CursorAdapter class.

VFP will automatically convert the ADO RecordSet into a VFP cursor for us, and will also handle the updating.
Essentially, this section is all about replacing the automatic generation of Update, Insert, and Delete commands with calls to stored procedures on the data source. This means that you'll be dealing with the UpdateCmd, InsertCmd, and DeleteCmd properties, and assumes that the Northwind database on your SQL Server already has stored procedures in place for performing these functions (they are not provided in the sample database).

As an example, let's take a look at the complete code for a simplified stored procedure you can use to update the ContactName field in the Customer table for the Northwind database:

   --T-SQL code, not VFP
   CREATE PROCEDURE UpdateCustomerContact (
      @CustomerID nchar (5),
      @ContactName nvarchar (30),
         @oldContact nvarchar (30)
     IF @CustomeriD IS NULL 
       RAISERROR('CustomerID is a required parameter',16,1)
       UPDATE Customers 
          SET ContactName = @contactName
        WHERE CustomerID  = @customerID
          AND ContactName = @oldContact
To save space, this procedure is lacking the full error handling code that you would normally include. Regardless, there is enough code here to illustrate how to perform an update with the CursorAdapter class.

Fortunately, establishing the UpdateCustomerContact procedure as the Update command is as easy as overriding the BeforeUpdate method with the following code:

   LPARAMETERS cFldState, lForce, nUpdateType, ;
     cUpdateInsertCmd, cDeleteCmd
   cUpdateInsertCmd = ;
     "EXECUTE UpdateCustomerContact '" + ;
     EVALUATE(this.Alias+".CustomerID") + "','" +;
     ALLTRIM(EVALUATE(this.Alias+'.ContactName'))+ ;
       "','" + ;
Here, the code populates the cUpdateInsertCmd parameter, in effect overriding the default Update command. I use the Evaluate function so the cursor name will be dynamic, assuming that the cursor name could easily be changed but the code may not. Also, I use the OLDVAL function to retrieve the value the ContactName field had before it was modified. This is critical to the procedure call as it expects the old value in the Where clause, much like the automatically generated Update statement.

Remember that the BeforeUpdate method is invoked automatically for us by a TableUpdate call just before the record is actually updated. Therefore, no matter what the current value is for UpdateCmd, this method overrides that to always use the stored procedure.

Note that you could also use the parameterization discussed earlier, instead of overriding the BeforeUpdate method. This would still require you to provide the UpdateCmd on the CursorAdapter, but, instead of hard-coding the parameters you would use variables or properties and precede them with question marks.

An important point to make here is that the cUpdateInsertCmd (or the object's UpdateCmd) cannot return a value. More accurately, if you return a value from the stored procedure, it doesn't have anywhere to "go," and the value is always lost. Therefore, it is critical that you add the appropriate RaisError calls in the stored procedure to have your code respond to any errors that may occur during the update (such as bad parameters or an update conflict). You would catch the error by testing the return value of TableUpdate, calling AError, and then analyzing the error array.

Similar code should also be written for the BeforeInsert and BeforeDelete methods, so that they also call stored procedures instead of ad-hoc queries. For the sake of space, I'll leave that code as "an exercise for the reader."

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