Settings required for generating large reports

Posted on August 18, 2010 at 6:25am

Hi All,

I thought I would add this to the forums as I spent a lot of time getting jasper server to generate very large reports (80M on 2 million records)... Hopefully this will save someone a lot of time...

First, we all know about the virtualizer but this only virtualizes report pages (and not the result set). By default jasper will fetch the entire result set into memory before it starts to generate the report. This is very fast but not very memory efficient. The solution to this problem is to get jdbc to "stream" result's back to jasper server... In order to stream the result set you need to set your driver to stream results... The settings below work for mysql but I assume they work for others as well. So here are the changes you will want to make and why:

Modify applicationContext.xml:

Change the com.jaspersoft.jasperserver.api.engine.common.service.impl.FileVirtualizerFactory to com.jaspersoft.jasperserver.api.engine.common.service.impl.SwapFileVirtualizerFactory and comment out the property directory. It should look like this when you are done...

</bean>

This will change jasper to use a single file for paging instead of multiple files per page... The report I generated was 400,000 pages so obviously that is a lot of files to be creating...

jasperreports.properties

Use these settings...

net.sf.jasperreports.governor.max.pages.enabled=true

net.sf.jasperreports.governor.timeout.enabled=true

net.sf.jasperreports.jdbc.concurrency=readOnly

net.sf.jasperreports.jdbc.fetch.size=-2147483648

net.sf.jasperreports.jdbc.holdability=close

net.sf.jasperreports.jdbc.result.set.type=forwardOnly

The last four settings are the "trick" to put the mysql connector into "streaming" mode... It will not load the entire result set into memory and then try and build the report.

Lastly you will need to modify your mysql configuration so the jdbc url you use has the following:

?maxAllowedPacket=104857600

Also you need to modify your my.cnf (mysql configuration) to include the following in the [mysqld] section:

max_allowed_packet = 100M

So what do these last two settings do? When jasper server generates a large report with the scheduler it will try and store the results into the repository. Without these settings you will get a nasty error when jasper tries to insert the output of the report into the repository saying something like "Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (85530691 > 16777216). You can change this value on the server by setting the max_allowed_packet' variable.".

With the above settings you can write a generated report up to 100M into the repository.. In production we use 160M to give some head room for larger reports...

I hope this helps save someone some time in tuning jasper server. If it helps you feel free to give me some karma points ;^)...

BTW) Using these settings I generated a 80M report (csv) on 2 million rows with only 512M allocated to the java heap!.. Obviously, we would use more Heap Space in production but I was looking to restrict the JVM's memory to ensure that the above settings really work.

-LeoD

JasperReports® Server

ldangelo

Joined: Dec 14 2006 - 1:31pm

Last seen: 12 years 7 months ago

10 Answers:

Posted on August 18, 2010 at 10:11am

This is really great Information > we have been using FileVirtulizer in internal Performance testing and tit really helps in finding balance between memory vs response time.

thanks for putting this on Forum :)

Ramnik Kaur

Senior QA Engineer

ramnik

331

Joined: Mar 16 2007 - 9:10am

Last seen: 12 years 4 months ago

Posted on September 5, 2010 at 11:07pm

Thanks - I needed this post.

P.S. You don't need to comment out "directory", just change it to:

<property name="tempDirectory" value="${java.io.tmpdir}"></property>

Post Edited by adamd at 09/06/2010 06:08

adamd

Joined: Sep 5 2010 - 11:00pm

Last seen: 8 years 10 months ago

Posted on September 22, 2010 at 8:27pm

Thanks for this post. I was really wondering if i can generate the large reports using jasper server.

I have a simillar requirement wherein i need to schedule the report which would be having around 1 million records.

When i tried to run such big report on jasperserver , obviously i got OutOfMemory Exception.

Also, i need to store the generated report in file system in stead of repository.

We have jasperserver 3.5. I am not sure what version of jasper server you are using because i am not able to find

the jasperreports.properties file in it. So it would be great if you can throw some light on it.

Also, if you can help with report getting stored on the file system , would appreciate it.

Thanks & Regards,

Bhushan

bhushan_makade

Joined: Jul 7 2010 - 12:37pm

Last seen: 9 years 1 week ago

Posted on April 4, 2011 at 10:31pm

Hi,

the solution is working for generating the large reports but after that its not working on small reports........

so its givig error for my other reports....so please let me know the soution for the same

shippu4u

Joined: Dec 8 2008 - 3:24am

Last seen: 10 years 7 months ago

What's the error you're getting?

ernestoo - 8 years 3 months ago

add comment

Posted on May 5, 2011 at 10:08pm

After changing the above seetings .I am able to generate teh large report but its not working for my othasr small reports for tahts its giving error.Unable to prepare query statement.

com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query : 
 
select
spid,spname, serviceclass, servicename,reqresponsemode, sapid,sapname, reqauditid, reqverb, reqtimestamp, responsetime,
( SELECT round(avg(vres.responsetime), 3) FROM vw_message_process_time_detail vres where vres.reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp) AS avgresponsetime,
( SELECT count(*) FROM vw_message_process_time_detail vres WHERE vres.responsetime > 3::numeric and vres.reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp) AS gt3sec
from
vw_message_process_time_detail
where
reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp
order by
spid, serviceclass, reqresponsemode, sapid, reqverb;
 
 
	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:192)
	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:123)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:164)
	at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:676)
	at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:596)
	at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1223)
	at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:853)
	at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:123)
	at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:439)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:748)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:367)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.executeReport(EngineServiceImpl.java:876)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.domain.impl.ReportUnitRequest.execute(ReportUnitRequest.java:60)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.execute(EngineServiceImpl.java:301)
	at com.jaspersoft.jasperserver.war.action.ViewReportAction.executeReport(ViewReportAction.java:329)
	at com.jaspersoft.jasperserver.war.action.ViewReportAction.verifyData(ViewReportAction.java:222)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.webflow.util.DispatchMethodInvoker.invoke(DispatchMethodInvoker.java:103)
	at org.springframework.webflow.action.MultiAction.doExecute(MultiAction.java:136)
	at org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:203)
	at org.springframework.webflow.engine.AnnotatedAction.execute(AnnotatedAction.java:142)
	at org.springframework.webflow.engine.ActionExecutor.execute(ActionExecutor.java:61)
	at org.springframework.webflow.engine.ActionState.doEnter(ActionState.java:180)
	at org.springframework.webflow.engine.State.enter(State.java:200)
	at org.springframework.webflow.engine.Transition.execute(Transition.java:229)
	at org.springframework.webflow.engine.TransitionableState.onEvent(TransitionableState.java:112)
	at org.springframework.webflow.engine.Flow.onEvent(Flow.java:572)
	at org.springframework.webflow.engine.impl.RequestControlContextImpl.signalEvent(RequestControlContextImpl.java:207)
	at org.springframework.webflow.engine.impl.FlowExecutionImpl.signalEvent(FlowExecutionImpl.java:214)
	at org.springframework.webflow.executor.FlowExecutorImpl.resume(FlowExecutorImpl.java:238)
	at sun.reflect.GeneratedMethodAccessor436.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)
	at org.acegisecurity.intercept.method.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:66)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)
	at $Proxy86.resume(Unknown Source)
	at org.springframework.webflow.executor.support.FlowRequestHandler.handleFlowRequest(FlowRequestHandler.java:115)
	at org.springframework.webflow.executor.mvc.FlowController.handleRequestInternal(FlowController.java:170)
	at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
	at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:45)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:820)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:755)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:396)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:360)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at logging.ReportLog.doFilter(ReportLog.java:39)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.jaspersoft.jasperserver.war.common.UploadMultipartFilter.doFilter(UploadMultipartFilter.java:86)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:264)
	at org.acegisecurity.ui.switchuser.SwitchUserProcessingFilter.doFilter(SwitchUserProcessingFilter.java:335)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)
	at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at com.jaspersoft.jasperserver.api.metadata.user.service.impl.MetadataAuthenticationProcessingFilter.doFilter(MetadataAuthenticationProcessingFilter.java:142)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at com.jaspersoft.jasperserver.war.util.RequestParameterAuthenticationFilter.doFilter(RequestParameterAuthenticationFilter.java:97)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessingFilter.java:181)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:178)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:178)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:191)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)
	at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:90)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.jaspersoft.jasperserver.war.util.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:70)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:138)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
	at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179)
	at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
	at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
	at org.josso.tc55.agent.SSOAgentValve.invoke(SSOAgentValve.java:271)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:241)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:580)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
	at java.lang.Thread.run(Thread.java:619)
Caused by: org.postgresql.util.PSQLException: Fetch size must be a value greater to or equal to 0.
	at org.postgresql.jdbc2.AbstractJdbc2Statement.setFetchSize(AbstractJdbc2Statement.java:2731)
	at org.apache.commons.dbcp.DelegatingStatement.setFetchSize(DelegatingStatement.java:276)
	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:169)
	... 109 more

net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query : select spid,spname, serviceclass, servicename,reqresponsemode, sapid,sapname, reqauditid, reqverb, reqtimestamp, responsetime, ( SELECT round(avg(vres.responsetime), 3) FROM vw_message_process_time_detail vres where vres.reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp) AS avgresponsetime, ( SELECT count(*) FROM vw_message_process_time_detail vres WHERE vres.responsetime > 3::numeric and vres.reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp) AS gt3sec from vw_message_process_time_detail where reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp order by spid, serviceclass, reqresponsemode, sapid, reqverb;

net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query : 
 
select
spid,spname, serviceclass, servicename,reqresponsemode, sapid,sapname, reqauditid, reqverb, reqtimestamp, responsetime,
( SELECT round(avg(vres.responsetime), 3) FROM vw_message_process_time_detail vres where vres.reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp) AS avgresponsetime,
( SELECT count(*) FROM vw_message_process_time_detail vres WHERE vres.responsetime > 3::numeric and vres.reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp) AS gt3sec
from
vw_message_process_time_detail
where
reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp
order by
spid, serviceclass, reqresponsemode, sapid, reqverb;
 
 
	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:192)
	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:123)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:164)
	at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:676)
	at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:596)
	at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1223)
	at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:853)
	at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:123)
	at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:439)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:748)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:367)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.executeReport(EngineServiceImpl.java:876)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.domain.impl.ReportUnitRequest.execute(ReportUnitRequest.java:60)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.execute(EngineServiceImpl.java:301)
	at com.jaspersoft.jasperserver.war.action.ViewReportAction.executeReport(ViewReportAction.java:329)
	at com.jaspersoft.jasperserver.war.action.ViewReportAction.verifyData(ViewReportAction.java:222)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.webflow.util.DispatchMethodInvoker.invoke(DispatchMethodInvoker.java:103)
	at org.springframework.webflow.action.MultiAction.doExecute(MultiAction.java:136)
	at org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:203)
	at org.springframework.webflow.engine.AnnotatedAction.execute(AnnotatedAction.java:142)
	at org.springframework.webflow.engine.ActionExecutor.execute(ActionExecutor.java:61)
	at org.springframework.webflow.engine.ActionState.doEnter(ActionState.java:180)
	at org.springframework.webflow.engine.State.enter(State.java:200)
	at org.springframework.webflow.engine.Transition.execute(Transition.java:229)
	at org.springframework.webflow.engine.TransitionableState.onEvent(TransitionableState.java:112)
	at org.springframework.webflow.engine.Flow.onEvent(Flow.java:572)
	at org.springframework.webflow.engine.impl.RequestControlContextImpl.signalEvent(RequestControlContextImpl.java:207)
	at org.springframework.webflow.engine.impl.FlowExecutionImpl.signalEvent(FlowExecutionImpl.java:214)
	at org.springframework.webflow.executor.FlowExecutorImpl.resume(FlowExecutorImpl.java:238)
	at sun.reflect.GeneratedMethodAccessor436.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)
	at org.acegisecurity.intercept.method.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:66)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)
	at $Proxy86.resume(Unknown Source)
	at org.springframework.webflow.executor.support.FlowRequestHandler.handleFlowRequest(FlowRequestHandler.java:115)
	at org.springframework.webflow.executor.mvc.FlowController.handleRequestInternal(FlowController.java:170)
	at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
	at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:45)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:820)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:755)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:396)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:360)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at logging.ReportLog.doFilter(ReportLog.java:39)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.jaspersoft.jasperserver.war.common.UploadMultipartFilter.doFilter(UploadMultipartFilter.java:86)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:264)
	at org.acegisecurity.ui.switchuser.SwitchUserProcessingFilter.doFilter(SwitchUserProcessingFilter.java:335)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)
	at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at com.jaspersoft.jasperserver.api.metadata.user.service.impl.MetadataAuthenticationProcessingFilter.doFilter(MetadataAuthenticationProcessingFilter.java:142)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at com.jaspersoft.jasperserver.war.util.RequestParameterAuthenticationFilter.doFilter(RequestParameterAuthenticationFilter.java:97)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessingFilter.java:181)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:178)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:178)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:191)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)
	at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:90)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.jaspersoft.jasperserver.war.util.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:70)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:138)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
	at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179)
	at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
	at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
	at org.josso.tc55.agent.SSOAgentValve.invoke(SSOAgentValve.java:271)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:241)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:580)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
	at java.lang.Thread.run(Thread.java:619)
Caused by: org.postgresql.util.PSQLException: Fetch size must be a value greater to or equal to 0.
	at org.postgresql.jdbc2.AbstractJdbc2Statement.setFetchSize(AbstractJdbc2Statement.java:2731)
	at org.apache.commons.dbcp.DelegatingStatement.setFetchSize(DelegatingStatement.java:276)
	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:169)
	... 109 more

org.postgresql.util.PSQLException: Fetch size must be a value greater to or equal to 0.

org.postgresql.util.PSQLException: Fetch size must be a value greater to or equal to 0.
	at org.postgresql.jdbc2.AbstractJdbc2Statement.setFetchSize(AbstractJdbc2Statement.java:2731)
	at org.apache.commons.dbcp.DelegatingStatement.setFetchSize(DelegatingStatement.java:276)
	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:169)
	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:123)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:164)
	at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:676)
	at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:596)
	at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1223)
	at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:853)
	at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:123)
	at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:439)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:748)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:367)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.executeReport(EngineServiceImpl.java:876)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.domain.impl.ReportUnitRequest.execute(ReportUnitRequest.java:60)
	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.execute(EngineServiceImpl.java:301)
	at com.jaspersoft.jasperserver.war.action.ViewReportAction.executeReport(ViewReportAction.java:329)
	at com.jaspersoft.jasperserver.war.action.ViewReportAction.verifyData(ViewReportAction.java:222)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.webflow.util.DispatchMethodInvoker.invoke(DispatchMethodInvoker.java:103)
	at org.springframework.webflow.action.MultiAction.doExecute(MultiAction.java:136)
	at org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:203)
	at org.springframework.webflow.engine.AnnotatedAction.execute(AnnotatedAction.java:142)
	at org.springframework.webflow.engine.ActionExecutor.execute(ActionExecutor.java:61)
	at org.springframework.webflow.engine.ActionState.doEnter(ActionState.java:180)
	at org.springframework.webflow.engine.State.enter(State.java:200)
	at org.springframework.webflow.engine.Transition.execute(Transition.java:229)
	at org.springframework.webflow.engine.TransitionableState.onEvent(TransitionableState.java:112)
	at org.springframework.webflow.engine.Flow.onEvent(Flow.java:572)
	at org.springframework.webflow.engine.impl.RequestControlContextImpl.signalEvent(RequestControlContextImpl.java:207)
	at org.springframework.webflow.engine.impl.FlowExecutionImpl.signalEvent(FlowExecutionImpl.java:214)
	at org.springframework.webflow.executor.FlowExecutorImpl.resume(FlowExecutorImpl.java:238)
	at sun.reflect.GeneratedMethodAccessor436.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)
	at org.acegisecurity.intercept.method.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:66)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)
	at $Proxy86.resume(Unknown Source)
	at org.springframework.webflow.executor.support.FlowRequestHandler.handleFlowRequest(FlowRequestHandler.java:115)
	at org.springframework.webflow.executor.mvc.FlowController.handleRequestInternal(FlowController.java:170)
	at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
	at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:45)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:820)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:755)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:396)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:360)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at logging.ReportLog.doFilter(ReportLog.java:39)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.jaspersoft.jasperserver.war.common.UploadMultipartFilter.doFilter(UploadMultipartFilter.java:86)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:264)
	at org.acegisecurity.ui.switchuser.SwitchUserProcessingFilter.doFilter(SwitchUserProcessingFilter.java:335)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)
	at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at com.jaspersoft.jasperserver.api.metadata.user.service.impl.MetadataAuthenticationProcessingFilter.doFilter(MetadataAuthenticationProcessingFilter.java:142)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at com.jaspersoft.jasperserver.war.util.RequestParameterAuthenticationFilter.doFilter(RequestParameterAuthenticationFilter.java:97)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessingFilter.java:181)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:178)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:178)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:191)
	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
	at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)
	at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:90)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.jaspersoft.jasperserver.war.util.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:70)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:138)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
	at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179)
	at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
	at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
	at org.josso.tc55.agent.SSOAgentValve.invoke(SSOAgentValve.java:271)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:241)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:580)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
	at java.lang.Thread.run(Thread.java:619)

shippu4u

Joined: Dec 8 2008 - 3:24am

Last seen: 10 years 7 months ago

Posted on September 16, 2011 at 5:03am

Hi,

Thanks for this post, i have similar requirement, it is really works. Iam still testing the functionality. I have question does these settings work if i try run multiple reports with large data. Any help is appreciated.

Thanks,

Surya

sendarnav

Joined: May 10 2011 - 1:41am

Last seen: 3 years 3 months ago

Posted on September 21, 2011 at 10:49am

Is it possible to make this work for a report that has subqueries (i.e. a table) or a subreport? When it goes to execute the other query, I get this exception:

java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@51a9bf1f is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
	at com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:2735)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1899)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
	at com.mysql.jdbc.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1606)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2213)

kjordan2001

Joined: Sep 21 2011 - 10:45am

Last seen: 6 years 9 months ago

Posted on September 27, 2011 at 3:06am

I have made the following configuration change in Jasper Server ..

</bean>

How do i know the virtualizer is getting used while report generation.

sendarnav

Joined: May 10 2011 - 1:41am

Last seen: 3 years 3 months ago

Posted on July 22, 2014 at 10:03am

Hi, this works in my iReport 3.7.1 version but just using JRGzipVirtualizer virtualizer, not with JRSwapFileVirtualizer :(

My question is: How can I configure this parameters in jasper grails plugin?

briinamar

Joined: Jul 9 2014 - 9:43am

Last seen: 4 years 11 months ago

Posted on May 1, 2015 at 8:58am

I am also facing the same problem. After making the change, I am getting the error com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query..

Any help appriciated..

debadarshan.behera

Joined: Jan 17 2014 - 1:50am

Last seen: 3 years 12 months ago

Main menu

You are here

Settings required for generating large reports

10 Answers:

org.postgresql.util.PSQLException: Fetch size must be a value greater to or equal to 0.