sybperl-l Archive

From: Michael Peppler <mpeppler at MBAY dot NET>
Subject: Handling multiple result sets
Date: Sep 5 1997 4:00PM

Hi!

I've finally started work on the real DBD::Sybase module, and I've run
into a problem which I suspect others have solved before me.

Sybase allows the following SQL syntax:

SELECT select_list FROM table WHERE condition ORDER BY clause 
COMPUTE aggregate expression BY column

For example, I have a simple bug tracking database. I have a table
called bug_log where each log/comment message for a bug is stored. The
bugs are identified by the id column.

I could write:

select * from bug_log order by id compute count(id) by id

to get both the content of the table and the number of rows for each
bug. Now the problem here is that we get rows of different widths back:
first N rows for the comments to the first bug, then a single row with
the value N, then M rows for the second bug, then a single row with the 
value M, etc.

The Sybase API is based on writing a nested loop:

while(ct_results(&restype) == CS_SUCCEED) {
    /* If result is fetchable */
    while(ct_fetch()) {
        /* here we do something with the data */
    }
}

which the DBI doesn't quite map to (at least not the way I understand
it :-).

I talked about this briefly with Tim B. at the Perl Conference, and
he came up with the idea of having $sth->fetch() return an empty
row, so that it could be called again to get the next result set. 
Another way would be to make $dbh->execute retryable, though I
don't know if that would break the DBI specs.

Comments and suggestions are welcome!

Michael

PS. I don't know how many people actually use the COMPUTE BY clause (I
know I don't, mostly) but it's handy when you'd like to retrieve data
and throw it into an HTML table - with the summary results already 
there.
-- 
Michael Peppler       -||-  Data Migrations Inc.
mpeppler@datamig.com  -||-  http://www.mbay.net/~mpeppler