EgonHugeist wrote: ↑22.01.2021, 07:09
1. assign param by index as it's implemented since ages.
Doesn't make sense - parameters have names for a reason. Humans like working with names and not with numbers.
EgonHugeist wrote: ↑22.01.2021, 07:09
2. Assign param by name as aehimself proposes.
This possiblity makes the most sense. If people expect parameters to keep their value (i.e. me) it is what they will expect.
EgonHugeist wrote: ↑22.01.2021, 07:09
3. Do not assign the values if the SQL changes as Fr0sT states.
Why would we implement this? What is the gain or the win in this behavior? People who expext this will assign values to all paramaters anyway - wether we implement it like this or not.
One example - I do have a program where I need to get a list of people who celebrated their birth days in the last days or who will celebrate their birth days in the next days. This is the (simplified) implementation:
1) Have a query sitting on my form with the following sql:
Code: Select all
select X.* from (
SELECT ID, Name, Surname, birthday, cast(EXTRACT(month from birthday) * 100 + Extract(day from birthday) as integer) as birthday_c
FROM clients C
WHERE (remindme = 'y') AND ((state <> 'dead') or (state is null)) and ((cast(:USERID as idtype) in (Advisor1, Advisor2)) or (cast(:CanSeeAllClients as yesno) = 'y'))
)
AS X
/* has to be added / changed by code: */
/* where birthday_c >= :MINDAY and birthday_c <= :MAXDAY */
Please remember that this code is more complex in the real world example as it has more parameters and fetches birthdays from different tables. (@Egonhugeist: FrameStartformularGeburtstage)
2) The folowing code in the initialization of the form:
Code: Select all
BirthdaysQ.ParamByName('USERID').AsString := frmDatamodule.UserID;
BirthdaysQ.ParamByName('CanSeeAllClients').AsString := frmDatamodule.CheckUserPermissionDB(psSeeAllClients);
3) The following code to react to user input:
Code: Select all
procedure TStartformularGeburtstageFrame.ApplyChangedBirthdayParams;
var
StartDate, EndDate: TDate;
from, until: Integer;
y, m, d: Word;
begin
StartDate := IncDay(date, BackDaysEdt.Value * (-1));
EndDate := IncDay(date, NextDaysEdt.Value);
DecodeDate(StartDate, y, m, d);
from := m * 100 + d;
DecodeDate(EndDate, y, m, d);
until := m * 100 + d;
if from <= until then
// All is in the same year
with ZQ_Partner.SQL do Strings[Count - 1] := 'where birthday_c >= :MINDAY and birthday_c <= :MAXDAY'
else
// Look in old and new year
with ZQ_Partner.SQL do Strings[Count - 1] := 'where birthday_c >= :MINDAY or birthday_c <= :MAXDAY';
BirthdaysQ.Close;
BirthdaysQ.ParamByName('minday').AsInteger := from;
BirthdaysQ.ParamByName('maxday').AsInteger := until;
BirthdaysQ.Open;
end;
So while it is true that - strictly speaking - I generate a new query when going through ApplyChangedBirthdayParams, it basically stays the same (more complex) query and only the last line - the way that birthdays get evaluated changes.
This also allows me to split the problem in three (logically separated) parts:
1) Generate a query that works - saved in the form
2) Assign the paramaters that have to be applied at all times right at the start. So I can't forget to set them later on.
3) Assign the parameters that the user is allowed to change.
By implementing option three we break the users option to work this way. Also we break old code that worked until now. And honestly I fail to see what we (and others) can gain by implementing this.
Side note: I do know (now) that there are ways to write the SQL so I don't have to change the SQL anymore. But I didn't know that 15 years ago, when I first implemented this. And also this is not the point. The point is: I don't want Zeos to force me to change my code and generate more work for me. I want Zeos to support me in getting my work done.
.