Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Is it possible to pass a Database Connection as a parameter to a Transformation

  1. #1
    Join Date
    Sep 2007
    Posts
    13

    Default Is it possible to pass a Database Connection as a parameter to a Transformation

    I have a job that needs to run multiple Transformations. All of these transformations will be basically identical, except that they will be hitting different boxes. Each box has an identical schema, the only differences are the URL and DB name.

    I would much rather maintain a single Transformation and pass these arguments than have to create a different transform for each database, and maintain them all separately.

    Is this possible?

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

    Default

    You can use variables as long as the database are all of the same type. Variables for IP address, user name, password, ...

    Regards,
    Sven

  3. #3
    Join Date
    Sep 2007
    Posts
    13

    Default

    Ok, so for example I have a job, in the transformation step, and I pass two arguments one for the url and one for the db name, correct?

    Now when I'm in the transformation and using for example a table input. How do I point the connection's host and db name to the arguments passed?

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

    Default

    What you could do:
    - Make a job with a setup transformation followed by your real transformation
    - In the setup transformation use a get system info step to get at the first 3 arguments e.g. and pass those to a set variable step. As argument use IP, userid, password (in case of Oracle also portnr e.g.)
    - In the real transformation use the variables in the connection like ${database} (if you used database as the name of the variable in set variable step).
    - Run the job specifying 3 arguments to use.

    Regards,
    Sven

  5. #5
    Join Date
    Sep 2007
    Posts
    13

    Default

    Does this imply that I need a separate setup transformation for each actual transformation?

    While this would be a marked improvement, I still feel like maintaining a separate transformation for each box is too much. Is there a way that I can keep all of my maintenance to the job and the actual transformation?

    Also, does this mean that I will need to run each instance of the actual transformation in a linear fashion so as not to continually over write the variable i have set?
    Last edited by kbrown; 09-07-2007 at 02:53 PM.

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

    Default

    The job and the 2 transformations stay the same, you just have to supply different arguments.... via the get system info you get will at the arguments.

    A job execution is isolated, you can run a job concurrently multiple times, outside of spoon using kitchen.

    Regards,
    Sven

  7. #7
    Join Date
    Sep 2007
    Posts
    13

    Default

    OK I tried this for a test run, but am getting an error in which it cannot connect to the database. Do I need to use a get variables step to actually access this?

    The error message is ambiguous so I have no idea why it is failing to connect. It does connect just fine when I hard code the host and db name in though.

    The error message:

    "A database error occurred, stopping everything:
    Error occured while trying to connect to the database

    Error connecting to database: (using class org.postgresql.Driver)
    The connection attempt failed."

  8. #8
    Join Date
    Sep 2007
    Posts
    13

    Default

    Nevermind, got it running. No idea, might've been that I didn't properly overwrite one of the ktr files when I thought I did.

    Thank you so much for your help.

    Kyle Brown

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.