Hitachi Vantara Pentaho Community Forums
Results 1 to 13 of 13

Thread: Passing a tablename parameter to table input in spoon (kettle)

  1. #1
    Join Date
    Dec 2008
    Posts
    6

    Default Passing a tablename parameter to table input in spoon (kettle)

    I am trying to pass a parameter containing a tablename to an sql query that will return a result from the specified table.

    When attempting this with a Table Input step (ie SELECT a FROM ? WHERE b=10; ) i get an issue where the parameter is inputted into the sql query as a string (ie with quotes). [hence will fail].

    Alternatively i have tried to use an Execute SQL Script step which will correctly input the parameter into the sql query without string quotes but when this query runs successfully, there are no usuable fields or outputs from this step.

    Any ideas?

    - Michael
    Last edited by MichaelO; 12-03-2008 at 01:55 AM. Reason: grammar

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

    Default

    You cannot bind table or column names with ?'s (it's a JDBC/SQL restriction).

    You can use variables for table names... but that requires some extra setting up (of the variables).

    Regards,
    Sven

  3. #3
    Join Date
    Dec 2008
    Posts
    6

    Default

    Thankyou for the reply sboden.

    I have been playing around with variables, but i cannot seem to get them to update correctly on each transformation. Can you please provide a very simple example ktr file or a description on what this setup of variables requires?

    Thanks,

    Michael
    Last edited by MichaelO; 12-03-2008 at 08:24 PM.

  4. #4
    Join Date
    Dec 2008
    Posts
    6

    Default

    Within my xaction i can successfully process a 'Pentaho Data Integration' action to call a kettle transformation and return a result set. Hence successfully generating my report. (ie just like http://www.nicholasgoodman.com/bt/bl...ettle-for-eii/)

    This is fine except that i need to use variables within my transformation. From testing and what i have read so far, if i want to use variables then i have to create a job (which includes transformation steps to set variable and then other steps to use that variable). I have created a job that successfully uses variables and can output a resultset to a file or database table correctly, although i now want to return this resultset directly to the xaction.

    Now i want to call this job from my xaction and return a resultset, but i get an Kettle.ERROR_0012 - Could not set up row listener error.

    How can i return the result set from my kettle job?


    Regards,
    Michael
    Last edited by MichaelO; 12-03-2008 at 11:46 PM.

  5. #5
    Join Date
    Jul 2008
    Posts
    23

    Default

    MicheaelO, just simply add the transformation step Copy rows to result in the end of your transformation. THis step will copy the rows and send them to the xaction.

    //Triii

  6. #6
    Join Date
    Dec 2008
    Posts
    6

    Default

    This works if you have only a transformation file (.ktr), but when you are calling a kettle job file from your xaction, then this last step Copy rows to result (in the last transformation of the job) results in the error i mentioned previously.

    - Michael
    Last edited by MichaelO; 12-04-2008 at 06:01 AM.

  7. #7
    Join Date
    Dec 2008
    Posts
    6

    Default

    Anyone know how to return a result set directly from kettle Job to xaction?

    - Mike

  8. #8
    Join Date
    Jul 2008
    Posts
    23

    Question

    MichaelO, did you solve this problem? If yes; how?

  9. #9
    Join Date
    Dec 2008
    Posts
    6

    Default

    No I have not solved this problem.

    My kettle jobs are currently writing the resultset back to various 'report' tables on the database. This is incredibly frustrating as i now need to maintain not only reports, but report tables as well.

    The only advantage i can see over using stored procedures is that the business logic is kept seperate from the data. Preferably I want the pdi to access the data and then push it to the report.
    Unfortunately as it stands I'm pretty sure this is not possible.

    If anyone knows a way to achieve this, please let me know.

    - MichaelO
    Last edited by MichaelO; 01-13-2009 at 07:49 PM.

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

    Default

    The only ETL sample on the BI server actually passes data directly from a transformation step onto a report/graph/... through an xaction.
    Many other samples can be found on the internet. (for example on the blog from Nick Goodman)

  11. #11
    Join Date
    Sep 2007
    Posts
    834

    Default

    MichaelO,
    I faced the same problem:
    No way to pass parameters to a job from an xaction, neither to return a result set directly from kettle Job to the xaction.
    However, with Transformations everything works fine. In my case, I fixed it replacing the Job by a couple of Transformation and temporary files/tables. Maybe this works for you too.

    regards,
    mc

    Quote Originally Posted by MichaelO View Post
    No I have not solved this problem.

    My kettle jobs are currently writing the resultset back to various 'report' tables on the database. This is incredibly frustrating as i now need to maintain not only reports, but report tables as well.

    The only advantage i can see over using stored procedures is that the business logic is kept seperate from the data. Preferably I want the pdi to access the data and then push it to the report.
    Unfortunately as it stands I'm pretty sure this is not possible.

    If anyone knows a way to achieve this, please let me know.

    - MichaelO

  12. #12
    Join Date
    Nov 1999
    Posts
    459

    Default xaction: get result set of a Data Integration job

    Here is the feature request:
    http://jira.pentaho.com/browse/BISERVER-258

    and for the variables set by the component:
    http://jira.pentaho.com/browse/BISERVER-257

    Vote for them ;-)

    Cheers,
    Jens
    Last edited by jbleuel; 01-15-2009 at 05:11 PM. Reason: addition

  13. #13
    Join Date
    Oct 2012
    Posts
    4

    Default

    Allthough this topic is originated several years ago, I would like to add that, using Spoon 4.2.1, I have solved this problem by using the option Dynamic SQL, as described in http://wiki.pentaho.com/display/EAI/Dynamic+SQL+row.

    Maybe this can help someone.

    Regards,
    -Dzo

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.