Chapter 3: Building a Visual FoxPro Application for SQL Server

Even if you haven't started using three-tier data access in FoxPro, you
certainly have heard of it. What's the big deal? Is this something that
you need to learn? In this chapter, you'll discover that

You absolutely need to learn three-tier data access.

It's easy.

The library described in this chapter can be used with your very next project.

In this chapter, we'll build a data access layer to communicate with either
DBFs or SQL Server. And we'll build it in such a way that there is absolutely
no code to change when you move from DBFs to SQL tables. We'll even include
an upsizing wizard to migrate the data for you. We'll talk about the things
that you don't want to do in SQL if you want to simplify programming (always
a good thing). We'll use a data access layer, which gives you the ability
to use DBFs, SQL Server, a WebConnection XML server, or XML Web services built
in Visual FoxPro 8, the best upgrade yet. The code for this chapter is written
to be compatible with Visual FoxPro 7, but in subsequent chapters we'll
add features only available in versions 8 and higher. It might surprise Microsoft,
but not everyone has the latest version of their languages.

Why Three-Tier?

Three-tier is a variant of n-tier: A calls B calls C, and so on. Each one
does a part of the task. With server farms and ASP applications, there can be
several data tiers, a page generation tier, and so forth. But for our purposes,
three-tier is generally sufficient. In the usual three-tier diagrams (which
we'll dispense with here), A is your form, B is a data access layer, and C
is the place where the data is storedusually DBFs in our world, but
that's changing, and that's where the data access layer comes in.

In traditional FoxPro applications, our forms contain the code that gets and
stores data. Our code snippets are full of SEEK and REPLACE
commands. The problem arises when our client decides that they're tired of
kicking everyone out of the application and rebuilding the indexes, or redoing a
400-file backup that just failed because a user didn't close the CONTROL
file, or watching an APPEND BLANK take 30 seconds because the table has
900,000 records and the index is 9MB. DBFs are great, but they do have
drawbacks. And the solution is spelled S-Q-L.

SQL has numerous benefits. When you back up a SQL database, you're
backing up a single file. Backup can be run while users are in the system. And
RESTORE is also a one-line command.

Security is another issue with FoxPro tables. Anyone with access to the DBF
directory on the server can see your FoxPro tables. SQL Server, on the other
hand, has complex security built in. So you can decide who does what. In
today's increasingly risky environment, users can and will demand improved
security. SQL Server is a good way to accomplish it.

So your client is sold. Install SQL Server. You can run SQL Server on your
development machine just fine; in fact, it's a great idea. Be sure to
install the Developer Edition, which has a Management Console. If all you have
is MSDE, it will work fine, but you have to create the database, indexes, and
logins programmatically, and it's just a little harder to learn some SQL
tasks nonvisually.

SQL Server runs as a service. It "listens" for requests from
workstations, does what is asked of it, and sends any result set back to the
workstation. There is no index traffic because the indexes don't come back
with the results. Most of the slowdown you might have experienced in FoxPro apps
on a LAN are due to network traffic, so solving the slowdown problem can be
sufficient motivation for migrating to SQL Server.

So you've installed SQL Server, and you need to migrate your application
to use SQL Server tables. First, you have to migrate the data. There are several
ways to do this, and all of them have problems. You can use the SQL Upsizing
Wizard, but the resulting SQL tables can cause serious programming headaches.
There's a DTS utility that is installed when you load SQL Server, and if
you like writing your data recoding routines in Basic, go right ahead. I prefer
FoxPro. So your best bet is to write your own data migration program. I've
included one in the code for this chapter.

What's wrong with the Upsizing Wizard? For one thing, it defaults to
permitting NULLs as values in uninitialized fields. If you've never run
into NULLs, consider yourself lucky. Statisticians need to know whether a value
of zero is a reported value or simply someone who didn't answer the
questionfor example, What is your age? You can't calculate average
age by summing ages and dividing by zero if half of the respondents didn't
want to answer. So you have to know which are missing values. SQL Server
allows for missing values, and in fact defaults to them. But they nearly double
the programming burden. ASP code goes bonkers with nulls. So unless you really,
truly care about missing values, you absolutely don't want to use NULLs.
That's why the preferred way to declare a column in T-SQL is

Age Integer NOT NULL DEFAULT 0,...

I strongly urge you to include NOT NULL in your column
declarations and to supply a default value.

Secondly, SQL has reserved words, which have to be enclosed in square
brackets if you use them as field names. I don't use them if I have my way.
But we often have to support legacy applications, and that means using two
systems in parallel for at least a while. So we'll want to enclose reserved
word column names in square brackets while building the table definitions. The
data conversion program provides for you to furnish a list of SQL keywords that
you've used as field names. I've seeded the list with the usual
suspects (for example, see line 21 of the LoadSQLTables.PRG file in
Listing 3.2). Add other field names that you've used in your tables if SQL
complains about them during the table creation process.

Finally, in order to make updating records easy, it's a good idea to
provide a unique integer key as the primary key for each table, especially if
you have another text field that you've been using as a key. The reason is
that unique keys are essential if you want to make the coding of updates simple,
so every table has to have one. In the FoxPro world we've developed the bad
habit of using a compound key (for example, PONum+LineNum for the
purchase order items file), which is simply a nightmare to code in some generic
fashion.

SQL Server has an autoincrementing feature called IDENTITY. For
example, you can declare an Integer field named MyKey and set
IDENTITY(1,1) (begin with 1 and increment by 1), and every time you
insert a record a new key value will appear. The problem is that if you need
instant access to that value, you need to add a SELECT @@IDENTITY
command after the INSERT commandfor example, after adding an
Invoice header and before inserting the related Invoice Detail Lines in order to
provide the header key in the detail records for subsequent JOINs. And
there are other reasons. If you have an IDENTITY field, you must not
include its name in any INSERT commands. So your
CommandBuilder code has to know to skip the key field if it's an
IDENTITY field. You get the picture. Identity fields are more trouble
than they're worth. So we'll do our own primary key generation instead
of using SQL's IDENTITY feature.

To get a head start, open each of your application's tables and create a
primary key field if it doesn't already have one (child tables are good
candidates). Use the MODIFY STRUCTURE command, add the PKFIELD column
name (you can use PKFIELD as the PRIMARY KEY column for every table if you want
to), and then use this to add unique keys:

REPLACE ALL "pkfield" WITH RECNO()

or

REPLACE ALL "pkfield" WITH TRANSFORM(RECNO(),"@L #######")

for character fields.

When you're done, do a SELECT MAX(KeyFieldName) FROM
(TableName) for each of your DBFs, and make sure that these are the values
that appear in the table called Keys that you'll find in the zip file for
this project. This table tells the application what the last primary key value
used was in each of the tables in the application. This table exists both for
DBF-based and for SQL-based systems. It's a loose end, and a commercial
application that used this technique would need to include a goof-proof way to
set these keys after migrating the data and before going live. You'll have
to do it manually. Or you can write a little utility routine as an exercise.
(Hint: You'll need a list of table names and their primary key fields in
order to automate the process.)

Getting Our Test Data Ready

The FlatFileForm and DataTier classes will allow us to
quickly create forms that work with both a DBF and a SQL table. But in order to
test them, we'll need to have the same tables in both formats. Luckily,
there's an easy way to accomplish this.

FoxPro ships with a sample data directory containing some good examples. Type
the following in the command window:

When you have your two tables, you should add integer keys to the tables
using MODIFY STRUCTURE. In this case, use PKFIELD (Integer)
for both tables, and make it the first field in the table. Before leaving the
schema designer, add PKFIELD as an index tag, or just type the
following in the command window:

Finally, you can set the database name in the SQL ConnectionString
and run the LoadSqlTables program to load your tables.

If you want to use your own tables from your own database, you can copy them
to DBFs very easily, using the procedure shown in Listing 3.1; substitute your
names for your database, userID, and password.