Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Kettle Table Input with table name in parameter

  1. #1

    Default Kettle Table Input with table name in parameter

    I'm desperately trying to use the Table Input step with 2 parameters a table name and a date.
    The problem is that I can't manage to set table name with parameter.
    I tried a simple query:
    Code:
    SELECT * FROM ?
    The step receive one row with one field [table=myTable].
    The result is:
    Code:
    2007/09/04 16:48:47 - Step.0 - Query parameters found = [table=myTable]
    2007/09/04 16:48:47 - Step.0 - ERROR (version 2.5.1, build 1 from 2007/09/04 14:26:25) : Erreur inattendue 'be.ibridge.kettle.core.exception.KettleDatabaseException: 
    2007/09/04 16:48:47 - Step.0 - ERROR (version 2.5.1, build 1 from 2007/09/04 14:26:25) : An error occurred executing SQL: 
    2007/09/04 16:48:47 - Step.0 - ERROR (version 2.5.1, build 1 from 2007/09/04 14:26:25) : SELECT * FROM ?;
    2007/09/04 16:48:47 - Step.0 - ERROR (version 2.5.1, build 1 from 2007/09/04 14:26:25) : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''myTable'' at line 1
    I think that Kettle automatically quote the Strings, but quoted table name isn't supported by Kettle.

    We need that because there is a huge number of entries and we have a table by month and we don't wan't to manually edit the script each month.
    Is there another solution, or workaround?

  2. #2

    Default

    Hi Axel,

    Try to use variable like
    SELECT * FROM ${my_table_name}

    Rgds

  3. #3
    Join Date
    May 2006
    Posts
    4,882

    Default

    For the ?'s: you can not bind tables, but Samatar's solution should work if you define the variable in another transformation before you run yours (or you define it e.g. in kettle.properties)

    Regards,
    Sven

  4. #4

    Default

    It works very well with an env variable.
    Thanks

    PS: as thread subscription by mail doesn't work, I haven't seen your replies early.

  5. #5

    Default Using Variables for query conditions

    Hi,
    I've got a variant on this problem- I have the following:

    1) Table Input which returns single column called "description"
    2) Select Values which renames "description" to "constValue"
    3) Another Table input with the following select:

    Code:
             SELECT * FROM contactdata WHERE description=${constValue}
    But it fails. Table Input #1 references the same table with: select distinct description form contactdata.

    I've tried using the AS keyword to rename the field and skip the need for a select values.

    Does not work either. I have two questions:

    1) Is there any way to "see" the SQL generated in Table Input #2 or to view variable state "live"?

    2) Am I doing this right? Is using the same table in #1 causing a problem in #3?
    Thanks in advance for your help.

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    You have to use ?'s ... not the ${} notation.

    Regards,
    Sven

  7. #7

    Unhappy

    Hi Sven,
    Thanks for the reply, unfortunately, still not working. :-(

    My system log is below:

    Code:
    2007/09/24 18:09:43 - Kettle - PREVIEW!!!
    2007/09/24 18:09:43 - Migrate Phone - Generic Version - Transformation is in preview mode...
    2007/09/24 18:09:43 - Migrate Phone - Generic Version - nr of steps to preview : 2, nr of hops : 1
    2007/09/24 18:09:43 - Migrate Phone - Generic Version - Dispatching started for transformation [Migrate Phone - Generic Version]
    2007/09/24 18:09:43 - Migrate Phone - Generic Version - Nr of arguments detected:10 
    2007/09/24 18:09:43 - Migrate Phone - Generic Version - This is not a replay transformation
    2007/09/24 18:09:43 - Migrate Phone - Generic Version - I found 2 different steps to launch.
    2007/09/24 18:09:43 - Migrate Phone - Generic Version - Allocating rowsets...
    2007/09/24 18:09:43 - Migrate Phone - Generic Version -  Allocating rowsets for step 0 --> Table input
    2007/09/24 18:09:43 - Migrate Phone - Generic Version -   prevcopies = 1, nextcopies=1
    2007/09/24 18:09:43 - Migrate Phone - Generic Version - Transformation allocated new rowset [Table input.0 - Table input 2.0]
    2007/09/24 18:09:43 - Migrate Phone - Generic Version -  Allocated 1 rowsets for step 0 --> Table input  
    2007/09/24 18:09:43 - Migrate Phone - Generic Version -  Allocating rowsets for step 1 --> Table input 2
    2007/09/24 18:09:43 - Migrate Phone - Generic Version -  Allocated 1 rowsets for step 1 --> Table input 2  
    2007/09/24 18:09:43 - Migrate Phone - Generic Version - Allocating Steps & StepData...
    2007/09/24 18:09:43 - Migrate Phone - Generic Version -  Transformation is about to allocate step [Table input] of type [TableInput]
    2007/09/24 18:09:43 - Migrate Phone - Generic Version -   Step has nrcopies=1
    2007/09/24 18:09:43 - Table input.0 - distribution activated
    2007/09/24 18:09:43 - Table input.0 - Starting allocation of buffers & new threads...
    2007/09/24 18:09:43 - Table input.0 - Step info: nrinput=0 nroutput=1
    2007/09/24 18:09:43 - Table input.0 - output rel. is  1:1
    2007/09/24 18:09:43 - Table input.0 - Found output rowset [Table input.0 - Table input 2.0]
    2007/09/24 18:09:43 - Table input.0 - Finished dispatching
    2007/09/24 18:09:43 - LocalVariables - ---> Create new KettleVariables for thread [Table input.0 (Thread-35)] for parent thread [main], same namespace? [true]
    2007/09/24 18:09:43 - Migrate Phone - Generic Version -  Transformation has allocated a new step: [Table input].0
    2007/09/24 18:09:43 - Migrate Phone - Generic Version -  Transformation is about to allocate step [Table input 2] of type [TableInput]
    2007/09/24 18:09:43 - Migrate Phone - Generic Version -   Step has nrcopies=1
    2007/09/24 18:09:43 - Table input 2.0 - distribution activated
    2007/09/24 18:09:43 - Table input 2.0 - Starting allocation of buffers & new threads...
    2007/09/24 18:09:43 - Table input 2.0 - Step info: nrinput=1 nroutput=0
    2007/09/24 18:09:43 - Table input 2.0 - Got previous step from [Table input 2] #0 --> Table input
    2007/09/24 18:09:43 - Table input 2.0 - input rel is 1:1
    2007/09/24 18:09:43 - Table input 2.0 - Found input rowset [Table input.0 - Table input 2.0]
    2007/09/24 18:09:43 - Table input 2.0 - Finished dispatching
    2007/09/24 18:09:43 - LocalVariables - ---> Create new KettleVariables for thread [Table input 2.0 (Thread-36)] for parent thread [main], same namespace? [true]
    2007/09/24 18:09:43 - Migrate Phone - Generic Version -  Transformation has allocated a new step: [Table input 2].0
    2007/09/24 18:09:43 - Migrate Phone - Generic Version - This transformation can be replayed with replay date: 2007/09/24 18:09:43
    2007/09/24 18:09:43 - Migrate Phone - Generic Version - Initialising 2 steps...
    2007/09/24 18:09:43 - LocalVariables - ---> Create new KettleVariables for thread [init of Table input.0 (Thread-37)] for parent thread [main], same namespace? [true]
    2007/09/24 18:09:43 - iMarket - New database connection defined
    2007/09/24 18:09:43 - LocalVariables - ---> Create new KettleVariables for thread [init of Table input 2.0 (Thread-38)] for parent thread [main], same namespace? [true]
    2007/09/24 18:09:43 - iMarket - New database connection defined
    2007/09/24 18:09:43 - iMarket - Connected to database.
    2007/09/24 18:09:43 - iMarket - Auto commit off
    2007/09/24 18:09:43 - Table input 2.0 - Connected to database...
    2007/09/24 18:09:43 - iMarket - Connected to database.
    2007/09/24 18:09:43 - iMarket - Auto commit off
    2007/09/24 18:09:43 - Table input.0 - Connected to database...
    2007/09/24 18:09:43 - Migrate Phone - Generic Version - Step [Table input.0] initialized flawlessly.
    2007/09/24 18:09:43 - Migrate Phone - Generic Version - Step [Table input 2.0] initialized flawlessly.
    2007/09/24 18:09:43 - Table input.0 - Starting to run...
    2007/09/24 18:09:43 - Table input 2.0 - Starting to run...
    2007/09/24 18:09:43 - Table input 2.0 - Reading query parameters from stream [Table input]
    2007/09/24 18:09:43 - Migrate Phone - Generic Version - Transformation has allocated 2 threads and 1 rowsets.
    2007/09/24 18:09:43 - Table input 2.0 - Reading from step [Table input]
    2007/09/24 18:09:44 - Table input.0 - Signaling output done to 1 output rowsets.
    2007/09/24 18:09:44 - Table input.0 - Finished reading query, closing connection.
    2007/09/24 18:09:44 - Table input 2.0 - Query parameters found = [description=Main 1:, description=Fax 1:, description=e-Mail:, description=Web:, description=Fax 2:, description=Main 2:, description=Direct:, description=Handphone:, description=Extension:, description=Main:, description=Fax:]
    2007/09/24 18:09:44 - iMarket - Connection to database closed!
    2007/09/24 18:09:44 - Table input.0 - Finished processing (I=11, O=0, R=0, W=11, U=0, E=0
    2007/09/24 18:09:44 - iMarket - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : ERROR executing query: be.ibridge.kettle.core.exception.KettleDatabaseException: 
    2007/09/24 18:09:44 - iMarket - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : offending row : [description=Main 1:, description=Fax 1:, description=e-Mail:, description=Web:, description=Fax 2:, description=Main 2:, description=Direct:, description=Handphone:, description=Extension:, description=Main:, description=Fax:]
    2007/09/24 18:09:44 - iMarket - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 
    2007/09/24 18:09:44 - iMarket - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : Error setting value #1 [Main 1:] on prepared statement (String)
    2007/09/24 18:09:44 - iMarket - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : java.sql.SQLException: No parameters defined during prepareCall()
    2007/09/24 18:09:44 - iMarket - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : No parameters defined during prepareCall()
    2007/09/24 18:09:44 - iMarket - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : ERROR in part: P Set values
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : Unexpected error : be.ibridge.kettle.core.exception.KettleDatabaseException: 
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : An error occurred executing SQL in part [P Set values]:
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : SELECT entityID, number FROM contactdata WHERE entityID > 0 AND description='?'
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : offending row : [description=Main 1:, description=Fax 1:, description=e-Mail:, description=Web:, description=Fax 2:, description=Main 2:, description=Direct:, description=Handphone:, description=Extension:, description=Main:, description=Fax:]
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : Error setting value #1 [Main 1:] on prepared statement (String)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : java.sql.SQLException: No parameters defined during prepareCall()
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : No parameters defined during prepareCall()
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : be.ibridge.kettle.core.exception.KettleDatabaseException: 
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : An error occurred executing SQL in part [P Set values]:
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : SELECT entityID, number FROM contactdata WHERE entityID > 0 AND description='?'
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : offending row : [description=Main 1:, description=Fax 1:, description=e-Mail:, description=Web:, description=Fax 2:, description=Main 2:, description=Direct:, description=Handphone:, description=Extension:, description=Main:, description=Fax:]
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : Error setting value #1 [Main 1:] on prepared statement (String)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : java.sql.SQLException: No parameters defined during prepareCall()
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : No parameters defined during prepareCall()
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	at be.ibridge.kettle.core.database.Database.openQuery(Database.java:2166)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	at be.ibridge.kettle.core.database.Database.openQuery(Database.java:2084)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	at be.ibridge.kettle.trans.step.tableinput.TableInput.doQuery(TableInput.java:188)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	at be.ibridge.kettle.trans.step.tableinput.TableInput.processRow(TableInput.java:114)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	at be.ibridge.kettle.trans.step.tableinput.TableInput.run(TableInput.java:290)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : Caused by: be.ibridge.kettle.core.exception.KettleDatabaseException: 
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : offending row : [description=Main 1:, description=Fax 1:, description=e-Mail:, description=Web:, description=Fax 2:, description=Main 2:, description=Direct:, description=Handphone:, description=Extension:, description=Main:, description=Fax:]
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : Error setting value #1 [Main 1:] on prepared statement (String)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : java.sql.SQLException: No parameters defined during prepareCall()
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : No parameters defined during prepareCall()
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	at be.ibridge.kettle.core.database.Database.setValues(Database.java:1011)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	at be.ibridge.kettle.core.database.Database.setValues(Database.java:765)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	at be.ibridge.kettle.core.database.Database.openQuery(Database.java:2100)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	... 4 more
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : Caused by: be.ibridge.kettle.core.exception.KettleDatabaseException: 
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : Error setting value #1 [Main 1:] on prepared statement (String)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : java.sql.SQLException: No parameters defined during prepareCall()
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : No parameters defined during prepareCall()
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	at be.ibridge.kettle.core.database.Database.setValue(Database.java:985)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	at be.ibridge.kettle.core.database.Database.setValues(Database.java:1007)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	... 6 more
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : Caused by: java.sql.SQLException: No parameters defined during prepareCall()
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	at com.mysql.jdbc.ServerPreparedStatement.getBinding(ServerPreparedStatement.java:747)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	at com.mysql.jdbc.ServerPreparedStatement.setString(ServerPreparedStatement.java:1822)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	at be.ibridge.kettle.core.database.Database.setValue(Database.java:862)
    2007/09/24 18:09:44 - Table input 2.0 - ERROR (version 2.4.0, build 160 from 2007/02/25 08:01:09) : 	... 7 more
    2007/09/24 18:09:44 - Table input 2.0 - Finished reading query, closing connection.
    2007/09/24 18:09:44 - iMarket - Connection to database closed!
    2007/09/24 18:09:44 - Table input 2.0 - Finished processing (I=0, O=0, R=11, W=0, U=0, E=1
    2007/09/24 18:09:44 - Kettle - The transformation has finished!!
    2007/09/24 18:09:44 - iMarket - Statement canceled!
    2007/09/24 18:09:44 - iMarket - Statement canceled!
    2007/09/24 18:09:44 - iMarket - Statement canceled!
    2007/09/24 18:09:44 - iMarket - Statement canceled!
    2007/09/24 18:09:44 - Kettle - Step: Table input 2 --> 0 rows
    It clearly finds the rows, and despite the SQL having '' around the ?, I get errors about "no parameter"!

    Confused! Thanks again for your support.
    Peter

  8. #8
    Join Date
    May 2006
    Posts
    4,882

    Default

    Lose the quotes around the ?

    Regards,
    Sven

  9. #9

    Default

    Sven,
    Again, thanks for your sterling support. The removal of the quotes did not quite do it- I also had to select the check box "Execute for each row". I was not sure if that applied to the origin step or the current step. Now I do!
    Any way, thanks again,
    Peter

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.