Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

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?