Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

CURSOR not getting any records.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Nov 17, 2008 3:22 pm    Post subject: CURSOR not getting any records.
Reply with quote

Hi,
Following is a basic cobol-db2 program which would get records based on gender(m or f). When i executed the program, its not getting any records from tables, since the SQLCODE is 100. But, there records in the table.

program
Code:
000100 IDENTIFICATION DIVISION.
000200 PROGRAM-ID.  DB2CURSO.
000800
000900 ENVIRONMENT DIVISION.
001000
001100 DATA DIVISION.
001200
001300****************************************************************
001400 WORKING-STORAGE SECTION.
001410****************************************************************
001411 01 WS-TEMP-VAR.
001412        05 END-OF-LOOP          PIC X(1).
001413        05 RECORD-COUNT         PIC 9(7).
001414        05 WS-SEX               PIC X.
001415
001416 01 WS-TEMP-EMP.
001417         10 WS-EMPNO                PIC X(6).
001418         10 WS-FIRSTNME.
001419            49 WS-FIRSTNME-TEXT     PIC X(12).
001420         10 WS-MIDINIT              PIC X(1).
001421         10 WS-LASTNAME.
001422            49 WS-LASTNAME-TEXT     PIC X(15).
001423         10 WS-HIREDATE             PIC X(10).
001424         10 WS-JOB                  PIC X(8).
001425         10 WS-SALARY               PIC S9(7)V9(2) USAGE COMP-3.
001426         10 WS-BONUS                PIC S9(7)V9(2) USAGE COMP-3.
001427         10 WS-COMM                 PIC S9(7)V9(2) USAGE COMP-3.
001430
001500*    ***********************************************************
001600*    SQL CONTROL BLOCK
001700*    ***********************************************************
001800     EXEC SQL INCLUDE SQLCA    END-EXEC.
001900
002000*    ***********************************************************
002010*    SQL/DEFINITION OF EMPLOYEE
002011*    ***********************************************************
002012     EXEC SQL INCLUDE EMPLOYEE END-EXEC.
002013
002014*   ************************************************************
002015*    CURSOR DECLARATION
002016*   ************************************************************
002017     EXEC SQL
002018          DECLARE ECURSOR CURSOR FOR
002019            SELECT EMPNO,FIRSTNME,MIDINIT,LASTNAME,HIREDATE,JOB,
002020              SALARY,BONUS,COMM
002021            FROM SHANDB.EMPLOYEE
002022            WHERE SEX=:WS-SEX
002023     END-EXEC.
002024*    ***********************************************************
002025
004300****************************************************************
005000 PROCEDURE DIVISION.
005100     INITIALIZE WS-TEMP-VAR
005101
005110     EXEC SQL
005120       OPEN ECURSOR
005140     END-EXEC.
005400
005500 0000-START-PROGRAM.
005600
005700     EXEC SQL
005800       WHENEVER SQLERROR
005900       GOTO 9000-DB-ERROR
006000     END-EXEC.
006100
006200     EXEC SQL
006300       WHENEVER SQLWARNING
006400       CONTINUE
006500     END-EXEC.
006600
006700     EXEC SQL
006800       WHENEVER NOT FOUND
006900       CONTINUE
007000     END-EXEC.
007010
007100     ACCEPT WS-SEX.
007101     DISPLAY 'SEX = 'WS-SEX
007102     IF WS-SEX = 'M'
007103        DISPLAY 'DETAILS OF ALL MALE EMPLOYEE'
007104     ELSE
007105        DISPLAY 'DETAILS OF ALL FEMALE EMPLOYEE'
007106     END-IF.
007107     PERFORM 0100-READ-PARA.
007108     GO TO 1000-END-PROGRAM.
007109
007110 0100-READ-PARA.
009120        PERFORM UNTIL END-OF-LOOP = 'Y'
009130           EXEC SQL
009140           FETCH   ECURSOR
009150           INTO    :EMPNO   ,:FIRSTNME,:MIDINIT,
009160                   :LASTNAME,:HIREDATE,:JOB,
009161                   :SALARY  ,:BONUS, :COMM
009180           END-EXEC
009193              DISPLAY 'FIRST NAME' FIRSTNME-TEXT
009194              DISPLAY 'SQLCODE=' SQLCODE
009200          EVALUATE SQLCODE
009300            WHEN 0
009310              MOVE EMPNO TO WS-EMPNO
009320              MOVE FIRSTNME-TEXT TO WS-FIRSTNME-TEXT
009330              MOVE MIDINIT TO WS-MIDINIT
009340              MOVE LASTNAME TO WS-LASTNAME
009350              MOVE HIREDATE TO WS-HIREDATE
009360              MOVE JOB TO WS-JOB
009370              MOVE SALARY TO WS-SALARY
009380              MOVE BONUS TO WS-BONUS
009390              MOVE COMM TO WS-COMM
009400              DISPLAY  WS-TEMP-EMP
009500              ADD 1 TO RECORD-COUNT
009600
009700            WHEN 100
009800              DISPLAY 'TOTAL NUMBER OF RECORDS = ' RECORD-COUNT
009810              MOVE 'Y' TO END-OF-LOOP
009900
010400            WHEN OTHER
010500              DISPLAY 'ERROR IN RETREVING DATA'
010600              DISPLAY 'SQLCODE = ' SQLCODE
010700
011400          END-EVALUATE
011500        END-PERFORM.
011700
011800 1000-END-PROGRAM.
011900     EXEC SQL
011910       CLOSE ECURSOR
011930     END-EXEC.
012000     GOBACK.
012100
012200 9000-DB-ERROR.
012300      GO TO 1000-END-PROGRAM.
012400



Output
Code:
SEX = F                                 
DETAILS OF ALL FEMALE EMPLOYEE           
FIRST NAME                               
SQLCODE=0000000100                       
TOTAL NUMBER OF RECORDS = 0000000       



Please tell why its not getting any records.

Sushanth
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Mon Nov 17, 2008 3:51 pm    Post subject:
Reply with quote

Sushanth,

you are OPENing the cursor before you are populating the WS-SEX field.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Mon Nov 17, 2008 3:53 pm    Post subject:
Reply with quote

besides, the cursor will not return any records, ever. Only rows.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Nov 17, 2008 4:03 pm    Post subject:
Reply with quote

Thank You Dick

Thank You Cris.

Problem SOLVED. Now cursors are properly returning ROWS.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us