There are several ways that can be used to extract information from or modify a table in a data source. The most basic way is to use predicates that pass an SQL statement directly to the ODBC driver. The basic call is:
| ?- odbc_sql(BindVals,SQLStmt,ResultRow).where BindVals is a list of (ground) values that correspond to the parameter indicators in the SQL statement (the '?'s); SQLStmt is an atom containing an SQL statement; and ResultRow is a returned list of values constituting a row from the result set returned by the SQL query. Thus for a select SQL statement, this call is nondeterministic, returning each retrieved row in turn.
The BindVals list should have a length corresponding to the number of parameters in the query, in particular being the empty list ([]) if SQLStmt contains no '?'s. If SQLStmt is not a select statement returning a result set, then ResultRow will be the empty list, and the call is deterministic. Thus this predicate can be used to do updates, DDL statements, indeed any SQL statement.
SQLStmt need not be an atom, but can be a (nested) list of atoms which flattens (and concatenates) to form an SQL statement.
BindVals is normally a list of values of primitive Prolog types: atoms, integers, or floats. The values are converted to the types of the corresponding database fields. However, complex Prolog values can also be stored in a database field. If a term of the form term(VAL) appears in the BindVal list, then VAL (a Prolog term) will be written in canonical form (as produced by write_canonical) to the corresponding database field (which must be CHAR or BYTE). If a term of the form string(CODELIST) appears in BindVal, then CODELIST must be a list of ascii-codes (as produced by atom_codes) and these codes will be converted to a CHAR or BYTE database type.
ResultRow for a select statement is normally a list of variables that will nondeterministically be bound to the values of the fields of the tuples returned by the execution of the select statement. The Prolog types of the values returned will be determined by the database types of the corresponding fields. A CHAR or BYTE database type will be returned as a Prolog atom; an INTEGER database field will be returned as a Prolog integer, and similarly for floats. However, the user can request that CHAR and BYTE database fields be returned as something other than an atom. If the term string(VAR) appears in ResultRow, then the corresponding database field must be CHAR or BYTE, and in this case, the variable VAR will be bound to the list of ascii-codes that make up the database field. This allows an XSB programmer to avoid adding an atom to the atom table unnecessarily. If the term term(VAR) appears in ResultRow, then the corresponding database field value is assumed to be a Prolog term in canonical form, i.e., can be read by read_canonical/1. The corresponding value will be converted into a Prolog term and bound to VAR. This allows a programmer to store complex Prolog terms in a database. Variables in such a term are local only to that term.
When connecting to multiple data sources, you should use the form:
| ?- odbc_sql(ConnectionName,BindVals,SQLStmt,ResultRow).
For example, we can define a predicate, get_test_name_price, which given a test ID, retrieves the name and price of that test from the test table in the hospital database:
get_test_name_price(Id,Nam,Pri) :-
odbc_sql([Id],'SELECT TName,Price FROM Test WHERE TId = ?', [Nam,Pri]).
The interface uses a cursor to retrieve this result and caches the cursor, so that if the same query is needed in the future, it does not need to be re-parsed, and re-optimized. Thus, if this predicate were to be called several times, the above form is more efficient than the following form, which must be parsed and optimized for each and every call:
get_test_name_price(Id,Nam,Pri) :-
odbc_sql([],['SELECT TName,Price FROM Test WHERE TId = ''',Id,''''], [Nam,Pri]).
Note that to include a quote (') in an atom, it must be represented by using two quotes.
There is also a predicate:
| ?- odbc_sql_cnt(ConnectionName,BindVals,SQLStmt,Count).
This predicate is very similar to odbc_slq/4 except that it can only be used for UPDATE, INSERT, and DELETE SQL statements. The first three arguments are just as in odbc_slq/4; the fourth must be a variable in which is returned the integer count of the number of rows affected by the SQL operation.