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 SELECT statement 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 \'
or '' (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.
- types(ListOfTypes)
- 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
default to 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.7 for notes on
type-conversion.
- null(NullSpecifier)
- Specify SQL NULL representation. See odbc_set_connection/2
for details.
- source(Bool)
- If
true (default 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
construct below.
lemmas(Lemmas) :-
findall(Lemma,
odbc_query(wordnet,
'select (lemma) from word',
row(Lemma)),
Lemmas).
Using the 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.
- wide_column_threshold(+Length)
- Specify threshold column width for using SQLGetData(). See odbc_set_connection/2
for details.
