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

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

    Re: Cobol retrieves result set of stored procedure?

    ‏2013-01-08T16:51:39Z  
    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
    264 Posts

    Re: Cobol retrieves result set of stored procedure?

    ‏2013-01-08T16:51:50Z  
    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

    Re: Cobol retrieves result set of stored procedure?

    ‏2013-01-09T11:10:10Z  
    • B.Hauser
    • ‏2013-01-08T16:51:50Z
    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
    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
    264 Posts

    Re: Cobol retrieves result set of stored procedure?

    ‏2013-01-10T08:07:13Z  
    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 ?
    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

    Re: Cobol retrieves result set of stored procedure?

    ‏2013-01-10T09:47:34Z  
    • B.Hauser
    • ‏2013-01-10T08:07:13Z
    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
    i compiled the code regardless pdm syntax error warning.

    it's ok now.

    thanks for your assistance.