What are the rules for parameters in DBISAM V4 scripts?

Example:

select f1 from t1 as mf where f2 =
into memory\temp
union
select fa from ta where fb = ;
select fx, mf
from t3
join memory\temp on mf = f8
where f9 = ;
etc etc

Can you do this type of thing? How many parameters/script steps are allowed?

Robert

Robert,

<< What are the rules for parameters in DBISAM V4 scripts? >>

Each semicolon-delimited statement will require a separate population of the
parameters via an OnGetParams event handler.  That's really all there is to
it for scripts.  The parameter rules for the individual statements are the
same as if they were executed individually, of course.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:E081645A-6D41-4FA8-856D-9598F0824408@news.elevatesoft.com...
> Robert,
>
> << What are the rules for parameters in DBISAM V4 scripts? >>
>
> Each semicolon-delimited statement will require a separate population of
> the parameters via an OnGetParams event handler.  That's really all there
> is to it for scripts.  The parameter rules for the individual statements
> are the same as if they were executed individually, of course.
>

OK, given my script

select f1 from t1 as mf where f2 =
into memory\temp
union
select fa from ta where fb = ;
select fx, mf
from t3
join memory\temp on mf = f8
where f9 = ;
etc etc

Can I have something like

Procedure OnGetParams;
p1 := 'foo'; p2 := 'fee'; p3 := 'faa';
end;

even though some of the specific parameters are not used in the step
involved? Obviously, I'm trying to keep it simple and uncouple the SQL from
the Pascal code.

Robert

Robert,

<< Can I have something like >>

You're going to have to verify that the parameter exists before you try to
assign a value to it, or else you will get an error.  Apart from that, yes,
you can do a generic type of parameter assignment that doesn't really pay
attention to the actual SQL.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:3E9A0B58-AAA4-4617-90BE-FD811260E4FE@news.elevatesoft.com...
> Robert,
>
> << Can I have something like >>
>
> You're going to have to verify that the parameter exists before you try to
> assign a value to it, or else you will get an error.

That the parameter exists somwhere in the script, or in the step about to be
initialized?

Robert

Apart from that, yes,
> you can do a generic type of parameter assignment that doesn't really pay
> attention to the actual SQL.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Robert


My fallible memory says that OnGetParams is fired for each loop through the query not each statement in the query, so effectively each time you call .ExecSQL.

Roy Lambert [Team Elevate]

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:4A4510F3-6757-4532-A64B-89DAD9F54C90@news.elevatesoft.com...
> Robert
>
>
> My fallible memory says that OnGetParams is fired for each loop through
> the query not each statement in the query, so effectively each time you
> call .ExecSQL.
>

My question to Tim (which obviously I'm not phrasing very well) is: since at
least according to the documentation OnGetParams works as you say, gets
fired once for every execution of the query (not for each step of a script),
then ALWAYS setting params in OnGetParams - whether it is a multi step
script or not - should work.

What I've always done in the past is something like

query.parambyname('foo').AsInteger := 2;
query.parambyname('fee').AsInteger := 3;
query.open;

This works if foo and fee are parameters in the first (or only) SQL
statement. Apparently, if we move the query.parambyname statements to
OnGetParams it will work EVEN IF FOO AND FEE ARE ON DIFFERENT STEPS IN A
SCRIPT. If that's the case, just for consistency I would start using
OnGetParams every time we set parameters, regradless of whether or not it is
a script. On every program, if you want to see how parameters are set for a
query, look into the query's OnGetParams. Makes sense to me.

However, I'm not 100% sure that the documentation is right. Tim seems to be
saying that it gets fired for each step (since each step is prepared before
execution, that's why you can't do explicit prepares on a script).

Robert

Robert


Yup, definitely not the question I was answering

Roy Lambert [Team Elevate]

Robert,

as far as I understand it, OnGetParams is called for each SQL complete statement in a script.

with the following SQL script :

UPDATE PRESTATIONS                      /* Stage 1 : Prestations, Collaborateur ligne */
SET   ColTrav=:NewCol,
   UIdGlob=:UIdGlob,
   LogCode=:LogCode,  
        LogUser=:LogUser,
   LogDateUpdate=:LogDateUpdate
WHERE    ColTravUId=:ColUId AND ColTravNatObj=:NatObj AND ColTrav=:OldCol COMMIT INTERVAL 300;

UPDATE Mandats                          /* Stage 2 : Collaborateur responsable de mandat */
SET   ColResp=:NewCol,
   UIdGlob=:UIdGlob,
        LogUser=:LogUser,
   LogDateUpdate=:LogDateUpdate
WHERE ColRespUId=:ColUId and ColRespNatObj=:NatObj and ColResp=:OldCol COMMIT INTERVAL 300;

I have an on OnGetParams that goes like :

procedure TfmCollab_V0100.USQLColGetParams(Sender: TObject);
var
 ix : integer;
 lParams : TDBIsamParams;
 lParam  : TDBIsamParam;
begin
 lParams := USQLCol.Params;
 for ix := 0 to lParams.Count - 1 do begin
   lParam  := lParams[ix];
   if lParam.Name = 'ColUId' then
     lParam.AsInteger := tCUId.Value
   else if lParam.Name = 'NatObj' then
     lParam.AsSmallInt := tCNatObj.AsInteger
   else if lParam.Name = 'OldCol' then
     lParam.AsString := FColOldValue
   else if lParam.Name = 'NewCol' then
     lParam.AsString := tCCol.AsString
   else if lParam.Name = 'UIdGlob' then
     lParam.AsInteger := tCUIdGlob.AsInteger
   else if lParam.Name = 'LogCode' then
     lParam.AsInteger := tCLogCode.AsInteger
   else if lParam.Name = 'LogUser' then
     lParam.AsString := tCLogUser.AsString
   else if lParam.Name = 'LogDateUpdate' then
     lParam.AsDateTime := tCLogDateUpdate.AsDateTime;
 end;
 Inc(FUpdColStage);
end;

tC : is a TDBISAMTable, and the USQLCol.ExecSQL SQL is called in the tCAfterPost event.

Bruno

"Bruno Krayenbuhl" <b.kray&_bluewin> wrote in message
news:78E2413D-8940-4D05-99E1-353083BA8350@news.elevatesoft.com...
> Robert,
>
> as far as I understand it, OnGetParams is called for each SQL complete
> statement in a script.
>

Thanks. Thas was also my understanding, but the manual is a  bit confusing.
As to the second part of my question, should I assume that paramcount
changes with each step, and only reflects the parameters active on the step
about to be executed? That would seem to be why you even bother to go thru
the loop, instead of setting every parameter every time. Is that the case?

Robert