Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
2 replies Latest Post - ‏2009-01-20T19:56:54Z by SystemAdmin
SystemAdmin
SystemAdmin
895 Posts
ACCEPTED ANSWER

Pinned topic SQL Date Column

‏2006-08-09T13:29:29Z |
Problem * Jun 20 2005 : 2:53:47 PM

I used the SQL Call builder to create an Update statement. I enter a date on my web form and when I try to run the SQL update statement I get the following error:

An error has occurred.
The error message is " Data type mismatch."

Click here for a detailed error message. to hide the detailed error message.
Exception: com.bowstreet.webapp.engine.actions.MethodExecutionException: Data type mismatch. Root exception is java.sql.SQLException: Data type mismatch.

Stack Trace:
java.sql.SQLException: Data type mismatch.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:382)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:359)
at com.ibm.as400.access.SQLDate.stringToDate(SQLDate.java:165)
at com.ibm.as400.access.SQLDate.set(SQLDate.java:382)
at com.ibm.as400.access.AS400JDBCPreparedStatement.setValue(AS400JDBCPreparedStatement.java:2818)
at com.ibm.as400.access.AS400JDBCPreparedStatement.setObject(AS400JDBCPreparedStatement.java:2341)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.setObject(WSJdbcPreparedStatement.java:1206)
at com.bowstreet.builders.webapp.methods.SqlStatementHelper.setStatementParameters(SqlStatementHelper.java:567)
at com.bowstreet.builders.webapp.methods.SqlStatementHelper.createStatement(SqlStatementHelper.java:873)
at com.bowstreet.builders.webapp.methods.SqlStatementHelper.createStatement(SqlStatementHelper.java:786)
at genjava._DirectoryPrototype.UpdateModel_UpdateEMPMASInvokeBase(_DirectoryPrototype.java:1109)
at java.lang.reflect.Method.invoke(Native Method)
at com.bowstreet.webapp.engine.actions.MethodAction.callAction(MethodAction.java:89)
at com.bowstreet.webapp.engine.WebAppAccessImpl.callMethod(WebAppAccessImpl.java:336)
at com.bowstreet.webapp.engine.WebAppAccessImpl.callMethod(WebAppAccessImpl.java:217)
at genjava._DirectoryPrototype.UpdateModel_UpdateEMPMASInvoke(_DirectoryPrototype.java:1136)
at java.lang.reflect.Method.invoke(Native Method)
at com.bowstreet.webapp.engine.actions.MethodAction.callAction(MethodAction.java:89)
at com.bowstreet.webapp.engine.WebAppAccessImpl.callMethod(WebAppAccessImpl.java:336)
at com.bowstreet.webapp.engine.WebAppAccessImpl.processAction(WebAppAccessImpl.java:835)
at com.bowstreet.webapp.engine.WebAppAccessImpl.processAction(WebAppAccessImpl.java:890)
at genjava._DirectoryPrototype.UpdateModel_updateRecord(_DirectoryPrototype.java:1488)
at java.lang.reflect.Method.invoke(Native Method)
at com.bowstreet.webapp.engine.actions.MethodAction.callAction(MethodAction.java:89)
at com.bowstreet.webapp.engine.WebAppAccessImpl.callMethod(WebAppAccessImpl.java:336)
at com.bowstreet.webapp.engine.WebAppAccessImpl.processAction(WebAppAccessImpl.java:835)
at com.bowstreet.webapp.engine.WebAppAccessImpl.processAction(WebAppAccessImpl.java:890)
at com.bowstreet.webapp.engine.WebAppRequestRunner.doRequest(WebAppRequestRunner.java:332)
at com.bowstreet.webapp.engine.WebAppRunner.doActualRequest(WebAppRunner.java:823)
at com.bowstreet.webapp.engine.WebAppRunner.doRequest(WebAppRunner.java:631)
at com.bowstreet.webapp.engine.WebAppRunner.doRequest(WebAppRunner.java:244)
at com.bowstreet.webapp.engine.WebAppRunner.doRequest(WebAppRunner.java:206)
at com.bowstreet.webapp.engine.WebAppServlet.doPost(WebAppServlet.java:72)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:1020)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:561)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:198)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:80)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:212)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:116)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:186)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:618)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:443)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)

Action Status:
The following table lists the actions that have run during this request.

Action ID:
WebAppRunner.doRequest
UpdateModel_UpdatePage_SaveData
UpdateModel_updateRecord
UpdateModel_UpdateCONTACTSInvoke
UpdateModel_UpdateCONTACTSInvokeBase
UpdateModel_UpdateCONTACTSStatement_createHelper
UpdateModel_UpdateCONTACTSStatement_refreshHelper
UpdateModel_UpdateCONTACTSDataSource_createHelper
UpdateModel_UpdateCONTACTSDataSource_refreshHelper
UpdateModel_UpdateCONTACTSDataSource_refreshHelper
UpdateModel_UpdateCONTACTSStatement_refreshHelper
UpdateModel_UpdateEMPMASInvoke
UpdateModel_UpdateEMPMASInvokeBase
UpdateModel_UpdateEMPMASStatement_createHelper
UpdateModel_UpdateEMPMASStatement_refreshHelper
UpdateModel_UpdateEMPMASDataSource_createHelper
UpdateModel_UpdateEMPMASDataSource_refreshHelper
UpdateModel_UpdateEMPMASDataSource_refreshHelper
UpdateModel_UpdateEMPMASStatement_refreshHelper

Thanks,

Chris Gormley

"We can't solve problems by using the same kind of thinking we used when we created them."
- Albert Einstein
Cause

Solution * dbdave
USA
45 Posts
Posted - Jun 20 2005 : 3:00:51 PM

Some databases don't perform automatic type casting from String to Date/Timestamp. Try using an explicit type cast for the date column if you have specified "Automatic" typecasting in SQLCall. Search your DB doc for a SQL function like TO_DATE(...) or CONVERT(...).

-Dave-
Updated on 2009-01-20T19:56:54Z at 2009-01-20T19:56:54Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    895 Posts
    ACCEPTED ANSWER

    Re: SQL Date Column

    ‏2007-11-07T23:06:08Z  in response to SystemAdmin
    Did anyone fix this, I'm getting the same problem....

    thx,
    Vishal
  • SystemAdmin
    SystemAdmin
    895 Posts
    ACCEPTED ANSWER

    Re: SQL Date Column

    ‏2009-01-20T19:56:54Z  in response to SystemAdmin
    For inserting/updating the date field in Portlet factory:,
    In SQL call builder,change the date fields to varchar in Paramaters section. (Dont leave it to automatic)
    Enter the date in mm/dd/yyyy format. If you have defined any RDD file, then change the data-types in data-definition of date fields to string.

    While getting the date fields from DB, use select char(<FieldName>, USA) as <FieldName> from <tableName>. (It will give you the date in mm/dd/yyyy format)

    Thanks,
    Shantanu.