CODEWITHSUNDEEP
pentahopentaho-spoon
Alexander Chandra
Alexander Chandra

Reputation: 659

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"

Upvotes: 1

Views: 10121

Answers (2)

Alexander Chandra
Alexander Chandra

Reputation: 659

Well i found the solution myself The First Step is Modified java script value->set variables and then get variable->table input(check replace variable in script) and you just need to place "?" to your query (this "?" string is your variable, i dont know if there any other way to call variable) enter image description here

and as reminder javascript on pentaho is a bit different with real javascript enter image description here

Upvotes: 1

Cyrus
Cyrus

Reputation: 2195

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.

Upvotes: 4

Related Questions