Hot questions for Using JasperReports in sql

Question:

I want to pass an entire sql query as a parameter into a jasper report. I tried using $P{QUERY}, but it gave me a mysql syntax error exception. And I want it to be passed during run time. Does anyone know how to do it?

Example code:

try {
        Map<String, Object> map = new HashMap<>();
        Connection conn = DatabaseConnection.getInstance().getConnection();

        map.put("QUERY", "Select u.name, u.status from user_info u where u.user_name = 'Thanuj'");

        JasperReport report = JasperCompileManager.compileReport("report1.jrxml");
        JasperPrint jp = JasperFillManager.fillReport(report, map, conn);
        JasperViewer.viewReport(jp, false);
    } catch (JRException ex) {
        Logger.getLogger(ReportTest.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(ReportTest.class.getName()).log(Level.SEVERE, null, ex);
    } catch (ClassNotFoundException ex) {
        Logger.getLogger(ReportTest.class.getName()).log(Level.SEVERE, null, ex);
    }

Answer:

I figured out the answer with some help from my colleague. I had to change the parameter ($P{QUERY}) into ($P!{QUERY}) in the jasper report's xml file (ex; report.jrxml)

Question:

I am using iReport to build jaspersoft reports and I am using Fishbowl as my DBMS.

I built my tables and in iReport is shows that my tables are relational but for some reason it is throwing me errors and will not run. It should be very simple. I am taking in a zipcode, date range or State Name and outputing the productName, total quantity fulfilled, zip and state abbreviation.

When I do upload the report to Fishbowl it runs but eventually crashes with an error saying it's out of memory. I do not believe this is the issue but it is an effect of what ever is causing the report to not run correctly. Maybe it's my joins?

Here is my SQL
SELECT
 STATECONST."CODE" AS STATECONST_CODE,
 ADDRESS."STATEID" AS ADDRESS_STATEID,
 ADDRESS."ZIP" AS ADDRESS_ZIP,
 SOITEM."PRODUCTNUM" AS SOITEM_PRODUCTNUM,
 SOITEM."QTYFULFILLED" AS SOITEM_QTYFULFILLED
FROM
 "STATECONST" STATECONST INNER JOIN "ADDRESS" ADDRESS ON STATECONST."ID" =     ADDRESS."STATEID"
 INNER JOIN "ACCOUNT" ACCOUNT ON ADDRESS."ACCOUNTID" = ACCOUNT."ID"
 INNER JOIN "CUSTOMER" CUSTOMER ON ACCOUNT."ID" = CUSTOMER."ACCOUNTID"
 INNER JOIN "SO" SO ON CUSTOMER."ID" = SO."CUSTOMERID"
 INNER JOIN "SOITEM" SOITEM ON SO."ID" = SOITEM."SOID"
The error is:

java.lang.OutOfMemoryError : Java heap space

Here are some screenshots to help as well.


Answer:

When you have out of memory the general action is:

Java heap space out of memory

Related to jasper report it generates by default the entire report in memory, this can be changed using JRVirtualizer, see example in sample reference

Example (from sample reference using file virtualizer)

//Create the virtualizer after 2 pages filled save in tmp director
JRFileVirtualizer virtualizer = new JRFileVirtualizer(2, "tmp");

//Preparing parameters
Map parameters = new HashMap();
parameters.put(JRParameter.REPORT_VIRTUALIZER, virtualizer);

Question:

I already have 6 Jasper Report templates created, with all of the static text fields to be filled out using a SQL query. The SQL query uses 2 parameters that I am passing in: FirstName and LastName. I am also passing in 2 other parameters that will just get added to Report.

This is the code i have so far to pass the HashMap with the parameters to the Report. But I am lost as there isnt really any good documentation or examples that I can find.

package print;
import net.sf.jasperreports.engine.*;
import net.sf.jasperreports.engine.export.*;
import java.util.*;

public class PrintCertificate  
{   
   public PrintCertificate(String output, String certType, String firstName, String lastName, String confirmDate, String pastorName)
   {
    if(certType=="rci_eng")
    {
        String fileName = "/RCI_Eng.jasper";
        output = "C:/Users/User/Desktop/Test/";

        HashMap<String, Object> map = new HashMap<String, Object>();
        map.put("FirstName",firstName);
        map.put("LastName",lastName);
        map.put("PastorName", pastorName);
        map.put("DateOfConfirmation", confirmDate);
        try
        {
            JasperPrint print = JasperFillManager.fillReport(fileName, map);
            JRDocxExporter exporter = new JRDocxExporter();
            exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
            exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, "test.docx");
            exporter.exportReport(print);

        }
        catch (Exception e)
        {
            e.printStackTrace();
            System.exit(1);
        }
    }
  }
}

I know this is probably far from correct, but if someone can point me in the right direction of good documentation or examples, or point out what I've done wrong, it would be of great help!


Answer:

Here is a brief description to use Jasper Report with your Java Application

  • First you have to set database connection to the Report.

  • Then you can design your SQL query for the report. You can add parameters to the SQL query in case you need to filter data through your query.Just add parameters using New Parameter button and you can drag and drop parameters displaying inside the text area to your query.

In your report inspector you can see all the column names of our query under Fields category and all the parameters defined under Parameters category.

  • You can design a basic report like below By dragging and dropping field names and parameters to your Report Designer you can design your report as you desire. Title Band(Your Title Here) , Column Header Band(Column Names) , Detail Band(Iterative Data here) and Summary Band(Like Grand_Total,Date,Issued By and chart elements can be added to this section) are enough for basic report.
  • Then you can declare ReportGenarator class to generate your report

    public class ReportGenarator {
    
    public static String OUT_PUT = "your_output_file_path/myreport.docx";
    public static String REPORT = "your_report_path/myreport.jrxml";
    
    public void genarateReport(String reportPath,
            Map<String, Object> map, Connection con) {
        try {
    
            JasperReport jr = JasperCompileManager.compileReport(
                    ClassLoader.getSystemResourceAsStream(reportPath));
            JasperPrint jp = JasperFillManager.fillReport(jr, map, con);
            JRDocxExporter export = new JRDocxExporter();
        export.setExporterInput(new SimpleExporterInput(jp));
        export.setExporterOutput(new SimpleOutputStreamExporterOutput(new File(OUT_PUT)));
        SimpleDocxReportConfiguration config = new SimpleDocxReportConfiguration();
        export.setConfiguration(config);
        export.exportReport();
        } catch (JRException ex) {
            ex.printStackTrace();   
        }
    } }
    
  • You can generate your report by Pressing a button in your application.So that you have to include below code inside your button action event

    Map<String, Object> map = new HashMap<>();
                map.put("headding", "REPORT FROM DATABASE CONNECTION");//parameter name should be like it was named inside your report.
                new ReportGenarator().genarateReport(
                        ReportGenarator.REPORT, map, your_DB_connction_reference_here);
    

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

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.

Question:

Can i use jooq to create queries for jasper report?

I mean query like this

select something from table where field=$P{someparameter}

the problem is the $P{someparameter} in the generated sql.

To be clear I just need the generated sql query.


Answer:

You can always resort to what jOOQ calls "plain SQL", if there is some vendor-specific SQL expression that you would like to include. In your case, I'm guessing that the following might be sufficient for you:

Select<?> select =
DSL.using(configuration)
   .select(TABLE.SOMETHING)
   .from(TABLE)
   .where(TABLE.FIELD.eq(DSL.field("$P{{someparameter}}", TABLE.FIELD.getDataType())));

Note that you'll have to "escape" the curly braces, as curly braces have a meaning in jOOQ's plain SQL template language.

You can then extract the SQL string like this:

String sql = select.getSQL();

And possibly extract the bind values as well:

List<Object> bindings = select.getBindValues();

Question:

I have to continue the development of a web app and I found this error but I don't know how to fix it. When I click generate pdf, it calls to the following function:

public void crearPDF(Integer idProyecto, Usuarios usuario, String email, Boolean archivoDoc, Locale locale) throws SQLException
    {
        System.err.println("Entrando en crearPDF");
        //String ruta = "\\\\SERVER\\producción\\PDFAPP\\" + usuario.getNombre() + "\\";
        String ruta = "/home/pdfapp/" + usuario.getNombre() + "/";
        Integer versionProyecto;

        File rutaFile = new File(ruta);

        JRPdfExporter exp = new JRPdfExporter();
        JRPdfExporter exp2 = new JRPdfExporter();
        JRDocxExporter expD = new JRDocxExporter();

        Connection conn = null;

        if(!rutaFile.exists()){
            rutaFile.mkdirs();
        }

        MailUtil mUtil = new MailUtil();

        String nombreProyecto;

        FacesContext context = FacesContext.getCurrentInstance();

        //LOCALIZACIÓN DE INFORMES

        //Si no viene especificado por el administrador, extraer el idioma del contexto (establecido en función del usuario)

        //Genera el ResourceBundle que le pasaremos por parámetro
        ResourceBundle rb = ResourceBundle.getBundle("com.appglass.jasper.resources.report", locale);

        //Introducimos los parámetros necesarios
        HashMap <String, Object> map = new HashMap <String, Object>();

        map.put("rutaImagenes", (Object) rutaImagenes);
        map.put("idProyecto", (Object) idProyecto);
        map.put("archivoDoc", (Object) archivoDoc);
        map.put(JRParameter.REPORT_LOCALE,locale);
        map.put(JRParameter.REPORT_RESOURCE_BUNDLE, rb);



        try{

            Session session = HibernateUtil.getSessionFactory().getCurrentSession();

            session.beginTransaction();
            //Nuevo para conseguir session.connection() en hibernate 4.x
            SessionFactoryImpl sessionFactory = (SessionFactoryImpl) session.getSessionFactory();
            conn = sessionFactory.getConnectionProvider().getConnection();
            //session.connection() fue borrado en hibernate 4.x
            //conn = session.connection();

/*JRProperties.setProperty("net.sf.jasperreports.default.pdf.font.name", "Deja Vu Sans");

JasperReport jasperReport = JasperCompileManager.compileReport(reportSource);
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, params);

JasperExportManager.exportReportToPdfFile(jasperPrint, outputFileName);            */
            InputStream reporte = (InputStream) getClass().getResourceAsStream("/com/appglass/jasper/report2.jasper");
            InputStream reporte2 = (InputStream) getClass().getResourceAsStream("/com/appglass/jasper/report2_subreport1B.jasper");
            InputStream reporte3 = (InputStream) getClass().getResourceAsStream("/com/appglass/jasper/report2_subreport2B.jasper");
            InputStream reporte4 = (InputStream) getClass().getResourceAsStream("/com/appglass/jasper/report2_plano.jasper");
            InputStream reporte5 = (InputStream) getClass().getResourceAsStream("/com/appglass/jasper/report2_resumen.jasper");
            // Path completo C:/Users/pool/Documents/NetBeansProjects/AppGlass/src/java

            if ((reporte == null) || reporte2 == null || reporte3 == null || reporte4 == null){
                System.err.println("No se ha encontrado el archivo .jasper");
            }


            System.err.println("Antes de llamar a JasperFillManager.fillReport");
            JasperPrint jasperPrint = JasperFillManager.fillReport(reporte, map, conn); //HERE THERE IS A PROBLEm<---------------------------------------------------------------
            System.err.println("Report1 done");
            JasperPrint jasperPrint2 = JasperFillManager.fillReport(reporte2, map, conn);
            JasperPrint jasperPrint3 = JasperFillManager.fillReport(reporte3, map, conn);
            JasperPrint jasperPrint4 = JasperFillManager.fillReport(reporte4, map, conn);
            JasperPrint jasperPrint5 = JasperFillManager.fillReport(reporte5, map, conn);
            System.err.println("Antes de llamar a JasperViewer.viewReport");


            //Para exportar el PDF al cliente
            HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();


            List list = new ArrayList();
            list.add(jasperPrint4);
            list.add(jasperPrint5);
            list.add(jasperPrint);
            list.add(jasperPrint2);
            list.add(jasperPrint3);


            if(!archivoDoc){
                exp.setParameter(JRPdfExporterParameter.JASPER_PRINT_LIST, list);
                exp2.setParameter(JRPdfExporterParameter.JASPER_PRINT_LIST, list);
                exp.setParameter(JRExporterParameter.CHARACTER_ENCODING, "UTF-8");
                exp.setParameter(JRExporterParameter.CHARACTER_ENCODING, "UTF-8");
            }

            nombreProyecto = queryGetNombreProyecto(conn, idProyecto);


            versionProyecto = queryGetVersionProyecto(conn, idProyecto);

            if(!archivoDoc){
                //Fijamos como salida un fichero en SERVER
                exp2.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, ruta + nombreProyecto + "(" + (versionProyecto + 1) + ").pdf");
                exp2.setParameter(JRPdfExporterParameter.METADATA_AUTHOR, usuario.getNombre());
                exp2.setParameter(JRPdfExporterParameter.METADATA_TITLE, nombreProyecto);
                exp2.exportReport();
            } else {
                expD.setParameter(JRDocxExporterParameter.JASPER_PRINT_LIST, list);
                //Fijamos como salida un fichero en SERVER
                expD.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, ruta + nombreProyecto + "(" + (versionProyecto + 1) + ").doc");

                //Fijamos como salida la un fichero en el servidor remoto
                expD.exportReport();
            }

            //Enviamos el mail de confirmación con el informe adjunto
            mUtil.confirmacionInforme(email, usuario, nombreProyecto, archivoDoc, versionProyecto + 1);

            //Fijamos como salida el lector de PDF del navegador
            if(!archivoDoc){

               response.setHeader("Content-Type", "application/pdf; charset=UTF-8");
               response.setHeader("Content-disposition", "attachment; filename*=UTF-8\'\'" + URLEncoder.encode(nombreProyecto, "UTF-8") + ".pdf");
               exp.setParameter(JRExporterParameter.OUTPUT_STREAM, response.getOutputStream());
               exp.exportReport();
            }

           session.getTransaction().commit();
           //conn.commit();

        }catch(SQLException sqlEx){
            System.err.println("Error creando el PDF");
            System.err.println(sqlEx.getClass());
            System.err.println(sqlEx.getCause());
            System.err.println(sqlEx.getMessage());

        }
        catch (HibernateException ex)
        {

            System.err.println("Error creando el PDF");
            System.err.println(ex.getClass());
            System.err.println(ex.getCause());
            System.err.println(ex.getMessage());

        } catch (JRException ex) {
            System.err.println("Error creando el PDF");
            System.err.println(ex.getClass());
            System.err.println(ex.getCause());
            System.err.println(ex.getMessage());
        } catch (IOException ex) {
            System.err.println("Error creando el PDF");
            System.err.println(ex.getClass());
            System.err.println(ex.getCause());
            System.err.println(ex.getMessage());
        } finally {

            if(conn != null){
                conn.close();
            }
        }

        System.err.println("Antes de responseComplete");
        FacesContext.getCurrentInstance().responseComplete();

    }

Stack of the error:

Severe:   log4j:WARN No appenders could be found for logger (net.sf.jasperreports.extensions.ExtensionsEnvironment).
Severe:   log4j:WARN Please initialize the log4j system properly.
Severe:   class net.sf.jasperreports.engine.JRException
Severe:   java.sql.SQLException: No database selected
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
    at com.sun.gjc.spi.jdbc40.PreparedStatementWrapper40.executeQuery(PreparedStatementWrapper40.java:642)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:239)
    at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1073)
    at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:667)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1253)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:877)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:822)
    at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:61)
    at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:446)
    at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:407)
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:824)
    at com.appglass.jasper.Reportes.crearPDF(Reportes.java:245)
    at com.appglass.backing.ProyectoAgregadoBacking.crearPDF(ProyectoAgregadoBacking.java:78)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.sun.el.parser.AstValue.invoke(AstValue.java:289)
    at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:304)
    at org.jboss.weld.util.el.ForwardingMethodExpression.invoke(ForwardingMethodExpression.java:40)
    at org.jboss.weld.el.WeldMethodExpression.invoke(WeldMethodExpression.java:50)
    at javax.faces.event.MethodExpressionActionListener.processAction(MethodExpressionActionListener.java:149)
    at javax.faces.event.ActionEvent.processListener(ActionEvent.java:88)
    at javax.faces.component.UIComponentBase.broadcast(UIComponentBase.java:813)
    at javax.faces.component.UICommand.broadcast(UICommand.java:300)
    at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
    at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282)
    at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:646)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:344)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
    at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:70)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:316)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
    at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
    at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:415)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:282)
    at com.sun.enterprise.v3.services.impl.ContainerMapper$HttpHandlerCallable.call(ContainerMapper.java:459)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:167)
    at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:201)
    at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:175)
    at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:235)
    at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:284)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:201)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:133)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:112)
    at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
    at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:561)
    at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:112)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:117)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:56)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:137)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:565)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:545)
    at java.lang.Thread.run(Thread.java:745)
Severe:   Error executing SQL statement for : report2
Severe:   Antes de responseComplete

report2.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="report2" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="0" resourceBundle="com.appglass.jasper.resources.report.properties" uuid="70977800-585f-4d11-94b6-26a37538935c">
    <property name="ireport.zoom" value="1.3636363636363635"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="56"/>
    <style name="table">
        <box>
            <pen lineWidth="1.0" lineColor="#000000"/>
        </box>
    </style>
    <style name="table_TH" mode="Opaque" backcolor="#F0F8FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table_CH" mode="Opaque" backcolor="#BFE1FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table_TD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <subDataset name="Table Dataset 1" uuid="836bbd96-4049-40fd-be32-86fc2832a1d2"/>
    <parameter name="idProyecto" class="java.lang.Integer">
        <defaultValueExpression><![CDATA[]]></defaultValueExpression>
    </parameter>
    <parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA["com/appglass/jasper/"]]></defaultValueExpression>
    </parameter>
    <parameter name="rutaImagenes" class="java.lang.String"/>
    <parameter name="archivoDoc" class="java.lang.Boolean">
        <defaultValueExpression><![CDATA[false]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT
     usuarios.`nombre` AS usuarios_nombre,
     proyectos.`nombreProyecto` AS proyectos_nombreProyecto,
     proyectos.`fecha` AS proyectos_fecha,
     proyectos.`version` AS proyectos_version,
     secciones.`tipoPuertaIzquierda` AS secciones_tipoPuertaIzquierda,
     secciones.`tipoPuertaDerecha` AS secciones_tipoPuertaDerecha

FROM
     `secciones` secciones INNER JOIN `proyectos` proyectos ON secciones.`id_proyecto` = proyectos.`id` INNER JOIN `usuarios` usuarios ON proyectos.`id_usuario` = usuarios.`id`
WHERE
     proyectos.id = $P{idProyecto}]]>
    </queryString>
    <field name="usuarios_nombre" class="java.lang.String"/>
    <field name="proyectos_nombreProyecto" class="java.lang.String"/>
    <field name="proyectos_fecha" class="java.sql.Date"/>
    <field name="proyectos_version" class="java.lang.Integer"/>
    <field name="secciones_tipoPuertaIzquierda" class="java.lang.Integer"/>
    <field name="secciones_tipoPuertaDerecha" class="java.lang.Integer"/>
    <variable name="pEspecial1" class="java.lang.Integer" calculation="System">
        <variableExpression><![CDATA[]]></variableExpression>
        <initialValueExpression><![CDATA[0]]></initialValueExpression>
    </variable>
    <variable name="pEspecial2" class="java.lang.Integer" calculation="System">
        <variableExpression><![CDATA[]]></variableExpression>
        <initialValueExpression><![CDATA[0]]></initialValueExpression>
    </variable>
    <variable name="rutaImagen1" class="java.lang.String">
        <variableExpression><![CDATA["images\\"]]></variableExpression>
    </variable>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="97" splitType="Stretch">
            <textField>
                <reportElement uuid="169e3810-2e48-4eea-94c9-64162d754b44" x="170" y="32" width="279" height="20"/>
                <textElement>
                    <font fontName="DejaVu Sans" size="14" isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{proyectos_nombreProyecto}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement uuid="683e06eb-b374-4ca0-8ee5-c80eb252da49" x="121" y="59" width="100" height="20"/>
                <textElement textAlignment="Right">
                    <font fontName="DejaVu Sans"/>
                </textElement>
                <textFieldExpression><![CDATA[new SimpleDateFormat("dd/MM/yyyy ").format($F{proyectos_fecha})]]></textFieldExpression>
            </textField>
            <image>
                <reportElement uuid="b4ecda02-fa51-45b5-8ee8-c999a48e9051" x="438" y="0" width="99" height="96"/>
                <imageExpression><![CDATA[$P{SUBREPORT_DIR} + $P{rutaImagenes} + "logoreport.jpg"]]></imageExpression>
            </image>
            <textField>
                <reportElement uuid="296afbcb-3d7f-4653-8287-92e582927039" x="21" y="32" width="149" height="20"/>
                <textElement>
                    <font fontName="DejaVu Sans" size="14"/>
                </textElement>
                <textFieldExpression><![CDATA[$R{general.resumenproy} + ":"]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true">
                <reportElement uuid="b8d89e63-7c47-4c80-92cc-5cdfccb8c35d" x="21" y="59" width="100" height="20"/>
                <textElement>
                    <font fontName="DejaVu Sans"/>
                </textElement>
                <textFieldExpression><![CDATA[$R{general.fechacrea} + ":"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement uuid="683e06eb-b374-4ca0-8ee5-c80eb252da49" x="537" y="1" width="17" height="13"/>
                <textElement textAlignment="Right">
                    <font size="6"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{proyectos_version} + ($P{archivoDoc} ? "*" : "")]]></textFieldExpression>
            </textField>
        </band>
    </title>
    <pageHeader>
        <band splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="22" splitType="Stretch">
            <textField>
                <reportElement uuid="0234364a-ef1b-4456-b3a4-d4d693b3c595" x="21" y="0" width="235" height="22"/>
                <textElement>
                    <font fontName="DejaVu Sans" size="12" isBold="true" isUnderline="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$R{cristal.titulo}]]></textFieldExpression>
            </textField>
        </band>
    </columnHeader>
    <detail>
        <band height="50" splitType="Immediate">
            <subreport>
                <reportElement uuid="88eb3e2c-c5ec-4a37-839e-7db3c0042d68" stretchType="RelativeToBandHeight" isPrintRepeatedValues="false" x="21" y="0" width="534" height="50" isRemoveLineWhenBlank="true">
                    <printWhenExpression><![CDATA[new Boolean($V{REPORT_COUNT}.intValue() ==1)]]></printWhenExpression>
                </reportElement>
                <subreportParameter name="REPORT_LOCALE">
                    <subreportParameterExpression><![CDATA[$P{REPORT_LOCALE}]]></subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="SUBREPORT_DIR">
                    <subreportParameterExpression><![CDATA[$P{SUBREPORT_DIR}]]></subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="idProyecto">
                    <subreportParameterExpression><![CDATA[$P{idProyecto}]]></subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="REPORT_RESOURCE_BUNDLE">
                    <subreportParameterExpression><![CDATA[$P{REPORT_RESOURCE_BUNDLE}]]></subreportParameterExpression>
                </subreportParameter>
                <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                <returnValue subreportVariable="pEspecial1" toVariable="pEspecial1"/>
                <returnValue subreportVariable="pEspecial2" toVariable="pEspecial2"/>
                <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "report2_cristalA.jasper"]]></subreportExpression>
            </subreport>
        </band>
        <band height="51" splitType="Immediate">
            <subreport>
                <reportElement uuid="3a84aa6f-11a5-41d4-bdc6-2d28a1b425ad" isPrintRepeatedValues="false" mode="Transparent" x="21" y="0" width="534" height="50" isRemoveLineWhenBlank="true" isPrintInFirstWholeBand="true">
                    <printWhenExpression><![CDATA[new Boolean($V{REPORT_COUNT}.intValue() ==1) && (($V{pEspecial1} > 0) || ($V{pEspecial2} > 0))]]></printWhenExpression>
                </reportElement>
                <subreportParameter name="REPORT_LOCALE">
                    <subreportParameterExpression><![CDATA[$P{REPORT_LOCALE}]]></subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="SUBREPORT_DIR">
                    <subreportParameterExpression><![CDATA[$P{SUBREPORT_DIR}]]></subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="idProyecto">
                    <subreportParameterExpression><![CDATA[$P{idProyecto}]]></subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="REPORT_RESOURCE_BUNDLE">
                    <subreportParameterExpression><![CDATA[$P{REPORT_RESOURCE_BUNDLE}]]></subreportParameterExpression>
                </subreportParameter>
                <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "report2_cristalB.jasper"]]></subreportExpression>
            </subreport>
        </band>
    </detail>
    <summary>
        <band height="287" splitType="Immediate">
            <image scaleImage="FillFrame" evaluationTime="Report">
                <reportElement uuid="d9af6aa4-b640-4c0f-81b0-aca30c23ea02" isPrintRepeatedValues="false" mode="Transparent" x="59" y="23" width="111" height="223" isRemoveLineWhenBlank="true">
                    <printWhenExpression><![CDATA[($V{pEspecial1} > 0)]]></printWhenExpression>
                </reportElement>
                <imageExpression><![CDATA[$P{SUBREPORT_DIR} + $P{rutaImagenes} + "puertapomo.jpg"]]></imageExpression>
            </image>
            <image scaleImage="FillFrame" evaluationTime="Report">
                <reportElement uuid="a0a422b4-8c27-4d73-a5bb-4724f091b48e" isPrintRepeatedValues="false" x="221" y="23" width="111" height="223" isRemoveLineWhenBlank="true">
                    <printWhenExpression><![CDATA[($V{pEspecial2} > 0)]]></printWhenExpression>
                </reportElement>
                <imageExpression><![CDATA[$P{SUBREPORT_DIR} + $P{rutaImagenes} + "pcerradura2.jpg"]]></imageExpression>
            </image>
            <textField evaluationTime="Report">
                <reportElement uuid="8837a7fd-db0e-40ef-9e65-eff14bc16e69" isPrintRepeatedValues="false" x="59" y="246" width="111" height="17" isRemoveLineWhenBlank="true">
                    <printWhenExpression><![CDATA[($V{pEspecial1} > 0)]]></printWhenExpression>
                </reportElement>
                <textElement textAlignment="Center">
                    <font fontName="DejaVu Sans"/>
                </textElement>
                <textFieldExpression><![CDATA[$R{cristal.tipo1}]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Report">
                <reportElement uuid="cb7abd52-2136-4f6d-8b3b-df6e8679ede7" isPrintRepeatedValues="false" x="221" y="246" width="111" height="17" isRemoveLineWhenBlank="true">
                    <printWhenExpression><![CDATA[($V{pEspecial2} > 0)]]></printWhenExpression>
                </reportElement>
                <textElement textAlignment="Center">
                    <font fontName="DejaVu Sans"/>
                </textElement>
                <textFieldExpression><![CDATA[$R{cristal.tipo2}]]></textFieldExpression>
            </textField>
        </band>
    </summary>
</jasperReport>

The stack of the error clearly states: java.sql.SQLException: No database selected but the database works well. Other methods of the app can select and insert data in the DB (MySQL). So I guess the problem is in JasperReports configuration. Does anybody have an idea of what is going on? Thank you!


Answer:

I found the problem. I had this in the server (glassfish)

"URL"="jdbc:mysql://localhost:3306/" 

But I should have instead:

"URL"="jdbc:mysql://localhost:3306/databasename"

Now it works well.

Question:

Trying to upgrade reports from JasperReports 3.5.3 to 6.3.1.

The snippet from jrxml:

<queryString language = "plsql"> <![CDATA[{call RPT_OCP($P{CUR},$P{ORACLE_REF_CURSOR})}]]> </queryString>

While trying to compile the report, facing the following exception,

Caused by: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Class com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory not found.
     [java]     at net.sf.jasperreports.engine.design.JRDesignDataset.queryLanguageChanged(JRDesignDataset.java:1255)
     [java]     at net.sf.jasperreports.engine.design.JRDesignDataset.setQuery(JRDesignDataset.java:695)
     [java]     at net.sf.jasperreports.engine.design.JasperDesign.setQuery(JasperDesign.java:881)
     [java]     ... 37 more

The JasperReports of 6.3.1 version does not have any jasperreport-extentions jar, what would be the work around for this?


Answer:

You should check that the handler (Executer) for plsql language is defined at jasperreports.properties file.

The right definition (for 6.x version) is:

net.sf.jasperreports.query.executer.factory.plsql=net.sf.jas‌​perreports.engine.qu‌​ery.PlSqlQueryExecut‌​erFactory

Maybe you have something different since the previous version of JasperReports engine.

BTW, JasperReports 3.5.3 did not support this extenstion (plsql). I think that using com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory at your case was some kind of stub/fix.

Question:

I'm trying to display data between two dates through Jasper report and these are my codes:

    String date1=jDateChooser1.getDateFormatString();
    String date2=jDateChooser2.getDateFormatString();
    try {

        Connection conn = null;

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    } catch (ClassNotFoundException ex) {
        Logger.getLogger(facilitiReports.class.getName()).log(Level.SEVERE, null, ex);
    }

    String url = "jdbc:sqlserver://localhost:1433;databaseName=bradb";
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(url, "sa", "oraclee");
    } catch (SQLException ex) {
        Logger.getLogger(facilitiReports.class.getName()).log(Level.SEVERE, null, ex);
    }

    JasperDesign jd = null;
    try {
        jd = JRXmlLoader.load("D:\\NetBeansProjects\\bra\\src\\facilitiReport.jrxml");
    } catch (JRException ex) {
        Logger.getLogger(facilitiReports.class.getName()).log(Level.SEVERE, null, ex);
    }

    String sql="SELECT * FROM facilitiTable WHERE facilitiDate BETWEEN='" + date1+ "' AND '" + date2+ "' ";
    JRDesignQuery newq=new JRDesignQuery();
    newq.setText(sql);

    jd.setQuery(newq);
    JasperReport jr = null;
    try {
        jr = JasperCompileManager.compileReport(jd);

    } catch (JRException ex) {
        Logger.getLogger(facilitiReports.class.getName()).log(Level.SEVERE, null, ex);
    }

    JasperPrint jp = null;
    try {
        jp = JasperFillManager.fillReport(jr, null, conn);
    } catch (JRException ex) {
        Logger.getLogger(facilitiReports.class.getName()).log(Level.SEVERE, null, ex);
    }

    JasperViewer.viewReport(jp);
    try {

            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(facilitiReports.class.getName()).log(Level.SEVERE, null, ex);
        }

the error that appears,is:

net.sf.jasperreports.engine.JRException: Error executing SQL statement for : null at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240) 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.JRBaseFiller.fill(JRBaseFiller.java:845) at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:61) at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:446) at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:849)


Answer:

done,, the error were in these two lines:

String date1=jDateChooser1.getDateFormatString();
String date2=jDateChooser2.getDateFormatString();

and final codes are:

String date1 = ((JTextField) jDateChooser1.getDateEditor().getUiComponent()).getText();
    String date2 = ((JTextField) jDateChooser2.getDateEditor().getUiComponent()).getText();
    try {

        Connection conn = null;

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    } catch (ClassNotFoundException ex) {
        Logger.getLogger(facilitiReports.class.getName()).log(Level.SEVERE, null, ex);
    }

    String url = "jdbc:sqlserver://localhost:1433;databaseName=bradb";
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(url, "sa", "oraclee");
    } catch (SQLException ex) {
        Logger.getLogger(facilitiReports.class.getName()).log(Level.SEVERE, null, ex);
    }

    JasperDesign jd = null;
    try {
        jd = JRXmlLoader.load("E:\\bra\\src\\facilitiReport2.jrxml");
    } catch (JRException ex) {
        Logger.getLogger(facilitiReports.class.getName()).log(Level.SEVERE, null, ex);
    }
    String sql = "SELECT * FROM facilitiTable WHERE deptDate BETWEEN '" + date1 + "' AND '" + date2 + "' ";
    JRDesignQuery newq = new JRDesignQuery();
    newq.setText(sql);

    jd.setQuery(newq);
    JasperReport jr = null;
    try {
        jr = JasperCompileManager.compileReport(jd);

    } catch (JRException ex) {
        Logger.getLogger(facilitiReports.class.getName()).log(Level.SEVERE, null, ex);
    }

    JasperPrint jp = null;
    try {
        jp = JasperFillManager.fillReport(jr, null, conn);
    } catch (JRException ex) {
        Logger.getLogger(facilitiReports.class.getName()).log(Level.SEVERE, null, ex);
    }

    JasperViewer.viewReport(jp);
    try {
        conn.close();

    } catch (SQLException ex) {
        Logger.getLogger(facilitiReports.class.getName()).log(Level.SEVERE, null, ex);
    }