dbStatementCompile

function

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

Returns: a dbStatement for the compiled SQL statement

Declaration

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

Argument definitions

database: is a dbDatabase instance representing an open database connection.

statement: is the SQL statement with optional parameter placeholders.

datatypes: is the data types of the parameters.

Purpose

Use dbStatementCompile to compile an SQL statement. The function returns an instance of type dbStatement 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), and
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.

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 dbOpenOCI11g '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