Hitachi Vantara Pentaho Community Forums
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Use parameters in Kettle

  1. #1
    Join Date
    May 2008
    Posts
    10

    Question Use parameters in Kettle

    Hi,

    First, I would like to thank the pentaho community and developpers for their work

    At this time, I would like to use parameters like it's used here http://wiki.pentaho.com/display/COM/...+Design+Studio
    but I wish catch them in Kettle.

    For example, I have a Text Box on my web page (after configuring correctly my .xaction).

    So I arrived on my web page (e.g. http://localhost:8080/pentaho/ViewAc...tion&path=test) where I have an input box. I put in a value and I would like use it in a Kettle extraction :

    SELECT * FROM MyTable WHERE id LIKE ?myvalue? where ?myvalue? correspond to my inputbox.

    The first link do this job but the query is build in xaction. In my case, it's in Kettle !

    Sorry for this bad english and many thanks !

  2. #2
    Join Date
    May 2008
    Posts
    10

    Default

    No one can answer me

  3. #3
    Join Date
    Dec 2005
    Posts
    531

    Default

    Hi,

    first you need to build a kettle transormation that works with a command line parameter. After you have it running, you can use an xaction to prompt for the parameter, and pass it to the xaction when calling it.

    Regards,
    Ingo

  4. #4
    Join Date
    May 2008
    Posts
    10

    Question

    Well it seems to be what I've done.

    In attachments, my xaction, kettle model, and report. I'll be glad if you can take a look

    Thanks.

    etl_param_ktr.ktr

    etl_param_report.xml

    etl_param_action.xaction

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

    Default

    Simply put... you cannot use variables in the same transformation where you defined them

    Instead of setting a variable you could e.g. just skip the set variable and in table input use "insert data from step" and "execute for each row" + use ? then instead of the current variable (without 's).

    Regards,
    Sven
    Last edited by sboden; 05-29-2008 at 09:37 AM.

  6. #6
    Join Date
    Sep 2007
    Posts
    834

    Default

    Mathieu,
    I browsed your files, and I changed a couple of things in order to get them work:
    1)In the resource transformation-file, the Mime Type has to be text/xml
    2) In the transformation, I droped the set variable step, and modified the table input step. Here I attach the modified transformation (this is exactly what Sven just told you)
    Good luck
    mc
    Attached Files Attached Files
    Last edited by Maria Roldan; 05-29-2008 at 10:08 AM.

  7. #7
    Join Date
    May 2008
    Posts
    10

    Question

    Hi everybody,

    First, thanks to answer quickly

    Maria, I try to do what sboden & you told me. Without success. In fact, in order to make it prewiable with Kettle, I changed "getting argument 1" to "get transformation name".

    I have the same error in both case : (sorry for flooding!)

    Code:
    2008/05/30 10:06:51 - Firebird - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : ERROR executing query: org.pentaho.di.core.exception.KettleDatabaseException: 
    2008/05/30 10:06:51 - Firebird - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : offending row : [param1 String]
    2008/05/30 10:06:51 - Firebird - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : 
    2008/05/30 10:06:51 - Firebird - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Error setting value #1 [param1 String] on prepared statement (String)
    2008/05/30 10:06:51 - Firebird - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : org.firebirdsql.jdbc.FBSQLException: Invalid column index.
    2008/05/30 10:06:51 - Firebird - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Invalid column index.
    2008/05/30 10:06:51 - Firebird - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : ERROR in part: P Set values
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Erreur inattendue : 
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : org.pentaho.di.core.exception.KettleDatabaseException: 
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : An error occurred executing SQL in part [P Set values]:
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : SELECT
        count(*) as nb
    FROM 
        AB
    WHERE PN LIKE '?'
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : 
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : offending row : [param1 String]
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : 
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Error setting value #1 [param1 String] on prepared statement (String)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : org.firebirdsql.jdbc.FBSQLException: Invalid column index.
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Invalid column index.
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : 
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : 
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : 
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.core.database.Database.openQuery(Database.java:1784)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:213)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:126)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2444)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.trans.steps.tableinput.TableInput.run(TableInput.java:335)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : offending row : [param1 String]
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : 
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Error setting value #1 [param1 String] on prepared statement (String)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : org.firebirdsql.jdbc.FBSQLException: Invalid column index.
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Invalid column index.
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : 
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : 
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.core.database.Database.setValues(Database.java:1054)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.core.database.Database.setValues(Database.java:795)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.core.database.Database.openQuery(Database.java:1718)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     ... 4 more
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Error setting value #1 [param1 String] on prepared statement (String)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : org.firebirdsql.jdbc.FBSQLException: Invalid column index.
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Invalid column index.
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : 
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.core.database.Database.setValue(Database.java:1027)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.core.database.Database.setValues(Database.java:1050)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     ... 6 more
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Caused by: org.firebirdsql.jdbc.FBSQLException: Invalid column index.
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.firebirdsql.jdbc.AbstractPreparedStatement.getField(AbstractPreparedStatement.java:470)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.firebirdsql.jdbc.AbstractPreparedStatement.setString(AbstractPreparedStatement.java:384)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.core.database.Database.setValue(Database.java:902)
    2008/05/30 10:06:51 - Extraction Firebird.0 - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     ... 7 more
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Something went wrong while trying to stop the transformation: org.pentaho.di.core.exception.KettleDatabaseException: 
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Error cancelling statement
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Not yet implemented.
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : org.pentaho.di.core.exception.KettleDatabaseException: 
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Error cancelling statement
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Not yet implemented.
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : 
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.core.database.Database.cancelStatement(Database.java:560)
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.core.database.Database.cancelQuery(Database.java:539)
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.trans.steps.tableinput.TableInput.stopRunning(TableInput.java:270)
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.trans.Trans.stopAll(Trans.java:1035)
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.trans.step.BaseStep.stopAll(BaseStep.java:1799)
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2459)
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.trans.steps.tableinput.TableInput.run(TableInput.java:335)
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) : Caused by: org.firebirdsql.jdbc.FBDriverNotCapableException: Not yet implemented.
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.firebirdsql.jdbc.AbstractStatement.cancel(AbstractStatement.java:481)
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     at org.pentaho.di.core.database.Database.cancelStatement(Database.java:554)
    2008/05/30 10:06:51 - Sylvie - ERROR (version 3.1.0-M2, build 709 from 2008/05/07 22:39:14) :     ... 6 more
    I haven't any idea of the meaning of this error, if you can help me again, I'll appreciate your generosity

    Many thanks !
    Last edited by geegee; 05-30-2008 at 04:11 AM.

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

    Default

    Use ? alone not '?' ... remove the quotes around ?

    Regards,
    Sven

  9. #9
    Join Date
    May 2008
    Posts
    10

    Default

    Well done ! There was a second error, based on my report in fact, text field are case sensitive with data set ! take care !

    I have another question ... how build a model with multiple argument ? Like this :

    SELECT count(CPRET) as NB FROM STAT WHERE DPRET < ? AND DPRET > ?
    What elements should I add in order to join "Get Argument Line 1" & "Get Argument Line 2" to my Extraction step ?

    Thanks a lot
    Last edited by geegee; 05-30-2008 at 05:40 AM.

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

    Default

    The fields incoming of rows are bound positionally to the SQL statement.

    select bla ... where a > ? and B < ?

    The first field in the row would be bound to the ? of the a, the second one would be bound to the one of the B... so order is important

    Regards,
    Sven

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.