Skip Menu |
 

This queue is for tickets about the DBD-ODBC CPAN distribution.

Report information
The Basics
Id: 63550
Status: resolved
Priority: 0/
Queue: DBD-ODBC

People
Owner: Nobody in particular
Requestors: john_corcoran [...] trepp.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: 1.26_4



Subject: BUG with statement attribute not returned from $dbh do() command
Date: Thu, 2 Dec 2010 15:48:17 -0500
To: <bug-DBD-ODBC [...] rt.cpan.org>
From: "John Corcoran" <john_corcoran [...] trepp.com>
Download (untitled) / with headers
text/plain 1.8k
Hi, I found the bug using the following modules DBD::ODBC -- 1.23 DBI -- 1.609 DBD::Sybase -- 1.08 Perl Version: v5.8.8 OS: Linux dev6 2.6.18-8.el5 #1 SMP Fri Jan 26 14:15:21 EST 2007 i686 i686 i386 GNU/Linux Basically, I'm writing an error handle for DBI to trap the error message and the actual SQL statement. I'm currently connecting to MS SQL 2005 using both ODBC (EasySoft) and DBD Sybase (freetds). The error only happens with DBD::ODBC. The error happens when you write an incorrect SQL statement using the dbh->do command. In ODBC, I'm unable to return the SQL Statement using the do command, it only works with a prepare statement. Its like the SQL Statements been replaced. Test Script below: use strict; use Carp qw(croak cluck); use DBI; my $dbh = DBI-> connect("dbi:ODBC:DSN=TEST;UID=sampleuser;PWD=sampleuser;Trusted_Connect ion=No;APP=$0;"); #my $dbh = DBI->connect("DBI:Sybase:TEST","sampleuser","sampleuser") || die "DBI connect failed: $DBI::errstr\n"; print "Testing the following Driver: $dbh->{Driver}->{Name} \n"; $dbh->{AutoCommit} = 1; $dbh->{RaiseError} = 0; $dbh->{PrintError} = 0; $dbh->{ShowErrorStatement} = 0; $dbh->{HandleError} = \&_err_handler; $dbh->do("select top 10 * from testTable"); sub _err_handler { my ($error, $h) = @_; print "errorMessage: $error \nerrorSql: $h->{Statement}\n" ; return 0; } ODBC Result: Testing the following Driver: ODBC errorMessage: DBD::ODBC::db do failed: Invalid object name ' testTable '. (SQL-42S02) errorSql: Expected Result: (What I get back from using DBD Sybase) Testing the following Driver: ODBC errorMessage: DBD::ODBC::db do failed: Invalid object name ' testTable '. (SQL-42S02) errorSql: select top 10 * from testTable Thanks John Corcoran
Download (untitled) / with headers
text/plain 2.5k
On Thu Dec 02 15:47:37 2010, john_corcoran@trepp.com wrote: Show quoted text
> Hi, > > I found the bug using the following modules DBD::ODBC -- 1.23 > > DBI -- 1.609 > > DBD::Sybase -- 1.08 > > > > Perl Version: v5.8.8 > > OS: Linux dev6 2.6.18-8.el5 #1 SMP Fri Jan 26 14:15:21 EST 2007 i686 > i686 i386 GNU/Linux > > > > Basically, I'm writing an error handle for DBI to trap the error message > and the actual SQL statement. I'm currently connecting to MS SQL 2005 > using both ODBC (EasySoft) and DBD Sybase (freetds). > > > > The error only happens with DBD::ODBC. The error happens when you write > an incorrect SQL statement using the dbh->do command. In ODBC, I'm > unable to return the SQL Statement using the do command, it only works > with a prepare statement. Its like the SQL Statements been replaced. > > > > Test Script below: > > > > use strict; > > use Carp qw(croak cluck); > > use DBI; > > > > my $dbh = DBI-> > connect("dbi:ODBC:DSN=TEST;UID=sampleuser;PWD=sampleuser;Trusted_Connect > ion=No;APP=$0;"); > > #my $dbh = DBI->connect("DBI:Sybase:TEST","sampleuser","sampleuser") || > die "DBI connect failed: $DBI::errstr\n"; > > > > print "Testing the following Driver: $dbh->{Driver}->{Name} \n"; > > > > $dbh->{AutoCommit} = 1; > > $dbh->{RaiseError} = 0; > > $dbh->{PrintError} = 0; > > $dbh->{ShowErrorStatement} = 0; > > $dbh->{HandleError} = \&_err_handler; > > > > $dbh->do("select top 10 * from testTable"); > > > > sub _err_handler { > > my ($error, $h) = @_; > > print "errorMessage: $error \nerrorSql: $h->{Statement}\n" ; > > return 0; > > } > > > > ODBC Result: > > > > Testing the following Driver: ODBC > > errorMessage: DBD::ODBC::db do failed: Invalid object name ' testTable > '. (SQL-42S02) > > errorSql: > > > > Expected Result: (What I get back from using DBD Sybase) > > > > Testing the following Driver: ODBC > > errorMessage: DBD::ODBC::db do failed: Invalid object name ' testTable > '. (SQL-42S02) > > errorSql: select top 10 * from testTable > > > > > > Thanks > > > > John Corcoran >
I'm sorry I have not replied to this before now but I am supposed to get emails when RTs are posted and yet again I didn't get one this time. The do method works differently in DBD::ODBC as by default it uses SQLExecDirect and not SQLPrepare/SQLExecute. It appears Statement does not get set if the do method is called with no parameters. I will look into this. Thanks Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 3.3k
On Mon Dec 13 12:03:37 2010, MJEVANS wrote: Show quoted text
> On Thu Dec 02 15:47:37 2010, john_corcoran@trepp.com wrote:
> > Hi, > > > > I found the bug using the following modules DBD::ODBC -- 1.23 > > > > DBI -- 1.609 > > > > DBD::Sybase -- 1.08 > > > > > > > > Perl Version: v5.8.8 > > > > OS: Linux dev6 2.6.18-8.el5 #1 SMP Fri Jan 26 14:15:21 EST 2007 i686 > > i686 i386 GNU/Linux > > > > > > > > Basically, I'm writing an error handle for DBI to trap the error message > > and the actual SQL statement. I'm currently connecting to MS SQL 2005 > > using both ODBC (EasySoft) and DBD Sybase (freetds). > > > > > > > > The error only happens with DBD::ODBC. The error happens when you write > > an incorrect SQL statement using the dbh->do command. In ODBC, I'm > > unable to return the SQL Statement using the do command, it only works > > with a prepare statement. Its like the SQL Statements been replaced. > > > > > > > > Test Script below: > > > > > > > > use strict; > > > > use Carp qw(croak cluck); > > > > use DBI; > > > > > > > > my $dbh = DBI-> > > connect("dbi:ODBC:DSN=TEST;UID=sampleuser;PWD=sampleuser;Trusted_Connect > > ion=No;APP=$0;"); > > > > #my $dbh = DBI->connect("DBI:Sybase:TEST","sampleuser","sampleuser") || > > die "DBI connect failed: $DBI::errstr\n"; > > > > > > > > print "Testing the following Driver: $dbh->{Driver}->{Name} \n"; > > > > > > > > $dbh->{AutoCommit} = 1; > > > > $dbh->{RaiseError} = 0; > > > > $dbh->{PrintError} = 0; > > > > $dbh->{ShowErrorStatement} = 0; > > > > $dbh->{HandleError} = \&_err_handler; > > > > > > > > $dbh->do("select top 10 * from testTable"); > > > > > > > > sub _err_handler { > > > > my ($error, $h) = @_; > > > > print "errorMessage: $error \nerrorSql: $h->{Statement}\n" ; > > > > return 0; > > > > } > > > > > > > > ODBC Result: > > > > > > > > Testing the following Driver: ODBC > > > > errorMessage: DBD::ODBC::db do failed: Invalid object name ' testTable > > '. (SQL-42S02) > > > > errorSql: > > > > > > > > Expected Result: (What I get back from using DBD Sybase) > > > > > > > > Testing the following Driver: ODBC > > > > errorMessage: DBD::ODBC::db do failed: Invalid object name ' testTable > > '. (SQL-42S02) > > > > errorSql: select top 10 * from testTable > > > > > > > > > > > > Thanks > > > > > > > > John Corcoran > >
> > I'm sorry I have not replied to this before now but I am supposed to get > emails when RTs are posted and yet again I didn't get one this time. > > The do method works differently in DBD::ODBC as by default it uses > SQLExecDirect and not SQLPrepare/SQLExecute. > > It appears Statement does not get set if the do method is called with no > parameters. I will look into this. > > Thanks > > Martin
It appears there is no statement handle to attach the Statement attribute to when you call do and there are no parameters. DBD::Sybase probably calls prepare/execute methods and the prepare will create a DBI statement handle but DBD::ODBC optimises do calls when there are no parameters into a direct call to SQLExecDirect and there is no DBI statement handle. I'm not sure there is anything I can do about this but at a minimum will document it and may be I'll consult other DBD authors. Bare with me. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 823b
On Mon Dec 13 12:22:48 2010, MJEVANS wrote: Show quoted text
> > It appears there is no statement handle to attach the Statement > attribute to when you call do and there are no parameters. DBD::Sybase > probably calls prepare/execute methods and the prepare will create a DBI > statement handle but DBD::ODBC optimises do calls when there are no > parameters into a direct call to SQLExecDirect and there is no DBI > statement handle. > > I'm not sure there is anything I can do about this but at a minimum will > document it and may be I'll consult other DBD authors. Bare with me. > > Martin
See this thread http://www.mail-archive.com/dbi-dev@perl.org/msg06175.html It appears I could set the Statement attribute on the connection handle. I will try and implement this in the next few days. Martin -- Martin J. Evans Wetherby, UK
Subject: RE: [rt.cpan.org #63550] BUG with statement attribute not returned from $dbh do() command
Date: Tue, 14 Dec 2010 08:31:39 -0500
To: <bug-DBD-ODBC [...] rt.cpan.org>
From: "John Corcoran" <john_corcoran [...] trepp.com>
Download (untitled) / with headers
text/plain 1.3k
That would be great. Thanks a lot. One solution on our end was not to use the do() command, but this was easier said than done since we're moving a lot of our legacy code from DBD Sybase to DBD ODBC which relied heavily on the do() command. Show quoted text
-----Original Message----- From: Martin J Evans via RT [mailto:bug-DBD-ODBC@rt.cpan.org] Sent: Tuesday, December 14, 2010 3:48 AM To: John Corcoran Subject: [rt.cpan.org #63550] BUG with statement attribute not returned from $dbh do() command <URL: https://rt.cpan.org/Ticket/Display.html?id=63550 > On Mon Dec 13 12:22:48 2010, MJEVANS wrote:
> > It appears there is no statement handle to attach the Statement > attribute to when you call do and there are no parameters. DBD::Sybase > probably calls prepare/execute methods and the prepare will create a
DBI
> statement handle but DBD::ODBC optimises do calls when there are no > parameters into a direct call to SQLExecDirect and there is no DBI > statement handle. > > I'm not sure there is anything I can do about this but at a minimum
will
> document it and may be I'll consult other DBD authors. Bare with me. > > Martin
See this thread http://www.mail-archive.com/dbi-dev@perl.org/msg06175.html It appears I could set the Statement attribute on the connection handle. I will try and implement this in the next few days. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 587b
On Tue Dec 14 08:30:56 2010, john_corcoran@trepp.com wrote: Show quoted text
> That would be great. Thanks a lot. One solution on our end was not to > use the do() command, but this was easier said than done since we're > moving a lot of our legacy code from DBD Sybase to DBD ODBC which relied > heavily on the do() command.
I have a version you could try which should make the Statement attribute available from the database handle passed to the error handler. If you can build DBD::ODBC youurself and test it let me know and I'll tell you where to find it. Martin -- Martin J. Evans Wetherby, UK
Subject: RE: [rt.cpan.org #63550] BUG with statement attribute not returned from $dbh do() command
Date: Tue, 14 Dec 2010 11:49:51 -0500
To: <bug-DBD-ODBC [...] rt.cpan.org>
From: "John Corcoran" <john_corcoran [...] trepp.com>
Download (untitled) / with headers
text/plain 982b
Sure can. Just let me know where to find it and I will test it in my environment here. Show quoted text
-----Original Message----- From: Martin J Evans via RT [mailto:bug-DBD-ODBC@rt.cpan.org] Sent: Tuesday, December 14, 2010 10:10 AM To: John Corcoran Subject: [rt.cpan.org #63550] BUG with statement attribute not returned from $dbh do() command <URL: https://rt.cpan.org/Ticket/Display.html?id=63550 > On Tue Dec 14 08:30:56 2010, john_corcoran@trepp.com wrote:
> That would be great. Thanks a lot. One solution on our end was not
to
> use the do() command, but this was easier said than done since we're > moving a lot of our legacy code from DBD Sybase to DBD ODBC which
relied
> heavily on the do() command.
I have a version you could try which should make the Statement attribute available from the database handle passed to the error handler. If you can build DBD::ODBC youurself and test it let me know and I'll tell you where to find it. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 350b
On Tue Dec 14 11:49:04 2010, john_corcoran@trepp.com wrote: Show quoted text
> Sure can. Just let me know where to find it and I will test it in my > environment here.
Should be arriving on a cpan mirror near you soon as 1.26_4. If you cannot see it and want it in the next half hour or so mail me and I can email it to yo. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 479b
On Tue Dec 14 12:06:16 2010, MJEVANS wrote: Show quoted text
> On Tue Dec 14 11:49:04 2010, john_corcoran@trepp.com wrote:
> > Sure can. Just let me know where to find it and I will test it in my > > environment here.
> > Should be arriving on a cpan mirror near you soon as 1.26_4. > > If you cannot see it and want it in the next half hour or so mail me and > I can email it to yo. > > Martin
Any progress on this? Can I write it off in 1.26_4 yet? Martin -- Martin J. Evans Wetherby, UK
Subject: RE: [rt.cpan.org #63550] BUG with statement attribute not returned from $dbh do() command
Date: Wed, 15 Dec 2010 12:52:00 -0500
To: <bug-DBD-ODBC [...] rt.cpan.org>
From: "John Corcoran" <john_corcoran [...] trepp.com>
Download (untitled) / with headers
text/plain 826b
Works perfectly. Thanks for the fix. Show quoted text
-----Original Message----- From: Martin J Evans via RT [mailto:bug-DBD-ODBC@rt.cpan.org] Sent: Wednesday, December 15, 2010 12:10 PM To: John Corcoran Subject: [rt.cpan.org #63550] BUG with statement attribute not returned from $dbh do() command <URL: https://rt.cpan.org/Ticket/Display.html?id=63550 > On Tue Dec 14 12:06:16 2010, MJEVANS wrote:
> On Tue Dec 14 11:49:04 2010, john_corcoran@trepp.com wrote:
> > Sure can. Just let me know where to find it and I will test it in
my
> > environment here.
> > Should be arriving on a cpan mirror near you soon as 1.26_4. > > If you cannot see it and want it in the next half hour or so mail me
and
> I can email it to yo. > > Martin
Any progress on this? Can I write it off in 1.26_4 yet? Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 311b
On Wed Dec 15 12:51:17 2010, john_corcoran@trepp.com wrote: Show quoted text
> Works perfectly. Thanks for the fix. >
Great. Can I be a little cheeky and ask you to provide a small review of DBD::ODBC on "rate this distribution" link at http://search.cpan.org/~mjevans/DBD-ODBC-1.25/. Martin -- Martin J. Evans Wetherby, UK


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.