6 Replies Latest reply on Dec 20, 2007 3:58 PM by 843859

    java.sql.SQLException: Cannot convert value

    843859
      I have a database table - name is client. It has one field with TimeStamp Datatype.

      I am using mysql-3.23.51-win and j2sdk-1_4_0_01-windows-i586.

      create table client (

      MODIFIED timestamp(14)

      );

      while I am doing select MODIFIED from client that time I am receiving 00000000000000.

      So It has 00000000000000 value in databse.

      Now I am getting this value using ResultSet. for that

      I have a one Client Object class name is GenericClient

      public class GenericClient {

      private java.util.Date _modified;

      public java.util.Date getModified() {
      return _modified;
      }
      public void setModified(java.util.Date newVal) {
      this._modified = newVal;
      }
      }

      In my database processing class I am getting database value and set that value in setModified(java.util.Date newVal).
      like

      private Client decodeRow(ResultSet rs) throws SQLException {
      GenericClient obj = new GenericClient();
      obj.setModified(rs.getTimestamp(1));
      return obj;
      }

      So at obj.setModified(rs.getTimestamp(1)); line I am received error message

      java.sql.SQLException: Cannot convert value '00000000000000' from column 1 to TIMESTAMP.

      Please guide me why I am receiving the above error.

      Thanks
      Amit
        • 1. Re: java.sql.SQLException: Cannot convert value
          843859
          use java.sql.Timestamp instead of java.util.Date
          • 2. Re: java.sql.SQLException: Cannot convert value
            843859
            Hi wpafbuser1,

            Still it is not working
            • 3. Re: java.sql.SQLException: Cannot convert value
              843859
              Are you passing in a Timestamp object or a String? Try using PreparedStatement and pass in a Timestamp object. Post the part of the code that is producing the error, post the full stack trace, what db are you using? post more info
              • 4. Re: java.sql.SQLException: Cannot convert value
                843859
                Please find some more information from my side

                1) I am passing Timestamp Object

                2) I am using PreparedStatement

                3) code

                Table : client
                ------------------
                create table client (
                
                     MODIFIED timestamp(14)
                
                );
                Bean Class
                -----------------
                public class GenericClient {
                     private java.util.Date _modified;
                
                     public java.util.Date getModified() {
                          return _modified;
                     }
                     public void setModified(java.util.Date newVal) {
                          this._modified = newVal;
                     }
                }
                Method - which is fired error
                ----------------------------------------
                private Client decodeRow(ResultSet rs) throws SQLException {
                     GenericClient obj = new GenericClient();
                     obj.setModified(rs.getTimestamp(1));
                     return obj;
                }
                3) Error Stack
                ==========
                java.sql.SQLException: Cannot convert value '00000000000000' from column 1 to TIMESTAMP.
                        at com.mysql.jdbc.ResultSet.getTimestampFromString(ResultSet.java:5538)
                        at com.mysql.jdbc.ResultSet.getTimestampInternal(ResultSet.java:5566)
                        at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:5219)
                        at com.ksea.manager.ClientManager.decodeRow(ClientManager.java:463)
                        at com.ksea.manager.ClientManager.loadByWhere(ClientManager.java:266)
                        at com.ksea.manager.ClientManager.loadByWhere(ClientManager.java:221)
                        at com.ksea.manager.ClientManager.loadByWhere(ClientManager.java:203)
                        at com.ksea.manager.ClientManager.loadByUsername(ClientManager.java:53)
                        at com.ksea.nt.DBSynch.synchEmployees(DBSynch.java:83)
                        at com.ksea.nt.DBSynch.synchAll(DBSynch.java:35)
                        at com.ksea.nt.DBSynch.main(DBSynch.java:168)
                4) db Info
                =======
                I am using mysql-3.23.51-win and j2sdk-1_4_0_01-windows-i586.
                • 5. Re: java.sql.SQLException: Cannot convert value
                  843859
                  Look here: http://dev.mysql.com/doc/refman/5.0/en/connector-j-installing-upgrading.html

                  That's an invalid value to pass in.
                  • 6. Re: java.sql.SQLException: Cannot convert value
                    843859
                    aMit, you have not changed the code - you are still trying to move it into java.util.Date. Why not try that as well?

                    At any rate, from the MySql manual, this:

                    "Illegal DATETIME, DATE, or TIMESTAMP values are converted to the ``zero'' value of the appropriate type ('0000-00-00 00:00:00', '0000-00-00', or 00000000000000)."

                    Also this:

                    "Remember that although DATETIME, DATE, and TIMESTAMP values all can be specified using the same set of formats, the types do not all have the same range of values. For example, TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not a valid TIMESTAMP value and will be converted to 0 if assigned to such an object."

                    I suspect that is what you were directed to - I did not check. But a read of the MySql manual, as well as trying different values should get the problem fixed.