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

Return type: dbStatement Returns:  A db.statement for the compiled SQL statement.

Declaration

  define external  db.statement function  db.compile-statement in
         value     db.database  database
     sql value     stream      statement
   types read-only integer     datatypes optional

Argument definitions

database

is a db.database object representing an open database connection.

statement

is the SQL statement with optional parameter placeholders.

datatypes

is the data types of the parameters.

Use db.compile-statement to compile an SQL statement. The function creates a db.statement OMX with a structure that you define.

Requirements

The database connection represented by database must be:

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

The statement must be a valid SQL statement (else exception OMDB501).

datatypes is an integer shelf, each item of which can be any of the following values:

• db.datetime-type for date, time, or timestamp values
• db.text-type for textual values
• db.binary-type for binary values
• db.integer-type for integer number values
• db.float-type for floating point numbers
• db.longtext-type for long text
• db.longbinary-type for long binary values
• db.blob-type for BLOB values
• db.clob-type for CLOB values

The keys for the datatypes shelf are ignored - only order matters.

Usage Notes - ODBC

The statement may contain parameter placeholders, each identified by a "?". You may specify the data type of each parameter, or you can let the system automatically determine the parameter data type. In the second case, an exception is thrown if the ODBC driver cannot determine the data type.

db.blob-type is the same as db.longbinary-type, and db.clob-type is the same as DB_LONGTEXT.

Usage Notes - OCI

The statement may contain parameter placeholders, each identified by a colon ':' followed by a unique name. A common technique is to name the first parameter ":1", the second ":2", and so on.

The data type of each parameter must be specified.

LOB parameters are only supported with OCI8i.

Example #1

Compile a simple statement with no parameters using the ODBC interface, and execute it.

  import "omdb.xmd" prefixed by db.

  process
     local db.statement Fred
     local stream SQL-insert initial
             { "insert into Course (cid, CourseName) "
                || "values ('789', 'Stargazing')"
             }
     local db.database this-db

     ;  create the database OMX objects
     set this-db to db.open-odbc 'dbDemo'

     ;   main function call - compile the SQL
     set Fred to db.compile-statement in this-db sql SQL-insert

     ;   now execute the compiled SQL
     db.execute Fred

Example #2

Compile a statement with parameters using the OCI interface and execute it.

  import "omdb.xmd" prefixed by db.

  process
     local db.statement Fred
     local stream SQL-insert initial
             { "insert into Course (cid, CourseName) "
                || "values (:1, :2)"
             }
     local db.database this-db
     local integer param-types variable
     local stream param-data variable

     ;  create the database OMX objects
     set this-db to db.open-oci8i 'dbDemo' user 'charley' password 'chaplin'

     ;  create types shelf
     set new param-types{"cid"} to db.integer-type
     set new param-types{"CourseName"} to db.text-type

     ;   main function call - compile the SQL
     set Fred to db.compile-statement
       in this-db
       sql SQL-insert
       types param-types

     ;  set up data to be inserted
     set new param-data{"cid"} to "789"
     set new param-data{"CourseName"} to "Stargazing"

     ;   now execute the compiled SQL
     db.execute Fred with param-data

Related Syntax    db.execute-in    db.query    db.field    db.database    dbStatementExecute    db.discard    db.statement    db.streaming-execute

Related Concepts    Using the OMDB library

Other Library Functions    db.advance-recordset    db.close    db.commit    db.compile-statement    db.database    db.delete    db.discard    db.execute    db.execute-in    db.field    db.insert    db.move-record    db.omdb-version    db.omdb-version-oci8    db.omdb-version-oci8i    db.omdb-version-odbc    db.open-oci8    db.open-oci8i    db.open-odbc    db.open-procedure    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 ] [ LEGACYLIBRARIES ] [ ERRORS ]