Error executing SQL statement for report when print jasper file

Question

i modify an old project and i cannot identify what is wrong with this code.using below .jrxml it create .jasper file. when i print the jasper file (using this code JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport,parameters,getConnection()); ) it show below error. please help me to resolve this.

this is top part of the Job.jrxml file

?xml version="1.0" encoding="UTF-8"  ?>
<!-- Created with iReport - A designer for JasperReports -->
<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
<jasperReport
         name="Task"
         columnCount="1"
         printOrder="Vertical"
         orientation="Landscape"
         pageWidth="1275"
         pageHeight="630"
         columnWidth="1172"
         columnSpacing="0"
         leftMargin="23"
         rightMargin="80"
         topMargin="0"
         bottomMargin="0"
         whenNoDataType="AllSectionsNoDetail"
         isTitleNewPage="false"
         isSummaryNewPage="false">
    <property name="ireport.scriptlethandling" value="0" />
    <property name="ireport.encoding" value="UTF-8" />
    <import value="java.util.*" />
    <import value="net.sf.jasperreports.engine.*" />
    <import value="net.sf.jasperreports.engine.data.*" />
    <reportFont name="Arial_Normal" isDefault="true" fontName="Arial" size="12" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
    <reportFont name="Arial_Bold" isDefault="false" fontName="Arial" size="12" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica-Bold" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
    <reportFont name="Arial_Italic" isDefault="false" fontName="Arial" size="12" isBold="false" isItalic="true" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica-Oblique" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
    <reportFont name="Courier" isDefault="false" fontName="Courier New" size="12" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica-Oblique" pdfEncoding="Cp1252" isPdfEmbedded="false"/>

    <parameter name="filter" isForPrompting="true" class="java.lang.String"/>
    <parameter name="dates" isForPrompting="false" class="java.lang.String"/>
    <parameter name="workFlow" isForPrompting="false" class="java.lang.String"/>
    <parameter name="workFlow1" isForPrompting="false" class="java.lang.String">
            <defaultValueExpression ><![CDATA["test"]]></defaultValueExpression>
    </parameter>
    <parameter name="userName" isForPrompting="false" class="java.lang.String"/>
    <queryString><![CDATA[SELECT A.initiatedTime, A.dueTime, A.completedTime, A.taskStatus,B.jobName,
                    C.taskName,  D.firstName||' '||D.lastName AlocatedUser , E.workflowName ,B.workflowdefinition
                    FROM T_JOB_TRANSACTION A , T_JOB_DEFINITION B ,T_TASK_DEFINITION C ,T_USER D ,T_WORKFLOW_DEFINITION E
                    WHERE A.jobDefinition=B.JobID 
                    AND C.taskId=A.taskDefinition
                    AND D.userId= A.firstUser
                    AND B.workflowDefinition =E.workflowId
                    AND B.isSaved=0
                    AND c.isUsertask=1 $P!{filter} $P!{workFlow} $P!{workFlow1}
                    GROUP BY B.workflowDefinition, C.taskName, B.jobName, D.firstName||' '||D.lastName,
                    A.initiatedTime, A.dueTime, A.completedTime, A.taskStatus ,E.workflowName  
                    UNION
                    SELECT A.initiatedTime, A.dueTime, A.completedTime, A.taskStatus, B.jobName,
                    C.taskName,  D.firstName||' '||D.lastName AlocatedUser , E.workflowName ,B.workflowdefinition
                    FROM T_JOB_TRANSACTION A , T_JOB_DEFINITION B ,T_TASK_DEFINITION C ,T_USER D ,T_WORKFLOW_DEFINITION E
                    WHERE A.jobDefinition=B.JobID 
                    AND C.taskId=A.taskDefinition
                    AND D.userId= A.firstUser
                    AND B.workflowDefinition =E.workflowId
                    AND B.isSaved=0
                    AND c.isUsertask=1 $P!{userName} 
                    AND B.isInitiated=1 AND B.isCancelled=0
                    AND E.workflowId IN (SELECT A.workflowID
                                         FROM T_SUB_WORKFLOW A, T_TASK_DEFINITION B
                                         WHERE A.TASK_ID = B.taskID
                                         AND B.isSubWfInvoke = 1 $P!{workFlow} $P!{workFlow1} )
                    GROUP BY B.workflowDefinition, C.taskName, B.jobName, D.firstName||' '||D.lastName,
                    A.initiatedTime, A.dueTime, A.completedTime, A.taskStatus ,E.workflowNameorder by completedTime]]></queryString>

    <field name="initiatedTime" class="java.sql.Timestamp"/>
    <field name="dueTime" class="java.sql.Timestamp"/>
    <field name="completedTime" class="java.sql.Timestamp"/>
    <field name="taskStatus" class="java.lang.String"/>
    <field name="jobName" class="java.lang.String"/>
    <field name="taskName" class="java.lang.String"/>
    <field name="AlocatedUser" class="java.lang.String"/>
    <field name="workflowName" class="java.lang.String"/>
    <field name="workflowdefinition" class="java.math.BigDecimal"/>

    <variable name="delay_time" class="java.lang.String" resetType="Report" calculation="Nothing">
        <variableExpression><![CDATA[String.valueOf((($F{completedTime}.getTime() - $F{dueTime}.getTime())>0) ? "True":"False")]]></variableExpression>
    </variable>
        <background>
            <band height="0"  isSplitAllowed="true" >
            </band>
        </background>
        <title>
            <band height="99"  isSplitAllowed="true" >
                <staticText>
                    <reportElement
                        x="5"
                        y="12"
                        width="958"
                        height="27"
                        key="staticText-2"/>
                    <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
                    <textElement textAlignment="Center" verticalAlignment="Middle">
                        <font reportFont="Arial_Normal" size="18"/>
                    </textElement>
                <text><![CDATA[NSB]]></text>
                </staticText>
                <staticText>
                    <reportElement
                        x="152"
                        y="51"
                        width="673"
                        height="21"
                        key="staticText-3"/>
                    <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
                    <textElement textAlignment="Center" verticalAlignment="Middle">
                        <font reportFont="Arial_Normal" size="15"/>
                    </textElement>
                <text><![CDATA[Task Details Reports]]></text>
                </staticText>
                <textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
                    <reportElement
                        x="6"
                        y="73"
                        width="957"
                        height="18"
                        key="textField"/>
                    <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
                    <textElement textAlignment="Center" verticalAlignment="Middle">
                        <font reportFont="Arial_Normal"/>
                    </textElement>
                <textFieldExpression   class="java.lang.String"><![CDATA[$P{dates}]]></textFieldExpression>
                </textField>
            </band>
        </title>
        <pageHeader>

this is the error when print report

 net.sf.jasperreports.engine.JRException: Error executing SQL statement for report : Job
10:27:49,838 ERROR TaskReportManager:181 - net.sf.jasperreports.engine.JRException: Error executing SQL statement for report : Job
java.sql.SQLException: ORA-00933: SQL command not properly ended

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
    at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:420)
    at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:896)
    at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:452)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:986)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2929)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:478)
    at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:77)
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:248)
    at com.affno.form.report.TaskReportManager.genReport(TaskReportManager.java:160)
    at com.affno.form.web.report.action.TaskReportAction.execute(TaskReportAction.java:412)
    at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
    at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
    at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
    at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at com.affno.common.filter.SessionCheckingFilter.doFilter(SessionCheckingFilter.java:140)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)

NESTED BY :
java.sql.SQLException: ORA-00933: SQL command not properly ended

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
    at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:420)
    at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:896)
    at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:452)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:986)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2929)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:478)
    at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:77)
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:248)
    at com.affno.form.report.TaskReportManager.genReport(TaskReportManager.java:160)
    at com.affno.form.web.report.action.TaskReportAction.execute(TaskReportAction.java:412)
    at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
    at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
    at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
    at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at com.affno.common.filter.SessionCheckingFilter.doFilter(SessionCheckingFilter.java:140)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)

NESTED BY :
net.sf.jasperreports.engine.JRException: Error executing SQL statement for report : Job
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:487)
    at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:77)
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:248)
    at com.affno.form.report.TaskReportManager.genReport(TaskReportManager.java:160)
    at com.affno.form.web.report.action.TaskReportAction.execute(TaskReportAction.java:412)
    at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
    at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
    at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
    at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at com.affno.common.filter.SessionCheckingFilter.doFilter(SessionCheckingFilter.java:140)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: ORA-00933: SQL command not properly ended

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
    at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:420)
    at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:896)
    at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:452)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:986)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2929)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:478)
    ... 33 more

Petter Friberg · Accepted Answer

As @Maheswaran Ravisankar commented, you have an error in your sql: ORA-00933: SQL command not properly ended

The easiest way for you to debug this error is to enable the logger for the JRJdbcQueryExecuter class, the property in the log.xml is:

<logger name="net.sf.jasperreports.engine.query.JRJdbcQueryExecuter">
    <level value="debug" />
</logger>

With the logger enable you will see the actual query that is executed and you can debug in a Oracle IDE (or post query on SO, if you can't find the error).

For more information about how to configure the log see Jakarta Commons Logging.

Error executing SQL statement for report when print jasper file

Tags:

java

jasper-reports

Sandun Chathuranga

1 Answers

Petter Friberg

Recent Activity

Donate For Us

Error executing SQL statement for report when print jasper file

Tags:

java

jasper-reports

Sandun Chathuranga

1 Answers

Petter Friberg

Related questions

Recent Activity

Donate For Us