Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Please help-- Need to pass database connection details as parameter/variable

  1. #1
    Join Date
    Apr 2014
    Posts
    9

    Default Please help-- Need to pass database connection details as parameter/variable

    Hi All,

    I need to pass database connection details (such as host name, user id , pwd etc ) as parameter or variable so that I can change the database for each execution.

    I have created a transformation where I am passing these details to set variable step and using these variable in the database connection.
    Now this database connection is being used in next transformation. Name:  2.jpg
Views: 1632
Size:  7.8 KBName:  1.jpg
Views: 1838
Size:  24.3 KB

    I am getting below error:
    Variable_connection - New database connection defined
    2014/05/27 21:54:54 - Spoon - Spoon
    2014/05/27 21:55:36 - Table input.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : An error occurred, processing will be stopped:
    2014/05/27 21:55:36 - Table input.0 - Error occured while trying to connect to the database
    2014/05/27 21:55:36 - Table input.0 -
    2014/05/27 21:55:36 - Table input.0 - Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
    2014/05/27 21:55:36 - Table input.0 - Io exception: The Network Adapter could not establish the connection
    2014/05/27 21:55:36 - Variable_connection - Statement canceled!
    2014/05/27 21:55:36 - Variable_connection - Statement canceled!
    2014/05/27 21:55:36 - Table input.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Error initializing step [Table input]
    2014/05/27 21:55:36 - temp - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Step [Table input.0] failed to initialize!
    2014/05/27 21:55:36 - temp - Step [Text file output.0] initialized flawlessly.
    2014/05/27 21:55:36 - Table input.0 - Finished reading query, closing connection.
    2014/05/27 21:55:36 - Text file output.0 - Closing normal file ...
    2014/05/27 21:55:36 - Transformation 2 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Unable to prepare for execution of the transformation
    2014/05/27 21:55:36 - Transformation 2 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleException:
    2014/05/27 21:55:36 - Transformation 2 - We failed to initialize at least one step. Execution can not begin!
    2014/05/27 21:55:36 - Transformation 2 -
    2014/05/27 21:55:36 - Transformation 2 -
    2014/05/27 21:55:36 - Transformation 2 - at org.pentaho.di.trans.Trans.prepareExecution(Trans.java:1068)
    2014/05/27 21:55:36 - Transformation 2 - at org.pentaho.di.trans.Trans.execute(Trans.java:578)
    2014/05/27 21:55:36 - Transformation 2 - at org.pentaho.di.job.entries.trans.JobEntryTrans.execute(JobEntryTrans.java:1037)
    2014/05/27 21:55:36 - Transformation 2 - at org.pentaho.di.job.Job.execute(Job.java:678)
    2014/05/27 21:55:36 - Transformation 2 - at org.pentaho.di.job.Job.execute(Job.java:815)
    2014/05/27 21:55:36 - Transformation 2 - at org.pentaho.di.job.Job.execute(Job.java:815)
    2014/05/27 21:55:36 - Transformation 2 - at org.pentaho.di.job.Job.execute(Job.java:500)
    2014/05/27 21:55:36 - Transformation 2 - at org.pentaho.di.job.Job.run(Job.java:407)
    2014/05/27 21:55:36 - temp - Finished job entry [Transformation 2] (result=[false])
    2014/05/27 21:55:36 - temp - Finished job entry [Transformation] (result=[false])
    2014/05/27 21:55:36 - temp - Job execution finished

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

    Default

    how are you setting the variables in the "Set Variables" Transformation?
    Last edited by gutlez; 05-27-2014 at 01:12 PM.

  3. #3
    Join Date
    Apr 2014
    Posts
    9

    Default

    Hi,

    below is the screenshot the way I am setting variable.I am reading the values from a file and then setting them as variable using set variable step.
    Name:  3.jpg
Views: 1661
Size:  24.8 KB

  4. #4
    Join Date
    Apr 2008
    Posts
    4,687

    Default

    I'm not seeing anything wrong (then again, screenshots are really hard to work from)
    Perhaps post the transforms & the job and we can take a deeper dive.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  5. #5
    Join Date
    Nov 2009
    Posts
    2

    Default

    Quote Originally Posted by yashsri View Post
    Hi,

    below is the screenshot the way I am setting variable.I am reading the values from a file and then setting them as variable using set variable step.
    Name:  3.jpg
Views: 1661
Size:  24.8 KB
    I do exactly what you're describing in PDI 4.2 and 4.4. (Haven't tried in later versions.) I set the variable scope to "Valid in the parent job", rather than the root job. Your screenshot is very fuzzy, but it also looks like you set the variable "database_name" and then used ${Database_name} in the connection description. Like the other poster, I don't see any obvious errors with your job/transformation from the screenshots.

    Name:  PDI-setconnections.jpg
Views: 1628
Size:  26.8 KBName:  PDI-dbconnection.jpg
Views: 1657
Size:  23.9 KB
    Last edited by dannward; 05-30-2014 at 08:18 PM.

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

    Default

    Quote Originally Posted by yashsri View Post
    I need to pass database connection details as parameter or variable
    You made a decision when using Set Variables in your first transformation, so stick to it and don't make your perfectly usable variables invisible by declaring parameters of the same names in your second transformation.
    So long, and thanks for all the fish.

  7. #7
    Join Date
    Apr 2014
    Posts
    9

    Default

    Hi gutlez,

    PFA the transformations.
    temp.kjb is the main job.
    Attached Files Attached Files

  8. #8
    Join Date
    Mar 2014
    Posts
    29

    Default

    Hi,

    Instead of reading variables from file you can declare the variables in kettle.properties file
    and use the same set of variables in creating the db connection.
    You can also change the settings(values in kettle.properties file) as per the requirement.

    Thanks and Regards,
    Bhanu.

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.