Using parameter with JasperSoft Studio Dataset query

[Benoit Laviolette]

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?

Attachment(s)

TestSQLQuery_CASD.jrxml.zip   926 B 1 version

TestSQLQuery_SQLServerConnection.jrxml.zip   952 B 1 version

[SteveTroy]

Attach your .jrxml file and let me take a look.

 

Steve Troy  ca technologies

Sr Principal Engineering Svcs Architect

Tel:        [phone]

Mobile: [phone]

[e-mail]

<mailto:[e-mail]>

 

[Benoit Laviolette]

Hi Steve,

Oh well, my bad, looks like it needs an exclamation mark in order to make it work. Actually it is not mandatory when using in a SQL Query but looks like it's the only way to have a query to CASD DataSource working with parameters.

 

After correction:

<queryString>
<![CDATA[select notify_msg_title from ntfm
where delete_flag = $P!{Test}]]>
</queryString>

 

I leave the post here in case I would not be the only facing this case.

[SteveTroy]

Ah Good Catch.  I missed that when reviewing your original post.

 

Good info for everyone to know.

 

Steve Troy  ca technologies

Sr Principal Engineering Svcs Architect

Tel:        [phone]

Mobile: [phone]

[e-mail]

<mailto:[e-mail]>

 

[Benoit Laviolette]

Just to give a little bit more information on this it is possible after that to link input parameters on Jasperserver to queries for example: "select sym from crt" for ticket type or "select last_name from grp where delete_flag = 0" to filter by group.