Hello
I would like to create a excel report .
I have to export table from mysql DB
in the table I have date format where the default value is 0000-00-00
Too create my report I use a tMysqlInput
whene I run my job I get an error message
Exception in component tMysqlInput_1
java.sql.SQLException: Cannot convert value '0000-00-00' from column 24 to TIMESTAMP.
at com.mysql.jdbc.ResultSet.getTimestampFromBytes(ResultSet.java:6965)
at com.mysql.jdbc.ResultSet.getTimestampInternal(ResultSet.java:6999)
at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:6319)
at hbrepport.report.report.tMysqlInput_1Process(report.java:496)
at hbrepport.report.report.main(report.java:754)
I change in the tMysqlInput the default value for date to 1600-01-01 but nothing change
Pls help
DN
It doesn't work either for a mysql date '0000-00-00' store in a java.sql.Date.
A possible workaround is to retrieve this value as a String instead of a Date in your input schema and with a routine, cast it back to a date with an acceptable format.
But this is definately problematic and having it working directly (since 0000-00-00 is a valid mysql date) would be great.
Instead of retrieving this value as a date type, select the String type from your schema. It will return the string value "0000-00-00" without throwing an exception. After that, you can import the string into your excel file!
I tryed this solution in the beguining but I get the same error message Starting job test3 at 12:29 01/06/2007. Exception in component tMysqlInput_1 java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1056) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927) at com.mysql.jdbc.ResultSet.getDateFromString(ResultSet.java:2129) at com.mysql.jdbc.ResultSet.getStringInternal(ResultSet.java:5833) at com.mysql.jdbc.ResultSet.getString(ResultSet.java:5645) at hbrepport.test3.test3.tMysqlInput_1Process(test3.java:491) at hbrepport.test3.test3.main(test3.java:740) Job test3 ended at 12:30 01/06/2007.
If the exception is saying that it can't put date '0000-00-00' in a java.sql.Date, it's because you extracted it as a DATE and not as a STRING in the schema. CLick on your tMysqlInput_1, edit its schema and on 'your_date_field' line, change the TYPE field to STRING instead of DATE.
Oh my bad, I'm so sorry denis. I forgot one very important step! Your SQL query must convert your date field in String first using any function which convert to string such as date_format (to get a particular formatting) EXAMPLE : SELECT date_format( my_date_field, '%Y-%m-%d %H:%i:%s') FROM MyTable;
