Did you know ... | Search Documentation: |
Parameterised queries |
ODBC provides for‘parameterized queries’. These are SQL
queries with a
-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:
?
[]
for Options.?
)
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:
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.