Re: Multiple Result Sets In A select Statement In Sybase

On 02/02/12 21:40, Melillo, Joseph wrote:
> Hi,
>
> I've looked at every resource I can find to try and find this out for
> myself, but I couldn't.  Therefore, I apologize if this is too simple
> a question.  If it is, I'd appreciate a pointer to the appropriate
> documentation where I can read further.
>
> Anyway, in Sybase, one can write a store procedure with more than one
> select statement in it.  As a result, the stored procedure returns
> more than one result set (i.e. one per select statement).  I'm trying
> to handle this in my script (Perl 5.8.8), but the loop terminates
> when all the rows of the first result set have been fetched,
> indicating that a call needs to be made to go to the next result
> set.

In ODBC, you'd call the SQLMoreResults API at this point and it would tell you if there was another result-set and you can restart your fetch if there is.

> There was a way to handle this in Sybase::DBlib ($dbh->dbresults).
> Also, using $dbh->nsql() returned all the result sets in an array of
> hashrefs, with the keys of the hashref representing the different
> fields of the different result sets, thus eliminating the need to
> switch from result set to result set.  Even if $sth->fetchall_hashref
> in DBI performs similarly to $dbh->nsql() in Sybase::DBlib, I'd
> rather fetch my rows one at a time.
>
> Is there an equivalent of $dbh->dbresults in DBI?

Not in DBI itself but there is in some DBDs. DBD::ODBC has odbc_more_results method and DBD::Sybase does it by adding an empty row at the end of the result-set and setting syb_more_results.  If you are using DBD::Sybase search for "Handling Multiple Result Sets" in the pod.

You haven't said which DBD you are using so it is difficult to say.
  
> Thanks in advance for any help you can give me!
>
> -Joe Melillo [email protected]

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com