Command: dbtrans

Syntax

dbtrans <dbase> {
    <db_command>
    ...
}

Description

Execute a database transaction 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

For the command block to execute correctly, the external database should have been created/started with transaction logging enabled.

The command block is treated as a single database transaction. At the beginning of the block the equivalent of an SQL begin work statement is sent to the database. At the end of the block, if no database errors occurred during execution of the block, an SQL commit work is sent. If errors did occur, an SQL rollback work is sent instead. A database error is an error incurred by a *command sent to the database. Errors in non-database commands will not trigger a rollback.

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:

* 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.

dbtrans command blocks may be nested to a depth of 16, but the commit/rollback action is performed only at the end of the outermost dbtrans block. If all inner block database operations are successful then a commit is performed on all relevant databases, otherwise rollbacks are performed.

The system numeric dberr, maintained by the system, records whether any database errors have occurred during execution of the current dbtrans command block. It is reset to zero at the beginning of a dbtrans command block. Setting dberr within a dbtrans block provides further control over whether a commit or a rollback action is performed at the end of the block.

The db command, which is very similar to dbtrans, executes a command block without the transaction capability.

Examples

# Block of sub-commands.
# School is a database identifier already set up.
dbtrans school {
    string name = "Alison Ordnung"
    numeric age = 18
    string class = 'Upper VI'
  * INSERT INTO pupils (name,age,class) \ 
        VALUES (:name,:age,:class)
}
if (dberr) !Could not enter record

See Also