AnsweredAssumed Answered

Using parameter with JasperSoft Studio Dataset query

Question asked by blav on Jan 29, 2018
Latest reply on Jan 30, 2018 by SteveTroy

I'm having an issue trying to use parameters in JasperSoft Studio Dataset queries with CASD data connection. When doing the same with a direct connection to SQL server it all works.


===========================================================

CASD connection - not working using parameter in the where clause

===========================================================

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.0.final using JasperReports Library version 6.3.0 -->
<!-- 2018-01-29T15:59:51 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="TestSQLQuery_SQLServerConnection" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="cc6bb629-dd7a-42b9-9c2f-f5a862fdb6cd">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="SDM_DS_Production "/>
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<parameter name="Test" class="java.lang.String"/>
<queryString>
<![CDATA[select notify_msg_title from ntfm
where delete_flag = $P{Test}]]>
</queryString>
<field name="notify_msg_title" class="java.lang.String"/>
<background>
<band splitType="Stretch"/>
</background>
<columnHeader>
<band height="44" splitType="Stretch">
<staticText>
<reportElement x="123" y="0" width="100" height="30" uuid="25383c65-17f1-442b-b369-5187b771d2af"/>
<text><![CDATA[notify_msg_title]]></text>
</staticText>
</band>
</columnHeader>
<detail>
<band height="43" splitType="Stretch">
<textField>
<reportElement x="123" y="13" width="100" height="30" uuid="73d75ae4-f3d0-4b57-9e3b-7e4065d8f71d"/>
<textFieldExpression><![CDATA[$F{notify_msg_title}]]></textFieldExpression>
</textField>
</band>
</detail>
</jasperReport>

===========================================================

 

===========================================================

SQL Server connection - All working well

===========================================================

 

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.0.final using JasperReports Library version 6.3.0 -->
<!-- 2018-01-29T15:57:50 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="TestSQLQuery_SQLServerConnection" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="cc6bb629-dd7a-42b9-9c2f-f5a862fdb6cd">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="SDM_DS_SQL_Server_Prod"/>
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<parameter name="Test" class="java.lang.String"/>
<queryString>
<![CDATA[select notify_msg_title from ntfm
where del = $P{Test}]]>
</queryString>
<field name="notify_msg_title" class="java.lang.String"/>
<background>
<band splitType="Stretch"/>
</background>
<columnHeader>
<band height="44" splitType="Stretch">
<staticText>
<reportElement x="123" y="0" width="100" height="30" uuid="25383c65-17f1-442b-b369-5187b771d2af"/>
<text><![CDATA[notify_msg_title]]></text>
</staticText>
</band>
</columnHeader>
<detail>
<band height="43" splitType="Stretch">
<textField>
<reportElement x="123" y="13" width="100" height="30" uuid="73d75ae4-f3d0-4b57-9e3b-7e4065d8f71d"/>
<textFieldExpression><![CDATA[$F{notify_msg_title}]]></textFieldExpression>
</textField>
</band>
</detail>
</jasperReport>

==========================================================

Here is the exception trace that I get when trying to run the report in JasperSoft Studio using CASD connection.
==========================================================
net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: TestSQLQuery_SQLServerConnection.
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:537)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$20(ReportControler.java:512)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler$5.run(ReportControler.java:393)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)
Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: TestSQLQuery_SQLServerConnection.
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:344)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1129)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:696)
at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:437)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:548)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver]An internal error occurred.
at com.ddtek.openaccess.ssp.Diagnostic.ClientCoreError(Unknown Source)
at com.ddtek.openaccess.ssp.Chain.decode(Unknown Source)
at com.ddtek.openaccess.ssp.Chain.send(Unknown Source)
at com.ddtek.openaccess.ctxt.stmt.StatementContext.execute(Unknown Source)
at com.ddtek.jdbc.openaccess.OpenAccessImplStatement.execute(Unknown Source)
at com.ddtek.jdbc.oabase.BaseStatement.commonExecute(Unknown Source)
at com.ddtek.jdbc.oabase.BaseStatement.executeQueryInternal(Unknown Source)
at com.ddtek.jdbc.oabase.BasePreparedStatement.executeQuery(Unknown Source)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310)
... 6 more
===========================================================

Actually, I have to build another report based on really more complex query but this post shows a basic simple case.
Does anyone know? Is it something not supported by CA yet?

Outcomes