1 The SQL-ODBC Library

1.4 Data retrieval using result-set collection

Executing an SQL SELECT statement by invoking the execute function on the instance of <sql-statement> that represents the statement yields a result set.

A result set is a Dylan collection which encapsulates the protocol necessary to retrieve data from a database. The SQL-ODBC library defines two subclasses of <result-set> that provide different behaviors and performance characteristics. The type of the result set returned by the execute function is determined by the result-set policy supplied to the function or macro.

There are two subclasses of <result-set>: <forward-only-result-set> and <scrollable-result-set>.

The <forward-only-result-set> class provides an efficient means of accessing the elements of a result set. Efficiency is achieved by performing minimal processing on each record retrieved and by maintaining in memory only the current record. Implicit in this behavior is that records you have accessed previously are no longer available to your application; if you maintain references to previous records behavior is unpredictable. The key for each access must always be greater than or equal to the previous access's key; otherwise, a condition is signaled.

The <scrollable-result-set> class allows your application to access elements of the result-set collection in any order, meaning that records you have accessed previously can be revisited. Scrollable result sets retrieve records synchronously.

Example:

This example returns a list of authors who have published two or more books.

(result-set-policy: make(<scrollable-result-set-policy>))
    select last_name, first_name, count(*)
    from author, book_author
    where book_author.author_id = author.author_id
    group by last_name, first_name
    having count(*) > 2
  end;
=> #(#("Date", "Chris", 2))

let query = make(<sql-statement>,
                text: "select last_name, first_name, count(*)"
                      "from author, book_author"
                      "where book_author.author_id 
                         = author.author_id"
                      "group by last_name, first_name having                          count(*) >= 2");
execute(query, result-set-policy: $scrollable-result-set-policy);