help with patching jasper to support jdbc transaction

[roosit]

 

As some of you may have noticed, executing sql statements like this "SET LOCAL TimeZone='Pacific/Enderbury'; SELECT NOW() AT TIME ZONE (SELECT current_setting('TIMEZONE')) ;" is not possible. Rendering reporting of jasper almost useless accross multiple time zones and user queries (at least in our situation).

As jdbc offers transaction processing by disabling the default set auto-commit. I guess applying this to jasperreports queryexecuter could solve this problem.

Does any one have any information on what the best approach would be to implement this? Why didnt jasper developers implement it this way, in the first place? Is this not as easy as it looks like?

Thanks

[marianol]

Can you explain what are you trying to achieve; i.e. what is the use case, how is the data stored in the DB, how should it be displayed in different user timezones? This will help me better understand how you can handle this.

 

I assume you already now this; but JR has a Time zone parameter for a report that will allow you to handle the porper date/time transformations taking the timezone into consideration.http://jasperreports.sourceforge.net/api/net/sf/jasperreports/engine/JRParameter.html#REPORT_TIME_ZONE

 

You can also use JNDI connections instead of JDBC and set defaultAutoCommit ="false" in the resource; you can see all the options you have here: https://tomcat.apache.org/tomcat-7.0-doc/jndi-resources-howto.html#JDBC_Data_Sources (scroll down a bit)

Is notmally recommended to use JNDI anyway so you can benefit of the app server handling the connection and connection pooling.

[roosit]

We have multiple views that are using TIMEZONE 'variable' like this:

CREATE VIEW vtoday ( DAY ) ASSELECT (NOW() at TIME ZONE (SELECT current_setting('TIMEZONE')))::DATE as DAY;[/code]

 

[roosit]

also configured jndi connection, disabling the auto commit, does indeed what it supposed to do, leaving the TIMEZONE setting for a future report and that is of course not desirable. Although a test report runned fine under jndi. When i switched the datasource of an old report to jndi i got a com.jaspersoft.jasperserver.api.JSException: jsexception.error.creating.connection. So for now, i will wait with switching jndi.