Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Table Input - Parameters

  1. #1

    Default Table Input - Parameters

    Hello.

    I'm having hard time working with parameters in the Table Input. I've done many searches and everywhere it says that I can use my parameters in a Table Input Step as filter argument.

    So my query is

    Code:
    Select Field1, Field2, Field3, Field4
    From MyTable
    Where MyID BETWEEN ? And ?
    In my Transformation settings, I did create two parameters which I fill when I run my transformation. When I execute the transformation, I get the following error:
    Code:
    2014/03/30 11:28:02 - xxx -     at org.pentaho.di.core.database.Database.openQuery(Database.java:1641)
    2014/03/30 11:28:02 - xxx -     at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:233)
    2014/03/30 11:28:02 - xxx -     at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:143)
    2014/03/30 11:28:02 - xxx -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
    2014/03/30 11:28:02 - xxx -     at java.lang.Thread.run(Unknown Source)
    2014/03/30 11:28:02 - xxx - Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '?'.
    Funny thing is that from what I understand, the question marks are replaced by the orders of the parameters, but when I go into my transformation settings, my parameters are not shown in the order I've entered them but by alphabetical order. So I'm not sure how we can know what is the "real" order... Anyway, I tried changing my query in case the parameters are passed by alphabetical order but still get the error.

    It seems very complicated to accomplish something so simple with parameters :-(

    I'm using PDI v5.0.1 by the way.

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by mblondinde View Post
    In my Transformation settings, I did create two parameters
    Kettle parameters can be used as variables for textual substitution anywhere in your SELECT statement - don't forget to enable option "Replace variables in script":

    Code:
    SELECT Field1, Field2, Field3, Field4
    FROM MyTable
    WHERE MyID BETWEEN ${LO} AND ${HI}
    Question marks are place holders for SQL parameters substituted by fields of an input rowset in the order of their appearance. The corresponding options are "Insert data from step" and "Execute for each row?".
    So long, and thanks for all the fish.

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.