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 (or concatenates) to form an SQL statement.
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.