Please help! ERROR: transaction is read-only

[xad]

I have the exact same problem as topic id #50351, where I have Jasper Server connecting to a postgres stored procedure. The stored procedure uses temp tables and I cannot get by the access restriction.

I've tried different connection arguments such as:

jdbc:postgresql://server:5432/database/
jdbc:postgresql://server:5432/database/defaultReadOnly=false; txnAutoWrap=OFF; disableLocalTxn=true

and every combination of these with no luck.
Ive also modified the content.xml in C:Program Filesjasperserver-pro-3.0apache-tomcatwebappsjasperserver-proMETA-INF to include the resource (this was the other topic's fix):

   <Resource name="jdbc/postgres" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
            username="foo" password="bar" driverClassName="org.postgresql.Driver"
            url="jdbc:postgresql://server:5432/database" defaultReadOnly="false"/>

No luck same issue.

I'm connecting to postgres version 7.4.19.

I have tried numerous configurations and connection arguments with no success. I need to get this up and running!

Please Help!

 

[xad]

Got it. I didn't realize that the context.xml was for the JDNI connection. After I assigned the datasource to the JDNI, everything worked.

[kcsekhar25]

 

The server has encountered an error. Please excuse the inconvenience.

Error Message

 

Error filling report

Error Trace

 

com.jaspersoft.jasperserver.api.JSException: Error filling report at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$FillResultListener.reportFillError(EngineServiceImpl.java:1206) at net.sf.jasperreports.engine.fill.BaseFillHandle.notifyError(BaseFillHandle.java:211) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:135) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:875) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:165) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:831) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1658) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1022) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:897) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:619) Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Sales By Customer at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:168) at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:113) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1087) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:668) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1258) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:877) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120) ... 9 more Caused by: org.postgresql.util.PSQLException: ERROR: transaction is read-only Where: SQL statement "delete from sales_by_cust_rpt_temp where session_id = $1 " PL/pgSQL function "sales_by_cust_rpt" line 12 at SQL statement at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233) ... 16 more

 

[chanduthedev]

I also faced the same issue. and it was working fine after changing from JDBC to JNDI driver.  can some one explain why its not working for JDBC driver and working for JNDI. is there any specific reason for this?

[kkumlien]

Same solution for Redshift: https://community.jaspersoft.com/wiki/server-error-when-using-derived-table-redshift