How do I use TADOQuery.Parameters with integer parameter types that have to be put in two or more places in a query?

Question

I have a complex query that contains more than one place where the same primary key value must be substituted. It looks like this:

select  Foo.Id,
        Foo.BearBaitId,
        Foo.LinkType,
        Foo.BugId,
        Foo.GooNum,
        Foo.WorkOrderId,
        (case when Goo.ZenID is null or Goo.ZenID=0 then
            IsNull(dbo.EmptyToNull(Bar.FanName),dbo.EmptyToNull(Bar.BazName))+' '+Bar.Strength else
            '@'+BarZen.Description end) as Description,
        Foo.Init,
        Foo.DateCreated,
        Foo.DateChanged,
        Bug.LastName,
        Bug.FirstName,
        Goo.BarID,
        (case when Goo.ZenID is null or Goo.ZenID=0 then
             IsNull(dbo.EmptyToNull(Bar.BazName),dbo.EmptyToNull(Bar.FanName))+' '+Bar.Strength else
             '@'+BarZen.Description end) as BazName,
        GooTracking.Status as GooTrackingStatus
  from
    Foo
  inner join Bug on (Foo.BugId=Bug.Id)
  inner join Goo on (Foo.GooNum=Goo.GooNum)
  left join Bar on (Bar.Id=Goo.BarID)
  left join BarZen on (Goo.ZenID=BarZen.ID)
  inner join  GooTracking on(Goo.GooNum=GooTracking.GooNum )
 where (BearBaitId = :aBaitid) 
UNION
 select Foo.Id,
        Foo.BearBaitId,
        Foo.LinkType,
        Foo.BugId,
        Foo.GooNum,
        Foo.WorkOrderId,
        Foo.Description,
        Foo.Init,
        Foo.DateCreated,
        Foo.DateChanged,
        Bug.LastName,
        Bug.FirstName,
        0,
        NULL,
        0
 from Foo
 inner join Bug on (Foo.BugId=Bug.Id)
  where (LinkType=0)  and (BearBaitId= :aBaitid ) 
order by BearBaitId,LinkType desc, GooNum

When I try to use an integer parameter on this non-trivial query, it seems impossible to me. I get this error:

Error

Incorrect syntax near ':'.

The query works fine if I take out the :aBaitid and substitute a literal 1.

Is there something else I can do to this query above? When I test with simple tests like this:

select * from foo where id = :anid

These simple cases work fine. The component is TADOQuery, and it works fine until you add any :parameters to the SQL string.

Update: when I use the following code at runtime, the parameter substitutions are actually done (some glitch in the ADO components is worked around) and a different error surfaces:

adoFooContentQuery.Parameters.FindParam('aBaitId').Value := 1;
adoFooContentQuery.Active := true;

Now the error changes to:

Incorrect syntax near the keyword 'inner''.

Note again, that this error goes away if I simply stop using the parameter substitution feature.

Update2: The accepted answer suggests I have to find two different copies of the parameter with the same name, which bothered me so I reworked the query like this:

 DECLARE @aVar int;
 SET @aVar = :aBaitid;
 SELECT ....(long query here)

Then I used @aVar throughout the script where needed, to avoid the repeated use of :aBaitId. (If the number of times the parameter value is used changes, I don't want to have to find all parameters matching a name, and replace them).

I suppose a helper-function like this would be fine too: SetAllParamsNamed(aQuery:TAdoQuery; aName:String;aValue:Variant)

Yes you can't have the same :parameter mentioned more than once when using ADO. I put `DECLARE @Param INT; SET @Param = :Param;` at the start of my queries when a parameter is used more than once. Then you can refer to @Param instead of :Param in the query. — Reversed Engineer, May 28 '19 at 14:12

score 2 · Accepted Answer · answered Sep 30 '11 at 17:43

2

FindParam only finds one parameter, while you have two with the same name. Delphi dataset adds each parameter as a separate one to its collection of parameters.

It should work if you loop through all parameters, check if the name matches, and set the value of each one that matches, although I normally choose to give each same parameter a follow-up number to distingish between them.

answered Sep 30 '11 at 17:43

GolezTrol

114,394
18
182
210

That is correct. I thought it would substitute more than once, and it doesn't. I made an incorrect assumption. – Warren P Sep 30 '11 at 18:19
1

Interesting, this must be specific to ADO implementation because with other datasets, at least with FIBPlus/Firebird for sure, it works as expected (the parameter can be referenced multiple times in the SQL but there's only one TParam for which you need to provide the value). – Ondrej Kelle Oct 01 '11 at 09:53
Here we should separate Delphi features and DBMS API's. (1) Delphi TParam supports a mix of positional and named binds through FParamRef, which links the same named parameters. Delphi TParameter (ADO) does not support that. (2) Historically, BDE and ODBC and many other DBMS API are supporting only positional binds. Some other DBMS API like Oracle OCI, SQLite API are supporting true named binds. And some as positional as named binds. – da-soft Oct 01 '11 at 10:38

How do I use TADOQuery.Parameters with integer parameter types that have to be put in two or more places in a query?

1 Answers1