Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Apparent Bug in KettleComponent.java

  1. #1
    Join Date
    Oct 2007
    Posts
    18

    Default Apparent Bug in KettleComponent.java

    I think I have stumbled across a bug in KettleComponent.java. I made the
    attached Kettle transformation with Spoon and it runs successfully there.
    However, when I run it under the Pentaho planform from an xaction file,
    then the Table Input step does not run correctly.

    The problem seems to involve the Set Variables step before the Table Input.
    In line 428 of KettleComponent.java, the list of fields expected in
    the result set from the SQL query is obtained by calling method
    RowMeta.getFieldNames. However, the obtained list includes the two
    variables from the Set Variables step. This means that instead of
    expecting the 3 fields from the query, KettleComponent expects 5 fields.
    Later, in rowWrittenEvent line 771, this causes an array bounds exception
    when the code attempts to access the fourth (non-existent) field in
    the result set.

    I made a simple workaround by changing line 428 of KettleComponent to
    call a new method instead of getFieldNames. The new method returns only
    the fields that have are originated in the Table Input step. That fixe
    the array bounds exception.

    Having done that, I ran across one other minor issue. The transformation
    file uses &apos.; for single-quotes in the query, instead of just literal
    single quotes. When I run the transformation from an xaction file,
    this prevents variable substitution within the quotes. However,
    to literal single quotes fixes that problem. Not sure if that's what
    is intended or not, but when running the transformation under Spoon
    the &apos.; doesn't prevent variable substitution.

    I'm running Kettle 3.0.0 GA with Pentaho platform from tag 1.7.0.931.
    The KettleComponent.java I'm using is downloaded from:

    http://wiki.pentaho.org/display/EAI/...ntaho+platform

    Let me know if you need any other info.

    Regards,
    Larry
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Thanks Larry, I created a JIRA case for that: http://jira.pentaho.org/browse/PDI-574

  3. #3
    Join Date
    May 2006
    Posts
    4,882

    Default

    The problem with your set variables step is not a bug, just wrong usage. The set variables step will add the fields to the output stream. You can remove them afterwards with a select values step.

    AND you can not set a variable in the same transformation where you will use it... sometimes it will work, sometimes it wont

    For the single quotes raise a JIRA in the appropriate department.

    Regards,
    Sven

  4. #4
    Join Date
    Oct 2007
    Posts
    18

    Default

    I'm confused ... the reason I have set up the variables is so that
    the query in my Table Input step can use them. Once the
    variables are present in the stream, then it causes the Table
    Input to crash with the array bounds exception. So how can
    I set up variables and then use them in a query?

    I've tried using the ? approach, but these don't seem to get
    evaluated inside single quotes. (I'm trying to pass date
    parameters and I need to convert these using Postgres
    to_date which involves single quotes.)

    Regards,
    Larry

  5. #5
    Join Date
    May 2006
    Posts
    4,882

    Default

    Still have to debug it, but I guess the table output step requires some extra code hardening... you could use select values to remove the extra "variable" values and it would probably work without exceptions.

    For the non-use of a variable in the same transformation as where it's being set.... have a look at the "process all tables" example in the samples directory.

    If you only need to set 1 row of variables (in spoon, not so sure about xactions):
    - Make a job
    - start -> transformation 1 -> transformation 2
    - Transformation 1 does system info and just sets variables
    - Transformation 2 does the same system info and links to the table input. In the table input you can then use the variables set in transformation 1.

    Regards,
    Sven

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    There is no need to be confused. As mentioned all over this forum and in the FAQ, the rule is very simple:

    Thou shall not use and set a variable in the same transformation!
    As Sven mentions, it is because all steps run in parallel. (it's a feature, not a bug, see the FAQ)

    On the subject of your question, an example that very very very much applies to your situation (get input from a "Get System Info" step into a "Table Input" step) is shown in the Spoon documentation.
    I suggest you check it out.

    Matt

  7. #7
    Join Date
    May 2006
    Posts
    4,882

    Default

    + there's a difference between parameters and variables.

    Parameters never use 's around them

    Regards,
    Sven

  8. #8
    Join Date
    Oct 2007
    Posts
    18

    Default

    I originally tried using Get System Info and then feeding that into a
    Table Input which used the parameters. But I ran into problems with single
    quotes.

    Specifically what I'm trying to do is:

    - In the action sequence, I have three (Pentaho) parameters from the user which
    are a year, month and day, e.g. '2007', '12', '01'.

    - I intend to get these into KettleComponent as parameters using Get System
    Info.

    - Then, the Postgres query run by the transformation would be like this:

    select firstname, lastname from customer
    where orderdate > to_date('???', 'YYYYMMDD')

    And hopefully the parameters would be substituted to become:

    select firstname, lastname from customer
    where orderdate > to_date('20071201', 'YYYYMMDD')

    Postgres wants the text to be within single quotes. But the problem I have
    is that the parameters don't seem to get resolved when they are within
    single quotes, I guess because parameters work that way. Hence my interest
    in variables which seem to work even within single quotes.

    So I guess the best approach is a job with multiple transformations, setting
    up the variables in one and using them in the second, as suggested by Sven?

    Regards,
    Larry

  9. #9
    Join Date
    May 2006
    Posts
    4,882

    Default

    parameters don't use 's ... in Oracle you would need to use something as ? || ? || ? without any quotes.

    Regards,
    Sven

  10. #10
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Larry, just look at the example in the documentation. (Table Input step)

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.