I think that I failed to mention that this query gets executed thousands of
times, and without using paremeters the query is prepared each time it is
executed, when it really only needs to be prepared once.
Thanks
Stacey
> -----Original Message-----
> From: Aaron Scott-Boddendijk
> [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, 18 October 2000 9:58 a.m.
> To: Multiple recipients of list delphi
> Subject: Re: [DUG]: Query Parameters
>
>
> > I am trying to speed up a process. Part of this process
> contains a loop
> > which inserts multiple records into a table. The existing
> code changes the
> > sql of the query in order to setup parameters.
>
> Hmm... I'm not sure why the parameter problems but in general
> I'd be looking
> at storing the queries as resourcestrings as in
>
> resourcestring
> QRY_InsertSomething =
> 'insert into fw_programme_cell'
>
> +'(cell_id,programme_hdr_id,treat_length_id,fw_year,reason_note,'
> +'added_on, added_by, chgd_on)'
> +'select
> %d,%d,treat_length_id,fw_year,reason_note,today, USER,
> ""'
> +'from z_fw_programme_cel'
> +'where cell_id = "%s"';
>
> And in the usage doing
>
> SQL.Text := Format(QRY_InsertSomething,[1,13,'Spam']);
> ExecSQL;
>
> If it's got parameters in it then it's still just
>
> resourcestring
> QRY_InsertSomething =
> 'insert into fw_programme_cell'
>
> +'(cell_id,programme_hdr_id,treat_length_id,fw_year,reason_note,'
> +'added_on, added_by, chgd_on)'
> +'select
> :Param1,:Param2,treat_length_id,fw_year,reason_note,today, USER, ""'
> +'from z_fw_programme_cel'
> +'where cell_id = :Param3';
>
> SQL.Text := QRY_InsertSomething;
> Prepare;
> while not LQuery2.EOF do begin
> // Set up parameters;
> ParamByName['Param1'].AsInteger := 1;
> ParamByName['Param2'].AsInteger := 13;
> ParamByName['Param3'].AsString :=
> LQuery2.FieldByName('cell_id').AsString;
> ExecSQL;
> LQuery2.Next;
> end;
> Unprepare;
>
> (Or something like that)
>
> > I want to do this is a parameterised query, but I am not
> sure of how to go
> > about it. I have tried the following with no luck. It
> doesn't like the
> > parameters for the select fields. I have tried aliasing the
> parameters.
>
> What's the error...
>
> One thing you may have to do is set the parameter type... Since it's a
> literal in the selection clause not bound to a table element there may
> be some issue with type (although the .asType should set
> this I would have thought).
>
> --
> Aaron Scott-Boddendijk
> INTAZ Limited
> +64 7 838 3371 Voice
> +64 7 838 3372 Fax
>
>
> --------------------------------------------------------------
> -------------
> New Zealand Delphi Users group - Delphi List -
> [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
> To UnSub, send email to: [EMAIL PROTECTED]
> with body of "unsubscribe delphi"
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
