?
Solved

Activation of parameters in an ADO-Query

Posted on 2005-03-15
8
Medium Priority
?
280 Views
Last Modified: 2010-04-05
Hi,

what is necessary to activate a new set of parameters in an ado-query ?

In detail:

I prepare the sql-statements in an ado-query only at the beginning
  sql.clear;
  sql.add('select ...  from ...   where  field1 = :p1  and  field2 = :p2' )

from then on, only the parameters are set and sql.open is called.
  parameters.parambyname('p1').value:= ...;
  parameters.parambyname('p2').value:= ...;
  sql.open;

But it looks as if these parameters never get active, always the same record seems to be selected.
(sometimes you are on direct way to get mad)

a)  Is this impression true ?
b) what is necessary to activate the new parameter set ?
    (To start with sql.clear every time and build it up completely new takes a lot of time)

K.-P.BEcker

 
0
Comment
Question by:KPBecker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 17

Accepted Solution

by:
mokule earned 750 total points
ID: 13549582
This should work i think

with ADOQuery1 do
  begin
  close;
  parameters.parambyname('p1').value:= ...;
  parameters.parambyname('p2').value:= ...;
  open;
  end;

mokule

0
 
LVL 12

Expert Comment

by:esoftbg
ID: 13549711
What about to call (Prepared := True) before (Active := True):

  with ADOQuery1 do
  begin
    Active := False;
    SQL.Text := 'select ...  from ...   where  field1 = :p1  and  field2 = :p2';
    Parameters.Parambyname('p1').Value:= 'zombi';
    Parameters.Parambyname('p2').Value:= 'vampire';
    Prepared := True;
    Active := True;
  end;
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 13549734
Excuse me about 'zombi' and 'vampire' Values, I have a big headache last 5 - 6 days, and I am almost mad ....
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:mokule
ID: 13549792
Well with Prepared You should be cautious.
I've just not long ago fall in AV with the code like Your above.
I must have done it this way
 with ADOQuery1 do
  begin
    Active := False;
    str := 'select ...  from ...   where  field1 = :p1  and  field2 = :p2';
    if SQL.Text <> str then
      begin
      SQL.Text := str
      Prepared := True;
      end;
    Parameters.Parambyname('p1').Value:= 'zombi';
    Parameters.Parambyname('p2').Value:= 'vampire';
    Active := True;
  end;

0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 13553734
Maybe you need to REfresh the Parameter-List :


with ADOQuery1 do
begin
      Close;
      Parameters.Refresh
      Parameters.ParamByName('p1').value:= ...;
      Parameters.ParamByName('p2').value:= ...;
      Open;
end;


Best regards,

The Mayor.
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 13555246
I just had the same porblem as KPBecker when working with Stored Procs.

I'm usinf Delphi 5  and SQL Server 2000.

I 'm pretty sure I found what was causing this strange behavior.
It is - and I'm not understand why - situated in the Stored Procedure Code itself (although in my case it is).

Here is the code of my Original Stored Proc :
CREATE PROCEDURE SP_GET_FIRST_MIXNR_FOR_LOGTABLE
@LogTableID int,
@MixNr varchar(20) output

AS

SELECT @MixNr = w.mix_nr
FROM Wagen w, Zetel z
WHERE w.Wagen_ID = z.Wagen_ID
      AND z.Zetel_ID = (SELECT MIN(zetel_id) FROM T_LOGDATA WHERE zetel_id > 1000000 and LOGTABLE_ID = @LogTableID)

if ( @MixNr IS NULL )
      set @MixNr = '0000000'

RETURN 0
GO




And now the code of the new one - which is always working in Delphi too :
CREATE PROCEDURE SP_GET_FIRST_MIXNR_FOR_LOGTABLE
@LogTableID int,
@MixNr varchar(20) output

AS

set @MixNr = (SELECT w.mix_nr
FROM Wagen w, Zetel z
WHERE w.Wagen_ID = z.Wagen_ID
      AND z.Zetel_ID = (SELECT MIN(zetel_id) FROM T_LOGDATA WHERE zetel_id > 1000000 and LOGTABLE_ID = @LogTableID) )

if ( @MixNr IS NULL )
      set @MixNr = '0000000'

RETURN 0
GO



As you can see, it was something in the assignment of my output-param @Mixnr.
When triggering the SP from within the MS Query Analyzer, it always works fine (both code).
But triggering the SP from Delphi-code returned always the same data when I once triggered the SP with a NULL-result (so the select-statement returned no records at all). In this case, the output-param was filled with the last value of a non-null select in the SP-Code.


Maybe that is also the cause in your case.
Please let met know if it has something to do with this in your case too.

Best regards,

The Mayor.
0
 

Author Comment

by:KPBecker
ID: 13653329
Hi,

still confused but on a higher level ...

I did some tests with your comments, here is what I found out.

The solution of  mokule (first comment) works, although I wanted to avoid the   qry.close / qry.open  which seems to be rather time-consuming.

esoftbg defines the sql-query again every time (That was what  I used: see question). The additional prepared:= true  gives no advantage.

Major:  The  'refresh'  (possibly without the need to close the query every time) seemed to be the best at the first glance. But whereever I placed this 'refresh' errors occured (with or without close).

Conclusion:  mokule gets the points

Thanks to all experts, of course !

KPBecker
0
 

Author Comment

by:KPBecker
ID: 13654488
Just an addition:

refresh  seems to destroy the list of parameters (After refresh, all parameters are unknown.)

Any explanation ?

KPBecker

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question