RE: [DUG]: Query Parameters

[Stacey Verner]

this would work well, but i think that it would be slower than the existing solution. That would require many statements to be sent to the database.   The existing code which inserts using a sub select is fine, and i expect that it would be much faster that that what you suggest here. I just wanted to speed it up a bit by preparing the query once, but in this case but it looks like that won't happen.   Thanks   Stacey -----Original Message----- From: Leigh Wanstead [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 18 October 2000 9:51 a.m. To: Multiple recipients of list delphi Subject: Re: [DUG]: Query Parameters Dear Stacey,   I do not think what you do is correct. Normal use for this just 1)prepare Insert query 2)put SELECT statement in TQuery, use a while loop   SQL for insert like this(CONST_SQL_INSERT):   insert into fw_programme_cell(cell_id  , programme_hdr_id  , treat_length_id  , fw_year  , reason_note  , added_on  , added_by  , chgd_on) VALUES(:cell_id  , :programme_hdr_id  , :treat_length_id  , :fw_year  , :reason_note  , :added_on  , :added_by  , :chgd_on)   SQL for SELECT like this(CONST_SQL_SELECT) select p_new_cell_id  , p_to_id  , treat_length_id  , fw_year  , reason_note  , today  , user  , "" from z_fw_programme_cel where cell_id = :p_cell_id   DELPHI code qryInsert.SQL.Text := CONST_SQL_INSERT; qrySelect.SQL.Text := CONST_SQL_SELECT; qrySelect.ParamByName('p_cell_id').AsInteger := XXX; qrySelect.Open;   qryInsert.Prepare; while not qrySelect.Eof do begin     qryInsert.ParamByName('cell_id').AsInteger := XXX;     qryInsert.ParamByName('XXXXX').....;     qryInsert.ParamByName('XXXXX').....;     qryInsert.ParamByName('XXXXX').....;     qryInsert.ParamByName('XXXXX').....;     qryInsert.ExecSQL;     qrySelect.Next; end;   qrySelect.Close;   Hope this help.   Best Regards Leigh Wanstead   ----- Original Message ----- From: Stacey Verner To: Multiple recipients of list delphi Sent: Wednesday, October 18, 2000 9:06 AM Subject: [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.   eg.           Clear;         Add(' insert into fw_programme_cell (');         Add('     cell_id,programme_hdr_id,treat_length_id,');         Add('     fw_year,reason_note,');         Add('     added_on, added_by, chgd_on)');         Add('   select');         Add('     ' + IntToStr(LNewCellId) + ',' + IntToStr(PToID) + ',treat_length_id,');         Add('     fw_year,reason_note,');         Add('     today, USER, ""');         Add('   from z_fw_programme_cel');         Add('   where cell_id = ' + LQuery2.FieldByName('cell_id').AsString);         ExecSQL;   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.   insert into fw_programme_cell(cell_id  , programme_hdr_id  , treat_length_id  , fw_year  , reason_note  , added_on  , added_by  , chgd_on) select :p_new_cell_id  , :p_to_id  , treat_length_id  , fw_year  , reason_note  , today  , user  , "" from z_fw_programme_cel where cell_id = :p_cell_id     I intend this to do something like:   select 1, 13, treat_length_id ...   which is fine in informix SQL.    Thanks   Stacey   Stacey Verner             Ph:   +64-9-4154790 Software Developer        Fax:  +64-9-4154791                           DDI:  +64-9-4154797 CJN Technologies Ltd.     Email: [EMAIL PROTECTED] PO Box 302-278, North Harbour, Auckland, New Zealand 12 Piermark Drive, North Harbour Estate, Auckland, NZ Visit our website at http://www.cjntech.co.nz/