Skip Menu |
 

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

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

People
Owner: Nobody in particular
Requestors: Damon.Atkins [...] contracted.pmintl.com
Cc:
AdminCc:

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



Subject: prepared_cache invalid after error
Date: Sun, 10 Oct 2010 22:55:28 +0200
To: "bug-DBI [...] rt.cpan.org" <bug-DBI [...] rt.cpan.org>
From: "Atkins, Damon (contracted)" <Damon.Atkins [...] contracted.pmintl.com>
For example if you prepare_cache('insert....') (Perl on Win32 ODBC to MSSQL) and then insert a NULL into a non-null field, an execute error occurs. Then you come back to do a valid insert, it freezes at the execute. (I thought I had an SQL dead lock) I found the fix was to delete the dbi hash used by the prepare_cache on execute errror, so that when prepare_cache is called again it is completed from scratch (ie no cache). DBD.pm 9530 2007-05-09 13:05:23Z W32ODBC.pm 8696 2007-01-24 23:12:38Z timbo $ This is perl, v5.8.8 built for MSWin32-x86-multi-thread (with 18 registered patches, see perl -V for more detail) Cheers Damon Show quoted text
________________________________ NOTICE: This e-mail may contain confidential information, which should not be copied or distributed without authorization. If you have received this e-mail message by mistake, please inform the sender and delete it from your system. Please note that, for the efficient preservation of Company records that may be required for litigation, e-mail messages sent to the author of this message will be copied and may be retained in a secure repository.
Moved to DBD-ODBC as I'm sure this isn't a bug in DBI itself.
Download (untitled) / with headers
text/plain 977b
On Sun Oct 10 16:55:59 2010, Damon.Atkins@contracted.pmintl.com wrote: Show quoted text
> For example if you prepare_cache('insert....') (Perl on Win32 ODBC to > MSSQL) > and then insert a NULL into a non-null field, an execute error occurs. > > Then you come back to do a valid insert, it freezes at the execute. (I > thought I had an SQL dead lock) > I found the fix was to delete the dbi hash used by the prepare_cache > on execute errror, so that > when prepare_cache is called again it is completed from scratch (ie > no cache). > > DBD.pm 9530 2007-05-09 13:05:23Z > W32ODBC.pm 8696 2007-01-24 23:12:38Z timbo $ > This is perl, v5.8.8 built for MSWin32-x86-multi-thread > (with 18 registered patches, see perl -V for more detail) > > Cheers > Damon > >
Appologies for not responding earlier but I never got an email from rt to say this bug was logged so missed it. I will attempt to look into your problem in the next few days. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 1.1k
On Tue Oct 26 14:15:00 2010, MJEVANS wrote: Show quoted text
> On Sun Oct 10 16:55:59 2010, Damon.Atkins@contracted.pmintl.com wrote:
> > For example if you prepare_cache('insert....') (Perl on Win32 ODBC to > > MSSQL) > > and then insert a NULL into a non-null field, an execute error occurs. > > > > Then you come back to do a valid insert, it freezes at the execute. (I > > thought I had an SQL dead lock) > > I found the fix was to delete the dbi hash used by the prepare_cache > > on execute errror, so that > > when prepare_cache is called again it is completed from scratch (ie > > no cache). > > > > DBD.pm 9530 2007-05-09 13:05:23Z > > W32ODBC.pm 8696 2007-01-24 23:12:38Z timbo $ > > This is perl, v5.8.8 built for MSWin32-x86-multi-thread > > (with 18 registered patches, see perl -V for more detail) > > > > Cheers > > Damon > > > >
> > Appologies for not responding earlier but I never got an email from rt > to say this bug was logged so missed it. I will attempt to look into > your problem in the next few days. > > Martin
Hang on a minute. Are you using WIN32ODBC or DBD::ODBC? Martin -- Martin J. Evans Wetherby, UK
Subject: RE: [rt.cpan.org #62033] prepared_cache invalid after error
Date: Fri, 29 Oct 2010 05:38:45 +0200
To: "bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From: "Atkins, Damon (contracted)" <Damon.Atkins [...] contracted.pmintl.com>
Download (untitled) / with headers
text/plain 925b
$$pconnectstring = DBI->connect("dbi:ODBC:$dsn", $login, $pwd Show quoted text
-----Original Message----- From: Martin J Evans via RT [mailto:bug-DBD-ODBC@rt.cpan.org] Sent: Wednesday, 27 October 2010 8:41 PM To: Atkins, Damon (contracted) Subject: [rt.cpan.org #62033] prepared_cache invalid after error <URL: https://rt.cpan.org/Ticket/Display.html?id=62033 > Hang on a minute. Are you using WIN32ODBC or DBD::ODBC? Martin -- Martin J. Evans Wetherby, UK NOTICE: This e-mail may contain confidential information, which should not be copied or distributed without authorization. If you have received this e-mail message by mistake, please inform the sender and delete it from your system. Please note that, for the efficient preservation of Company records that may be required for litigation, e-mail messages sent to the author of this message will be copied and may be retained in a secure repository.
Download (untitled) / with headers
text/plain 1.5k
On Fri Oct 29 05:27:34 2010, Damon.Atkins@contracted.pmintl.com wrote: Show quoted text
> $$pconnectstring = DBI->connect("dbi:ODBC:$dsn", $login, $pwd > -----Original Message----- > From: Martin J Evans via RT > [mailto:bug-DBD-ODBC@rt.cpan.org] > Sent: Wednesday, 27 October 2010 > 8:41 PM > To: Atkins, Damon (contracted) > Subject: [rt.cpan.org > #62033] prepared_cache invalid after error > > <URL: > https://rt.cpan.org/Ticket/Display.html?id=62033 > > > > Hang on a > minute. Are you using WIN32ODBC or DBD::ODBC? > > Martin
The following works for me and outputs: C:\Users\martin\Documents\dbd_odbc\rt62033>perl test.pl DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Can not insert the value NULL into column 'b', table 'master.dbo.mje'; column does n ot allow nulls. INSERT fails. (SQL-23000) [state was 23000 now 01000] [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated . (SQL-01000) at test.pl line 14. '1', 'fred ' 1 rows 1 use DBI; use strict; my $h = DBI->connect("dbi:ODBC:asus2", {PrintError => 0}) or die "connect"; eval {$h->do(q/drop table mje/);}; $h->do(q/create table mje (a int not null, b char(10) not null)/); my $s = $h->prepare_cached(q/insert into mje values(?,?)/); eval { $s->execute(1, undef); }; print "insert null died\n" if $@; $s->execute(1, 'fred'); my $r = $h->prepare(q/select * from mje/); $r->execute; print DBI::dump_results($r); What are you doing differently? Martin -- Martin J. Evans Wetherby, UK
Subject: RE: [rt.cpan.org #62033] prepared_cache invalid after error
Date: Tue, 2 Nov 2010 00:09:19 +0100
To: "bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From: "Atkins, Damon (contracted)" <Damon.Atkins [...] contracted.pmintl.com>
Download (untitled) / with headers
text/plain 1.6k
Prepare cache is in a function and is called every time The following works for me and outputs: C:\Users\martin\Documents\dbd_odbc\rt62033>perl test.pl DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Can not insert the value NULL into column 'b', table 'master.dbo.mje'; column does n ot allow nulls. INSERT fails. (SQL-23000) [state was 23000 now 01000] [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated . (SQL-01000) at test.pl line 14. '1', 'fred ' 1 rows 1 use DBI; use strict; # No eval warps, call prepare_cached each time sub doit { my $dbh=shift @_; my $s = $dbh->prepare_cached(q/insert into mje values(?,?)/); $s->execute(@_); print "sql errors $DBI::errstr\n"; } my $h = DBI->connect("dbi:ODBC:asus2", {PrintError => 0}) or die "connect"; eval {$h->do(q/drop table mje/);}; $h->do(q/create table mje (a int not null, b char(10) not null)/); doit($h,1, undef); doit($h,1,'fred'); my $r = $h->prepare(q/select * from mje/); $r->execute; print DBI::dump_results($r); What are you doing differently? Martin -- Martin J. Evans Wetherby, UK NOTICE: This e-mail may contain confidential information, which should not be copied or distributed without authorization. If you have received this e-mail message by mistake, please inform the sender and delete it from your system. Please note that, for the efficient preservation of Company records that may be required for litigation, e-mail messages sent to the author of this message will be copied and may be retained in a secure repository.
Download (untitled) / with headers
text/plain 754b
On Mon Nov 01 19:09:35 2010, Damon.Atkins@contracted.pmintl.com wrote: Show quoted text
> > Prepare cache is in a function and is called every time >
The following still works for me. What output do you get when you run it? use DBI; use strict; sub doit { my $dbh=shift @_; my $s = $dbh->prepare_cached(q/insert into mje values(?,?)/); $s->execute(@_); print "sql errors $DBI::errstr\n"; } my $h = DBI->connect("dbi:ODBC:asus2", {PrintError => 0}) or die "connect"; eval {$h->do(q/drop table mje/);}; $h->do(q/create table mje (a int not null, b char(10) not null)/); doit($h,1, undef); doit($h,1,'fred'); my $r = $h->prepare(q/select * from mje/); $r->execute; print DBI::dump_results($r); Martin -- Martin J. Evans Wetherby, UK
Subject: RE: [rt.cpan.org #62033] prepared_cache invalid after error
Date: Tue, 2 Nov 2010 23:59:27 +0100
To: "bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From: "Atkins, Damon (contracted)" <Damon.Atkins [...] contracted.pmintl.com>
Download (untitled) / with headers
text/plain 1.8k
The value returned by $s = $dbh->prepare_cached(...) is invalid. I asume that $s contains a handle to a prepared statement and that ODBC invalidates it (or closes it, or MS SQL invalidates it) but $s = $dbh->prepare_cached(...) still returns it. and so the execute fails. Assuming your connecting to a MSSQL DB via ODBC Maybe the next step is just to check versions of DLL's Cheers Show quoted text
-----Original Message----- From: Martin J Evans via RT [mailto:bug-DBD-ODBC@rt.cpan.org] Sent: Tuesday, 2 November 2010 7:08 PM To: Atkins, Damon (contracted) Subject: [rt.cpan.org #62033] prepared_cache invalid after error <URL: https://rt.cpan.org/Ticket/Display.html?id=62033 > On Mon Nov 01 19:09:35 2010, Damon.Atkins@contracted.pmintl.com wrote:
> > Prepare cache is in a function and is called every time >
The following still works for me. What output do you get when you run it? use DBI; use strict; sub doit { my $dbh=shift @_; my $s = $dbh->prepare_cached(q/insert into mje values(?,?)/); $s->execute(@_); print "sql errors $DBI::errstr\n"; } my $h = DBI->connect("dbi:ODBC:asus2", {PrintError => 0}) or die "connect"; eval {$h->do(q/drop table mje/);}; $h->do(q/create table mje (a int not null, b char(10) not null)/); doit($h,1, undef); doit($h,1,'fred'); my $r = $h->prepare(q/select * from mje/); $r->execute; print DBI::dump_results($r); Martin -- Martin J. Evans Wetherby, UK NOTICE: This e-mail may contain confidential information, which should not be copied or distributed without authorization. If you have received this e-mail message by mistake, please inform the sender and delete it from your system. Please note that, for the efficient preservation of Company records that may be required for litigation, e-mail messages sent to the author of this message will be copied and may be retained in a secure repository.
Download (untitled) / with headers
text/plain 716b
On Tue Nov 02 18:59:46 2010, Damon.Atkins@contracted.pmintl.com wrote: Show quoted text
> The value returned by $s = $dbh->prepare_cached(...) is invalid. > I > asume that $s contains a handle to a prepared statement and that > ODBC invalidates it (or closes it, or MS SQL invalidates it) > but > $s = $dbh->prepare_cached(...) still returns it. > and so the > execute fails. > > Assuming your connecting to a MSSQL DB via ODBC > Maybe the next step is just to check versions of DLL's > > Cheers
I was connecting to MS SQL Server via ODBC. Can you send me a trace. set DBI_TRACE=15=x.log then run the smallest script which reproduces the problem and send me the log file. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 981b
On Wed Nov 03 04:25:22 2010, MJEVANS wrote: Show quoted text
> On Tue Nov 02 18:59:46 2010, Damon.Atkins@contracted.pmintl.com wrote:
> > The value returned by $s = $dbh->prepare_cached(...) is invalid. > > I > > asume that $s contains a handle to a prepared statement and that > > ODBC invalidates it (or closes it, or MS SQL invalidates it) > > but > > $s = $dbh->prepare_cached(...) still returns it. > > and so the > > execute fails. > > > > Assuming your connecting to a MSSQL DB via ODBC > > Maybe the next step is just to check versions of DLL's > > > > Cheers
> > I was connecting to MS SQL Server via ODBC. > > Can you send me a trace. > > set DBI_TRACE=15=x.log > > then run the smallest script which reproduces the problem and send me > the log file. > > Martin
Have you resolved this issue? I've not heard anything in nearly 3 weeks and cannot see a way to progress this without a log as I cannot reproduce your problem. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 1.1k
On Mon Nov 22 09:02:41 2010, MJEVANS wrote: Show quoted text
> On Wed Nov 03 04:25:22 2010, MJEVANS wrote:
> > On Tue Nov 02 18:59:46 2010, Damon.Atkins@contracted.pmintl.com wrote:
> > > The value returned by $s = $dbh->prepare_cached(...) is invalid. > > > I > > > asume that $s contains a handle to a prepared statement and that > > > ODBC invalidates it (or closes it, or MS SQL invalidates it) > > > but > > > $s = $dbh->prepare_cached(...) still returns it. > > > and so the > > > execute fails. > > > > > > Assuming your connecting to a MSSQL DB via ODBC > > > Maybe the next step is just to check versions of DLL's > > > > > > Cheers
> > > > I was connecting to MS SQL Server via ODBC. > > > > Can you send me a trace. > > > > set DBI_TRACE=15=x.log > > > > then run the smallest script which reproduces the problem and send me > > the log file. > > > > Martin
> > Have you resolved this issue? > > I've not heard anything in nearly 3 weeks and cannot see a way to > progress this without a log as I cannot reproduce your problem. > > Martin
I will code this issue in a few days. Martin -- Martin J. Evans Wetherby, UK
Subject: RE: [rt.cpan.org #62033] prepared_cache invalid after error
Date: Tue, 14 Dec 2010 01:50:14 +0100
To: "bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From: "Atkins, Damon (contracted)" <Damon.Atkins [...] contracted.pmintl.com>
Download (untitled) / with headers
text/plain 4.7k
0x2a00 DBI::db::get_info 0x10000 DBI::db::take_imp_data 0x10c00 DBI::db::disconnect 0x2000 DBI::db::selectrow_array 0x2200 DBI::db::tables 0x0430 DBI::db::quote_identifier 0x0000 DBI::db::clone 0x0430 DBI::db::quote 0x2200 DBI::db::type_info 0xaa00 DBI::db::statistics_info 0x2000 DBI::db::selectrow_arrayref 0x0400 DBI::db::begin_work 0x2800 DBI::db::last_insert_id 0xaa00 DBI::db::foreign_key_info 0x2200 DBI::db::primary_key 0x0c80 DBI::db::commit 0x0404 DBI::db::ping 0x2000 DBI::db::selectall_arrayref 0x2a00 DBI::db::type_info_all 0x3200 DBI::db::do 0x2000 DBI::db::selectcol_arrayref 0xa200 DBI::db::prepare_cached 0x0004 DBI::db::rows 0x0c80 DBI::db::rollback 0xaa00 DBI::db::column_info 0xaa00 DBI::db::table_info 0xaa00 DBI::db::primary_key_info 0xa200 DBI::db::prepare 0x0000 DBI::db::preparse 0x0004 DBI::db::connected 0x0200 DBI::db::data_sources 0x2000 DBI::db::selectall_hashref 0x2000 DBI::db::selectrow_hashref 0x0000 DBI::dr::default_user 0x0800 DBI::dr::data_sources 0x0800 DBI::dr::disconnect_all 0x8000 DBI::dr::connect_cached 0x8000 DBI::dr::connect 0x0000 DBI::st::more_results 0x0000 DBI::st::blob_read 0x0000 DBI::st::fetchall_hashref 0x0000 DBI::st::bind_param_inout_array 0x0000 DBI::st::finish 0x0004 DBI::st::rows 0x5040 DBI::st::execute_for_fetch 0x0000 DBI::st::fetchrow_hashref 0x0000 DBI::st::_get_fbav 0x0000 DBI::st::_set_fbav 0x0000 DBI::st::fetchrow 0x0000 DBI::st::fetch 0x5040 DBI::st::execute_array 0x0000 DBI::st::dump_results 0x0000 DBI::st::bind_param_array 0x0000 DBI::st::fetchrow_array 0x1040 DBI::st::execute 0x0000 DBI::st::bind_col 0x0000 DBI::st::fetchall_arrayref 0x0000 DBI::st::fetchrow_arrayref 0x0000 DBI::st::bind_param_inout 0x0000 DBI::st::bind_columns 0x0800 DBI::st::cancel 0x0000 DBI::st::blob_copy_to_file 0x0000 DBI::st::bind_param 0x0404 DBI::common::parse_trace_flag 0x0004 DBI::common::errstr 0x0004 DBI::common::trace_msg 0x0004 DBI::common::err 0x0004 DBI::common::CLEAR 0x0004 DBI::common::state 0x0000 DBI::common::_not_impl 0x0004 DBI::common::NEXTKEY 0x0004 DBI::common::trace 0x0004 DBI::common::debug 0x0404 DBI::common::parse_trace_flags 0x0000 DBI::common::private_attribute_info 0x0000 DBI::common::swap_inner_handle 0x0004 DBI::common::EXISTS 0x0404 DBI::common::FETCH 0x0004 DBI::common::FIRSTKEY 0x0010 DBI::common::set_err 0x10004 DBI::common::DESTROY 0x0004 DBI::common::dump_handle 0x0404 DBI::common::FETCH_many 0x0100 DBI::common::can 0x041c DBI::common::STORE 0x0004 DBI::common::private_data 0x0006 DBI::common::func Try1 Correct CreateMaint(DBI::db=HASH(0x3e72ac4),2010-01-01 00:00:00,datkins,2010-01-01 00:00:00,2010-01-01 00:10:00,A Comment1,maint enance,blackout,Script :,myselftest) RowId 25576 Try2 NULL to fail CreateMaint(DBI::db=HASH(0x3e72ac4),2010-01-01 00:00:00,datkins,,2010-01-01 00:10:00,A Comment2,maintenance,blackout,Scr ipt :,myselftest) DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'S cheduled_Start_Date_Time', table 'System_Tools_DEV.dbo.T_Alarm_Maintenances'; column does not allow nulls. INSERT fails. (SQL-23000) [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (SQL-01000)(DBD: st_execute/SQLExecute err=-1) at bug.pl line 57. SQLexec: INSERT INTO T_Alarm_Maintenances (Created_Date_Time, Created_By, Scheduled_Start_Date_Time, Scheduled_End_Date_ Time, Comment, Source, Component, Media, Media_data) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ;SELECT SCOPE_IDENTITY() Data:'2010-01-01 00:00:00','datkins','','2010-01-01 00:10:00','A Comment2','maintenance','blackout','Script :','myselfte st' Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'Scheduled_Start_Date_Tim e', table 'System_Tools_DEV.dbo.T_Alarm_Maintenances'; column does not allow nulls. INSERT fails. (SQL-23000) [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (SQL-01000)(DBD: st_execute/SQLExecute err=-1) at bug.pl line 62. RowId Try3 Correct CreateMaint(DBI::db=HASH(0x3e72ac4),2010-01-01 00:00:00,datkins,2010-01-01 00:00:00,2010-01-01 00:10:00,A Comment3,maint enance,blackout,Script :,myselftest) ---- It Stops, Control-C needed to break it --- Terminating on signal SIGINT(2) NOTICE: This e-mail may contain confidential information, which should not be copied or distributed without authorization. If you have received this e-mail message by mistake, please inform the sender and delete it from your system. Please note that, for the efficient preservation of Company records that may be required for litigation, e-mail messages sent to the author of this message will be copied and may be retained in a secure repository.
Download prepare_cache.log
application/octet-stream 36.4k

Message body not shown because it is not plain text.

Download (untitled) / with headers
text/plain 4.2k
On Mon Dec 13 19:50:37 2010, Damon.Atkins@contracted.pmintl.com wrote: <snipped some log output> Show quoted text
> Try1 Correct > CreateMaint(DBI::db=HASH(0x3e72ac4),2010-01-01 > 00:00:00,datkins,2010-01-01 00:00:00,2010-01-01 00:10:00,A > Comment1,maint > enance,blackout,Script :,myselftest) > RowId 25576 > Try2 NULL to fail > CreateMaint(DBI::db=HASH(0x3e72ac4),2010-01-01 > 00:00:00,datkins,,2010-01-01 00:10:00,A > Comment2,maintenance,blackout,Scr > ipt :,myselftest) > DBD::ODBC::st > execute failed: [Microsoft][ODBC SQL Server Driver][SQL > Server]Cannot insert the value NULL into column 'S > cheduled_Start_Date_Time', table > 'System_Tools_DEV.dbo.T_Alarm_Maintenances'; column does not allow > nulls. INSERT fails. > (SQL-23000) > [Microsoft][ODBC SQL Server > Driver][SQL Server]The statement has been terminated. (SQL- > 01000)(DBD: st_execute/SQLExecute > err=-1) at bug.pl line 57. > SQLexec: INSERT INTO T_Alarm_Maintenances (Created_Date_Time, > Created_By, Scheduled_Start_Date_Time, Scheduled_End_Date_ > Time, > Comment, Source, Component, Media, Media_data) > VALUES (?, ?, ?, ?, > ?, ?, ?, ?, ?) > ;SELECT SCOPE_IDENTITY()
I don't think DBI ever intended that you could prepare multiple statements and cache them like this. If the first fails what should happen with the second? Just don't do this, set up a second prepare for the identity or create a procedure which does the insert, obtains the identity and returns it. Show quoted text
> Data:'2010-01-01 > 00:00:00','datkins','','2010-01-01 00:10:00','A > Comment2','maintenance','blackout','Script :','myselfte > st' > Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert > the value NULL into column 'Scheduled_Start_Date_Tim > e', table > 'System_Tools_DEV.dbo.T_Alarm_Maintenances'; column does not allow > nulls. INSERT fails. (SQL-23000) > [Microsoft][ODBC SQL Server > Driver][SQL Server]The statement has been terminated. (SQL- > 01000)(DBD: st_execute/SQLExecute > err=-1) at bug.pl line 62. > RowId > > > Try3 Correct > CreateMaint(DBI::db=HASH(0x3e72ac4),2010- > 01-01 00:00:00,datkins,2010-01-01 00:00:00,2010-01-01 00:10:00,A > Comment3,maint > enance,blackout,Script :,myselftest) > ---- It > Stops, Control-C needed to break it --- > Terminating on signal > SIGINT(2) > > NOTICE: This e-mail may contain confidential > information, which should not be copied or distributed without > authorization. If you have received this e-mail message by > mistake, please inform the sender and delete it from your system. > Please note that, for the efficient preservation of Company records > that may be required for litigation, e-mail messages sent to the > author of this message will be copied and may be retained in a > secure repository.
Admittedly, it is more than unfortunate that your script hangs but this is because the insert statement failed, SQL Server moved on to the select identity and returned the value down the socket but you never called odbc_more_results. You'd need to do something like: use DBI; use strict; sub doit { my $dbh=shift @_; my $s = $dbh->prepare_cached(q/insert into mje (b) values(?);select @@identity/); $s->execute(@_); print "sql errors $DBI::errstr\n"; $s->{odbc_more_results}; my $identity; ($identity) = $s->fetchrow_array; print "identity = ", DBI::neat($identity), "\n"; ($identity) = $s->fetchrow_array; } my $h = DBI->connect("dbi:ODBC:mydsn", "user", "password", {PrintError => 0}) or die "connect"; eval {$h->do(q/drop table mje/);}; $h->do(q/create table mje (a int identity, b char(10) not null)/); doit($h,undef); #doit($h,1, undef); #doit($h,1,'fred'); #doit($h,1, undef); doit($h,'fred'); my $r = $h->prepare(q/select * from mje/); $r->execute; print DBI::dump_results($r); But although that will probably stop the hang I doubt you'll get the identity back with DBD::ODBC 1.13 which is over 6 years old now. There is a small bug in the latest DBD::ODBC which I'll fix and then the above example will work completely but I'd strongly suggest you don't prepare 2 statements in one go as you will eventually find a MS SQL Server ODBC Driver this won't work on (one which batches up the statements for instance). Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 1.6k
On Tue Dec 14 09:12:27 2010, MJEVANS wrote: Show quoted text
> use DBI; > use strict; > > sub doit > { > my $dbh=shift @_; > my $s = $dbh->prepare_cached(q/insert into mje (b) values(?);select > @@identity/); > $s->execute(@_); > print "sql errors $DBI::errstr\n"; > > $s->{odbc_more_results}; > my $identity; > ($identity) = $s->fetchrow_array; > print "identity = ", DBI::neat($identity), "\n"; > ($identity) = $s->fetchrow_array; > > } > > > my $h = DBI->connect("dbi:ODBC:mydsn", "user", "password", > {PrintError => 0}) or die "connect"; > > eval {$h->do(q/drop table mje/);}; > > $h->do(q/create table mje (a int identity, b char(10) not null)/); > > doit($h,undef); > #doit($h,1, undef); > #doit($h,1,'fred'); > #doit($h,1, undef); > doit($h,'fred'); > > my $r = $h->prepare(q/select * from mje/); > $r->execute; > print DBI::dump_results($r); > > But although that will probably stop the hang I doubt you'll get the > identity back with DBD::ODBC 1.13 which is over 6 years old now. > > There is a small bug in the latest DBD::ODBC which I'll fix and then the > above example will work completely but I'd strongly suggest you don't > prepare 2 statements in one go as you will eventually find a MS SQL > Server ODBC Driver this won't work on (one which batches up the > statements for instance). > > Martin
I have released 1.26_4 which fixed the small issue where odbc_more_results might not set the Active flag on the statement handle which could stop the retrieval of the $identity in the above example from working. Are you ok for me to right this off now? Martin -- Martin J. Evans Wetherby, UK
Subject: RE: [rt.cpan.org #62033] prepared_cache invalid after error
Date: Thu, 16 Dec 2010 10:50:22 +0100
To: "bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From: "Atkins, Damon (contracted)" <Damon.Atkins [...] contracted.pmintl.com>
Download (untitled) / with headers
text/plain 473b
Yes , And thanks NOTICE: This e-mail may contain confidential information, which should not be copied or distributed without authorization. If you have received this e-mail message by mistake, please inform the sender and delete it from your system. Please note that, for the efficient preservation of Company records that may be required for litigation, e-mail messages sent to the author of this message will be copied and may be retained in a secure repository.


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.