>...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.
You need to add the properties to the DataSource definition that you are
creating for the connection pool. For example here is how I configure my
Db2 datasource. Notice the properties specified after --property
asadmin create-jdbc-connection-pool --datasourceclassname
com.ibm.db2.jcc.DB2SimpleDataSource --property
User=xxx:Password=xxx:driverType=4:portNumber=50000:dataBaseName=xxx:serverName=xxx
jdbc-db2-pool
You will need to see the documentation of the DataSource you are using
to find out which property corresponds to "date format=iso" and "time
format=iso"
Regards,
Mitesh
glassfish_at_javadesktop.org wrote:
> 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
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
> For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>
>