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

 Function SafeSkip 
 LParameters cAlias 
 llMoved = .F. 
 * Avoid that "EOF encountered" error 
 If !EOF(cAlias) 
   Skip In (cAlias) 
   If EOF(cAlias) 
     * Moved to EOF, retreat 
     Skip ­1 In (cAlias) 
   Else 
     llMoved = .T. 
   Endif	 
 Endif 
 Return llMoved 

• It creates a single point of maintenance if a change needs to be made.
• The same code can access multiple data sources.
• You can begin coding a client/server application using a VFP back end until a decision has been made on using MS SQL Server, or Oracle, or Informix, or Sybase, or DB/2, and so on.

Step 1. View-based data entry forms
When it comes to data entry forms for a VFP client/server application, you can use either SQL Pass-through (SPT), which is simply passing a SQL string through ODBC to the data source, or remote views, meaning DBC-stored SQL statements whose resultant temporary tables act much like native VFP tables. SPT will store its result set in a local cursor, so you'd have to then loop through the controls on a form and bind each of them individually (for instance, thisform.txtCustomer.Value = SQLResult.customer). Of course, updating the server would require you to create the appropriate SQL Update, Insert, and Delete statements yourself, not to mention the multi-user contention checking that must be coded as well -- a rather tedious, error-prone task. This is the way things are typically done in Visual Basic (so you know there must be a better way with VFP <g>).

Remote views, on the other hand, are incredibly simple to work with. All you need to do is create a view with the same structure as the underlying table, open the view (perform the SQL Select), manipulate the local temporary table it creates in the same way you would a native FoxPro table, then issue TableUpdate. TableUpdate automatically creates the SQL Update, SQL Insert, and SQL Delete statements for you and passes them to the ODBC driver manager on your workstation. The ODBC driver manager uses the ODBC driver you've chosen to translate the SQL into a syntax that the back end understands. It's that simple. I recommend using remote views for data entry forms for the following reasons:

• One code set. The same set of code can work against VFP tables or remote tables on a SQL Server. You'll just use local views or remote views accordingly.
• Performance. In many ways, views can be faster than SPT because TableUpdate (a low-level VFP function) automatically creates the SPT code for you. The time it would take to create the SQL update strings yourself and execute the code would most likely be longer than TableUpdate. If you think about how much TableUpdate actually does -- scanning the change buffer, determining the type of update made, reading view properties, building the batch of SQL statements, passing them to the ODBC driver manager, returning a success/failure flag, and clearing the change buffer -- I'm sure you'll agree that it can outperform SPT.
• All of the above can be done without a syntax error.
• Properties of views offer additional functionality over SPT. For example, to simulate a BatchUpdate count of five, you'd need to concatenate five SQL statements with semicolons before passing them to ODBC.

SPT does still have a very important place on a client/server application; I'll discuss it more in Step 3. For more information on the basic usage of local and remote views, refer to the VFP Developer's Guide.

Step 2. All data-related commands should funnel through one function
About the worst thing you can do to make converting your application to a client/server design difficult is to hard-code data access commands like SQL statements, Zap, Pack, Reindex, Seek, and so forth, within your code. In other words, does your code look like the following?

Function PurgeOrders * This function purges (deletes) orders shipped * prior to the date passed. LParameter dShipDate Local lcMsg Select Count(*) as OrderCnt from Orders ; Where ShipDate <= dShipDate Into Cursor tcPurge If _Tally = 0 lcMsg = "There are no orders to purge." Else Delete from Orders Where ShipDate <= dShipDate lcMsg = Trim(Str(_Tally)) + ; " orders were purged from the system." Endif MessageBox(lcMsg) EndFunc

These kinds of SQL statements are only good to access tables that can be found along your path. In the client/server world, you communicate with a data source via a connection handle. For views, the Remote Connection <ConnectionName | DataSourceName> clause of the Create SQL View command enables a view to access remote data. And for in-line SQL, the FoxPro function SQLExec() is used to pass SQL statements through to the server via ODBC. It returns -1 for an error, 1 for success, and 0 if an asynchronous query has yet to complete. As an example, here's how to get a cursor of sales orders for a given customer using SQL Pass-through:

llSuccess = SQLExec(goEnv.nHandle, ; "Select * From ORDERS Where Customer = ?cCustomer",; "tcOrders") > 0

If you'd employed a wrapper function for the SQL Select statements in PurgeOrders, then you could conditionally run it against data on a SQL Server, data on a file server, or even data on the local workstation. You should create classes of just such functions to make data access back-end-independent. Here's Function PurgeOrders rewritten in a client/server fashion:

Function PurgeOrders * This function purges (deletes) orders shipped * prior to the date passed. LParameter dShipDate Local lcSQLStr lcSQLStr = "Select Count(*) as OrderCnt " + ; "from Orders " + ; "Where ShipDate <= ?dShipDate" This.SQLExecute(lcSQLStr, "tcPurge") If Reccount("tcPurge") = 0 lcMsg = "There are no orders to purge." Else lcSQLStr = "Delete from Orders ; Where ShipDate <= ?dShipDate" This.SQLExecute (lcSQLStr) lcMsg = Trim(Str(Reccount("tcPurge"))) ; + " orders were purged from the system." Endif MessageBox(lcMsg) EndFunc Function SQLExecute * Wrapper for VFP function SQLExec LParameters cExecuteStr, cCursor Local llSuccess cCursor = Iif(PCount() = 1, "SQLResult", cCursor) llSuccess = .T. If goEnv.lRemote llSuccess = (SQLExec(goEnv.nHandle, ; cExecuteStr, cCursor) > 0) Else && local, just macro expand cExecuteStr * Add VFP "Into Cursor..." clause If Upper(Left(cExecuteStr,6)) = "SELECT" cExecuteStr = cExecuteStr + ; " Into Cursor " + cCursor + " NoFilter" Endif * This should be error trapped to return llSuccess &cExecuteStr Endif Return llSuccess EndFunc

You'll note the use of Reccount() vs. _Tally. SPT cursors don't update _TALLY, so you'll have to forget using this pretty cool system variable. However, you won't have to worry about Reccount() containing deleted records, since remote SQL data sources don't have a two-stage delete. And if the back end was a VFP database, the NoFilter clause would prevent VFP from creating a filter as opposed to a temporary table.

The second worst thing you can do to make converting your application to a client/server design difficult is to embed VFP functions in your SQL Selects. However, if you have a wrapper like SQLExecute, you have an opportunity to parse out the SQL statement before it executes and convert it to the back-end-specific syntax. For instance, the SQL Server function Convert() is used to convert data types. So, you could probably imagine what the code would look like to find "Str(" or "Val(" in a string and StrTran() the Convert function in its place. One last option is to remove the embedded function and make the SQL statement back-end-independent, then use the VFP-specific function on the local cursor.

Step 3. Use stored procedures or parameterized queries
In terms of execution speed, there's nothing faster than a stored procedure (SP). Since they're precompiled and live in the same building as the tables, what could be more efficient? The way that parameters need to be passed to back ends is often different, so, again, a wrapper function comes in handy here. For example, notice the different syntax between VFP and SQL Server:

SQLExec("usp_GetOrders('Acme')") && VFP SQLExec("usp_GetOrders 'Acme'") && SQL Server

All you'd need to do to solve this is enhance the SQLExecute wrapper function to handle stored procedures. (I'll leave that manageable task to you.)

If you don't want to go the stored procedure route, there's always parameterized queries stored in business objects. With parameterized queries, you wouldn't have to worry about back-end-specific stored procedure call syntax, nor would you have to worry about rewriting the stored procedures for each data source. The hit you take in performance might be worth the boost in maintainability.

You've already seen what a parameterized query looks like -- it's just an SPT statement:

llSuccess = SQLExec(goEnv.nHandle, ; "Select * From Orders Where Customer ; = ?cCustomer", "tcOrders") > 0

Notice the syntax of the filter condition: Customer = ?cCustomer. That syntax is important because: 1) most back ends support it; and 2) it prevents you from having to build the parameters of the SQL statement into a back-end-specific character string:

Case lSQLServer "...Customer = '" + cCustomer + "' And ; shipdate <= '" + DTOS(dShipdate) + "'" Case VFP "...Customer = '" + cCustomer + "' And ; shipdate <= {" + DTOS(dShipdate) + "}"

Another benefit is that you won't run into the dreaded "apostrophe bug." It occurs when you try to build a SQL statement like the preceding one, but the variable (in this case, cCustomer ) has an apostrophe in it (for instance, "Jim's"). Since its value is evaluated before the string is sent to the server, you'll end up with mismatched quotes and an error at execution time. As you can see, the use of the "?" can really tighten your code, so I highly recommend its usage.

For the preceding reasons, I recommend SPT instead of views whenever you're creating an ad hoc query and/or don't intend to update the data source. Many people create views for reporting purposes, but for a large system, I can't see incurring the overhead of using a view when a stored procedure or SPT SELECT statement can be deployed instead. (Unless you wanted to expose a server-side view to your users.) The client-side DBC of views can get quite bloated with data it doesn't need to maintain, since there are no view properties that really need to be set.

Step 4. Find creative ways to limit result sets
In the client/server paradigm, record sets are transferred from the server to the local workstation; thus, they should be as small as possible in order to reduce network traffic. Therefore, unlike the way the typical VFP file-server application functions, forms should open without data. That will also help maintain a snappy form load, even as your data grows beyond the capacity of file-server databases, like VFP.

And if you think about it, it does make sense to give users only what they're looking for. Why present a user a with grid that has 10,000 open sales orders when he or she can only work with one customer at a time? I think we programmed like that for so long because incremental search utilities made for quick navigation of even large amounts of data. While that still might be acceptable for small systems, the load would become unbearable on a network, and your application wouldn't scale as data and users grow.

Parameterized views
So if your forms open without data in the client/server world, how do we go about getting data? You need to provide your users with a facility for entering filter criteria. These values will serve as parameters of the Where portion of the view's SQL statement. You can provide this functionality in many different ways -- your choices range from the very simple to the very complex. A simple implementation is to have a form bound to a parameterized view. When a user enters Find mode, you then provide a text box for entering the parameter value. For example:

* Create this view when you create the form. Create SQL View vOrders As Select * From Orders Where Customer = ?cCustomer * Open the view (with no data) in the Data Environment * or Load when you run the form. Use vOrders NoData * Get the customer parameter in Find Mode cCustomer = thisform.txtCustomer.Value Requery() && retrieve orders for cCustomer

A second technique that would provide much more flexibility is to provide a Query-By-Form (QBF) facility. QBF means that in Find Mode, you provide for the user a way to enter filter criteria into any (or just most) of the controls on a form. You then build a SQL Where string accordingly and use it in one of two ways:

1. Build an SPT statement to create a record set of the important fields and the unique ID. Create a view that's parameterized on a unique ID. Users can browse the SPT cursor to find the data they need, and you can requery the view based on the unique ID.

2. You can also use the QBF-generated SQL string to recreate the remote view definition on the fly. Since your DBC of views is on the workstation, there's no contention issue here. (More on this in Step 5.)

Tip!
There are at least two third-party tools available for implementing Query-By-Form in VFP:

• QBF Builder (http://www.hallogram.com/qbfbuilder)
• Stonefield Query (http://www.stonefield.com)

Handling controls bound to look-up tables
This concept of limiting result sets should be applied to controls as well. What good is limiting sales orders to one customer if you're going to populate several combo boxes on your form with look-up table data? The several-thousand-record list box or combo box control has no place in a client/server application. There are at least two ways of getting around this problem. One is to not use these kinds of controls on large tables and instead use a text box. Then just launch a separate form with a grid upon the user choosing a hot key.

If you insist on using multi-record controls, you can try a second technique. Don't populate the control until the GotFocus event is fired. This late-binding technique will save you the overhead of populating unused controls. You can also use a combination of each of these techniques depending on the anticipated size of the look-up table, and just control it with a property and/or options table.

The VFP Developer's Guide offers yet another suggestion: copying often-used lookup tables to each workstation. However, knowing when to keep the local version in synch with the server version sounds like a nightmare to me. Therefore, I recommend one of the other options.

One-to-many forms
One-to-many forms should be handled as follows. Whatever technique you've decided to use for single-table forms should also be used for the header portion of a one-to-many form. The views of child tables should be parameterized on the unique ID of its parent. So, when you navigate from one header to another, the child view just needs to be requeried. Using this method, only the children of the current header need to be brought locally.

Step 5. Keep views in their own DBC to treat the back end independently
If, in your framework design, views are maintained in their own DBC, it will make the transition to other back ends more seamless. In other words, when you want your application to access a different data source, you want to keep as many pieces of your framework still intact. Keeping your views in a separate DBC from the tables (in a VFP back-end scenario) will make accessing a remote data source a matter of flipping a switch. The implementation could be as follows:

• Create a directory called \VFPViews, and store a DBC of local views in it. Name the DBC AppViews.DBC (\VFPViews\AppViews.DBC).
• Create a separate directory for each data source you wish to access, keeping the view's DBC name the same. In other words, the DBC \SSViews\AppViews.DBC would contain views that access a SQL Server. Since there are often several places in your code where you'll refer to the views database, this will prevent your having to write conditional code per back end.
• In an initialization file of your choice, store information like the ODBC DSN name, the directory path where the views are stored, a flag indicating whether you're accessing local or remote data, and, if local, the directory path of where the VFP tables are stored.
• Set Path To... the appropriate views directory on application startup.
• If it's a VFP back-end, Set Path To... the network directory where the VFP tables are stored.

While it's true that you could add the name of the views database to the initialization file as well, often this is already hard-coded throughout an application. Having a separate DBC of views will also keep the size of it manageable. If the back end was a VFP database, combining them would mean that twice the number of database objects would need to be stored. If such was the case, Modify Database could take forever on a large system. You'll also notice that the view's DBC (regardless of whether it contains local or remote views) is kept locally, not on a network to be shared by all users. Using this technique will allow for a lot of user-specific flexibility, as you'll learn later in the article.

This whole piece feels like it's assuming a local store as well, but it's not explicitly said.

Step 6. Use Locate rather than Seek for local data navigation
Since all data is accessed via SQL statements in a client/server application, you lose the ability to use every VFP developer's best friend -- Seek. Wipe those tears, there's an alternative: Locate. Since local result sets will be (by client/server definition) relatively small, the Locate command will more than suffice for moving the record pointer in a local cursor or temporary table.

Here's even better news. The result set of a view is actually a temporary table. To see what I mean, open any view and check out the return value of ? DBF(). You should see something like C:\WINDOWS\TEMP\76633985.TMP. So this "thing" actually has a disk presence. And in VFP, if it has a disk presence, we can index it. So, feel free to index a view's cursor as much as you'd like if you feel you need the speed. But I'd still stick with Locate over Seek. Since Locate is also Rushmore-optimizable, using it with or without an index won't fail -- one will just be marginally faster than the other.

Conclusion
Well, we're only halfway there, but already I'm sure you can see the writing on the wall. Wrappers and views are certainly the keys to making your code back-end-independent. Keeping those concepts in mind at all times will give you the leverage you need to make a flexible, scalable client/server application. Next month, I'll cover six more steps and close with a sample form that can access a VFP database as well as a SQL Server database. Then, you're on your own. See you next month. Until then, start wrapping.

Download the code: 05falino.exe

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's 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.