Announcement Announcement Module
Collapse
No announcement yet.
Problems passing datetime Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problems passing datetime

    Hi,

    I have a class extending org.springframework.jdbc.object.StoredProcedure that executes a stored procedure on a MS SQL 2005 server. I am running this in a Weblogic 8.1 app server. Two of the parameters are declared as datetime in the SP. From the Spring side, I have this code:

    In the constructor:
    Code:
    ...
    declareParameter(new SqlParameter("date1Param", Types.DATE));
    declareParameter(new SqlParameter("date2Param", Types.DATE));
    ...
    In the execute() method:
    Code:
    Map params = new HashMap();
    ...
    params.put("date1Param", new java.sql.Date(new java.util.Date().getTime()));
    params.put("date2Param", new java.sql.Date(new java.util.Date().getTime()));
    super.execute(params);
    When I run the code, I get this error message:
    Code:
    Caused by: java.sql.SQLException: [BEA][SQLServer JDBC Driver][SQLServer]Error converting data type nvarchar to datetime.
    	at weblogic.jdbc.base.BaseExceptions.createException(Unknown Source)
    	at weblogic.jdbc.base.BaseExceptions.getException(Unknown Source)
    	at weblogic.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
    	at weblogic.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
    	at weblogic.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
    	at weblogic.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
    	at weblogic.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
    	at weblogic.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
    	at weblogic.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
    	at weblogic.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
    	at weblogic.jdbc.base.BaseStatement.commonExecute(Unknown Source)
    	at weblogic.jdbc.base.BaseStatement.executeInternal(Unknown Source)
    	at weblogic.jdbc.base.BasePreparedStatement.execute(Unknown Source)
    	at weblogic.jdbcx.base.BasePreparedStatementWrapper.execute(Unknown Source)
    	at weblogic.jdbc.wrapper.PreparedStatement.execute(PreparedStatement.java:102)
    	at weblogic.jdbc.rmi.internal.CallableStatementImpl_weblogic_jdbc_wrapper_CallableStatement_weblogic_jdbcx_base_BaseCallableStatementWrapper.execute(Unknown Source)
    	at weblogic.jdbc.rmi.internal.CallableStatementImpl_weblogic_jdbc_wrapper_CallableStatement_weblogic_jdbcx_base_BaseCallableStatementWrapper_WLSkel.invoke(Unknown Source)
    	at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:477)
    	at weblogic.rmi.internal.BasicServerRef$1.run(BasicServerRef.java:420)
    	at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:363)
    	at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:147)
    	at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:415)
    	at weblogic.rmi.internal.BasicExecuteRequest.execute(BasicExecuteRequest.java:30)
    	at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:219)
    	at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:178)
    	at java.lang.Thread.startThreadFromVM(Unknown Source)
    Why would this happen? Why does it think that those parameters are nvarchar when I pass them in as java.sql.Date?

    Thanks.

  • #2
    I would suggest to activate Spring debug-level logging and take a look on it.

    BTW, how the procedure is database is defined?

    Comment


    • #3
      Please, post a real declaration of your stored procedure (header only is sufficient) and code fragment that calls it (with all parameters declaration, and such).

      Regards,
      Oleksandr

      Comment


      • #4
        I was actually able to resolve the issue. The problem was that my SP is returning a parameter. Also, there are 2 out parameters. I thought I would declare all out parameters and the return parameter at the end of the declareParameter() calls after I am done declaring the in parameters. This is when I saw the error.

        But when I moved declareParameter() for the return parameter to the front, the error went away.

        I think what happens is that Spring converts the procedure call into a CallableStatement {? = call <SP name> ?, ?, ?, .... } and then substitutes the ?'s with the parameter values in the same order that they were declared. So, the first parameter ended up in the ? before the =, the second parameter ended up in the first ? after the SP name, etc. This is why an nvarchar value for one of the parameters got substituted for a neighboring datetime parameter.

        Thanks for your help.

        Comment


        • #5
          Exactly, and it was whay I have asked you to post exact declaration of procedure and parameters.

          Moreover, such behavior of Spring seems to be correct and in line with JDBC specification for CallableStatement.

          Regards,
          Oleksandr

          Originally posted by imchi View Post
          I was actually able to resolve the issue. The problem was that my SP is returning a parameter. Also, there are 2 out parameters. I thought I would declare all out parameters and the return parameter at the end of the declareParameter() calls after I am done declaring the in parameters. This is when I saw the error.

          But when I moved declareParameter() for the return parameter to the front, the error went away.

          I think what happens is that Spring converts the procedure call into a CallableStatement {? = call <SP name> ?, ?, ?, .... } and then substitutes the ?'s with the parameter values in the same order that they were declared. So, the first parameter ended up in the ? before the =, the second parameter ended up in the first ? after the SP name, etc. This is why an nvarchar value for one of the parameters got substituted for a neighboring datetime parameter.

          Thanks for your help.

          Comment


          • #6
            Hi,

            I am using Oracle 10g and passing the value for ad_request_startedon as datetime . But it is storing date only .how to store date and time in request startedon column ?.


            table Structure :

            create table e_soap
            (
            request_startedon date
            );


            Java Code :


            public int create_eminder(Date ad_request_startedon )
            {
            logger.info("The Time is " + ad_request_startedon);
            return eminder.create_eminder(ad_request_startedon);
            }

            class createeminder extends StoredProcedure{
            public createeminder(DataSource ds)
            {
            super(ds,"owner.p_create_eminders");
            declareParameter(new SqlParameter("ad_request_startedon", Types.DATE));
            }

            int create_eminder(Date ad_request_startedon)
            {
            Map inParams=new HashMap();
            inParams.put("ad_request_startedon", ad_request_startedon);
            Map outParams= execute(inParams);
            return 1;
            }

            }

            Output :
            INFO: The Time is Wed Jul 23 07:44:55 GMT+05:30 2008.

            Please help me ASAP .

            Thanks in Advance .

            Comment


            • #7
              woop, this was exactly what i was having problems with.

              Comment


              • #8
                I have solved this issue . We have to use java.sql.timestamp instead of java.util.date .

                The Java Code is

                import java.sql.Timestamp; //instead of java.util.Date

                public int create_eminder(Timestamp ad_request_startedon )
                {
                logger.info("The Time is " + ad_request_startedon);
                return eminder.create_eminder(ad_request_startedon);
                }

                class createeminder extends StoredProcedure{
                public createeminder(DataSource ds)
                {
                super(ds,"owner.p_create_eminders");
                declareParameter(new SqlParameter("ad_request_startedon", Types.TIMESTAMP));
                }

                int create_eminder(Timestamp ad_request_startedon)
                {
                Map inParams=new HashMap();
                inParams.put("ad_request_startedon", ad_request_startedon);
                Map outParams= execute(inParams);
                return 1;
                }

                Comment


                • #9
                  That is what I have all the time (silently) supposed, just had no time to prove.
                  Dare to say that type of ad_request_startedon does not matter, it as well may still be date, what is important is usage of Types.TIMESTAMP in the declareParameter, as Types.DATE instructs JDBC driver to trim timepart of the value (see Javadoc for java.sql.Types).

                  What I was (slightly) unsure of - if Oracle JDBC driver would understand Types.TIMESTAMP for DATE column. Luckily it does.

                  Regards,
                  Oleksandr
                  Originally posted by sparthibhan View Post
                  I have solved this issue . We have to use java.sql.timestamp instead of java.util.date .

                  The Java Code is

                  import java.sql.Timestamp; //instead of java.util.Date

                  public int create_eminder(Timestamp ad_request_startedon )
                  {
                  logger.info("The Time is " + ad_request_startedon);
                  return eminder.create_eminder(ad_request_startedon);
                  }

                  class createeminder extends StoredProcedure{
                  public createeminder(DataSource ds)
                  {
                  super(ds,"owner.p_create_eminders");
                  declareParameter(new SqlParameter("ad_request_startedon", Types.TIMESTAMP));
                  }

                  int create_eminder(Timestamp ad_request_startedon)
                  {
                  Map inParams=new HashMap();
                  inParams.put("ad_request_startedon", ad_request_startedon);
                  Map outParams= execute(inParams);
                  return 1;
                  }

                  Comment


                  • #10
                    Both Types.TIMESTAMP and Types.DATE are working(In Declare Parameter) fine for oracle 10g . But the datatype of ad_startedon should be java.sql.Timestamp .If you use java.util.Date , the date only will go to backend .

                    Comment

                    Working...
                    X