specifying the same parameter multiple times in the same query

specifying the same parameter multiple times in the same query

Postby costa » Sat 29 Jun 2013 01:00

If I use the following query, two parameters with the same name, p1 are created.

Code: Select all
select :p1 from sys.dual
union all
select :p1 from sys.dual


devart ends up running this query:

Code: Select all
select :1 from sys.dual
union all
select :2 from sys.dual


This creates a problem for me because I need only parameter. I expected that my query was translated to this:

Code: Select all
select :1 from sys.dual
union all
select :1 from sys.dual


I also need to specify the parameters by name.

Thanks
costa
 
Posts: 30
Joined: Thu 27 Aug 2009 20:30

Re: specifying the same parameter multiple times in the same query

Postby AlexP » Mon 01 Jul 2013 09:12

Hello,

Such work with parameters is due to the implementation of parameter parsing in the DB.pas module: found parameters are replaced with the '?' symbol in the TParams.ParseSQL method; therefore, for correct work with them, we assign serial numbers to them. However, this doesn't affect calls to parameters: you can call a parameter by both its index or name.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: specifying the same parameter multiple times in the same query

Postby costa » Tue 02 Jul 2013 02:23

If I specify the same parameter multiple times but I set it only once (by name) there will be an error saying that not all the parameters have been bound.

I personally think this is a flaky behavior. Oracle oci supports binding parameters by name.

Is there a workaround to avoid this error and use the same parameter multiple times? Using parameters by position is a pain.

Thanks
costa
 
Posts: 30
Joined: Thu 27 Aug 2009 20:30

Re: specifying the same parameter multiple times in the same query

Postby AlexP » Tue 02 Jul 2013 09:09

Hello,

We cannot reproduce the problem. Please specify your driver and IDE versions.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: specifying the same parameter multiple times in the same query

Postby stratdaz » Tue 16 Jul 2013 06:02

Think I'm having the same problem.

Using dotConnect for Oracle 7.7.226.0 in direct mode.

This doesn't work throwing "not all variables bound" (note the :subsystem_id variable bound twice) ...

SELECT master_dim_id
FROM PAN_HIERARCHY_V
WHERE subsystem_id = :subsystem_id
AND cube_id = :cube_id
AND section_fg = 'Y'
AND PAN_USER_DIM_ACCESS_F(:subsystem_id, dim_id, :user_id) = 1
GROUP BY master_dim_id
HAVING COUNT(*) > 1
ORDER BY 1

whereas this (no variable duplication with :1 and :3 set to the same value) is fine ...

SELECT master_dim_id
FROM PAN_HIERARCHY_V
WHERE subsystem_id = :1
AND cube_id = :2
AND section_fg = 'Y'
AND PAN_USER_DIM_ACCESS_F(:3, dim_id, :4) = 1
GROUP BY master_dim_id
HAVING COUNT(*) > 1
ORDER BY 1

I've just committed to using DIRECT mode and I have a stack of old data access classes that are now breaking.

Any ideas Devart?

Thanks
stratdaz
 
Posts: 23
Joined: Tue 04 May 2010 00:46

Re: specifying the same parameter multiple times in the same query

Postby AlexP » Tue 16 Jul 2013 11:17

Hello,

Please ask/move your question to the dedicated forum of dotConnect for Oracle
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: specifying the same parameter multiple times in the same query

Postby costa » Wed 22 Jan 2014 00:32

Hi:

Sorry for getting back so late (didn't work with Delphi for a while). It seems that whatever you guys did this has been fixed in version 6.3.4. It works nicely. I can use now something like this:

Code: Select all
sqlquery1.ParamByName('p1').Value := 102;


and there is no error and the query works fine.

Thank you!
costa
 
Posts: 30
Joined: Thu 27 Aug 2009 20:30

Re: specifying the same parameter multiple times in the same query

Postby AlexP » Wed 22 Jan 2014 10:45

As I wrote you before, generating the resulting SQL query is performed in the TParams.ParseSQL method of the DB.pas module, and each parameter (even with the same name) is replaced by its index there. However, in our driver, you can call a parameter by both name and index - and we will substitute parameter values correctly.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35


Return to dbExpress driver for Oracle