Uploaded image for project: 'Pentaho Data Integration - Kettle'
  1. Pentaho Data Integration - Kettle
  2. PDI-8753

java stack trace when have a table with a date or timestamp Unable to get value 'Date' from database (Vertica)

    Details

    • Type: Bug
    • Status: Closed
    • Severity: High
    • Resolution: Duplicate
    • Affects Version/s: 4.4.0 GA (4.8.0 GA Suite Release)
    • Component/s: Database
    • Labels:
      None
    • Environment:
      any, I was working on windows 2008 server 64bit
    • 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.

      Description

      when use a table input and table output with a column defined with date or timestamp get stack trace. Happens with Vertica DB 6.0 and the following vertica jdbc drivers vertica-jdk5-6.0.0-0.jar ,vertica_5.1.6_jdk_5.jar, vertica_5.1.1_jdk_5.jar
      I will include a simple job along with the ddl for the tables. You can install the sample database Vertica provides in /opt/vertica/examples. This will load the source table for you. My script will create the target table. It also has the source table if you can not load the examples for some reason.

      does not work..
      SELECT
      date_key
      ,date
      , full_date_description
      , day_of_week
      , day_number_in_calendar_month
      , day_number_in_calendar_year
      , day_number_in_fiscal_month
      , day_number_in_fiscal_year
      , last_day_in_week_indicator
      , last_day_in_month_indicator
      , calendar_week_number_in_year
      , calendar_month_name
      , calendar_month_number_in_year
      , calendar_year_month
      , calendar_quarter
      , calendar_year_quarter
      , calendar_half_year
      , calendar_year
      , holiday_indicator
      , weekday_indicator
      , selling_season
      FROM public.date_dimension

      does work.
      SELECT
      date_key
      , full_date_description
      , day_of_week
      , day_number_in_calendar_month
      , day_number_in_calendar_year
      , day_number_in_fiscal_month
      , day_number_in_fiscal_year
      , last_day_in_week_indicator
      , last_day_in_month_indicator
      , calendar_week_number_in_year
      , calendar_month_name
      , calendar_month_number_in_year
      , calendar_year_month
      , calendar_quarter
      , calendar_year_quarter
      , calendar_half_year
      , calendar_year
      , holiday_indicator
      , weekday_indicator
      , selling_season
      FROM public.date_dimension

      Here is the stack trace:
      2012/10/16 14:55:20 - Spoon - Transformation opened.
      2012/10/16 14:55:20 - Spoon - Launching transformation [test_edw_lock_bug]...
      2012/10/16 14:55:20 - Spoon - Started the transformation execution.
      2012/10/16 14:55:20 - Transformation metadata - Natural sort of steps executed in 0 ms (2 time previous steps calculated)
      2012/10/16 15:02:03 - Spoon - The transformation has finished!!
      2012/10/16 15:15:52 - /Transformation 1 - Dispatching started for transformation [/Transformation 1]
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : Unexpected error
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : Couldn't get row from result set
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) :
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : Unable to get value 'Date' from database resultset, index 1
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : [Vertica][JDBC](10140) Error converting value to Timestamp.
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) :
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) :
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : at org.pentaho.di.core.database.Database.getRow(Database.java:2758)
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : at org.pentaho.di.core.database.Database.getRow(Database.java:2730)
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : at org.pentaho.di.core.database.Database.getRow(Database.java:2704)
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:257)
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:143)
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : at java.lang.Thread.run(Unknown Source)
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : Unable to get value 'Date' from database resultset, index 1
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : [Vertica][JDBC](10140) Error converting value to Timestamp.
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) :
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : at org.pentaho.di.core.database.BaseDatabaseMeta.getValueFromResultSet(BaseDatabaseMeta.java:1900)
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : at org.pentaho.di.core.database.DatabaseMeta.getValueFromResultSet(DatabaseMeta.java:2613)
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : at org.pentaho.di.core.database.Database.getRow(Database.java:2750)
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : ... 6 more
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : Caused by: java.sql.SQLException: [Vertica][JDBC](10140) Error converting value to Timestamp.
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : at com.vertica.jdbc.SForwardResultSet.getTimestamp(Unknown Source)
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : at org.pentaho.di.core.database.BaseDatabaseMeta.getValueFromResultSet(BaseDatabaseMeta.java:1887)
      2012/10/16 15:15:52 - Table input.0 - ERROR (version 4.4.0, build 1 from 2012-09-19 16.37.26 by buildguy) : ... 8 more
      2012/10/16 15:15:52 - Table input.0 - Finished reading query, closing connection.
      2012/10/16 15:15:52 - Table input.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=1)
      2012/10/16 15:15:52 - /Transformation 1 - /Transformation 1
      2012/10/16 15:15:52 - /Transformation 1 - /Transformation 1

      1. date_dim_error.ktr
        13 kB
        mark warner
      2. date_dim_tbls.sql
        2 kB
        mark warner

        Issue Links

          Activity

          Hide
          mwarner@vertica.com mark warner added a comment -

          example job just add the date to the source table and it will fail.

          Show
          mwarner@vertica.com mark warner added a comment - example job just add the date to the source table and it will fail.
          Hide
          mwarner@vertica.com mark warner added a comment -

          here are the source and target tables.

          Show
          mwarner@vertica.com mark warner added a comment - here are the source and target tables.
          Hide
          sflatley Sean Flatley (Inactive) added a comment -

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

          Show
          sflatley Sean Flatley (Inactive) added a comment - Changes checked into TRUNK, 4.4.0, 4.4.0-GA and 4.4.1 at revision 17501.
          Hide
          bbruce Brandon Bruce (Inactive) added a comment -

          closing as a dupe

          Show
          bbruce Brandon Bruce (Inactive) added a comment - closing as a dupe

            People

            • Assignee:
              sflatley Sean Flatley (Inactive)
              Reporter:
              mwarner@vertica.com mark warner
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: