Re: seeking barebones sqlite example with transaction

[runrig]

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?

[bart]

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

[dimar]

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"