Skip to Content.
Sympa Menu

freetds - RE: [freetds] FAQ submission: multiple results for a single query

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Garrett Goebel <garrett AT scriptpro.com>
  • To: 'FreeTDS Development Group' <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] FAQ submission: multiple results for a single query
  • Date: Thu, 30 Oct 2003 14:23:06 -0600

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 https://lists.ibiblio.org/sympa/arc/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





Archive powered by MHonArc 2.6.24.

Top of Page