 |
|
|
|
||||
|
|||||
|
|
|||||
| Related Syntax | |||||
Using the OMDB library |
|||||
This document covers the following topics:
The OMDB library provides a way to interface with relational databases from your OmniMark programs. This library includes functions to:
Before you can do anything with your database from an OmniMark program, there must be an open connection to the database. A connection is made using the db.open-odbc function. To make a connection you must know:
db.open-odbc function returns a connection to the specified database as a db.database OMX component.
import "omdb.xmd" prefixed by db.
process
local db.database Targetdb
set Targetdb to db.open-odbc "Phred" user "Jeremy" password "2002the"
The database connection OMX component is typically assigned to a variable, as in the code sample above.
OMDB also supports accessing Oracle 8 (and higher) databases via OCI instead of ODBC. Which function you use depends on which Oracle client you have installed. If you have the Oracle 8 client libraries installed on your machine, then you use the db.open-oci8 function to connect to the database. If you have the Oracle 8i client libraries installed on your machine, and you are connecting to an Oracle 8i (or higher) database, then you use the db.open-oci8i function.
import "omdb.xmd" prefixed by db.
process
local db.database Targetdb
set Targetdb to db.open-oci8i "Phred" user "Jeremy" password "2002the"
The database connection OMX component is typically assigned to a variable, as in the code sample above.
An open database connection operates in one of two transaction modes:
import "omdb.xmd" prefixed by db.
process
local db.database Targetdb
local db.database MyOwnDB
set Targetdb to db.open-odbc "MarketStats"
set MyOwnDB to db.open-odbc "MarketStats"
db.set-transaction-type of MyOwnDB to db.manual-commit
Although MyOwnDB and Targetdb are both connections to the same database, each connection behaves independently. The connection Targetdb has the default transaction mode of auto-commit. In this mode, every insert, delete, or update operation is a permanent change to the database.
The connection MyOwnDB has a transaction mode of manual commit. In manual-commit mode insert, delete, and update operations are not permanent, and cannot be seen by the Targetdb connection, until they are committed using db.commit. The db.rollback function is used to cancel all uncommitted operations associated with a specific connection.
You can also create connections to databases by different methods. For example, you can have both ODBC and OCI connections open at the same time:
import "omdb.xmd" prefixed by db.
process
local db.database Targetdb
local db.database MyOwnDB
set Targetdb to db.open-odbc "MarketStats"
set MyOwnDB to db.open-oci8i "MarketStats"
A database connection is closed when:
db.close is used to explicitly close the connection.
The db.execute-in function can be used with any valid non-selecting SQL statement (that is, anything except a query). For example:
db.execute-in Targetdb
sql ("create table AvailableDownloads "
|| "(ItemID integer not null, ItemType char(6), "
|| "AccessLevel char(1), "
|| "constraint item_id primary key (ItemID));")
What is considered valid SQL will depend on the SQL server for your database.
The db.query function allows you to execute any SQL query statement against an open database. The result of the query is returned in a shelf of type db.field. The resulting value for a field is placed in a separate item on the shelf and given a unique key. The key corresponds to the name of the field in the table the queried data was obtained from.
import "omdb.xmd" prefixed by db.
process
local db.database XLdb
local db.field NewUsers variable
set XLdb to db.open-odbc "Phase2"
db.query XLdb
sql ( "select * from OldList " ) into NewUsers
In the example above, the table OldList contains three columns: Name, UserID and PhoneNum. Each row of the result set from this query will contain three items:
db.query XLdb
sql ( "select C.Name, S.Name, SC.Grade "
|| "from Student S, Course C, StudentCourse sc "
|| "where SC.CID = C.CID and S.SID = SC.SID "\
into NewUsers
Each row of the result set for this query will contain three items:
The results from a query are returned in a shelf of db.field items. Individual result items are accessed by using a unique key. In the example:
set CourseName to db.reader of NewUsers {"Name"}
set StudentName to db.reader of NewUsers {"Name*2"}
set Grade to db.reader of NewUsers {"Grade"}
previously defined variables are assigned the values from the current row of the result set. The value of an item in the result shelf is accessed using the function db.reader.
But what if the result set from the query is empty (no data met the selection criteria of the SQL query)? The first call to db.reader would result in an exception being thrown. The way to avoid this is to test if there is a record in the results set to be read before attempting to read a record.
This test is performed using the function db.record-exists. db.record-exists returns TRUE when there is a row in the results set to be read, and FALSE when there is not. For example:
do when db.record-exists NewUsers
set CourseName to db.reader of NewUsers {"Name"}
set StudentName to db.reader of NewUsers {"Name*2"}
set Grade to db.reader of NewUsers {"Grade"}
done
When a query results in multiple rows, we need to be able to move to the various rows in the result set. This is done using the function db.move-record.
repeat
exit unless db.record-exists NewUsers
set CourseName to db.reader of NewUsers {"Name"}
set StudentName to db.reader of NewUsers {"Name*2"}
set Grade to db.reader of NewUsers {"Grade"}
. . .
db.move-record NewUsers
again
This code in this example tests for a record, processes the record if the test succeeds, and then moves to the next record. As the loop repeats, the test for a record is performed again. If the move to the next row of the data set did not move beyond the boundary of the result set, the test will again succeed and processing can continue.
The function db.move-record allows you to make several types of movement through a result set. Which of these types are available for you to use in your particular case depends on the capabilities of the database driver involved. Many ODBC drivers, as well as the OCI drivers, only support a move to the next row. Other ODBC drivers allow more complex movements through the result set. For example
db.move-record NewUsers to db.first-recordrepositions the data cursor to the first row of the result set NewUsers. The code
db.move-record NewUsers by 2skips the next row, and positions the data cursor on the row following the skipped row. A complete list of the moves supported by the
db.move-record function (though not necessarily the database driver you are using) are detailed in the reference for the function. If you attempt a movement of the data cursor that is not supported by the database driver you are using, the external exception OMDB501 will be thrown.
There is often a requirement for a single SQL statement to be executed many times in a single program. The OMDB function library provides functions that allow you to pre-compile any valid SQL statement once, and then execute the pre-compiled statement. These functions offer an efficiency over the functions db.execute-in and db.query that both compile and execute a statement each time they are called.
The db.compile-statement function requires a SQL statement and a database connection as input. If the SQL statement requires input data, you can explicitly specify the data types for the input data in a shelf, or let OMDB to determine the data types automatically.
The following example shows the code to compile a SQL statement for the ODBC interface and explicitly specify the data types of the input parameters.
local db.statement CompanyInsert
local db.statement CompanyQuery
local integer inserttypes variable initial { db.text-type, db.integer-type }
local integer querytypes variable initial { db.text-type }
set CompanyInsert to db.compile-statement in Targetdb
sql "insert into Customers (CompanyName,Country) Values (?,?);"
types inserttypes
set CompanyQuery to db.compile-statement in Targetdb
sql ( "select CompanyID from Customers where CompanyName = ?" )
types querytypes
The '?''s in the SQL statement are the place holders for the input parameters with the ODBC interface. The shelf inserttypes specifies that the data provided for the first '?' must be text and the value supplied for the second '?' must be an integer. The shelf querytypes contains the specification for a single input parameter of type text.
When using the OCI interface, you need to use unique names beginning with colons. A common technique is to name the first parameter ":1", the second ":2", and so on.
The following example shows the code to compile a SQL statement for the OCI interface and explicitly specify the data types of the input parameters.
local db.statement CompanyInsert
local db.statement CompanyQuery
local integer inserttypes variable initial { db.text-type, db.integer-type }
local integer querytypes variable initial { db.text-type }
set CompanyInsert to db.compile-statement in Targetdb
sql "insert into Customers (CompanyName,Country) Values (:1,:2);"
types inserttypes
set CompanyQuery to db.compile-statement in Targetdb
sql ( "select CompanyID from Customers where CompanyName = :1" )
types querytypes
The most straightforward way to execute your compiled SQL statement is with the function db.execute. Data for any input parameters are supplied in a shelf. The data must match the specification made at compile time. That is, there must be the same number of input values, presented in the same order and of the same type. If the compiled statement is a query, a shelf of db.field items must be supplied to receive the result set.
The following sample shows code that will execute the statements compiled in the example above:
local db.field QueryResults variable
local stream TableQueryShelf variable
local stream TableInputShelf variable
set new TableQueryShelf {"CompanyName"} to Company
db.execute CompanyQuery
with TableQueryShelf
into QueryResults
do unless db.record-exists QueryResults
clear TableInputShelf
set new TableInputShelf {"CompanyName"} to Company
set new TableInputShelf {"Country"}
to db.reader of QueryResults-01 {"CountryCode"}
db.execute CompanyInsert with TableInputShelf
done
This code sample shows keys being used for the input shelf TableInputShelf. They are there only for readability and are not necessary. What is important when using db.execute is the number of values in the input data shelf, their data types and their position in the input data shelf.
All other methods of inserting data into a database from OmniMark require the data to be contained in a buffer, which is not desirable for large amounts of data. The db.streaming-execute function allows data which is external to the OmniMark program to be streamed directly into the database.
Be aware that the order in which parameters are requested for insertion depends on the database driver, and thus requires using a different programing technique.
The following code sample demonstrates streaming data into a database using the OCI interface, with data both coming from a large binary file and coming from a tcp connection.
local db.database this-database
local db.statement stmt
local integer param-types variable
local db.field insert-handle
local stream in-stream
local tcp.connection tcpcon
. . .
set new param-types{"1"} to db.blob-type
set new param-types{"2"} to db.clob-type
set stmt to db.compile-statement in this-database
sql "insert into MyTable values (:1, :2)" types param-types
db.streaming-execute stmt with insert-handle
repeat
exit when not db.prepare insert-handle
open in-stream as db.writer of insert-handle
using output as in-stream
do select key of insert-handle
case 1
output binary-mode file "largebinaryfile.bin"
case 2
output tcp.reader of tcpcon protocol tcp.multi-packet
done
close in-stream
again
db.execute-in or
db.execute are not the only way to make changes to a database. The OMDB library includes a set of functions for direct table manipulation. These functions (db.insert, db.delete, and db.update) have several advantages over simply executing the comparable SQL statements:
To use these functions you must have open connections to the database (made using one of the dbOpen functions) and to the target table (made using the function db.open-table).
The db.open-tablefunction returns a db.table OMX component representing a connection to a single table in an open database. Multiple connections can be opened to the same table, and each newly opened connection will behave independently of the others. You may close a connection to a table by allowing it to go out of scope, or by explicitly closing it using db.close.
A stored procedure is a set of SQL statements or statements in another language (such as Visual Basic) with an assigned name that is stored in a database in its compiled form. The OMDB library function db.open-procedure is used to open a connection to a procedure stored in a connected database and return an OMX component db.procedure. The use of stored procedures can be helpful in controlling access to data (end-users may enter or change data but do not write procedures), preserving data integrity (information is entered in a consistent manner), and improving productivity (statements in a stored procedure only need to be written once).
Access these stored procedures from your OmniMark program using the function db.open-procedure. With most installations and platforms, you can use the simple form of this function, as in:
import "omdb.xmd" prefixed by db.
process
local db.procedure test-proc
local db.database Targetdb
set Targetdb to db.open-odbc "Phred" user "Jeremy" password "2002the"
set test-proc to db.open-procedure in Targetdb named 'OMDB_PROC_1'
where 'OMDB_PROC_1' is the name the procedure was stored under. This format does not require you to describe the parameters of the procedure. In some rare circumstances when using the ODBC interface, your ODBC driver, database, or platform will require that you provide specific descriptive details on the parameters used by the stored procedure at the time the connection to the procedure is made. For details on this form of the function, please consult the documentation for dbProcedureOpen.
Once a connection to the stored procedure has been made, you can execute it using the function db.execute. This function supplies any input required and executes the procedure.
Shelves are used to handle the input and output parameters and the procedure results. For example:
local stream in-val variable
local db.field out-val variable
local db.field res-val variable
set new in-val to '7734'
set new in-val to '34-RTS-485643'
set new in-val to '47'
db.execute purchase-item
in in-val
out out-val
result res-val
Notice that the single record of items in the input shelf in-val is not keyed. db.execute works the same way that db.execute does: the data items in the shelf must be in a specific order.
The output shelf out-val is a keyed shelf containing a single record. After the execution of the stored procedure this shelf is cleared and then propagated with db.field items attached to the output and inout procedure parameters. The order of the output shelf items corresponds directly to the ordering of the procedure input parameters. Item keys are assigned to the shelf items based on the names of the procedure parameters. For example, :
output db.reader of out-val{ 'ItemCount' } ||
' units were sold%n'
output db.reader of out-val{ 'InventoryCount' } ||
' units remaining in inventory%n'
output 'Total value of the sale was $' ||
db.reader of out-val{'RETURN_VALUE'} || '%n'
If the stored procedure is a function that returns a value, the value can be found at the beginning of the output shelf, keyed 'RETURN_VALUE'.
The results shelf supplied with the db.execute function is attached to the result set of any SQL query executed in the stored procedure. As a stored procedure can contain more than one statement, it is possible that it may also contain more than one query. Each query may yield multiple records in its result set. The function db.advance-recordset allows you to move through any number of result sets arising from the execution of a stored procedure. For example:
db.execute proc
result rset
repeat
output "Result Set %n"
repeat
exit unless db.record-exists rset
output "%n Record"
repeat over rset
output "%n%t" || key of rset || '= "' ||
db.reader of rset || '"'
again
output "%nEnd of Record %n"
db.move-record rset
again
exit unless db.advance-recordset rset
again
The code in this sample reads through a result set in the results shelf until the end of the result set is passed. The call to db.advance-recordset moves to the next record set in the results shelf if one exists. If db.advance-recordset indicates that there is another record set in the results shelf, the process of reading through the entire result set is repeated.
