Same Cursor name used in Program and Subprogram



IBM's flagship relational database management system

Same Cursor name used in Program and Subprogram

Postby gouravar » Fri Oct 31, 2014 4:13 pm

Hi All,

I have two Programs - Program A and Program B . In Program A, I have 1 cursor with name Cursor1.
I declared the cursor and then opened it, after opening the cursor I called Program B[my cursor1 of Program A is still open]. In Program B also 1 declared and open another cursor named Cursor1.{Program A and Program B cursors are on different tables but unknowingly they have same names}.

As I have seen in same program we can't open two cursors with same name, will the above scenario give Sqlcode -502 or it will execute without any issue or we will get error during precompile time ?

Please share your thoughts.
Note: I don't have terminal now to test this scenario that's I want to discuss with you guys.
gouravar
 
Posts: 7
Joined: Tue Jun 12, 2012 10:36 pm
Has thanked: 3 times
Been thanked: 0 time

Re: Same Cursor name used in Program and Subprogram

Postby gouravar » Sat Nov 01, 2014 6:38 pm

Can anyone please respond on this ?
gouravar
 
Posts: 7
Joined: Tue Jun 12, 2012 10:36 pm
Has thanked: 3 times
Been thanked: 0 time

Re: Same Cursor name used in Program and Subprogram

Postby NicC » Sat Nov 01, 2014 6:45 pm

Obviously not. Especially as for most helpers this is the weekend and they are probably carryng in with their lives.

What have you studied so far to try and reach your own conclusion?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Same Cursor name used in Program and Subprogram

Postby alexm » Wed Nov 26, 2014 11:17 pm

Hi gouravar,
after a long weekend, I found some time to answer your question ;)

When program 'A' gets compiled, a DBRM 'A' is being generated. It includes all the SQL - and additional information like host variables etc - exctracted from your program 'A' source code. At this time, the DB2 precompiler has no knowledge of the existence of a program 'B'. When program 'B' gets compiled, a DBRM 'B' is being generated - here also, DB2 has no knowledge of a program 'A', it only sees program 'B'.

If you look at the DB2 precompiler output listing, you may have a look at those places where the precompiler replaced your EXEC SQL ... END-EXEC statements. You'll find new structures (SQL-PLISTn) and calls to routines (PERFORM SQL-INITIAL, ...) but none of those places have a reference to your cursor name. So if the cursor name is not known to DB2 either at precompile nor at runtime, DB2 does not care about cursor names...

I've created A and B samples. Both use cursor name 'C1' and access different tables:
IDENTIFICATION DIVISION.                           |  IDENTIFICATION DIVISION.
PROGRAM-ID.    A.                                 <-> PROGRAM-ID.    B.
ENVIRONMENT DIVISION.                              |  ENVIRONMENT DIVISION.
CONFIGURATION SECTION.                             |  CONFIGURATION SECTION.
DATA DIVISION.                                     |  DATA DIVISION.
WORKING-STORAGE SECTION.                           |  WORKING-STORAGE SECTION.
01  TEMP           PIC  X(1).                      |  01  TEMP           PIC  X(1).
01  DISCOMP        PIC +9(9).                      |  01  DISCOMP        PIC +9(9).
    EXEC SQL INCLUDE SQLCA END-EXEC.               |      EXEC SQL INCLUDE SQLCA END-EXEC.
    EXEC SQL DECLARE C1 CURSOR FOR SELECT         <->     EXEC SQL DECLARE C1 CURSOR FOR SELECT
         IBMREQD FROM SYSIBM.SYSDUMMY1 END-EXEC.  <->              'X' FROM SYSIBM.SYSDUMMYE END-EXEC.
PROCEDURE DIVISION.                                |  PROCEDURE DIVISION.
MAIN SECTION.                                      |  MAIN SECTION.
    EXEC SQL OPEN C1 END-EXEC.                     |      EXEC SQL OPEN C1 END-EXEC.
    MOVE SQLCODE TO DISCOMP                        |      MOVE SQLCODE TO DISCOMP
    DISPLAY 'A OPEN  <' DISCOMP '/' SQLERRMC '>'   |      DISPLAY 'B OPEN  <' DISCOMP '/' SQLERRMC '>'
    CALL 'B'                                      <| 
    EXEC SQL FETCH C1 INTO :TEMP END-EXEC.         |      EXEC SQL FETCH C1 INTO :TEMP END-EXEC.
    MOVE SQLCODE TO DISCOMP                        |      MOVE SQLCODE TO DISCOMP
    DISPLAY 'A FETCH <' DISCOMP '/' SQLERRMC       |      DISPLAY 'B FETCH <' DISCOMP '/' SQLERRMC
                                '/' TEMP '>'       |                                  '/' TEMP '>'
    EXEC SQL CLOSE C1 END-EXEC.                    |      EXEC SQL CLOSE C1 END-EXEC.
    MOVE SQLCODE TO DISCOMP                        |      MOVE SQLCODE TO DISCOMP
    DISPLAY 'A CLOSE <' DISCOMP '/' SQLERRMC '>'   |      DISPLAY 'B CLOSE <' DISCOMP '/' SQLERRMC '>'
    GOBACK                                         |      GOBACK
    .                                              |      .
MAIN-EXIT. EXIT.                                   |  MAIN-EXIT. EXIT.

Running programs results in:
A OPEN  <+000000000/        > 
B OPEN  <+000000000/        > 
B FETCH <+000000000/        /X>
B CLOSE <+000000000/        > 
A FETCH <+000000000/        /Y>
A CLOSE <+000000000/        > 

I'm now back enjoying the next weekend :D
User avatar
alexm
 
Posts: 35
Joined: Wed Oct 13, 2010 6:40 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post