I have created a stored procedure in postgresql database and am calling this from my jasper report and have set the fields in report.
However since a temp table is created during execution, the report fails when ran from report server with the error below.
org.postgresql.util.PSQLException: ERROR: cannot execute DROP TABLE in a read-only transaction Where: SQL statement “DROP TABLE IF EXISTS msg_stats”
How to fix this error? is there a configuration change i could make to allow reportserver execute the stored procedure call?
Am using a JDBC datasource and passing readonly=true as part of the JDBC URL did not help.
Please advise, thanks.
CREATE OR REPLACE FUNCTION app_summary(
IN startdate timestamp without time zone,
IN enddate timestamp without time zone,
OUT param1 character varying,
OUT param2 character varying,
OUT param3 bigint,
OUT param4 bigint,
OUT param5 bigint,
OUT param6 bigint,
OUT param7 bigint,
OUT param8 bigint,
OUT param9 bigint,
OUT param10 bigint,
OUT param11 bigint,
OUT param12 bigint)
RETURNS SETOF record AS
$BODY$
DECLARE
rec RECORD;
BEGIN
DROP TABLE IF EXISTS msg_stats;
CREATE TEMP TABLE msg_stats (param1 varchar(100),
param2 varchar(20),
param3 bigint,
param4 bigint,
param5 bigint,
param6 bigint,
param7 bigint,
param8 bigint,
param9 bigint,
param10 bigint,
param11 bigint,
param12 bigint);
insert into msg_stats(param1,param2, param3,
param4 ,
param5 ,
param6 ,
param7 ,
param8 ,
param9 ,
param10,
param11,
param12) (select c.param1 param1, c.param2 param2,
sum(case when a.messagetype='D' then 1 else 0 end) param3,
sum(case when a.messagetype='MMixed' then 1 else 0 end) param4,
sum(case when a.messagetype='Merge' then 1 else 0 end) param5,
sum(case when a.messagetype='MMessage' then 1 else 0 end) param6,
sum(case when a.messagetype='MApp' then 1 else 0 end) param7,
sum(case when a.messagetype='NRepeat' then 1 else 0 end) param8,
sum(case when a.messagetype='LRepeat' then 1 else 0 end) param9,
sum(case when a.messagetype='Expiring' then 1 else 0 end) param10,
from table2 c
inner join table4 b on c.username=b.pid
inner join table5 a on a.pid=b.pid and a.datereceived >= $1 and a.datereceived <= $2
group by c.username);
for rec in select a.pid p1, p.pname p2, count(b.activated) activations from
pharmacies p
inner join table4 a on p.username=a.pid
inner join table5 b on a.pid=b.id
and b.activated=True and b.datecreated >= $1 and b.datecreated <= $2
group by a.pid, p.pname
LOOP
IF EXISTS (select * from msg_stats where msg_stats.pid=rec.pid) THEN
update msg_stats set param11=rec.activations where msg_stats.pid=rec.pid;
ELSE
insert into msg_stats(pid, pname, param11) values (rec.pid, rec.pname, rec.activations);
END IF;
END LOOP;
for rec in select b.pid pid, p.pname, count(1) totalactivations from pharmacies p
inner join table4 b on p.uname=b.pid
inner join table5 c on b.id=c.pid
and c.activated=True group by b.pid, p.pname
LOOP
IF EXISTS (select * from msg_stats where msg_stats.pid=rec.pid) THEN
update msg_stats set param12=rec.totalactivations where msg_stats.pid=rec.pid;
ELSE
insert into msg_stats(pid, pharmacyname, param12) values (rec.pid, rec.pname, rec.totalactivations);
END IF;
END LOOP;
RETURN QUERY SELECT * FROM msg_stats;
END
$BODY$
LANGUAGE plpgsql;