in reply to Re: DBD::ODBC and FreeTDS / limited to one statement handle
in thread DBD::ODBC and FreeTDS / limited to one statement handle

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.

Replies are listed 'Best First'.
Re^3: DBD::ODBC and FreeTDS / limited to one statement handle
by talexb (Chancellor) on May 15, 2024 at 00:48 UTC

    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.

[reply]
[d/l]
[select]

      So it turns out I'm a goof (this is not surprising), and after posting here, ruminating, and digging through the FreeTDS code, my brain proposed another solution. If I can't have multiple statement handles for a single database handle, can I have multiple database handles, each with its own statement handle? I should try that, right? The solution couldn't be that obvious, right? RIGHT?

      Good grief. I just tried it, and it works. I just created two $dbh guys, each with their own $sth; they each called my good friend fetchrow_hashref, and it all went swimmingly.

      I understand this is called Rubber Duck Debugging. Now it's time for some Scotch to celebrate. You goofball.

      Alex / talexb / Toronto

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

[reply]
[d/l]
[select]

        Many database connections don't like more than a single active statement handle that hasn't been completely fetched ("cursor"). I know that SQLite dislikes this.

        My usual approach is to either convert the two parallel SQL statements into a single SQL statement, likely with a JOIN, or to use ->fetchall_arrayref to first read all of the rows of the first statement and then process the second statement. My machines usually have enough memory for the second approach.

[reply]
[d/l]
        So it turns out I'm a goof (this is not surprising)

        Bolding emphasis mine, which is what I'm responding to here. That's absolute hogwash. You are one of the most methodical, logical and practical thinkers here on Perlmonks, and I've felt that for what, two plus decades.

        You hit, as you said, a de-facto situation of Rubber Duck Debugging. You're not the Maple Leafs... you're a Perl Hacker ;)

        I know what it's like to have that "sigh, I'm an idiot" moment. Awesome feeling, isn't it!

[reply]

In Section Seekers of Perl Wisdom