RE: FAQ submission: multiple results for a single query

Here it is broken into 3 entries:


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.


How do I work with multiple concurrently active result sets?

You don't.

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.

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


How do I work with multiple consecutively active result sets?

o  Perl

  use DBI;
  my $dbh = DBI->connect(...);
  my $sth = $dbh->prepare('select 1 select 2');
  $sth->execute();
  do {
    while (my $data = $sth->fetch) {
          print "@$data\n";
    }
  } while ($sth->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