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.
- 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.
- 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.
- Table
- the name of the table
- Column
- the name of the column
- Type
- a variable type and length. The types are
- i - integer
- a - atom
- s - string
- t - time
- d - date
- f - single float
- g - double float
The type indicates what Prolog type is mapped to the
table column. Atoms and strings need a length as well, such as a10 for
a 10 character atom or s25. Date and time fields map into Prolog structures
date/3 and time/3 respectively.
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.)
Copyright ©1987-2000 Amzi! inc. All Rights Reserved.