SPUG: MS SQL & ODBC and nested queries (use odbc_cursortype => 2 in connect)

Aaron W. West tallpeak at hotmail.com
Thu May 13 14:25:52 CDT 2004


The subject says it all. I answered my own question with perldoc DBD::ODBC.
Use dynamic cursors for nested queries, by setting odbc_cursortype => 2 in
DBI->connect. I might have thought that would be the default, but static
cursors are apparently the default.

So this is just for information in case anyone ran into this lately.

-------------

I use DBI 1.42 and DBD::ODBC 1.09.

Recently I noticed that I get ODBC errors when I try using nested queries.
It seems you can't run another query (even with a different statement
handle) when one query is "busy" with results. You need to either call
finish on the statement handle ($sth->finish()) or retrieve all the results
first, before running an execute on another statement handle.

I suppose the standard way to avoid the problem is, as I did, to use a
$sth->fetchall_arrayref for the "outer" query. That was my fix. If anyone
does things differently, I'd be curious to know. I may not like this
approach when my outer-query result set is millions of rows (for backfills,
etc).

Here's an example of the problem (by the way, pubsex.pl means pubs example.
Shame on you for thinking otherwise...)

$ cat pubsex.pl
use DBI;
use DBD::ODBC;
$dsn = "driver={SQL Server};Server=.;database=pubs";
print "enter password:";
$password = <>;
chomp $password;
$dbh = DBI->connect( "dbi:ODBC:$dsn", "SA", $password, { AutoCommit => 1 } )
  || die;
$sql = "select * from sales";
$sth = $dbh->prepare($sql);
$sth->execute();
@row = $sth->fetchrow_array();
print "@row\n";
$sth2 = $dbh->prepare("update titles set price=9.99 where title_id='asdf'")
|| die;
$sth2->execute() || die;

$ perl pubsex.pl
enter password:[my password for SA]
6380 6871 1994-09-14 00:00:00.000 5 Net 60 BU1032
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Connection
is busy with results for another hstmt (SQL-HY000)(DBD:
st_execute/SQLExecute err=-1) at pubsex.pl line 15, <> line 1.
Died at pubsex.pl line 15, <> line 1.

Nevermind, perldoc DBD::ODBC has the answer:

      odbc_cursortype (applies to connect only!)
          This allows multiple concurrent statements on SQL*Server.  In your
          connect, add { odbc_cursortype => 2 }.  If you are using DBI >
          1.41, you should also be able to use { odbc_cursortype =>
          DBI::SQL_CURSOR_DYNAMIC } instead.  For example:

           my $dbh = DBI->connect("dbi:ODBC:$DSN", $user, $pass, {
RaiseError =
 1, odbc_cursortype => 2});
           my $sth = $dbh->prepare("one statement");
           my $sth2 = $dbh->prepare("two statement");
           $sth->execute;
           my @row;
           while (@row = $sth->fetchrow_array) {
              $sth2->execute($row[0]);
           }

          See t/20SqlServer.t for an example.

--

But now my results are different, that's weird:

Oh, it's because I had:

@row = $sth->fetchrow_array() || die;

..and the || die changed the statement in some way such that only the last
element of the row was stored in @row. I guess it's being interpreted as a
scalar?

Sometimes perl is weird...

The version that works with no error:

$ cat pubsex.pl
use DBI;
use DBD::ODBC;
$dsn = "driver={SQL Server};Server=.;database=pubs";
print "enter password:";
$password = <>;
chomp $password;
$dbh = DBI->connect( "dbi:ODBC:$dsn", "SA", $password,
        { AutoCommit => 1, odbc_cursortype => 2 } )  || die;
$sql = "select * from sales";
$sth = $dbh->prepare($sql) || die;
$sth->execute() || die;
@row = $sth->fetchrow_array() or die;
print "@row\n";
$sth2 = $dbh->prepare("update titles set price=9.99 where title_id='asdf'")
|| die;
$sth2->execute() || die;

$ echo [password] | perl pubsex.pl
enter password:6380 6871 1994-09-14 00:00:00.000 5 Net 60 BU1032



More information about the spug-list mailing list