freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
[freetds] FAQ submission: multiple results for a single query
- 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
-
[freetds] FAQ submission: multiple results for a single query,
Garrett Goebel, 10/30/2003
- <Possible follow-up(s)>
-
RE: [freetds] FAQ submission: multiple results for a single query,
Castellano, Nicholas, 10/30/2003
- RE: [freetds] FAQ submission: multiple results for a single query, Michael Peppler, 10/30/2003
- RE: [freetds] FAQ submission: multiple results for a single query, Bort, Paul, 10/30/2003
- RE: [freetds] FAQ submission: multiple results for a single query, Castellano, Nicholas, 10/30/2003
- RE: [freetds] FAQ submission: multiple results for a single query, Castellano, Nicholas, 10/30/2003
- RE: [freetds] FAQ submission: multiple results for a single query, Garrett Goebel, 10/30/2003
- RE: [freetds] FAQ submission: multiple results for a single query, Castellano, Nicholas, 10/30/2003
- RE: [freetds] FAQ submission: multiple results for a single query, Garrett Goebel, 10/30/2003
- RE: [freetds] FAQ submission: multiple results for a single query, Castellano, Nicholas, 10/30/2003
Archive powered by MHonArc 2.6.24.