The database interface is envisioned to consist of two layers. The first layer is an encapsulation of the core functionality of ODBC. This layer makes it possible to run SQL queries. The second layer exploits the relation between Prolog predicates and database tables, providing ---a somewhat limited--- natural Prolog view on the data. The current interface only covers the first layer.
The value of RDMS for Prolog is often over-estimated, as Prolog itself can manage substantial amounts of data. Nevertheless a Prolog/RDMS interface provides advantages if data is already provided in an RDMS, data must be shared with other applications, there are strong persistency requirements or there is too much data to fit in memory.
The popularity of ODBC makes it possible to design a single foreign-language module that provides RDMS access for a wide variety of databases on a wide variety of platforms. The SWI-Prolog RDMS interface is closely modeled after the ODBC API. This API is rather low-level, but defaults and dynamic typing provided by Prolog give the user quite simple access to RDMS, while the interface provides the best possible performance given the RDMS independency constraint.
The Prolog community knows about various high-level connections between RDMS and Prolog. We envision these layered on top of the ODBC connection described here.
The ODBC interface deals with a single ODBC environment with multiple simultaneous connections. The predicates in this section deal with connection management.
alias
option is used. In addition to the options below, options applicable to
odbc_set_connection/2
may be provided.
user(User).once (default if an alias
is provided), a second call to open the same DSN simply
returns the existing connection. If multiple (default if
there is no alias name), a second connection to the same data-source is
opened.true, use Microsoft SQL server 2005 mars mode.
This is support for multiple concurrent statements on a connection
without requiring the dynamic cursor (which incurs an astounding 20-50x
slowdown of query execution!!). MARS is a new feature in SQL2k5
apparently, and only works if you use the native driver. For the
non-native driver, specifying that it is enabled will have absolutely no
effect.'3.0'.
The other supported value is '2.0'.
The following example connects to the WordNet1An
SQL version of WordNet is available from http://wordnet2sql.infocity.cjb.net/
[1] database, using
the connection alias wordnet and opening the connection
only once:
open_wordnet :-
odbc_connect('WordNet', _,
[ user(jan),
password(xxx),
alias(wordnet),
open(once)
]).
user and
password.
Whenever possible, applications should use odbc_connect/3. If you need this predicate, please check the documentation for SQLDriverConnect() and the documentation of your driver.bugFacilities to deal with prompted completion of the driver options are not yet implemented.
read, tell the driver we only access the database in
read mode. If update (default), tell the driver we may
execute update commands.true (default), each update statement is committed
immediately. If false, an update statement starts a
transaction that can be committed or rolled-back. See section
2.4 for details on transaction management.dynamic
makes it possible to have multiple active statements on the same
connection with Microsoft SQL server. Other values are static, forwards_only
and keyset_driven.unicode while
on other platforms it is utf8. Below, the *A() functions
refer to the‘ansi' ODBC functions that exchange bytes and the *W()
functions refer to the‘unicode' ODBC functions that exchange UCS-2
characters.
true (default false), statements returning
SQL_SUCCESS_WITH_INFO succeed without printing the info.
See also section 2.8.1.$null$. NullSpecifier is
an arbitrary Prolog term, though the implementation is optimised for
using an unbound variable, atom and functor with one unbound variable.
The representation null(_) is a commonly used alternative.
The specified default holds for all statements executed on this connection. Changing the connection default does not affect already prepared or running statements. The null-value can also be specified at the statement level. See the option list of odbc_query/4.
Name(Value). If Property
is unbound all defined properties are enumerated on backtracking.
Currently the following properties are defined.
cursor_type
to dynamic. See odbc_set_connection/2.
ODBC distinguishes between direct execution of literal SQL strings and parameterized execution of SQL strings. The first is a simple practical solution for infrequent calls (such as creating a table), while parameterized execution allows the driver and database to precompile the query and store the optimized code, making it suitable for time-critical operations. In addition, it allows for passing parameters without going through SQL-syntax and thus avoiding the need for quoting.
[]
If the statement is a SELECT statement the result-set is
returned in RowOrAffected. By default rows are returned
one-by-one on backtracking as terms of the functor row/Arity,
where Arity denotes the number of columns in the result-set.
The library pre-fetches the next value to be able to close the statement
and return deterministic success when returning the last row of the
result-set. Using the option findall/2 (see below) the
result-set is returned as a list of user-specified terms. For other
statements this argument returns affected(Rows), where Rows
represents the number of rows affected by the statement. If you are not
interested in the number of affected rows odbc_query/2
provides a simple interface for sending SQL-statements.
Below is a small example using the connection created from
odbc_connect/3.
Please note that the SQL-statement does not end in the‘;
lemma(Lemma) :-
odbc_query(wordnet,
'SELECT (lemma) FROM word',
row(Lemma)).
The following example adds a name to a table with parent-relations,
returning the number of rows affected by the statement. Note that the
SQL quote character is the ASCII single quote and, as this SQL
quote is embedded in a single quoted Prolog atom, it must be written as \'
or '' (two single quotes). We use the first
alternative for better visibility.
insert_child(Child, Mother, Father, Affected) :-
odbc_query(parents,
'INSERT INTO parents (name,mother,father) \
VALUES (\'mary\', \'christine\', \'bob\')',
affected(Affected)).
Options defines the following options.
default to use
default conversion for that column. The length of the type-list must
match the number of columns in the result-set.
For example, in the table word the first column is
defined with the SQL type DECIMAL(6). Using this SQL-type, “001''
is distinct from “1'', but using Prolog integers is a valid
representation for Wordnet wordno identifiers. The
following query extracts rows using Prolog integers:
?- odbc_query(wordnet,
'select * from word', X,
[ types([integer,default])
]).
X = row(1, entity) ;
X = row(2, thing) ;
...
See also section 2.7 for notes on type-conversion.
true (default false), include the
source-column with each result-value. With this option, each result in
the
row/N-term is of the format below. TableName
or
ColumnName may be the empty atom if the information is not
available.3This is one possible
interface to this information. In many cases it is more efficient and
convenient to provide this information separately as it is the same for
each result-row.
column(TableName, ColumnName, Value)
lemmas(Lemmas) :-
findall(Lemma,
odbc_query(wordnet,
'select (lemma) from word',
row(Lemma)),
Lemmas).
Using the findall/2 option the above can be implemented
as below. The number of argument of the row term must match
the number of columns in the result-set.
lemmas(Lemmas) :-
odbc_query(wordnet,
'select (lemma) from word',
Lemmas,
[ findall(Lemma, row(Lemma))
]).
The current implementation is incomplete. It does not
allow arguments of
row(...) to be instantiated. Plain instantiation can always
be avoided using a proper SELECT statement. Potentially useful however
would be the translation of compound terms, especially to translate
date/time/timestamp structures to a format for use by the application.
SELECT). The predicate prints a
diagnostic message if the query returns a result.
ODBC provides for‘parameterized queries'. These are SQL queries
with a ?
[]?silent(true) option
of odbc_set_connection/2.
An alternative mapping can be selected using the > option
of this predicate described below.char, varchar,
etc. to specify the field-width. When calling odbc_execute/[2-3], the
user must supply the parameter values in the default Prolog type for
this SQL type. See section 2.7 for
details.atom > date
The use must supply an atom of the format YYYY-MM-DD
rather than a term date(Year,Month,Day). This construct
enhances flexibility and allows for passing values that have no proper
representation in Prolog.
default. It unifies Variable with
the
PrologType > SqlType as using the
types derived.4 The current
version does not provide the field with in SqlType. Future
versions may improve on that. This feature is first of all
intended for debugging. Using odbc_debug(1), the library
prints details on the derived types.Options defines a list of options for executing the statement. See odbc_query/4 for details. In addition, the following option is provided:
auto
(default) to extract the result-set on backtracking or fetch
to prepare the result-set to be fetched using odbc_fetch/3.
ODBC doesn't appear to allow for multiple cursors on the same
result-set.5Is this right?
This would imply there can only be one active odbc_execute/3
(i.e. with a choice-point) on a prepared statement. Suppose we have a
table age (name char(25), age integer) bound to the
predicate age/2 we cannot write the code
below without special precautions. The ODBC interface therefore creates
a clone of a statement if it discovers the statement is being executed,
which is discarded after the statement is finished.6The
code is prepared to maintain a cache of statements. Practice should tell
us whether it is worthwhile activating this.
same_age(X, Y) :-
age(X, AgeX),
age(Y, AgeY),
AgeX = AgeY.
Normally SQL queries return a result-set that is enumerated on backtracking. Using this approach a result-set is similar to a predicate holding facts. There are some cases where fetching the rows one-by-one, much like read/1 reads terms from a file is more appropriate and there are cases where only part of the result-set is to be fetched. These cases can be dealt with using odbc_fetch/3, which provides an interface to SQLFetchScroll().
As a general rule of thumb, stay away from these functions if you do not really need them. Experiment before deciding on the strategy and often you'll discover the simply backtracking approach is much easier to deal with and about as fast.
fetch(fetch) and be executed using odbc_execute/2. Row
is unified to the fetched row or the atom end_of_file7This
atom was selected to emphasise the similarity to read.
after the end of the data is reached. Calling odbc_fetch/2
after all data is retrieved causes a permission-error exception. Option
is one of:
relative(1) is the same
as next, except that the first row extracted is row 2.
In many cases, depending on the driver and RDBMS, the cursor-type
must be changed using odbc_set_connection/2
for anything different from next to work.
Here is example code each time skipping a row from a table‘test' holding a single column of integers that represent the row-number. This test was executed using unixODBC and MySQL on SuSE Linux.
fetch(Options) :-
odbc_set_connection(test, cursor_type(static)),
odbc_prepare(test,
'select (testval) from test',
[],
Statement,
[ fetch(fetch)
]),
odbc_execute(Statement, []),
fetch(Statement, Options).
fetch(Statement, Options) :-
odbc_fetch(Statement, Row, Options),
( Row == end_of_file
-> true
; writeln(Row),
fetch(Statement, Options)
).
Most SQL queries return only a single result set - a list of rows. However, some queries can return more than one result set. For example,’SELECT 1; SELECT 2' is a batch query that returns a single row (1) and then a single row(2). Queries involving stored procedures can easily generate such results.
To retrieve data from a subsequent result set, odbc_next_result_set/1 can be used, but only for prepared queries which were prepared with fetch(fetch) as the fetch style in the option list.
fetch(Options) :-
odbc_prepare(test,
'select (testval) from test; select (anotherval)
from some_other_table',
[],
Statement,
[ fetch(fetch)
]),
odbc_execute(Statement, []),
fetch(Statement, Options).
fetch(Statement, Options) :-
odbc_fetch(Statement, Row, Options),
( Row == end_of_file
-> ( odbc_next_result_set(Statement)
-> writeln(next_result_set),
fetch(Statement, Options)
; true
)
; writeln(Row),
fetch(Statement, Options)
).
ODBC can run in two modi. By default, all update actions are immediately committed on the server. Using odbc_set_connection/2 this behaviour can be switched off, after which each SQL statement that can be inside a transaction implicitly starts a new transaction. This transaction can be ended using odbc_end_transaction/2.
commit pending updates are made permanent, using
rollback they are discarded.The ODBC documentation has many comments on transaction management and its interaction with database cursors.
With this interface we do not envision the use of Prolog as a database manager. Nevertheless, elementary access to the structure of a database is required, for example to validate a database satisfies the assumptions made by the application.
all_types to enumerate all known types. This predicate
calls
SQLGetTypeInfo() and its facet names are derived from the
specification of this ODBC function:
NULL. May be unknownfalse,
true, like_only or all_except_like.
Databases have a poorly standardized but rich set of datatypes. Some
have natural Prolog counterparts, some not. A complete mapping requires
us to define Prolog data-types for SQL types that have no standardized
Prolog counterpart (such as timestamp), the definition of a default
mapping and the possibility to define an alternative mapping for a
specific column. For example, many variations of the SQL DECIMAL
type cannot be mapped to a Prolog integer. Nevertheless, mapping to an
integer may be the proper choice for a specific application.
The Prolog/ODBC interface defines the following Prolog result types
with the indicated default transformation. Different result-types can be
requested using the types(TypeList) option for the
odbc_query/4
and odbc_prepare/5
interfaces.
char, varchar,
longvarchar, binary, varbinary,
longvarbinary, decimal and numeric.
Can be used for all types.bit, tinyint,
smallint and integer. Please note that
SWI-Prolog integers are signed 32-bit values, where SQL allows for
unsigned values as well. Can be used for the integral, and decimal
types as well as the types date and timestamp,
which are represented as POSIX time-stamps (seconds after Jan 1, 1970).real, float
and
double. Can be used for the integral and decimal
types as well as the types date and timestamp,
which are represented as POSIX time-stamps (seconds after Jan 1, 1970).
Representing time this way is compatible to SWI-Prologs time-stamp
handling.date(Year,Month,Day) used as
default for the SQL type date.time(Hour,Minute,Second) used as
default for the SQL type time.timestamp(Year,Month,Day,Hour,Minute,Second,Fraction) used
as default for the SQL type timestamp.
ODBC operations return success, error or‘success with info'. This section explains how results from the ODBC layer are reported to Prolog.
If an ODBC operation returns‘with info', the info is extracted
from the interface and handled to the Prolog message dispatcher print_message/2.
The level of the message is informational and the term is
of the form:
If an ODBC operation signals an error, it throws the exception
error(. The
arguments of the odbc(State, Native, Message), _)odbc/3 term are explained in section
2.8.1.
In addition, the Prolog layer performs the normal tests for proper arguments and state, signaling the conventional instantiation, type, domain and resource exceptions.
There is a wealth on ODBC implementations that are completely or almost compatible to this interface. In addition, a number of databases are delivered with an ODBC compatible interface. This implies you get the portability benefits of ODBC without paying the configuration and performance price. Currently this interface is, according to the PHP documentation on this subject, provided by Adabas D, IBM DB2, Solid, and Sybase SQL Anywhere.
The SWI-Prolog ODBC interface was developed using unixODBC and MySQL on SuSE Linux.
On MS-Windows, the ODBC interface is a standard package, linked
against
odbc32.lib.
The following issues are identified and waiting for concrete problems and suggestions.
The SWI-Prolog ODBC interface started from a partial interface by Stefano De Giorgi. Mike Elston suggested programmable null-representation with many other suggestions while doing the first field-tests with this package.