RE: FAQ submission: multiple results for a single query

> -----Original Message-----
> From: Garrett Goebel [mailto:garrett@xxxxxxxxxxxxx] 
> Sent: Thursday, October 30, 2003 11:18 AM
> To: 'FreeTDS Development Group'
> Subject: [freetds] FAQ submission: multiple results for a single query
> 
> 
> Thanks for the "multiple result sets" explanation.
> 
> I felt kind of guilty posting because I hadn't checked the 
> FAQ or dug through the mailing list archives extensively for 
> it. On the other hand, I figured if I'd found a bug in the 
> ODBC implementation I might be forgiven.
> 
> This morning I checked the FAQ... and this isn't in there. 
> Here's an attempt to rephrase your explanation into a FAQ 
> submission with some additional examples. Is this the correct 
> way to make a submission? Please check it for errors, 
> omissions, and feel free to add examples where appropriate. 
> For example at the very bottom I've left <todo>'s for odbc 
> and ct-lib examples of moving to and fetching the next result set.
> 
> 
> How do I work with multiple result sets?
> 
> It depends on what you mean by "multiple result sets". 
> FreeTDS supports SQL statements which return multiple 
> consecutive result sets, but not multiple active result sets.

I think this sentence is confusing two separate issues.  What exactly is a
SQL statement which returns "multiple active result sets"?  I don't know of
any single SQL statement that would do that.  I don't like FAQ answers that
strongly depend on a confused viewpoint for interpretation.

> TDS is a strictly "conversational" protocol. The client 
> initiates a call to the server and the server responds. The 
> client can not initiate additional call-response pairs until 
> it has fully dealt with the server's response. I.e., multiple 
> concurrent conversations on the same database connection are 
> not supported.

This is phrased better but it doesn't really address the question I think
you are asking.  The problem is really that the question "How do I work with
multiple result sets?" is a bad question, since it can mean two different
things to different people.

It would be much better to address the two questions separately:  How do I
work with multiple concurrent result sets on the same database handle?  How
do I work with multiple sequential result sets returned from the execution
of a query?

> Multiple consecutive result sets example:
> 
> o  Perl
> 
>   use DBI;
>   my $dbh = DBI->connect(...);
>   my $sth = $dbh->prepare('select 1 select 2');
>   $sth->execute();
>   do {
>     while (my $data = $sth->fetch) {
>         $sth->execute($data->[0]);
>     }
>   } while ($sth->{odbc_more_results});

The *_more_results stuff is specific to the DBD driver.  If your DBI handle
is backed by a DBD::Sybase object, you would need to use "syb_more_results"
instead.  I don't know of a good database-independent way of doing this.

Cheers,
--nick
-----------------------------------------
The information contained in this transmission may contain privileged and 
confidential information and is intended only for the use of the person(s) 
named above. If you are not the intended recipient, or an employee or agent 
responsible for delivering this message to the intended recipient, any review, 
dissemination, distribution or duplication of this communication is strictly 
prohibited. If you are not the intended recipient, please contact the sender 
immediately by reply e-mail and destroy all copies of the original message. 
Please note that we do not accept account orders and/or instructions by e-mail, 
and therefore will not be responsible for carrying out such orders and/or 
instructions.