Working with data

Working with data

That dimwitted fellow in the carnival midway has been patiently waiting for another command to work with the table he's got in his hand, and now is his chance. There are many times that you'll want to move the record pointer to a specific location in a table, and, optionally, find out the value at that location. For example, you might want to determine whether a series of records with a particular value exists, and, if so, update each of those records with a new value, or perhaps display those values for selection by the user. In either case, the important point is that you want to physically move the record pointer in that table to the record in question.

You'll also want to be able to add new records, make changes to existing records, and delete records. I'll cover each of these operations from an interactive point of view and introduce you to the commands that you can use either interactively or programmatically to do these same operations.

Navigating through tables and finding specific data

You can use the GO and SKIP commands to physically move the record pointer without regard for the data, and you can use the SEEK and LOCATE commands to find a record that has specific data, and to move the record pointer to that record.

SKIP

SKIP moves the record pointer a specified number of records, either backward or forward through the table. It can also be issued without an argument, in which case the record pointer is advanced a single record. You can use a positive number to advance the record pointer in the table and a negative number to reposition the record pointer backward.

You might be asking yourself, in that devious manner that most developers know all too well, "What happens if I use an argument that's bigger than the number of records in the table?" In general, attempting to skip past the last record or before the first record will position the record pointer at the appropriate end of the file. But there's actually a bit more to it than that, so it's time to talk about the Phantom Record.

BOF(), EOF() and the Phantom Record

Now that you're comfortable with simple navigation, you need to be aware of the peculiar way that FoxPro structures a .DBF table and the way it handles being at the beginning and end of a file. Before I delve into this conversation, however, I need to introduce a couple of functions and a new tool-the Watch window-that will make the following discussion much easier to understand.

The BOF() and EOF() functions return logical values that indicate whether the record pointer is positioned at the beginning or the end of the file. The RECNO() function, as you already know, returns the current record number.

Visual FoxPro has a sophisticated debugging tool that consists of five windows. I'll discuss the Debugger in much more detail in Chapter 19, but I need to introduce the Watch window now. First, open the Tools menu and select Options. In the Options dialog, select the Debug tab, click the FoxPro Frame option in the Environment drop-down, and then close the dialog. Finally, open the Watch window from the Tools menu. (You can also enter the command ACTIVATE WINDOW WATCH in the Command window.)

You enter expressions in the Watch text box at the top of the Watch window. The expression, together with its evaluated value, will appear in the bottom portion of the Watch window. As you change components that make up the expression, you'll see the evaluated value change in the Watch window. For example, enter the expression:

recno()

in the Watch window's Watch text box. Then open a Browse window, and as you move the highlight in the Browse window, you'll see the record number (the evaluation of the "recno()" expression) change. See Figure 3.13.

For the current exercise, enter the BOF() and EOF() functions in the Watch window if you haven't already. If you don't have a table open, the values 0, .F., and .F. will appear. Open a table with at least four records in it. You'll then see the values 1, .F., and .F. (Remember, when you open a table, the record pointer is automatically placed on the first record.)

If you're paying attention, these results might be disconcerting. Shouldn't the BOF() value evaluate to a true value? After all, we're on the first record!

BOF()

The BOF() function evaluates to .T. when you try to move the record pointer before the first record in the table. When you opened the table, you were on record number one, and BOF() was .F. Now, try skipping back a record (SKIP -1). Naturally, the record pointer stayed on record number one, but the BOF() condition changed to .T. Essentially, you can think of this function as indicating that you have "bumped up" against the top of the file. When you skip again (forward, that is), you'll move to record number two, and BOF() turns to .F. If you SKIP -1 again, you'll be back on record number one, and BOF() will still be .F. BOF()evaluates to .T. only if you try to move before record number one.

Once you've done something to set BOF() to .T., any further attempts to navigate before record number one (another "SKIP -1", or a "SKIP -500") will generate a "Beginning of file encountered" error. Note that simply being on record number one and doing a SKIP -1 command won't generate the error-if BOF() is .F. You're allowed, while on record number one, to perform a SKIP -1 command once.

EOF()

Now that I've beat the BOF() function to death, you are probably expecting the discussion for the EOF() function to go rather smoothly. But perhaps there's a slight feeling of dread-after all, what is that "phantom record" mentioned briefly above? Your feeling of dread is well-placed; after all, it's a "phantom" record, right?

Visual FoxPro (and other tools that use a standard .DBF file structure) places a vehicle called the Phantom Record at the end of a file; this record changes the way that EOF() is evaluated. The phantom record acts like an actual record in many situations and allows tables to be related even when one of the tables doesn't have any matching records for a record in the other table. However, it isn't a record, and pretending it like it is will cause early aging and other undesirable side effects.

Suppose your table has 10 records. As you can assume, moving the record pointer to record 10 will return .F. for EOF() because we haven't moved "past" the last record. And, like the events that occur at the beginning of the file, using the SKIP command to move past the last record will flip the EOF() value from .F. to .T. However, the record pointer doesn't stay on record 10-it moves to record number 11, and you can see this in the Watch window. EOF() turns to .T. but RECNO() evaluates to 11-in a 10- record file! This 11th record is the phantom record.

Notice that skipping from record number 10 (when EOF() was .F.) to record number 11 (when EOF() was set to .T.) didn't generate the "End of file encountered" error you might have expected. However, trying to use the SKIP command again (to record number 12, if you want to think about it that way) will do so, because at the moment you are trying to SKIP to 12, EOF() is already .T. This works just like BOF(), doesn't it? The difference, however, is that while attempting to move before the first record will result in the record pointer staying on record number one, attempting to move past the last record (record number 10) will result in the record pointer moving past the last record and onto the phantom record.

The reason that I've made such a big deal out of this phantom issue is that you have to handle movement at the end of a file differently than at the beginning of the file.

For example, suppose you've provided a mechanism to allow the user to navigate through the table sequentially, using a pair of Next and Previous buttons or arrows. If the user is on the first record, he shouldn't be allowed to press Previous, and if the user is on the last record, he shouldn't be allowed to press Next. You can do this by checking to see if the user is on the first or last record and dimming the appropriate button.

However, you can't just look for "record number one" and "record number NNN" where NNN is the number of records in the table. The user might have changed the order of the records in the table, or set a filter. In either case, "record number one" might not be, currently, the first record in the file. Instead, when attempting to back up through a file, you need to try to move past the beginning of the file and see if the BOF() flag was set to .T.

It isn't quite as easy when we're testing to see if we can move to the next record. Remember, the difference is that when we bump into a true EOF() condition, Visual FoxPro doesn't stay on the same record as it does with BOF(). Instead, the record pointer is moved to the phantom record. Thus, when moving past the end of the file to see if the EOF() flag was set to .T., you'll need to move the record pointer back-off the phantom record and back onto the last real record.

GO

GO moves the record pointer to the record number or position specified by the argument. Arguments can be "TOP", "BOTTOM", or a numeric value. GO TOP moves the record pointer to the first record and GO BOTTOM moves the record pointer to the last record. GO TOP and GO BOTTOM keep BOF() and EOF() at .F., and they both respect the current index order and any filters in effect.

GO N moves the record pointer to record N, if it exists. If it doesn't exist ("N" is larger than the number of records in the table), an error will be generated. You can also simply type a record number in the Command window and press Enter to move the record pointer to that record number. If you enter a record number that doesn't exist, an error will be generated.

SEEK

Here's where the action really starts. In the mid-1990s, it was common to show off FoxPro's prowess as a fast database tool by opening a table that contained every street name in the United States (1.6 million records) on a notebook computer, and then asking someone in the audience for the name of the street they lived on. The speaker would enter a query that asked for every street in the U.S. by that name. A few microseconds later (well, maybe as long as a quarter second, sometimes), a return list of 20, 50, or 200 street names would appear, together with the city and state that the street was in. Yes, that's a successful query of a 1.6-million-record table on a simple notebook computer with a sub-second response time. Old hands at FoxPro would simply yawn, and ask if there was anything new.

SEEK allows you to search for the first record containing a value matching an expression of your choice. In order to use SEEK, you must search for an expression for which there is a corresponding index tag, and the table must be set to that order before doing the SEEK. (If the table isn't set to any order, using the SEEK command will generate an error.) Suppose you're looking for the street name "WINDWARD" in the cStreetName field in the STREETS table (you'd need an index on cStreetName):

m.cSeekString = 'WINDWARD'use STREETS order cStreetName

seek m.cSeekString

Now, suppose you weren't sure how the last name was entered into the table. It could have been in proper case ("Windward"), uppercase ("WINDWARD"), or, perhaps in the event that someone had the CAPS LOCK key on by mistake, in reverse proper case ("wINDWARD"). In each of these cases, you'd have to SEEK for the exact manner in which "WINDWARD" was typed into the field. SEEKing on "WINDWARD" when it was entered as "Windward" would not produce a match.

The natural assumption is to convert the entry to the same type each time, such as uppercase:

seek upper(m.cSeekString)

This wouldn't necessarily work, because you'd still be looking for an uppercase expression in a field that might not contain an uppercase value. The values in the field must match the search expression exactly. In other words, if you wanted to search on UPPER(m.cSeekString), you'd need an index tag on the expression UPPER(cStreetName) in the table. Here is what the code might look like:

m.cSeekString = 'Windward'use STREETS

index on upper(cStreetName) tag ucStreetName

set order to ucStreetName

seek upper(m.cSeekString)

(Of course, you wouldn't actually index every time you did a SEEK-it was shown just as an illustration.)

Note that the SEEK command finds only the first record that meets the condition. However, because the table is ordered on the expression that you're SEEKing, all of the records that meet the condition will be clustered together. In other words, if you're SEEKing on WINDWARD, all of the records that contain WINDWARD will be grouped together.

RUSHMORE-revisited

An expression that matches an index tag in a table is referred to as a "Rushmore-optimizable" expression. Searching and querying tables using Rushmore-optimizable expressions is extremely fast, and you should endeavor to do so, in contrast to not using Rushmoreoptimizable expressions, whenever possible.

The FOR expressions in the next few commands feature these Rushmore-optimizable expressions.

LOCATE

The LOCATE command can be used to find any piece of data in one or more records, regardless of the available index tags. If an index tag is available, Visual FoxPro and LOCATE will take advantage of it, but the point of using LOCATE is that you can find any data in a table.

Why not simply create as many index tags as could possibly be needed, and then use SEEK? First, each index tag requires disk space, and complex tags require an inordinate amount of space-space that might be needed more urgently in another part of the application. Second, and more importantly, each index tag imposes overhead because it needs to be maintained every time an add, edit, or delete operation is performed on the file. If a file has dozens and dozens of tags, day-to-day performance can be slowed considerably-and for what benefit? Why maintain tags that are rarely, or even never, used?

The syntax of the LOCATE command requires an expression made up of one or more fields in the table, an operator, and a value. A LOCATE command can have multiple conditions if desired, while a similarly complex SEEK command would need a correspondingly complex index tag. The LOCATE command that corresponds to the earlier search for "WINDWARD" would look like this:

m.cSeekString = 'Windward'

locate for cStreetName = m.cSeekString

Note that the same situation with possible case problems exists. You could simply convert both the expression and the value to uppercase:

m.cSeekString = 'Windward'locate for upper(cStreetName) = upper(m.cSeekString)

As mentioned earlier, one of the benefits to using LOCATE is that you can create multiple conditions. For instance, suppose you wanted to find the Windward street in the northeastern United States (where the zip code began with a "0"). You could use the following command:

m.cSeekString = 'Windward'
m.cZipSeekString = '0'
locate for cZip = m.cZipSeekString ;

and upper(cStreetName) = upper(m.cSeekString)

However, a command with multiple conditions separated by an AND will be evaluated more quickly if the condition most likely to be false is placed first. In other words, suppose you have four conditions all separated by ANDs. If one of those conditions is much more likely to be false, place it at the beginning, because the command will terminate as soon as that condition becomes false. After all, because the definition of an AND is that all of the conditions have to be true, why bother continuing once you find a part that is false?

Note that the LOCATE command finds only the first record that meets the condition. In order to find additional records that meet the condition, use the CONTINUE command repeatedly to find subsequent matches.

Modifying data

There are three things you generally want to do to the data in a table: add new records, edit existing records, or delete existing records. These three functions together are generally called "maintenance" functions, and Visual FoxPro contains many seemingly redundant commands that provide these capabilities. Some commands are legacies to a smaller and leaner language, while others are newer and more efficient. In addition, some commands are intended to be used in the interactive mode while others are used only inside a program (programmatically). We'll look at each command and discuss when and where it should be used.

By the way, there are also a series of SQL commands that perform these same operations- I'll cover them in Chapter 4 along with the rest of VFP's SQL commands.

Adding records interactively

You can add a record while in Browse or Edit mode by pressing Ctrl+Y. Doing so will add a record at the bottom of the browse/edit window in which the data for the new record can be entered.

You can use the Append Mode command in the View menu when you have a Browse or Edit window open, to add multiple records without having to press Ctrl+Y for each new record. First, select the Append Mode menu option. A blank record will be added to the table. After entering data into at least one field, pressing the down arrow will automatically add another record. You can continue adding records just by pressing the down arrow as long as you've entered data into at least one field of the most recent record. (This requirement exists so those who park a finger on the down arrow key don't accidentally add 15 blank records by mistake.)

Note that while the record appears to have been added to the bottom of the table, any open index is automatically updated. Once you move the cursor beyond the top or bottom of the Browse window and return to that record, you will see the record in its correct location relative to the other records.

Adding records programmatically

There are two methods to add records to a table in a program. The first way is to add a blank record to the table, and then replace the empty values in that record with data values from the user. The second way is to use the SQL INSERT command to insert a record already populated with data-as I said earlier, I'll discuss that in Chapter 4. The first method has been in use since the first version of dBASE II, and is only now giving way to the more efficient insert method.

There really isn't much to the first method-using the APPEND command to add a record to a table. Period. Open a table, enter APPEND BLANK command. (If you just type APPEND, you'll be placed in an open editing window on a blank record-which you wouldn't want to do in a program.) Once the command is finished executing (about a tenth of a blimptosecond later), there's a new record at the end of the table and it's the current record. From then on, you can treat it as you would any other record that you edit. See the following section on editing records for information about putting data in an empty record.

Adding a batch of records from another source

You can also add a group of records from another table or a non-.DBF file with the APPEND FROM command. Note that as of Visual FoxPro 6.0, if DELETED is ON, deleted records aren't added, and if DELTED is OFF, deleted records in the source table are added and the deletion flag stays-in previous versions, the deletion flag was removed once the deleted records were part of the new table.

Suppose you have a table called CUSTOMER that contains all past and present customers for your organization. Another department does a mailing and comes up with a list of potential customers. You don't want to just add that entire list-instead, you'd like to get rid of existing customers first so that they're not added twice.

Your cohort in the other department is an industrious sort and has already gone through the list and deleted the records for current customers. Then you get the list and simply use the APPEND FROM command to add it to your CUSTOMER table:

use PROSPECT

* next command returns the value '100'count

* next command returns the value '77'

* (23 records in PROSPECT where deleted)count for ! deleted()
select 0
use CUSTOMER

* next command returns the value '500'count

* next command also returns '500'count for ! deleted()

* message '100 records added' appears after the following

* command is completedappend from PROSPECT

* returns the value '600'count

* also returns '600'count for ! deleted()

* note that the deleted flag for those 23 records

* in the PROSPECT table has been removed when they

* were brought into the CUSTOMER table

The COPY TO command can be used to create a new file with a subset of records from the current table. This example copies customers whose last date of purchase was at least 1,000 days ago:

use CUSTOMER

* returns the value '500'count
copy to OLDCUST ;

for dLastPurch < date() - 1000sele 0 use OLDCUST

* returns the value '128'count

Deleting records interactively

You can delete a record in Browse or Change mode by pressing the Ctrl+T keystroke combination, or by clicking on the hollow rectangle to the left of the first field in the record.

Deleting records programmatically

You can also delete one or more records by using the DELETE command, including a scope or range of records. Using DELETE without any additional parameters just deletes the current record. The following command would delete the records of all individuals born after Richard Nixon's election:

delete for dBirth >

Naturally, deleting records in an actual application is more complex, because we're often going to have to handle business rules along the way. For example, if we delete an organization from the ORG table, how should we deal with the individuals in the IND table related to that organization? Do we let them float free or do we delete them as well? And what about their phone numbers?

Of course, there are related issues when adding records: when we add a person to the IND table, do we have to add a company to the ORG table as well?

These issues all fall under the heading of "Referential Integrity." If you're serious about RI, you'll want to check out the new and very improved RI Builder that Steve Sawyer wrote- see Effective Techniques for Application Development with Visual FoxPro 6.0 by Jim Booth and Steve Sawyer (Hentzenwerke Publishing, 1998).

Nonetheless, the mechanisms we'll use for adding and deleting don't change-just the environment and rules under which we use those mechanisms.

Handling deleted records: recalling and packing

It's important to know that deleting a record doesn't actually remove it from the table. Rather, it sets a "delete" flag in the record. You can issue the command SET DELETED ON to tell Visual FoxPro to ignore all records marked for deletion when browsing, printing reports, and so on.

You can unmark a deleted record by pressing the Ctrl+T keystroke combination when in a Browse or Edit window, or by clicking on the filled-in rectangle to the left of the first field in a deleted record. You can also programmatically "undelete" by using the RECALL command, which is similar to the DELETE command. For example, the following command will undelete all those records tagged for deletion in the Richard Nixon example:

recall for dBirth >

You can permanently remove deleted records from a table by using the PACK command. To PACK a table, you must have Exclusive use of it, either by issuing the SET EXCLUSIVE ON command and then opening the table, or by including the EXCLUSIVE clause to the USE command:

use CUSTOMER exclusive pack

Issuing the PACK command will result in an "Are you sure?" dialog. Note that there's no "unpack" command-packing a table actually performs the following steps:

1. Renames the existing table with a .BAK extension.

2. Copies the structure to a new .DBF file.

3. All records not marked for deletion are appended to the new file.

4. The indexes are rebuilt, and finally the .BAK file is deleted.

It is important to realize that, as a result, there is no "unpack" command. You cannot get the records back-period.

Many experienced developers don't use the PACK command to remove

deleted records because of the potential for data corruption during the process. Instead, they use a routine that essentially does the same thing. This routine looks like this:

use CUSTOMER && CUSTOMER has some deleted records

copy to TEMPCUST for NOT deleted()

delete file CUSTOMER.DBF

delete file CUSTOMER.CDX

delete file CUSTOMER.FPT

rename TEMPCUST.DBF to CUSTOMER.DBF

rename TEMPCUST.FPT to CUSTOMER.FPT

<run routine to recreate indexes>

(Note that the DELETE command requires the FILE keyword but the RENAME command does not. The ERASE command does the same thing as DELETE but doesn't require the FILE keyword. Isn't Fox lovely-always seven ways to do anything!) The difference is that, as a developer, you control when you delete the original file containing the deleted records and can delete the file once you've made sure that the new table without the deleted records has been created successfully.

An alternative to PACKing the table to get rid of deleted records is

the use of a technique called 'Record Recycling.' In this technique, each time a record is deleted, the contents of the record are blanked out. Then, when the user wants to add a new record, the system first looks for an (empty) deleted record. If one is found, the system undeletes it and presents it to the user as a 'newly added' record. If the empty deleted record is not found, the system will go ahead and add a brand new record.

While this is somewhat more complex, in large systems with a lot of

transaction activity, or in systems where gaining exclusive use in

order to remove the deleted records is difficult, record recycling can

reduce the size of the tables by keeping the number of deleted

records to a minimum.

Editing records interactively

Editing records interactively is reasonably straightforward. The contents of a Browse or Edit window can be changed simply by typing over them. The contents are written to the table once you have moved to another record (see the following section, "How do I save the data?" for complete details). If you press Escape while still in the field, the original field contents will be preserved.

Editing records programmatically

Editing a record programmatically involves using the REPLACE or GATHER MEMVAR command to replace the old data with new data.

The REPLACE command has three pieces: the keyword itself, the old and new values, and a scope or range of records upon which the command should operate. For example, to add a new record and stuff values from memory variables into the new record:

* create variables that contain data that will be

* placed into the new record*
m.cNameFirst = 'Courtney'
m.cNameLast = 'Love'
m.dBirth =
*

* add a new record to the IND table*
use IND
append blank
*

* now stuff the values from the memory variables into the table

* the 'm.' indicates a memory variable*
replace ;

cNameFirst with m.cNameFirst, ;
cNameLast with m.cNameLast, ;
dBirth with m.dBirth

The same operation performed with a GATHER MEMVAR command doesn't save any code, but it relieves typing. REPLACE requires explicit naming of the fields to be updated, while GATHER MEMVAR will stuff the values of any memory values whose names match fields in the table. Here's the same code using GATHER MEMVAR:

* create variables that contain data that will be

* placed into the new record*
m.cNameFirst = 'Courtney'
m.cNameLast = 'Love'
m.dBirth =
*

* add a new record to the IND table*
use IND
append blank
*

* now stuff the values from the memory variables into the table*
gather memvar

Furthermore, typically when GATHER is used, a corresponding command, SCATTER, is used to create a memory variable, or the memory variable is created through a screen. By doing so, adding a new field to a table (and the corresponding screen) doesn't require updating a bunch of REPLACE commands-it comes along for the ride.

One hidden danger of the GATHER MEMVAR command is that, as it was

used in the above example, it doesn't automatically include memo fields. If you have a number of memory variables and one or more are to be gathered into a field's memo fields, you must include the MEMO clause:

gather memo memvar

As a result, I always use 'GATHER MEMO MEMVAR' even when the table doesn't have any memo fields-it's safer and the inclusion of the MEMO clause doesn't have any adverse effects when it's unnecessary.

How do I save the data?

There's a story about a fellow who attended a class in order to learn how to use a spreadsheet, but kept getting called out of the class for this phone call and that meeting. As a result, his knowledge of the tool was less than perfect. About three weeks after the class, the instructor got a panicked call from the fellow because he had run out of room in his spreadsheet. Repeated questioning jut turned up answers that made no sense, so the instructor made a courtesy call to the fellow's office.

There, on the computer screen, was the spreadsheet he had been working on-and the bottom row of the display was clearly the middle of some worksheet he had been constructing. The row number at the bottom of the screen was 16384. Quickly paging up, the instructor realized that the fellow had used the same file to build a number of separate spreadsheets-and kept paging down to start each new worksheet.

Evidently the fellow didn't create separate files for each worksheet. After asking why, the instructor's eyes grew wide. This fellow hadn't been in the classroom when the Save command-or the concept of separate files-had been explained, and somehow he had muddled through the rest of the class without picking up the idea. As a result, he had started the spreadsheet program, and just kept adding more data-and had never even saved the file once!

If you're using Visual FoxPro and Visual Studio, you're used to working with Windows, and thus you're envious of a machine that hasn't crashed in three weeks, but try to take your mind off that for a second. The point of this story is that we sometimes take saving data without a second thought. Sure, you know to save your Word documents. But when you get your e-mail, do you execute a "save e-mail" command? Of course not-it just happens automagically.

It's worthwhile to be very explicit when data is saved in Visual FoxPro.

There are two mechanisms for working with tables in Visual FoxPro: direct access and buffering. For the time being, I'm only going to address direct access, or when you have the table opened and you're editing the actual data. I'll address buffering-a more sophisticated method usually used in VFP applications-in Chapter 14, "Your First LAN Application: Building Upon Your Forms."

First of all, when you're in interactive mode, you're using direct access-you're actually editing the data. When you start editing the contents of a field in a record, Visual FoxPro locks that record automatically. No one else can make changes to that record-any field in that record-while you have it locked. However, others can read that record-as it stood before you started editing it-and they can edit other records in the table.

While you are editing the field, you are actually working on a copy of the data in the field. When you leave the field, your changes to the field you are editing are NOT yet written back to the disk. Instead, you need to close the table, leave the record by moving to another record, or execute the FLUSH command to write the contents of the field to disk. If you press Escape while still in the field, your changes will be abandoned and the contents of the field will revert to the original value.

Because this all happens behind the scenes (with the exception of explicitly FLUSHing), it's often not obvious, but it's important to understand this. As long as you're on that record (and haven't FLUSHed it to disk), other users looking at the record might be looking at "yesterday's news"-which obviously has significant ramifications for multi-user database programming.