swirl
Guide to OmniMark 7   OmniMark home
docs home 
IndexConceptsTasksSyntaxLibrariesLegacy LibrariesErrors
 
      Other Library Functions  
function  

dbQuery

 
 

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.


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
   dbClose
   dbCommit
   dbDatabase
   dbExecute
   dbField
   dbFieldPrepare
   dbFieldSink
   dbFieldValue
   dbIsVersionCompatible
   dbLibraryVersion
   dbLibraryVersionOCI8
   dbLibraryVersionOCI8i
   dbLibraryVersionODBC
   dbOpenOCI8
   dbOpenOCI8i
   dbOpenODBC
   dbProcedure
   dbProcedureClose
   dbProcedureExecute
   dbProcedureOpen
   dbQuery
   dbRecordExists
   dbRecordMove
   dbRecordSetMove
   dbRollback
   dbSetTransactionType
   dbStatement
   dbStatementCompile
   dbStatementDiscard
   dbStatementExecute
   dbStatementStreamingExecute
   dbTable
   dbTableClose
   dbTableDelete
   dbTableInsert
   dbTableOpen
   dbTableUpdate
 
 

Top [ INDEX ] [ CONCEPTS ] [ TASKS ] [ SYNTAX ] [ LIBRARIES ] [ LEGACYLIBRARIES ] [ ERRORS ]

OmniMark 7.1.2 Documentation Generated: June 28, 2005 at 5:45:06 pm
If you have any comments about this section of the documentation, send email to [email protected]

Copyright © Stilo Corporation, 1988-2005.