db.compile-statement

function

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

Returns: A db.statement for the compiled SQL statement.


Declaration
export external statement function  
   compile-statement     in value     database db-connection
                        sql value     string   sql-statement
                      types read-only integer  datatypes optional
                     cursor value     integer  cursor    optional

      

Argument definitions

db-connection
is a db.database object representing an open database connection.
sql-statement
is the SQL statement with optional parameter placeholders.
datatypes
is the data types of the parameters.
cursor
specifies whether the result set has a static or dynamic cursor.


Purpose

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

Requirements

The database connection represented by db-connection must be:

The sql-statement must be a valid SQL statement, otherwise exception OMDB501 will be thrown.

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

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

Usage Notes - General

If the cursor parameter is not specified or is set to db.static-cursor, then only db.move-record can be used to advance the cursor. If db.dynamic-cursor is specified, then db.move-dynamic-record can be used. It is not recommended to specify a dynamic cursor unless the functionality of db.move-dynamic-record is required, as dynamic cursors are more expensive.

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

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.

  import "omdb.xmd" prefixed by db.
  
  process
     local db.database  d
     local db.statement s
     local string       sql-insert initial { "insert into Course (cid, CourseName) values ('789', 'Stargazing')" }
  
     set d to db.open-odbc "dbDemo"
     set s to db.compile-statement in d sql sql-insert
  
     db.execute s

Example #2

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

  import "omdb.xmd" prefixed by db.
  
  process
     local db.database  d
     local db.statement s
     local string       sql-insert initial { "insert into Course (cid, CourseName) values (:1, :2)" }
     local integer      parameter-types variable
     local stream       parameter-data  variable
  
     set d to db.open-oci11g "dbDemo" user "charley" password "chaplin"
  
     set new parameter-types{"cid"} to db.integer-type
     set new parameter-types{"CourseName"} to db.text-type
  
     set s to db.compile-statement in d sql sql-insert types parameter-types
  
     set new parameter-data{"cid"} to "789"
     set new parameter-data{"CourseName"} to "Stargazing"
  
     db.execute s with parameter-data