Report execution fails due to read-only transaction mode

[januszrut]

Hi, 

my problem is related to Jasperserver 5.1.0 from WAR file installation + Postgresql 9.1 +Tomcat 7 on Ubuntu 12.04 LTE

I have a report that uses a plpgsql function. This function uses temporary tables. It runs properly in iReport but fails when connected to jasperserver report on Tomcat.

The error message is:
org.postgresql.util.PSQLException: ERROR: cannot execute DROP TABLE in a read-only transaction 

I knew this problem: in Jasperserver 4.7 + Postgresql 8.3 I successfuly used a workaround for it: I'd place 'set transaction read write;' at the beginning of the function.
but now when I place this statement in a function, another error comes out:
org.postgresql.util.PSQLException: ERROR: transaction read-write mode must be set before any query Gdzie: SQL statement "set transaction read write" 

Ghrrrr ... ;) And again: no problem when the function is called from cmdline, it fails just in jasper report.

btw. 'defaultReadOnly=false' statements in context.xml and/or in postgresql connection string don't work

Kind regards
Janusz

 

[januszrut]

-----   PROBLEM SOLVED ----- 

Hi,

I got it! I misunderstood the concept of connecting via JDBC directly vs connecting by JNDI!

I created a JNDI connection dedicated for the database (I'd previously connected by JDBC) in context.xml and the problem is solved!

look here: http://community.jaspersoft.com/questions/525672/postgresql-jasper-reporting-server-error

 

[rosie.oneill]

Another option is to set the JDBC flag to false.

 

In your datasource, add this to the JDBC url:

?defaultReadOnly=false

With version 6, you can use attributes to get dynamic datasource URLs, which we use, so JNDI was not an option.

There is also an article here with a server level configuraiton approach (I haven't tried it)

http://community.jaspersoft.com/wiki/how-remove-readonly-flag-jdbc-and-jndi-data-sources-jasperreports-server