Library: Database access (OMDB) legacy library Include: omdb.xin

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

Declaration

  define external dbStatement function dbStatementCompile
         value    dbDatabase  database
     SQL value    stream      statement

  or

  define external  dbStatement function  dbStatementCompile
         value     dbDatabase  database
     SQL value     stream      statement
   types read-only integer     datatypes

Argument definitions

database

is a dbDatabase object representing an open database connection.

statement

is the SQL statement with optional parameter placeholders.

datatypes

is the data types of the parameters.

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

Requirements

You must include the following line at the beginning of your OmniMark program:

  include "omdb.xin"

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 for date, time, or timestamp values
• DB_TEXT for textual values
• DB_BINARY for binary values
• DB_INTEGER for integer number values
• DB_FLOAT for floating point numbers
• DB_LONGTEXT for long text
• DB_LONGBINARY for long binary values
• DB_BLOB for BLOB values
• DB_CLOB 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 is the same as DB_LONGBINARY, and DB_CLOB 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.

  include "omdb.xin"
     process
        ; local variables
        local dbStatement Fred
        local stream SQL-insert initial
                { "insert into Course (cid, CourseName) "
                   || "values ('789', 'Stargazing')"
                }
        local dbDatabase this-db

        ;  create the database OMX objects
        set this-db to dbOpenODBC 'dbDemo'

        ;   main function call - compile the SQL
        set Fred to dbStatementCompile this-db sql SQL-insert

        ;   now execute the compiled SQL
        dbStatementExecute Fred

Example #2

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

     include "omdb.xin"

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

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

        ;  create types shelf
        set new param-types{"cid"} to DB_INTEGER
        set new param-types{"CourseName"} to DB_TEXT

        ;   main function call - compile the SQL
        set Fred to dbStatementCompile 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
        dbStatementExecute Fred with param-data

Other Library Functions    dbClose    dbCommit    dbDatabase    dbExecute    dbField    dbFieldPrepare    dbFieldSink    dbFieldValue    dbIsVersionCompatible    dbLibraryVersion    dbLibraryVersionOCI8    dbLibraryVersionOCI8i    dbLibraryVersionODBC    dbOpenOCI8    dbOpenOCI8i    dbOpenODBC    dbProcedure    dbProcedureClose    dbProcedureExecute    dbProcedureOpen    dbQuery    dbRecordExists    dbRecordMove    dbRecordSetMove    dbRollback    dbSetTransactionType    dbStatement    dbStatementCompile    dbStatementDiscard    dbStatementExecute    dbStatementStreamingExecute    dbTable    dbTableClose    dbTableDelete    dbTableInsert    dbTableOpen    dbTableUpdate

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