sybperl-l Archive

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

Tim Bunce wrote:
> 
> > 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.

Oh well - we'll do some experimenting then :-)

> 
> > Sybase allows the following SQL syntax:
> >
> > SELECT select_list FROM table WHERE condition ORDER BY clause
> > COMPUTE aggregate expression BY column
> >

Here's what Sybase returns for this query in isql (the interactive,
command line interface):

1> select id, convert(char(10), log_by), log_date from BugTrack..bug_log
where id < 5 order by id compute count(id) by id
2> go
 id                     log_date                   
 ----------- ---------- -------------------------- 
           2 mpeppler          Feb 17 1995  3:45PM 
           2 mpeppler          Feb 17 1995  3:48PM 
           2 mpeppler          Feb 17 1995  4:07PM 
           2 mpeppler          Feb 17 1995  4:15PM 
 count                                             
 -----------                                       
           4                                       

 id                     log_date                   
 ----------- ---------- -------------------------- 
           3 mpeppler          Apr 19 1995 11:21AM 
 count                                             
 -----------                                       
           1                                       

 id                     log_date                   
 ----------- ---------- -------------------------- 
           4 mpeppler          Apr 19 1995 11:21AM 
 count                                             
 -----------                                       
           1                                       

(9 rows affected)
1> 

As you can see, the row count is the count of the *normal* rows returned
by the query. The compute by rows are additional rows.

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

Yes.

In this case, the Sybase::CTlib module would look like this:

$dbh->ct_execute("
select id, log_by, log_date from BugTrack..bug_log where id < 5 order by
id compute count(id) by id
");

while($dbh->ct_results($restype) == CS_SUCCEED) {
    print "restype = $restype\n";
    if($dbh->ct_fetchable($restype)) {
	print "fetchable data:\n";
	while(@dat = $dbh->ct_fetch) {
	    print "\t@dat\n";
	}
    }
    print "Rows: ", $dbh->ct_res_info(CS_ROW_COUNT), "\n" 
	if $restype == CS_CMD_DONE;
}

Which gives the following (truncated) output:

kiruna (2:34pm):10 > perl ct.pl
restype = 4040
fetchable data:
	2 mpeppler Feb 17 1995  3:45:30:750PM
	2 mpeppler Feb 17 1995  3:48:39:756PM
	2 mpeppler Feb 17 1995  4:07:38:723PM
	2 mpeppler Feb 17 1995  4:15:02:040PM
restype = 4045
fetchable data:
	4

(etc...)

restype = 4040
fetchable data:
	4 mpeppler Apr 19 1995 11:21:49:690AM
restype = 4045
fetchable data:
	1
restype = 4046
Rows: 9


As you see, sending a query with a compute by clause causes the 
result set to be broken up. This is different from executing a
stored proc that contains to SELECT clauses, both of which need to 
be processed (which again is perfectly valid in Transact SQL).

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

I was thinking of being able to call $sth->execute again to get
the next result set. I don't know if this is valid DBI code:
while($sth->execute) {
	while(@dat = $sth->fetch) {
		do something here...
	}
}
$sth->finish;

I sort of doubt it...

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

As shown in the example above, $sth->{NUM_OF_ROWS} is really only
set after the entire query has been processed (at least in Sybase's
view). We could of course change that and set it after each sub-set.

Michael
-- 
Michael Peppler       -||-  Data Migrations Inc.
mpeppler@datamig.com  -||-  http://www.mbay.net/~mpeppler