Skip to Content.
Sympa Menu

freetds - [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: [freetds] FAQ submission: multiple results for a single query
  • Date: Thu, 30 Oct 2003 10:17:56 -0600

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