Library: Interfaces, 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

Argument definitions

database

is a db.database object obtained by opening a connection to a database using one of the dbOpen 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.

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

Requirements

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

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. The key of each shelf item is the unique name of the corresponding field.

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

Example:

  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-oci8    db.open-oci8i

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.execute    db.execute-in    db.field    db.insert    db.move-record    db.omdb-version    db.omdb-version-oci8    db.omdb-version-oci8i    db.omdb-version-odbc    db.open-oci8    db.open-oci8i    db.open-odbc    db.open-procedure    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 ] [ LEGACYLIBRARIES ] [ ERRORS ]