Transactional updates to Sybase from Dblib to DBI

Question

I have a perl code currently, that connects to Sybase via Sybase::DBlib and does the following

$dbh->sql('Begin tran');
$query= <some delete query>
$dbh->sql($query)
in a loop{
    do insert query
}
COMPLETE:
$dbh->sql('commit tran');

I am porting the above code to start using DBI instead, but I am not sure how to work with transactions. How can I emulate the above using DBI. And what does COMPLETE do?

Answer 1

Yes.

Look at TRANSACTIONS in the DBI docs. It gives this code example.

$dbh->{AutoCommit} = 0;  # enable transactions, if possible
$dbh->{RaiseError} = 1;
eval {
    foo(...)        # do lots of work here
    bar(...)        # including inserts
    baz(...)        # and updates
    $dbh->commit;   # commit the changes if we get this far
};
if ($@) {
    warn "Transaction aborted because $@";
    # now rollback to undo the incomplete changes
    # but do it in an eval{} as it may also fail
    eval { $dbh->rollback };
    # add other application on-error-clean-up code here
}

For your specific use-case, you could do:

$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;
eval {
    $dbh->prepare("DELETE FROM stuff");
    $dbh->execute;
    $dbh->commit;
};
if ($@) {
    warn "Transaction aborted because $@";
    eval { $dbh->rollback };
}

The most important part is that you need to turn AutoCommit off yourself before you start with your transaction. That works by just changing the internals of the $dbh.

Note that you can also use Try::Tiny instead of the eval block, which looks a bit nicer.

If you want to turn auto-committing back on so there are no transactions any more, just set $dbh->{AutoCommit} = 1.

---

You can also use begin_work and commit and leave $dbh->{AutoCommit} at 1, so you don't have to mess with it.

use Try::Tiny;

try {
    $dbh->begin_work;
    $dbh->do( ... );
    $dbh->commit;
catch {
    warn "Transactino aborted because $_";
    try { $dbh->rollback };
};