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

Peter Darley pdarley at kinesis-cem.com
Fri May 14 10:19:23 CDT 2004


David,
	I'm not super hip to the lingo, and I'm not sure whas a DSN is, but I'm
connecting to a SQL Server directly using DBI::Sybase.  Does that fit your
bill, or is it using the mysterious DSN?
Thanks,
Peter Darley

-----Original Message-----
From: spug-list-bounces at mail.pm.org
[mailto:spug-list-bounces at mail.pm.org]On Behalf Of David Innes
Sent: Thursday, May 13, 2004 5:45 PM
To: 'Aaron W. West'; spug-list at mail.pm.org
Subject: RE: SPUG: MS SQL & ODBC and nested queries (use odbc_cursortype
=> 2inconnect)


Doh!  If I'd figured that out I'd be using DBI to this day.  I started
hooking SQL Server directly via the ADO object and now it's too entrenched.

Speaking of DBD and MS Sql Server, it's irksome that there's still no
direct, DSN-less DBI::MSSQL module (at least last time I looked) since
that's always faster, and less complex than ODBC and DSN.

I mean, if you can write directly to SQL with ADO it shouldn't be that hard
to develop a DBI for it.  (Actually last time I looked there was an DBI::ADO
but it was still labeled experimental.)

By the way, I'd be delighted to be made a fool of if someone can prove me
wrong about lack of SQLServer and/or solid ADO support for those of us in
the Windows world.

		-- David Innes

----Original Message-----
From: spug-list-bounces at mail.pm.org [mailto:spug-list-bounces at mail.pm.org]
On Behalf Of Aaron W. West
Sent: Thursday, May 13, 2004 12:26 PM
To: spug-list at mail.pm.org
Subject: SPUG: MS SQL & ODBC and nested queries (use odbc_cursortype => 2
inconnect)

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
_____________________________________________________________
Seattle Perl Users Group Mailing List
POST TO: spug-list at mail.pm.org  http://spugwiki.perlocity.org
ACCOUNT CONFIG: http://mail.pm.org/mailman/listinfo/spug-list
MEETINGS: 3rd Tuesdays, Location Unknown
WEB PAGE: http://www.seattleperl.org

_____________________________________________________________
Seattle Perl Users Group Mailing List
POST TO: spug-list at mail.pm.org  http://spugwiki.perlocity.org
ACCOUNT CONFIG: http://mail.pm.org/mailman/listinfo/spug-list
MEETINGS: 3rd Tuesdays, Location Unknown
WEB PAGE: http://www.seattleperl.org




More information about the spug-list mailing list