RE: FAQ submission: multiple results for a single query

Castellano, Nicholas wrote:
> Garrett Goebel wrote:
> > 
> > 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.

A victim of trying to keep it short. How about:

How do I work with multiple result sets?

It depends on what you mean by "multiple result sets".
FreeTDS supports multiple consecutively active result sets,
but not multiple concurrently active result sets.

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

I wouldn't argue that it is a bad question. But it is one that has been
frequently asked.

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

Indeed. Perhaps the FAQ would be better served by three entries. One to
disambiguate the two different meanings, and one more for each. It'd be easy
enough to split what I've rehashed into 3 entries.
 

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


Noted. Here it is using Michael Peppler's suggestion:

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->more_results());


thanks,

Garrett

--
Garrett Goebel
IS Development Specialist 

ScriptPro                   Direct: 913.403.5261 
5828 Reeds Road               Main: 913.384.1008 
Mission, KS 66202              Fax: 913.384.2180 
www.scriptpro.com          garrett at scriptpro dot com