specifying the same parameter multiple times in the same query

[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

[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.

[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

[AlexP]

Hello,

We cannot reproduce the problem. Please specify your driver and IDE versions.

[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

[AlexP]

Hello,

Please ask/move your question to the dedicated forum of dotConnect for Oracle

[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!

[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.