Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Unanswered: Do a COMMIT in FOREACH

    Hi,

    The problem i will present is a lock problem.
    I have Store procedures that make a Foreach Select to get information an for each row return will performe several update, inserts, and more select depending on the information that was return by the First FOREACH.
    The problem is that when you have multiThread these foreach will coul lock many rows during may time.

    To resolve the problem i tried to:
    1- put a commit inside FOREACH (Didn't work because the cursor will ends)
    2- I try to insert the FOREACH SELECT information into a collection array an then iterate tha collection. But to do that i need a FOREACH iterate the collection and then the COMMIT problem will exists.

    Anyone have any ideia?
    FOREACH
    SELECT col1, col2,col,3 FROM Tab1, Tab2
    INTO var1,var2,var3....
    WHERE ......









    END FOREACh

  2. #2
    Join Date
    May 2004
    Location
    Barcelona, Spain
    Posts
    54
    Quote Originally Posted by antpedro
    Hi,

    To resolve the problem i tried to:
    1- put a commit inside FOREACH (Didn't work because the cursor will ends)
    2- I try to insert the FOREACH SELECT information into a collection array an then iterate tha collection. But to do that i need a FOREACH iterate the collection and then the COMMIT problem will exists.

    Anyone have any ideia?
    FOREACH
    SELECT col1, col2,col,3 FROM Tab1, Tab2
    INTO var1,var2,var3....
    WHERE ......

    END FOREACh
    Try declaring your cursor 'with hold' as in

    foreach with hold select ... into ... from ...
    end foreach;

    These cursors will not be closed whenever you make a commit.

    Regards,

  3. #3
    Join Date
    Nov 2003
    Posts
    6

    Foreach With Hold Execute Procedure

    Hi everyone specially iaguigon,

    Thanks for the advice, did work but only for the Select Statements.

    My problem is that inside the Store procedures I invoke another’s store procedures that return several rows.

    I do:

    FOREACH EXECUTE PROCEDURE SP1(Args....)
    INTO.....
    END FOREACH;

    But I change as you told me I change SP1 to make commit inside and because of that I was force to change the call for:
    FOREACH WITH HOLD EXECUTE PROCEDURE SP1(Args....)
    INTO.....
    END FOREACH;

    But a syntax error occurred: SQL Error (-201) : A syntax error has occurred.

    Can somebody help me?

    Thanks again and have a nice weekend

  4. #4
    Join Date
    May 2004
    Location
    CHINA Taiwan
    Posts
    3
    If you want to check detail information of the error codes. such as SQL,4GL RUNTIME.....MORE...
    I found a site offers these error code details, you can study on
    http://www.oninit.com/errorcode/index.html
    it's do a little help for me at work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •