Crystal Reports 9 on JSP viewer times out

[organickarma]

My organization uses a jsp based CR9 reporting method. The jsp viewer is used to display the report.

All the reports run off java stored procedures and each report has a subreport resting in it which runs off a regular pl/sql procedure and the parameters of the main and sub report are linked. My first assumption here is that the java code is opening and closing all connections for the procedure queries correctly.

A servelet processes the request parameters from the jsp and passes it to the reporting component and calls the report viewer.

Here is what is happening..We have about 25 reports resting in this application and we are facing a problem.

We run the reports for a while and things go fine and response times are ok.. After a bit when one report times out the entire performance starts to degrade....and all reports which were running ok previously start to time out too... I was wondering why this is happening and if it had to do with database connections remaining open after the report has timed out

we have an exception handler that calls the viewer.dispose() when the report times out or if the viewer object or session object is null. Is this not enough to handle the open database connections...

Any help or a push in the right direction would be helpful.

Thanx

 

[organickarma]

An update on this situation and I hope this helps someone help me.

After monitoring the database sessions et al I found that it was one report that was the culprit.

All other reports residing in java stored procedures run their sql code and close the database resources.

Also we have connection pooling which ensures that once a database session/connection has been inactive for a while it times out.

This one report however seems to run forever. Once it times out the database session shows active until the DBA manually kills the process.

The difference with this report is that it runs off a regular PL/SQL based stored procedure and Crystal does not seem to de allocate its database resources on time out.

Any suggestions on how I can terminate this procedure and de allocate the resources when the crystal report times out?