Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

seeking barebones sqlite example with transaction

by dimar (Curate)
on Aug 04, 2006 at 21:56 UTC ( #565756=perlquestion: print w/replies, xml ) Need Help??
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();

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?

      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...

      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"

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 }

    Update: Added RaiseError code per runrig's comment.

      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).
        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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://565756]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (5)
As of 2017-08-20 21:40 GMT
Find Nodes?
    Voting Booth?
    Who is your favorite scientist and why?

    Results (317 votes). Check out past polls.