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
|