seeking barebones sqlite example with transaction

dimar has asked for the wisdom of the Perl Monks concerning the following question:

Problem: The goal is to do a simple INSERT query using the DBI and sqlite. Everything works as expected when a *single* insert query is submitted. However, when a *compound* query is submitted (using BEGIN TRANSACTION syntax) the attempted operation does not work.

Request: Does anyone have a working barebones snippet of code that shows how to do a compound query using the latest development release of sqlite and perl DBI?

### This code no worky as expected ...
### anyone have a working alternative?

### begin_: init perl
    use strict;
    use warnings;
    use DBI;

### begin_: init vars
    my  $dbh;
    my  $oStt;
    my  $oData;
    my  $sQry000;
    my  $sDbsUrl    = "test.db";

    ### p__: compound INSERT query
    $sQry000    =
    q^
        BEGIN TRANSACTION;
        
        INSERT INTO user ('fname','lname')
        VALUES ('mickey','mouse');
        
        INSERT INTO user ('fname','lname')
        VALUES ('donald','duck');
        
        COMMIT;
    ^;

    ### p__:   run the query
    $dbh    = DBI->connect( "dbi:SQLite:$sDbsUrl" ) 
        || die "Cannot connect: $DBI::errstr";
    $oStt   = $dbh->prepare($sQry000);
    $oStt->execute();
[download]

=oQDlNWYsBHI5JXZ2VGIulGIlJXYgQkUPxEIlhGdgY2bgMXZ5VGIlhGV

Comment on seeking barebones sqlite example with transaction Download Code

Replies are listed 'Best First'.
Re: seeking barebones sqlite example with transaction by runrig (Abbot) on Aug 04, 2006 at 22:04 UTC
Don't put the BEGIN/COMMIT in your sql, and don't use compound queries. See the AutoCommit attribute, and the commit() method in DBI. Basically, turn AutoCommit off (upon connect() or afterwards), execute your inserts (or other statements) one statement at a time then call commit() or rollback() to commit or rollback your transactions. With AutoCommit off, a new transaction is automatically started after every commit or rollback (and after turning it off). On another note, you may also want to check out What are placeholders in DBI, and why would I want to use them?	[reply]
Re^2: seeking barebones sqlite example with transaction by bart (Canon) on Aug 05, 2006 at 00:06 UTC
One note to add: in SQLite, during a transaction, all other write access is simply forbidden. That matters a lot if your list of queries can take many seconds to complete... No, they're not queued, SQLite just will make you wait until the transaction ends, up to as long as the timeout you allowed, and then, if you still got no access, cause a fatal error. Basically, if you can have transactions that take several seconds, any other writing access to the database is impractical. Unless you're prepared to wait for a long time...	[reply]
Re^2: seeking barebones sqlite example with transaction by dimar (Curate) on Aug 05, 2006 at 14:30 UTC
Yes. I am familiar with placeholders, but deliberately left them out of this SOPW pursuant to the "barebones" inquiry. Given that the DBI presents abstractions and conveniences that may overlap, supercede and perhaps even run counter-intuitive to the idioms of the underlying database system (in this case sqlite), it seemed like the reductionist approach was the best way to pose this question. The side note is, however, appreciated because I think it (and the other posts in this thread) help illuminate how "sqlite" should be considered qualitatively different from "sqlite + DBI" =oQDlNWYsBHI5JXZ2VGIulGIlJXYgQkUPxEIlhGdgY2bgMXZ5VGIlhGV	[reply]
Re: seeking barebones sqlite example with transaction by rhesa (Vicar) on Aug 04, 2006 at 22:14 UTC
I don't know about SQLite specifically, but not every DBD driver supports compound statements like that. The portable way to do transactions with DBI is by using `begin_work`, and `commit/rollback`. Usually you'd wrap the transaction in an eval to catch errors, so the following would be the more common idiom (copied from the Transactions section in the DBI docs): `$dbh->begin_work; eval { local $dbh->{RaiseError} = 1; 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 }` [download] Update: Added RaiseError code per runrig's comment.	[reply] [d/l] [select]
Re^2: seeking barebones sqlite example with transaction by runrig (Abbot) on Aug 04, 2006 at 22:19 UTC
Note that for catching errors with eval like that, you should turn on the RaiseError attribute (upon connect or afterward). (And thanks, I didn't notice the begin_work() method to temporarily turn off AutoCommit before now).	[reply]
Re^3: seeking barebones sqlite example with transaction by rhesa (Vicar) on Aug 04, 2006 at 22:28 UTC
Heh, I was about to update my node to that effect. The Transaction section in the DBI docs does mention turning on `RaiseError`. Strangely enough, it doesn't use the `begin_work` method, although I think it's a cleaner way of writing this pattern. I looked at the code of `begin_work`, and it doesn't touch `RaiseError`. I suppose that makes sense in general, but I'd expect such a handy method to do that for me.	[reply] [d/l] [select]