How to master transaction?
Posted: 25.05.2009, 04:42
Hi, guys, the transaction in FB is driving me crazy. 
On top of this is the AutoCommit flag. Yes, I have read the quick guide in documentation area which has a brief explaination about it and has 4 URLs linking to 4 relative threads, but after I want over them, I was still hard to understand how to use it in a correcting way.
In fact, my problem is, my autocommit was set to true, and I used multithread to achieve the purpose, that was every threads could update or select data from a FB database file and they shared a TZConnection, but they had different TZQuery in their thread.
Sometimes they ran well, but sometimes, an exception raised from my program as below:
--------------
Project XXX.exe raised exception class EZDatabaseError with message 'Invalid operation in AutoCommit mode'. Process stopped. Use Step or Run to continue.
--------------
Here is the 'update' clauses.
Here is the 'select' clauses.
Here is the process opening the database.
And here is the creating database clauses.
On top of this is the AutoCommit flag. Yes, I have read the quick guide in documentation area which has a brief explaination about it and has 4 URLs linking to 4 relative threads, but after I want over them, I was still hard to understand how to use it in a correcting way.
In fact, my problem is, my autocommit was set to true, and I used multithread to achieve the purpose, that was every threads could update or select data from a FB database file and they shared a TZConnection, but they had different TZQuery in their thread.
Sometimes they ran well, but sometimes, an exception raised from my program as below:
--------------
Project XXX.exe raised exception class EZDatabaseError with message 'Invalid operation in AutoCommit mode'. Process stopped. Use Step or Run to continue.
--------------
Here is the 'update' clauses.
Code: Select all
try
fdbquery.SQL.Clear();
fdbquery.Close();
fdbquery.Connection.StartTransaction();
fdbquery.SQL.Add(sqlstr);
fdbquery.ExecSQL();
fdbquery.Connection.Commit();
fdbquery.Close();
except
if fdbquery <> nil then begin
fdbquery.Connection.Rollback();
fdbquery.Close();
end;
end;
Code: Select all
try
fdbquery.SQL.Clear();
fdbquery.Close();
fdbquery.Connection.StartTransaction;
fdbquery.SQL.Add(sqlstr);
fdbquery.Open;
while not fdbquery.Eof do begin
{ fetch data }
fdbquery.Next();
end;
fdbquery.Connection.Commit;
fdbquery.Close();
except
if fdbquery <> nil then begin
fdbquery.Connection.Rollback;
fdbquery.Close();
end;
Result := false;
exit;
end;
Here is the process opening the database.
Code: Select all
fdb := TZConnection.Create(nil);
if not FileExists(fdbfullname) then begin
if not inCreateDB(fdbfullname, fdbdllfullname) then begin
Result := false;
exit;
end;
end;
fdb.Disconnect;
fdb.Database := fdbfullname;
fdb.User := 'SYSDBA';
fdb.Password := 'masterkey';
fdb.Protocol := 'firebirdd-2.0';
fdb.Properties.Clear;
fdb.Properties.Add('Dialect=3');
fdb.Properties.Add('hard_commit=yes');
fdb.Connect;
Code: Select all
sqlstr := Format('CreateNewDatabase=CREATE DATABASE ''%s'' USER ''SYSDBA'' PASSWORD ''masterkey'' PAGE_SIZE 16384 DEFAULT CHARACTER SET NONE', [dbfullname]);
fdb.Properties.Clear;
fdb.Properties.Add(sqlstr);
fdb.Properties.Add('Dialect=3');
fdb.Properties.Add('hard_commit=yes');
fdb.Database := dbfullname;
fdb.Protocol := 'firebirdd-2.0';
fdb.TransactIsolationLevel := tiReadCommitted;
try
fdb.Connect;
except
Result := false;
exit;
end;
try
try
sc := TZSQLProcessor.Create(nil);
except
Result := false;
exit;
end;
sc.Connection := fdb;
sc.DelimiterType := dtSetTerm;
fdb.StartTransaction;
{something about sc.Script.Add(...) }
try
sc.Execute;
except
fdb.Rollback;
Result := false;
exit;
end;
fdb.Commit;
Result := true;
finally
if sc <> nil then begin
sc.Connection := nil;
FreeAndNil(sc);
end;
end;
