Most SQL queries return only a single result set - a list of rows.
However, some queries can return more than one result set. For example,’SELECT
1; SELECT 2' is a batch query that returns a single row (1) and then a
single row(2). Queries involving stored procedures can easily generate
such results.
To retrieve data from a subsequent result set, odbc_next_result_set/1
can be used, but only for prepared queries which were prepared with
fetch(fetch) as the fetch style in the option list.
- odbc_next_result_set(+Statement)
- Succeeds if there is another result set, and positions the cursor at the
first row of the new result set. If there are no more result sets, the
predicate fails.
fetch(Options) :-
odbc_prepare(test,
'select (testval) from test; select (anotherval)
from some_other_table',
[],
Statement,
[ fetch(fetch)
]),
odbc_execute(Statement, []),
fetch(Statement, Options).
fetch(Statement, Options) :-
odbc_fetch(Statement, Row, Options),
( Row == end_of_file
-> ( odbc_next_result_set(Statement)
-> writeln(next_result_set),
fetch(Statement, Options)
; true
)
; writeln(Row),
fetch(Statement, Options)
).
