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:
    • 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: