PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
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 8 1997 10:56PM

> From: Michael Peppler 
> 
> 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)
> 
> 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.

Okay. In this scenario I'd say just return them as one stream and let the
application deal with it (or not, as the case may be :-).

Where there are two separate queries concatenated, unlike this case, then
perhaps there's a role to be played by an intermediate 'end of data' return
from fetch.

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

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

I meant to say NUM_OF_FIELDS (etc). Sorry.

Tim.