Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Converting Timestamp in JDBC

  1. #1

    Default Converting Timestamp in JDBC

    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
    Last edited by greg.soulsby; 11-10-2009 at 03:42 AM.

  2. #2
    Join Date
    Aug 2006
    Posts
    1,741

    Default

    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.

  3. #3
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    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
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  4. #4

    Default Cool

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.