Active Reports 14 - parameter binding bug

Active Reports 14 - parameter binding bug

Posted by: mschmitz on 12 August 2020, 10:35 am EST

• 

  mschmitz

  • Post Options:
  • Link
    Copy

  Posted 12 August 2020, 10:35 am EST - Updated 30 September 2022, 7:51 am EST

  Hello these are the versions I’m running

  Active Reports 14.1.19970.0
  
  Microsoft Visual Studio Professional 2013 Version 12.0.40629.00 Update 5
  
  Microsoft .NET Framework Version 4.8.03761

  I keep getting an error when trying to run page reports in the designer and in the viewer which says “The query appears to be invalid Additional Information: ‘ORA-081008: not all variables bound’”. I noticed it happens on some reports and not others.

  After troubleshooting it all day I’ve been able to replicate the bug in a simplified query where I set up 3 parameters: SUSPENDC, FROMDATE, and TODATE.

  When I set up this query I get the error when the designer is validating
  
  select *
  
  FROM PHILLY.PARKCITEHIST PC
  
  WHERE :SUSPENDC is null
  
  and :FROMDATE is null
  
  and :FROMDATE is null
  
  and :TODATE is null
  
  and :TODATE is null

  when I remove teh SUPENDC from the where clause then the query validates without issue

  select *
  
  FROM PHILLY.PARKCITEHIST PC
  
  WHERE :FROMDATE is null
  
  and :FROMDATE is null
  
  and :TODATE is null
  
  and :TODATE is null

  when I add the SUSPENDC back and this time remove the FROMDATE then it runs without issue

  select *
  
  FROM PHILLY.PARKCITEHIST PC
  
  WHERE :SUSPENDC is null
  
  and :FROMDATE is null
  
  and :TODATE is null
  
  and :TODATE is null

  when I put the FROMDATE back in and this time remove a TODATE then it runs without issue.

  select *
  
  FROM PHILLY.PARKCITEHIST PC
  
  WHERE :SUSPENDC is null
  
  and :FROMDATE is null
  
  and :FROMDATE is null
  
  and :TODATE is null

  I can have one of each and it validates without issue

  select *
  
  FROM PHILLY.PARKCITEHIST PC
  
  WHERE :SUSPENDC is null
  
  and :FROMDATE is null
  
  and :TODATE is null

  

  1408×1464 392 KB

  It’s pretty replaceable on my end but I don’t know why the pattern is the way it is which triggers that error.

  I have the report parameters set up and tried to modify them any way I could to get around the errors but didn’t have any luck.
  
  

  

  834×445 63.2 KB

  

  1021×510 110 KB

  I looked at the rdlx files in a text editor too to see if I see anything out of the ordinary but didn’t see anything.

  Both the designer and viewer app I have set up used to run on oracle client but due to a breaking change that occurred between versions 13 and 14 I changed it to work with oracle managed data access the same way shown in the same report located in Samples14-master\Advanced\PageAndRDL\OracleDataProvider\C# . Both the designer and viewer were originally created with active reports 11 and upgraded to active reports 14.

• 

  mschmitz

  • Post Options:
  • Link
    Copy

  Posted 12 August 2020, 12:12 pm EST

  I’m thinking the pattern is that if a parameter is used more than once in a query, there can’t be a parameter used twice consequentially in first parameter and second parameter of the query.

  So this validates in the designer for example:

  select * 
  FROM PHILLY.PARKCITEHIST PC 
  WHERE 
  :SUSPENDC is null
  and :FROMDATE is null 
  and :TODATE is null  
  and :FROMDATE is not null
  and :TODATE is not null  
  and :TODATE is not null 
  and :SUSPENDC is null
  and :TODATE is null 
  and :TODATE is null   
  and :FROMDATE is not null 
  and :FROMDATE is not null
  and :FROMDATE is not null
  

  this example also gives the error

  select * 
  FROM PHILLY.PARKCITEHIST PC 
  WHERE 
  [b]:SUSPENDC is null
  and :SUSPENDC is null[/b]
  and :FROMDATE is null 
  and :TODATE is null  
  and :FROMDATE is not null
  and :TODATE is not null  
  and :TODATE is not null 
  and :SUSPENDC is null
  and :TODATE is null 
  and :TODATE is null   
  and :FROMDATE is not null 
  and :FROMDATE is not null
  and :FROMDATE is not null
  

• 

  mschmitz

  • Post Options:
  • Link
    Copy

  Posted 12 August 2020, 12:12 pm EST

  so does this

  select * 
  FROM PHILLY.PARKCITEHIST PC 
  WHERE 
  :SUSPENDC is null
  [b]and :FROMDATE is null 
  and :FROMDATE is null[/b]
  and :TODATE is null  
  and :FROMDATE is not null
  and :TODATE is not null  
  and :TODATE is not null 
  and :SUSPENDC is null
  and :TODATE is null 
  and :TODATE is null   
  and :FROMDATE is not null 
  and :FROMDATE is not null
  and :FROMDATE is not null
  

• 

  mschmitz

  • Post Options:
  • Link
    Copy

  Posted 12 August 2020, 12:13 pm EST

  but as long as the first 2 parameters aren’t referenced consecutively before another parameter is then it can repeat as much as needed without errors

  This one validates because SUSPENDC and FROMDATE aren’t used twice in the beginning.

  select * 
  FROM PHILLY.PARKCITEHIST PC 
  WHERE 
  :SUSPENDC is null
  and :FROMDATE is null
  and :TODATE is null  
  and :FROMDATE is not null
  and :TODATE is not null  
  and :TODATE is not null 
  and :SUSPENDC is null
  and :TODATE is null 
  and :TODATE is null   
  and :FROMDATE is not null 
  and :FROMDATE is not null
  and :FROMDATE is not null
  and :SUSPENDC is null
  and :SUSPENDC is null
  and :SUSPENDC is null
  and :TODATE is null 
  and :TODATE is null   
  and :FROMDATE is not null 
  and :FROMDATE is not null
  and :FROMDATE is not null
  

  (had to break this up into multiple posts because it kept getting detected as spam)

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 13 August 2020, 3:34 am EST

  Hello,

  Have you tried to directly bind the DataTable with the same query using the “Oracle.DataAccess.dll” in a simple console sample? It is helpful for us to differentiate the issue. If the issue does not occur with a simple sample. Could you please provide the sample db so that I can reproduce the issue at my end.

  Thanks,
  
  Mohit

• 

  mschmitz

  • Post Options:
  • Link
    Copy

  Posted 13 August 2020, 1:56 pm EST - Updated 30 September 2022, 7:51 am EST

  Hello could you elaborate more on what you mean by simple console sample? It would be nice to know for the future. I am able to duplicate the issue with the same project Samples14-master\Advanced\PageAndRDL\OracleDataProvider\C#\OracleDataProvider.sln

  I add a data source oracle managed data access:

  

  811×802 70.4 KB

  Set up a few parameters in the report

  

  891×557 107 KB

  A couple parameters in the dataset

  

  I used this query to replicate the error.

  select sysdate
  
  from dual
  
  where ‘TEST’ = :someparameter1 and ‘TEST’ = :someparameter1
  
  and ‘TEST’ = :someparameter2

  selecting from dual will work in any oracle database so no need for a pre-defined set of data to test on.

  

  But make a minor change then this does validate

  select sysdate
  
  from dual
  
  where ‘TEST’ = :someparameter2 and ‘TEST’ = :someparameter1
  
  and ‘TEST’ = :someparameter2

  

  697×463 46.5 KB

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 19 August 2020, 5:41 pm EST

  Hello,

  Thanks for the information.

  I am able to reproduce the issue at my end. I have escalated the issue to the development team (AR-24963)and will inform you once I get any information from them.

  Thanks,
  
  Mohit

• 

  mschmitz

  • Post Options:
  • Link
    Copy

  Posted 21 August 2020, 1:10 am EST - Updated 30 September 2022, 7:51 am EST

  There was another buggy issue possibly related that I’ve noticed. Sometimes on reports the report will behave as if there are no results when there should be. In the past I’ve been able to fix it by deleting parameters and re-adding them.

  The last time I fixed a report I compared the rdlx files to help figure out what was different between the working version and non-working version of the report. The only change was the order that the dataset parameters appear. Apparently they needed to be in order of appearance in the query.

  This was the only difference in the file between when results were returned and not returned:
  
  

  

  1524×559 186 KB

  The dataset parameters were in this order before:

  

  964×397 54.1 KB

  Then changed it to this order after:

  

  1023×369 76.2 KB

  It was a report where I needed to move a copy of the FROMDATE and TODATE parameters to the beginning of a query and gave them a meaningless purposes there to get around the other buggy issue mentioned in the beginning of this thread.

  Added the yellow highlighted changes to the query to work around that binding error. I have it checking if those parameters are not null even though I know they won’t be but it was a work around for that binding issue.
  
  

  

  1027×577 84.8 KB

  But it also required me to have the parameters listed in this order for the report to return results: FROMDATA, TODATE, and SUSPENDCODEPARAM. Rather than SUSPENDCODEPARAM, FROMDATE, TODATE. I could try to replicate this issue with the sample project as well if needed.

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 25 August 2020, 1:12 am EST

  Hello,

  Thanks for the detailed explanation. I am able to reproduce the issue at my end. I have escalated the issue to the development team (AR-24990) and will inform you once I get any information from them.

  Thanks,
  
  Mohit

• 

  mschmitz

  • Post Options:
  • Link
    Copy

  Posted 22 December 2020, 6:14 am EST

  Hello I was wondering if there was any traction on this (maybe fixed in the active reports 15?)

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 22 December 2020, 2:24 pm EST

  Hello,

  This issue is fixed in the latest version of the AR14. You can download the build from the following link:
  
  http://cdn.grapecity.com/ActiveReports/AR14/Hotfixes/ActiveReports-v14.2.20743.msi

  Also, use the following updated sample:
  
  https://github.com/activereports/Samples14/tree/master/Advanced/PageAndRDL/OracleDataProvider

  Thanks,
  
  Mohit