Turn Your VFP App Client/Server: A 12-Step Program, Part 2

Jim Falino

Last month, Jim began the first of this two-part series on the steps you need to take to develop client/server applications with Visual FoxPro (see Part 1 in the April 1999 issue of FoxTalk). Jim raises six more important issues this month and concludes with a sample form that can access both VFP and SQL Server tables.

I have a lot of information I'd like to pass along, so please see Part 1 for an extended introduction. Let's get right to it. One reminder: All examples are from the SQL Server 7.0 Northwind Traders database.

Step 7: Use surrogate primary keys for all tables
Even before VFP 3.0 gave us the ability to add primary keys to our tables, using a field -- or group of fields -- to uniquely identify a table row had been suggested by many to be a sound practice. In file-server applications, though, in many situations there really was no requirement to do this -- it was only a good idea. However, with views, the rules change.

In order to use updateable views, there must exist a primary key on the table on which the view is based (I typically don't update data from a multi-table view). The reason is that since the view creates a local result set when opened (USE <Viewname>), you need some mechanism to be able to find the underlying record on the server that you're attempting to update. This wasn't always necessary in a file-server model, whereby a shared record could be directly edited.

In order for updates to work, views require that you either set the KeyField property at design time in the View Designer or with DBSETPROP("Viewname.KeyField1", "Field", "KeyField", .T.), or set the KeyFieldList property on the open cursor at runtime using CURSORSETPROP("KeyFieldList", <comma-delimited list of primary key fields>).

In either case, the view will use the key field(s) to find the record on the server you've requested to SQL Update or SQL Delete. The keys will also be used to detect update conflicts -- reporting back an error if anyone has either deleted the record you're trying to update or has changed the key. Aside from being a necessity for updateable views, adding primary keys to your tables also makes querying for one particular record simpler. You'll find that in the client/server world, you'll often need that ability.

The argument for surrogate keys
If you do have multiple fields that make up your primary keys, I suggest using a system-generated surrogate key in addition. Having this field, typically an integer type, will give you the luxury of a unique Row ID that you'll be glad you have. Along with this, for child tables you'll want to add one more integer field to serve as the foreign key. You'd populate it with its parent's surrogate primary key before saving a new child.

The benefits of surrogate keys include faster joins, faster updates, faster deletes, and the simplified retrieval of the children of a parent. The only drawback is generating them. I use a system table of next numbers -- it contains one row per table in the application -- but this can cause multi-user contention problems if you're not careful. Since these keys really are meaningless (that's why they're also called abstract keys), you shouldn't attempt to get the next sequential number from the server while in the midst of a transaction. This might create too much contention on the system table when you need primary keys for a highly active table. It's better to get the key outside of the transaction and risk losing it if an update fails.

One other thought might be to use one of several techniques to generate a unique ID locally -- eliminating the trip to the server and any possible multi-user contention. Much like the way I never let a contractor leave my house without recommending a good roofer, I never let a conversation with a developer end without asking about their unique ID generation technique. Two interesting ones I've heard that use client-side generation are:

1. Use a GUID. They're 26 characters long, (supposedly) unique across the world, and lightning-fast to generate. They're not beautiful to look at, but it might be well worth the pain.

2. Get a unique ID from a server-side system table on application startup, and then use a local application property as a counter. Concatenate the two to generate a unique alphanumeric string. Although I'm simplifying it a bit, I believe this technique is used in Code Book.

Step 8: Add a timestamp column to every table
Client/server applications almost exclusively use optimistic locking. Given that fact, multi-user contention checking becomes much more of a challenge to implement. You must continually remind yourself while coding, "I might not have the latest version of this record set."

Although the primary key is used to detect update conflicts, its ability is limited to detecting whether another user has either deleted the record you're trying to update or has changed the key. The primary key won't help you determine whether another user made changes to non-key fields before you committed your changes.

Typically, client/server applications use some form of timestamp column -- one that's re-stamped as part of every update -- to indicate the latest version of a record. (I say "some form of timestamp" because the datatype can be almost anything.) This column is then queried on the server during every update to determine whether it conflicts with the version on the local workstation. For example, a VFP view might generate the following SQL statement after updating the customer view:

Replace state With 'NY' For city = 'New York' In vcustomers
TableUpdate(.T., .F., "vcustomers"):

* This is what is auto-generated by the view 
* and passed to the server.
* There would be one of these per updated record.
Update customers Set state = 'NY' ;
  Where cust_pkey = ?vcustomers.cust_pkey and ;
  timestamp = ?vcustomers.timestamp

If this SQL update statement fails with an Update Conflict error, you can report to your user that someone else changed this record while you were editing it. Where you go from here depends on how nice you are. Ultimately, either a requery must occur or you can try to issue TableUpdate again with the Force parameter set to true (which isn't recommended unless you provide the user with a field-by-field comparison of the changes).

The view property -- WhereType -- is responsible for the multi-user contention check. You have four options to determine what fields are compared when the contention check portion of the SQL statement is generated. Key and Modified Fields and Key and Updatable Fields sound like powerful features, but I feel that in most cases these options are too dangerous to implement. (I wouldn't want user 1 and user 2 to update two different columns of the same record without first knowing of each other's changes.)

That leaves Key Fields Only and Key and Timestamp. Key and Timestamp is only supported if your database supports a timestamp-type column. This column is automatically re-stamped by the server on every update. The client application doesn't maintain this field -- the view will just include the column in the contention check.

SQL Server has support for a timestamp column. In fact, the SQL Server Upsizing Wizard has an option to create a timestamp column for each table. The column isn't actually a datetime datatype, but rather a system-generated unique string. The beauty of it is that the server automatically manages it and that it's much more accurate than the datetime datatype (you'll never have to worry about two users updating at the same exact time).

Trap!
Some behaviors to be aware of when using the SQL Server timestamp column:
If you don't requery your view after every save, the timestamp column in your local view cursor won't be refreshed with the current value on the server. Thus, subsequent attempts to save the same record will fail with an Update Conflict error when the two timestamps are compared.

Tip!
Workarounds to the shortcomings of the Upsizing Wizard:
The SQL Server Upsizing Wizard has many problems that make it, in my opinion, almost unusable. Most people I know have had to write their own tools to get around the issues. The one saving grace is that the source code for all of the wizards and builders now ships with version 6.0 of VFP. It's really not that tough to hack your way through your stumbling blocks. The source code is located in \<location of VFP 6.0>\Tools\Xsource\Xsource.zip.

So Key and Timestamp sounds great for SQL Server, but what about other back ends that don't support a timestamp column? And how can my prototype with local views against a VFP database work with the same set of code? My workaround is to add your own timestamp column and maintain it from either the client-side or a server-side update trigger. You then make the field a KeyField (see Step 7) and use the Key Fields Only WhereType. So your KeyFieldList might be cust_pkey and timestamp. (Note that the fields in the KeyField or KeyFieldList property of a view don't actually have to be primary or candidate keys.)

You can use a datetime-type column for this purpose. However, the VFP datetime column is only precise to a second, so conceivably two users can update the same record within the same second. If you feel that this is a potential problem for your application, use some other technique to generate a unique number or string. I used this workaround until I upsized to SQL Server and included its timestamp-type column. (Since you have no control over this field name, you might want to call your VFP timestamp column something different so there's no conflict when you upsize.) Then you can just change the WhereType to Key and Timestamp, and you're done.

Step 9: Life without dates
It was a bit unsettling -- okay, maybe more like a sudden jolt -- to find out that SQL Server, like most big-time databases, doesn't support a date datatype. You must use a datetime-type field instead. If you're converting an existing VFP application that uses date fields, the problems this causes could be quite extensive. It really all depends on how your application uses dates and how much you care about seeing 12:00:00 AM tacked onto the end of your date fields. I've summarized the issues I've been confronted with as well as their possible workarounds.

Controls bound to date fields
In cases where showing the time portion of a datetime field is just plain inappropriate, you obviously have to perform some sort of trickery to lose the default time of 12:00:00 AM. You have several options. You could use DBSetProp to change the datatype of the view definition from datetime to date. (As mentioned in Part 1, having a local DBC of views eliminates any multi-user contention issues.)

Create SQL View vOrder Remote Connection ;
   Remote1 As Select * from orders
DBSetProp("vOrders.orderdate", ;
   "Field", "DataType", "D(8)")
Use vOrders

Doing so will not only truncate the time portion, but also provide you with a VFP date type field in your view -- enabling your date-specific code to work unchanged. And fortunately, when you issue TableUpdate on a view with a date-type field in it, a default time of 12:00:00:000 AM is automatically appended to it on SQL Server . . . without error.

If you have controls that aren't bound to a view but rather to a read-only SQL Passthrough (SPT) cursor, you can use the back end's data type conversion function to do the truncation. Here's a SQL statement that could be passed to SQL Server that would do just that:

SELECT orderid, ;
  customerid, ;
  CONVERT(char(10), orderdate, 101) as orderdate ;
  from orders

Note that 101 refers to the optional style argument of the CONVERT function. 1 indicates American format -- mm/dd/yy. You then add 100 if you want the century included.

To make the CONVERT function work against local data as well, you might want to create a stored procedure called "Convert" in the database of views. Have it accept the three arguments of SQL Server's CONVERT function and, if passed a datetime-type, return SubStr(TToC(pDateTimeField),1,10). You'd also need to write a Char stored procedure as well to handle the SQL Server function datatype, char(10).

Datetime fields can't be "empty"
As if losing date fields wasn't bad enough, you also must contend with the fact that datetime fields can't be empty on most databases. (There's a whole world of limited functionality beyond FoxPro, isn't there?!) Datetime columns must be populated by a valid date and time or be Null.

If you allow your datetime fields to accept Null, it quickly solves the user-interface problem but probably generates others. You could experience unexpected results by blindly introducing Nulls. (For example, comparing two dates where one is Null returns Null, Empty(NullDate) returns .F., and so on.) But if you're prepared to tackle these issues, Nulls really come in handy when presenting data to users because of VFP's Set NullDisplay To environment command, and the NullDisplay property of controls like the text box.

If you don't want to use Nulls, VFP and SQL Server are actually pretty kind to you. Sending an empty date to SQL Server causes no error. However, SQL Server will create a default datetime of 01/01/1900 12:00:000 AM. Now when you query that data, you'll see 01/01/1900 on the local cursor -- assuming you've used one of the techniques discussed earlier for truncating the time. I don't feel that that's such a horrible thing for a user to see, but if you've chosen to Set Century Off, 01/01/00 will look like January 1, 2000. (As if Y2K bugs weren't bad enough, upsizing will create a Y1.9K bug!)

I've come up with three possible workarounds. The first is to add a column default to all datetime fields -- one that will make it very clear that this is a bogus date -- like 01/01/9999. (Note that the datetime datatype has a date range from January 1, 1753, to December 31, 9999.) Again, you'd need Set Century On to differentiate it from 1999. A similar option is to add a column default to all datetime fields that follows a business rule. Perhaps all Ship Dates can default to eight weeks after the Order Date, a required field.

The third option involves some more sleight-of-hand. Wouldn't it be great if there were a way to clear out each of the dates equal to {01/01/1900} in the open view cursor, send no updates to the server, and leave no pending changes? Well, it can be done, and it goes a little something like this:

Procedure OpenView
LParameters pcView
* Retrieve data form server
Use (pcView) in 0
* Prevent update statements from going to server
CursorSetProp('SendUpdates', .F., pcView)
* Strip 01/01/1900 out of this cursor
RemoveDefaultDates(pcView)
* Remove pending changes/clear the change buffer
TableUpdate(.T., .T., pcView)
* Restore update capability to view
CursorSetProp('SendUpdates', .T., pcView)

For brevity, I won't provide code for function RemoveDefaultDates. But all it does is loop through the columns for date-type fields and then replace them with {} if they equal {01/01/1900}. Now all data entry screens will look and work as they did with a VFP back end.

Well, that works for views, but what about SPT cursors? You'd just need to run the function RemoveDefaultDates against any cursor that will be eventually presented to the user. (Hopefully, you've built a report printing class.)

Tip!
Differences between remote SPT cursors and local SPT cursors:
SQL Passthrough cursors are read-only when created from file-server data sources, but read/write when created from a client/server data source. So, if you're prototyping locally, you'll of course need to first make the cursor read/write before changing it.

Date math
You need to be aware of any date math you might have in your application. Being that you have datetime fields on the server (and might have them on a local cursor too) where you originally had date fields, errors in calculations could occur. For example:

?Date() + 20      && 03/08/99 + 20 = 03/28/99
?Datetime() + 20  && 03/08/99 10:35:15 PM + 20 = ;
                    && 03/08/99 10:35:35 PM!
 * against SQL Server: 
 * ExpectedDate is 14 DAYS after order date
Select orderdate, ;
  Orderdate + 14 as ExpectedDate ;
  From Orders

 * against VFP: 
 * ExpectedDate is 14 SECONDS after order date
Select orderdate, ;
  Orderdate + 14 as ExpectedDate ;
  From Orders

You can see that adding a number to a datetime field can have different results based on the back end. It's tough to tell whether seconds or days will be added. A workaround might be to make the incrementing number a function. That way, you have the necessary hook to determine the back end and provide the appropriate calculation.

Step 10: Avoiding that Empty() feeling about Nulls
If you're like me, perhaps you've taken advantage of the flexibility that the VFP Empty() function provides. Empty(eExpression) works for any data type, except object. So you could trap for 0, or an empty string, or an empty date, or logical false with this one function without even checking for the datatype. Bad move.

Problems can occur because after upsizing, you might find some unexpected return values from functions and queries. They might even be of a different data type than you expect. When these values are evaluated with Empty() -- or any function, for that matter -- it could cause major problems. For example:

?Empty({})            && true
?Empty({01/01/1900})  && false 
?Empty(Null)          && false

You've seen the {01/01/1900} issue in Step 9 -- life without dates, so you know it can occur. Clearing them out locally seems to be the easiest workaround. But what about the Null problem? Most know that Null is Not Empty, but did you know that even if you have no columns in your database that accept Null, you still might get Nulls back from the server? Try this on SQL Server:

Function GetMaxOrderQty
lcSQL = "SELECT MAX(Quantity) AS nMaxQty" +;
" FROM [Order Details] " +;
" WHERE ProductID = 99 " 
SQLExec(lcSQL, "cBigOrder")
If Reccount("cBigOrder") > 0
  lnRetval = 0
Else
  lnRetval = cBigOrder.nMaxQty
Endif
Return lnRetval

What's the datatype of the return value? It depends. If there are any records found for ProductdID = 99, the answer is numeric. If there are no records found for ProductdID = 99, the answer is Null -- despite the fact that the Quantity column doesn't accept Nulls.

The reason is because in SPT queries to SQL Server, you won't get a result cursor without records for aggregate queries such as these. You'll get one record regardless of whether any matches are found, unless you have a Group By clause on a non-aggregate column. And the aggregate column(s) will have a value of Null. That could really sting you in many places if you're not careful to avoid it.

In the preceding example, lnRetval would be Null because there's no ProductID = 99. To avoid getting Null return values, you have several choices. Of course, finding all of the aggregate functions in your code and doing an IsNull() check is one way. You could also write a wrapper for Empty, perhaps IsEmpty, that traps for Null, and 01/01/1900 as well. This can be used to check the values of different data types without the fear of encountering unexpected results.

Lastly, adding the Count() function, as in Count(*) as Cnt, to all queries such as these gives you a common way to check for a return value. The Cnt column will always be numeric, so now you can check for Cnt > 0 instead of Reccount() > 0 without fear of Nulls.

Step 11: The case for unbound controls
You've seen a lot of shortcomings to working with fields that are of different data types -- depending on the back end. I see them as well. These problems make it very difficult to write one set of code to access multiple data sources. Obviously, not all applications require such flexibility, but for those that either need or would like this functionality, unbound controls might be the answer.

Unbound controls on a form don't have a datatype. You take the result set of a query and essentially "paint" the controls with the values of the field contents. That would give you the necessary hooks to manipulate the data any way you need to before presenting it to the user.

This provides great flexibility, but a ton of coding. Can you just imagine how much code it would take to read the data, loop through the controls, and paint the form, then loop through the controls again to create the SQL code that will write the updates to the server? Not to mention having to add the field-level, data type validation that you get for free with a bound control. The average FoxPro developer is so used to bound controls that he or she would rather chew glass than write all of that tedious code. There must be a better way.

If I had to live my life over again, I'd change only one thing: I'd have created yet another layer of data abstraction. (Okay, two things: I also would have bought Microsoft stock 10 years ago. Who knew?) The extra layer would be implemented in the form of a read/write cursor with the same structure and content of the view's result set.

Controls can then still be bound to a data source, while at the same time you have the ability to manipulate the cursor in any way before presenting it. So the process would go something like this:

Procedure Load
* For this test, open the form with data.
Use vOrders In 0
* Get a copy of the result set.
Select * from vOrders Into Cursor cOrders1 NoFilter
* Make a read-write copy of cOrders1.
Use (DBF()) In 0 Again Alias cOrders 
* Close the temporary read-only cursor.
Use In cOrders1
* 
* Now the Init of the controls fire, 
* which are each bound to cOrders.
* cOrders will be the form's master alias.
EndProc

Now just edit the form as usual. On the Save, dump the edited cursor back into the view and issue TableUpdate against it. The entire view-based framework discussed earlier would all still apply -- you're just slipping in one additional layer. And with small result sets -- a client/server requirement -- coupled with the lightning-fast VFP data engine, performance shouldn't be an issue.

By providing yourself with this opportunity to manipulate the data before presenting it, you get the best of bound and unbound controls. This will make possible the addition of new features where the underlying data doesn't match what the user sees -- for instance, multi-currency, multi-language, and character representations of data like 4 DOZ (four dozen).

Step 12: Creating a form that accesses multiple back ends
I've provided a form that's indicative of how you could design a form that needs to access multiple data sources. It's rather down and dirty -- with all instance-level code -- for ease of learning, but you could always enhance it once you understand the design. Also for simplicity, I've created only one filter field and haven't implemented Query by Form.

Before you can run the form, do the following: Extract the files and subfolders in the accompanying Download file to any directory. You'll find the form Orders, the bmps to support the form, and main.prg. The subdirectory AppData contains a copy of the Northwind Traders database in VFP format. (I used the SQL Server Data Transformation Wizard to do this.) The SSViews directory contains a remote view, vOrders, in a DBC called AppViews. Similarly, the VFPViews directory contains a local view, vOrders, in a DBC called AppViews. (Refer to Step 5 in last month's article for more on the file and directory layout.)

Run VFP version 6.0 and set the default to the directory where you extracted the files. Have SQL Server 7.0 installed and running. In the Control Panel, create an ODBC User DSN that connects to the Northwind Traders database and call it NORTHWIND_SS7.

To run the form against SQL Server, issue: MAIN("NORTHWIND_SS7"). To run the form against a the VFP back end, issue: MAIN("NORTHWIND_VFP"). As you can see from Figure 1, the title bar indicates the data source. Choose Find, enter a customer like "VINET," and then choose Retrieve. The five orders for this customer will be retrieved from the server.

Don't get too excited to see some magical code, because there really isn't any. In fact, it's not only simple, but almost the identical code you'd see in a form bound to buffered tables. The only differences are the addition of the NoData clause of the Use command in the Load, the setting of the cCustomer private variable that serves as the view parameter, and the subsequent Requery function call that will retrieve the data for the appropriate customer. These are the only two procedures of even remote interest (pun highly calculated):

PROCEDURE Load
* Important for local views
Set Exclusive Off
* Need this for table buffering
Set MultiLocks On
Set Data To AppViews
* No data on load, please
Use vOrders NoData
* Set optimistic table buffering
CursorSetProp("Buffering", 5)
ENDPROC

PROCEDURE cmdfind.Click
PRIVATE cCustomerID
cCustomerID = ""
IF Thisform.PendingChanges()
  WITH Thisform
    .Lockscreen = .T.
    IF Not Thisform.lFindMode
      thisform.lFindMode = .T.
      * Change to find mode
      this.Caption = "\<Retrieve"
      .SetAll("Enabled", .F., "textbox")
      .SetAll("Enabled", .F., "_commandbutton")
      .txtCustomerID.Enabled = .T.
      .txtCustomerID.Setfocus()
    ELSE	&& In find mode, so Retrieve
      .SetAll("Enabled", .T., "textbox")
      .SetAll("Enabled", .T., "_commandbutton")
      this.Caption = "\<Find"
      .txtCustomerID.Enabled = .F.
      cCustomerID = .txtCustomerID.Value
      Requery()
      thisform.lFindMode = .F.
    ENDIF
    .cmdfind.Enabled = .T.
    .txtOrderID.Enabled = .F.
    .Refresh()
    .Lockscreen = .F.
  ENDWITH
ENDIF
ENDPROC

Acknowledgments
I've leaned on a lot of people during this VFP to SQL Server migration -- you will, too. So I'd like to thank the following for their contributions: Brent Vollrath and Terry Weiss of Micro Endeavors; Richard Berry, Phu Luong, and the rest of the Visual Garpac Development Team; Roger Nettler and Stan York of Programmed Solutions, Inc.; and Bill Martiner, an independent consultant and a SQL Server guru.

Conclusion
I hope you won't let all of these issues deter you from migrating to SQL Server. Yes, it's a lot of work. But it's more tedious than it is complicated. I hope this article saves you a lot of time by making you aware of many of the issues before you even begin.

As far as having one set of code access a VFP database as well as client/server databases, I don't recommend it for the long term. There's a little too much conditional code required and duplication of tools that must be written to warrant it. In the end, you'll probably end up with a system that's inefficient against all back ends, although you might get away with it if you throw enough hardware at the situation. I'd use local views only if I were definitely planning to upsize to SQL Server, Oracle, and so on. After the prototyping stage, I recommend that you gradually migrate your code to work remotely only.

SQL Server 7.0's ability to work on Windows 95/98 and NT and a host of new features make moving from VFP a much more seamless process. It can easily serve as your only back end, while using the VFP data engine for crunching data brought from the server. Together, they make a perfect team.

I welcome hearing from you about your experiences, trials, workarounds, and successes. Good luck.

Jim Falino has been happily developing in the Fox products since 1991, beginning with FoxBASE+. He's a Microsoft Certified Professional in Visual FoxPro and the vice president of the Professional Association of Database Developers (PADD) of the New York Metro Area. For the past three years he has been a project manager, leading the development of a very large client/server apparel-manufacturing application for the GARPAC Corporation using Visual FoxPro as a front end to any ODBC-compliant SQL back end. jim@garpac.com.