Date type problem JSP, mySQL

[vdhara]

Hi,

I am trying to insert a record in a mySQL 5.0 database through a JSP.
I am getting an error that the format is invalid.

I don't know how to manipulate the code using Java itself.

Can anybody guide me?

Thanks

VDhara

[masijade]

How about posting what you have so we are not guessing about how you are currently doing things when trying to present a solution that doesn't involve too much of a rewrite.

[vdhara]

How about posting what you have so we are not guessing about how you are currently doing things when trying to present a solution that doesn't involve too much of a rewrite.

Thanks for the response.

Hoping for an early reply!! I am sitting on a deliverable here!!!

Here's the code!!

I am using Tomcat

Here is the error Tomcat gives
'0000-00-00' cannot be represented as java.sql.Date!!

This is the Method where we call the INSERT statement

/*********************
public static void addEmployeecb(Employeecb employeecb, DataSource dataSource)
    throws Exception {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      conn = dataSource.getConnection();
      stmt = conn.createStatement();
      StringBuffer sqlString =
        new StringBuffer("insert into employees(EmpId, Name, DoJ, Designation, Status, UserID," +
   " HiredBy, HireDate, Password, OpenDate, OpenID, EditDate, EditID, NavGroup, SecGroup)");
      sqlString.append("values (\""
        + employeecb.getEmpid()+ "\", ");
      sqlString.append("\""
        + employeecb.getName()+ "\", ");
      sqlString.append("\""
        + employeecb.getDoj() + "\", ");

      sqlString.append("\""
        + employeecb.getDesignation()+  "\", ");
      sqlString.append("\""
        + employeecb.getStatus() + "\", ");
      sqlString.append("\""
        + employeecb.getUserid()+  "\", ");
      sqlString.append("\""
        + employeecb.getHiredby() + "\", ");
      sqlString.append("\""
     + employeecb.getHiredate()+  "\", ");
      sqlString.append("\""
        + employeecb.getHiredate() + "\", ");
      sqlString.append("\""
        + employeecb.getPassword()+  "\", ");
      sqlString.append("\""
        + employeecb.getOpendate() + "\", ");  
      sqlString.append("\""
     + employeecb.getOpenid()+  "\", ");
      sqlString.append("\""
     + employeecb.getEditdate()+ "\", ");
      sqlString.append("\""
     + employeecb.getEditid()+  "\", ");
      sqlString.append("\""
     + employeecb.getNavgroup()+ "\", ");
      sqlString.append("\""
        + employeecb.getSecgroup()+ "\")");

      stmt.execute(sqlString.toString());
    }
    finally {
      if ( rs != null ) {
        rs.close();
      }
      if ( stmt != null ) {
        stmt.close();
      }
      if ( conn != null ) {
        conn.close();
      }
    }
  }
**********************/ // Java Method ends
/*********** This is the JSP

    <html:form action="/AddEmployeecb" >
      <table width="500" border="0">
        <tr>
          <td>Emp No:</td>
          <td><html:text property="empno" /></td>
          <td>Employee ID:</td>
          <td><html:text property="empid" /></td>
        </tr>
        <tr>
          <td>Name:</td>
          <td><html:text property="name" /></td>
          <td>Date Joined:</td>
          <td><html:text property="doj" /></td>
        </tr>
        <tr>
          <td>Designation:</td>
          <td><html:text property="designation" /></td>
          <td>Status:</td>
          <td><html:text property="status" /></td>
        </tr>
        <tr>
          <td>UserID:</td>
          <td><html:text property="userid" /></td>
          <td>Hired By:</td>
          <td><html:text property="hiredby" /></td>
        </tr>        
        <tr>
          <td>Hire Date:</td>
          <td><html:text property="hiredate" /></td>
          <td>Password:</td>
          <td><html:text property="password" /></td>
        </tr>  
        <tr>
          <td>Open Date:</td>
          <td><html:text property="opendate" /></td>
          <td>Open ID:</td>
          <td><html:text property="openid" /></td>
        </tr>              
        <tr>
          <td>Edit Date:</td>
          <td><html:text property="editdate" /></td>
          <td>Edit ID:</td>
          <td><html:text property="editid" /></td>
        </tr>        
        <tr>
          <td>Navigation Group:</td>
          <td><html:text property="navgroup" /></td>
          <td>Security Group:</td>
          <td><html:text property="secgroup" /></td>
        </tr>        
        <tr>
            <td colspan="2" align="center">
            <html:submit /><html:cancel /><html:reset />
          </td>
        </tr>
      </table>
    </html:form> 
*************/ // End of JSP

Here is the error Tomcat gives
'0000-00-00' cannot be represented as java.sql.Date!!

[jwenting]

Seems a very clear and concise error message to me.
You're trying to turn something into a date that isn't recognised as a date.
The solution is to make sure it is recognised, using the relevant formatters and converters provided in the standard library.

[iamthwee]

Please use code tags.

Ain't you supposed to do that now you're a moderator Jeroen? He he.

[vdhara]

Seems a very clear and concise error message to me.
You're trying to turn something into a date that isn't recognised as a date.
The solution is to make sure it is recognised, using the relevant formatters and converters provided in the standard library.

Oh Yeah? That much is apparent from the dictinary meaning of the words in that message!!!:mad:

What I was trying to achieve when I posted was to save time doing research.

Here's what that message actually means.

It has nothing to do with formatters and converters!! :mrgreen: :mrgreen:

You see I tried to insert through SQL INSERT statement an invalid date value like 1999/13/12 which is invalid because yyyy/mm/dd is the format mySQL expects.

Here's the problem though. mySQL actually inserts the record but puts '0000-00-00' as the value.

Now when I go to my JSP front end and Java issues a SELECT and retrieves the list, it will roll back every thing because of that one dirty record!

No doubt there is a way around this and mySQL can be prevented from inserting thr record.

Infact that was the kind of info I was hoping somebody will give me!!

Not how 'concise' th eerror messages are :rolleyes:

[masijade]

If you have MySQL 5.0 or later you can start your mysql server with the option --sql_mode="NO_ZERO_DATE" , or to be even more restricitive --sql_mode="STRICT_ALL_TABLES" , so that insertion of invalid dates will cause an error (and not be inserted). If you are 4.1 or earlier, you will have to check the date programmatically before the insert so that entering of invalid dates does not occur (which you should be doing anyway).

Edit:

Also, if you can afford to ignore records with invalid dates, then when selecting your records insert

AND HireDate != '0000-00-00'
AND OpenDate != '0000-00-00'
AND EditDate != '0000-00-00'

into your Where clause so that invalid records will not cause your statement to crash.

Otherwise, do not use getDate to get the Date Column. Use getString and then parse the date using SimpleDateFormat and catch any exception in order to set the date with a default value (say new Date(0) maybe). This will also keep your statements from crashing.

[jwenting]

It isn't recognised as a date, period.
It's up to you to make sure only things that are recognised as dates are inserted...

The actual date given (whether the string can be parsed to an actual date or not) is impossible from your original message to know.
Many people will give some value like 00-00-0000 to indicate any date, and it might have been the driver returning that message because it doesn't recognise the actual format used.

Maybe you should be less agressive towards people trying to help, it doesn't make them any more likely to help you in the future.