Using the OMDB library

Using the OMDB library

This document covers the following topics:

  • Introduction to OMDB
  • Accessing an ODBC-compliant database from an OmniMark program
  • Accessing an Oracle database via OCI from an OmniMark program
  • Creating a database connection
  • Executing SQL statements against your database
  • Working with the result set from a query
  • Compiling SQL statements
  • Inserting large data into your database
  • Manipulating individual database tables without using SQL statements
  • Using stored procedures

Introduction to OMDB

The OMDB library provides a way to interface with relational databases from your OmniMark programs. This library includes functions to:

  • connect to a database and its tables
  • manipulate data in the database
  • execute SQL statements against a database
  • access the result set of a SQL query
The current library functions both supports interactions with ODBC-compliant databases, and supports interactions with Oracle 11g (and higher) databases via OCI.

Accessing an ODBC-compliant database from an OmniMark program

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:

  • the DSN (data source name) for the database you wish to connect to
  • the userid and password (if required)
The db.open-odbc function returns a connection to the specified database as a db.database instance.
  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 data type instance is typically assigned to a variable, as in the code sample above.

Accessing an Oracle database via OCI from an OmniMark program

OMDB also supports accessing Oracle 11g (and higher) databases via OCI instead of ODBC: use the db.open-oci11g function to connect to the database.

  import "omdb.xmd" prefixed by db.
  
  process
     local db.database Targetdb
  
     set Targetdb to db.open-oci11g "Phred" user "Jeremy" password "2002the"

Creating a Database Connection

An open database connection operates in one of two transaction modes:

  • db.auto-commit - All transactions are committed as soon as they are performed. This is the default value for all open database connections.
  • db.manual-commit - No transactions are committed to the database until the programmer specifically commits that change. You must explicitly set a connection to this mode.
In the example below we open two different ODBC connections to the database "MarketStats".
  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-oci11g "MarketStats"

A database connection is closed when:

  • the associated variable goes out of scope, or
  • the function db.close is used to explicitly close the connection.

Executing SQL statements against your database

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:
  • NewUsers {"Name"},
  • NewUsers {"UserID"} and
  • NewUsers {"PhoneNum"}
In the case above, each data item in the result set came from a column or field with a distinct name. However, this is not necessarily always the case. If the query involves multiple tables, it is possible that data items in the result set could originate from two columns with identical names. The example below queries two fields named "Name": one in the Course table, and one in the Student table.The unique key names are created from the column names by adding a suffix to the next occurrence of any duplicate field name.
     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:
  • NewUsers {"Name"},
  • NewUsers {"Name*2"},
  • NewUsers {"Grade"},
where NewUsers {"Name"} represents the course name and NewUsers {"Name*2"} represents the student name.

Working with the result set from a query

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-record
repositions the data cursor to the first row of the result set NewUsers. The code
        db.move-record NewUsers by 2
skips 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.

Compiling SQL statements

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.

Inserting large data into your database

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 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
  
        using output as db.writer of insert-handle
        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

Manipulating individual database tables without using SQL statements

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:

  • They execute more efficiently.
  • It is unnecessary to explicitly specify the data type of the values in each item in the input shelf.
  • The number of shelf items for input is not fixed.
  • The input data is provided in a shelf of items keyed to the column names of the table. The order in which the shelf items are presented is irrelevant.
  • They make it possible to insert binary data into the database without the special encoding required when using a SQL statement.
  • They do not require the programmer to have a knowledge of SQL.
The disadvantage of these functions over executing comparable SQL statements is that:
  • They are inflexible.
  • They can only update a single table at a time.
  • They require you to open a connection to a table.

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 instance 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.

Using stored procedures

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 instance of 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 db.open-procedure.

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.