db.query

function

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      integer     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

Purpose

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'