Hi,

I am learning how to use params and have problems using params with scripts.

My example (doing nothing real, just example with two commands):

 with workQuery do
   begin
     Close;
     SQL.Clear;
     SQL.Add('DROP TABLE IF EXISTS Memory\Temp;'        );

     SQL.Add('SELECT'                                  );
     SQL.Add('  *'                                     );
     SQL.Add('FROM'                                    );
     SQL.Add('  NewTable N'                            );
     SQL.Add('WHERE'                                   );
     SQL.Add('    ( N.Placano = '+QuotedStr('J')+' )'  );
     SQL.Add('        and'                             );
     SQL.Add('    ( N.Storno  = '+QuotedStr('N')+' )'  );
     SQL.Add('        and'                             );
     SQL.Add('    ( N.Datum_Od BETWEEN AND :do )'  );
     SQL.Add('        and'                             );
     SQL.Add('    ( N.Datum_Do BETWEEN AND :do )'  );
     SQL.Add(';'                                       );
     Prepare;
     ParamByName('od').AsDate:=now-100;
     ParamByName('do').AsDate:=now+100;
     ExecSQL;
   end;  //with

Without first line (DROP TABLE...) it works OK, with this line parameter 'od' is
not found.

How can I use ParamByName in scripts with multiple commands?
Must I call ExecSQL after each command?
Must I clear scripts after each ExecSQL?

I am using DBisam v4

TIA for any help.

Regards, Joze

Joze,

<< How can I use ParamByName in scripts with multiple commands? >>

This is covered in the manual:

http://www.elevatesoft.com/manual?action=mantopic&id=dbisam4&product=d&version=7&category=1&topic=28

under "Parameters and Multiple SQL Statements".

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:D0061633-C183-4CFD-826A-FB94AD34F5EE@news.elevatesoft.com...
> Joze,
>
> << How can I use ParamByName in scripts with multiple commands? >>
>
> This is covered in the manual:
>
> http://www.elevatesoft.com/manual?action=mantopic&id=dbisam4&product=d&version=7&category=1&topic=28
>
> under "Parameters and Multiple SQL Statements".
>

let me make sure I understand

if the sql is
update table set field = :f;
update table2 set field2 = :f2;

Then my code would be something like

var QStep : integer;
.....
QStep := 0;
Query.ExecSQL;
....
Procedure QueryOnGetParams...
inc(QStep);
case QStep of
1 : Query.ParamByName('f').AsInteger := 25;
2: Query.ParamByName('f2').AsInteger := 33;
end;

Robert

> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Robert

I'm operating from memory, and infrequent use, here so could be totally wrong. As I remember the script back in the V3 days would pick up the parameters for the first loop, and keep those all the time. The OnGetParams event was to populate the parameters for each loop round. So to extend your example you'd have something like:

procedure TForm1.DoALoop;
begin
Query.Prepare;
while not table1.eof do begin
 Query.Close;
 Query.ExecSQL;
 table1.next;
end;
end;

procedure TForm1.QueryOnGetParams....
begin
Query.ParamByName('f').AsInteger := table1.fieldbtname('x').asinteger;
Query.ParamByName('f2').AsInteger := table1.fieldbtname('y').asinteger;
end;


Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:95692F7B-2F3D-4862-AD7D-4F48070BE0E3@news.elevatesoft.com...
> Robert
>
> I'm operating from memory, and infrequent use, here so could be totally
> wrong. As I remember the script back in the V3 days would pick up the
> parameters for the first loop, and keep those all the time. The
> OnGetParams event was to populate the parameters for each loop round.

That's definitely NOT the case. You simply reset the parameters for the
whole query by doing a query.parambyname. You do not need to use
OnGetParams.

> So to extend your example

You are not extending my example, you are changing it completely. The whole
point is whether setting up the value of :f2 before starting the execution
of the query will work.

> you'd have something like:
>
> procedure TForm1.DoALoop;
> begin
> Query.Prepare;
> while not table1.eof do begin
> Query.Close;
> Query.ExecSQL;
> table1.next;
> end;
> end;
>
> procedure TForm1.QueryOnGetParams....
> begin
> Query.ParamByName('f').AsInteger := table1.fieldbtname('x').asinteger;
> Query.ParamByName('f2').AsInteger := table1.fieldbtname('y').asinteger;
> end;
>

That's not the issue. What you are describing is the standard use of
parameters. The issue (I think) is that parameters only apply to the first
SQL statement in the script (go back to my SQL). So if you want to use
parameters in the second or third or other step, you would have to set them
before that step starts. If that is true (that is the question, I guess)
then your code would not work. :f2 would not have a value.

Anyeay, that's the way I understand it, and that's what I was asking Tim to
confirm. I'm pretty sure that the answer applies to V3 and V4.

Robert

>
> Roy Lambert
>

 SQL.Add('    ( N.Datum_Od BETWEEN 1 AND :do1 )'  );
 SQL.Add('        and'                             );
 SQL.Add('    ( N.Datum_Do BETWEEN 2 AND :do2 )'  );
 SQL.Add(';'                                       );
 Prepare;
 ParamByName('od1').AsDate:=now-100;
 ParamByName('do1').AsDate:=now+100;
 ParamByName('od2').AsDate:=now-100;
 ParamByName('do2').AsDate:=now+100;
 ExecSQL;



"Joze" <Joze.NoSpam.Stihec@nospam.siol.net> wrote in message
news:cle124dupbipqj68ijjapecacsmcotv1sa@4ax.com...
> Hi,
>
> I am learning how to use params and have problems using params with
> scripts.
>
> My example (doing nothing real, just example with two commands):
>
>  with workQuery do
>    begin
>      Close;
>      SQL.Clear;
>      SQL.Add('DROP TABLE IF EXISTS Memory\Temp;'        );
>
>      SQL.Add('SELECT'                                  );
>      SQL.Add('  *'                                     );
>      SQL.Add('FROM'                                    );
>      SQL.Add('  NewTable N'                            );
>      SQL.Add('WHERE'                                   );
>      SQL.Add('    ( N.Placano = '+QuotedStr('J')+' )'  );
>      SQL.Add('        and'                             );
>      SQL.Add('    ( N.Storno  = '+QuotedStr('N')+' )'  );
>      SQL.Add('        and'                             );
>      SQL.Add('    ( N.Datum_Od BETWEEN AND :do )'  );
>      SQL.Add('        and'                             );
>      SQL.Add('    ( N.Datum_Do BETWEEN AND :do )'  );
>      SQL.Add(';'                                       );
>      Prepare;
>      ParamByName('od').AsDate:=now-100;
>      ParamByName('do').AsDate:=now+100;
>      ExecSQL;
>    end;  //with
>
> Without first line (DROP TABLE...) it works OK, with this line parameter
> 'od' is
> not found.
>
> How can I use ParamByName in scripts with multiple commands?
> Must I call ExecSQL after each command?
> Must I clear scripts after each ExecSQL?
>
> I am using DBisam v4
>
> TIA for any help.
>
> Regards, Joze

It would be nice if youy read the original question before replying.

Robert

"Walter Matte" <mattew_@_interlog.com> wrote in message
news:32D3C878-96DD-4EBA-A668-09CAED2A2922@news.elevatesoft.com...
>
>  SQL.Add('    ( N.Datum_Od BETWEEN 1 AND :do1 )'  );
>  SQL.Add('        and'                             );
>  SQL.Add('    ( N.Datum_Do BETWEEN 2 AND :do2 )'  );
>  SQL.Add(';'                                       );
>  Prepare;
>  ParamByName('od1').AsDate:=now-100;
>  ParamByName('do1').AsDate:=now+100;
>  ParamByName('od2').AsDate:=now-100;
>  ParamByName('do2').AsDate:=now+100;
>  ExecSQL;
>
>
>
> "Joze" <Joze.NoSpam.Stihec@nospam.siol.net> wrote in message
> news:cle124dupbipqj68ijjapecacsmcotv1sa@4ax.com...
>> Hi,
>>
>> I am learning how to use params and have problems using params with
>> scripts.
>>
>> My example (doing nothing real, just example with two commands):
>>
>>  with workQuery do
>>    begin
>>      Close;
>>      SQL.Clear;
>>      SQL.Add('DROP TABLE IF EXISTS Memory\Temp;'        );
>>
>>      SQL.Add('SELECT'                                  );
>>      SQL.Add('  *'                                     );
>>      SQL.Add('FROM'                                    );
>>      SQL.Add('  NewTable N'                            );
>>      SQL.Add('WHERE'                                   );
>>      SQL.Add('    ( N.Placano = '+QuotedStr('J')+' )'  );
>>      SQL.Add('        and'                             );
>>      SQL.Add('    ( N.Storno  = '+QuotedStr('N')+' )'  );
>>      SQL.Add('        and'                             );
>>      SQL.Add('    ( N.Datum_Od BETWEEN AND :do )'  );
>>      SQL.Add('        and'                             );
>>      SQL.Add('    ( N.Datum_Do BETWEEN AND :do )'  );
>>      SQL.Add(';'                                       );
>>      Prepare;
>>      ParamByName('od').AsDate:=now-100;
>>      ParamByName('do').AsDate:=now+100;
>>      ExecSQL;
>>    end;  //with
>>
>> Without first line (DROP TABLE...) it works OK, with this line parameter
>> 'od' is
>> not found.
>>
>> How can I use ParamByName in scripts with multiple commands?
>> Must I call ExecSQL after each command?
>> Must I clear scripts after each ExecSQL?
>>
>> I am using DBisam v4
>>
>> TIA for any help.
>>
>> Regards, Joze
>
>

Robert,

<< let me make sure I understand

if the sql is
update table set field = :f;
update table2 set field2 = :f2;

Then my code would be something like >>

Yes, that would work.  You can also just query the parameters to find out
which one's are being asked for via the Params.FindParam method:

http://www.elevatesoft.com/manual?action=mancompmethod&id=dbisam4&product=d&version=7&comp=TDBISAMParams&method=FindParam

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:083BC87B-58A6-4449-A725-C660D9B513CE@news.elevatesoft.com...
>
> Yes, that would work.  You can also just query the parameters to find out
> which one's are being asked for via the Params.FindParam method:
>

I assume this quering of the parameters using FindParam has to be done as
each step starts - in the OnGatParams. Correct?

This is the first I hear of this method. Much better than what I was doing,
since it will work even if you change the SQL.

Robert

Robert,

<< I assume this quering of the parameters using FindParam has to be done as
each step starts - in the OnGatParams. Correct? >>

Yes, because the params will change as each statement is executed.

<< This is the first I hear of this method. Much better than what I was
doing, since it will work even if you change the SQL. >>

Correct.  If the parameter no longer exists, then it will simply not be
populated.

--
Tim Young
Elevate Software
www.elevatesoft.com