Clarity Service Management

Expand all | Collapse all

Using parameter with JasperSoft Studio Dataset query

Jump to Best Answer
  • 1.  Using parameter with JasperSoft Studio Dataset query

    Posted 01-29-2018 04:14 PM

    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)



  • 2.  Re: Using parameter with JasperSoft Studio Dataset query

    Posted 01-30-2018 01:18 AM
      |   view attached

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

     

    Steve Troy  ca technologies

    Sr Principal Engineering Svcs Architect

    Tel:        +1 425 201 3528

    Mobile: +1 206 718 7466

    Steven.Troy@ca.com

    <mailto:Steven.Troy@ca.com>

     



  • 3.  Re: Using parameter with JasperSoft Studio Dataset query
    Best Answer

    Posted 01-30-2018 08:45 AM

    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.



  • 4.  Re: Using parameter with JasperSoft Studio Dataset query

    Posted 01-30-2018 11:09 AM
      |   view attached

    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:        +1 425 201 3528

    Mobile: +1 206 718 7466

    Steven.Troy@ca.com

    <mailto:Steven.Troy@ca.com>

     



  • 5.  Re: Using parameter with JasperSoft Studio Dataset query

    Posted 01-30-2018 11:25 AM

    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.