I'm having an issue where adding the correct properties to the JDBC connection pool in Glassfish...is not working like it should.
I'm using the "jt400" JDBC driver for IBM iSeries AS400. The null date format in this database is '0001-01-01', which causes this exception:
[code]
Error Code: 183
Call: SELECT t0.LOCODE, t0.DLDACDAT, t0.DICODE, t0.NBRHEAD, t0.PRCHDID, t0.PCDSEQ, t1.LOCODE, t1.PCDEDAT, t1.DICODE, t1.PRCHDID, t1.PCDSEQ FROM DELSTDP t0 LEFT OUTER JOIN PROCDTP t1 ON ((t1.PRCHDID = t0.PRCHDID) AND (t1.PCDSEQ = t0.PCDSEQ)) WHERE ((t0.DICODE = CAST (? AS VARCHAR(32672) )) AND (t0.LOCODE = CAST (? AS VARCHAR(32672) )))
bind => [BV, BV]
Query: ReportQuery(com.myapp.ejb.model.DeliveryList)
at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:319)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:566)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:452)
at oracle.toplink.essentials.threetier.ServerSession.executeCall(ServerSession.java:473)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:214)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:285)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:615)
at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2416)
at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2382)
at oracle.toplink.essentials.queryframework.ReportQuery.executeDatabaseQuery(ReportQuery.java:802)
at oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:628)
at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:692)
at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:746)
at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2233)
at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:952)
at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:924)
at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:367)
at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getResultList(EJBQueryImpl.java:478)
at com.myapp.ejb.session.DeliveryListBean.getByDivisionLocation(DeliveryListBean.java:40)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1067)
at com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:176)
at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:2895)
at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:3986)
at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:197)
... 38 more
Caused by: java.sql.SQLException: [SQL0183] Result of date or timestamp expression not valid.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:520)
at com.ibm.as400.access.JDServerRowCache.fetch(JDServerRowCache.java:301)
at com.ibm.as400.access.JDServerRowCache.fetch(JDServerRowCache.java:232)
at com.ibm.as400.access.JDServerRowCache.next(JDServerRowCache.java:637)
at com.ibm.as400.access.AS400JDBCResultSet.next(AS400JDBCResultSet.java:1764)
at com.sun.gjc.spi.base.ResultSetWrapper.next(ResultSetWrapper.java:82)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:548)
... 65 more
[/code]
The field causing the issue is "t0.DLDACDAT" which is mapped like so, in the DeliveryList entity:
[code]
@Column(name="DLDACDAT")
@Temporal(TemporalType.DATE)
private Date actualDate;
[/code]
There is a very easy fix for this, found on IBM's web site:
http://www-1.ibm.com/support/docview.wss?uid=nas125a815068ae8792586256aaa0054f5fb
...apparently, just appending ";date format=iso;time format=iso;" to the end of my JDBC URL fixes the issue. Indeed, if I do it on the raw connection I've created in the "Services" panel in Netbeans, I can query w/o any errors.
However, after changing my "URL" property in the connection pool in Glassfish, saving it, restarting, and re-deploying the app - the error does not go away.
What can I do?
[Message sent by forum member 'zambizzi' (zambizzi)]
http://forums.java.net/jive/thread.jspa?messageID=249385