ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Using RPG to validate stored procedure results set

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using RPG to validate stored procedure results set

    I wrote an RPG program that will call my stored procedure, which has both input and output fields as well as a results set, to test the stored procedure. I used the following code to call the SP and it works terrific. I load the results set data into a subfile and display it. The problem is that I cannot call the stored procedure a second time. When I do I get this error "Cursor C1 already assigned to result set." I did a little research and have tried and tried to use DEALLOCATE DESCRIPTOR, but clearly I am not doing it correctly, if indeed that is what I should be doing.

    And I know I should be using iSeries Navigator to test this (ACS), and I do.
    I am writing this program so that my QA folks, who do not have access to ACS (for whatever reason) can run the stored procedure tests as they require.

    Any help is greatly appreciated!


    c/Exec SQL
    c+ Call MY_STOREDPROC (arm1, arm2, arm3)
    c+End_Exec

    c/Exec SQL
    c+ Associate Result Set Locators (:Results_Set)
    c+ With Procedure MY_STOREDPROC
    c/End-Exec

    c/Exec SQL
    c+ Allocate C1 Cursor for Result Set :Results_Set
    c/End-Exec

    c/Exec SQL
    c+ Fetch Next from C1 into :Results_Data
    c/End-Exec



  • #2
    I have should have mentioned that if I exit the program and call it again it works OK. What I meant by calling a second time is within the program itself. In other words, I am prompting the user to enter the input parameter values, they press F10 to continue, and then the program displays the results. If they enter different data, without exiting the program, and press F10 a second time, that is when the cursor error happens.

    Comment


    • #3
      Do you close the cursor before calling it the second time?

      Comment


      • #4
        Yes. I have the following code before the CALL. Which is wrong. Your question made me realize what I am doing wrong. I changed the "Close fcursor" to "Close C1" and now it works. Wow! Thanks for your help!

        c/Exec SQL
        c+ Close fcursor
        c/End-Exec

        Comment


        • #5
          The cursor is opened within the stored procedure and you have to close (your) cursor at the end of your work.
          Normally the stored procedure itself should handle the already opened cursor, i.e. close the cursor before reopening it
          ... but most (stored procdedure) programmers don't care about that especially if you try to close an already closed cursor an error is returned which has to be handled.

          Comment

          Working...
          X