In some cases (specifically when using a cursor on a query rather than a "normal" stream request) TDS will allow more than one open statement handle (at least with Sybase open client).

I suspect that FreeTDS may be more limited in this case than the native MS client.

Michael

PS - DBD::Sybase has a work-around for this where it "cheats" by opening additional connection handles where necessary. That's actually a pretty ugly hack that can lead to deadlocks, etc...

The TDS protocol was originally limited to one statement handle per database connection. You can see the same issue with DBD::Sybase using the Sybase client.

Aha! This is the content that I was looking for.

And this is fine -- I just needed to really determine exactly what the limitation was, and then get my brain to suggest a workaround (not really a hack). And now I have it. Thanks to all who answered -- I appreciate you! If you're in Vegas next month, hit me up for a beer, and I will gladly buy. :)

Alex / talexb / Toronto

Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

What happens when you try to instantiate a new one?

Relevant code:

 27     my $sth1 = $dbh->prepare ( $q1 );
 28     my $sth2 = $dbh->prepare ( $q2 );
 29
 30     print "INFO: If we got this far, we were able to create two \
statement handles.\n";
 31
 32     $sth1->execute or
 33       die ( "FATAL: Unable to execute q1: " . $sth1->errstr );
 34     $sth2->execute or
 35       die ( "FATAL: Unable to execute q2: " . $sth1->errstr );
[download]

Here's the error:

INFO: If we got this far, we were able to create two statement handles
+.
DBD::ODBC::st execute failed: [FreeTDS][SQL Server]Invalid cursor stat
+e (SQL-24000) at two_handles.pl line 34.
FATAL: Unable to execute q2: [FreeTDS][SQL Server]Invalid cursor state
+ (SQL-24000) at two_handles.pl line 34.
Issuing rollback() due to DESTROY without explicit disconnect() of DBD
+::ODBC::db handle Driver=FreeTDS;ServerName=SqlServer;Database=FOOBAR
+ at two_handles.pl line 34.
[download]

So FreeTDS is reporting on an invalid cursor state .. but I don't know if it's FreeTDS' error, or if that's coming from DBD::ODBC. I may need to start spelunking.

Alex / talexb / Toronto

Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

Yeah, OK -- it looks like it's not DBD::ODBC.

The FreeTDS file freetds/src/tds/mem.c has a routine tds_alloc_lookup_sqlstate(TDSSOCKET * tds, int msgno) with a switch statement that appears to map a couple of errors to 24000; the first one is SQLS_ENTRY(16999, "24000"); /* Invalid cursor state */.

In the file freetds/src/odbc/error.c I see a line that appears to report an ODBC error of 24000 with the same text: ODBCERR("24000", "Invalid cursor state"),

So I've ansered my own question.

Alex / talexb / Toronto

Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.