Table Input Step FAQ

Variables are not getting substituted

Q: I'm feeding parameters via a input hop to a table input, but they're not being replaced properly. My input row contains e.g. the fieldname "input_param". The following is my query in Table input step:

SELECT VID
  FROM CO_VEHICLES;
 WHERE PONO = ${input_param};

the input_param does not get replaced. How to do this?

A:The reason it fails is that there's a difference between variables and field substitution. The above syntax would work if "input_param" would be a variable. However to use the field values of the incoming rows as arguments you need to use ? Parameters. The query would need to become:

SELECT VID
  FROM CO_VEHICLES;
 WHERE PONO = ?;

These parameter markers are filled in positionally.

Parameters are working, but not as part of a tablename

Q: I'm using parameters in a table input step. This works when I use the parameter in a where clause as:

SELECT VID
  FROM CO_VEHICLES;
 WHERE POID = ?;

If 1 argument would be passed to the table input step from the previous step. However when I use the parameter marker as part of a tablename as:

 SELECT VID
  FROM CO_VEHICLES_?;

it fails. The intention of the above query would be e.g. to access multiple tables CO_VEHICLES_1, CO_VEHICLES_2, ... where the number would be passed as a parameter. This always fails, why?

A:The reason it fails is that JDBC does not allow parameter markers in a table. The reason for this is simple: the tablename determines the columns you can get retrieve, the access rights, ... These things are checked once for parametrized SQL statements and afterwards only the parameters are filled in to execute the query.

If parameter markers would be allowed in tablenames the SQL statement would have to be prepared every time, hence parameter markers in tablenames are not supported.

There is a way around it: you can use (kettle) variables as part of a tablename as in:

 SELECT VID
  FROM CO_VEHICLES_${NUMBER};

What PDI does for these queries (if you enable "variable replacement" in the table input step) is:

• Replace the variables in the SQL statements;
• Bind the parameters per execution;

This way JDBC is not aware of the changing tablename (because it will be filled in by PDI before the SQL is sent over to the database), it will however result in different SQL statements being prepared by the database. There's also another section in is document on the setting of variables.