:- 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.
- Determine the Prolog type used to report the column-values. When
omitted, default conversion as described in section
2.7 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.7 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.