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

Declaration

define external  function db.query
         value       db.database  database
   sql   value       stream      query
   into  modifiable  db.field     record
   cursor value int32 cursor optional

Argument definitions

database

is a db.database object obtained by opening a connection to a database using one of the db.open functions.

statement

is the SQL query you want to execute on the database.

record

is a shelf of db.field items that will be bound to the result set fields.

cursor

specifies whether the result set has a static or dynamic cursor

Use db.query to execute a query statement on a database.

Requirements - Relational Database

The db.database object database must represent an existing database connection (else external exception OMDB101).

The SQL statement supplied for query must be a valid SQL statement (else external exception OMDB501).

Usage Notes

The connection represented by db.database must remain open for as long as you are using the record set produced by the db.query.

The data cursor is positioned on the first row of the result set, assuming a row exists.

External exception OMDB202 is thrown if the query does not produce any columns.

The result set produced by the db.query function is attached to the db.field OMX shelf variable. Each item on the db.field OMX shelf may be used to access the value of a queried field in the current row of the result set. If the database is a relational one rhen the key of each shelf item is the unique name of the corresponding field. For XML databases the shelf items have no key.

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.

When querying a relational database the key name of the field item is set to the queried column name. If that key name already exists in the db.field shelf, a suffix is added to the key name to make it unique. The suffix consists of an asterisk followed by the occurrence count. For example, the first column in the query named "StudentID" will correspond to the db.field item with the key "StudentID". The second occurrence of a column named "StudentID" in the query will correspond to the db.field item with a key named "StudentID*2".

When querying an XML database using XQuery the resulting record set is slightly different from the one returned by a query to a relational database. The db.field shelf will only contain 1 item; XML databases do not have columns. The db.field shelf item will not be keyed because there are no columns and therefore no column names. An XQuery can return multiple items. Use the the db.move-record and db.record-exists functions to access them the same way that rows in a relational databse record set are accessed.

Example - Relational Database:

  import "omdb.xmd" prefixed by db.
  
  process
     local db.database my-database
     local db.field my-query variable
  
     local stream SQL-query initial
        { "select C.CourseName, S.StudentName, SC.Grade " ||
          "from Student S, Course C, StudentCourse SC " ||
          "where SC.CID = C.CID and S.SID = SC.SID "
        }
  
     set my-database to db.open-odbc "DatabaseDemo"
  
     db.query my-database SQL sql-query into my-query
  
     repeat over my-query
        output key of my-query
        output '%t' when ! #last
     again
     output '%n' || ( '-' repeated 45 ) || '%n'
  
     repeat
        exit unless db.record-exists my-query
        repeat over my-query
           output db.reader of my-query null '-DNF-'
           output '%t' when ! #last
        again
        output '%n'
        db.move-record my-query
     again
  
     catch #external-exception identity catch-id message catch-msg
        output 'An error occurred while accessing an OMDB function.%n'
        output '%g(catch-id) : %g(catch-msg)%n'

Related Syntax    db.open-odbc    db.execute-in    db.move-record    db.record-exists    db.reader    db.field    db.database    db.compile-statement    dbStatementExecute    db.discard    db.statement    db.open-oci8i    db.move-dynamic-record

Related Concepts    Using the OMDB library

Other Library Functions    db.advance-recordset    db.close    db.commit    db.compile-statement    db.database    db.delete    db.discard    db.document-writer-sedna    db.execute    db.execute-in    db.field    db.insert    db.is-null    db.move-dynamic-record    db.move-record    db.omdb-version    db.omdb-version-oci10g    db.omdb-version-oci11g    db.omdb-version-oci8i    db.omdb-version-oci9i    db.omdb-version-odbc    db.omdb-version-sedna    db.open-oci10g    db.open-oci11g    db.open-oci8i    db.open-oci9i    db.open-odbc    db.open-procedure    db.open-sedna    db.open-table    db.prepare    db.procedure    db.query    db.reader    db.record-exists    db.rollback    db.set-transaction-type    db.statement    db.streaming-execute    db.table    db.update    db.writer

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