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:

open, otherwise external exception OMDB101 will be thrown, and
in scope, otherwise external exception OMDB103 will be thrown.

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:

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
db.yminterval-type for Oracle year-month intervals
db.dsinterval-type for Oracle day-second intervals

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

Related Topics

Other Library Functions