2.3.3 Fetching rows explicitely

Normally SQL queries return a result-set that is enumerated on backtracking. Using this approach a result-set is similar to a predicate holding facts. There are some cases where fetching the rows one-by-one, much like read/1 reads terms from a file is more appropriate and there are cases where only part of the result-set is to be fetched. These cases can be dealt with using odbc_fetch/3, which provides an interface to SQLFetchScroll().

As a general rule of thumb, stay away from these functions if you do not really need them. Experiment before deciding on the strategy and often you'll discover the simply backtracking approach is much easier to deal with and about as fast.

odbc_fetch(+Statement, -Row, +Option)

Fetch a row from the result-set of Statement. Statement must be created with odbc_prepare/5 using the option fetch(fetch) and be executed using odbc_execute/2. Row is unified to the fetched row or the atom end_of_file^{6This atom was selected to emphasise the similarity to read.} after the end of the data is reached. Calling odbc_fetch/2 after all data is retrieved causes a permission-error exception. Option is one of:

next

Fetch the next row.

prior

Fetch the result-set going backwards.

first

Fetch the first row.

last

Fetch the last row.

absolute(Offset)

Fetch absolute numbered row. Rows count from one.

relative(Offset)

Fetch relative to the current row. relative(1) is the same as next, except that the first row extracted is row 2.

bookmark(Offset)

Reserved. Bookmarks are not yet supported in this interface.

In many cases, depending on the driver and RDBMS, the cursor-type must be changed using odbc_set_connection/2 for anything different from next to work.

Here is example code each time skipping a row from a table‘test' holding a single column of integers that represent the row-number. This test was executed using unixODBC and MySQL on SuSE Linux.

fetch(Options) :-
        odbc_set_connection(test, cursor_type(static)),
        odbc_prepare(test,
                     'select (testval) from test',
                     [],
                     Statement,
                     [ fetch(fetch)
                     ]),
        odbc_execute(Statement, []),
        fetch(Statement, Options).

fetch(Statement, Options) :-
        odbc_fetch(Statement, Row, Options),
        (   Row == end_of_file
        ->  true
        ;   writeln(Row),
            fetch(Statement, Options)
        ).

odbc_close_statement(+Statement)

Closes the given statement (without freeing it). This must be used if not the whole result-set is retrieved using odbc_fetch/3.