SET LOCAL TimeZone, compound sql, transaction

[roosit]

to query views depending on a set timezone, i need to be able to run something like this:

BEGIN;
SET LOCAL TimeZone='Pacific/Enderbury';

SELECT NOW() AT TIME ZONE (SELECT current_setting('TIMEZONE')) ;
COMMIT;
 

The set local is setting the timezone only for this transaction. Anybody have a suggestion on how to implement this in a single report?

Thanks in advance

PS.  just curious, why is valid sql not simply executed?

 

[roosit]

i have read comments about subdataset, tables and subreports, those are just complicating things

[roosit]

 

aalso solutions with creating stored procedures dont have my preference. Since you have to define return types, i can then start making a procedure for every report, that is not why i am using jasperserver

[roosit]

I have seen lots of posts of people requiring to run compound sql queries. Has some one already thought about creating a patch for the query executer / validator?

net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:137

[roosit]

Is this going to solve the issue?

modify file
apache-tomcatwebappsjasperserverWEB-INFclassesesapisecurity-config.properties:

security.validation.sql.on=false