JasperReports with Subreports - Connection Problem

[J4mie]

I've created a Master Report which has 2 subreports which was created using iReport. The setup for my Master report is as follows:


<bean id="dataSource"> ....</bean>

<bean id="simpleReportCompile" class="org.springframework.web.servlet.view.jasperreports .JasperReportsPdfView">
<property name="url">
<value>/WEB-INF/reports/FAI901.jrxml</value>
</property>
<property name="jdbcDataSource">
<ref bean="dataSource"/>
</property>
<property name="reportCompiler">
<ref bean="jdtCompiler"/>
</property>
<property name="subReportUrls">
<map>
<entry key="FAI901Components">
<value>/WEB-INF/reports/FAI901Components.jasper</value> </entry>
<entry key="FAI901InspectionPlan">
<value>/WEB-INF/reports/FAI901InspectionPlan.jasper</value>
</entry>
</map>
</property>
</bean>

In my master report (FAI901) I link to the 2 subreports and pass them the Connection and the Parameters needed to complete their queries.

The Connection is passed using iReports $P{REPORT_CONNCETION} parameter.

When I run the report in Spring I get the following error 500:


exception

javax.servlet.ServletException: Unable to get next record.
at org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:408)
at org.springframework.web.servlet.FrameworkServlet.d oPost(FrameworkServlet.java:357)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:802)
at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:200)
at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:146)
at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:209)
at ...

root cause

java.sql.SQLException: *** ERROR[8813] Trying to fetch from a statement that is in the closed state.
at com.tandem.t4jdbc.SQLMXMessages.throwSQLException( SQLMXMessages.java:71)
at com.tandem.t4jdbc.InterfaceResultSet.fetchN(Interf aceResultSet.java:484)
at com.tandem.t4jdbc.SQLMXResultSet.next(SQLMXResultS et.java:1457)
at net.sf.jasperreports.engine.JRResultSetDataSource. next(JRResultSetDataSource.java:92)
at net.sf.jasperreports.engine.fill.JRFillDataset.nex t(JRFillDataset.java:715)
at net.sf.jasperreports.engine.fill.JRBaseFiller.next (JRBaseFiller.java:932)
at net.sf.jasperreports.engine.fill.JRVerticalFiller. fillReport(JRVerticalFiller.java:130)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill (JRBaseFiller.java:730)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill (JRBaseFiller.java:632)
at net.sf.jasperreports.engine.fill.JRFiller.fillRepo rt(JRFiller.java:63)
at net.sf.jasperreports.engine.JasperFillManager.fill Report(JasperFillManager.java:402)
at org.springframework.web.servlet.view.jasperreports .AbstractJasperReportsView.fillReport(AbstractJasp erReportsView.java:595)
at org.springframework.web.servlet.view.jasperreports .AbstractJasperReportsView.renderMergedOutputModel (AbstractJasperReportsView.java:527)
at org.springframework.web.servlet.view.AbstractView. render(AbstractView.java:250)
at org.springframework.web.servlet.DispatcherServlet. render(DispatcherServlet.java:928)
at org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:705)
at org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:625)
at org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:392)
at org.springframework.web.servlet.FrameworkServlet.d oPost(FrameworkServlet.java:357)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:802)
at ...

NESTED BY :
net.sf.jasperreports.engine.JRException: Unable to get next record.
at net.sf.jasperreports.engine.JRResultSetDataSource. next(JRResultSetDataSource.java:96)
at net.sf.jasperreports.engine.fill.JRFillDataset.nex t(JRFillDataset.java:715)
at net.sf.jasperreports.engine.fill.JRBaseFiller.next (JRBaseFiller.java:932)
at net.sf.jasperreports.engine.fill.JRVerticalFiller. fillReport(JRVerticalFiller.java:130)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill (JRBaseFiller.java:730)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill (JRBaseFiller.java:632)
at net.sf.jasperreports.engine.fill.JRFiller.fillRepo rt(JRFiller.java:63)
at net.sf.jasperreports.engine.JasperFillManager.fill Report(JasperFillManager.java:402)
at org.springframework.web.servlet.view.jasperreports .AbstractJasperReportsView.fillReport(AbstractJasp erReportsView.java:595)
at org.springframework.web.servlet.view.jasperreports .AbstractJasperReportsView.renderMergedOutputModel (AbstractJasperReportsView.java:527)
at org.springframework.web.servlet.view.AbstractView. render(AbstractView.java:250)
at org.springframework.web.servlet.DispatcherServlet. render(DispatcherServlet.java:928)
at org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:705)
at org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:625)
at org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:392)
at org.springframework.web.servlet.FrameworkServlet.d oPost(FrameworkServlet.java:357)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:802)
at ...
... 46 more


Has anyone come across this problem, or know how I could fix it?

I tried removing the sub-reports from the master and ran it with the spring setup as described above and threre were no problems.

Any help would be much appreciated.

[gilbertoca]

How are you populating the ModelAndView from your controller code?

[J4mie]

ModelAndView code:

public ModelAndView handleSimpleReportPost(HttpServletRequest request, HttpServletResponse response) throws Exception {
Double faiNumber = new Double(request.getParameter("faiNumber"));
Double company = new Double(request.getParameter("company"));
Map model = new HashMap();
model.put("company", company);
model.put("faiNumber", faiNumber);

return new ModelAndView("simpleReportCompile", model);
}

I think the problem is with the JDBC driver not being able to handle multiple connections or statements.

I need to be able to pass the Subreports a new JDBC connection instead of it using the existing connection - how can I do this?

[gilbertoca]

I don't see you setting up the DataSource here. I know you have static statement in your report, but even so you have to pass a DataSource to it, don't you? Did you do it on you webapplicationcontext file?
Why would you need to pass two different connections to the report?
This kind of business rule shouldn't be in the service layer?
Gilberto

[J4mie]

Sorry - I actually set up my dataSource in the servlet.xml file - in the first clip of code I just had the tag there to demonstrate it existed.

Here's the code for my connection:

<bean id="dataSource" class="com.tandem.t4jdbc.SQLMXDataSource">
<property name="url" value="jdbc:t4sqlmx://100.0.0.20:61000/:"/>
<property name="user" value="USER.NAME"/>
<property name="password" value="*****"/>
</bean>

I had a reply from a post on the iReports forum which told me the following:

It's a JDBC error. I saw something of similar with an old version of
JDBC drivers for SQL server.

This happens, I think, when the JDBC driver is not able to handle more
than a statement at the same time.


So this is why I've been looking at ways of passing a seperate connection to the Subreports as Im unsure of any other way which I could get around this problem.

Is passing a java.sql.connection as a Parameter to the Subreport a valid way of passing connections?
Is there another way of getting round this problem of Statements (Trying to fetch from a statement that is in the closed state.)

Thanks for taking the time to help me out.

[gilbertoca]

Are you saying that you have two report, the master and the detail with same datasource connection on ireport, and that each one works alone, but when you run the master which link (using the option "use connection expression" $P{REPORT_CONNECTION}) to detail it doesn't work?

[J4mie]

Yes that's the problem Im having.

Seperately they work fine, but when the Master calls the Subreport I get the error I put in the first post.

[gilbertoca]

Sorry, I'm not being rude with you, but shouldn't you build and test your reports before try it with springmvc and jasperreportsviews?
Chech your database;
Chech your jdbc driver (try with other and inclusive other database);
chech you ireport version an so ...

Gilberto