lconklin Posted January 7, 2009 Share Posted January 7, 2009 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<queryString> <![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;]]> </queryString> 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 More sharing options...
swood Posted January 7, 2009 Share Posted January 7, 2009 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. ShermanJaspersoftPost Edited by Sherman Wood at 01/07/09 21:24 Link to comment Share on other sites More sharing options...
lconklin Posted January 7, 2009 Author Share Posted January 7, 2009 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 More sharing options...
swood Posted January 8, 2009 Share Posted January 8, 2009 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:jdbc:postgresql://localhost:5432/jasperserver?defaultAutoCommit=false ShermanJaspersoft Link to comment Share on other sites More sharing options...
lconklin Posted January 8, 2009 Author Share Posted January 8, 2009 Sherman I tried both of these url's for the JDBC connection jdbc:postgresql://localhost:5432/dm_JobCosting/jasperserver?defaultAutoCommit=false jdbc:postgresql://localhost:5432/dm_JobCosting/defaultAutoCommit=false 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 More sharing options...
lucianc Posted January 8, 2009 Share Posted January 8, 2009 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".Regards,Lucian Link to comment Share on other sites More sharing options...
lconklin Posted January 8, 2009 Author Share Posted January 8, 2009 Lucian Great picture of a dog. Is it a border collie? I have a border collie for search and resuce. On the JNDI can I use that without LDAP? Link to comment Share on other sites More sharing options...
lucianc Posted January 8, 2009 Share Posted January 8, 2009 lconklinWrote: 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.HTH,Lucian Link to comment Share on other sites More sharing options...
lconklin Posted January 8, 2009 Author Share Posted January 8, 2009 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 More sharing options...
lucianc Posted January 8, 2009 Share Posted January 8, 2009 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 More sharing options...
lconklin Posted January 9, 2009 Author Share Posted January 9, 2009 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" url="jdbc:postgresql://localhost:5432/dm_JobCosting/defaultReadOnly=false"/>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 More sharing options...
xad Posted March 5, 2009 Share Posted March 5, 2009 Iconklin,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 More sharing options...
swood Posted March 16, 2009 Share Posted March 16, 2009 Yes - the context.xml is in jasperserver-pro/META-INF. This is a JNDI connection. ShermanJaspersoft Link to comment Share on other sites More sharing options...
chaddn Posted February 10, 2010 Share Posted February 10, 2010 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" driverClassName="com.ingres.jdbc.IngresDriver" url="jdbc:ingres://dbtest:II7/dbname"/>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.IngresDriverjava.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?Thanks,ChaddPost Edited by chaddn at 02/10/2010 22:06 Link to comment Share on other sites More sharing options...
mpenningroth Posted February 11, 2010 Share Posted February 11, 2010 You may try to add the jdbc driver jar file to the common/lib directory for tomcat. You may also need to add a resource reference in the web.xml file to the jndi resource. Link to comment Share on other sites More sharing options...
chaddn Posted February 11, 2010 Share Posted February 11, 2010 Thanks for the reply, copying the JDBC driver to the common/lib directory under tomcat worked.-Chadd Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now