10.14.2.2 Example 2

This example is a simple SQL query using a fixed SQL string.

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