Topic
5 replies Latest Post - ‏2013-01-10T09:47:34Z by SystemAdmin
SystemAdmin
SystemAdmin
3129 Posts
ACCEPTED ANSWER

Pinned topic Cobol retrieves result set of stored procedure?

‏2013-01-08T15:32:12Z |
db2 for i 7.1 says that rpg and cobol can use result set of stored procedure.

but i can not find cobol solution even i follow the red book.

is there any one succeed?
Updated on 2013-01-10T09:47:34Z at 2013-01-10T09:47:34Z by SystemAdmin
  • kmilligan
    kmilligan
    14 Posts
    ACCEPTED ANSWER

    Re: Cobol retrieves result set of stored procedure?

    ‏2013-01-08T16:51:39Z  in response to SystemAdmin
    RPG and COBOL can do that with embedded SQL. This article shows the new ASSOCIATE LOCATOR and ALLOCATE CURSOR SQL statements that need to be used:
    http://www.mcpressonline.com/database/db2/db2-for-i-71-more-than-meets-the-eye.html
  • B.Hauser
    B.Hauser
    250 Posts
    ACCEPTED ANSWER

    Re: Cobol retrieves result set of stored procedure?

    ‏2013-01-08T16:51:50Z  in response to SystemAdmin
    Hi Jan,

    Consuming Result Sets is not very difficult:
    1. You need to define a Result Set Locator for each result set returned to be consumed:
    01 RSLCsrC01 SQL TYPE IS RESULT_SET_LOCATOR.
    2. Call your stored procedure
    3. Associate each Result Set Locator with a result set, by using the new SQL Command ASSOCIATE RESULT SET LOCATORS:
    EXEC SQL ASSOCIATE RESULT SET LOCATORS (:MYRSLOC1, :MYRSLOC2, ... :MYRSLOCN)
    WITH SPECIFIC PROCEDURE YourStrProc.
    4. Declare a Cursor for each Result Set by using the new SQL Command ALLOCATE CURSOR. The ALLOCATE CURSOR statement declares the cursor and positions at the beginning of the result set.
    Since the OPEN statement was already performed in the stored procedure, executing an OPEN statement is not neccessary/allowed.
    EXEC SQL ALLOCATE MyCursor CURSOR FOR RESULT SET :MYRSLOC.
    5. After having declared the cursor, you can read with the FETCH statement though the result set, as you do it with any other cursor.
    6. After having processed all rows, Execute the CLOSE Statement to CLOSE the cursor.

    Birgitta
    • SystemAdmin
      SystemAdmin
      3129 Posts
      ACCEPTED ANSWER

      Re: Cobol retrieves result set of stored procedure?

      ‏2013-01-09T11:10:10Z  in response to B.Hauser
      my cobol is opm cobol.
      the code looks like ILE COBOL.

      declaring result_set_locator got some error in the working storage section.

      how can i fix this ?
      • B.Hauser
        B.Hauser
        250 Posts
        ACCEPTED ANSWER

        Re: Cobol retrieves result set of stored procedure?

        ‏2013-01-10T08:07:13Z  in response to SystemAdmin
        Just a question: Can you get your program compiled?

        PDM was not enhanced for Release 7.1, so you may get a syntax error in your source code, while your program can be compiled. (Use RDp instead of PDM for coding, because RDp includes all release 7.1 enhancements)

        Birgitta
        • SystemAdmin
          SystemAdmin
          3129 Posts
          ACCEPTED ANSWER

          Re: Cobol retrieves result set of stored procedure?

          ‏2013-01-10T09:47:34Z  in response to B.Hauser
          i compiled the code regardless pdm syntax error warning.

          it's ok now.

          thanks for your assistance.