Building a Visual FoxPro Application for SQL Server
This chapter is from the book
This chapter is from the book
This chapter is from the book
Chapter 3: Building a Visual FoxPro Application for SQL Server
In This Chapter
-
Why Three-Tier?
-
Creating the SQL Database and Loading Your Tables
-
Writing the Sample Application
-
The Form Template
-
A Search Form Template
-
The Data Tier
-
What's Next?
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:
USE C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 8\SAMPLES\DATA\Customer COPY TO CUSTOMER USE C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 8\SAMPLES\DATA\EMPLOYEE COPY TO EMPLOYEE
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:
USE CUSTOMER EXCLUSIVE INDEX ON PKFIELD TAG PKFIELD USE CUSTOMER EXCLUSIVE INDEX ON PKFIELD TAG PKFIELD
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.
Listing 3.1 Procedure to Copy a SQL Table to a DBF
PROCEDURE SQLToDBF
PARAMETERS TableName
ConnStr = [Driver={SQL Server};Server=(local);Database=(Name);UID=X;PWD=Y;]
Handle = SQLStringConnect( ConnStr )
SQLExec ( Handle, [SELECT * FROM ] + TableName )
COPY TO ( TableName )
MessageBox ( [Done], 64, [Table ] + TableName + [ copied from SQL to DBF], 1000 )
ENDPROC
Sample usage:
SQLToDBF ( [Customers] )