sybperl-l Archive

From: Tim Bunce <Tim dot Bunce at ig dot co dot uk (Tim Bunce)>
Subject: Re: Handling multiple result sets
Date: Sep 5 1997 7:14PM

> From: Michael Peppler 
> 
> Hi!

Hi Michael.

> 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.

Nope. It's Sybase specific.

> 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 :-).

How do you deal with meta-data like the number of fields or the data type
of each field? I presume each time round the loop all that meta data
can change.

> 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,

i.e. Look like the end of the data - to represent here the end of a data set.

> 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.

What do you mean by retryable?

> Comments and suggestions are welcome!

The only problem that I can see with the $sth->fetch approach is the
timing of the change in meta-data values.

	$sth->fetch;	# returns last row of data set 1
	# point A
	$sth->fetch;	# returns nothing (end of data set)
	# point B
	$sth->fetch;	# returns first row of data set 2
	# point C

At what point should $sth->{NUM_OF_ROWS} change?

Tim.