- odbc_query(+Connection, +SQL, -RowOrAffected)
- Same as odbc_query/4
- odbc_query(+Connection, +SQL, -RowOrAffected, +Options)
- 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
SELECTstatement 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
''(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.
- odbc_query(+Connection, +SQL)
- As odbc_query/3,
but used for SQL-statements that should not return result-rows (i.e. all
statements except for
SELECT). The predicate prints a diagnostic message if the query returns a result.