Hitachi Vantara Pentaho Community Forums
Results 1 to 12 of 12

Thread: Passing multiple parameters to an 'Execute SQL Statements' stage from input stream

  1. #1
    Join Date
    Mar 2014
    Posts
    7

    Default Passing multiple parameters to an 'Execute SQL Statements' stage from input stream

    Hi All,

    I am in need of executing a couple of SQL statements for every input row coming from a file. For this I need to pass 3 input columns from the file as parameters to the SQL stage. Here is an example:

    Columns in the Input file:
    Key-val1
    Key-val2
    Key-val3

    And here are the sql stmts that I need to execute inside the 'Execute SQL Statements' stage:

    Update table1 set target_column1 = 'abc' where Key_column = Key-val1;

    Update table2 set target_column2 = '123' where Key_column = Key-val2;

    Delete from table3 where Key_column = Key-val3;

    How can I pass these columns as parameters inside the 'Execute SQL Statements' stage? I know we can pass one parameter using '?' but how can we do it for multiple parameters?

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

    Default

    Quote Originally Posted by harsha1238 View Post
    I know we can pass one parameter using '?' but how can we do it for multiple parameters?
    Where in the documentation did you read, that only a single parameter is allowed?

    Quote Originally Posted by Pentaho
    Parameters The list of used parameters that will replace the question marks in the query in the given order. So the first question mark will be replaced by the first parameter, the second question mark by the second parameter etc.
    Note: If you need the same parameter multiple times in your query, you need to use multiple question marks. Using a "Select Values" step you can duplicate field values within the "Select & Alter" tab by selecting the value once and renaming it a second or third time.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Mar 2014
    Posts
    7

    Default

    Yup - I very conveniently didn not read that part when I posted the question. Now I have done so. Thank you and sorry for the time wasted. The issue is resolved.

  4. #4
    Join Date
    Feb 2015
    Posts
    26

    Default

    Name:  1. Transformation.jpg
Views: 1267
Size:  18.3 KBName:  2. Table Input.jpg
Views: 1283
Size:  13.9 KBName:  3. Execute SQL Script.jpg
Views: 1293
Size:  23.2 KB

    Hi, I am running into a similar problem but not sure how to solve this:-

    1. I have a "Table Input" which is Generating 8 columns(and 10 rows)->values flowing to "Execute Sql Script" which is Inserting data into a table.

    now my problem is,
    1. I want to insert data only if it does not already exists so as to avoid duplicate data getting inserted.
    2. How to get only a specific parameter from Input Table and not all the fields

    I am not sure how to do this. I am pasting attachments. Please let me know the exact way of doing this

    Please answer both questions

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by ravinder1483 View Post
    values flowing to "Execute Sql Script" which is Inserting data into a table.
    According to your screenshot, it's doing a select, not an Insert.

    Quote Originally Posted by ravinder1483 View Post
    now my problem is,
    1. I want to insert data only if it does not already exists so as to avoid duplicate data getting inserted.
    2. How to get only a specific parameter from Input Table and not all the fields
    Use the correct steps, and life will be a lot easier. The step you are looking for is Table Output, but you will need to define error handling and make sure that there is a primary key on your table. If the key is already in use, then the Table Output will refuse to load the row, and the error handling will allow Table Output to move to the next row.

    SQL Script step is for exactly what it describes: Running a SQL Script for each row of data that comes in, not for Selects or Inserts.
    Last edited by gutlez; 02-20-2015 at 12:43 PM.

  6. #6
    Join Date
    Feb 2015
    Posts
    26

    Default

    1. Reason i used "Execute SQL Script" is because i am Inserting Identity value of Source Table in the target Table(in both its Primary Key). For this, i found :Execute SQL Script" will do the needful(See i have specified Identity Insert ON in that script).
    2. I was experimenting something, that is why i removed Insert Statement and just kept Select Statement there.I apologize for my mistake but please consider that i have Insert Statement there.
    3. I am able to populate Target Table but want to make sure that next time data is reloaded, duplicate rows are eliminated. Lets Say i use Primary Key(Identity Column) to verify this.
    4. I want to know how to write a query in "Execute SQL Script" which uses same parameter multiple times. From the definition, its not clear to me.

    Now please let me know how to achieve steps 3 and 4 using "Execute SQL Script"

    I am in learning phase and want to be clear on these things.

    Please please please reply
    Last edited by ravinder1483; 02-20-2015 at 03:34 PM.

  7. #7
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    If you are turning off Identity / PK checking, then you can't be sure that you're not going to insert an additional row within one step.
    You will need to build in checks for yourself that the row doesn't already exist.

    So you'll need to do a lookup of the table that you're going to insert into to check to see if the PK already exists. If it does, send it to an update step, rather than an insert step.

    If you need to use the same column twice, you need two columns with the same data. That's left as an exercise for the reader.

  8. #8
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by ravinder1483 View Post

    I am in learning phase and want to be clear on these things.
    This is part of why you were advised to use Table Output -- that's what it's designed to do: INSERT INTO dbo.product_type_temp_ravi (columns) VALUES (?, ?, ?, ?)

  9. #9
    Join Date
    Feb 2015
    Posts
    26

    Default

    Insert Into TableA(Value,Id)
    Select Value,Id From TableInput I Where Not Exists(Select 1 From TableA A WHERE A.Id = I.Id)


    Considering that Value,Id are parameters from Table Input, please suggest how to write the above statement in Execute Sql Script
    Last edited by ravinder1483; 02-23-2015 at 06:22 AM.

  10. #10
    Join Date
    Feb 2015
    Posts
    26

    Default

    Quote Originally Posted by ravinder1483 View Post
    Insert Into TableA(Value,Id)
    Select Value,Id From TableInput I Where Not Exists(Select 1 From TableA A WHERE A.Id = I.Id)


    Considering that Value,Id are parameters from Table Input, please suggest how to write the above statement in Execute Sql Script
    Leave it. I figured it out how to do this.

    For all those who are also struggling with this and are still unclear of how to do this, find below the answer:-
    1. Connect Table Input with Execute Sql Script Via HOP
    2. Double click "Execute Sql Script" and click on Get Fields. It will add fields of "Table Input" in the parameters section(bottom left).
    3. Again click on Get Fields. A message box will appear and select "Add All". This will basically duplicate the parameters in the Parameters Section. Keep the Required ones and delete the rest. Here Sequence matters a lot. So the final Parameter list will be as follows:-
    (i) Value
    (ii) Id
    (iii) Value --- Same as first parameter but requires to be added again in the parameters section.

    4. Write Query As:-
    Insert Into TableA(Value,Id)
    Select '?','?' Where Not Exists(Select 1 From TableA A WHERE A.Id = '?')


    So, the first '?' maps to Value, the second '?' maps to Id, & the third '?' maps to Value

    Note:- Sequence matters as all '?' are replaced in sequence by the parameters specified in Parameters Section of "Execute Sql Script". From the definition, I was not clear about the sequence thing and this caused a great confusion to me.

    Hope it helps..
    Last edited by ravinder1483; 02-23-2015 at 06:22 AM.

  11. #11

    Default

    Quote Originally Posted by harsha1238 View Post
    Hi All,

    I am in need of executing a couple of SQL statements for every input row coming from a file. For this I need to pass 3 input columns from the file as parameters to the SQL stage. Here is an example:

    Columns in the Input file:
    Key-val1
    Key-val2
    Key-val3

    And here are the sql stmts that I need to execute inside the 'Execute SQL Statements' stage:

    Update table1 set target_column1 = 'abc' where Key_column = Key-val1;

    Update table2 set target_column2 = '123' where Key_column = Key-val2;

    Delete from table3 where Key_column = Key-val3;

    How can I pass these columns as parameters inside the 'Execute SQL Statements' stage? I know we can pass one parameter using '?' but how can we do it for multiple parameters?

    Hi,

    Could please tell me how to pass the multiple parameters..We are strucking now where you where.

    Regards,
    Midhun R

  12. #12
    Join Date
    Aug 2011
    Posts
    360

    Default

    Quote Originally Posted by Varadharajan View Post
    Hi,

    Could please tell me how to pass the multiple parameters..We are strucking now where you where.

    Regards,
    Midhun R
    Hi,

    To make it clear, working with SQL parameters is the same on every steps that use them (execute script, database join, even table input with inout from stream rows):
    1. Each ? in your query is replaced by a parameter value
    2. So you need a parameter for each ? you wrote in the query
    3. Order matters: first ? is replaced by the first parameter field, second ? is replaced by the second parameter field etc....
    4. If you need the samed field in multiple places, you have to specify the same field multiple times in the parameter list.


    Hope it's clear

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.