Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Retrieving auto-generated key from Table output

  1. #1

    Default Retrieving auto-generated key from Table output

    Hi everybody,

    I have this transformation (see attachment):

    1)Read data from source table

    id
    username
    log
    date

    2)Filter the data to prepare the dataset to be inserted in another table. Fields that will pass through the filter:

    username rename_to user
    date rename_to date_entered

    3)Table output: inserts the fields into the table called user_reg. This table has an auto-incremental key called id. I need the generated key for further usage so I checked the checkbox to get back the key that was generated by inserting a row into the table. Also, I’ve specified the name of the new field in the output rows that will contain the auto-generated key as “log_id”.

    4)I need to save a log file containing the content of the “log” field (see step 1) along side with the id generated in step 3 (log_id)

    Issues description:

    A)Although the step 4 (“4) Text file output”) is allowing me to define the log_id as an output field (by including the field in the list after clicking Get Fields), spoon is throwing an exception during the execution: “Field [log_id] couldn’t be found in the input stream!” What am I doing wrong?

    B)In the step 4 I’ll need to save the log field to the file, but this field was filtered in step 2 and cannot be found on the input stream. Is there any approach to work around this kind of situations?

    Thanks a lot!!!

    Mauro.
    Attached Files Attached Files

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

    Default

    In 3) Use id instead if log_id.

  3. #3

    Default That doesn't fix it

    Thanks for you fast reply sboden, but unfortunately your suggestion didn't fix any of my two issues.

    There is only one change noticeable: now the error message says "Field [id] couldn’t be found in the input stream!".

    Thanks,

    Mauro.

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

    Default

    lol ...
    1) Run Verify transformation
    2) Use right click on a step (I would start with the table output) and then show output to see where you go wrong. It's something with your id field.

    For A) you can put any field you like in there... the real check is only done at executon time and this is way you get an exception then and not before. A hint... the fields you get with get fields are the only ones which will be available

    For B) no "easy" one. The work around consist of doing the insert. And then in another transformation doing the same but instead of writing rows looking up the generated key.

    Regards,
    Sven

  5. #5

    Default

    Hi Sven,

    For A) after trying several combinations of id's in spoon and in the database I was about to give up and suddenly came to my mind the possibility of a bug in spoon. It turned to be the case. Take a look of this: http://jira.pentaho.org/browse/PDI-473 this bug affected 3.0.0 GA and was fixed in 3.0.1. So I'll have to wait for 3.0.1.

    For B) Yep, unfortunately you're right, there isn't a clean solution for this issue. It would be great having a Table Output step with a field definition tab then we won't need to use a filter.

    Thanks for your help and for your time!!!

    Mauro.

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

    Default

    Seems you're right for A) ... you try to download the nightly build of Kettle at ftp://download.pentaho.org/client/pe...a-integration/ but it feels like it's hosted using "barbed wire" as internet connection

    For B) ... it's in JIRA http://jira.pentaho.org/browse/PDI-16 , it's a good idea, it will get in some time... vote for it, it's not making much different right now but if a lot of people start voting for it may increase its priority.

    Regards,
    Sven

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.