- Home
- Articles
- Tools
- Related Links
- About FoxDev

Need database development, operations, or hosting services?
Get info fast:

Visual FoxPro - Client/Server

Robert C. Bradley

 

Client/Server: Why Would I Want It?

That was my question at one time. Given Visual FoxPro's blinding speed, it seemed silly to even consider using anything else as a data source. Client/server seemed like a gigantic waste of money and effort for 98% of typical FoxPro applications.

And I still think that for the majority of FoxPro implementations, using native Fox tables is still the way to go. But I have become a convert of sorts, in that I now see many applications, even relatively small ones, where using a SQL server backend makes sense.

Before we go further, we should define what "client/server" means, at least in the context in which we are using it here. "Client" refers to the so-called "front-end" application, the one that is running on the user's desktop. For our purposes here, the Visual FoxPro application is the client. "Server" refers to the SQL server "back-end", the piece that is responsible for storing and managing the data, ensuring integrity and security, and responding to the client (Visual FoxPro) requests for data or transactions.

The server software may be Microsoft SQL Server® (not to be confused with the generic term SQL server; a recent FoxPro Advisor full-page advertisement made the mistake of using the generic name as a moniker for the Microsoft product); Oracle® Sybase® and IBM's Universal DataBase® (UDB).

 

Benefits of Client/Server

Performance

Queries against large tables creates a great deal of network traffic. For example, if there was a Contacts table with 50,000 records and you issued the statement:
SELECT LNAME, FNAME FROM CONTACTS WHERE ZIPCODE="32216"
and there are only 12 matching records, how many records do you think will be transmitted from your LAN server to your PC? The answer is: 50,000. Remember, all the processing on a VFP-only system is done by the client CPU, so all of the records have to be transmitted in order for the client machine to read and accept or reject them (I realize that Rushmore doesn't necessarily send the entire record, but play along). Again, even though you have a network server, that is not the same as a database server.

By using client/server architecture, the server does the vast majority of the query processing. In the above example, the SQL server itself does the work; only the two fields for the 12 matching records are sent over the network to the client.

"Big deal" you say. Our network is pretty fast, so this operation even without the use of indexes is pretty fast. And it may well be true for the majority of applications: there are only a handful of users and fewer than 20,000 or so records even in the largest tables. For the performance benefit aspect, I would say that such an application and organization would likely not benefit (though they may still receive some integrity benefit, as we'll discuss in the next section).

But even with small tables and few users, imagine a scenario where a satellite office needs access to the database. If you have an office two miles away, they are out of LAN range. Dial-up Networking and Remote Access give the users in that satellite office the tools to establish a dial-up network connection and be a node on the network, just like a PC in the host office (albeit with a much slower connection). But with the traditional VFP application using native Fox tables, imagine dragging thousands of records across a phone line just to do a single query that will ultimately reject 98% of the data sent it.

A client/server system will send only the results to the client, regardless whether the client is a room away or a thousand miles away. Security willing, any computer anywhere in the world could connect to that database server via TCP/IP or dial-up, and have fairly decent response...if the system is designed correctly (discussed later).

Long-distance aside, it still may be possible to get better overall performance with a client/server system. If you buy a decent, near-leading-edge computer for your database server (and I'm talking only of a few thousand dollars), you don't need super high-power on the desktops for processing all the data. If you have less than last year's model as desktops or have a slow network, client/server may give you better performance than native Fox tables, even if the tables are small.

Integrity

Everyone raise their hand who has had data corruption in a DBF file, or ever had to reindex to rebuild a corrupt index. OK, everyone put their hands down. Now raise your hands if you've ever encountered "orphaned" child records where the parent record has been deleted, or never entered. It looks like a wheat field out there.

I have worked with four different SQL server systems over the past couple of years, and do not know of a single instance where any of the above conditions occured. I'm not saying that SQL server datases never, ever, ever have corruption, but it is much rarer than what we typically think of as "normal" in the FoxPro world. Some SQL servers even let you add fields and make other changes to a table even while the system is up and users are in them!

All SQL server systems will enforce your data integrity and security constraints like two layers of Kevlar. While Visual FoxPro gives us tools for enforcing referential integrity at the database container level, few are convinced that those tools are especially reliable.

 

Disadvantages of Client/Server

You'll have to change the way you program certain things. For example, no more BROWSE command on a 10,000 record table; true, you can do this if you want, but the entire table would have to be downloaded, and it kind of defeats the purpose of moving to client/server.

Instead, you'll want to change or write the application so that the user provides and initial key, such as a last name, zip code, or some such. Then, only the records matching that key are brought down for further processing.

You don't have to automatically give up pick lists and the like, though; things like state lists, race codes, and other such tables of less than a hundred or two records are still perfectly fine.

 

Properly Using Visual FoxPro's Client/Server Interface

 

Setup

Obviously, someone will have to install, configure, and create tables for your chosen backend (such as Oracle® or Microsoft SQL Server®). Assuming they have already done so, you'll need to install an ODBC driver (and possibly some other client-specific tools) for that database server. Generally, TCP/IP is used to communicate with the database server, so you may need to assign a logical name to a specific IP address.

 

Connecting

Before you can retrieve or update data on the database server, you must establish a connection. A connection usually involves identifying a server, a database, a username and a password. Connections can be named and stored in a database container so that you can reconnect later by specifying the connection name, or they can be created on-the-fly through a command.

A named connection can be created by issuing the command CREATE CONNECTION while you have a database container open. You will be presented with a dialogue allowing you to pick the ODBC data source, username, and so forth. When you save it, you'll be asked to enter a name; you can use that name later to establish a connection, or it can be used as a connection by any remote views you create (more on remote views in a moment).

To establish a connection using a named view, open the database container (you can issue the command OPEN DATABASE WHATEVER to do that) and issue the command: nCONNECT=SQLCONNECT("MYCONNECTION") where "myconnection" is the name of the connection you've created. Note that we've stored the connection handle returned to us by SqlConnect() in the memvar nConnect for use later.

Connections can also be created that do not need to be stored in a database container. These will need to contain all the information necessary to establish the connection each time.

To establish a connection dynamically, issue the command
nCONNECT=SQLSTRINGCONNECT("driver={SYBASE ODBC DRIVER};UID=me;PWD=mypwd;DATABASE=maindata")
An alternative to using the "driver=" method is to use the "dsn=" parameter, where the DSN (data source name) is one found in your Control Panel/32-bit ODBC User DSN section.

 

Gimme Some Data

There are two ways to get or update data from your SQL backend. One is with a remote view, and the other is with SQL pass-through. Both have their strengths and weaknesses; most applications make use of a combination of the two.

Remote Views look and smell like native FoxPro tables, so they are often favored by newcomers to client/server. It can be created while in the database designer (right-click, then choose New Remote View), or via the command:
CREATE SQL VIEW myview REMOTE
Either way, you'll create a view that is stored in the database container (DBC), and can be opened later by issuing the USE command as you would any other table. You'll be prompted to select an existing named connection, so be sure you create a connection and store it in that DBC first.

Remote views are, by default, not updateable. Don't misunderstand: you can change the data in the view, but those changes won't be sent to the database server. To make a view updateable, you must check the Send SQL Updates checkbox on the Update Criteria tab, and ensure the primary key settings are correct. With an updateable view, changes to a record will automatically create an SQL Update statement that is sent to the database server; new records will create an SQL Insert statement. With row-buffering, these statements will be sent to the server when the row-pointer changes or you issue a TableUpdate() command. With table-buffering, the statements are sent when the table is closed or a TableUpdate() is issued. Either can be aborted by issuing a TableRevert() command.

Issuing a USE command with the name of a remote view will cause the data to come from the database server to a local cursor on your PC. Assuming there is no Where clause in the view, all records for that table (or tables, if the view joins multiple tables) will be pulled. This is important to keep in mind with large tables, as opening a view for a 100,000 record table will cause a great deal of network traffic and server work, and will eat up a bit of your disk real estate. Adding the NoData clause to the end of the USE statement will cause only the structure to come down.

A parameterized view is the proper way to work with large tables by using a remote view. It limits the records being sent to the local PC to only those matching a condition or set of conditions, as if you had issued a SELECT statement with a WHERE clause.

It is important to understand that issuing the command
SELECT * FROM RVCLIENT WHERE STATE='UT' AND LASTNAME='BOGART'
(where RVCLIENT is a standard remote view) will cause all of the records in the Client table to be brought down to the local machine...if there are 300,000 records in Client, then it will waste significant time and network resources. The WHERE conditions are applied by Visual FoxPro, not the database server.

A parameterized view allows the database server to apply WHERE conditions before data is sent to the local machine. To take a regular view and turn it into a parameterized one, open the Remote View Designer and move to the Filter tab. In the first Field Name line, choose the State field from the picklist, and enter "?cState" (without the quotes) in the Example column. The use of the "?" prefix is important, as that is what identifies this as a parameterized view. Now choose AND in the logical column and move to the second line. Pick the LastName field, and enter "?cLastName" in the Example column.

The identifiers "cState" and "cLastName" are simply the names of memory variables. You can name them whatever you like, so long as they are prefixed with the "?" character.

Now that we've made this a parameterized view, simply store "UT" to the memory variable cState and "BOGART" to the memory variable cLastName. By issuing the command: USE RVCLIENT we will only receive the records that match the two conditions. If we had issued the USE RVCLIENT command without first creating the memory variables, FoxPro would prompt us for their values.

Issuing the statement REQUERY() at any time cause the underlying SQL Select statement to be re-issued. We might do this if we have changed the values of our two memory variables, or if we wanted to get more recent data.

SQL pass-through gives finer control over the process of retrieving and updating SQL server data. It also requires that you know a bit more about your particular back-end server.

You must first establish a connection with the database server before you can successfully issue an SQL pass-through command. Remember to store the connection handle returned to you in a memory variable, as you'll need to reference that connection handle for each SQL command you issue.

Here is a sample set of SQL pass-through commands:
NHANDLE = SQLCONNECT("MYCONNECTION") && myconnection is a named connection in the DBC
NRESULT = SQLEXEC(NHANDLE, "SELECT * FROM CLIENT WHERE STATE='UT' AND LASTNAME='BOGARD'")
The first command establishes the connection (the handle number returned should always be greater than zero; if it is not, then there was a problem connecting), and the second command sends the statement to the database server (nResult will be greater than zero if the command executed without error).

Any SQL statement that your database server recognizes can be sent with SQL pass-through, even calling stored procedures!



www.foxdev.com

Have a question about our services?

Send us an

   
 

Home · About Us · Articles · Related Links · Contact Us
Copyright © 2007 Broad Lea LLC, all rights reserved. FoxDev SM Broad Lea LLC.