RE: Multiple Result Sets In A select Statement In Sybase
That's exactly what I was hoping! Thanks!!
-Joe Melillo
joseph.meli...@moodys.com
-Original Message-
From: Martin J. Evans [mailto:martin.ev...@easysoft.com]
Sent: Friday, February 03, 2012 10:38 AM
To: Melillo, Joseph
Cc: dbi-users@perl.org
Subject: Re: Multiple Result Sets In A select Statement In Sybase
On 03/02/12 15:12, Melillo, Joseph wrote:
> I'm using a Sybase driver, but only through
> DBI->connect("dbi:Sybase:server=..." ... ). I'm not using the DBD
> package at all, at least not directly.
>
> Thanks very much for your help!! I have a feeling the information
> you gave me will bear fruit!!
>
>
> -Joe Melillo joseph.meli...@moodys.com
So you are using DBD::Sybase - i.e., the Sybase in 'dbi:Sybase:' tells DBI
to load DBD::Sybase. As a result, all the Sybase specific stuff will be
documented in DBD::Sybase.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
-
The information contained in this e-mail message, and any attachment thereto,
is confidential and may not be disclosed without our express permission. If you
are not the intended recipient or an employee or agent responsible for
delivering this message to the intended recipient, you are hereby notified that
you have received this message in error and that any review, dissemination,
distribution or copying of this message, or any attachment thereto, in whole or
in part, is strictly prohibited. If you have received this message in error,
please immediately notify us by telephone, fax or e-mail and delete the message
and all of its attachments. Thank you. Every effort is made to keep our network
free from viruses. You should, however, review this e-mail message, as well as
any attachment thereto, for viruses. We take no responsibility and have no
liability for any computer virus which may be transferred via this e-mail
message.
Re: Multiple Result Sets In A select Statement In Sybase
On 03/02/12 15:12, Melillo, Joseph wrote:
I'm using a Sybase driver, but only through
DBI->connect("dbi:Sybase:server=..." ... ). I'm not using the DBD
package at all, at least not directly.
Thanks very much for your help!! I have a feeling the information
you gave me will bear fruit!!
-Joe Melillo joseph.meli...@moodys.com
So you are using DBD::Sybase - i.e., the Sybase in 'dbi:Sybase:' tells DBI
to load DBD::Sybase. As a result, all the Sybase specific stuff will be
documented in DBD::Sybase.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
RE: Multiple Result Sets In A select Statement In Sybase
I'm using a Sybase driver, but only through
DBI->connect("dbi:Sybase:server=..." ... ). I'm not using the DBD package at
all, at least not directly.
Thanks very much for your help!! I have a feeling the information you
gave me will bear fruit!!
-Joe Melillo
joseph.meli...@moodys.com
-Original Message-
From: Martin J. Evans [mailto:martin.ev...@easysoft.com]
Sent: Friday, February 03, 2012 3:32 AM
To: Melillo, Joseph
Cc: dbi-users@perl.org
Subject: 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 joseph.meli...@moodys.com
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
-
The information contained in this e-mail message, and any attachment thereto,
is confidential and may not be disclosed without our express permission. If you
are not the intended recipient or an employee or agent responsible for
delivering this message to the intended recipient, you are hereby notified that
you have received this message in error and that any review, dissemination,
distribution or copying of this message, or any attachment thereto, in whole or
in part, is strictly prohibited. If you have received this message in error,
please immediately notify us by telephone, fax or e-mail and delete the message
and all of its attachments. Thank you. Every effort is made to keep our network
free from viruses. You should, however, review this e-mail message, as well as
any attachment thereto, for viruses. We take no responsibility and have no
liability for any computer virus which may be transferred via this e-mail
message.
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 joseph.meli...@moodys.com Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
