This section describes record selection for tables. Database reference associated with a stored procedure are documented separately. Once you have assigned a database reference, you can begin selecting and processing records. You may select records using a specific field or you may use an SQL command to select one or more records. Your database provider may restrict the use of certain SQL statements. It is a good idea to find out if your database does have any limitations before using SQL commands.
You may select records based upon the contents of a field or key by using the following command:
$set_var DB_SELECT "field=value"
The field parameter must specify a valid field within the table you are accessing. The value parameter should be a value you are searching for within the particular column. You cannot use this form of the DB_SELECT command when you have used *DUMMY* in place of a table name because no field names are retrieved for dummy table placeholders. Therefore, the field name cannot be validated. Once the field name is validated, CopiaFacts will perform an SQL query to retrieve the records. Some providers do not support indexing support and some providers will use an index to speed an SQL query. So this method usually proves effective. The number of records matching your request will be returned in DB_RESULT.
In order to execute an SQL query you would submit a one of the following commands:
$set_var DB_SELECT "SQL=sqlcommand"
$set_var DB_SELECT "SQL2=sqlcommand"
The first command is used for queries that return a record set. If the query specified does not return a record set, then this command will cause an unspecified ADO error (-18) with a message from the database provider indicating no record set was returned. The second command should be used to execute queries or DDL commands that do not return a record set. Any record set returned by a query using this command will be discarded. The number of records selected or affected is returned in DB_RESULT. If an error occurs, then a negative error number is returned in DB_RESULT.
There are two other forms of the select command that are used to determine what fields are retrieved or written when records are read or added to and from your variable name space. Normally, all fields, except binary, BLOB, graphic, auto-increment, and aggregate fields are returned when you retrieve records or updated when you write records. However, you can restrict or extend the fields used by the following commands:
$set_var DB_SELECT "USE=field1;field2…fieldn"
$set_var DB_SELECT "USE2=field1;field2…fieldn"
$set_var DB_SELECT "EXCLUDE=field1;field2…fieldn"
Use the first two commands to name the fields you wish to return when you read records or to update when you update records. The first command (USE) only allows you to select supported field types, i.e., those fields that do not contain binary, BLOB, graphic, auto-increment, or aggregate values. The second command (USE2) allows you to include restricted field types. There are some caveats for processing restricted fields (see section below). You may need to use the exclude command to bypass fields that should not be updated when a record is added, especially if the database provider does not provide enough field type information for us to determine that a field should not be updated. Each field name in the list part of the command is separated from the others in the list by a semi-colon or comma (all delimiters in a single statement must be the same). When you append a record, CopiaFacts will still try to retrieve all fields from your variable name space unless the fields are excluded, regardless of the use statement. So you may wish to exclude fields that are updated by the database, such as auto-increment fields, if you find that the software is unable to determine that the field is auto-increment. By default, all of the fields defined for a table, except for restricted field types, will be used unless you modify the list with the USE or USE2 command.