odbc_fetch/3

Availability::- use_module(library(odbc)).(can be autoloaded)

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^{7This
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)
        ).