FreeTDS Development Group

["Lowden, James K" <james.k.lowden AT alliancebernstein.com>]

> From: Frediano, VF-IT
> Sent: Thursday, June 29, 2006 2:45 AM
> 
> > > From: Lowden, James K Sent: Wednesday, June 28, 2006 11:04 AM
> > > 
> > > > From: ZIGLIO, Frediano, VF-IT Sent: Wednesday, June 28, 2006
> > > > 8:23 AM
> > > > 
> > > > > I'm getting an invalid cursor state error trying to fetch
> > > > > data from a stored procedure using prepare/execute from
> > > > > DBD::ODBC.  It looks like DBD::ODBC thinks there is more
> > > > > than one result set and is trying to get column descriptors
> > > > > when there are no metadata.
> > 
> > Simpler still, src/odbc/unittests/funccall.c fails:
> > 
> > ================= $ tsql -C | grep Ver; isql --version; cat
> > typescript
> >                            Version: freetds v0.64.dev.20060127
> 
> 0.64.dev.20060127 ??? This is not RC2...
> 
> I think a unixODBC upgrade will fix all above. 

OK.  I updated unixODBC backed up to HEAD of 0.64 branch.  As you say,
all tests now pass.  DBD::ODBC, however, still exhibits the same
problem.  

$ isql --version
unixODBC 2.2.11

$ tsql -C | grep Ver
Version: freetds v0.62.5.dev.20060629

$ pwd; tail -3  nohup.out
/usr/users/home/jklowden/projects/freetds.cvs/releases/0.62/freetds/buil
d/src/odbc/unittests
===================
All 22 tests passed
===================

Using samples/odbc_rpc.pl (from CVS HEAD) on this configuration has the
same problems.  (I changed the code slightly because $rc was used both
for $return_status and to get Perl's return code.)  

$ perl odbc_rpc.pl -D dbi:ODBC:mpquant -U $U -P$P simdb..IsFreeTdsBroken
odbc_async_exec is: 0
Binding parameter #1, the return code

Executing: "{? = call simdb..IsFreeTdsBroken }" with parameters ''
execute returned: '-1'
Result #1:
[LatestInputsmDate]
'2006-06-01 00:00:00.000'
(return status is 19)
Result #2:
===> state: HY010 msg: Function sequence error
DBD::ODBC::st fetchrow failed: (DBD: st_fetch/SQLFetch err=-1) at
odbc_rpc.pl line 115.

Error is slightly different, problem the same: $sth->{Active} returns
TRUE after the last row was read.  

So I tried the technique suggested by  "perldoc DBI":

=== fetching code ===
my @names = @{$sth->{NAME}};    # print column names for each result set
print '[', join("], [", @{$sth->{NAME}}), "]\n" if @names;
$i = 1;
my $row;
while($row = $sth->fetchrow_arrayref) {
        print q('), join(q(', '), @$row), qq('\n);
        print qq((return status is $return_status)\n);
}
print qq((return status is $return_status)\n);
=== end fetching code ===

$ perl odbc_rpc.pl -D dbi:ODBC:mpquant -U $U -P$P simdb..IsFreeTdsBroken
odbc_async_exec is: 0
Binding parameter #1, the return code

Executing: "{? = call simdb..IsFreeTdsBroken }" with parameters ''
execute returned: '-1'
[LatestInputsmDate]
'2006-06-01 00:00:00.000'
(return status is 19)
(return status is 0)
                 ^^^ correct!
DBI::db=HASH(0x8200228)->disconnect invalidates 1 active statement
handle (either destroy statement handles or call finish on them before
disconnecting) at odbc_rpc.pl line 132.

In this case, I'm not using $sth->{Active}, but DBD::ODBC evidently is,
and complains because $sth is still "active" even after
fetchrow_arrayref() stops returning data and $return_status (the bound
parameter) is populated.  

Please see diff below for my changes to odbc_rpc.pl.

> I recently updated
> raiserror test to relax test if DM present.  

Understood.  

> Do you think that a
> setbuf(stdout, NULL) in common.c:Connect could help?? 

It's OK as it is, or you can change it if you want to.  It looks right
on the terminal; only redirected can it be confusing sometimes.  Doesn't
come up very often.  

I think the next thing to do is look at how DBD::ODBC implements the
Active property of a statement handle.... 

--jkl

=== patch follows ===
--- ../../../../freetds/samples/odbc_rpc.pl     2005-12-09
10:32:19.000000000 -0500
+++ ./odbc_rpc.pl       2006-06-29 13:32:52.000000000 -0400
@@ -78,9 +78,9 @@
 my $sth = $dbh->prepare( $sql );
 
 # Bind the return code as "inout".
-my $rc;
+my $return_status = 19;
 print STDERR qq(Binding parameter #1, the return code\n);
-$sth->bind_param_inout(1, \$rc, SQL_INTEGER);
+$sth->bind_param_inout(1, \$return_status, SQL_INTEGER);
 
 # Bind the input parameters (we don't do outputs in this example).
 # Placeholder numbers are 1-based; the first "parameter" 
@@ -104,18 +104,31 @@
             join(q(', '), @ARGV[1..$#ARGV]), qq('\n);
 
 # Execute the SQL and print the (possibly several) results
-$rc = $sth->execute;
+my $rc = $sth->execute;
 print STDERR qq(execute returned: '$rc'\n) if $rc;
 
+# new way
+my @names = @{$sth->{NAME}};   # print column names for each result set
+print '[', join("], [", @{$sth->{NAME}}), "]\n" if @names;
 $i = 1;
-while ( $sth->{Active} ) { 
-       printf "Result #%d:\n", $i++;
-        my @names = @{$sth->{NAME}};   # print column names for each
result set
-       print '[', join("], [", @{$sth->{NAME}}), "]\n" if @names;
-       while(@dat = $sth->fetchrow) {
-               print q('), join(q(', '), @dat), qq('\n);
-       }
+my $row;
+while($row = $sth->fetchrow_arrayref) {
+       print q('), join(q(', '), @$row), qq('\n);
+       print qq((return status is $return_status)\n);
 }
+print qq((return status is $return_status)\n);
+# end new way
+
+##  $i = 1;
+##  while ( $sth->{Active} ) { 
+##     printf "Result #%d:\n", $i++;
+##          my @names = @{$sth->{NAME}};       # print column names for
each result set
+##     print '[', join("], [", @{$sth->{NAME}}), "]\n" if @names;
+##     while(@dat = $sth->fetchrow) {
+##             print q('), join(q(', '), @dat), qq('\n);
+##             print qq((return status is $return_status)\n);
+##     }
+##  }
 
 $dbh->disconnect();
 

-----------------------------------------
The information contained in this transmission may be privileged and
confidential and is intended only for the use of the person(s) named
above. If you are not the intended recipient, or an employee or agent 
responsible
for delivering this message to the intended recipient, any review, 
dissemination,
distribution or duplication of this communication is strictly prohibited. If 
you are
not the intended recipient, please contact the sender immediately by reply 
e-mail
and destroy all copies of the original message. Please note that we do not 
accept
account orders and/or instructions by e-mail, and therefore will not be 
responsible
for carrying out such orders and/or instructions.  If you, as the intended 
recipient
of this message, the purpose of which is to inform and update our clients, 
prospects
and consultants of developments relating to our services and products, would 
not
like to receive further e-mail correspondence from the sender, please "reply" 
to the
sender indicating your wishes.  In the U.S.: 1345 Avenue of the Americas, New 
York,
NY 10105.