AnsweredAssumed Answered

Parameter not working in MySQL query

Question asked by Mayuri Donda on Jan 25, 2019
Latest reply on Feb 13, 2019 by Mayuri Donda

I am using PRD CE 8.1

I have added drop down parameter (Exam_Id) in report.

Static query works fine - select * from Table where ExamId = 1

But dynamic is not working - select * from Table where ExamId = ${Exam_Id} gives me below error

org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: SELECT * from Fact f join Dimension ee on f.ExamEventId = ee.ExamEventId where f.ExamId = ${Exam_Id} at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:184) at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:142) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStaticInternal(CompoundDataFactory.java:172) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:154) at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryInternal(CachingDataFactory.java:408) at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryStatic(CachingDataFactory.java:151) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStaticInternal(CompoundDataFactory.java:168) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:154) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryData(CompoundDataFactory.java:67) at org.pentaho.reporting.engine.classic.core.states.datarow.DefaultFlowController.performQueryData(DefaultFlowController.java:256) at org.pentaho.reporting.engine.classic.core.states.datarow.DefaultFlowController.performQuery(DefaultFlowController.java:192) at org.pentaho.reporting.engine.classic.core.states.process.ProcessState.initializeForMasterReport(ProcessState.java:318) at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.prepareReportProcessing(AbstractReportProcessor.java:442) at org.pentaho.reporting.engine.classic.core.modules.output.pageable.graphics.PrintReportProcessor.getNumberOfPages(PrintReportProcessor.java:69) at org.pentaho.reporting.engine.classic.core.modules.gui.base.PreviewPane$RepaginationRunnable.run(PreviewPane.java:238) at org.pentaho.reporting.engine.classic.core.util.Worker.run(Worker.java:153) Caused by: java.sql.SQLException: Parameter metadata not available for the given statement at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) at com.mysql.cj.jdbc.MysqlParameterMetadata.checkAvailable(MysqlParameterMetadata.java:86) at com.mysql.cj.jdbc.MysqlParameterMetadata.getParameterClassName(MysqlParameterMetadata.java:164) 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) ... 15 more 

Please suggest what needs to be done to solve above?

Outcomes