-1

I created a report which is run successfully in iReport. In this report I've used two parameters: start_date and end_date but when it run on JasperReports Server it give the exception in which (start_date and end_date) parameters are not set in query you can see the following exception :

Exception is given as:

The server has encountered an error. Please excuse the inconvenience.
Error Message

Error filling report
Error Trace

com.jaspersoft.jasperserver.api.JSException: Error filling report at 
com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$FillResultListener.reportFillError(EngineServiceImpl.java:1231) 
at net.sf.jasperreports.engine.fill.BaseFillHandle.notifyError(BaseFillHandle.java:211) 
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:135) 
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:878) 
at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:165) 
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:834) 
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1696) 
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1055) 
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:989) 
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:900) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) 
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) 
at java.lang.Thread.run(Thread.java:724) Caused by: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query : 
SELECT request_for_vehicle_managament_details.date r_date, cast(replace(group_concat(concat(request_for_vehicle_managament_details.start_time," - ", request_for_vehicle_managament_details.end_time)),',', '\n') as char) as d_time, driver_master.first_name as name FROM request_for_vehicle_magament INNER JOIN request_for_vehicle_managament_details ON request_for_vehicle_magament.pk = request_for_vehicle_managament_details.request_for_vehicle_magament_pk INNER JOIN vehicle_master ON request_for_vehicle_managament_details.vehicle_master_pk = vehicle_master.pk INNER JOIN driver_master ON vehicle_master.driver_master_pk = driver_master.pk where request_for_vehicle_magament.date_from>=? and request_for_vehicle_magament.date_to<= ? group by request_for_vehicle_managament_details.date 
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:372) 
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:196) 
at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:168) 
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1087) 
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:668) 
at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1281) 
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:900)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120) ... 
10 more Caused by: java.sql.SQLException: Could not set parameter a
t org.mariadb.jdbc.internal.SQLExceptionMapper.getSQLException(SQLExceptionMapper.java:154)
at org.mariadb.jdbc.MySQLPreparedStatement.setParameter(MySQLPreparedStatement.java:443) 
at org.mariadb.jdbc.MySQLPreparedStatement.setNull(MySQLPreparedStatement.java:173) 
at org.apache.commons.dbcp.DelegatingPreparedStatement.setNull(DelegatingPreparedStatement.java:104) 
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setDate(JRJdbcQueryExecuter.java:642) 
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:577) 
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:399) 
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter$1.visit(JRJdbcQueryExecuter.java:332) 
at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter$QueryParameter.accept(JRAbstractQueryExecuter.java:157) 
at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.visitQueryParameters(JRAbstractQueryExecuter.java:646) 
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:317) ... 17 more Caused by: org.mariadb.jdbc.internal.common.query.IllegalParameterException: No '?' on that position 
at org.mariadb.jdbc.internal.common.query.MySQLParameterizedQuery.setParameter(MySQLParameterizedQuery.java:95) 
at org.mariadb.jdbc.MySQLPreparedStatement.setParameter(MySQLPreparedStatement.java:441) ... 26 more

The jrxml:

<?xml version="1.0" encoding="UTF-8"?>
<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="TransportDriverDutyChart" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="0" bottomMargin="20" uuid="0fcc5245-15a0-479c-992c-e93d7359aff6">
    <property name="ireport.jasperserver.reportUnit" value="/reports/Ils/transport"/>
    <property name="ireport.jasperserver.url" value="http://192.168.1.33:8181/jasperserver/services/repository"/>
    <style name="Crosstab Data Text" hAlign="Center"/>
    <parameter name="start_date" class="java.util.Date">
        <defaultValueExpression><![CDATA[]]></defaultValueExpression>
    </parameter>
    <parameter name="end_date" class="java.util.Date">
        <defaultValueExpression><![CDATA[]]></defaultValueExpression>
    </parameter>
    <parameter name="service_pk" class="java.lang.String">
        <defaultValueExpression><![CDATA[" "]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT
request_for_vehicle_managament_details.date r_date,
cast(replace(group_concat(concat(request_for_vehicle_managament_details.start_time," - ",
request_for_vehicle_managament_details.end_time)),',', '\n') as char) as d_time,
driver_master.first_name as name
FROM
request_for_vehicle_magament
INNER JOIN request_for_vehicle_managament_details ON request_for_vehicle_magament.pk = request_for_vehicle_managament_details.request_for_vehicle_magament_pk
INNER JOIN vehicle_master ON request_for_vehicle_managament_details.vehicle_master_pk = vehicle_master.pk
INNER JOIN driver_master ON vehicle_master.driver_master_pk = driver_master.pk
where  request_for_vehicle_magament.date_from>=$P{start_date} and  request_for_vehicle_magament.date_to<= $P{end_date}
group by request_for_vehicle_managament_details.date]]>
    </queryString>
    <field name="r_date" class="java.sql.Date"/>
    <field name="d_time" class="java.lang.String"/>
    <field name="name" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="77" splitType="Stretch">
            <textField>
                <reportElement uuid="ad2c4342-26fe-40f6-871c-927aecf30f18" mode="Opaque" x="0" y="48" width="555" height="20" backcolor="#86ABD9"/>
                <textElement textAlignment="Center">
                    <font size="12" isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA["Driver duty chart between "+ $P{start_date}+" and "+$P{end_date}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement uuid="5955ee9a-99f0-4a1a-ab78-90c7e585d25b" x="165" y="18" width="326" height="20" forecolor="#000000" backcolor="#FFFFFF"/>
                <textElement>
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[LBS NATIONAL ACADEMY OF ADMINISTRATION]]></text>
            </staticText>
            <image onErrorType="Blank">
                <reportElement uuid="7da01409-759e-4226-985b-b839527e74d6" x="0" y="0" width="160" height="40"/>
                <imageExpression><![CDATA["repo:logo.gif"]]></imageExpression>
            </image>
        </band>
    </title>
    <summary>
        <band height="176" splitType="Stretch">
            <crosstab>
                <reportElement uuid="9b9d6bbe-0c86-4d64-868e-77b8c1b2d25f" x="0" y="0" width="555" height="176"/>
                <rowGroup name="name" width="90">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{name}]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents backcolor="#F0F8FF" mode="Opaque">
                            <box>
                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                                <reportElement uuid="245f6b2f-b701-4734-9425-50f84418f94a" style="Crosstab Data Text" x="0" y="0" width="90" height="25"/>
                                <textElement verticalAlignment="Bottom"/>
                                <textFieldExpression><![CDATA[$V{name}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents/>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="r_date" height="30">
                    <bucket class="java.sql.Date">
                        <bucketExpression><![CDATA[$F{r_date}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents backcolor="#F0F8FF" mode="Opaque">
                            <box>
                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                                <reportElement uuid="4eeaf306-6593-4d83-a21e-9a7357ab9765" style="Crosstab Data Text" x="0" y="0" width="100" height="30"/>
                                <textElement verticalAlignment="Bottom"/>
                                <textFieldExpression><![CDATA[$V{r_date}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents/>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="d_timeMeasure" class="java.lang.String">
                    <measureExpression><![CDATA[$F{d_time}]]></measureExpression>
                </measure>
                <crosstabCell width="100" height="25">
                    <cellContents>
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                            <reportElement uuid="a020f51b-04f5-4f2e-b090-9e7a30b5179d" style="Crosstab Data Text" x="0" y="0" width="100" height="25"/>
                            <textElement verticalAlignment="Bottom"/>
                            <textFieldExpression><![CDATA[$V{d_timeMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell height="25" rowTotalGroup="name">
                    <cellContents backcolor="#BFE1FF" mode="Opaque">
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement uuid="defe4d36-e58d-4340-bce1-4c12232c4d0e" style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>
                            <textElement/>
                            <textFieldExpression><![CDATA[$V{d_timeMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="50" columnTotalGroup="r_date">
                    <cellContents backcolor="#BFE1FF" mode="Opaque">
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement uuid="59d53e33-3a0c-45b8-9b97-22ac20ea6eb6" style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>
                            <textElement/>
                            <textFieldExpression><![CDATA[$V{d_timeMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell rowTotalGroup="name" columnTotalGroup="r_date">
                    <cellContents backcolor="#BFE1FF" mode="Opaque">
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement uuid="688018a7-8a72-4354-9f5a-56daf786db4b" style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>
                            <textElement/>
                            <textFieldExpression><![CDATA[$V{d_timeMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
            </crosstab>
        </band>
    </summary>
</jasperReport>

Screensort of the output of report in iReport is following:

enter image description here

Please provide me solution or tell me what is the meaning of this exception

Alex K
  • 22,315
  • 19
  • 108
  • 236
Neeraj singh
  • 257
  • 1
  • 8
  • 18
  • You did not pass the parameters... – Alex K Sep 05 '13 at 11:56
  • [Getting Started with JasperReports Server](http://community.jaspersoft.com/wiki/getting-started-jasperreports-server) & [JasperReports Server Samples](http://community.jaspersoft.com/wiki/jasperreports-server-samples) – Alex K Sep 05 '13 at 11:58
  • sir i have created two input control fo date type to select start_date and end_date – Neeraj singh Sep 05 '13 at 12:17
  • Maybe this post help you [JasperServer - null values for input controls](http://stackoverflow.com/q/11082379/876298) – Alex K Sep 05 '13 at 12:25
  • Another post: [Cascading (dependent) parameter values in JRXML are returning null values instead of default values](http://community.jaspersoft.com/wiki/cascading-dependent-parameter-values-jrxml-are-returning-null-values-instead-default-values) – Alex K Sep 05 '13 at 12:27
  • And another related posts: [“Rolling up” groups in Jaspersoft iReport](http://stackoverflow.com/q/10897962/876298) & [Equality comparison for null fails in input control, 4.7](http://community.jaspersoft.com/wiki/equality-comparison-null-fails-input-control-47) – Alex K Sep 05 '13 at 12:34
  • I get it where is the problem, the problem in the cast() function in the query, when i remove this report is executed well,but when i remove cast() function then a refrence [B@65c9658f is comeing from d_time column – Neeraj singh Sep 05 '13 at 13:42
  • You can post your solution as an answer - to help future readers – Alex K Sep 05 '13 at 13:47

1 Answers1

1

I get it where is the problem, the problem in the cast() function in the query, when I remove this report is executed well, but when I remove cast() function then a reference [B@65c9658f is coming from d_time column

Solution is that I used convert function instead of cast function in the sql query. In convert function I used utf8 format now it works for me, using utf8 may also be work with cast function you can try.

You can see query is following: 

SELECT
request_for_vehicle_managament_details.date r_date,
replace(CONVERT(group_concat(concat(request_for_vehicle_managament_details.start_time," - ",
request_for_vehicle_managament_details.end_time)) USING utf8 ),',',' ') as d_time,
driver_master.first_name as name
FROM
request_for_vehicle_magament
INNER JOIN request_for_vehicle_managament_details ON request_for_vehicle_magament.pk = request_for_vehicle_managament_details.request_for_vehicle_magament_pk
INNER JOIN vehicle_master ON request_for_vehicle_managament_details.vehicle_master_pk = vehicle_master.pk
INNER JOIN driver_master ON vehicle_master.driver_master_pk = driver_master.pk
where  request_for_vehicle_magament.date_from >= $P{start_date} and  request_for_vehicle_magament.date_to <= $P{end_date}
group by request_for_vehicle_managament_details.date
Alex K
  • 22,315
  • 19
  • 108
  • 236
Neeraj singh
  • 257
  • 1
  • 8
  • 18