Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Issues with variables

  1. #1
    Join Date
    Apr 2014
    Posts
    13

    Default Issues with variables

    Hello:
    I've having an issue passing along connection variables to a child job. First I'll give you some details on my setup:
    OS = Windows Server 2012
    Kettle build = 5.0.1(stable)
    Repository type = database(MySQL)
    Source/Target database = MSSQL 2012
    Running the current Kettle job from = inside of Spoon

    As I mentioned I'm having problems passing the connection string parts down. I've got a DB connection table with the requisite parts: host, database, port, and credentials. The main job's 1st step is to query that table(Table Input) and then Copy Rows to Result. The next step is the child job which is set to Execute for every input. The 1st step inside of that child job is to Set Variables. It sets the following: HostName, DatabaseName, PortNumber, UserName, and UserPassword. So the transform Gets rows from result and Sets Variables. The next step in the job then gets those variables through a Get Variables step and creates a ${HOSTNAME}, ${DATABASENAME}, and so on. Finally, the job should load the test table. The connection inside of the Table Input and Table Output of the final transformation should use the variables to create their connections.

    Here is a breakdown of the steps:

    Master Job(main job)
    Get DB Connections > Load Dim Test(set to execute for every input)

    Load Dim Test(child job)
    Set Variable > Get Variable > Load Dim Test

    The error dump indicates an error while connecting to the database. It never makes it past the Master Job. Any ideas? It appears as though Kettle is attempting to validate the connections prior to execution starting. Maybe I need to initialize the values of the connection string parts inside of the kettle.properties file. Any help you can provide is much appreciated!

    I’ve included the log text below:
    2014/12/11 09:15:08 - Spoon - Starting job...
    2014/12/11 09:15:09 - Master Load Mather Load All - Start of job execution
    2014/12/11 09:15:09 - Master Load Mather Load All - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : A serious error occurred during job execution:
    2014/12/11 09:15:09 - Master Load Mather Load All - Error occured while trying to connect to the database
    2014/12/11 09:15:09 - Master Load Mather Load All -
    2014/12/11 09:15:09 - Master Load Mather Load All - Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
    2014/12/11 09:15:09 - Master Load Mather Load All - The syntax of the connection URL 'jdbc:jtds:sqlserver:///' is invalid.
    2014/12/11 09:15:09 - Master Load Mather Load All - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
    2014/12/11 09:15:09 - Master Load Mather Load All - Error occured while trying to connect to the database
    2014/12/11 09:15:09 - Master Load Mather Load All -
    2014/12/11 09:15:09 - Master Load Mather Load All - Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
    2014/12/11 09:15:09 - Master Load Mather Load All - The syntax of the connection URL 'jdbc:jtds:sqlserver:///' is invalid.
    2014/12/11 09:15:09 - Master Load Mather Load All -
    2014/12/11 09:15:09 - Master Load Mather Load All -
    2014/12/11 09:15:09 - Master Load Mather Load All - at org.pentaho.di.core.database.Database.normalConnect(Database.java:415)
    2014/12/11 09:15:09 - Master Load Mather Load All - at org.pentaho.di.core.database.Database.connect(Database.java:353)
    2014/12/11 09:15:09 - Master Load Mather Load All - at org.pentaho.di.core.database.Database.connect(Database.java:306)
    2014/12/11 09:15:09 - Master Load Mather Load All - at org.pentaho.di.core.database.Database.connect(Database.java:294)
    2014/12/11 09:15:09 - Master Load Mather Load All - at org.pentaho.di.job.Job.beginProcessing(Job.java:966)
    2014/12/11 09:15:09 - Master Load Mather Load All - at org.pentaho.di.job.Job.execute(Job.java:474)
    2014/12/11 09:15:09 - Master Load Mather Load All - at org.pentaho.di.job.Job.run(Job.java:407)
    2014/12/11 09:15:09 - Master Load Mather Load All - Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    2014/12/11 09:15:09 - Master Load Mather Load All - Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
    2014/12/11 09:15:09 - Master Load Mather Load All - The syntax of the connection URL 'jdbc:jtds:sqlserver:///' is invalid.
    2014/12/11 09:15:09 - Master Load Mather Load All -
    2014/12/11 09:15:09 - Master Load Mather Load All - at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:540)
    2014/12/11 09:15:09 - Master Load Mather Load All - at org.pentaho.di.core.database.Database.normalConnect(Database.java:399)
    2014/12/11 09:15:09 - Master Load Mather Load All - ... 6 more
    2014/12/11 09:15:09 - Master Load Mather Load All - Caused by: java.sql.SQLException: The syntax of the connection URL 'jdbc:jtds:sqlserver:///' is invalid.
    2014/12/11 09:15:09 - Master Load Mather Load All - at net.sourceforge.jtds.jdbc.Driver.setupConnectProperties(Driver.java:242)
    2014/12/11 09:15:09 - Master Load Mather Load All - at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:182)
    2014/12/11 09:15:09 - Master Load Mather Load All - at java.sql.DriverManager.getConnection(DriverManager.java:571)
    2014/12/11 09:15:09 - Master Load Mather Load All - at java.sql.DriverManager.getConnection(DriverManager.java:233)
    2014/12/11 09:15:09 - Master Load Mather Load All - at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:528)
    2014/12/11 09:15:09 - Master Load Mather Load All - ... 7 more
    2014/12/11 09:15:09 - Spoon - Job has ended.
    2014/12/11 09:16:20 - General - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Unable to get rows of data from logging table ListenerReporting-ListenerReporting
    2014/12/11 09:16:20 - General - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Unable to get rows of data from logging table ListenerReporting-log_channel
    2014/12/11 09:16:20 - General - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
    2014/12/11 09:16:20 - General - Error occured while trying to connect to the database
    2014/12/11 09:16:20 - General -
    2014/12/11 09:16:20 - General - Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
    2014/12/11 09:16:20 - General - The syntax of the connection URL 'jdbc:jtds:sqlserver://${HOSTNAME}:${PORTNUMBER}/${DATABASENAME}' is invalid.
    2014/12/11 09:16:20 - General -
    2014/12/11 09:16:20 - General -
    2014/12/11 09:16:20 - General - at org.pentaho.di.core.database.Database.normalConnect(Database.java:415)
    2014/12/11 09:16:20 - General - at org.pentaho.di.core.database.Database.connect(Database.java:353)
    2014/12/11 09:16:20 - General - at org.pentaho.di.core.database.Database.connect(Database.java:306)
    2014/12/11 09:16:20 - General - at org.pentaho.di.core.database.Database.connect(Database.java:294)
    2014/12/11 09:16:20 - General - at org.pentaho.di.ui.spoon.trans.TransHistoryDelegate.getHistoryData(TransHistoryDelegate.java:472)
    2014/12/11 09:16:20 - General - at org.pentaho.di.ui.spoon.trans.TransHistoryDelegate.access$500(TransHistoryDelegate.java:78)
    2014/12/11 09:16:20 - General - at org.pentaho.di.ui.spoon.trans.TransHistoryDelegate$3.run(TransHistoryDelegate.java:426)
    2014/12/11 09:16:20 - General - at java.lang.Thread.run(Thread.java:745)
    2014/12/11 09:16:20 - General - Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    2014/12/11 09:16:20 - General - Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
    2014/12/11 09:16:20 - General - The syntax of the connection URL 'jdbc:jtds:sqlserver://${HOSTNAME}:${PORTNUMBER}/${DATABASENAME}' is invalid.
    2014/12/11 09:16:20 - General -
    2014/12/11 09:16:20 - General - at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:540)
    2014/12/11 09:16:20 - General - at org.pentaho.di.core.database.Database.normalConnect(Database.java:399)
    2014/12/11 09:16:20 - General - ... 7 more
    2014/12/11 09:16:20 - General - Caused by: java.sql.SQLException: The syntax of the connection URL 'jdbc:jtds:sqlserver://${HOSTNAME}:${PORTNUMBER}/${DATABASENAME}' is invalid.
    2014/12/11 09:16:20 - General - at net.sourceforge.jtds.jdbc.Driver.setupConnectProperties(Driver.java:242)
    2014/12/11 09:16:20 - General - at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:182)
    2014/12/11 09:16:20 - General - at java.sql.DriverManager.getConnection(DriverManager.java:571)
    2014/12/11 09:16:20 - General - at java.sql.DriverManager.getConnection(DriverManager.java:215)
    2014/12/11 09:16:20 - General - at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:524)
    2014/12/11 09:16:20 - General - ... 8 more
    2014/12/11 09:16:20 - General - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
    2014/12/11 09:16:20 - General - Error occured while trying to connect to the database
    2014/12/11 09:16:20 - General -
    2014/12/11 09:16:20 - General - Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
    2014/12/11 09:16:20 - General - The syntax of the connection URL 'jdbc:jtds:sqlserver://${HOSTNAME}:${PORTNUMBER}/${DATABASENAME}' is invalid.
    2014/12/11 09:16:20 - General -
    2014/12/11 09:16:20 - General -
    2014/12/11 09:16:20 - General - at org.pentaho.di.core.database.Database.normalConnect(Database.java:415)
    2014/12/11 09:16:20 - General - at org.pentaho.di.core.database.Database.connect(Database.java:353)
    2014/12/11 09:16:20 - General - at org.pentaho.di.core.database.Database.connect(Database.java:306)
    2014/12/11 09:16:20 - General - at org.pentaho.di.core.database.Database.connect(Database.java:294)
    2014/12/11 09:16:20 - General - at org.pentaho.di.ui.spoon.trans.TransHistoryDelegate.getHistoryData(TransHistoryDelegate.java:472)
    2014/12/11 09:16:20 - General - at org.pentaho.di.ui.spoon.trans.TransHistoryDelegate.access$500(TransHistoryDelegate.java:78)
    2014/12/11 09:16:20 - General - at org.pentaho.di.ui.spoon.trans.TransHistoryDelegate$3.run(TransHistoryDelegate.java:426)
    2014/12/11 09:16:20 - General - at java.lang.Thread.run(Thread.java:745)
    2014/12/11 09:16:20 - General - Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    2014/12/11 09:16:20 - General - Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
    2014/12/11 09:16:20 - General - The syntax of the connection URL 'jdbc:jtds:sqlserver://${HOSTNAME}:${PORTNUMBER}/${DATABASENAME}' is invalid.
    2014/12/11 09:16:20 - General -
    2014/12/11 09:16:20 - General - at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:540)
    2014/12/11 09:16:20 - General - at org.pentaho.di.core.database.Database.normalConnect(Database.java:399)
    2014/12/11 09:16:20 - General - ... 7 more
    2014/12/11 09:16:20 - General - Caused by: java.sql.SQLException: The syntax of the connection URL 'jdbc:jtds:sqlserver://${HOSTNAME}:${PORTNUMBER}/${DATABASENAME}' is invalid.
    2014/12/11 09:16:20 - General - at net.sourceforge.jtds.jdbc.Driver.setupConnectProperties(Driver.java:242)
    2014/12/11 09:16:20 - General - at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:182)
    2014/12/11 09:16:20 - General - at java.sql.DriverManager.getConnection(DriverManager.java:571)
    2014/12/11 09:16:20 - General - at java.sql.DriverManager.getConnection(DriverManager.java:215)
    2014/12/11 09:16:20 - General - at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:524)
    2014/12/11 09:16:20 - General - ... 8 more
    2014/12/11 09:19:33 - Spoon - Spoon

  2. #2
    Join Date
    Feb 2011
    Posts
    152

    Default

    I have done this type of setup successfully. On the step to call your sub-job from the master job, you are executing for every row, but did you specify on the parameters tab the parameters and the stream column name that holds the values? It will not automatically fill them in, even if they are named the same.

  3. #3
    Join Date
    Apr 2014
    Posts
    13

    Default

    Yeah that doesn't seem to help either. I'm a bit worried there is something going wrong with the database repository. Based on the setup this should work - there are links in the forum and other places in doing just this.

    http://forums.pentaho.com/showthread...looping-in-PDI

  4. #4
    Join Date
    Feb 2011
    Posts
    152

    Default

    That may be. In my particular situation, I am not using a database repository. Instead, we use a file repository that is managed by a version control system (SVN).

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

    Default

    Quote Originally Posted by kadams21 View Post
    Load Dim Test(child job)
    Set Variable > Get Variable > Load Dim Test
    I'm going to assume you mean Child Transformation, not child job. Note: You CANNOT set and use a Variable in the same transformation, as **ALL** steps initialize at the beginning. Thus, your "General" step cannot use the variables that you will set when you receive them.

  6. #6
    Join Date
    Apr 2014
    Posts
    13

    Default

    It's actually a sub-job I guess you'd say. Load Dim Test is a job with a step setting variables and another step getting said variables. The last step is a transform.

    The issue I was having revolved around the database repository and it's logging. The connections for the log tables could not live in the database was meant to be dynamic. To alleviate the issue I am not using a central set of logging tables inside of a config database. I also saw some stangeness with the database repository with log connections getting written to the database tables(repository) even if I removed them. I relented and went with a single logging connection to be shared across jobs. Hope that makes sense or if you don't agree.

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.