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 AfterSelectinstead, 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
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
, 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)
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 = ;
"EXECUTE UpdateCustomerContact '" + ;
EVALUATE(this.Alias+".CustomerID") + "','" +;
"','" + ;
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
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."