swirl
Guide to OmniMark 9   OmniMark home
docs home 
IndexConceptsTasksSyntaxLibrariesLegacy LibrariesErrors
 
  Related Topics   Other Library Functions  
function  

db.compile-statement

 
 

Library: Database access (OMDB)
Import: omdb.xmd
Return type: db.statement

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     string      statement
                         types read-only integer     datatypes optional
                        cursor value     integer     cursor 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.
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 a db.statement OMX with a structure that you define.

Requirements

The database connection represented by database must be:

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:

The keys for the datatypes shelf are ignored - only 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.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 Topics
 
Other Library Functions
 
 

Top [ INDEX ] [ CONCEPTS ] [ TASKS ] [ SYNTAX ] [ LIBRARIES ] [ LEGACY LIBRARIES ] [ ERRORS ]

OmniMark 9.1.0 Documentation Generated: September 2, 2010 at 1:38:10 pm
If you have any comments about this section of the documentation, please use this form.

Copyright © Stilo International plc, 1988-2010.