Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Help with Execute Sql Script step

  1. #1
    Join Date
    Oct 2009
    Posts
    4

    Default Help with Execute Sql Script step - CORRECTED

    Hello guys.

    I have one problem, I need to execute many updates from sql file to mysql database.

    But i don't have success because i don't know how to separate my commands

    All of this examples return the same error.

    "UPDATE MYFACT SET column1='A', column2='2' WHERE SK_MYFACT = 1;
    UPDATE MYFACT SET column1='B', column2='3' WHERE SK_MYFACT = 2;"

    OR

    "UPDATE MYFACT SET column1='A', column2='2' WHERE SK_MYFACT = 1
    GO
    UPDATE MYFACT SET column1='B', column2='3' WHERE SK_MYFACT = 2"

    Thank you
    Last edited by Rodrigo; 08-05-2010 at 02:39 PM.

  2. #2
    Join Date
    Oct 2007
    Posts
    107

    Default

    Hi Rodrigo

    Can you post your transformation as we're missing details here because it's quite easy to execute more than one command in the Execute SQL Script by separating them by a ";"
    Thanks.

  3. #3
    Join Date
    Jun 2006
    Posts
    282

    Default

    You shouldn't need quotes. I haven't used MySql in a while so I dont' remebmer if it is case sensitive. Try dropping the quotes and just seperate each statement with a semi colon. Also, you may need a commit in there too. Sorry, I use SS 2005. But as CHamel stated, scripts definitely support multiple statements.
    "If you want to increase your success rate, double your failure rate."
    Thomas Watson, Sr (former president of IBM)

  4. #4
    Join Date
    Oct 2009
    Posts
    4

    Default

    Sorry but when I try to attach job, transformation and file (.sql) the forum give me an error.

    I'll describe here my steps.

    1- I call the transformation.
    2- In transformation I use Table input to get te values from stage.
    3- I build the updates in the "Modified Java Script" like this:

    var command = 'UPDATE FATO_NORMAL SET '+STO_FLG_ACTIVECAMPO.getString()+'=\''+STO_FLG_ACTIVE.getString()+'\', '+STO_OBSCAMPO.getString()+'=\''+STO_OBS.getString()+'\' WHERE SK_NORMAL = '+SK_NORMAL_INSERT.getInteger()+';';

    4- I build the file .sql, each line is one command.
    The file is equal to that

    UPDATE FATO_NORMAL SET STO_FLG_ACTIVE_1140='S', STO_OBS_1140='FIM DO EXPEDIENTE' WHERE SK_NORMAL = 1;
    UPDATE FATO_NORMAL SET STO_FLG_ACTIVE_1140='S', STO_OBS_1140='01FIM DO EXPEDIENTE (55)' WHERE SK_NORMAL = 2;
    UPDATE FATO_NORMAL SET STO_FLG_ACTIVE_1140='S', STO_OBS_1140='01FIM DO EXPEDIENTE (51)' WHERE SK_NORMAL = 5;

    5- After end my transformation, my job execute the step "Execute sql script"
    But give me this error.

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE FATO_NORMAL SET STO_FLG_ACTIVE_1140='S', STO_OBS_1140='01FIM DO EXPEDIENT' at line 2

    I hope with this information you are able to help me.

    Thank you two.

  5. #5
    Join Date
    Oct 2007
    Posts
    107

    Default

    Damn, upload functionnalities not working anymore with this new forum. Send me your email (in private if you don't want to have it public here), I will send you an example that should represent your configuration and works very well on my side. Is it possible that your data is not exactly the way you think it is and that there's some "quotes" inside of the strings you're reading from your database?

  6. #6
    Join Date
    Oct 2009
    Posts
    4

    Default

    Thanks for all.

    The CHamel could help me.

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.