<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<body link="#355491" alink="#4262a1" vlink="#355491" style="background: #e2e2e2; margin: 0; padding: 20px;">
<div>
<table cellpadding="0" bgcolor="#FFFFFF" border="0" cellspacing="0" style="border: 1px solid #dadada; margin-bottom: 30px; width: 100%; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tbody>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" style="border: solid 2px #ccc; background: #dadada; width: 100%; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tbody>
<tr>
<td bgcolor="#000000" valign="middle" height="58px" style="border-bottom: 1px solid #ccc; padding: 20px; -moz-border-radius-topleft: 3px; -moz-border-radius-topright: 3px; -webkit-border-top-right-radius: 5px; -webkit-border-top-left-radius: 5px;">
<h1 style="color: #333333; font: bold 22px Arial, Helvetica, sans-serif; margin: 0; display: block !important;">
<!-- To have a header image/logo replace the name below with your img tag -->
<!-- Email clients will render the images when the message is read so any image -->
<!-- must be made available on a public server, so that all recipients can load the image. -->
<a href="http://community.jboss.org/index.jspa" style="text-decoration: none; color: #E1E1E1">JBoss Community</a></h1>
</td>
</tr>
<tr>
<td bgcolor="#FFFFFF" style="font: normal 12px Arial, Helvetica, sans-serif; color:#333333; padding: 20px; -moz-border-radius-bottomleft: 4px; -moz-border-radius-bottomright: 4px; -webkit-border-bottom-right-radius: 5px; -webkit-border-bottom-left-radius: 5px;"><h3 style="margin: 10px 0 5px; font-size: 17px; font-weight: normal;">
Oracle timezone problems & JBoss
</h3>
<span style="margin-bottom: 10px;">
created by <a href="http://community.jboss.org/people/martinnovak">Martin Novák</a> in <i>Datasource Configuration</i> - <a href="http://community.jboss.org/message/580959#580959">View the full discussion</a>
</span>
<hr style="margin: 20px 0; border: none; background-color: #dadada; height: 1px;">
<div class="jive-rendered-content"><p>Hello, we have a database that contains a table with a column of type TIMESTAMP WITH LOCAL TIME ZONE. We have two applications, one runs on Glassfish and works well. But the other one runs on JBoss 4.2.3, and we are getting the following error:</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p><span style="font-family: 'andale mono', times;">Caused by: java.sql.SQLException: The database session time zone is not set</span></p><p><span style="font-family: 'andale mono', times;">        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)</span></p><p><span style="font-family: 'andale mono', times;">        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)</span></p><p><span style="font-family: 'andale mono', times;">        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)</span></p><p><span style="font-family: 'andale mono', times;">        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)</span></p><p><span style="font-family: 'andale mono', times;">        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)</span></p><p><span style="font-family: 'andale mono', times;">        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)</span></p><p><span style="font-family: 'andale mono', times;">        at oracle.jdbc.driver.TimestampltzAccessor.getTimestamp(TimestampltzAccessor.java:298)</span></p><p><span style="font-family: 'andale mono', times;">        at oracle.jdbc.driver.OracleResultSetImpl.getTimestamp(OracleResultSetImpl.java:1060)</span></p><p><span style="font-family: 'andale mono', times;">        at oracle.jdbc.driver.OracleResultSet.getTimestamp(OracleResultSet.java:512)</span></p><p><span style="font-family: 'andale mono', times;">        at org.jboss.resource.adapter.jdbc.WrappedResultSet.getTimestamp(WrappedResultSet.java:979)</span></p><p><span style="font-family: 'andale mono', times;">        at org.hibernate.type.TimestampType.get(TimestampType.java:30)</span></p><p><span style="font-family: 'andale mono', times;">        at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)</span></p><p><span style="font-family: 'andale mono', times;">        at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154)</span></p><p><span style="font-family: 'andale mono', times;">        at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)</span></p><p><span style="font-family: 'andale mono', times;">        at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2096)</span></p><p><span style="font-family: 'andale mono', times;">        at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1380)</span></p><p><span style="font-family: 'andale mono', times;">        at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1308)</span></p><p><span style="font-family: 'andale mono', times;">        at org.hibernate.loader.Loader.getRow(Loader.java:1206)</span></p><p><span style="font-family: 'andale mono', times;">        at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:580)</span></p><p><span style="font-family: 'andale mono', times;">        at org.hibernate.loader.Loader.doQuery(Loader.java:701)</span></p><p><span style="font-family: 'andale mono', times;">        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)</span></p><p><span style="font-family: 'andale mono', times;">        at org.hibernate.loader.Loader.doList(Loader.java:2220)</span></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>This error occurs just when we read from that field, not when we write to it. However the strange thing is that the reading works just fine on Glassfish and fails on JBoss. I have noticed that Glassfish uses directly OracleDataSource whereas JBoss does not. When I tried to use OracleDataSource on JBoss, the error was still the same. I also tried both OCI and THIN and there was no difference. It seems to me that this has something to do with Oracle JDBC formatting.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>I have found on the internet some solutions that basically say that we should set the timezone on the oracle connection (OracleConnection) manually. This is what we don't want to do, actually. And I believe if reading from local timezone column works on Glassfish, it should work on JBoss as well. But I have no idea how to set it up. Has anyone came across to this problem?</p><p>thanks,</p><p>Martin</p></div>
<div style="background-color: #f4f4f4; padding: 10px; margin-top: 20px;">
<p style="margin: 0;">Reply to this message by <a href="http://community.jboss.org/message/580959#580959">going to Community</a></p>
<p style="margin: 0;">Start a new discussion in Datasource Configuration at <a href="http://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2077">Community</a></p>
</div></td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>