Re: $dbh->prepare not triggering die

What I usually do is:

    my $dbh = DBI -> connect ("DBI:$driver:database=$db;host=$host",
                              $user, $password,
                              {RaiseError => 1, PrintError => 0,
                               AutoCommit => 0, Taint      => 1})
              or die "Failed to connect: $DBI:errstr\n";
 
    ...

    eval {
        my $sth = $dbh -> prepare ($cmd);
        $dbh -> execute (@variables);
        while (my @row = $dbh -> fetchrow_array) {
            ... process @row ...
        }
        $dbh -> commit;
    };
    if ($@) {
        $dbh -> rollback;
        croak "Database query failed: $@";
    }
[download]

Abigail

Comment on Re: $dbh->prepare not triggering die Download Code

Replies are listed 'Best First'.
Re: Re: $dbh->prepare not triggering die by UnderMine (Friar) on Dec 02, 2002 at 11:15 UTC
This methodology works well with larger database such as Oracle or SQL Server. However the normally distributed version of mysql does not have transactional support :( which makes things more difficult as rollback and commit are just no-op functions. mysqlmax and the commercial versions do have transactional support but not many people use them. Transactional support is a whole new ball game especially when you need multiple sub-procedures with an error in any of them causing a rollback. I tend to move the transaction start and transaction commit/rollback into a sub-procedures on their own to handle this. Hope it helps UnderMine	[reply]
Re: $dbh->prepare not triggering die by Abigail-II (Bishop) on Dec 02, 2002 at 13:24 UTC
Really? Odd, I lifted that code from a project I'm currently working on, and that does use MySQL. Not my choice, as MySQL isn't much of a database, but newer versions do support transactions - use the `InnoDB` type of tables. Why anyone would bother using a database that doesn't support transactions is beyond me. It just doesn't make any sense. Abigail	[reply] [d/l]
Re: Re: $dbh->prepare not triggering die by UnderMine (Friar) on Dec 02, 2002 at 16:46 UTC
Lifted straight from the mysql website so I appologise for (slight) miss-information. ;) MySQL 3.23.34a (and newer) source releases include the full source for Berkeley DB and InnoDB tables. By using these table handler you can use transactions in MySQL. MySQL-Max has both of these handlers compiled-in. Hope this is clearer UnderMine	[reply]
Re^2: $dbh->prepare not triggering die by diotalevi (Canon) on Dec 02, 2002 at 12:45 UTC
Well what the heck! What good is a database when it doesn't support transactions? This is a divergence from the original question but why on earth doesn't the stock MySQL do transactions by default? That's just... you might as well be using a really fast DBD::CSV or something then for all the help it's giving you. I mean c'mon - this is one of the reasons why you step up to RDBMS in the first place. If that's isn't a deliberately crippled install then I just don't get it (which might actually be true). `__SIG__ use B; printf "You are here %08x\n", unpack "L!", unpack "P4", pack "L!", B::svref_2object(sub{})->OUTSIDE;` [download]	[reply] [d/l]