[Ruby-DBI-devel] Performing SQL actions that can't be done inside of a transaction...

[Sean C. <se...@ch...>]

I've got a pretty interesting problem in that I'm dynamically creating
a database using Ruby.  I've got something of a problem though with
the way that transactions are handled with the execute() methods in
that they always prepend BEGIN WORK; if you're outside of a
transaction.  There are a couple of workarounds for this, but none of
them elegant or correct, IMHO.

*) What I'm doing now:

   dbh.do('ABORT; #{sql}; COMMIT; BEGIN;')

   It's super ugly, but it works insofar as it keeps the database in
   sync with DBI.

*) A possible hack would be to add :in_transaction as an accessor:

   # make sure you're currently outside of a transaction
   dbh.in_transaction = true
   dbh.do(sql)
   dbh.in_transaction = false

*) Another option would be to create something parallel to #do, only
 have it skip the write-through transaction bits.

 dbh.do_exec(sql)

*) The most elegant and likely correct solution would be to add
 another options tunable that would let #do omit adding a 'BEGIN
 WORK;' to every command.

 dbh['AutoBegin'] = false
 dbh.do(sql)
 dbh.commit

I'm personally a fan of the last option as it'd give the developer
fine grained control over when a transaction should begin or end and
would let people execute administrative SQL commands that can't be
executed inside of a transaction (create/drop database, etc).  Anyone
else have an opinion on this?  -sc

-- 
Sean Chittenden