Net Express Oracle testing - Duplicate Cursor - COBOL

[jessicatx]

Hope that this is the correct place to post this question. I posted it in the COBOL forum and someone objected. Gee, my feelings are SO-O-O-O hurt... Really, I just could use some help!

This concerns Lawson tables in the Lawson financial suite.

I've written a tiny program (COBOL) that accesses three tables and I have tested it using Net Express's Animator. I am getting an error: Duplicate cursor name. Cannot see what I am doing wrong. My "driver" table is being read in a certain sequence. Then I open Cursor 1 and do a fetch. Cursor 1 will read an Open Item using the Invoice Nbr + Customer Nbr that was retrieved by the driver table. Next I open Cursor 2 and do fetches until no more rows satisfy the criteria (SQLCODE = 100). Cursor 2 looks for any applied payments against this same Invoice Nbr + Customer Nbr. I then close both Cursor 1 and Cursor 2 and continue with the next fetch from the "driver" cursor.

It is very tedious to step thru every piece of code (until I get this failure), so I am debugging by writing to a text file, before and after every Exec SQL (and also after I have checked the SQLCODE!). I generally place the paragraph name + an identifying number in each output record, so I can "walk" through the code after the failure occurs. I cannot see where I am bypassing any necessary error handling (where SQLCODE checking occurs). The main cursor is only opened at the start of the program, then the rest of the application is a series of Performs.

The Duplicate Cursor Name gives an SQLCODE of -00000001 (give or take a few zeroes) and SQLWARN1 thru SQLWARN4 are all blanks.

I have tried this process with two tables and with three tables. When retrieving from 3 tables, I err out after the 7th driver record, and with only 2 tables, I err out after the 14th driver record.

Also: do I need to have the driver table set up as a cursor? (I am currently using it that way.) It is not subject to update (only reading this table), but I want to read it in a given sequence. Should I substitute an ordinary Select, with an Order By option?

The Oracle data base is on an NT server. I am working under WIN2K. I believe we are on Oracle 8.I.

Thanks in advance for any suggestions.

Jessica

 

[lewisp]

I dont know COBOL at all and I've never come across the Lawson suite. However, in an attempt to give some advice, you seem to have a number of cursors open at the same time. Can you be sure your code is not attempting to re-open a cursor that is already open, ie. in a loop or because you forgot to close it?

 

[abhijit74]

Jessica,
I was wondering if you could post part of your code.

Cheers!
Abhijit

 

[jessicatx]

Thanks, Abhijit. Here is some of the relevant code, which seems pretty basic. And yet...

Hope the following sheds some light on my problem. Thanks for any ideas you can toss me.

           EXEC SQL
               OPEN AROIHDR-CURSOR
           END-EXEC.

           IF SQLCODE NOT = 000
               DISPLAY '*** AROIHDR CURSOR NOT OPENED ***'
               GO TO 9999-CLOSE-OUTPUT-FILE.

           PERFORM 9000-READ-AROIHDR THRU 9000-RA-EXIT.

           IF END-OF-AROIHDR
               DISPLAY '*** NO AROIHDR ROWS WERE READ ***'
               GO TO 9999-CLOSE-OUTPUT-FILE.

           PERFORM 2000-PROCESS-HDRS THRU 2000-PH-EXIT
               UNTIL END-OF-AROIHDR.

           WRITE INT-RPT-REC FROM RESET-LINE AFTER  ADVANCING 1 LINE.

           PERFORM 8000-PRINT-HEADERS THRU 8000-PH-EXIT.

           GO TO 9999-CLOSE-OUTPUT-FILE.

       2000-PROCESS-HDRS.

           MOVE AROH-CUSTOMER  TO CUST-NBR-PRT
                                  PREV-CUST-NBR.
           MOVE AROH-TRANS-NBR TO IRD-TRANS-NBR
                                  PREV-TRANS-NBR.
           MOVE AROH-ORIG-AMT  TO WS-ORIG-AMT
                                  WS-INV-BAL.

           IF AROH-R-DESC = 'INVOICE CURRENT FUND'
                         OR 'CREDIT - CURRENT FUND'
               MOVE AROH-TRANS-USER2 TO IRD-DESCRIPTION
           ELSE
               MOVE AROH-R-DESC      TO IRD-DESCRIPTION.

           IF AROH-TRANS-TYPE  =  'C'
               COMPUTE WS-ORIG-AMT = WS-ORIG-AMT * -1
               COMPUTE WS-INV-BAL  = WS-ORIG-AMT * -1
           ELSE
               COMPUTE WS-ORIG-AMT = WS-ORIG-AMT * +1
               COMPUTE WS-INV-BAL  = WS-ORIG-AMT * +1.

           EXEC SQL
               OPEN AROITEMS-CURSOR
           END-EXEC.

           IF SQLCODE NOT = 000
               DISPLAY '*** AROITEMS CURSOR NOT OPENED ***'
               DISPLAY '*** STATUS IS ' DISPLAY-SQL-CODE
               GO TO 9999-CLOSE-OUTPUT-FILE.

           PERFORM 9200-READ-AROITEMS THRU 9200-RA-EXIT.

      *>> IF THERE ARE NO AROITEMS FOR THIS CUST+TRANS, THEN
      *>> CLOSE THE CURSOR AND EXIT THIS PARAGRAPH.

           IF END-OF-AROITEMS
               ADD 1                TO WS-HDRS-WITHOUT-ITEMS
               PERFORM 9250-CLOSE-AROI-CURSOR THRU 9250-CAC-EXIT
               GO TO 2000-CONTINUE-PROCESS.

           EXEC SQL
               OPEN ARAPPLIED-CURSOR
           END-EXEC.

           IF SQLCODE NOT = 000
               DISPLAY '*** ARAPPLIED CURSOR NOT OPENED ***'
               DISPLAY '*** STATUS IS ' DISPLAY-SQL-CODE
               GO TO 9999-CLOSE-OUTPUT-FILE.

           MOVE ZEROES TO ARAPPLIED-STATUS
                          ARDISPUTE-STATUS
                          IRD-TOTAL-APPLD.

           PERFORM 9100-READ-ARAPPLIED THRU 9100-RA-EXIT
               UNTIL END-OF-ARAPPLIED.

           PERFORM 9250-CLOSE-AROI-CURSOR THRU 9250-CAC-EXIT.
           PERFORM 9150-CLOSE-ARAP-CURSOR THRU 9150-CAC-EXIT.

       2000-CONTINUE-PROCESS.

           PERFORM 9500-WRITE-RPT THRU 9500-WR-EXIT.

           PERFORM 9000-READ-AROIHDR THRU 9000-RA-EXIT.

       2000-PH-EXIT.
            EXIT.

       9000-READ-AROIHDR.

           EXEC SQL FETCH AROIHDR-CURSOR
                    INTO  :AROH-CUSTOMER,
                          :AROH-TRANS-TYPE,
                          :AROH-TRANS-NBR,
                          :AROH-TRANS-DATE,
                          :AROH-ORIG-AMT,
                          :AROH-TRAN-AMT,
                          :AROH-R-DESC,
                          :AROH-TRANS-USER2
           END-EXEC.

           IF SQLCODE = 100
               MOVE 10 TO AROIHDR-STATUS
               GO TO 9000-RA-EXIT.

           IF (SQLCODE = +1) AND
              (SQLWARN1 = 'W')
               NEXT SENTENCE

           ELSE

           IF SQLCODE NOT = 000
               DISPLAY '*** GOING TO PROGRAM EXIT ***'
               GO TO 9999-CLOSE-OUTPUT-FILE.

           MOVE AROH-TRANS-NBR        TO IRD-TRANS-NBR.
           MOVE AROH-TRANS-DATE       TO WS-BREAK-DATE.
           MOVE WSBD-YEAR             TO IRD-TRDT-YEAR.
           MOVE WSBD-MO               TO IRD-TRDT-MO.
           MOVE WSBD-DAY              TO IRD-TRDT-DAY.
           MOVE AROH-ORIG-AMT         TO IRD-ORIG-AMT.

           ADD 1 TO WS-AROIHDRS-READ.

       9000-RA-EXIT.
           EXIT.

       9100-READ-ARAPPLIED.

           EXEC SQL FETCH ARAPPLIED-CURSOR
                    INTO  :ARAP-CR-CUSTOMER,
                          :ARAP-TRANS-TYPE,
                          :ARAP-TRANS-NBR,
                          :ARAP-APPLD-AMT,
                          :ARAP-ADJ-AMT,
                          :ARAP-DEPOSIT-DATE
           END-EXEC.

           IF SQLCODE = 100
               MOVE 10 TO ARAPPLIED-STATUS
               GO TO 9000-RA-EXIT.

           IF SQLCODE NOT = 000
               DISPLAY '*** GOING TO PROGRAM EXIT ***'
               GO TO 9999-CLOSE-OUTPUT-FILE.

           IF ARAP-APPLD-AMT = ZERO
               NEXT SENTENCE
           ELSE
               IF AROI-TRANS-TYPE = 'C'
                    COMPUTE WS-APPLD-AMT = ARAP-APPLD-AMT * -1
               ELSE
                   COMPUTE WS-APPLD-AMT = ARAP-APPLD-AMT * +1.

           COMPUTE WS-INV-BAL = WS-INV-BAL
                              - WS-APPLD-AMT.

           IF ARAP-ADJ-AMT = ZERO
               NEXT SENTENCE
           ELSE
               IF AROI-TRANS-TYPE = 'C'
                    COMPUTE WS-ADJ-AMT = ARAP-ADJ-AMT * +1
               ELSE
                   COMPUTE WS-ADJ-AMT = ARAP-ADJ-AMT * -1.

           COMPUTE WS-INV-BAL = WS-INV-BAL
                              - WS-ADJ-AMT.

           ADD 1 TO WS-ARAPPLIEDS-READ.

       9100-RA-EXIT.
           EXIT.

       9150-CLOSE-ARAP-CURSOR.

           EXEC SQL
               CLOSE ARAPPLIED-CURSOR
           END-EXEC.

           IF SQLCODE NOT = 000
               DISPLAY '*** GOING TO PROGRAM EXIT ***'
               GO TO 9999-CLOSE-OUTPUT-FILE.

       9150-CAC-EXIT.
           EXIT.

       9200-READ-AROITEMS.

           EXEC SQL FETCH AROITEMS-CURSOR
                    INTO  :AROI-CUSTOMER,
                          :AROI-TRANS-TYPE,
                          :AROI-TRANS-NBR,
                          :AROI-TRAN-AMT,
                          :AROI-APPLD-AMT,
                          :AROI-ADJ-AMT
           END-EXEC.

           IF SQLCODE = 100
               MOVE 10 TO AROITEMS-STATUS
               GO TO 9200-RA-EXIT.

           IF SQLCODE NOT = 000
               DISPLAY '*** GOING TO PROGRAM EXIT ***'
               GO TO 9999-CLOSE-OUTPUT-FILE.
       9200-RA-EXIT.
           EXIT.

       9250-CLOSE-AROI-CURSOR.

           EXEC SQL
               CLOSE AROITEMS-CURSOR
           END-EXEC.

           IF SQLCODE NOT = 000
               DISPLAY '*** GOING TO PROGRAM EXIT ***'
               GO TO 9999-CLOSE-OUTPUT-FILE.

       9250-CAC-EXIT.

 

[karluk]

In the "if" statement where you check for headers without items, you perform 9250 to close one cursor, but not 9150 to close the other. That means you still have one open cursor when you go through the loop the next time.

IF END-OF-AROITEMS
ADD 1 TO WS-HDRS-WITHOUT-ITEMS
PERFORM 9250-CLOSE-AROI-CURSOR THRU 9250-CAC-EXIT
GO TO 2000-CONTINUE-PROCESS.

 

[karluk]

Nope, I guess I don't see the problem. The other cursor isn't opened until after the "if" statement.

 

[Ygor]

Make sure that MODE=ANSI otherwise END_OF_FETCH=1403 not 100

 

[jessicatx]

Ygor -

Where would I put this parameter - compiler directives? In Net Express's Compiler Directives, when I go into Advanced Directives, I see these settings -

ESQL Preprocessor: OpenESQL
Directive: DBMAN
Current Settings: SQL(DBMAN=ODBC)
Remarks say "Enables ANSI SQL92 Entry level support"

In the Build Project Settings, under the Compile tab, it reads -

Directives: OBJ(%TARGETDIR\%BASENAME.obj) SQL(DBMAN=ODBC);

Thanks for any help.

Jessica

 

[Ygor]

urm. I've just noticed that you have GO TO 9000-RA-EXIT instead of GO TO 9100-RA-EXIT.

 

[jessicatx]

You caught me. I was cutting and pasting the code (because it was requested that I post it), and in getting rid of a bunch of my **comments**, I accidentally deleted the paragraph that you just mentioned. So I re-keyed it, and I made a goof (obviously). The actual code DOES read "go to 9100-ra-exit". Sharp eyes! I was only trying to present the code that was being executed, not all my comments in this code.

Jessica