function
Library: Database access (OMDB)
Import : omdb.xmd |
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
Use db.query to execute a query statement on a 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).
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.
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'