Hitachi Vantara Pentaho Community Forums
Results 1 to 12 of 12

Thread: Execute SQL script bug?

  1. #1

    Default Execute SQL script?

    Hi,

    Can someone confirm what the behavior for unchecking "Execute for each row" in Execute SQL script is to execute the SQL just once for the entire transformation?

    Also, is there a way to execute SQL for each step - basically every time its called instead of per row or per transformation?

    Thanks in advance.
    Last edited by ritesht; 11-07-2008 at 11:19 PM.

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

    Default

    Quote Originally Posted by ritesht View Post
    Can someone confirm what the behavior for unchecking "Execute for each row" in Execute SQL script is to execute the SQL just once for the entire transformation?
    Confirmed... the step is an anomaly

    Quote Originally Posted by ritesht View Post
    Also, is there a way to execute SQL for each step - basically every time its called instead of per row or per transformation?
    Doesn't make sense... it's what it does now... once per transformation, or once per input row "when it's called": http://wiki.pentaho.com/display/EAI/Execute+SQL+script

    Regards,
    Sven
    Last edited by sboden; 11-08-2008 at 04:49 AM.

  3. #3

    Default Execute SQL script?

    What I mean by that is, if I had execute SQL script step in a sub transformation and if I called that sub transformation multiple times from the calling transformation, it doesnt seem like its getting executed EVERYTIME its called - only once for that row, which is not what I want.

    Also the reason why I have to use execute SQL script is because I have to increment a field value by 1, is there any other built in step that can do this in single step? I am forced to do this is in single step because of the non serial execution of rows and race condition, causing duplicate primary key exception. Update step or insert/update etc need field to be passed, which means I have to do a look up first and then update, which is not what I want.

    Also the original subject of the post, is not what I intended it to be - is there a way to change the subject? I tried edit, but it only edits the response subject not the post itself.

    Quote Originally Posted by sboden View Post
    Doesn't make sense... it's what it does now... once per transformation, or once per input row "when it's called": http://wiki.pentaho.com/display/EAI/Execute+SQL+script

    Regards,
    Sven
    Last edited by ritesht; 11-08-2008 at 03:30 PM. Reason: incorrect subject line

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

    Default

    Quote Originally Posted by ritesht View Post
    What I mean by that is, if I had execute SQL script step in a sub transformation and if I called that sub transformation multiple times from the calling transformation, it doesnt seem like its getting executed EVERYTIME its called - only once for that row, which is not what I want.
    There's a difference between what you think it should be doing, and what it does... a subtransformation is initialized once, so without the "execute for 1 row" the sql is only executed 1 time.

    Quote Originally Posted by ritesht View Post
    Also the reason why I have to use execute SQL script is because I have to increment a field value by 1, is there any other built in step that can do this in single step? I am forced to do this is in single step because of the non serial execution of rows and race condition, causing duplicate primary key exception. Update step or insert/update etc need field to be passed, which means I have to do a look up first and then update, which is not what I want.
    none built-in... usually the work around is to split processing in several phases. I had a look at your transformations from your previous mails and I don't think you're going to be able to make yours work. I think you're trying to be too complex.

    Quote Originally Posted by ritesht View Post
    Also the original subject of the post, is not what I intended it to be - is there a way to change the subject? I tried edit, but it only edits the response subject not the post itself.
    You can't change the subject.

    Regards,
    Sven
    Last edited by sboden; 11-08-2008 at 05:44 PM.

  5. #5

    Default

    Thanks Sven for the response.

    I obviously am not trying to make the transformation complex on purpose - that was the way I could think of initially. I will appreciate if you can suggest a simpler way for what I am trying to do.

    Quote Originally Posted by sboden View Post
    There's a difference between what you think it should be doing, and what it does... a subtransformation is initialized once, so without the "execute for 1 row" the sql is only executed 1 time.


    none built-in... usually the work around is to split processing in several phases. I had a look at your transformations from your previous mails and I don't think you're going to be able to make yours work. I think you're trying to be too complex.


    You can't change the subject.

    Regards,
    Sven

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

    Default

    In your specific case, don't use subtransformations.

    For the rest it's hard to tell... it depends. Think of yourself as maintenance developer 3 months down the road trying to understand what you wrote.

    In general, splitting up transformations to run in multiple sequential transformations usually does a lot for me. E.g. doing dimensions and facts in 1 transformation will cause you some key problems without additional tricks... split the dimensions and facts updating in 2 transformations and all those are solved.

    Regards,
    Sven

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

    Default

    Wat baten kaars en bril, als den uil niet zienen wil.

  8. #8

    Default

    This is yet another proof amongst several others that I have seen here of how impossible it is to deal with you - even when the question is not asked to you or answered by you.

    And don't talk about "community" when you don't understand the meaning of it.

    Quote Originally Posted by MattCasters View Post
    Wat baten kaars en bril, als den uil niet zienen wil.

  9. #9

    Default

    Thanks for the response again.

    I am new to PDI and ETL in general (few days) - design good practices and which steps are good to combine only comes with experience or good documentation. I dont have either. So forum is the place to learn things using suggestions from others.

    Will think about multiple sequential transformations.

    Quote Originally Posted by sboden View Post
    In your specific case, don't use subtransformations.

    For the rest it's hard to tell... it depends. Think of yourself as maintenance developer 3 months down the road trying to understand what you wrote.

    In general, splitting up transformations to run in multiple sequential transformations usually does a lot for me. E.g. doing dimensions and facts in 1 transformation will cause you some key problems without additional tricks... split the dimensions and facts updating in 2 transformations and all those are solved.

    Regards,
    Sven

  10. #10

    Default

    Is it really wise to piss off the guy who writes the software?

    You do strike me as an angry person rit

    Quote Originally Posted by ritesht View Post
    This is yet another proof amongst several others that I have seen here of how impossible it is to deal with you - even when the question is not asked to you or answered by you.

    And don't talk about "community" when you don't understand the meaning of it.
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

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

    Default

    ritesht, it's true that Sven's patience is legendary, borderline saint. I'm not even going to pretend I can compete with that. Unlike Sven, I usually resort to sarcasm when I can't take it anymore.

    At the same time, it was not Sven nor me, nor our community that put you in the mess that you seem to be in, on the contrary.
    Dealing with de-normalized data and string primary keys are a lot to deal with in any data integration tool and we have done our best to help you here and on the IRC channel.
    Sven and I tried to talk you out of using mappings for your specific complex situation on several occasions, but only you can decide what to do.

    On top of that, shooting in all directions, changing subjects and blaming the rest of the world for your problems seems to be easier for you than giving a simple description of your situation.

    Matt

  12. #12

    Default

    Forum is for ppl to ask legit and relevant questions - is it not? It seems that If one chooses to write transformation a certain way (for whatever reasons) - and if they run into issues, they cant even post to the forum because its not Matt's way!! That certainly isn't community driven approach. One of the reasons why ppl choose to make their code open source is exactly for this reason - that community does think of different ways of solving the same problem and in the process discover things they would not have thought of otherwise.

    Quote Originally Posted by MattCasters View Post
    ritesht, it's true that Sven's patience is legendary, borderline saint. I'm not even going to pretend I can compete with that. Unlike Sven, I usually resort to sarcasm when I can't take it anymore.
    and I appreciated and thanked each time for the inputs and suggestions.
    Quote Originally Posted by MattCasters View Post
    At the same time, it was not Sven nor me, nor our community that put you in the mess that you seem to be in, on the contrary.
    Dealing with de-normalized data and string primary keys are a lot to deal with in any data integration tool and we have done our best to help you here and on the IRC channel.
    Sven and I tried to talk you out of using mappings for your specific complex situation on several occasions, but only you can decide what to do.
    Again, I am only posting questions as I encounter them. its not shooting in all directions, I am trying to solve the problem I have at hand. Asking questions is not blamming the world!! I believe I have given description of my situation in each question I have asked, whether it was simple or not, is relative and noone has asked me to explain it in simpler terms than what it was.

    I hope we can focus on answering questions if and when we can, rather then arguing.

    Quote Originally Posted by MattCasters View Post
    On top of that, shooting in all directions, changing subjects and blaming the rest of the world for your problems seems to be easier for you than giving a simple description of your situation.

    Matt

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.