Database Containers

So far we've talked about free tables, which are not contained in a database. Many of the features of FoxPro tables and cursors require the use of a database container (DBC), a sort of metatable containing references to tables and their fields and indexes. It also holds transactional information, stored procedures and triggers (including generated relational integrity triggers), and the SELECT statements and connection information needed to build local and remote views. You can't use any of the advanced features without including a database container in your project. And if you do, consider buying Doug Hennig's Stonefield Database Toolkit (SDT) (www.Stonefield.com), which adds a ton of additional functionality to the DBC.

In FoxPro, DBFs can either be free tables or can belong to a database container. In the latter case, the table headers include an additional 254 bytes for the name of the DBC file used as the database container. Additional details of the DBC are stored in a memo file with the extension DCX, and an index file with the extension DCT.

The DBC file is itself a DBF. Its structure is shown in Listing 6.2.

Listing 6.2. The Structure of the DBC File

 Structure for table:PINTER.DBC Field  Field Name  TypeWidthDec   Index   Collate Nulls 1  OBJECTIDInteger 4 No 2  PARENTIDInteger 4 No 3  OBJECTTYPE  Character  10 No 4  OBJECTNAME  Character 128 No 5  PROPERTYMemo (binary)   4 No 6  CODEMemo (binary)   4 No 7  RIINFO  Character   6 No 8  USERMemo4 No ** Total **  165 

When you create the database, five records are added, containing five records with the following values in the ObjectName field:

 OBJECTIDPARENTID OBJECTTYPE OBJECTNAME 1   1 Database   Database 2   1 Database   TransactionLog 3   1 Database   StoredProceduresSource 4   1 Database   StoredProceduresObject 5   1 Database   StoredProceduresDependencies 

Transactions in progress are stored in the database. When transactions are committed, they are removed from the DBC and put into tables and removed from the DBC; if rolled back, they are simply removed from the DBC. Stored procedures, including relational integrity triggers, are stored here. So if you want to use transactions, stored procedures, and relational integrity constraints, you must use a database. Otherwise, the database may not be necessary or even useful. However, if you intend to use local and/or remote views, you must use a DBC.

Local Views

Local views are cursors created based on SQL SELECT statements stored in database containers. They don't have indexes, although if the SQL SELECT statement has an ORDER BY statement, it determines the order of the records. Views can be marked as updateable either in the database container or by using CursorSetProp() settings. When a view is marked as updateable, as the view is changed, the records in the underlying table are updated.

Remote Views

Remote views are the same as local views except that their data sources are not FoxPro tables. The sources can be SQL Server, or any ODBC data source. In fact, you can create a remote view to a FoxPro table using an ODBC driver, although as Nixon once said, it would be wrong. (He did it anyway, as some of us recall.) Remote views can also be updateable.

At one time, many of us hoped that remote views would provide a way to use SQL Server. We were wrong. Remote views work, but barely . So, instead, we use SQL Pass-Through (SPT).

SQL Pass-Through

SQL is supported directly in FoxPro. The name is meant to imply that if for some reason you don't want to use remote views, real men use SQL Pass-Through (SPT). The reason for the clear distinction is that for FoxPro people, having to build an INSERT or UPDATE string seemed incredibly difficult. After all, if we use DBFs, there's nothing to do; leave the record, or issue TableUpdate() if you used buffering, and the change is permanent. Issue APPEND BLANK on an unbuffered DBF, and you have inserted a blank record, a truly unnatural act in SQL.

As long as we're quoting Nixon, let me make one thing perfectly clear: To add a record in SQL, you must create and send a complete, syntactically correct INSERT statement to SQL, using SQL delimiters. SQL's syntax is slightly different than that of FoxPro's twangy dialect , and SQL Server makes no effort to understand its distant cousin. Similarly, the UPDATE and DELETE commands must be expressed in their full glory .

SQL offers several things that DBFs don't provide. BACKUP and RESTORE are easier in every way. User access control is built in, and it's virtually impossible to damage SQL tables or index files. We've all had to build our own mechanisms to deal with these three problems, although the excellent Stonefield Database extensions written by Doug Hennig do a great job in these areas and, therefore, are part of the essential FoxPro toolkit.

The ability to back up and restore a database is essential. A SQL database consists of only two files; the database itself and a log file where transactions are stored. To back up a FoxPro application, you have to get all users to close their applications, and then copy all DBF, FPT, and CDX files to a backup location. In SQL, BACKUP is a single command, and users don't have to exit the application.

When a backup is done, the log file is erased. The theory is that if it is necessary to restore a database from the last good backup, the transactions log that has accumulated since that backup can be used to restore the database up to the moment that the database failed. It is also possible to use the BACKUP command to simply erase the transactions log, and in fact that's how it is often used.

SQL only returns a handle to a user when a valid user ID and password are supplied. You can either provide a single user ID and password for all users of your application, or issue one to each user. In either case, you don't have to write a single line of code to manage database access. That can be the single reason that justifies using SQL Server instead of DBFs.

Finally, SQL Server supports indexes that are remarkably similar to those in FoxPro; in fact, the indexing technology used by Microsoft in the latest versions of SQL Server came from FoxPro, and uses the Rushmore technology first developed years ago by the Fox Software team in Toledo. FoxPro indexes can be corrupted; SQL Server indexes are almost impossible to damage, and can be rebuilt automatically by SQL Server.

If any of these three features are important to your client, SQL Server is well worth the additional cost. However, SQL requires knowing more than is required to use DBFs. If you want to use SQL Server, you're going to have to cozy up to the Query Analyzer and get to know it. Help is available, from the Help, Transact-SQL Help menu pad. Transact -SQL is the actual name of the command language used in SQL Server, just as it was when they bought it from Sybase. (Did I mention that Microsoft bought some of its products from other companies rather than developing them in-house? Even Word.) A quick primer follows .

Connections and Connection Strings

The SQLConnect() command is used to supply the parameters necessary to connect to a connection previously defined in a database container (DBC), including (if desired) a user ID and password. If the UID and PWD are not supplied, SQL asks for them. This is necessary because a SQL connection consists at a minimum of the name of the server and the name of the database, and the SQLConnect() function provides no mechanism for naming them.

My preference is to use the SQLStringConnect command, which requires a complete connection string. If you don't know what connection strings look like, create an empty text file with the extension .udl , and double-click on it in Windows Explorer. The dialog that appears will allow you to build one, and will store it in the .udl file. Don't forget to select Microsoft OLE DB Provider for SQL Server on page 1 of the dialog. The usual server name is "(local)" if you've got SQL Server Developer Edition on your computer, and the default user ID of "sa" and a blank password are still there unless you changed them. Click the Test button to see if it worked. If you choose the Northwind database that is installed for testing purposes, your connection string will look like this:

 Provider={SQL Server};Server=(local);Database=Northwind;UID=sa;PWD=; 

To use it, do this:

 Str = [Provider={SQL Server};Server=(local);Database=Northwind;UID=sa;PWD=;] Handle = SQLStringConnect( str ) 

Either of these two commands returns an integer called a Handle . I used Handle as the variable name here, but use whatever you like.

SQLExec()

SQLExec(Handle, Cmd) executes the string defined in the variable Cmd . The command can be any of the four SQL commands: SELECT , INSERT , UPDATE , or DELETE . It can also be a SQL stored procedure, or one of the built-in SQL stored procedures that begin with the letters sp .

If the command is a SELECT , the matching records are returned in a cursor named SQLResult by default. If a third parameter, a string containing a cursor name, is supplied, the result is returned in a cursor of that name.

Finally, SQLDisconnect(Handle) closes the connection. For convenience, SQLDisconnect(0) closes any and all open connections.

As we saw in full detail in Chapter 2, you can't bind the controls on your forms to the cursor returned by SQL because it breaks the data binding. The ControlSource property of each control must be the name of an alias and field that exist at the end of the Load event of the form. That's why we create a cursor in the Load event, and then "pour" the data form that the cursor returned either by a call to SQLEXEC() or by a call to a Web service into the previously defined cursor.

Other SQL Commands

FoxPro also provides a few other SQL functions to return useful information about tables. You can do the same thing by instantiating an object based on the SQLDMO.DLL (SQL Data Management Objects) component that comes with SQL. On my computer, it's located in the following directory:

 Program files\Microsoft SQL Server\Tools\Bin 

It even comes with a pair of help files. The one called SQLDMO80.hlp is of absolutely no use unless you're already a SQLDMO expert. The other one, SQLDMO.CHM , is pretty cool and contains excellent examples. Look for how-to articles on the Internet, or visit my Web site.

Table 6.2 lists a few FoxPro functions that are available for use with SQL Server.

Table 6.2. SQL Functions in FoxPro

SQL Command	Purpose
SQLCANCEL()	Requests cancellation of an executing SQL statement
SQLCOLUMNS()	Stores a list of column names and information about each column for the specified data source table to a Visual FoxPro cursor
SQLCOMMIT()	Commits a transaction
SQLGETPROP()	Returns current or default settings for an active connection
SQLMORERESULTS()	Copies another result set to a Visual FoxPro cursor if more result sets are available
SQLPREPARE()	Prepares a SQL statement for remote execution by SQLEXEC()
SQLROLLBACK()	Cancels any changes made during the current transaction
SQLSETPROP()	Specifies settings for an active connection
SQLTABLES()	Stores the names of tables in a data source to a Visual FoxPro cursor

Some of these merit a few comments. SQLSetProp() can be used to establish asynchronous access to SQL Server, so that the program can request data and allow the user to go on doing other things. SQLMORERESULTS() returns the next N records, where N is the number of records you've specified to be returned in each packet of records in this "progressive fetch" scheme.

TIP

This should give you pause. I've done a query against a million records, and returned the few dozen that matched the query, in a few milliseconds . FoxPro tables are free. SQL Server isn't. Is it possible that a free data storage mechanism is hundreds of times faster than one that, to put it one way, isn't free? Try it yourself.

Transactions are considered by some to be the difference between trivial and serious database applications. I don't agree; I've built applications for some of the most important companies and nonprofit organizations in the world in which transactions were completely irrelevant. However, if you want to be able to commit both the header and the detail records of a purchase order, or commit neither if some problem occurs, transactions are how you do it. SQL Server supports transactions. To begin a transaction, issue the following command:

 = SQLSETPROP(gnHandle, 'Transactions', 2)  && manual 

TIP

FoxPro supports transactions on DBF tables that are members of a database container using the BEGIN TRANSACTION command.

The Upsizing Wizard

FoxPro includes an Upsizing Wizard, which will "automatically" create tables in SQL Server corresponding to the DBFs in your application, migrate the data in your DBFs to SQL Server, and create remote views to be used in place of DBFs throughout your application. It sounds great, but it isn't.

SQL Server is a different technology. The data is kept elsewhere, and the supposition is that you'll bring only the record or group of records that are needed for the current form. In FoxPro, you get all of the records all the time. The SQL equivalent of USE CUSTOMERS is SELECT * FROM CUSTOMERS , which brings the entire table to each workstation. The slowdown can be glacial . So unless you change the architecture of each and every screen, performance will be awful , and your users will be outraged.

On the other hand, if your queries are properly constructed to minimize data traffic, SQL performance for very large numbers of records and a large number of users will actually improve. Index traffic is the single greatest cause of poor performance in DBF-based applications, and SQL Server completely eliminates it.