temporary table creation not working in jasperserver(cannot execute CREATE TABLE in a read-only transaction)

Question

I have a function in postgres with temporary table.

create or replace function sp_test_function()
returns table (id integer,enqu_id integer) as
$BODY$
BEGIN

create temporary table temp_table(
id serial,
enquiry_id integer

) on commit drop;

insert into temp_table(enquiry_id) select enquiry_id  from sales_enquiry;

return query select t.id,enquiry_id from temp_table t;

END;
$BODY$
language plpgsql;

I have a report in jasper and using above function for fetch data. The problem when i run the report from server getting this error cannot execute CREATE TABLE in a read-only transaction. I tried SET TRANSACTION READ WRITE

$BODY$
BEGIN
 SET TRANSACTION READ WRITE
create temporary table temp_table(
id serial,
enquiry_id integer

) on commit drop

But got another error transaction read-write mode must be set before any query. How to set transaction in postgres function?

Answer 1

Finally i found the answer.

Step 1: Open the context.xml file (C:\Jaspersoft\jasperreports-server-cp-5.5.0\apache-tomcat\webapps\jasperserver\META-INF\context.xml)

Add the below code in that file(i am using postgresql db)

<Resource name="jdbc/your_db_name" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
        username="db_username" password="db_password" 
        driverClassName="org.postgresql.Driver"
        validationQuery="SELECT 1"
        testOnBorrow="true"
        url="jdbc:postgresql://127.0.0.1:5432/ur_db_name?useUnicode=true&amp;characterEncoding=UTF-8&amp;autoReconnect=true&amp;autoReconnectForPools=true"            factory="com.jaspersoft.jasperserver.tomcat.jndi.JSBasicDataSourceFactory"/>

Step:2 Open web.xml (C:\Jaspersoft\jasperreports-server-cp-5.5.0\apache-tomcat\webapps\jasperserver\WEB-INF\web.xml)

Add the below code

<resource-ref>
    <description>some_description</description>
        <res-ref-name>jdbc/ur_db_name</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>

Step 3: Restart your jasperserver

Step 4: Login to jasper server and add new datasource. From the Drop down list select JNDI Data Source instead of JDBC Data Source.

In the text box field Service Name (required): type jdbc/ur_db_name . And Click Test Connection You get a popup message on top Connection Passed. That's it Now you can use this datasource in reports. Hope this will help.