I want to get all the table names from the database and then get all the rows from the tables. So I created a transformation like this:
- Get Table Names: Added the database connection and stored the table name in a output field called "tablename".
- Table Input: Marked "Replace variables in script" and "Execute for each row". Added the first step in "Insert data from step". SQL is "SELECT * from ?".
I have read up a lot of tutorials online, including the documentation.
My problem is that everywhere it says that I my "?" should be replaced with the parameter. But this does not happen. Here are the logs:2013/06/22 03:33:25 - Get table names.0 - Starting to run...
2013/06/22 03:33:25 - Postgres 9.1.9 RO - read :9 table names from db meta-data.
2013/06/22 03:33:25 - Table input.0 - Query parameters found = [stackexchange2]
2013/06/22 03:33:25 - Table input.0 - SQL query : SELECT * from ?
2013/06/22 03:33:25 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unexpected error
2013/06/22 03:33:25 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
2013/06/22 03:33:25 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : An error occurred executing SQL:
2013/06/22 03:33:25 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : SELECT * from ?
2013/06/22 03:33:25 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ERROR: syntax error at or near "$1"
Position: 16
2013/06/22 03:33:25 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
UPDATE
I just want to make this work. I am learning the tool right now, and it would be good to know how '?' works.
Actually you can do this and you absolutely would do this. Take a look at "Metadata Injection".
Also you can't parameterise a table name - jdbc doesnt allow this. However you can get away with using the SQL step rather than table input and generate the whole SQL string in a field if you really want to. Wouldnt really recommend that though.
As said above, please describe further exactly what you're trying to do. If you're just migrating a database without doing any transformation of the data, i.e. from one db to another, then dont bother with Kettle as thats not what it's for.
To solve your situation, i prefer to work with jobs, please find {kettle_intalation_folder_path}/examples/jobs/process all tables/Process all tables.kjb, because your case is a simplification of that example.