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