< Day Day Up > |
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 FormatLocal 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 TableYou 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 AliasesThe 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 AliasFName = 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. CursorsCursors 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 CastThere 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
BufferingIf 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 RecordFoxPro 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. IndexesIndexes 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. |
< Day Day Up > |