You read records into the CopiaFacts user variable space after selecting them first by using the following command:

$set_var DB_ACTION get_record

This command retrieves the next record from the set of records retrieved. Fields from the record are placed in variables in the user variable space. For example, suppose we have a database with the following fields: NAME, ADDRESS, CITY, STATE, ZIP, and AGE. Suppose we want to select the records for people who live in Chicago. We might do the following:

$set_var DB_FILE "CLIENTS%=C:\My Documents\Clients.dbf"

$set_var DB_SELECT "CITY=Chicago"

Assuming that DB_RESULT returned a value greater than zero, we could use the following command to read the first record:

$set_var DB_ACTION get_record

After CopiaFacts read the record we would have the following CopiaFacts variables set with the sample data:

CLIENTS%NAME      John Smith

CLIENTS%ADDRESS   1400 State Street

CLIENTS%CITY      Chicago

CLIENTS%STATE     IL

CLIENTS%ZIP       60006

CLIENTS%AGE       43

These variables may be used just like any other CopiaFacts variables, for example:

$set_var ROUTETO @CLIENTS%NAME

Important: if any of the variables receives a database 'memo' field containing newline sequences, these must be deleted or converted as described in the Large Text Fields topic.

If we wanted to update this record we might change the AGE field as follows:

$set_var CLIENTS%AGE 45

$set_var DB_ACTION update_record

This would change John's age and then post the record. All other fields would be unchanged. If we wished to delete that record instead, we would just do the following command:

$set_var DB_ACTION delete_record

In order to append a new record, you would have to create a record in the user variable space first and then append it. You might do the following:

$set_var CLIENTS%NAME "Eleanor Smith"

$set_var CLIENTS%ADDRESS "123 Main Street"

$set_var CLIENTS%CITY "Anytown"

$set_var CLIENTS%STATE "ME"

$set_var CLIENTS%ZIP 12345

$set_var CLIENTS%AGE 34

$set_var DB_ACTION append_record

After any assignment to DB_ACTION, you should always check the value in the DB_RESULT variable to see if the action worked.

For update, delete, and append operations you should be working with a table resource. You will get unpredictable results otherwise. Table resources include a list of field or column names for the table which are used in append operations and as a default for update operations. You may be able to perform delete, append, and update operations using SQL statements without using a table resource. However, if you perform these operations using the system variable DB_ACTION, you must use a table resource defined in the DB_FILE command.