Library: Database access (OMDB) legacy library Include: omdb.xin

Declaration

define external  function dbQuery
         value       dbDatabase  database
   SQL   value       stream      query
 record  modifiable  dbField     record

Argument definitions

database

is a dbDatabase 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 dbField items that will be bound to the result set fields.

Use dbQuery to execute an SQL statement on a database.

Requirements

You must include the following line at the beginning of your OmniMark program:

  include "omdb.xin"

The dbDatabase 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 dbDatabase 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 dbQuery function is attached to the dbField OMX shelf variable. Each item on the dbField 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 dbField 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 dbField item with the key "StudentID". The second occurrence of a column named "StudentID" in the query will correspond to the dbField item with a key named "StudentID*2".

Example:

        include "omdb.xin"
  
        process
           local dbDatabase my-database
           local dbField 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 dbOpenODBC "DatabaseDemo"
  
          dbQuery my-database SQL SQL-query record my-query
  
           repeat over my-query
              output key of my-query
              output '%t' when ! #last
           again
           output '%n' || ( '-' repeated 45 ) || '%n'
  
           repeat
              exit unless dbRecordExists my-query
              repeat over my-query
                 output dbFieldValue my-query null '-DNF-'
                 output '%t' when ! #last
              again
              output '%n'
              dbRecordMove 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'

Other Library Functions    dbClose    dbCommit    dbDatabase    dbExecute    dbField    dbFieldPrepare    dbFieldSink    dbFieldValue    dbIsVersionCompatible    dbLibraryVersion    dbLibraryVersionOCI8    dbLibraryVersionOCI8i    dbLibraryVersionODBC    dbOpenOCI8    dbOpenOCI8i    dbOpenODBC    dbProcedure    dbProcedureClose    dbProcedureExecute    dbProcedureOpen    dbQuery    dbRecordExists    dbRecordMove    dbRecordMoveDynamic    dbRecordSetMove    dbRollback    dbSetTransactionType    dbStatement    dbStatementCompile    dbStatementDiscard    dbStatementExecute    dbStatementStreamingExecute    dbTable    dbTableClose    dbTableDelete    dbTableInsert    dbTableOpen    dbTableUpdate

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