Ok. Remarks below are for IBX in Delphi but I assume they are not that different from IBX for Lazarus (although they are now two separate products).
First of all, the TIBDatabase itself doesn't really need a TIBTransaction. You see the name is "DefaultDatabase" in TIBTransaction and "DefaultTransaction" in TIBDatabase. The transaction is used if you don't assign a transaction to the query of if you execute an SQL directly from the TIBDatabase component (with ExecuteImmediate). But the connection itself doesn't need a transaction.
Next, I usually create a transaction attached to the query and make that query the owner. In that case I don't need to destroy/free it myself.
I have something like this:
function CreateDefaultTransaction(Sender: TComponent; Db: TIBDatabase): TIBTransaction;
var
Trans: TIBTransaction;
begin
Trans := TIBTransaction.Create(Sender);
Trans.DefaultDatabase := Db;
Trans.DefaultAction := taRollback;
Trans.Params.Add('read_committed');
Trans.Params.Add('rec_version');
Trans.Params.Add('nowait');
Trans.Active := false;
Result := Trans;
end;
and use it like this:
Query1 := TIBQuery.Create(nil);
Query1.Database := datamd.Database;
Query1.Transaction := CreateDefaultTransaction(Query1, Query1.Database);
Query1.SQL.Text := 'SELECT * FROM RELATIE';
// CreateUpdateQueries(Query1, 'RELATIE'); // because IBX in Delphi doesn't automatically fill this
Query1.Active := true;
The connection is created in a DataModule. You could create a connection per form. In fact, if you use Zeos, I guess this is needed if you want a transaction per form. But for IBX you could use one connection which you could establish in a DataModule.
The CreateDefaultTransaction() creates a transaction and hangs it on the Query. When the query is opened, the transaction is automatically started. The DefaultAction of the transaction is taRollback so if you don't Commit manually, the transaction is rolled back.
Otherwise:
begin
if Assigned(Query1) and Assigned(Query1.Transaction) and Query1.Transaction.InTransaction then
Query1.Transaction.Commit;
if Assigned(Query1) then
Query1.Free;
end;
If you used Query1 := TIBQuery.Create(Self) you won't need the Query1.Free because the Self (TForm) will take case of that. But in your example (if you use it in a single buttonclick) you can use nil. But don't forget to use try/except. I also don't like the with-block.
So:
procedure TForm1.Button1Click(Sender: TObject);
var
j_query: TIBQUery;
begin
if not (main_connection.Connected) then main_connection.Open;
j_query := TIBQuery.Create(nil);
try
j_query.Database := main_connection;
j_query.Transaction := CreateDefaultTransaction(j_query, j_query);
j_query.SQL.Text := 'SELECT * FROM CLIENTS WHERE UPPER(NOM) LIKE :NOMC';
j_query.ParamByName('NOMC').AsString := 'JAB%';
j_query.Open;
if j_query.RecordCount > 0 then
ShowMessage(FieldByName('PRENOM').AsString)
else
ShowMessage('nothing found');
// j_query.Close; and rollback transaction is done automatically in .Close
// you don't commit if you didn't change anything. Then rollback is prefered.
finally
j_query.Free;
end;
end;
B.T.W. This could all be wrapped into one single procedure.
function GetQueryResult(SQL, Param: String; Db: TIBDatabase; Tr: TIBTransaction = nil);
(If Tr is nil a DefaultTransaction is created, if it is not nil, that transaction would be used)
Nom := GetQueryResult('SELECT * FROM CLIENTS WHERE UPPER(NOM) LIKE :param', 'JAB%', main_connection);
(or something like that)