SQLException in GenericDatabaseExtractor

[joker]

Hi,

I'm getting the following exception.

An exception occured while running tool org.wandora.application.tools.extractors.GenericDatabaseExtractor

org.wandora.topicmap.TopicMapException: java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 3 to TIMESTAMP.
at org.wandora.application.tools.extractors.GenericDatabaseExtractor.execute(GenericDatabaseExtractor.java:124)
at org.wandora.application.tools.AbstractAdminTool.run(AbstractAdminTool.java:192)
at java.lang.Thread.run(Thread.java:735)
Caused by: java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 3 to TIMESTAMP.
at com.mysql.jdbc.ResultSet.getTimestampFromString(ResultSet.java:5465)
at com.mysql.jdbc.ResultSet.getStringInternal(ResultSet.java:4886)
at com.mysql.jdbc.ResultSet.getString(ResultSet.java:4739)
at org.wandora.application.tools.extractors.GenericDatabaseExtractor.makeTopicMap(GenericDatabaseExtractor.java:337)
at org.wandora.application.tools.extractors.GenericDatabaseExtractor.execute(GenericDatabaseExtractor.java:112)
... 2 more

This is on Linux 2.6.18-92.el5 #1 SMP (RHEL). Db is mysql 5 and I use Java 1.6.

Could be related to this:

http://bugs.mysql.com/bug.php?id=18308

BR,

Juha

[Olli]

This problem is indeed related to the way MySQL converts zero dates to Java sql date objects. As documented here you can change this behavior with connection string parameters.

To do this in Wandora do the following.

-When database extractor tool (or any other tool for that matter) asks to select the database, create a new database connection. Then instead of using MySQL type, select Other so we have more control over connection parameters.

-Enter a name for the configuration, this can be anything you want.

-Driver for MySQL databases should be "com.mysql.jdbc.Driver".

-Connection string for MySQL is normally "jdbc:mysql://server/database". Now we want to append additional connection parameters here, specifically zeroDateTimeBehavior. So use "jdbc:mysql://server/database?zeroDateTimeBehavior=convertToNull". Replace "server" and "database" with your server ip address and database name respectively. This parameter will cause MySQL driver to convert zero dates to null values instead of throwing an exception.

-Finally enter database user name and password. You should leave the script part blank. Then click OK and select and use the new connection.