Simple confirmatory question
lazarus / 16-Databases

----------------------------------------------------------------------
egsuh · 2024-10-08 07:23

There is a very simple function below.

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
TAQFB4.RespondentEmail(tpid: string; trid: integer): string;var   xqr:
TSQLQuery;   xtr: TSQLTransaction;begin   xqr :=
TSQLQuery.Create(nil);   xtr := TSQLTransaction.Create(xqr); 
 xtr.DataBase := fbAQ;   xqr.DataBase := fbAQ;   xqr.Transaction :=
xtr;    try      with xqr do begin         SQL.Text := 'select Email
from samplelist '                    + ' where project_id=:pid and
respondent_id=:rid';         Transaction.Active := True;       
 Params[0].AsString := tpid;         Params[1].AsInteger := trid;    
   Open;         Result := Fields[0].AsString;         (Transaction as
TSQLTransaction).Rollback;      end;   finally      xqr.Free; 
 end;end;
Here, the query and transaction are created locally. Is this
customary?

I used to use TSQLQuery component dropped on the DataModule (let's say
its name is SQLQuery1). But this method had risk of "nesting the same
TSQLQuery and TSQLtransaction, when the function is called within
another function that uses the same transaction (if not the same
TSQLQuery).  So I'm moving to locally creating query and transaction
components.

----------------------------------------------------------------------
rvk · 2024-10-08 07:40

Quote from: egsuh on October 08, 2024, 09:23:23 amHere, the query and
transaction are created locally. Is this customary?

I used to use TSQLQuery component dropped on the DataModule (let's say
its name is SQLQuery1). But this method had risk of "nesting the same
TSQLQuery and TSQLtransaction, when the function is called within
another function that uses the same transaction (if not the same
TSQLQuery).  So I'm moving to locally creating query and transaction
components.
Yes, this is customary. Precisely for the reason you mention.
Although I would make it a standalone function, passing the database,
in which case it can even be used for different database connections.
But making it a function in the datamodule is also possible.

PS. I would build in some error checking. If your query doesn't return
a result it will generate an exception at the moment (at the Fields[0]
which doesn't exist when there is no resultset). That could be
intentional but you need to handle the exception a step higher then.

PPS. I also would make such function a bit more generic. For example
also passing which fieldname you want to return.
But I guess you get the point... standalone functions like this will
greatly reduce the chances for errors in using the wrong TSQLQuery and
messing things up.

PPPS. I also have some procedure which change the database with
insert, update and delete. In that case you also need to pass a
TSQLTransaction so the update is done in the correct transaction flow
for the rest of your program.

----------------------------------------------------------------------
egsuh · 2024-10-08 08:13

@rvk

Thank you for your replay, which gives me comfort. I'll add another
layer of try... except so that errors are handled (even though some
parts are left intentionally so that logical errors raise exceptions.)

Regarding generic approach, I do not write many programs.

Third point is interesting. Probably I might have to define like (with
default values of Transaction, I do not have to change other codes) :

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
TAQFB4.RespondentEmail(tpid: string; trid: integer; Transaction:
TSQLTransaction=nil): string;var     ...begin     ...    if
Transaction  = nil then begin         xtr := TSQLTransaction.Create
(xtr);        xtr.DataBase := fbAQ;     end     else xtr :=
transaction;      xqr.Transaction := xtr;      ... end;
Really thank you for the points. They are learnable from nowhere but
experiences plus ingenuity.

----------------------------------------------------------------------
Ștefan-Iulian Alecu · 2024-10-08 21:00

Along with what rvk mentioned, you should probably also guard xtr
behind a try..finally block. This is how I'd approach your code in
particular:

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
TAQFB4.RespondentEmail(AProjectID: string; ARespondentID: integer):
string;var  query: TSQLQuery;  transaction: TSQLTransaction;begin 
query := TSQLQuery.Create(Self);  transaction :=
TSQLTransaction.Create(query);   try    query.Database := fbAQ;   
transaction.Database := fbAQ;    query.Transaction := transaction;   
query.SQL.Text := 'select Email from samplelist where project_id =
:pid and respondent_id = :rid';   
query.Params.ParamByName('pid').AsString := AProjectID;   
query.Params.ParamByName('rid').AsInteger := ARespondentID;     if not
transaction.Active then      transaction.StartTransaction;     try   
 query.Open;      if not query.EOF then        Result :=
query.Fields[0].AsString      else        raise Exception.Create('No
email found for the specified project and respondent IDs.');      
transaction.Commit;    except      on E: Exception do      begin     
 transaction.Rollback;        raise Exception.Create('Error fetching
respondent email: ' + E.Message);      end;    end;  finally   
query.Free;  end;end;

----------------------------------------------------------------------
CharlyTango · 2024-10-09 11:49

Don't create zombies and also free the transaction, but I guess that
was just a typo anyway.

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 .... 
finally    transaction.Free    query.Free;  end;
But the real question beside a fomal query strategy in a function is
to have some kind of business logic.
Is it worth an exception if the query for a mail address brings no
result?

This would mean that a serious error has occurred that is incompatible
with the further course of the programme, which in my opinion is not
the case here. Therefore an exception might be the wrong tool.

----------------------------------------------------------------------
Zvoni · 2024-10-09 12:17

Quote from: CharlyTango on October 09, 2024, 01:49:55 pmBut the real
question beside a fomal query strategy in a function is to have some
kind of business logic.
Is it worth an exception if the query for a mail address brings no
result?

This would mean that a serious error has occurred that is incompatible
with the further course of the programme, which in my opinion is not
the case here. Therefore an exception might be the wrong tool.
Agreed.
A Try/Finally/Except for a simple SELECT-Query doesn't make sense,
except if you are allowing Users to type the Query themselves!
Then it does make sense to guard against TYPO's

If the Query itself (the SQL-Statement) is correct, then it's a simple
check against EOF/RecordCount>0

Next: For SELECT-Queries Transactions are superflous. You cannot
commit them, you cannot roll them back.
This only applies to INSERT/UPDATE/DELETE

----------------------------------------------------------------------
rvk · 2024-10-09 12:19

Quote from: CharlyTango on October 09, 2024, 01:49:55 pmDon't create
zombies and also free the transaction, but I guess that was just a
typo anyway.No no no..   Look closer at the source.
TSQLTransaction is created with xtr (the query itself) as Owner.

NEVER free components of which your code isn't itself the owner (by
having it created with nil).

So either use Create(nil) and do Free OR do Create(Owner) and let the
owner free the object when doing Owner.Free.

Quote from: CharlyTango on October 09, 2024, 01:49:55 pmIs it worth an
exception if the query for a mail address brings no result?Yes, I
would return an empty string in this case (not an exception).