RE: [DUG]: Query Parameters

[Stacey Verner]

Sounds like a good plan, except that we have found stored procedures to be very slow in Informix because they appear to be interpreted, line by line.   I will give this a go anyway.   Thanks   Stacey -----Original Message----- From: Leigh Wanstead [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 18 October 2000 10:58 a.m. To: Multiple recipients of list delphi Subject: Re: [DUG]: Query Parameters I do not have any experience with Informix, but in MS SQL or Interbase or Oracle, I will do it this way.   Write a storeprocedure at database end.   Depends on your select statement return single or multiple record, if is single put value in local variable and insert it, if multiple use cursor do it.   Because all work is done at server end, so no traffic will be generated on network which I can imagine is far more simple and efficient, especially for a query will run thousand times.   Best Regards Leigh Wanstead ----- Original Message ----- From: Stacey Verner To: Multiple recipients of list delphi Sent: Wednesday, October 18, 2000 10:14 AM Subject: RE: [DUG]: Query Parameters 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/