Page 1 of 1
specifying the same parameter multiple times in the same query
Posted: Sat 29 Jun 2013 01:00
by costa
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
Re: specifying the same parameter multiple times in the same query
Posted: Mon 01 Jul 2013 09:12
by AlexP
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.
Re: specifying the same parameter multiple times in the same query
Posted: Tue 02 Jul 2013 02:23
by costa
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
Re: specifying the same parameter multiple times in the same query
Posted: Tue 02 Jul 2013 09:09
by AlexP
Hello,
We cannot reproduce the problem. Please specify your driver and IDE versions.
Re: specifying the same parameter multiple times in the same query
Posted: Tue 16 Jul 2013 06:02
by stratdaz
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
Re: specifying the same parameter multiple times in the same query
Posted: Tue 16 Jul 2013 11:17
by AlexP
Hello,
Please ask/move your question to the dedicated forum of
dotConnect for Oracle
Re: specifying the same parameter multiple times in the same query
Posted: Wed 22 Jan 2014 00:32
by costa
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!
Re: specifying the same parameter multiple times in the same query
Posted: Wed 22 Jan 2014 10:45
by AlexP
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.