Jump to content
JasperReports Library 7.0 is now available ×

PostgreSQL Jasper Reporting Server Error


Recommended Posts

I am trying to run the following query in an jrxml file. I can run the query just fine using the same jrxml file with ireports. Both iReports and Jasper Reporting server are usinmg the same datasource. When I run the qury from Jasper Reporting Server I am getting the error below. At first thought I thought the issue was with the postgresql. But I found I am use the older driver and the query still works in iReports but not in Jasper Reporting. I am using the postgresql-8.3-604.jdbc3.jar. I have put this driver in C:Program Filesjasperserver-3.1apache-tomcatcommonlib This issue is holding us up to deploying Jasper Reporting Server into production. Please help!!!!!

The query is

  <![CDATA[sELECT * FROM public.sp_jcemployeeutilization_2()
as jc_employeeutilization_rec(
superempnum  bigint, 
employeename varchar(25),
empnum bigint,
shift bigint,
hr_totalhours bigint,
supervisorname varchar(25),
jc_totalhours  bigint,
jc_indirecthours bigint,
jc_unaccountedhours bigint,
jc_jobcost_percentage FLOAT,
jc_indirect_percentage FLOAT,
jc_unaccounted_percentage FLOAT)
order by shift,superempnum, employeename;]]>


r Trace

com.jaspersoft.jasperserver.api.JSExceptionWrapper: Error executing SQL statement for : JobCost-Employee Utilization

net.sf.jasperreports.engine.JRException: Error executing SQL statement for : JobCost-Employee Utilization

org.postgresql.util.PSQLException: ERROR: transaction is read-only


Post Edited by larry conklin at 01/07/09 19:33
Link to comment
Share on other sites

  • Replies 15
  • Created
  • Last Reply

Top Posters In This Topic

The exception is caused by creating the table on a read-only connection.

I see that you thought you had a fix for this: jasperforge.org/plugins/espforum/view.php through updating the driver.

How are you setting up the report data source? As a JDBC or JNDI connection?

I have to say that creating a table like this as part of running the report seems very strange.




Post Edited by Sherman Wood at 01/07/09 21:24
Link to comment
Share on other sites

Sherman, you are correct in that I am creating a temp table to perform certain sql data manipulations. I then select everything out of my temp table for printing. I am doing alot of sql data manipulations to my data to get it ready for printing. I am using JDBC. I have assigned permissions to the datasource as adminstr in hopes to get around this issue. but it hasn't helped.
Link to comment
Share on other sites

If you are using a JDBC data source in JasperServer, there is a connection pool automatically created behind the scenes with DBCP. I think that the auto commit property is not being managed properly. Try using a JDBC URL like:





Link to comment
Share on other sites



I tried both of these url's for the JDBC connection




I am still getting the transaction read-only error. Both connection string work when I click on the test connection button. My biggest frstration is that this query works in iReports. Isn't iReports are of Jasper? Why would it work there but not in the jasper reporting server?



Link to comment
Share on other sites

JasperServer creates read-only connections for JDBC data sources.  Switching to a JNDI data source would allow you to control how the connections are created, which is not currently possible for plain JDBC data sources.

Hence you could workaround the issue by defining a JNDI data source which refers a resource which is configured with defaultReadOnly="false".



Link to comment
Share on other sites


Lucian Great picture of a dog. Is it a border collie? I have a border collie for search and resuce.

She's a mixed-breed dog (as far as I can tell).  I took her from a shelter and I don't know anything about the parents; there might some border collie ancestry as she does look a little bit like one.

On the JNDI can I use that without LDAP?

Yes.  To define a JNDI datasource, you need to edit $TOMCAT/conf/Catalina/localhost/jasperserver.xml and create a new Resource element; you would set defaultReadOnly="false" for the datasource.  Once you have configured the Tomcat JNDI resource, start the server and create a JNDI data source in JasperServer.



Link to comment
Share on other sites

In my $TOMCAT/conf/Catalina/localhost/ (C:Program Filesjasperserver-3.1apache-tomcatconfCatalinalocalhost) directory all I have is host-manager.xml & manager.xml Ok now if I understand you correctly


1. I am going to create a file called jasperserver.xml in this directory.


2. In this file I am going to put resource name and resource parms pointing to my jdbc connection.


3. in jasperreporting server create a jndi connection that has the resource name.


4. where does defaultReadOnly="false" go? in jasperserver appended to the resource name or in the resource file as a parm along with user, password, etc?


Link to comment
Share on other sites

If you don't have a conf/Catalina/localhost/jasperserver.xml file, you should try webapps/jasperserver/META-INF/context.xml instead.  That file should contain at least one resource named jdbc/jasperserver.  You need to define the new JNDI resource in the same file; defaultReadOnly="true" would be an attribute of the JNDI resource.

Link to comment
Share on other sites

Yea!!! I got it to work.  In the context.xml file in webapps I put the following code.

<Resource name="jdbc/postgres" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
            username="postgres" password="xxxxxx" driverClassName="org.postgresql.Driver"

I have test this connection and it will allow me to call an store proc that has a create temp table statement in it. This work around solves my problem with jdbc connections being read only.

Link to comment
Share on other sites

  • 1 month later...


I am having the same exact problem, so I'm glad that I founf this post. Is the context.xml file that you edited located at C:Program Filesjasperserver-pro-3.0apache-tomcatwebappsjasperserver-proMETA-INF ?

Also, this is still a JDBC connection? not a JDNI as the tech suggested?


Link to comment
Share on other sites

  • 2 weeks later...
  • 10 months later...

I have the same need, only for an Ingres database.  I create temp tables in reports to manipulate data.

Here's what I added in $CATALINA/webapps/jasperserver/META-INF/context.xml..

<Resource name="jdbc/dbtest" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
        username="ingres" password="xxxxxx"

After saving the file, I restarted tomcat and mysql.  I then added the JNDI datasource in Jasperserver using the service name "jdbc/dbtest", as named in context.xml.  When I test the JNDI connection it just tells me "test failed".  I can currently use the same JDBC connection in Jasperserver and it connects fine.

I looked in the catalina.out log file and saw the following message which seems to have occurred when I tested the connection..

15:23:38,347 ERROR DbcpDataSourceFactory,http-8443-Processor22:61 - java.lang.ClassNotFoundException: com.ingres.jdbc.IngresDriver
java.lang.ClassNotFoundException: com.ingres.jdbc.IngresDriver
        at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1355)
        at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1201)
        at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:242)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.DbcpDataSourceFactory.registerDriver(DbcpDataSourceFactory.java:59)

Why does it find the Ingres driver when I use the class as a normal JDBC connection, but not within a JNDI connection?



Post Edited by chaddn at 02/10/2010 22:06
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...