DynamicWhere Issue

Data Abstract
1

This code was generated
WHERE
(((“VOID” = :DAPARAM_1) AND (“Member_Nbr” = :Member_Nbr)) AND ((“YMDEFF” <= :As_Of_Date) AND (“YMDEND” >= :As_Of_Date)))

From this DynamicWhere
WhereExpression dynamicWhere;
dynamicWhere = new BinaryExpression(
new BinaryExpression(
new BinaryExpression(
new FieldExpression(“VOID”),
new ConstantExpression(" "),
BinaryOperator.Equal),
new BinaryExpression(
new FieldExpression(“Member_Nbr”),
new ParameterExpression(“Member_Nbr”, DataType.WideString, 12),
BinaryOperator.Equal),
BinaryOperator.And),
new BinaryExpression(
new BinaryExpression(
new FieldExpression(“YMDEFF”),
new ParameterExpression(“As_Of_Date”, DataType.LargeInt, 0),
BinaryOperator.LessOrEqual),
new BinaryExpression(
new FieldExpression(“YMDEND”),
new ParameterExpression(“As_Of_Date”, DataType.LargeInt, 0),
BinaryOperator.GreaterOrEqual),
BinaryOperator.And),
BinaryOperator.And);
It errors out with an Oracle error.

Thanks,
Monte

2

Hello

Could you say which exactly error did you get and which exactly Data Abstract version is used?

Thanks in advance

3

Hey Anton…
Error was {“ORA-01008: not all variables bound”}
and I am using the .Net version of DA I think the May build

Why would it be generating DAPARAM_1 for a constant? And it looks like it is putting quotes around Aliased names and
even sql developer throws an error on that. Is there a way not to generate the quotes on Aliased columns?
Thanks,
Monte

4

Anton,

Also a method like NewBinaryExpressionList would be handy in the .Net version like the one in the Delphi WhereBuilder…

Thanks,
Monte

5

Hello

Could you double-check your DataAbstract.daConfig file and say which parameter handling type is set for driver profiles ODP10g.NET and ODP9i.NET (somewhere around lines 410-436 in the file)?

Thanks in advance

6 
  <ParameterHandling Type="ByName"
                 SQLSymbol=":"
                 NameSymbol="" />

Thanks,
Monte

7

Hello

Please update to the May 2015 release. It should contain fix for this issue.

Regards

8

Anton,

I am running May 2015 8.3.91.1167

Thanks,
Monte

9

Hello

Hmm, this is odd. Maybe your project includes the DataAbstract.daConfig file from olderversion of Data Abstract? We have changed parameter handling type for both ODP10g.NET and ODP9i.NET type to Sequential while your version of file still contains ByName setting (this can cause issues similar to the ones you have).

10

So I should set it to sequential? I’ll give that a try… thanks,
Monte

11

Anton,

I set the ParameterHandling to Sequential and I got the same result. “ORA-01008: not all variables bound”

Here’s the generated DynamicWhere:
(((YMDEFF <= :As_Of_Date) and (YMDEND >= :As_Of_Date)) and (MEMBER_NBR = :Member_Nbr))

Here’s the generated SQL…
SELECT c.MEMBER_NBR as “Member_Nbr”\r\n , c.REGION as “Region_Code”\r\n , c.VOID\r\n , c.YMDEFF\r\n , c.YMDEND\r\n , c.BUSINESS_UNIT as “Business_Unit”\r\n , c.CARRIER as “Carrier”\r\n , c.PROG_NBR as “Program”\r\n , c.GROUP_NBR as “Group”\r\n , c.DIVISION_NBR as “Division”\r\n , h.DESCRIPTION as “Group_Desc”\r\n , c.BENEFIT_PKG\r\n , g.NAME_X as “Region_Desc”\r\nFROM MEMBER_S c \r\n LEFT OUTER JOIN SUPER_T h ON h.SUPER_KEY = ‘REG’ || c.REGION\r\n LEFT OUTER JOIN GRP_M g ON c.GROUP_NBR = g.GROUP_NBR\r\nWHERE\r\n\tC.VOID = ’ ’ AND\r\n(((“YMDEFF” <= :As_Of_Date) AND (“YMDEND” >= :As_Of_Date)) AND (“Member_Nbr” = :Member_Nbr))

That where rendering is incorrect based on the DynamicWhere… It should be MEMBER_NBR in quotes…

Thanks,
Monte

12

Could you create a testcase for this issue? Client and server apps and a table definition SQL to repro this locally? Send it to support@ and we’ll find out what causes the issue.

Thanks in advance

13

Not sure I can do that, but as I’ve investigated this more the following appears to be the underlying issue…

WHERE (YMDEFF <= :AS_OF_DATE) AND (YMDEND >= :AS_OF_DATE) causes the ORA-01008 whether or not I used ByName or Sequential ParameterHandling. To solve this I generated an extra AS_OF_DATE parameter and associated it
with YMDEND in the 2nd binary expression (YMDEND >= :AS_OF_DATE1). It works but it’s definitely more overhead. Oracle supports use of the one parameter by the way. Can we get this to support multiple references to a single parameter please?

Thanks
Monte H

14

Thanks, logged as bugs://72573

15

Hello

I’ve logged this as a bug for further investigation. Could you please show the initial DynamicWhere expression that was turned into this SQL statement?

Thanks in advance

16

Anton…

rendered: {(((YMDEFF = :AS_OF_DATE) and (YMDEND = :AS_OF_DATE)) and (MEMBER_NBR = :MEMBER_NBR))}

original: (YMDEFF=:AS_OF_DATE) AND (YMDEND=:AS_OF_DATE) AND (MEMBER_NBR=:MEMBER_NBR)

17

Thanks. The bug is logged. Seems the sequential parameter handling procedure assumes that all parameters have distinct names (which is not correct ofc).

18

bugs://72573 got closed with status fixed.