ODBC Example 2 (SICStus Prolog)

:- use_module(library(odbc)).

example_select :-
    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).

As always, you begin by opening an environment. 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.

10.31.2.2 Example 2