 |
 |
|
|
       |
|||
|
||||||||||
|
|
||||||||||
| Other Library Functions | ||||||||||
| function | dbQuery |
|
Library: omdb - high level database access
Include: omdb.xin |
define external function dbQuery
value dbDatabase database
SQL value stream query
record modifiable dbField record
Where:
database
statement
record
The dbQuery function executes an SQL statement on a database. The data cursor is positioned on the first row of the result set, assuming a row exists. An exception is raised if the statement is not a valid query.
This function communicates with the database through the same dbDatabase OMX variable that you used to create the connection to the database. This function also requires a dbField OMX shelf. 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".
For 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 " ||
"order by C.CID, SC.Grade desc"
}
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'
| ---- |
