[RESOLVED] SQLite3 and error 'cannot start transaction within a
transaction'
lazarus / 16-Databases

----------------------------------------------------------------------
rise_nak · 2011-06-09 11:28

Hello!
I'm traying to write logging into table called LOG.
I need two separated transactions, so i add to form SQLTrans1
(TSQLTransaction) and SQLTransLog, which are connected with
SQLiteConn1 (TSQLite3Connection).
The idea is that when I have to do rollback on transaction SQLTrans1 i
want to log all querys from SQLTrans1, which wasn't done. So I need to
do commit only on SQLTransLog.
When I do any query I got an error: 'cannot start transaction within a
transaction'

the logging code:

SQLQUpdateLog.SQL.Text := 'INSERT INTO Log (LOG_DATA, LOG_TYP,
LOG_FUNKCJA, LOG_TABELA, LOG_EVENT) VALUES (:data, :typ, :funkcja,
:tabela, :tekst);';
    SQLQUpdateLog.Params.ParamByName('data').AsString :=
FormatDateTime('yyyy-mm-dd hh:nn:ss', Now);
    SQLQUpdateLog.Params.ParamByName('typ').AsInteger :=
aTypZdarzenia;
    SQLQUpdateLog.Params.ParamByName('funkcja').AsString := aFunkcja;
    if length(aTabela)>0 then
       SQLQUpdateLog.Params.ParamByName('tabela').AsString := aTabela
    else
       SQLQUpdateLog.Params.ParamByName('tabela').IsNull;
    SQLQUpdateLog.Params.ParamByName('tekst').AsString := aText;

    SQLQUpdateLog.ExecSQL;

    SQLTransLog.Commit;

----------------------------------------------------------------------
Lacak2 · 2011-06-14 06:58

IMO you must firs ROLLBACK changes on SQLTrans1 and then start new
transaction and write into LOG and commit.
IMO per one connection you can have only one active transaction.

----------------------------------------------------------------------
rise_nak · 2011-06-16 12:14

I tried to make two connection to SQLite 3 DB and one transaction per
connection. I got an error that database is locked.
Now I know that SQLite3 was bad choice for my program, I modiffied a
little sql querys and changed DB engine to Firebird.
Logging function works without changes and there is no problem with
one connection and two transaction parallel.

Thanks for help!