Been playing with transactions for SQLite3 DBD and noticed this code
in the dbd_sqlite_end_transaction():
----------------------------------
if (trans) {
ret = dbd_sqlite3_query(trans->handle, &nrows, "END TRANSACTION;");
if (trans->errnum) {
trans->errnum = 0;
ret = dbd_sqlite3_query(trans->handle, &nrows, "ROLLBACK;");
} else {
ret = dbd_sqlite3_query(trans->handle, &nrows, "COMMIT;");
}
trans->handle->trans = NULL;
}
return ret;
----------------------------------
I'm reading this as: if there is a transaction and "END TRANSACTION"
is successful (i.e. trans->errnum is zero), then do "COMMIT". If
something like that is tried in SQlite3 command line tool, the output
looks like this:
----------------------------------
[EMAIL PROTECTED] t]$ sqlite3 test.db
SQLite version 3.3.3
Enter ".help" for instructions
sqlite> create table test (a text);
sqlite> begin;
sqlite> insert into test values ('test');
sqlite> end;
sqlite> commit;
SQL error: cannot commit - no transaction is active
----------------------------------
So, it would seem that you cannot "COMMIT" when you issued "END
TRANSACTION" already (i.e. it's already been done) in SQLite3. As a
result of this, dbd_sqlite_end_transaction() appears to be returning a
non-zero value, signaling to the caller that the transaction didn't
end successfully, when in fact it did.
Note that the data did end up in the database on "END":
----------------------------------
sqlite> select * from test;
test
----------------------------------
I also think that the "ROLLBACK" bit is probably unnecessary as well.
If the transaction failed, it will be rolled back anyway when "END
TRANSACTION" is issued. This is not a big problem, but it can probably
be removed.
Is there something I missed here or does that look like a real bug in
SQLite DBD driver?
--
Bojan
