Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Table input error with parametized fields - PostgreSQL

  1. #1
    Join Date
    Apr 2014
    Posts
    2

    Default Table input error with parametized fields - PostgreSQL

    Hi,

    I'm having a lot of headaches about getting field replacement to work in Table input.
    After reading the docs and forums I can't make this simple example to work correctly:

    Table input confs:

    SQL: SELECT object.fk_user_id AS email FROM objects WHERE objects.id=?
    Insert data from step: parent step
    Execute for each row: checked

    "?" should be replaced by the value of the first field in input row. Am I right?

    Instead, what I get is this error:

    Code:
    2014/04/03 19:21:01 - Table input.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Unexpected error
    2014/04/03 19:21:01 - Table input.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
    2014/04/03 19:21:01 - Table input.0 - An error occurred executing SQL:
    2014/04/03 19:21:01 - Table input.0 - SELECT objects.fk_user_id AS email FROM objects WHERE objects.id=?
    2014/04/03 19:21:01 - Table input.0 - 
    2014/04/03 19:21:01 - Table input.0 - offending row : [object_id Integer], [md5 String], [country String], [email String], [timestamp Timestamp], [hour Integer], [day Integer], [month Integer], [year Integer]
    2014/04/03 19:21:01 - Table input.0 - 
    2014/04/03 19:21:01 - Table input.0 - Error setting value #2 [String] on prepared statement
    2014/04/03 19:21:01 - Table input.0 - The column index is out of range: 2, number of columns: 1.
    I'm I doing something wrong? Is this a bug?
    I'm using Kettle 5.0.1-stable

    Any help is appreciated.

    Thanks
    Last edited by abranches; 04-03-2014 at 02:40 PM.

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Since your query only has one ?, PDI expects the data flow to only have one field.
    If your query has two ?'s, PDI expects the data flow to have exactly two fields.

    etc...

  3. #3
    Join Date
    Jul 2009
    Posts
    476

    Default

    The Table Input step is designed to be an initial step in a row stream, not a middle step. Try the Database Lookup step instead.

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by robj View Post
    The Table Input step is designed to be ... not a middle step.
    Come on, you know better than that!

    Name:  SURPRISE.png
Views: 143
Size:  4.6 KB
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Jul 2009
    Posts
    476

    Default

    Marabu, you are correct. In fact, I used to have transformations that used Table Input steps in the middle, but it just seemed wrong to do it that way, and I forgot that the checkbox there is what made them work.

  6. #6
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Now you make me feel bad for joking, robj, but it's all my own fault.

    Maybe we shouldn't talk about middle steps, when we actually want to refer to transforming steps.
    Even though "Table Input" can have incoming rows, those are only for configuration and nothing will show up downstream.
    We can have a most complicated data flow leading to a TI step, and, although TI is positioned in the middle of it all, technically it's still an input step.

    Please, forgive me.
    So long, and thanks for all the fish.

  7. #7
    Join Date
    Apr 2014
    Posts
    2

    Default

    Sorry for the late answer.
    Thank you for your clarification. I understood the concept of not using table input as a middle step. I replaced my first example by a Database lookup and it did the job.

    However, what if I need some other queries besides a simple lookup?
    I need to set a field value to true of false depending whether some field exists in a table. By SQL it would be something like:

    SELECT EXISTS(SELECT * FROM CertifiedPublishers WHERE email=?) AS is_certified_publisher;

    In my understanding this would be quite trivial if table input could receive a use only a single field and output the new field value "is_certified_publisher".
    Do you I need to process this kind of logic using pentaho steps?

    Thanks
    Last edited by abranches; 04-15-2014 at 09:07 AM.

  8. #8

    Default

    Quote Originally Posted by abranches View Post
    Sorry for the late answer.
    Thank you for your clarification. I understood the concept of not using table input as a middle step. I replaced my first example by a Database lookup and it did the job.

    However, what if I need some other queries besides a simple lookup?
    I need to set a field value to true of false depending whether some field exists in a table. By SQL it would be something like:

    SELECT EXISTS(SELECT * FROM CertifiedPublishers WHERE email=?) AS is_certified_publisher;

    In my understanding this would be quite trivial if table input could receive a use only a single field and output the new field value "is_certified_publisher".
    Do you I need to process this kind of logic using pentaho steps?

    Thanks
    The existence of a field within a table can be much easier established by querying the available views on the metadata. For SQL Server, PostgreSQL and MySQL these would be within the information_schema views. Every relational database has these metadata views in some way, so you need not to write explicit queries against 'real' tables to check existance!!

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.