Did you know ... | Search Documentation: |
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.
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:
relative(1)
is the same
as next
, except that the first row extracted is row 2.
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) ).