Data Access in Visual FoxPro Before Visual FoxPro 8

In earlier versions of FoxPro, we had a few ways to access data: DBFs, views, remote views, and SQL Pass-Through. The advent of the Internet allowed us to add HTTP and remote data servers to that list, including new HTTP data access to SQL Server.

The DBF Format

Local tables with the extension .dbf are the best-known identifying characteristic of FoxPro applications. The DBF, described in an earlier chapter, is a disk file with a header of 512 bytes if part of a database or 256 bytes if a free table, followed by 32 bytes to describe each field in the table, followed by fixed-length records preceded by a delete byte , which contains an asterisk if it has been marked for deletion with the DELETE command. (Tables that are members of a database have the name of the database container file [DBC] stored in the additional 256 bytes of the header.) Cursors (described later in this chapter) have the same format as free tables because they can't be created as members of a database. Part of the header information is stored in hexadecimal format, including LSB (least-significant byte first) format, so that reading cursors is a skill in itself. However, it is almost never necessary to do so, unless you're Paul Heiser and you sell a tool to fix them when they get damaged.

Creating a Table

You can create a table either interactively or in code. Usually, when I'm building a system, I simply type

 CREATE CUSTOMER 

A Table Designer window opens up and allows me to begin describing the table, as shown in Figure 6.1.

Figure 6.1. The MODIFY STRUCTURE dialog.

Field names are limited to 10 characters if the table is not part of a database container. If a database container is open , the table is automatically added to it and can have long field names .

Tables can also be created using the CREATE TABLE command, which has this syntax:

 CREATE TABLE SUBS (  ;  SubNumber Integer,  ;  StartDate Date,     ;  Cost Numeric(6,2),  ;  Expires Date,       ;  CustomerID Char(10) ) 

Typically, you would use some data source, perhaps a table of possible fields to be included in a particular survey, and then generate a string containing the command. Then you would use macro expansion, (for example, &Cmd ) to execute the command and create the table.

Using Local Tables (DBFs)

To use a DBF, you, well, USE a DBF:

 USE ( TableName ) 

This command opens the named DBF in the current work area and moves the record pointer to the first record. In VB, there is no current select area as there is in FoxPro, and the position of the record pointer within a dataset serves as a surrogate for RECNO() (record number). In FoxPro, the current select area ( SELECT() ) and the current record number ( RECNO() ) are managed by the FoxPro environment, so that we can use them without resorting to the methods or properties of some data handling class. They're just there .

This notion of a current work area is not without its problems, and we've all experienced them. If you already have a table open, say in select area 1, USE ( TableName ) will close it and open the new table in select area 1. So you usually use the following syntax to ensure that you haven't just closed a table that you still needed:

 SELECT 0    && get the next available work area USE (  TableName  ) 

You can also use abbreviated syntax:

 USE (  TableName  ) IN 0 

However, this doesn't select the new table; that requires a separate SELECT command. So you would ordinarily use this:

 USE CUSTOMERS IN 0 SELECT CUSTOMERS 

Aliases

The USE command can also specify an ALIAS . In a FoxPro program, when you issue the SELECT ( TableName ) command, you actually select the table's ALIAS . The alias is by default the table's name; however, if you're working with a class of tables that might have other names but represent a single type of data, for example timesheets, you can specify an alias, as shown in Listing 6.1.

Listing 6.1. Specifying an Alias

 FName = GETFILE ( [DBF], Where is the timesheet to import?] IF EMPTY ( FName )    RETURN   ELSE    USE ( FName ) IN 0 ALIAS TIMESHEET    SELECT TIMESHEET ENDIF 

When a table is open, the command LIST STRUCTURE provides a list of the fields ( columns ) in the table. To save the resulting list to a file, use LIST STRUCTURE TO ABC.TXT . You can also use LIST STRUCTURE TO PRINTER NOCONSOLE to send it straight to a printer.

The contents of a field in a table are referred to using the syntax TABLENAME.FIELDNAME , for example, CLIENTS.PHONE . You can also SELECT a table, and then refer simply to the PHONE field because the alias of the current work area is assumed by default.

Visual Basic .NET doesn't have the concept of a default alias. You can't type Phone and assume that it will know what table to look in. In fact, it won't look in any table.

TIP

Here's a little preview: The only way that an expression like CLIENTS.Phone will have any meaning in Visual Basic .NET is if you have a Clients object that has a property named Phone . The Clients object is exactly like any other object; it's based on a class, consisting of a CLASS definition containing PUBLIC PROPERTY declarations that have the same names as the fields in your table. You're responsible for building the class and for moving the data from the XML structure that holds it to the class. More about this later.

Cursors

Cursors are temporary tables. You get a cursor when you issue a SQL SELECT statement to retrieve records from a DBF. You also get a cursor when you return records from SQL Server. And as we've seen, you can use XMLToCursor() to build a cursor from an XML string.

There is also a CREATE CURSOR command, which looks exactly like the CREATE TABLE command. It creates a temporary structure like that of a DBF, typically with the extension .tmp , which you won't see unless you use the DBF ( cursorname ) function to return the name. I've only come up with one use for this, in conjunction with the APPEND FROM ( tablename ) command, which requires a table name rather than a cursor name.

Cursors, unlike tables, can have long field names. This makes them perfect for storing the contents of cursors returned from SQL Server because few SQL database administrators limit themselves to 10-character field names.

Supporting Cast

There are a number of commands and functions that are meaningful while a table is open.

Table 6.1. Commands and Functions for Working with Open Tables

Function/Command	Action/Return Value
RECNO()	Returns the record number of the current work area
RECCOUNT()	Returns the total number of records in the current work area
FCOUNT()	Returns the number of fields in the current alias
FIELD( n )	Returns the name of the n th field in the current alias
SKIP ( n ; default is 1)	Moves forward n records
SKIP ( -n ; no default)	Moves backward n records
TOP or GO TOP	Moves to the first record in the current index order, or to record 1 if no index is attached
BOTTOM or GO BOTTOM	Moves to the last record in the current index order, or to record ( RECCOUNT() ) if no index is attached
SCAN...ENDSCAN	Executes the code between these two words once for each record in the current alias
CursorSetProp ( "Buffering", n )	Sets the current alias's buffering mode to n (1 = none, 2 = pessimistic record locking, 3 = optimistic record locking, 4 = pessimistic table locking, 5 = optimistic record locking)
APPEND BLANK	Adds a blank record to the current alias; if buffering is on, the record is not permanently added until the TableUpdate() function is called
DELETE	Marks the current record in the current alias for deletion the next time PACK is issued while the current alias is selected
TableUpdate()	Makes permanent any changes made since buffering mode for the current alias was turned on
TableRevert()	Undoes any changes made since buffering mode for the current alias was turned on
CursorToXML(alias, "varname")	Converts the records in the current alias to XML and stores it in the named variable
XMLToCursor(varname, cursor, flags)	Converts the XML in the named variable to records in the named cursor, based on the flag values supplied

Buffering

If buffering mode is set to 2, 3, 4, or 5, two versions of changed records are maintained . If the TableUpdate() function is called, the changes become permanent. If TableRevert() is called, the changes are discarded.

TableUpdate() and TableRevert()

When you've enabled buffering, something strange happens; as you change records, FoxPro maintains two copies of each changed record ”one before and one after the change. If you issue the TableUpdate() function, changes are made permanent. If you issue the TableRevert() function, changes are not saved.

After you make changes to a buffered table, you can't close the form that's using it unless you call TableUpdate() or TableRevert() . That's why, in your form templates where you use buffering, you either have to set ControlBox to False to eliminate the possibility of a user trying to close a form with pending changes, or automatically call TableUpdate() or TableRevert() if a form is closed after changes have been made, depending on which you want to be the default behavior. It's a little messy, but it's unavoidable when you begin using buffering.

The Phantom Record

FoxPro has a record pointer, which tells it which is the current record. When you use APPEND BLANK , FoxPro jumps to the end of the alias and adds a blank record. However, it's not actually added until you use TABLEUPDATE() , if buffering is enabled. If you use TABLEREVERT() , where is the record pointer? The answer is, it's one record beyond the number of records in the file. So you would have a RECCOUNT() of, say, 12, and a RECNO() of 13.

One of the consequences of this is that if you cancel an add, you have to tell FoxPro what record you were pointing to before the APPEND . That's why the FoxPro template in Chapter 2, "Building Simple Applications in Visual FoxPro and Visual Basic .NET," had a property named BeforeAdd , so that after a canceled add we could go back to the record that was showing before the APPEND BLANK was issued.

Indexes

Indexes are implemented in FoxPro either as single-index IDX files (compact or not compact) or as tags in CDX (compact multiple index) files. To create a simple index, you use the command

 INDEX ON (  Expression  ) TO (  NameWithoutTheExtension  ) COMPACT 

To create a tag in a CDX file you issue the command

 INDEX ON (  Expression  ) TAG (  TagName  ) 

To attach an index, you use the command SET INDEX TO ( IdxFileName ) . You can attach several IDX and CDX files in a single SET INDEX statement. However, when you open a DBF, if a CDX file of the same name exists, it is automatically opened, although no tag order is set. To do that, you issue the command

 SET ORDER TO TAG ( TagName ) 

or simply

 SET ORDER TO ( TagName ) 

After an index has been selected, a SEEK command for a value that's in the index will find any matching record. Depending on the SET NEAR and SET EXACT commands, the record pointer can be set to the next record following the place where the record would have been had it existed, or to the end of the alias.