SDAC optimization issue (ISNULL)

[GC]

Although this is not a bug it might cause some problems.

The following constuction won't work well in SDAC if you pass NULL to the parameter "param" (whicxh is varchar(xx)):

isnull(:Param,'Something') = 'Something'

if you pass NULL to param SDAC will optimize the SQL and pass it to SQL server as varchar(1) and ISNULL(Varchar(1),'Something') will return VARCHAR(1) which in this case will be 'S'. 'S' = 'Something' will then result to false.
The solution is to simply CAST :Param to VARCHAR(xx) where xx is your desired length and this will solve the problem.

This is not a bug report, just thought that it would be helpful.

[Ikar]

We need to consider properly all possible side effects from this optimization. Probably, we'll do it later.

[Guest]

You can also try to set null value to a parameter of type GUID (uniqueidentifier)... sometimes you can get the statement generated with varchar(1) parameter which doesn't work.

[Ikar]

We couldn't reproduce the problem.
Please send us complete small sample to demonstrate it and include script to create server objects.

Please supply us following information

• Exact version of Delphi or C++ Builder
• Exact version of SDAC. You can see it in About sheet of TMSConnection Editor
• Exact version of Microsoft SQL Server and OLE DB provider that you use. You can see it in Info sheet of TMSConnection Editor

You can also try to set null value to a parameter of type GUID (uniqueidentifier)... sometimes you can get the statement generated with varchar(1) parameter which doesn't work.

[zedmartins]

About this problem with statements like:

ISNULL(:PARAM,'something')

Is there anything I can do besides rewriting my SQL to add a CONVERT function?

Thanks,

Z

[Antaeus]

Another way is to change parameter type from ptInput to ptInputOutput, but this way may affect performance of your application. Therefore we recommend using the CONVERT function.

[zedmartins]

Thanks,

I found out that changing the Options,AutoPrepare to True makes the error go away,

Regards,

Zed