Active Reports 14 - parameter binding bug

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

  • Posted 12 August 2020, 10:35 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



    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.




    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.
  • Replied 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
    :SUSPENDC is null
    and :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

  • Replied 12 August 2020, 12:12 pm EST

    so does this

    select * 
    FROM PHILLY.PARKCITEHIST PC
    WHERE
    :SUSPENDC is null
    and :FROMDATE 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

  • Replied 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)
  • Replied 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
  • Replied 13 August 2020, 1:56 pm 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:



    Set up a few parameters in the report



    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

  • Replied 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
  • Replied 21 August 2020, 1:10 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:


    The dataset parameters were in this order before:



    Then changed it to this order after:



    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.


    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.
  • Replied 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
  • Replied 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?)
  • Replied 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
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels