The first example is a simple SQL query using a fixed SQL string.
:- use_module(library(odbc)).
example1 :-
odbc_env_open(EnvHandle),
odbc_db_open('MyDatabase', EnvHandle, ConnectionHandle),
odbc_query_open(ConnectionHandle, StatementHandle),
odbc_query_execute_sql(StatementHandle,
"SELECT cookie,soft FROM bakery order by soft",
ResultSet),
show_result(ResultSet),
odbc_query_close(ResultSet),
odbc_db_close(ConnectionHandle),
odbc_env_close(EnvHandle).
show_result(ResultSet) :-
odbc_sql_fetch(ResultSet, Row),
show_result1(Row, ResultSet).
show_result1([], _ResultSet) :- !.
show_result1(Row, ResultSet) :-
format('~w~n', [Row]),
flush_output,
odbc_sql_fetch(ResultSet, Row1),
show_result1(Row1, ResultSet).
You begin by opening an environment. This is a handle which can be
used for various calls to the ODBC driver.
You then connect to the database with odbc_db_open/3.
The first argument is the identifier for the database in the DBMS.
In this scenario, connecting to the database does not require a
username and a password.
The output from odbc_db_open/3 is an opaque handle on the
database.
First, odbc_query_open/2 is used to create an SQL query, which is
straightforward.
Then, odbc_query_execute_sql/3 is used to execute the SQL
query. By executing an SQL query a result set is created.
Each consecutive call of odbc_sql_fetch/2 will retrieve one
row from the result set.