Perl, DBI, and Transactions
A Perl script using DBI/DBD::Informix issued "begin work", an update, then "rollback work", but the rollback failed with SQL -255: Not in transaction. Suggestions were that the statements might be on different connections (ruled out — same $dbh), then that DBI's AutoCommit, which is on by default, was committing each statement; the fix is to turn AutoCommit off (and let DBI handle begin/commit/rollback) per the DBD::Informix docs. A side tip: run `perl -MDBI=999` (or 9999 for DBD::Informix) to reveal installed module versions.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I've been working on some perl scripts that, among other things, update a few rows in a database. If something fails, I want to roll back the updates. When I do a rollback, Informix tells me that I'm not in a transaction. So I made a little test script to experiment with. The latest version is below. Why does this give me a DBD::Informix::st execute failed: SQL: -255: Not in transaction. at ./test.pl line 17. ?? I'm running perl 5.005_03 built for sun4-solaris. I can't seem to figure out how to get a version of the DBI and DBD we're using... I appreciate any and all <well-intentioned> advice. Thanks, Brian McLaughlin <sample code below> #!/usr/bin/perl use DBI; require 'informix.pl'; my $dbh = getDB(); my $query = "begin work"; my $results = $dbh->prepare($query); $results->execute(); $query = "update gfu_login set user_expire = 25 where box_id = '207050'"; my $results2 = $dbh->prepare($query); $results2->execute(); $query = "rollback work"; my $results3 = $dbh->prepare($query); $results3->execute(); releaseDB($dbh);
On Aug 2, 12:59 pm, "Brian McLaughlin" <bmcla...@georgefox.edu> wrote: > I've been working on some perl scripts that, among other things, update > a few rows in a database. If something fails, I want to roll back the > updates. When I do a rollback, Informix tells me that I'm not in a > transaction. > > So I made a little test script to experiment with. The latest version > is below. Why does this give me a > DBD::Informix::st execute failed: SQL: -255: Not in transaction. at > ./test.pl line 17. > > ?? > > I'm running perl 5.005_03 built for sun4-solaris. I can't seem to figure > out how to get a version of the DBI and DBD we're using... > > I appreciate any and all <well-intentioned> advice. > > Thanks, > > Brian McLaughlin > > <sample code below> > > #!/usr/bin/perl > use DBI; > > require 'informix.pl'; > my $dbh = getDB(); > > my $query = "begin work"; > my $results = $dbh->prepare($query); > $results->execute(); > > $query = "update gfu_login set user_expire = 25 where box_id = > '207050'"; > my $results2 = $dbh->prepare($query); > $results2->execute(); > > $query = "rollback work"; > my $results3 = $dbh->prepare($query); > $results3->execute(); > > releaseDB($dbh); The only reason for that could be because your statements are executed using different database connections.
Thanks for the quick reply. The handle ($dbh) is the same for all three statements. The $results -- I initially used the same for all three statements. I was kind-of grasping at straws when I tried making them all be different. It gives the same results whether I use $results for all three statements or if I have $results, $results2, and $results3 as the sample I included does. Brian McLaughlin >The only reason for that could be because your statements are executed >using different database connections. >> DBD::Informix::st execute failed: SQL: -255: Not in transaction. at ./test.pl line 17. >> >> #!/usr/bin/perl >> use DBI; >> >> require 'informix.pl'; >> my $dbh = getDB(); >> >> my $query = "begin work"; >> my $results = $dbh->prepare($query); >> $results->execute(); >> >> $query = "update gfu_login set user_expire = 25 where box_id = >> '207050'"; >> my $results2 = $dbh->prepare($query); >> $results2->execute(); >> >> $query = "rollback work"; >> my $results3 = $dbh->prepare($query); >> $results3->execute(); >> >> releaseDB($dbh); ______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list
On Aug 2, 1:15 pm, "Brian McLaughlin" <bmcla...@georgefox.edu> wrote: > Thanks for the quick reply. > > The handle ($dbh) is the same for all three statements. > > The $results -- I initially used the same for all three statements. I > was kind-of grasping at straws when I tried making them all be > different. It gives the same results whether I use $results for all > three statements or if I have $results, $results2, and $results3 as the > sample I included does. > > Brian McLaughlin > > > > > > >The only reason for that could be because your statements are executed > >using different database connections. > >> DBD::Informix::st execute failed: SQL: -255: Not in transaction. at > ./test.pl line 17. > > >> #!/usr/bin/perl > >> use DBI; > > >> require 'informix.pl'; > >> my $dbh = getDB(); > > >> my $query = "begin work"; > >> my $results = $dbh->prepare($query); > >> $results->execute(); > > >> $query = "update gfu_login set user_expire = 25 where box_id = > >> '207050'"; > >> my $results2 = $dbh->prepare($query); > >> $results2->execute(); > > >> $query = "rollback work"; > >> my $results3 = $dbh->prepare($query); > >> $results3->execute(); > > >> releaseDB($dbh); > > ______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list Another blind guess is that "autocommit", if there is such thing in DBI, is turned on. It was long time ago I used Perl.
By DBI default, AutoCommit is on, you'll have to turn it off. Refer to the DBD::Informix docs for specifics. Also, the easiest way to get module versions is the following trick: perl -MDBI=999 Which will request version 999 of DBI and not finding that version the returned error include the actual version of DBI. HTH, -D -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of askel Sent: Thursday, August 02, 2007 1:55 PM To: informix-list@iiug.org Subject: Re: Perl, DBI, and Transactions On Aug 2, 1:15 pm, "Brian McLaughlin" <bmcla...@georgefox.edu> wrote: > Thanks for the quick reply. > > The handle ($dbh) is the same for all three statements. > > The $results -- I initially used the same for all three statements. I > was kind-of grasping at straws when I tried making them all be > different. It gives the same results whether I use $results for all > three statements or if I have $results, $results2, and $results3 as the > sample I included does. > > Brian McLaughlin > > > > > > >The only reason for that could be because your statements are executed > >using different database connections. > >> DBD::Informix::st execute failed: SQL: -255: Not in transaction. at > ./test.pl line 17. > > >> #!/usr/bin/perl > >> use DBI; > > >> require 'informix.pl'; > >> my $dbh = getDB(); > > >> my $query = "begin work"; > >> my $results = $dbh->prepare($query); > >> $results->execute(); > > >> $query = "update gfu_login set user_expire = 25 where box_id = > >> '207050'"; > >> my $results2 = $dbh->prepare($query); > >> $results2->execute(); > > >> $query = "rollback work"; > >> my $results3 = $dbh->prepare($query); > >> $results3->execute(); > > >> releaseDB($dbh); > > ______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list Another blind guess is that "autocommit", if there is such thing in DBI, is turned on. It was long time ago I used Perl. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list <font face="Arial" size="2" color="#008000"> Please consider the environment before printing this email.</font> _____________________________________________________________________________________ The information contained in this email may be confidential and/or legally privileged. It has been sent for the sole use of the intended recipient(s). If the reader of this message is not an intended recipient, you are hereby notified that any unauthorized review, use, disclosure, dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please contact the sender by reply email and destroy all copies of the original message. To contact our email administrator directly, send to postmaster@dlapiper.com Thank you. _____________________________________________________________________________________
On Aug 2, 11:02 am, "Priest, Darryl" <darryl.pri...@dlapiper.com> wrote: > By DBI default, AutoCommit is on, you'll have to turn it off. Refer to > the DBD::Informix docs for specifics. > > Also, the easiest way to get module versions is the following trick: > > perl -MDBI=999 > > Which will request version 999 of DBI and not finding that version the > returned error include the actual version of DBI. Don't forget that DBD::Informix is at version 2007.0226, so you need to use 9999, not 999. :-) Someone else has a module versioned by date: 20070730.01 or thereabouts. -=JL=-