Search code examples
pentahopentaho-spoon

Pentaho passing variable from modified java script value to table input

I need to passing variable from modified java script value and pass it to table input query

this is my enter image description here

If i click preview the output

initator_reference | a | ''   |
1                  | a | null |
2                  | a | null |
3                  | a | null |
4                  | a | null |

It shouldnt be null but "testing" string on that third field and dont ask me why i put that variable in select, its just for testing before i put it in "where condition=variable"

Solution

  • Table Input supports two different ways of making the SQL dynamic: Variable Substitution and inserting data from an incoming stream.

    Variable substitution

    This is what you currently have configured in the Table Input: You put ${variable} somewhere and when the step initializes, the value is pasted as text into the SQL.

    Because all the steps initialize at the same time in a transformation, your Javascript step has not had time to set the value. In PDI you cannot set and use a variable within the same transformation.

    Insert data from step

    The second way is used by selecting a source step in the "Insert data from step" option in the Table input. In this mode, the Table Input takes a row from the selected step and inserts fields (in order) into the SQL at question marks (?) you insert. Normally it expects a single row, but you can choose to execute for EACH row.

    This way should work for your scenario:

    1. Put a Generate Rows step at the start and set it to generate 1 row.
    2. Connect it to the Javascript step.
    3. In the Javascript step, specify the return variable as an output field in the bottom grid, it will get added to the stream.
    4. In the Table Input, select the Javascript step at "Insert data from step"
    5. In your SQL, insert ? at the position of the variable. You may need single quotes around it if the value is a string: WHERE column = '?'.
    6. Preview the transformation to see the result, the Table Input's preview is disabled because of the dependency.

    Notes:

    • Step 1 and 2 may not be needed, I'm not sure if the JS step generates a row by itself. I like to be explicit about that, often naming the Generate Rows step "Generate 1 row".
    • If you have an existing stream or multiple fields to insert, you can use a Select Values step to put the fields in the order they need to be inserted into the SQL.