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.

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.


Multiple active result sets example:

a client sends a (pseudo-code) query:

  send_query ("select au_id from authors")
  while (get_returned_row(:my_author)) {
      send_query("delete title_author where au_id = :my_author")
  }

the server response to the initial select query consists of:

o  results metadata describing the result set
o  row data (one for each row returned)
o  done message

An error will occur when the client attempts to initiate an
additional query before dealing with the entire server response.
 

Work-Arounds for this limitation:

o  use a while loop

  declare @id int
  select @id = min(au_id) from authors
  while @id is not null
  begin
      delete title_author where au_id = @id
      select @id = min(au_id) from authors where au_id > @id
  end


o  use a cursor

  declare @id int
  declare au_id_cursor cursor for
  select au_id from authors
  open au_id_cursor
  fetch next from au_id_cursor into @id
  while @@fetch_status = 0
  begin
      delete title_author where au_id = @id
      fetch next from au_id_cursor into @id
  end
  close au_id_cursor
  deallocate au_id_cursor


o  open 2 database connections (Perl)

  use DBI;
  my $dbh1 = DBI->connect(...);
  my $dbh2 = DBI->connect(...);
  my $sth1 = $dbh1->prepare('select au_id from authors');
  my $sth2 = $dbh2->prepare('delete title_author where au_id = ?');
  $sth1->execute();
  while (my $data = $sth->fetch) {
        $sth->execute($data->[0]);
  }


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});


o  ODBC

  <todo>
  see http://lists.ibiblio.org/pipermail/freetds/200q3/002313.html


o  ct-lib

  <todo>

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