Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Loading Variables from Database

  1. #1

    Default Loading Variables from Database

    Hi all,

    I'm really hoping this is something simple... But here goes:

    We want our variables stored in a database (for ease of transportation, as we're currently doing a lot of hardware migration across the company). They're currently loaded into a PostgreSQL database and I have a Transformation that reads in a parameter (from the parent Job) that determines which sets of variables to use from the database.

    After splitting the string up, the following query is run in a Table Input:
    Code:
    SELECT variable,value,id_type FROM config 
      WHERE id_tool = '?'
        AND id_app = '?';
    This seems to work fine. I then run the following script in a Modified Java Script Value step:
    Code:
    var a = id_type + "_" + variable;
    variable = a;
    setVariable(variable,value,"r");
    The Transformation finishes and as I understand it (maybe this is where I'm wrong), the remaining steps in the parent job and any child Transformations/Jobs should then be able to utilise the variables set in the javascript.

    Is that correct? If not, is there a better way of going about loading in variables from a database?

    Thanks in advance,
    Dwayne

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

    Default

    Quote Originally Posted by dhughes View Post
    Is that correct?
    Yes.
    Quote Originally Posted by dhughes View Post
    If not, is there a better way of going about loading in variables from a database?
    You could eliminate the JavaScript by concatenating strings in your SELECT statement and using a "Set Variables" step.
    So long, and thanks for all the fish.

  3. #3

    Default

    Thanks for the quick response, Marabu.

    Good call concatenating in the SELECT and using Set Variables. I've put that in now and looks much nicer!

    However, when I'm running the Job, things still aren't working correctly and I'm being presented with errors such as:
    Code:
    INFO  07-11 15:49:53,623 - Retrieve latest attachment - We can not find output folder [${V_DWRK}]! We need to create it ...
    ERROR 07-11 15:49:53,631 - Retrieve latest attachment - Unexpected error:
    We can not find Attachment folder [${V_DATT}]!
    
    
    ERROR 07-11 15:49:53,631 - Retrieve latest attachment - org.pentaho.di.core.exception.KettleException:
    We can not find Attachment folder [${V_DATT}]!
    I've attached the Job and Transformation below if you wanted to have a quick look:
    j_hpsmat_sr_start.kjb
    db2var.ktr

    As stated above, the job is stopping at the Get emails from POP ("Retrieve latest attachment") step, after it's run the "Read Configuration" Transformation.

    I'm really thinking that I've just missed a tick box somewhere... Something super simple. This Thursday feels like a Monday I tell you!

    Thanks,
    Dwayne

  4. #4

    Default

    I almost forgot, here is the testing data I'm using in the database:

    id_tool id_app id_type description value id_type_count variable
    DI AT V Working Directory /opt/pentaho/appdata/working/hpsmat
    0
    DWRK
    DI AT V Archive Directory /opt/pentaho/appdata/archive/hpsmat
    0
    DARC
    DI AT V Attachments Working Directory /opt/pentaho/appdata/working/hpsmat/attachments
    0
    DATT
    DI AT V Attachment Archive Directory /opt/pentaho/appdata/archive/hpsmat/attachments
    0
    DAAT

    The parameter that gets passed through is: "DIAT"
    This is split to run a SELECT on "id_tool" and "id_app" and retrieves all of the rows above.

    The variables are then to be created as: "id_type" + "_" + "variable", with a value of "value"
    Therefore, "V_DWRK" would have a value of "/opt/pentaho/appdata/working/hpsmat"

    I hope this helps clarify what I'm trying to accomplish if I didn't make it very clear earlier.

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

    Default

    Set Variables is not able to read the name of a variable from a field.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  6. #6

    Default

    I gave that a whirl, Marabu, but am receiving the following error:
    Code:
    ERROR 08-11 13:49:53,877 - Get codes - Unexpected error
    ERROR 08-11 13:49:53,877 - Get codes - java.lang.ArrayIndexOutOfBoundsException: 2
            at org.pentaho.di.trans.steps.stringcut.StringCut.getOneRow(StringCut.java:95)
            at org.pentaho.di.trans.steps.stringcut.StringCut.processRow(StringCut.java:154)
            at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
            at java.lang.Thread.run(Unknown Source)
    
    
    INFO  08-11 13:49:53,877 - db2var-2 - Transformation has allocated 4 threads and 3 rowsets.
    ERROR 08-11 13:49:53,878 - db2var-2 - Errors detected!
    ERROR 08-11 13:49:53,878 - db2var-2 - Errors detected!
    INFO  08-11 13:49:53,887 - Set vars - Starting to run...
    INFO  08-11 13:49:53,888 - Get codes - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
    INFO  08-11 13:49:53,888 - Select config - Starting to run...
    INFO  08-11 13:49:53,888 - Select config - Reading query parameters from stream [Get codes]
    INFO  08-11 13:49:53,888 - Select config - Reading from step [Get codes]
    INFO  08-11 13:49:53,891 - Set vars - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0)
    INFO  08-11 13:49:53,891 - db2var-2 - db2var-2
    INFO  08-11 13:49:53,891 - db2var-2 - db2var-2
    ERROR 08-11 13:49:53,941 - Select config - Unexpected error
    ERROR 08-11 13:49:53,942 - Select config - org.pentaho.di.core.exception.KettleException:
    Expected to read parameters from step [Get codes] but none were found.
    
    
            at org.pentaho.di.trans.steps.tableinput.TableInput.readStartDate(TableInput.java:75)
            at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:112)
            at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
            at java.lang.Thread.run(Unknown Source)
    I've tried a few things with the String Cut step to see if it was doing something funny, but can't work it out.

    Thoughts?

  7. #7

    Default

    So, within the Table input step, I put a tick in "Replace variables in script" and removed the entry from "Insert data from step" (from the transformation you attached). This gets past this error, but am then presented with the original issue of:
    Code:
    INFO  08-11 15:40:57,543 - Retrieve latest attachment - We can not find output folder [${V_DWRK}]! We need to create it ...
    INFO  08-11 15:40:57,549 - AppDev Logs - Connection to database closed!
    ERROR 08-11 15:40:57,553 - Retrieve latest attachment - Unexpected error:
    We can not find Attachment folder [${V_DATT}]!
    
    
    ERROR 08-11 15:40:57,553 - Retrieve latest attachment - org.pentaho.di.core.exception.KettleException:
    We can not find Attachment folder [${V_DATT}]!
    However, I'm now afraid that without those entries in the Table Input step, that it's just not setting any variables...

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

    Default

    You shouldn't enable and disable options randomly.
    Read the available documentation before switching from SQL parameters to Kettle variables.
    And no single quotes around SQL placeholders (?), please.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  9. #9

    Default

    Marabu, you're an absolute champ!
    I had scoured over the documentation and still managed to get it wrong... I had seen the SQL placeholders without the single quotes a thousand times, but for some reason still thought I needed them.

    Thanks for pointing it out. Changed them and voilà! All working.

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.