Other Library Functions

dbQuery

function

Library: Database access (OMDB legacy)
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.


Purpose

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