Library: Database access (OMDB)
Import : omdb.xmd
define external function db.execute value db.procedure procedure in read-only stream inshelf optional null value string null optional out modifiable db.field outshelf optional result modifiable db.field result optional or define external function db.execute value db.statement statement with read-only stream values optional null value string null optional into modifiable db.field record optional
The database where the procedure is stored must be:
At the beginning of the function, the output shelf is cleared and filled with db.field items attached to the output and inout (combined input and output) procedure parameters. The input shelf items are sequentially matched to the procedure input and inout parameters by position. You can represent NULL values in the input shelf item by an optional null specifier string or as an unattached stream.
The result shelf can contain multiple records. The input and output shelves can only contain a single record.
The result shelf is cleared in the function. If the procedure returns a result set the result shelf is filled with db.field items attached to the result set.
The output and result db.field field items are keyed with the corresponding procedure parameter names, if they exist.
If a record used by the procedure has a date, time, or timestamp field, you must represent the field's value in the OmniMark Date and Time library format. (This format returns the time with a time zone offset from UTC time, which most databases do not provide.)
Use db.execute to execute a compiled SQL statement that was produced by the db.compile-statement function.
The db.database object supplied when the statement was compiled must be open (else external exception OMDB101).
The db.statement object statement must be open (else external exception OMDB701).
If the db.statement object is a compiled query, it requires the db.field shelf argument record to receive the results (else external exception OMDB704).
The input shelf values must:
When you call db.execute, you must supply data for all the defined parameters. You can express null data values as unattached shelf items or as the supplied null identifier string.
db.execute allows you to execute a compiled statement and to specify all the parameter values at once. This is more efficient for small parameters.
For large parameters (for example, db.blob-type types) when there is a large amount of data to be inserted, it is more efficient to use the db.streaming-execute function, as that avoids creating an entire copy of the data in memory prior to execution.
db.compile-statement for additional notes and examples.