- Reference manual
- SWI-Prolog ODBC Interface
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.
- odbc_query(+Connection, +SQL, -RowOrAffected)
- Same as odbc_query/4
- odbc_query(+Connection, +SQL, -RowOrAffected, +Options)
- Fire an SQL query on the database represented by Connection.
SQL is any valid SQL statement. SQL statements can be
specified as a plain atom, string or a term of the format
Format-Arguments, which is converted using format/2.
If the statement is a
SELECTstatement 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
''(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.
- Determine the Prolog type used to report the column-values. When
omitted, default conversion as described in section
2.6 is implied. A column may specify
defaultto 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
wordthe 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
wordnoidentifiers. 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.6 for notes on type-conversion.
- Specify SQL NULL representation. See odbc_set_connection/2 for details.
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)
- findall(Template, row(Column, ... ))
- Instead of returning rows on backtracking this option makes odbc_query/3
return all rows in a list and close the statement. The option is named
after the Prolog findall/3
predicate, as the it makes odbc_query/3
behave as the commonly used findall/3
lemmas(Lemmas) :- findall(Lemma, odbc_query(wordnet, 'select (lemma) from word', row(Lemma)), Lemmas).
findall/2option the above can be implemented as below. The number of argument of the
rowterm 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.
- Specify threshold column width for using SQLGetData(). See odbc_set_connection/2 for details.
- odbc_query(+Connection, +SQL)
- As odbc_query/3,
but used for SQL-statements that should not return result-rows (i.e. all
statements except for
SELECT). The predicate prints a diagnostic message if the query returns a result.
ODBC provides for `parameterized queries'. These are SQL queries with
-sign at places where parameters appear.
The ODBC interface and database driver may use this to precompile the
SQL-statement, giving better performance on repeated queries. This is
exactly what we want if we associate Prolog predicates to database
tables. This interface is defined by the following predicates:
- odbc_prepare(+Connection, +SQL, +Parameters, -Statement)
- As odbc_prepare/5
- odbc_prepare(+Connection, +SQL, +Parameters, -Statement, +Options)
- Create a statement from the given SQL (which may be a format
specification as described with odbc_query/3)
statement that normally has one or more parameter-indicators (
) and unify Statement with a handle to the created statement. Parameters is a list of descriptions, one for each parameter. Each parameter description is one of the following:
- Uses the ODBC function SQLDescribeParam() to obtain information about
the parameter and apply default rules. See section
2.6 for details. If the interface fails to return a type or the type
is unknown to the ODBC interface a message is printed and the interface
handles the type as text, which implies the user must supply an atom.
The message can be suppressed using the
silent(true)option of odbc_set_connection/2. An alternative mapping can be selected using the > option of this predicate described below.
- SqlType(Specifier, ...)
- Declare the parameter to be of type SqlType with the given
specifiers. Specifiers are required for
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.6 for details.
- PrologType > SqlType
- As above, but supply values of the given PrologType, using
the type-transformation defined by the database driver. For example, if
the parameter is specified as
atom > date
The use must supply an atom of the format
YYYY-MM-DDrather than a term
date(Year,Month,Day). This construct enhances flexibility and allows for passing values that have no proper representation in Prolog.
Options defines a list of options for executing the statement. See odbc_query/4 for details. In addition, the following option is provided:
- Determine the FetchType, which is one of
auto(default) to extract the result-set on backtracking or
fetchto prepare the result-set to be fetched using odbc_fetch/3.
- odbc_execute(+Statement, +ParameterValues, -RowOrAffected)
- Execute a statement prepared with odbc_prepare/4
with the given
ParameterValues and return the rows or number of affected
rows as odbc_query/4.
This predicate may return type_error exceptions if the provided
parameter values cannot be converted to the declared types.
ODBC doesn't appear to allow for multiple cursors on the same result-set.4Is 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.5The 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.
- odbc_execute(+Statement, +ParameterValues)
- Like odbc_query/2, this predicate is meant to execute simple SQL statements without interest in the result.
- If the thread ThreadId is currently blocked inside odbc_execute/3 then interrupt it. If ThreadId is not currently executing odbc_execute/4 then odbc_cancel_thread/1 succeeds but does nothing. If ThreadId is not a valid thread ID or alias, an exception is raised.
- Destroy a statement prepared with odbc_prepare/4. If the statement is currently executing (i.e. odbc_execute/3 left a choice-point), the destruction is delayed until the execution terminates.
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.
- odbc_fetch(+Statement, -Row, +Option)
- Fetch a row from the result-set of Statement. Statement
must be created with odbc_prepare/5
using the option
fetch(fetch)and be executed using odbc_execute/2. Row is unified to the fetched row or the atom
end_of_file6This 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:
- Fetch the next row.
- Fetch the result-set going backwards.
- Fetch the first row.
- Fetch the last row.
- Fetch absolute numbered row. Rows count from one.
- Fetch relative to the current row.
relative(1)is the same as
next, except that the first row extracted is row 2.
- Reserved. Bookmarks are not yet supported in this interface.
In many cases, depending on the driver and RDBMS, the cursor-type must be changed using odbc_set_connection/2 for anything different from
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) ).
- loses the given statement (without freeing it). This must be used if not the whole result-set is retrieved using odbc_fetch/3.