0 Replies Latest reply on Jan 31, 2003 4:49 AM by Adis

    SQL server insert java.sql.Date problem.

    Adis Newbie

      I have following problem,
      I try to insert java.sql.Date into SQlserver2000 field datetime.
      My bean is CMP1.1 and error I get is

      10:34:59,948 ERROR [JDBCCommand] Exception caught executing SQL
      java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Syntax error converting datetime from
      binary/varbinary string.
      at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
      at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
      at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
      at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
      at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
      at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
      at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
      at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
      at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
      at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)......

      My jdbc driver is as you can see Microsoft jdbc driver.
      As I understand this is because my date is not formated as It should be.
      I can get same error when I run little test application that connects directly to the database and runs insert like:
      insert into mytable(mydate) values('mydate')
      I should probably avoid this if I could use
      PreparedStatement and setDate(1,mydate) but I don't have this option with CMP1.1 bean.
      Is there any solution to my problem? Am I completly wrong about reason of this error?

      Interesting is also that when I used smalldatetime sql server type my ejb didn't throw exception but inserted some incorrect value (1925... in stead of 1975-07-21).
      But tryed to write out my java.sql.date and it has format like yyyy-MM-dd.

      Any suggestions, please?
      I'm getting desperate.