Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2011
    Posts
    7

    Unanswered: cobol cursor error

    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.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It would help if you post (or attach) your entire program.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2011
    Posts
    7
    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?
    Last edited by nexuz; 04-12-11 at 07:00.

  4. #4
    Join Date
    Apr 2011
    Posts
    7
    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

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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.

  6. #6
    Join Date
    Apr 2011
    Posts
    7
    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

  7. #7
    Join Date
    Apr 2011
    Posts
    7
    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!

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Apr 2011
    Posts
    7
    nope its not supported by PERcobol, thanks anyway for the tip

  10. #10
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by nexuz View Post
    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.

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation DB2 made easy !

    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
    Last edited by Lenny77; 04-14-11 at 12:22.

  12. #12
    Join Date
    Apr 2011
    Posts
    7
    Quote Originally Posted by dr_te_z View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •