|Did you know ...||Search Documentation:|
:- use_module(library(odbc)).(can be autoloaded)
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
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
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.
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
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.6 for notes on type-conversion.
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).
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.