Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Execute row SQL script separator

  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Default Execute row SQL script separator

    Dear all,

    I am trying to dynamically execute MULTIPLE SQL statements from a field but am struggling with the SQL statement separator. Namely, my understanding of "SQL script" is MULTIPLE statements, which begs the question about the statement separator. I tried ';' but the step complains upon execution (invalid character). I am using an Oracle database. I have checked the documentation but couldn't find any pertinent info.
    As long as I have a single SQL statement with no terminator (i.e. ';') everything works fine.

    Many thanks,

    Ichi

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

    Default

    What's in a name?

    "Execute row SQL script" actually does execute a "statement".
    If you must have multiple statements in a single field, you can try to let the query processor decompose the string into single statements.
    MySQL, for example, provides a server option MYSQL_OPTION_MULTI_STATEMENTS_ON for this, I recall.
    Oracle might be able to execute a batch of statements, if you wrap them up in BEGIN END - not sure, though.
    When everything else fails, you can use a "Split field to rows" step before the SQL step.

    PS: MYSQL Connector/J does support the option "allowMultiQueries". Check your Oracle driver documentation.
    Last edited by marabu; 09-03-2012 at 01:18 AM.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Feb 2010
    Posts
    3

    Default

    Quote Originally Posted by ichipow View Post
    Dear all,

    I am trying to dynamically execute MULTIPLE SQL statements from a field but am struggling with the SQL statement separator. Namely, my understanding of "SQL script" is MULTIPLE statements, which begs the question about the statement separator. I tried ';' but the step complains upon execution (invalid character). I am using an Oracle database. I have checked the documentation but couldn't find any pertinent info.
    As long as I have a single SQL statement with no terminator (i.e. ';') everything works fine.

    Many thanks,

    Ichi
    Thanks for the prompt reply! Actually, I daisy-chained two Execute row SQL script steps, each step executing a single SQL statement.

Tags for this Thread

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.