Library: Database access (OMDB) Import: omdb.xmd

Declaration

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

Argument definitions

procedure

is the db.procedure object associated with an open procedure.

inshelf

is a shelf of stream items that contain the input and inout parameter values.

null

is a string, rather than an unattached stream, to represent a NULL value in the input parameter values.

outshelf

is a shelf of db.field items to receive output and inout parameter values, and the function procedure return.

result

is a db.field shelf attached to the attached to the result set for any SQL query executed.

statement

is a compiled db.statement object.

values

is a shelf containing the parameter data.

null

is an optional string representing null values.

record

is a shelf of db.field items bound to result set fields.

Executing a Stored Procedure

The database where the procedure is stored must be:

• open (else external exception OMDB104)
• in scope (else external exception OMDB103)

procedure must:

• exist (else external exception OMDB402)
• have a name (else external exception OMDB408)
• be open for processing requests (else external exception OMDB405)

• have an item for every input parameter (else external exception OMDB407)
• contain valid data (else external exception OMDB501)

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

Executing a Compiled SQL Statement

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:

• contain the parameters required by the compiled statement (else external exception OMDB702).
• contain the correct number of parameters (else external exception OMDB703).

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.

See db.compile-statement for additional notes and examples.

Related Topics Using the OMDB library db.database db.open-procedure db.procedure

Other Library Functions db.advance-recordset db.close db.commit db.compile-statement db.database db.delete db.discard db.document-writer-sedna db.execute db.execute-in db.field db.insert db.is-null db.move-dynamic-record db.move-record db.omdb-version db.omdb-version-oci10g db.omdb-version-oci11g db.omdb-version-oci8i db.omdb-version-oci9i db.omdb-version-odbc db.omdb-version-sedna db.open-oci10g db.open-oci11g db.open-oci8i db.open-oci9i db.open-odbc db.open-procedure db.open-sedna db.open-table db.prepare db.procedure db.query db.reader db.record-exists db.rollback db.set-transaction-type db.statement db.streaming-execute db.table db.update db.writer

[ INDEX ] [ CONCEPTS ] [ TASKS ] [ SYNTAX ] [ LIBRARIES ] [ LEGACY LIBRARIES ] [ ERRORS ]