By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,543 Members | 2,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,543 IT Pros & Developers. It's quick & easy.

Not getting sqlcode 100 at end of fetch

P: n/a
I'm having a problem that I can't seem to find any solution for online.

I'm using a cursor in a cobol program to fetch some data. I know for a
fact that the select should return 2 rows. The fetch works as I would
expect it to on the 2 rows, but then when I do my next fetch, the
program ends abnormally.

My next step is to check for sqlcode 100, as I would expect it to be,
but my program never even gets to this step.

Any ideas? Here is some code, if it helps.

EXEC SQL
DECLARE ACH_SEARCH CURSOR FOR
SELECT
A.INDVD_NAME,
D.FILE_CRT_DATE,
C.EFCTV_ENTRY_DATE,
A.TRNSC_CODE,
A.RCVNG_DFI_ID,
A.DFI_ACNT_ID,
A.AMT,
A.INDVD_ID,
C.CMPNY_DSCRT_TEXT,
A.TRACE_NMBR,
B.PYMNT_RLTD_TEXT,
A.DSCRT_DATA_CODE
FROM (((TPMNFLA B
RIGHT OUTER JOIN TPMNFBD A ON
A.TRACE_NMBR = B.TRACE_NMBR)
INNER JOIN TPMNFLB C ON
A.NACHA_SQN_NMBR = C.NACHA_SQN_NMBR AND
A.BATCH_NMBR = C.BATCH_NMBR)
INNER JOIN TPMNFLH D ON
C.NACHA_SQN_NMBR = D.NACHA_SQN_NMBR)
WHERE A.INDVD_NAME LIKE 'ROSEL C GOMEZ '
END-EXEC

PERFORM A8100-FETCH-SEARCH THRU
A8100-FETCH-SEARCH-EXIT
UNTIL 88-DONE

EXEC SQL
FETCH ACH_SEARCH
INTO
:NFBD-INDVD-NAME,
:NFLH-FILE-CRT-DATE,
:NFLB-EFCTV-ENTRY-DATE,
:NFBD-TRNSC-CODE,
:NFBD-RCVNG-DFI-ID,
:NFBD-DFI-ACNT-ID,
:NFBD-AMT,
:NFBD-INDVD-ID,
:NFLB-CMPNY-DSCRT-TEXT,
:NFBD-TRACE-NMBR,
:WS-ADDENDA INDICATOR :ADIND,
:NFBD-DSCRT-DATA-CODE
END-EXEC.

EVALUATE TRUE
WHEN SQLCODE = 0
do stuff
WHEN SQLCODE = 100
SET 88-DONE TO TRUE

WHEN SQLCODE < -900
SET DB2-SYS-ERROR TO TRUE
PERFORM S9910-PROCESS-SQL-ERROR
THRU S9910-PROCESS-SQL-ERROR-EXIT

Oct 10 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a

scoonie999 wrote:
I'm having a problem that I can't seem to find any solution for online.

I'm using a cursor in a cobol program to fetch some data. I know for a
fact that the select should return 2 rows. The fetch works as I would
expect it to on the 2 rows, but then when I do my next fetch, the
program ends abnormally.

My next step is to check for sqlcode 100, as I would expect it to be,
but my program never even gets to this step.

Any ideas? Here is some code, if it helps.
What happens in your code if you get a SQLCODE of, say, -818?

Pete H

Oct 10 '06 #2

P: n/a

scoonie999 wrote:
I'm having a problem that I can't seem to find any solution for online.

I'm using a cursor in a cobol program to fetch some data. I know for a
fact that the select should return 2 rows. The fetch works as I would
expect it to on the 2 rows, but then when I do my next fetch, the
program ends abnormally.

My next step is to check for sqlcode 100, as I would expect it to be,
but my program never even gets to this step.

Any ideas? Here is some code, if it helps.
Or perhaps a better example than my previous post, a -305? Hint:
----------------------------------------------------------------------------------------------------------
"If the data type can handle NULLs, the application must provide a NULL
indicator. Otherwise, an error may occur. If a NULL indicator is not
used, an SQLCODE -305 (SQLSTATE 22002) is returned.
-----------------------------------------------------------------------------------------------------------

Pete H

Oct 10 '06 #3

P: n/a
scoonie999 wrote:
I'm having a problem that I can't seem to find any solution for online.

I'm using a cursor in a cobol program to fetch some data. I know for a
fact that the select should return 2 rows. The fetch works as I would
expect it to on the 2 rows, but then when I do my next fetch, the
program ends abnormally.

My next step is to check for sqlcode 100, as I would expect it to be,
but my program never even gets to this step.

Any ideas? Here is some code, if it helps.

EXEC SQL
DECLARE ACH_SEARCH CURSOR FOR
SELECT
A.INDVD_NAME,
D.FILE_CRT_DATE,
C.EFCTV_ENTRY_DATE,
A.TRNSC_CODE,
A.RCVNG_DFI_ID,
A.DFI_ACNT_ID,
A.AMT,
A.INDVD_ID,
C.CMPNY_DSCRT_TEXT,
A.TRACE_NMBR,
B.PYMNT_RLTD_TEXT,
A.DSCRT_DATA_CODE
FROM (((TPMNFLA B
RIGHT OUTER JOIN TPMNFBD A ON
A.TRACE_NMBR = B.TRACE_NMBR)
INNER JOIN TPMNFLB C ON
A.NACHA_SQN_NMBR = C.NACHA_SQN_NMBR AND
A.BATCH_NMBR = C.BATCH_NMBR)
INNER JOIN TPMNFLH D ON
C.NACHA_SQN_NMBR = D.NACHA_SQN_NMBR)
WHERE A.INDVD_NAME LIKE 'ROSEL C GOMEZ '
END-EXEC

PERFORM A8100-FETCH-SEARCH THRU
A8100-FETCH-SEARCH-EXIT
UNTIL 88-DONE

EXEC SQL
FETCH ACH_SEARCH
INTO
:NFBD-INDVD-NAME,
:NFLH-FILE-CRT-DATE,
:NFLB-EFCTV-ENTRY-DATE,
:NFBD-TRNSC-CODE,
:NFBD-RCVNG-DFI-ID,
:NFBD-DFI-ACNT-ID,
:NFBD-AMT,
:NFBD-INDVD-ID,
:NFLB-CMPNY-DSCRT-TEXT,
:NFBD-TRACE-NMBR,
:WS-ADDENDA INDICATOR :ADIND,
:NFBD-DSCRT-DATA-CODE
END-EXEC.

EVALUATE TRUE
WHEN SQLCODE = 0
do stuff
WHEN SQLCODE = 100
SET 88-DONE TO TRUE

WHEN SQLCODE < -900
SET DB2-SYS-ERROR TO TRUE
PERFORM S9910-PROCESS-SQL-ERROR
THRU S9910-PROCESS-SQL-ERROR-EXIT
It is hard to tell what the problem is because I can't see all of your
code, inlcuding the paragraph names that are referenced in your PERFORM
UNTIL.

Do you have any exit handlers declared?

Is your declare cursor in your working storage section? (if not, then
move it there).

Oct 10 '06 #4

P: n/a
Mark A<m0****@yahoo.com10/10/06 2:30 PM >>>
>Is your declare cursor in your working storage section? (if not, then
move it there).
Does this actually make a difference? I started doing this when I noticed
that DECLARE CURSOR doesn't generate any COBOL code, but does its placement
in WS actually have a specific benefit? Most examples I see have it in the
procedure division.

Just wondering...
Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
Oct 11 '06 #5

P: n/a
Frank Swarbrick wrote:
Does this actually make a difference? I started doing this when I noticed
that DECLARE CURSOR doesn't generate any COBOL code, but does its placement
in WS actually have a specific benefit? Most examples I see have it in the
procedure division.

Just wondering...
Frank
It is exactly because the Declare Cursor does not generate any code by
the precompiler (the generated code is included with the Open Cursor),
that it should not be in the Procedure Division.

Typically, programmers will check SQL returns codes for every SQL
statement, and if they check the return code for a declare cursor, they
are really checking the return code for the previous SQL statement
executed, which leads to many logic errors (often intermittent).

I have seen this problem a thousand times. It is much safer to put
declare cursor in Working Storage Section.

Oct 11 '06 #6

P: n/a
Mark A<m0****@yahoo.com10/11/06 9:45 AM >>>
>Frank Swarbrick wrote:
>Does this actually make a difference? I started doing this when I
noticed
>that DECLARE CURSOR doesn't generate any COBOL code, but does its
placement
>in WS actually have a specific benefit? Most examples I see have it in
the
>procedure division.

Just wondering...
Frank

It is exactly because the Declare Cursor does not generate any code by
the precompiler (the generated code is included with the Open Cursor),
that it should not be in the Procedure Division.

Typically, programmers will check SQL returns codes for every SQL
statement, and if they check the return code for a declare cursor, they
are really checking the return code for the previous SQL statement
executed, which leads to many logic errors (often intermittent).

I have seen this problem a thousand times. It is much safer to put
declare cursor in Working Storage Section.
Sounds good! Would be nice if the DB2 examples followed this practice,
but...

Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
Oct 11 '06 #7

P: n/a
I was able to get this working. Not sure why this worked, but adding a
check that a field is not null (this particular field never should have
nulls in it) to the where clasue did the trick. I now get the +100
return code when I expect to.

where clause now looks like this

WHERE A.INDVD_NAME = 'ROSEL C GOMEZ '
AND A.INDVD_NAME IS NOT NULL

Now I need to do some real work to this program.

Thanks for the replies.

Oct 11 '06 #8

P: n/a
scoonie999 wrote:
I was able to get this working. Not sure why this worked, but adding a
check that a field is not null (this particular field never should have
nulls in it)
Do you have a check constraint on the table in that case? Because if you
don't, it may just be that you have some invalid data in your table and
executing the query in your application simply fetches those rows.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Oct 12 '06 #9

P: n/a
This seems to match what I was referring to in my 2nd post from
10/10... You have no indicator variables defined and more importantly
unless I'm missing something, you don't seem to be checking for
SQLCODES that can range from -001 to -899... See my post from the 10th

Pete H

scoonie999 wrote:
I was able to get this working. Not sure why this worked, but adding a
check that a field is not null (this particular field never should have
nulls in it) to the where clasue did the trick. I now get the +100
return code when I expect to.

where clause now looks like this

WHERE A.INDVD_NAME = 'ROSEL C GOMEZ '
AND A.INDVD_NAME IS NOT NULL

Now I need to do some real work to this program.

Thanks for the replies.
Oct 12 '06 #10

P: n/a
Your code has a number of problems:
1. You didn't properly code for nullable columns in the database
2. You didn't code to check for ALL non-expected sqlcodes
3. You are using a coding style that forces duplication of logic.

You stated that the program abended and you appeared to be stuck there.
If you were using an interactive debugger or the COBOL batch debugging
aids, you should have been able to obtain a lot more information about
what was happening and probably would have been able to fix it yourself.

Phil Sherman

scoonie999 wrote:
I was able to get this working. Not sure why this worked, but adding a
check that a field is not null (this particular field never should have
nulls in it) to the where clasue did the trick. I now get the +100
return code when I expect to.

where clause now looks like this

WHERE A.INDVD_NAME = 'ROSEL C GOMEZ '
AND A.INDVD_NAME IS NOT NULL

Now I need to do some real work to this program.

Thanks for the replies.
Oct 13 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.