We are migrating to Pentaho 8.1 but we found out that none of our reports work. We are using Oracle 11g R2 and ojdbc6.jar.
We tested same queries without parameters and it works but one you write a $ it crashes with
Caused by: java.sql.SQLFeatureNotSupportedException: Función no soportada: checkValidIndex
at oracle.jdbc.driver.OracleParameterMetaData.checkValidIndex(OracleParameterMetaData.java:196)
at oracle.jdbc.driver.OracleParameterMetaData.getParameterClassName(OracleParameterMetaData.java:346)
at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrize(SimpleSQLReportDataFactory.java:356)
at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:277)
at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:179)
... 3 more
We found similar problem report by one guy [PRD-6006] Regression: Oracle SQL queries with parameters broken in PIR - Pentaho Platform Tracking
It really critical for us to make reports work. Can you suggest at least a possible work around?
Thank you
I have discovered the same issue. Seems to have been introduced by this PR
[PDB-2004] SQL List Prompts are converting parameters based on string to double precision numbers by Advent51 · Pull Req…
Although when using ojdbc6.jar I did not get unsupported feature checkValidIndex. It was getParameterClassName.
I got checkValidIndex when I tried it with ojdbc8.jar. It could be that I was using an old version of ojdbc6.jar.
I did find a not so great work around. If you use ojdbc8.jar it can work. However, you have to structure the query in certain way. I found this online that kind of shed some insight
The Oracle Driver
Basically, Your parameters have to be in WHERE. You need to put your parameters on the right side of any expression E.g.
FOO = ${bar} <- Yes
${bar} = FOO <- No
Oh, and if you have any comments in your query, they have to be after all the parameters.
Also, I had some MySQL reports that met similar fate. Thankfully, setting driver property generateSimpleParameterMetadata=true fixed it without having to re-write queries.