Command: db

Syntax

db <dbase> {
    <db_command>
    ...
}

Description

Execute a database command block.

Parameters

• <dbase>
  An existing database identifier linked to an external database.

• <db_command>
  A command line containing either:

1. Language commands to be executed, or

2. A statement to be sent to the database interface. This should start with a * character, and is typically an SQL statement.

Switches

• -ab
  When this switch is present, database errors detected within any <db_command> will result in subsequent commands in the block being ignored. Use -no_ab to turn this feature off.
  When this switch is not present, the default behavior is determined by the value of the system numeric session_abort_on_error, which is set to true at program start.

Notes

In addition to standard SQL statements, which are passed directly to the database, there are two special forms of the SQL FETCH statement.

They have the following syntax, either:

* FETCH INTO <ident> { ,<ident> ... }

where <ident> is one or more language identifiers whose values are to be retrieved from the database.

and

* MULTIFETCH {<num>} INTO <array> { ,<array> ... }

where <array> is one or more language identifier arrays whose values are to be retrieved from the database. If <num> is specified, then num rows are fetched, otherwise all rows corresponding to the current SELECT criteria are read. Each <array> is filled by a column of data and the sizes of variable-length arrays are adjusted accordingly.

In SQL statements which require identifier values, language identifiers may be specified by preceding them with a : character, e.g.

* INSERT INTO mytable VALUES(:x, :y[55], :sss)

Such language variables may be array expressions, e.g. y[55] above, but must not contain any embedded white space. The only permitted identifier types are numeric, string, time and point. An attempt to output other types of identifier will produce an error. Colons within quoted strings are treated literally and are not assumed to prefix language variables. Numeric values will be expanded with trailing zeros stripped off, string values will be enclosed within quotes, time values will be output in a suitable form for the database, point values will be output in the form (x,y) or (x,y,z) depending on the current value of the <dbase>.dimensions attribute, which can only be 2 or 3.

For more details on SQL (Structured Query Language) statements, consult the relevant documentation on Embedded SQL for your particular database.

db command blocks may be nested to a depth of 16.

The system numeric dberr, maintained by the system, records whether any database errors have occurred during execution of the db command block. It is reset to 0 at the beginning of a db command.

The dbtrans command, which is very similar to db, executes a command block as a single transaction with commit/rollback capability.

Examples

Block of sub-commands (school is a database identifier already set up).

db school {
    string name, class; numeric age
    string s = 'Huntley'
  * SELECT * FROM pupils WHERE name = :s
  * FETCH INTO name, age, class
    string names[], classes[]
    numeric ages[]
  * SELECT * FROM pupils
  * MULTIFETCH INTO names,ages,classes
}

See Also