Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Pentaho variable in SQL script?

  1. #1
    Join Date
    Feb 2017
    Posts
    14

    Default Pentaho variable in SQL script?

    I'd like to use the @uid = SCOPE_IDENTITY() result of one SQL script INSERT commands in another one later down the stream.
    How do I accomplish that? Can I set and use Pentaho variables inside my SQL script?
    Last edited by stephanb; 05-03-2018 at 02:01 PM.

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

    Default

    Quote Originally Posted by stephanb View Post
    Can I set and use Pentaho variables inside my SQL script?
    Short answer... No.
    PDI Variables cannot be set and used in the same transform.

    Perhaps if you elaborated on what you're trying to do, rather than the solution you've selected, we might be able to help you.

  3. #3
    Join Date
    Feb 2017
    Posts
    14

    Default

    Fair enough, and thank you for the short answer.
    I have a 4 table INSERT script. I pull the value of the first insert's identity column, and use it in the subsequent inserts to keep my other tables consistent.

    Later in the (same) transformation, after splitting an incoming field into its own rows, I have another SQL script INSERT for those.
    I'd like reuse the value of the first script's @uid so I don't have to do something like this:

    INSERT INTO UserRoles (userid, role)
    VALUES ((SELECT id FROM Users WHERE ApplicantID=?),?);

    That embedded SELECT statement makes this INSERT really slow.

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

    Default

    Don't use SQL script steps where a native step will do.

    Fork your stream data, and send it to a Table Output step. One of the options there is to return the Identity of the inserted row. This can then be used to merge the data back in (stream lookup) to your stream*. In theory, you can specify which fields are sent to the DB in each Table output, allowing you to simply chain the 4 Table Output steps. I haven't tried doing this, so you may have to experiment a little.

    Alternatively, if you must use a single 4 table insert in your SQL script, use SQL variables, not PDI variables. Those should be able to be used withing a single SQL statement. But if you are doing that, you might as well build a stored procedure that takes all your columns as input and does the inserts.

    *WARNING: Splitting a stream and then re-merging it has the potential of a dead-lock. Design plans must be built to ensure you don't create one.
    Last edited by gutlez; 05-03-2018 at 03:28 PM.

  5. #5
    Join Date
    Feb 2017
    Posts
    14

    Default

    Quote Originally Posted by gutlez View Post
    Don't use SQL script steps where a native step will do.

    Fork your stream data, and send it to a Table Output step. One of the options there is to return the Identity of the inserted row. This can then be used to merge the data back in (stream lookup) to your stream*. In theory, you can specify which fields are sent to the DB in each Table output, allowing you to simply chain the 4 Table Output steps. I haven't tried doing this, so you may have to experiment a little.

    Alternatively, if you must use a single 4 table insert in your SQL script, use SQL variables, not PDI variables. Those should be able to be used withing a single SQL statement. But if you are doing that, you might as well build a stored procedure that takes all your columns as input and does the inserts.

    *WARNING: Splitting a stream and then re-merging it has the potential of a dead-lock. Design plans must be built to ensure you don't create one.
    I see what you're describing. Chaining the table outputs should work. I just read about the "Return auto-generated key" option, which I can use in the subsequent Table output steps. I hope that native single-table actions are faster too, because the SQL script steps were the bottlenecks in my transformation.

    If I chain them, there shouldn't be a reason to fork and merge, eliminating the deadlock possibility.
    I'm playing with the setup now. Thank you.

  6. #6
    Join Date
    Feb 2017
    Posts
    14

    Default

    Thank you very much. My transformation looks much better now, and it works almost 20 times faster than using SQL scripts.
    Unreal. Take care.

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.