Converting Timestamp in JDBC

[greg.soulsby]

When trying to get the JDBC link going I am unable to read the TIMESTAMP Mysql type. Getting

Couldt read from database: Cannot convert value "00-0000 00:00:00" to from column 6 to TIMESTAMP.

But I have TIMESTAMP=0, (and tried TIMESTAMP=9) which I thought told Weka to treat a timestamp like a string?

Any suggestions?

The database setting are:

# Database settings for MySQL 3.23.x, 4.x
#
# url: http://www.mysql.com/
# jdbc: http://www.mysql.com/products/connector/j/
# author: Fracpete (fracpete at waikato dot ac dot nz)
# version: $Revision: 1.3 $

# JDBC driver (comma-separated list)
jdbcDriver=org.gjt.mm.mysql.Driver
# jdbcDriver=com.mysql.jdbc.Driver

# database URL
# jdbcURL=jdbc:mysql://localhost:3306/db
jdbcURL=jdbc:mysql://localhost:3306/db


# specific data types
# string, getString() = 0; --> nominal
# boolean, getBoolean() = 1; --> nominal
# double, getDouble() = 2; --> numeric
# byte, getByte() = 3; --> numeric
# short, getByte()= 4; --> numeric
# int, getInteger() = 5; --> numeric
# INT, getInteger() = 5; --> numeric
# INT., getInteger() = 5; --> numeric
# long, getLong() = 6; --> numeric
# gloat, getFloat() = 7; --> numeric
# date, getDate() = 8; --> date
# text, getString() = 9; --> string

#Text
CHAR=0
TINYTEXT=0
TEXT=0
VARCHAR=0
LONGVARCHAR=0
BINARY=0
VARBINARY=0
LONGVARBINARY=0
BLOB=0
MEDIUMTEXT=0
MEDIUMBLOB=0
LONGTEXT=0
LONGBLOB=0

#Number types
BIT=1
NUMERIC=2
DECIMAL=2
FLOAT=2
DOUBLE=2
TINYINT=3
SMALLINT=4
#SHORT=4
SHORT=5
INTEGER=5
INT=5
INT_UNSIGNED=5
MEDIUMINT=5
BIGINT=6
LONG=6

#Data Types
REAL=7
DATE=8
TIME=8
TIMESTAMP=0
DATETIME=0

#mappings for table creation
CREATE_STRING=VARCHAR(8000)
CREATE_INT=INT
CREATE_DOUBLE=DOUBLE

#database flags
checkUpperCaseNames=false
setAutoCommit=true
createIndex=false

#flags for loading and saving instances using DatabaseLoader/Saver
nominalToStringLimit=50
idColumn=auto_generated_id




# other options
CREATE_DOUBLE=DOUBLE
CREATE_STRING=TEXT
CREATE_INT=INT
checkUpperCaseNames=false
checkLowerCaseNames=false
checkForTable=true

[Mark]

Hi Greg,

Are you sure that TIMESTAMP=9 doesn't work? It looks to me like this column is a TIMESTAMP column created in an older version of MySQL (< 4.1) and now you have a more recent version installed. In the older version, TIMESTAMP was stored and returned as a number. In newer versions TIMESTAMP is stored and returned as a string (and old TIMESTAMP values are converted and returned as strings). This looks to be the case here as the value you mention has length 17 and new TIMESTAMP values always have length 19. See:

http://dev.mysql.com/doc/refman/4.1/...p-pre-4-1.html

If it is stored/returned as a string, then accessing it as a string (9) should work. Alternatively, you could try accessing it as a date (8) with the appropriate conversion string specified in the props file. Looks to me like this entry (under other options in the props file) might convert it OK:

DateFormat=yy-MMdd HH:mm:ss

Cheers,
Mark.

[Taqua]

You can control the behaviour of the JDBC driver via the "zeroDateTimeBehavior" property.
Set it to "round" or "convertToNull" to get rid of the Exception.

http://dev.mysql.com/doc/refman/5.1/...roperties.html

[greg.soulsby]

Thanks Guys. For future google searchers with this problem, found you can set the JDBC properties through the url. This worked:

jdbc:mysql://localhost:3306/myDB?user=MyUser&password=MyPass&zeroDateTimeBehavior=round

This did not:

jdbc:mysql://localhost:3306/myDB?user=MyUser&password=MyPass&zeroDateTimeBehavior=convertToNull

This version gives a different, meaningless error something like

Couldt read from database: null

Thought that might assist other Java challenged, it would save a lot of time if the values like this and the

#Text
CHAR=0
TINYTEXT=0
etc

lines required in databaseUtil.props could be places in databaseUtil.prop.mysql

Thanks again.