Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Execute SQL script with parameter in a transformation

  1. #1

    Default Execute SQL script with parameter in a transformation

    Hi !

    I try to execute a SQL script where I insert rows in a table. Before executing this script, I extract from a table the max value of the key of the table to use it to insert my rows.

    So in the step "Execute SQL Script", I add my paramater at the bottom of the window, and in my script I write "insert into table values (?,1,99,'XXXX','YYYYY');".
    When I try to execute this line, i have a syntax error near ",".

    I know that I have to increment my key, but it's just to see if the syntax works fine, but it doesn't.

    I don't have to mark any checkbox like "Replace variables in SQL script" ?

    Do you have any idea about my problem ?

    I use Kettle 4.1.2 and Postgres Databases.

    Thanks for your reply.

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    What in particular is wrong with using a "Table Output" step?

  3. #3

    Default

    I do this because I have to add in a dimension table in my datawarehouse a row which is not in my databases of production. It is a row like "Unknown".

  4. #4
    Join Date
    Nov 2008
    Posts
    143

    Default

    did you fill in the Parameters grid to state which field populates which parameter?

  5. #5

    Default

    Yes, I add my field in the Parameters grid.

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Well, in any case, "Table Output" takes care of the correct mapping of fields automatically.
    If you script the SQL the parameters are positional (sequential) and as such a bit harder to figure out.

    PostgreSQL does not (to my knowledge) have auto-increment fields, only sequences, right?

  7. #7

    Default

    Yes (to my knowledge too) PostgreSQL have only sequences. But I think i'm gonna use a sequence, it will be easier. Cause I really don't know where my syntax error comes from...

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.