cobol cursor error

nexuz · #1 (**permalink**) 04-11-11, 17:16

hi all!

I'm currently working on a cobol program for school.
The purpose is that it checks absences from students, without a certificate (for example: a doctor's note).
Now I have programmed a cursor that queries all absent records of a particular student, I open my cursor and fetch it, the first fetch does everything right but the second time I fetch, it returns SQLCODE = -000001.

I can't find any explanation about this error code, maybe one of you can help me out...?

Code:

          EXEC SQL DECLARE absent_without_certificate_cursor CURSOR FOR
            SELECT a.idabsent,a.from_date,a.to_date
            FROM absences a
            WHERE idstudent = :idstudent_db AND 
            0 = (SELECT COUNT(*) FROM certificates c
            WHERE c.idabsent = a.idabsent)
          END-EXEC

ps: At school we use Legacy perCobol ide for programming in cobol.

Marcus_A · #2 (**permalink**) 04-11-11, 18:17

It would help if you post (or attach) your entire program.

nexuz · #3 (**permalink**) 04-12-11, 05:56

Yeah, I could do that but its entirly written in dutch ^^
this is the paragraph were it wents wrong:

Code:

 get_absences_without_certificate.
          EXEC SQL 
            OPEN absent_without_certificate_cursor
          END-EXEC
           
          
         EXEC SQL 
            FETCH absent_without_certificate_cursor
            INTO :idabsent_db,:from_date_db,:to_date_db
          END-EXEC

          
          display "first time fetch: " SQLCODE
          accept stoper
         
          IF SQLCODE = 100
            MOVE 0 TO absent_without_certificate_counter          
          END-IF
            
          PERFORM UNTIL SQLCODE = 100
            PERFORM determine_difference_between_absent_date_and_current_date
            IF difference_between_absent_date_and_current_date > CONSTANT_DAYS
               PERFORM mark_absent_as_illigal
             END-IF

 
         EXEC SQL 
            FETCH absent_without_certificate_cursor
            INTO :idabsent_db,:from_date_db,:to_date_db
          END-EXEC


         display "after first time: " SQLCODE
         accept stoper

          END-PERFORM

          EXEC SQL 
            CLOSE absent_without_certificate_cursor
          END-EXEC      
          .

After the green fetch the SQLCODE is 000000, but after the red fetch the SQLCODE is -000001. in my database I have 2 absent records for this student, but even if there were no absent records in the database, why isn't my SQLCODE not 100?

nexuz · #4 (**permalink**) 04-12-11, 06:09

I have also a student_cursor that gets all the students from the database

Code:

          EXEC SQL DECLARE students_cursor CURSOR FOR
            SELECT idperson,firstname,lasname
            FROM person            
            WHERE idtypeperson like "student"
          END-EXEC

when I fetch this cursor, nothing wents wrong, at the end SQLCODE becomes 100.

So, I don't understand why my absent_without_certificate cursor doesn't work, maybe it is the sub-query I used, but that would be strange because I tested the query in mysql.
I really need some help with this, if this problem is solved, than my program is ready

dr_te_z · #5 (**permalink**) 04-12-11, 06:43

Are there still schools where they teach cobol+SQL? GOOD NEWS

You tested your query in mysql and you access db2 from your cobol program? How do you pre-compile/compile and link? a return code of 1 indicates problems in that direction.
I would advice you to limit the length of you cursor names. Maybe it will work on the newer versions of the software, but later in practice you will only find short code-like cursornames. Better get used to it.

nexuz · #6 (**permalink**) 04-12-11, 07:16

The length of my cursor names got a little bit longer by translating them into English

.

I use the PERcobol ide of LegacyJ, it compiles the cobol source-code to java source-code so basically it isn't really cobol anymore.

But I find it kinda strange, all my other cursors don't have this problem, also I have never had such a problem(s) with cursors and finally it is strange that the first fetch of my cursor does do the right thing
*the idabsent_db,
*the from_date_db
*the to_date_db
variables are all containing the correct values as in the database.
Only at the moment the second fetch starts fetching it goes wrong, the fetch returns a SQLCODE -000001 and my program is getting in a timeless loop.

On the internet I can't find anything about this error code -000001

nexuz · #7 (**permalink**) 04-12-11, 16:55

hi, problem solved in further in my program I did an update to the database and imediately I did a commit, now according to cobol you can't do a commit while you are fetching, I placed my my commit statement outside de perform loop and now everything works correctly.

thanks for the replies!

Marcus_A · #8 (**permalink**) 04-13-11, 00:35

You may want to try defining the cursor using the WITH HOLD option. This allows you to do a commit without closing the cursor (doing intermediate commits is usually a good idea to decrease lock contention). But not sure if that is supported in your PERcobol ide of LegacyJ environment.

nexuz · #9 (**permalink**) 04-13-11, 05:02

nope its not supported by PERcobol, thanks anyway for the tip

dr_te_z · #10 (**permalink**) 04-14-11, 06:15

Quote:

Originally Posted by nexuz

I use the PERcobol ide of LegacyJ, it compiles the cobol source-code to java source-code so basically it isn't really cobol anymore.

So you do not use the db2 precompiler (db2prep)? What happens to your EXEC SQL lines? Are they converted to JDBC? Just out of interest: I'd like to see a piece of the generated java code please.

Lenny77 · #11 (**permalink**) 04-14-11, 10:46

Why do not make easy change to the cursor ?

Code:

EXEC SQL DECLARE absent_without_certificate_cursor 
            CURSOR FOR
        SELECT a.idabsent,a.from_date,a.to_date
        FROM absences a  
        Where  WHERE idstudent = :idstudent_db 
                  AND not exists            
                (SELECT  1  FROM certificates c
                   WHERE    c.idabsent = a.idabsent ) 
END-EXEC

Lenny

nexuz · #12 (**permalink**) 04-14-11, 16:12

Quote:

Originally Posted by dr_te_z

So you do not use the db2 precompiler (db2prep)? What happens to your EXEC SQL lines? Are they converted to JDBC? Just out of interest: I'd like to see a piece of the generated java code please.

Yes it generates a preparedStatement in java code en executes that

Internet Services

Web Development

Digital Marketing

Consumer Tech