2.3.1 One-time invocation

odbc_query(+Connection, +SQL, -RowOrAffected)

Same as odbc_query/4 using [] for Options.

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 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‘;’character.

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.

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.