ODBC Support

The tools for extended predicates have been used to implement ODBC support for Prolog. The full source code for the ODBC support is included so you can modify and enhance it as you see fit.

The ODBC predicates are defined in two layers.

Extended predicates that provide direct access to ODBC functions. These are defined in aodbc.lsx.
A Prolog shell that makes it easy to map Prolog predicates to database tables, so that Prolog can easily backtrack through database tables as easily as Prolog facts. These are defined in aodbc.plm.

The source code for each is provided in the directory \src\lsx\odbc.

There is also a customer-contributed ODBC interface that covers the full ODBC API. It is available on an as-is basis on our web site and can be modified as you see fit.

Prolog Interface

The high-level ODBC interface is designed to present an easy mapping of Prolog predicates to database queries. db_query/2 is the main predicate that provides a mapping from a simple Prolog query format with backtracking to the rows of a database table. Multiple db_query/2 statements can be nested to form arbitrarily complex joins. Internally each keeps track of its own ODBC cursor.

db_query/2 maps the Prolog query to an SQL query, using the bound variables in the where clause. This utilizes the query capabilities of the particular ODBC driver. Normal Prolog unification can be used on top of that to fine tune the resulting set of records.

db_open(DBSource): Open the named ODBC source with no user id or password. DBSource should be an atom or a string.
db_open(DBSource, UserID): Open the named ODBC source for UserID. Both arguments should be atoms or strings.
db_open(DBSource, UserID, Password): Open the named ODBC source for UserID and Password. All arguments are atoms or strings.
db_close: Closes the open ODBC connection, freeing all open queries. (Open queries are db_query queries that did backtrack through all solutions.)
db_query(Table, QueryList): Pose the QueryList to the named Table, unifying the variables as appropriate. The QueryList is a list of column names and Prolog terms separated by '=' signs. It is expected the application will backtrack through all solutions as the cursor is kept open. (Use db_queryonce for single solutions, see db_freeall for dealing with incomplete queries.) The following example uses the dbgene sample:
db_queryonce(Table, QueryList): Like db_query/2, except it is intended to be executed just once, and the cursor is closed after the call.
db_query(SQL, Input, Format, Output): A version of db_query that lets the program specify an arbitrary SQL statement. Input is a list of values to replace '?' in the query, Format is a list of format specifications of the expected output values, and Output is a list of variables that will be bound to the results. The following example uses the dbgene sample:
db_queryonce(SQL, Input, Format, Output): Like db_query/4, except it is intended to be executed just once, and the cursor is closed after the call.
db_table(Table, Column, Type): User-defined descriptions of ODBC tables used by db_query to build queries. One db_table/3 fact must be entered for each column that will be used in db_query/2 queries.

Prolog Interface Example

This example program poses a query against a database of basketball games that include the date and time of each game. It backtracks through the games printing each as it goes.

db_table(games, home, a20).
db_table(games, away, a20).
db_table(games, day, d).    % date data type
db_table(games, time, t).   % time data type

main :-
  db_open(events),
  db_query(games, [home=H, away=A, day=D, time=T]),
  write(H:A), tab(1), write(D),
  tab(1), write(T), nl,
  fail.
main :- db_close.

?- main.
Duke : Clemson date(1997,1,12) time(14,30,0)
Florida State : Georgia Tech date(1997,1,12) time(20,15,0)
Maryland : North Carolina date(1997,1,14) time(13,0,0)

There are a number of samples illustrating the ODBC connection in the Samples directory. samples\odbc\dbgene contains a Prolog program that can be run in the IDE and that accesses an ODBC database. See the samples documentation for details on how to set it up.

Extended Predicates

The direct ODBC extended predicates provide a lower level connection between Prolog and ODBC. The basic loop is to call db_query/5, specifying an SQL query, followed by epeated calls to db_fetch/2 retrieve the database fields and map them to Prolog terms. The Prolog ODBC shell provides an interface to this basic loop that maps more naturally to Prolog backtracking search.

db_init: Initialize the ODBC environment. This predicate is called only once as only one ODBC environment is necessary for multiple connections.
db_free: Closes the DB environment. There should be one of these calls to balance every db_init.
db_connect(HDBC, DataSource, UserID, Password): Connects to the ODBC DataSource, using UserID and Password, all strings or atoms. HDBC should be an unbound variable which is bound with the address of the connection.
db_disconnect(HDBC): Disconnect the specified ODBC connection. There should be one db_disconnect for every db_connect.
db_query(HDBC, QAddr, SQL, WhereList, FieldTypes): Using connection HDBC, db_query/5 prepares and executes the SQL statement, using the Prolog terms in the WhereList to bind with the where clause, and mapping the requested database fields to the types listed in the FieldTypes list. The internal address of the query is returned in QAddr, for use in subsequent db_fetch calls.
db_fetch(QAddr, AnsList): Fetches the next row which satisfied the query identified by QAddr. The database fields specified in the SQL query are mapped to the Prolog terms in the AnsList.
db_freeq(QAddr): Frees the internal resources associated with the query identified by QAddr. This is only necessary if the query has not been exhausted through repeated calls to db_fetch/2. (Even then it is not totally necessary, as all of the memory still allocated will be freed when the ODBC connection is closed.)
db_freeall: Frees all the open queries. If an application uses cut (!) to trim the search tree on database queries, then it is recommended that db_freeall be called periodically to free up ODBC resources being used to hold unfinished queries. Failure to do so can result in system errors in the ODBC drivers that can hang an application.