Chapter 20  The ODBC binding

• ODBC objects
• A small example

ODBC stands for “Open Database Connectivity.” It is standard, developed by http://www.microsoft.com, for generic database access. There are versions for most popular operating systems, including Mac OS X and GNU/Linux.

ODBC itself is documented by Microsoft ODBC Function Summary, and information abounds. The Wikipedia ODBC page is another good starting point.

The OMake ODBC port is actually modeled on JDBC (JDBC Tutorial, java.sql), but the underlying access is still ODBC. We document part of the interface here.

Generally speaking, a JDBC-style database has several components.

• A DriverManager provides a set of ways to connect ot a database.
• A Connection represents a connection to a database. The connection usually requires a username and password. Transactions are managed by the connection.
• A Statement is an SQL statement to be executed by the database.
• A RowSet is the result of an SQL query. With some limitations, one may scroll both forward and backward through the query result, reading, and perhaps modifying it.

20.1  ODBC objects

20.1.1  Odbc::DriverManager

Methods:

• getConnection(url : String, user = <String>, passwd = <String>, timeout = <Int>)

  Connect to a database. The url refers to the database, and it will in general depend on your local machine configuration. The user and password are needed if your database requires you to log in. The timeout is minimum amount of time to wait before giving up.

20.1.2  Obdc::Connection

Methods:

• close() close the connection.
• createStatement(type = <CursorType>, concurrency = <Concurrency>, holdability = <Holdability>) Create a new statement. The statement is initially empty.

  CursorType:

  • CURSOR_TYPE_FORWARD_ONLY the entries will be read from beginning to end.
  • CURSOR_TYPE_SCROLL_INSENSITIVE the entries are insensitive to changes made by other processes.
  • CURSOR_TYPE_SCROLL_SENSITIVE the entries are sensitive to changes made by other processes.

  Concurrency:

  • CURSOR_CONCUR_READ_ONLY the result of the query is read-only.
  • CURSOR_CONCUR_UPDATABLE the result of the query can be modified.

  Holdability:

  • CURSOR_CLOSE_CURSORS_AT_COMMIT the cursor is closed with the statement.
  • CURSOR_HOLD_CURSORS_OVER_COMMIT the cursor survives even of the statement is closed.
• prepareStatement(query : String, type = <CursorType>, concurrency = <Concurrency>, holdability = <Holdability>)

  Create a PreparedStatement. A PreparedStatement is like a Statement that is partially filled in. It is usually more efficient to use a PreparedStatement if it is going to be executed multiple times.

• setAutoCommit(flag) specify whether results are to be committed after each SQL operation. If the flag is false, then results must be committed explicity, or rolled-back.
• rollback() rollback all uncommitted database operations.
• commit() commit all uncommitted database operations.

20.1.3  Odbc::Statement

Methods:

• close() close the statement.
• execute(sql : String) execute an SQL operation that does not have a query result.
• executeQuery(sql : String) : Rowset execute a SQL query.

20.1.4  Odbc::PreparedStatement

Methods:

• close() close the statement.
• executeQuery(sql : String) : Rowset execute a SQL query.
• setString(i : Int, s : String) specify a parameter.

20.1.5  Odbc::Rowset

A Rowset is the result of a query.

Methods. Most methods return true on success, false on failure.

• first() : bool scroll to the first row.
• last() : bool scroll to the last row.
• absolute(row) : bool scroll to an absolute position.
• relative(off) : bool scroll to a row specified relative to the current one.
• next() : bool scroll to the next row.
• previous() : bool scroll to the previous row.
• close() close the RowSet.
• labels() : String Array get the labels of the columns.
• findColumn(label : String) : Int get the column number of the specified column.
• get(c) get the value of the specified column; c is a column name or column number.
• update(c, data) update the value of the specified column.
• getRow() fetch the current row from the database.
• updateRow() flush any modifications to the row to the database.
• deleteRow() delete the current row from the RowSet.
• refreshRow() discard any local changes and refetch the current row from the database.
• cancelRowUpdates() same as refreshRow().

20.2  A small example

The following trivial example fetches the title and subtitle of all recorded programs in a MythTV database.

open odbc

conn = $(DriverManager.getConnection mythtv, user = mythtv, passwd = mythtv)
stmt = $(conn.createStatement)
rs = $(stmt.executeQuery $'SELECT title, subtitle FROM recorded;')
while $(rs.next)
    title = $(rs.get title)
    subtitle = $(rs.get subtitle)
    println($"title = $(title); subtitle = $(subtitle)")