Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Oracle date / timestamp problem using "Dimension lookup/update"

  1. #1
    Join Date
    Dec 2013
    Posts
    7

    Default Oracle date / timestamp problem using "Dimension lookup/update"

    Using Kettle 5.0.1 stable.

    Oracle (JDBC) seems to send wrong metadata information "timestamp" for "date" datatype.

    To reproduce:
    SQL> create table atab ( id integer, fach_id integer, name varchar(255), begin date, ende date, version integer );

    And use the attached transformation (ora.ktr) to lookup the dataset: fach_id=10, name='Hans'

    As expected, there will be added two rows:
    0, null, null, ..., 1
    1, 10, 'Hans', '01.01.00', '31.12.99', 1
    The first is the well-know "missing" and the second results from the insert von (10, 'Hans') with open end and open begin date.

    Now we change the value of 'Hans' to 'Uli':
    SQL> update atab set name='Uli' where fach_id=10;
    SQL> commit;

    Then we run the transformation again, in order to lookup for 'Hans' which will not found.
    The transformation tries to insert the new row and update the old one, which results in the following error:

    2014/01/12 19:26:09 - Dimension lookup/update.0 - Caused by: java.lang.RuntimeException: BEGIN Timestamp : There was a data type error: the data type of java.util.Date object [Sun Jan 12 19:26:08 CET 2014] does not correspond to value meta [Timestamp]
    2014/01/12 19:26:09 - Dimension lookup/update.0 - at org.pentaho.di.core.row.value.ValueMetaTimestamp.writeData(ValueMetaTimestamp.java:494)

    Any help would be appreciated!
    Attached Files Attached Files

  2. #2

    Default

    I am having this exact same issue but with MySQL 5.5.29 running on OS X Mavericks. PDI v5.0.1 stable. I am a new user it should be noted. Dimension loads up fine on first run but has trouble any time it needs to update/insert a record.

  3. #3

    Default

    I was actually able to reproduce this error by using a datagrid as the input. The Dimension Lookup/Update step errors when it tries to deal with the "date_to" field as described in the first post. Attachedtest_dimension.ktr is the transformation I tried using MySQL 5.5.29, PDI 5.0.1.

    The other interesting thing is that "Get SQL" always seems to want to alter the dimension table's date_from and date_to fields to DATETIMES even though they already are...

    Quote Originally Posted by chriskozlowski View Post
    I am having this exact same issue but with MySQL 5.5.29 running on OS X Mavericks. PDI v5.0.1 stable. I am a new user it should be noted. Dimension loads up fine on first run but has trouble any time it needs to update/insert a record.

  4. #4
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    If it's a reproducible error, maybe you can report it as a bug?
    http://jira.pentaho.com
    -- Mick --

  5. #5

    Default

    I submitted this issue as a bug with a supporting transformation that should reproduce the error:
    http://jira.pentaho.com/browse/PDI-11353

    Quote Originally Posted by Mick_data View Post
    If it's a reproducible error, maybe you can report it as a bug?
    http://jira.pentaho.com

  6. #6
    Join Date
    Dec 2013
    Posts
    7

    Default Solution: Oracle maps date to timestamp

    Got the solution:

    The problem has to deal with the "mapping date to timestamp" in Oracle which can be deactivated by setting a connection property, eg. using shared.xml see below.
    Setting it this way, date are dates and no timestamps via JDBC.

    <connection>
    <name>...</name>
    <server>...</server>
    <type>ORACLE</type>
    <access>Native</access>
    <database>...</database>
    <port>1521</port>
    <username>abx_co</username>
    <password>...</password>
    <servername/>
    <data_tablespace/>
    <index_tablespace/>
    <attributes>
    <attribute><code>EXTRA_OPTION_ORACLE.oracle.jdbc.mapDateToTimestamp</code><attribute>false</attribute></attribute> !!!!! this line !!!!!
    <attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute>
    ....
    </attributes>
    </connection>

  7. #7

    Default

    This essentially solves the problem for MySQL too. I had to go under the DB Connection properties->Advanced->Uncheck "Supports the timestamp datatype" option. All fixed. THANK YOU!!!!!!

    Quote Originally Posted by kgdeck View Post
    Got the solution:

    The problem has to deal with the "mapping date to timestamp" in Oracle which can be deactivated by setting a connection property, eg. using shared.xml see below.
    Setting it this way, date are dates and no timestamps via JDBC.

    <connection>
    <name>...</name>
    <server>...</server>
    <type>ORACLE</type>
    <access>Native</access>
    <database>...</database>
    <port>1521</port>
    <username>abx_co</username>
    <password>...</password>
    <servername/>
    <data_tablespace/>
    <index_tablespace/>
    <attributes>
    <attribute><code>EXTRA_OPTION_ORACLE.oracle.jdbc.mapDateToTimestamp</code><attribute>false</attribute></attribute> !!!!! this line !!!!!
    <attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute>
    ....
    </attributes>
    </connection>

Tags for this Thread

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.