Use of IBX lazarus / 16-Databases ---------------------------------------------------------------------- fjabouley · 2020-01-21 20:27 Hello all. I'm starting reading IBX documentation, and as I don't want to repeat the mistakes I made previously with zeos, may I ask you if using this for example will be enough to open a transaction, do some stuff and close it ? Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  var  main_connection: TIBDatabase;  var  main_transaction: TIBTransaction;  implementation  {$R *.lfm}  { TForm1 }    procedure TForm1.FormCreate(Sender: TObject);begin  main_connection := TIBDatabase.Create(nil);  with main_connection do  begin    Connected := False;    LoginPrompt := False;    with Params do    begin      Clear;      Add('user_name=SYSDBA');      Add('password=');    end;  DatabaseName := 'inet://' + 'localhost' + '/' + 'C:\jsw\JDB.FDB';  end;  main_transaction := TIBTransaction.Create(nil);  with main_transaction do  begin    Active := False;    with Params do    begin      Clear;      Add('read_committed');      Add('rec_version');      Add('nowait');    end;    DefaultDatabase := main_connection;  end;end;  procedure TForm1.FormDestroy(Sender: TObject);begin  if assigned(main_transaction) then  begin    main_transaction.Active := false;    main_transaction.Free;  end;   if assigned(main_connection) then  begin    main_connection.Connected := false;    main_connection.Free;  end;end;  procedure TForm1.Button1Click(Sender: TObject);var j_query : TIBQUery;begin  if not(main_connection.Connected) then main_connection.Open;j_query := TIBQuery.Create(nil);with j_query do beginDatabase := main_connection;Transaction := main_transaction;Transaction.StartTrans action;SQL.Clear;SQL.Add('SELECT * FROM CLIENTS WHERE UPPER(NOM) LIKE :NOMC');ParamByName('NOMC').AsString:='JAB%';Open;showmessage(FieldByN ame('PRENOM').AsString);Close;Transaction.Commit;Free;end;end; Best regards ---------------------------------------------------------------------- rvk · 2020-01-22 10:07 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: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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 thisQuery1.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: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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) Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Nom := GetQueryResult('SELECT * FROM CLIENTS WHERE UPPER(NOM) LIKE :param', 'JAB%', main_connection);(or something like that) ---------------------------------------------------------------------- fjabouley · 2020-01-22 13:28 Thanks rvk ! Do you have to free the transaction you created in the j_query ? or is it freed when you free j_query ? Code: [Select]j_query.Transaction := CreateDefaultTransaction(j_query, main_connection); Regards ---------------------------------------------------------------------- rvk · 2020-01-22 13:54 Quote from: fjabouley on January 22, 2020, 02:28:43 pmDo you have to free the transaction you created in the j_query ? or is it freed when you free j_query ? If you do Component.Create(Owner) and give the object an owner then THAT owner is responsible for freeing that Component. If you do Component.Create(nil) then YOU (your code) is responsible for freeing that Component. So if you do Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i query := TIBQuery.Create(nil);query.transaction := TIBTransaction.Create(query);you see that query.transaction is owned by query and it will take care of the freeing of the transaction. But YOU need to free the query itself with query.free. If you do Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i query := TIBQuery.Create(Self); // or .Create(FormInstance);query.transaction := TIBTransaction.Create(query);then you don't need to do query.free (and no Transaction.Free) because the Self (or FormInstance) will free them during the closing of the form. But you'll only do TIBQuery.Create(Self) when you want the TIBQuery to be open/existing during the complete lifetime of the form. If you only want to query the database for a value (short and simple sql) then TIBQuery.Create(nil) and query.Free is better (because the lifetime of the query and transaction is limited to that time). ---------------------------------------------------------------------- devEric69 · 2020-01-22 14:20 In explicit clients-server (regardless of the management of memory resources on the client side, ie Create, Free, ...), always try to code in pseudo-code (if you don't use the defaultAction property!): Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i oTrans.StartTransactiontry  SQL statements //for example, several tables can be UPDATEd, INSERTed, etc.  oTrans.Commit;except  oTrans.Rollback; //it prevents the creation of an orphan log, and most importantly, it closes the transaction even in case of SQL failure, on server side then.  Raise;end; So, by modifying your code: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     j_query := TIBQuery.Create(nil);    try        j_query.Database := main_connection;        j_query.Transaction := main_transaction;      j_query.Transaction.StartTransaction;        try //(!!!)            j_query.SQL.Clear;            j_query.SQL.Add('SELECT * FROM CLIENTS WHERE UPPER(NOM) LIKE :NOMC');            j_query.ParamByName('NOMC').AsString:='JAB%';            j_query.Open;            Showmessage(FieldByName('PRENOM').AsString);            j_query.Transaction.Commit; //Open-SELECT = success => trans. is finished with commit        Except //(!!!)            j_query.Transaction.Rollback;  // //(!!!) Open-SELECT = failed => trans. is finished with rollback             raise; //(!!!) to see what is wrong in the SELECT?        end;        if j_query.state=dsBrowse then             j_query.Close;    Finally      j_query.Free;    end; ---------------------------------------------------------------------- rvk · 2020-01-22 15:49 Quote from: devEric69 on January 22, 2020, 03:20:04 pm(if you don't use the defaultAction property!):Isn't DefaultAction a mandatory property? That's why I always have it set to taRollback so I always know it's rolled back in case of an exception and the exception can just fall through to the main procedure. (i.e. I don't have to do a try/except because it will just go to the caller with the exception) But I guess it's a question of preference if you want to manually do it (maybe for readability). Not sure about the usefulness of this line (because the query if freed directly after it the close is done regardless, so the if and close can be removed). Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   if j_query.state=dsBrowse then    j_query.Close;finally  j_query.Free; ---------------------------------------------------------------------- devEric69 · 2020-01-22 16:44 Quote from: rvk on January 22, 2020, 04:49:30 pmQuote from: devEric69 on January 22, 2020, 03:20:04 pm(if you don't use the defaultAction property!):Isn't DefaultAction a mandatory property? That's why I always have it set to taRollback so I always know it's rolled back in case of an exception and the exception can just fall through to the main procedure. (i.e. I don't have to do a try/except because it will just go to the caller with the exception) Indeed, I'm wrong: there is no "saNone" choice, for the defaultAction property: so, we must choose between saCommit, saCommitRetainning, saRollBack, saRollbackRetaining. ==> I've posted the behaviour of IBX trick properties here: https://fo rum.lazarus.freepascal.org/index.php/topic,48159.msg346926.html#msg346 926 . Quote from: rvk on January 22, 2020, 04:49:30 pmQuoteBut I guess it's a question of preference if you want to manually do it (maybe for readability). Always true: it's especially because I've just finished many months of Php\mariaDb programming with the PDO object: then, with the obligation to code all explicitly; there's no such clever properties to make life easier. Quote from: rvk on January 22, 2020, 03:20:04 pmNot sure about the usefulness of this line (because the query if freed directly after it the close is done regardless, so the if and close can be removed). Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   if j_query.state=dsBrowse then    j_query.Close;finally  j_query.Free; And true again. Free leads to an implicit Close. Aside: in the explicit context, these two lines could be replaced  by something else if the Commit works (like the well-known example of [pseudo-code on] ClearOfCachedUpdates; [pseudo-code /off], if the client side CachedUpdates property is used).