Recent

Author Topic: Use of IBX  (Read 2091 times)

fjabouley

  • Full Member
  • ***
  • Posts: 128
Use of IBX
« on: January 21, 2020, 09:27:18 pm »
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][+][-]
  1.  
  2. var
  3.   main_connection: TIBDatabase;
  4.  
  5.  
  6. var
  7.   main_transaction: TIBTransaction;
  8.  
  9.  
  10. implementation
  11.  
  12.  
  13. {$R *.lfm}
  14.  
  15.  
  16. { TForm1 }
  17.  
  18.  
  19.  
  20.  
  21. procedure TForm1.FormCreate(Sender: TObject);
  22. begin
  23.   main_connection := TIBDatabase.Create(nil);
  24.   with main_connection do
  25.   begin
  26.     Connected := False;
  27.     LoginPrompt := False;
  28.     with Params do
  29.     begin
  30.       Clear;
  31.       Add('user_name=SYSDBA');
  32.       Add('password=');
  33.     end;
  34.     DatabaseName := 'inet://' + 'localhost' + '/' + 'C:\jsw\JDB.FDB';
  35.   end;
  36.   main_transaction := TIBTransaction.Create(nil);
  37.   with main_transaction do
  38.   begin
  39.     Active := False;
  40.     with Params do
  41.     begin
  42.       Clear;
  43.       Add('read_committed');
  44.       Add('rec_version');
  45.       Add('nowait');
  46.     end;
  47.     DefaultDatabase := main_connection;
  48.   end;
  49. end;
  50.  
  51.  
  52. procedure TForm1.FormDestroy(Sender: TObject);
  53. begin
  54.   if assigned(main_transaction) then
  55.   begin
  56.     main_transaction.Active := false;
  57.     main_transaction.Free;
  58.   end;
  59.  
  60.   if assigned(main_connection) then
  61.   begin
  62.     main_connection.Connected := false;
  63.     main_connection.Free;
  64.   end;
  65. end;
  66.  
  67.  
  68. procedure TForm1.Button1Click(Sender: TObject);
  69. var j_query : TIBQUery;
  70. begin
  71.  
  72.  
  73. if not(main_connection.Connected) then main_connection.Open;
  74. j_query := TIBQuery.Create(nil);
  75. with j_query do begin
  76. Database := main_connection;
  77. Transaction := main_transaction;
  78. Transaction.StartTransaction;
  79. SQL.Clear;
  80. SQL.Add('SELECT * FROM CLIENTS WHERE UPPER(NOM) LIKE :NOMC');
  81. ParamByName('NOMC').AsString:='JAB%';
  82. Open;
  83. showmessage(FieldByName('PRENOM').AsString);
  84. Close;
  85. Transaction.Commit;
  86. Free;
  87. end;
  88. end;
  89.  
Best regards
« Last Edit: January 22, 2020, 06:50:08 pm by fjabouley »

rvk

  • Hero Member
  • *****
  • Posts: 6056
Re: Use of IBX
« Reply #1 on: January 22, 2020, 11:07:09 am »
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][+][-]
  1. function CreateDefaultTransaction(Sender: TComponent; Db: TIBDatabase): TIBTransaction;
  2. var
  3.   Trans: TIBTransaction;
  4. begin
  5.   Trans := TIBTransaction.Create(Sender);
  6.   Trans.DefaultDatabase := Db;
  7.   Trans.DefaultAction := taRollback;
  8.   Trans.Params.Add('read_committed');
  9.   Trans.Params.Add('rec_version');
  10.   Trans.Params.Add('nowait');
  11.   Trans.Active := false;
  12.   Result := Trans;
  13. end;

and use it like this:
Code: Pascal  [Select][+][-]
  1. Query1 := TIBQuery.Create(nil);
  2. Query1.Database := datamd.Database;
  3. Query1.Transaction := CreateDefaultTransaction(Query1, Query1.Database);
  4. Query1.SQL.Text := 'SELECT * FROM RELATIE';
  5. // CreateUpdateQueries(Query1, 'RELATIE'); // because IBX in Delphi doesn't automatically fill this
  6. 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:
Code: Pascal  [Select][+][-]
  1. begin
  2.   if Assigned(Query1) and Assigned(Query1.Transaction) and Query1.Transaction.InTransaction then
  3.     Query1.Transaction.Commit;
  4.   if Assigned(Query1) then
  5.     Query1.Free;
  6. 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][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. var
  3.   j_query: TIBQUery;
  4. begin
  5.   if not (main_connection.Connected) then main_connection.Open;
  6.   j_query := TIBQuery.Create(nil);
  7.   try
  8.     j_query.Database := main_connection;
  9.     j_query.Transaction := CreateDefaultTransaction(j_query, j_query);
  10.     j_query.SQL.Text := 'SELECT * FROM CLIENTS WHERE UPPER(NOM) LIKE :NOMC';
  11.     j_query.ParamByName('NOMC').AsString := 'JAB%';
  12.     j_query.Open;
  13.     if j_query.RecordCount > 0 then
  14.       ShowMessage(FieldByName('PRENOM').AsString)
  15.     else
  16.       ShowMessage('nothing found');
  17.     // j_query.Close; and rollback transaction is done automatically in .Close
  18.     // you don't commit if you didn't change anything. Then rollback is prefered.
  19.   finally
  20.     j_query.Free;
  21.   end;
  22. end;

B.T.W. This could all be wrapped into one single procedure.
Code: Pascal  [Select][+][-]
  1. 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][+][-]
  1. Nom := GetQueryResult('SELECT * FROM CLIENTS WHERE UPPER(NOM) LIKE :param', 'JAB%', main_connection);
(or something like that)

fjabouley

  • Full Member
  • ***
  • Posts: 128
Re: Use of IBX
« Reply #2 on: January 22, 2020, 02:28:43 pm »
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

  • Hero Member
  • *****
  • Posts: 6056
Re: Use of IBX
« Reply #3 on: January 22, 2020, 02:54:17 pm »
Do 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][+][-]
  1. query := TIBQuery.Create(nil);
  2. 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][+][-]
  1. query := TIBQuery.Create(Self); // or .Create(FormInstance);
  2. 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

  • Hero Member
  • *****
  • Posts: 648
Re: Use of IBX
« Reply #4 on: January 22, 2020, 03:20:04 pm »
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][+][-]
  1. oTrans.StartTransaction
  2. try
  3.   SQL statements //for example, several tables can be UPDATEd, INSERTed, etc.
  4.   oTrans.Commit;
  5. except
  6.   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.
  7.   Raise;
  8. end;

So, by modifying your code:
Code: Pascal  [Select][+][-]
  1.     j_query := TIBQuery.Create(nil);
  2.     try
  3.         j_query.Database := main_connection;
  4.         j_query.Transaction := main_transaction;
  5.         j_query.Transaction.StartTransaction;
  6.         try //(!!!)
  7.             j_query.SQL.Clear;
  8.             j_query.SQL.Add('SELECT * FROM CLIENTS WHERE UPPER(NOM) LIKE :NOMC');
  9.             j_query.ParamByName('NOMC').AsString:='JAB%';
  10.             j_query.Open;
  11.             Showmessage(FieldByName('PRENOM').AsString);
  12.             j_query.Transaction.Commit; //Open-SELECT = success => trans. is finished with commit
  13.         Except //(!!!)
  14.             j_query.Transaction.Rollback;  // //(!!!) Open-SELECT = failed => trans. is finished with rollback
  15.             raise; //(!!!) to see what is wrong in the SELECT?
  16.         end;
  17.         if j_query.state=dsBrowse then
  18.             j_query.Close;
  19.     Finally
  20.         j_query.Free;
  21.     end;
« Last Edit: January 22, 2020, 03:38:03 pm by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

rvk

  • Hero Member
  • *****
  • Posts: 6056
Re: Use of IBX
« Reply #5 on: January 22, 2020, 04:49:30 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][+][-]
  1.   if j_query.state=dsBrowse then
  2.     j_query.Close;
  3. finally
  4.   j_query.Free;

devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: Use of IBX
« Reply #6 on: January 22, 2020, 05:44:54 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://forum.lazarus.freepascal.org/index.php/topic,48159.msg346926.html#msg346926 .

Quote
But 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.

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][+][-]
  1.   if j_query.state=dsBrowse then
  2.     j_query.Close;
  3. finally
  4.   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).

« Last Edit: January 22, 2020, 06:05:16 pm by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

 

TinyPortal © 2005-2018