in reply to RFC: Transactions.pm

Hi Juerd. I was playing around with your code using the following MySQL database table: 
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | YES  |     | NULL    |       |
| name        | varchar(64) | YES  |     | NULL    |       |
| description | varchar(64) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
and the following client code: 
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect( ... );
use transactions $dbh;

$dbh->do('delete from foo');

transaction {
   for (1..10) {
      my $sth = $dbh->prepare('insert into foo values(?,?,?)');
      $sth->execute($_, chr(ord('a')+$_), chr(ord('z')-$_));
      rollback if $_ == 5;
   }
};

print map "@$_\n", @{$dbh->selectall_arrayref('select * from foo')};
$dbh->disconnect;

__END__
results:
1 b y
2 c x
3 d w
4 e v
5 f u

[download]
I honestly expected there to be no output ... shouldn't a rollback cause all 'inserted' data to be 'undone'?

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

Replies are listed 'Best First'.
Re: (jeffa) Re: RFC: transactions.pm
by chromatic (Archbishop) on Apr 27, 2003 at 20:01 UTC

    Are you using a BerkeleyDB or InnoDB or Gemini table type? The default MyISAM table type didn't support transactions for most of MySQL's life. (I'm not sure if the latest versions do.)

[reply]
Re: (jeffa) Re: RFC: transactions.pm
by Juerd (Abbot) on Apr 27, 2003 at 20:03 UTC

    I honestly expected there to be no output ... shouldn't a rollback cause all 'inserted' data to be 'undone'?

    It seems that your MySQL (table) does not support transactions, or that my module is broken. I haven't been able to try it with a real DB *yet*, and have only used print to see what methods it executes, but everything seems to be in order. Could you please check if transactions work if you use them without this module?

    The module does not work the way I expected it to work. I should have tested it with a database.

    Update: New version. This one actually works. I have used your code to test it: 

    use warnings;
use Transactions;
use DBI;

my $dbh = DBI->connect("dbi:SQLite:db.dat");

our $T = $dbh;

$dbh->do('delete from foo');

transaction {
    for (1..10) {
        my $sth = $dbh->prepare('insert into foo values(?,?,?)');
        $sth->execute($_, chr(ord('a')+$_), chr(ord('z')-$_));
        rollback if $_ == 5;
    }
};

print map "@$_\n", @{$dbh->selectall_arrayref('select * from foo')};
$dbh->disconnect;

    [download]

    Juerd # { site => 'juerd.nl', plp_site => 'plp.juerd.nl', do_not_use => 'spamtrap' }

[reply]
[d/l]

In Section Meditations