Pentaho Data Integration - Kettle
  1. Pentaho Data Integration - Kettle
  2. PDI-8688

Error Converting Vertica DATE data-type in Table Output Step

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 4.3.0 GA (4.5.0 GA Suite Release)
    • Component/s: Transformation
    • Labels:
      None
    • Environment:
      Windows 7, Vertica 6.0.0 JDBC driver, Vertica 6 server
    • PDI Sub-component:
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.
    • QA Validation Status:
      Validated by QA
    • CI Build:
      PDI EE Assembly 4.4.4 612

      Description

      When using a transformation to output to a text file from SQL (Table Input step -> Text File Output step) it fails when there is a field of data type DATE.

      Example SQL:

      SELECT CAST('2012-10-05' AS DATE) AS "someDate";

      Error Log:

      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Unexpected error
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Couldn't get row from result set
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Unable to get value 'Date' from database resultset, index 0
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : [Vertica][JDBC](10140) Error converting value to Timestamp.
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.getRow(Database.java:2824)
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.getRow(Database.java:2796)
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.getRow(Database.java:2770)
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:257)
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:143)
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at java.lang.Thread.run(Unknown Source)
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Unable to get value 'Date' from database resultset, index 0
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : [Vertica][JDBC](10140) Error converting value to Timestamp.
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.BaseDatabaseMeta.getValueFromResultSet(BaseDatabaseMeta.java:1900)
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.DatabaseMeta.getValueFromResultSet(DatabaseMeta.java:2613)
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.getRow(Database.java:2816)
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : ... 6 more
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Caused by: java.sql.SQLException: [Vertica][JDBC](10140) Error converting value to Timestamp.
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at com.vertica.utilities.conversion.TypeConverter.toTimestamp(Unknown Source)
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at com.vertica.jdbc.SForwardResultSet.getTimestamp(Unknown Source)
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.BaseDatabaseMeta.getValueFromResultSet(BaseDatabaseMeta.java:1887)
      2012/10/05 12:45:48 - Table input 2.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : ... 8 more

      1. VERTICA_5.ktr
        11 kB
        Sean Flatley
      2. VERTICA_6.ktr
        11 kB
        Sean Flatley

        Issue Links

          Activity

          Hide
          Sean Flatley added a comment -

          Changes checked into TRUNK, 4.4.0, 4.4.0-GA and 4.4.1 at revision 17501.

          Show
          Sean Flatley added a comment - Changes checked into TRUNK, 4.4.0, 4.4.0-GA and 4.4.1 at revision 17501.
          Hide
          Sean Flatley added a comment - - edited

          Validation

          • The attached job can be used to create a
            table to read and write to. The table has a date
            key and a date column.
          • Enginerring has a QA Vertica 5 database. Check the IWiki
            for details.
          • I have a VM that has Vertica 6. So does Matt Burgess.
            See us if you are validating with Vertica 5.

          This matrix shows what database type and jar to use when validating
          with Vertica 5 and 6.

          Database / Kettle Connection Type / JDBC Driver / DatabaseMeta
          ------------------------------------------------------------------------------------------------------------------------
          Vertica 5 / Vertica / vertica_4.1.17_jdk_5.jar / VerticaDatabaseMeta
          Vertica 6 / Vertica5++ / vertica-jdk5-6.0.0-0.jar / Vertica5DatabaseMeta

          • The attached transformations can be used to validate.

          VERTICA_5.ktr
          VERTICA_6.ktr

          Show
          Sean Flatley added a comment - - edited Validation The attached job can be used to create a table to read and write to. The table has a date key and a date column. Enginerring has a QA Vertica 5 database. Check the IWiki for details. I have a VM that has Vertica 6. So does Matt Burgess. See us if you are validating with Vertica 5. This matrix shows what database type and jar to use when validating with Vertica 5 and 6. Database / Kettle Connection Type / JDBC Driver / DatabaseMeta ------------------------------------------------------------------------------------------------------------------------ Vertica 5 / Vertica / vertica_4.1.17_jdk_5.jar / VerticaDatabaseMeta Vertica 6 / Vertica5++ / vertica-jdk5-6.0.0-0.jar / Vertica5DatabaseMeta The attached transformations can be used to validate. VERTICA_5.ktr VERTICA_6.ktr
          Hide
          mark warner added a comment -

          Can you please clarify if this is resolved in 4.8 and if so how?

          Show
          mark warner added a comment - Can you please clarify if this is resolved in 4.8 and if so how?
          Hide
          Jens Bleuel added a comment -

          Mark, it was fixed by return false by supportsTimeStampToDateConversion() and will be in the coming 4.8 suite release (PDI 4.4.0)
          If you want to test it, you can check a nightly build on http://ci.pentaho.com/view/Data%20Integration/job/Kettle-4.4/

          Show
          Jens Bleuel added a comment - Mark, it was fixed by return false by supportsTimeStampToDateConversion() and will be in the coming 4.8 suite release (PDI 4.4.0) If you want to test it, you can check a nightly build on http://ci.pentaho.com/view/Data%20Integration/job/Kettle-4.4/
          Hide
          Golda David added a comment -

          Tested both Vertica 5 and 6 databases and the output format for the DATE field was converted properly.

          This issue is resolved in PDI 4.4 GA

          Show
          Golda David added a comment - Tested both Vertica 5 and 6 databases and the output format for the DATE field was converted properly. This issue is resolved in PDI 4.4 GA

            People

            • Assignee:
              Golda David
              Reporter:
              Mike Sloper
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: