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.
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.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.