MVSFORUMS.com :: View topic - 1st Stored Procedure - Can't seem to get it work correctly

1st Stored Procedure - Can't seem to get it work correctly

through

#1: 1st Stored Procedure - Can't seem to get it work correctly Author: NASCAR9, Location: California

Posted: Mon Jun 06, 2005 9:44 am
—
I created a simple SP and it does not seem to be doing the SQL Select in the SP. I know the SP is executing, I have moved values to the varibles and returned them. Can someone show what I'm doing wrong. The Key being passed exists in the table.

Thanks
NSACAR9

The SP Program:

Code:

CBL NODYNAM
IDENTIFICATION DIVISION.
PROGRAM-ID. FSPS0001.
AUTHOR.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SPECIAL-NAMES.
C01 IS NEW-PAGE.
INPUT-OUTPUT SECTION.
FILE-CONTROL.

DATA DIVISION.
FILE SECTION.

WORKING-STORAGE SECTION.

01 END-CODE PIC S9(9) BINARY VALUE ZERO.

01 CONSTANTS.
05 PGMNAME PIC X(11) VALUE 'FSPS0001 '.
05 TABLE-PREMSUM PIC X(30) VALUE
'HOURS.PREMSUM '.
05 TABLE-SYSDUMMY PIC X(30) VALUE
'SYSIBM.SYSDUMMY1 '.

EXEC SQL
INCLUDE PRMSUM
END-EXEC.

EXEC SQL
INCLUDE SQLCA
END-EXEC.

COPY CCSQLMSG.

LINKAGE SECTION.
*-----------------------------------------------------------
01 V-SSN PIC S9(9) USAGE COMP
VALUE ZEROES.
01 V-DATE PIC X(10) VALUE SPACES.
01 V-ANSWER PIC X(01) VALUE SPACES.
*-----------------------------------------------------------
PROCEDURE DIVISION USING V-SSN
V-DATE
V-ANSWER.
*-----------------------------------------------------------

MOVE V-SSN TO PREMSUM-SSNO.
MOVE V-DATE TO PREMSUM-PSTPERIOD.

EXEC SQL
SELECT BAL_MED
INTO :PREMSUM-BAL-MED
FROM HOURS.PREMSUM
WHERE SSNO = :PREMSUM-SSNO AND
PSTPERIOD = :PREMSUM-PSTPERIOD
END-EXEC.

MOVE SQLCODE TO CCSQLMSG-SQLCODE.

IF CCSQLMSG-SQLCODE-OK
MOVE PREMSUM-BAL-MED TO V-ANSWER
ELSE
IF CCSQLMSG-SQLCODE-NOTFND
MOVE 'N' TO V-ANSWER
ELSE
MOVE TABLE-PREMSUM TO CCSQLMSG-TABLE
MOVE OPER-SELECT TO CCSQLMSG-OPERATION
MOVE SQLCODE TO CCSQLMSG-SQLCODE-Z
MOVE SQLSTATE TO CCSQLMSG-SQLSTATE
DISPLAY PGMNAME CCSQLMSG-ERR-MSG
END-IF.

IF V-ANSWER > SPACES
CONTINUE
ELSE
MOVE 'G' TO V-ANSWER.

MOVE PREMSUM-SSNO TO V-SSN.
MOVE PREMSUM-PSTPERIOD TO V-DATE.

EXEC SQL
COMMIT
END-EXEC.

STOP RUN.

The calling Program:

IDENTIFICATION DIVISION.
PROGRAM-ID. TESTPROC.
AUTHOR.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SPECIAL-NAMES.
C01 IS NEW-PAGE.
INPUT-OUTPUT SECTION.
FILE-CONTROL.

DATA DIVISION.
FILE SECTION.

WORKING-STORAGE SECTION.

01 CONSTANTS.
05 PGMNAME PIC X(11) VALUE 'FSPS0001 '.

*01 WS-VARIBLES.
01 V-SSN PIC S9(9) USAGE COMP
VALUE 107057549.
01 V-DATE PIC X(10) VALUE '2004-08-01'.
01 V-ANSWER PIC X(01) VALUE SPACES.

EXEC SQL
INCLUDE SQLCA
END-EXEC.

COPY CCSQLMSG.

*---------------------------------------------------------------*
PROCEDURE DIVISION.
*---------------------------------------------------------------*

EXEC SQL
CALL HOURS.GETPREMSUM (:V-SSN, :V-DATE, :V-ANSWER)
END-EXEC.

MOVE SQLCODE TO CCSQLMSG-SQLCODE.

IF CCSQLMSG-SQLCODE-OK
CONTINUE
ELSE
IF CCSQLMSG-SQLCODE-NOTFND
MOVE 'X' TO V-ANSWER
ELSE
MOVE TABLE-PREMSUM TO CCSQLMSG-TABLE
MOVE OPER-SELECT TO CCSQLMSG-OPERATION
MOVE SQLCODE TO CCSQLMSG-SQLCODE-Z
MOVE SQLSTATE TO CCSQLMSG-SQLSTATE
DISPLAY PGMNAME CCSQLMSG-ERR-MSG
END-IF.

DISPLAY 'V-SSN = ' V-SSN.
DISPLAY 'V-DATE = ' V-DATE.
DISPLAY 'V-ANSWER = ' V-ANSWER.

EXEC SQL
COMMIT
END-EXEC.

STOP RUN.

The SP:

Code:

CREATE PROCEDURE HOURS.GETPREMSUM
(INOUT SSN INTEGER,
INOUT PERIOD DATE,
INOUT ANSWER CHAR(01))
LANGUAGE COBOL
EXTERNAL NAME FSPS0001
COLLID FBELCOLL
SECURITY DB2
WLM ENVIRONMENT WLMTENV1;

GRANT EXECUTE ON PROCEDURE HOURS.GETPREMSUM TO PUBLIC

The output:

V-SSN = 0107057549
V-DATE = 2004-08-01
V-ANSWER = G

#2: Author: kolusu, Location: San Jose

Posted: Mon Jun 06, 2005 10:29 am
—
Nascar9,

Is CCSQLMSG-SQLCODE-NOTFND mean sqlcode of 100? How is Bal-med defined on the table? Is it a character field or Integer field?

I notice that you are missing a Couple Of END-IF in your pgm.

Kolusu

#3: Author: NASCAR9, Location: California

Posted: Mon Jun 06, 2005 10:41 am
—
kolusu, Thanks for the reply.

01 CCSQLMSG-SQLCODE PIC S9(05) VALUE ZEROS.
88 CCSQLMSG-SQLCODE-OK VALUE +00000
88 CCSQLMSG-SQLCODE-NOTFND VALUE +00100
88 CCSQLMSG-SQLCODE-END-CURS VALUE +00100
88 CCSQLMSG-SQLCODE-BAD-DATE VALUE -00181
88 CCSQLMSG-SQLCODE-NULL VALUE -00305
88 CCSQLMSG-SQLCODE-DUPKEY VALUE -00803
88 CCSQLMSG-SQLCODE-MULT-ROWS VALUE -00811
88 CCSQLMSG-SQLCODE-UNCONNECT VALUE -00900

BAL_MED CHAR(1) NOT NULL,
10 PREMSUM-BAL-MED PIC X(1).

I may have been lazy on my END-IF statements.
If you see any that will cause a problem, please point them out.
Thanks
NASCAR9

#4: Author: Bithead, Location: Michigan, USA

Posted: Mon Jun 06, 2005 11:26 am
—
Is there anything in the WLM address space for any output from "DISPLAY PGMNAME CCSQLMSG-ERR-MSG"?

#5: Author: NASCAR9, Location: California

Posted: Mon Jun 06, 2005 11:33 am
—
The SQLCODE is Zeroes, I have checked with move in this IF:IF CCSQLMSG-SQLCODE-OK . The SP is executing but, the SQL never does the Select.

#6: Author: Bithead, Location: Michigan, USA

Posted: Mon Jun 06, 2005 11:39 am
—
Try this. Change

Code:

IF CCSQLMSG-SQLCODE-OK
MOVE PREMSUM-BAL-MED TO V-ANSWER
ELSE
IF CCSQLMSG-SQLCODE-NOTFND
MOVE 'N' TO V-ANSWER
ELSE

Code:

IF CCSQLMSG-SQLCODE-OK
MOVE PREMSUM-BAL-MED TO V-ANSWER
ELSE
IF CCSQLMSG-SQLCODE-NOTFND
MOVE 'N' TO V-ANSWER
ELSE
MOVE 'X' TO V-ANSWER

Then post the displays.

#7: Author: NASCAR9, Location: California

Posted: Mon Jun 06, 2005 12:02 pm
—
Bithead, Here's what happened:
V-SSN = 0107057549
V-DATE = 2004-08-01
V-ANSWER = X
I assummed it was running OK, guess I was wrong.

Heres a query from SPUFI:
SELECT SSNO, PSTPERIOD, BAL_MED
FROM HOURS.PREMSUM
WHERE SSNO = 0107057549 AND
PSTPERIOD = '2004-08-01';
---------+---------+---------+---------+---------+---------+-
SSNO PSTPERIOD BAL_MED
---------+---------+---------+---------+---------+---------+-
107057549 2004-08-01 Y
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

#8: Author: Bithead, Location: Michigan, USA

Posted: Mon Jun 06, 2005 12:04 pm
—
Please post the DCL for HOURS.PREMSUM.

#9: Author: NASCAR9, Location: California

Posted: Mon Jun 06, 2005 12:11 pm
—

Code:

******************************************************************
* DCLGEN TABLE(HOURS.PREMSUM) *
* LIBRARY(PF.DECLGEN(PRMSUM)) *
* ACTION(REPLACE) *
* LANGUAGE(COBOL) *
* NAMES(PREMSUM-) *
* STRUCTURE(HOURS-PREMSUM-TABLE) *
* QUOTE *
* COLSUFFIX(YES) *
* ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS *
******************************************************************
EXEC SQL DECLARE HOURS.PREMSUM TABLE
( SSNO INTEGER NOT NULL,
PSTPERIOD DATE NOT NULL,
MEMSTAT CHAR(10) NOT NULL,
WRKPERIOD DATE NOT NULL,
ACCTNO SMALLINT NOT NULL,
PREM_TYPE CHAR(10) NOT NULL,
PREM_MEDC SMALLINT NOT NULL,
PREM_PLAN CHAR(10),
DENT_PLAN CHAR(10),
PREMAMT DECIMAL(11, 2) NOT NULL,
DENTAL_COV CHAR(1) NOT NULL,
DENTAMT DECIMAL(11, 2) NOT NULL,
PREMPAID DECIMAL(11, 2) NOT NULL,
DENTPAID DECIMAL(11, 2) NOT NULL,
PAY_TYPE CHAR(10) NOT NULL,
BAL_MED CHAR(1) NOT NULL,
BAL_DENT CHAR(1) NOT NULL,
USERID CHAR(40) NOT NULL,
LASTUPDT TIMESTAMP NOT NULL,
EMPRNO INTEGER,
EMPRSUB SMALLINT,
PLANTYPE CHAR(1),
MEMTYPE CHAR(1),
MEMCLASS CHAR(8)
) END-EXEC.
**************************************************************
* COBOL DECLARATION FOR TABLE HOURS.PREMSUM
**************************************************************
01 HOURS-PREMSUM-TABLE.
* SSNO
10 PREMSUM-SSNO PIC S9(9) USAGE COMP.
* PSTPERIOD
10 PREMSUM-PSTPERIOD PIC X(10).
* MEMSTAT
10 PREMSUM-MEMSTAT PIC X(10).
* WRKPERIOD
10 PREMSUM-WRKPERIOD PIC X(10).
* ACCTNO
10 PREMSUM-ACCTNO PIC S9(4) USAGE COMP.
* PREM_TYPE
10 PREMSUM-PREM-TYPE PIC X(10).
* PREM_MEDC
10 PREMSUM-PREM-MEDC PIC S9(4) USAGE COMP.
* PREM_PLAN
10 PREMSUM-PREM-PLAN PIC X(10).
* DENT_PLAN
10 PREMSUM-DENT-PLAN PIC X(10).
* PREMAMT
10 PREMSUM-PREMAMT PIC S9(9)V9(2) USAGE COMP-3.
* DENTAL_COV
10 PREMSUM-DENTAL-COV PIC X(1).
* DENTAMT
10 PREMSUM-DENTAMT PIC S9(9)V9(2) USAGE COMP-3.
* PREMPAID
10 PREMSUM-PREMPAID PIC S9(9)V9(2) USAGE COMP-3.
* DENTPAID
10 PREMSUM-DENTPAID PIC S9(9)V9(2) USAGE COMP-3.
* PAY_TYPE
10 PREMSUM0PAY-TYPE PIC X(10).
* BAL_MED
10 PREMSUM-BAL-MED PIC X(1).
* BAL_DENT
10 PREMSUM-BAL-DENT PIC X(1).
* USERID
10 PREMSUM-USERID PIC X(40).
* LASTUPDT
10 PREMSUM-LASTUPDT PIC X(26).
* EMPRNO
10 PREMSUM-EMPRNO PIC S9(9) USAGE COMP.
* EMPRSUB
10 PREMSUM-EMPRSUB PIC S9(4) USAGE COMP.
* PLANTYPE
10 PREMSUM-PLANTYPE PIC X(1).
* MEMTYPE
10 PREMSUM-MEMTYPE PIC X(1).
* MEMCLASS
10 PREMSUM-MEMCLASS PIC X(8).
***********************************************************
* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 24
***********************************************************

#10: Author: Bithead, Location: Michigan, USA

Posted: Mon Jun 06, 2005 12:21 pm
—
NASCAR9,

The call looks OK so there must be some other reason. Try adding a parameter to hold the SQLCODE in the SP after the SELECT call and pass it back to the calling program. Then display that as well.

#11: Author: kolusu, Location: San Jose

Posted: Mon Jun 06, 2005 12:46 pm
—
Nascar,

You need to use the BBCODEs to enable the proper format of the code. put your code enclosed in between the bbcode and check it.

{code}
IDENTIFICATION DIVISION.
PROGRAM-ID. SAMPLE
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-CHAR PIC X(08).
{/code}

8 ) Translates to a smiley .

Now replace the { brackets with [ for {CODE} & {/CODE} .

Now this is how it looks like

Code:

IDENTIFICATION DIVISION.
PROGRAM-ID. SAMPLE
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-CHAR PIC X(08).

Kolusu

#12: Author: NASCAR9, Location: California

Posted: Mon Jun 06, 2005 1:48 pm
—
I'm getting a -804. How do I get the Reason Code? This will help in the problem determination.
Thanks
NASCAR9

#13: Author: schintala, Location: USA

Posted: Mon Jun 06, 2005 2:42 pm
—
Looks to me SP definition needs to be corrected.

CREATE PROCEDURE HOURS.GETPREMSUM
(IN SSN INTEGER,
IN PERIOD DATE,
OUT ANSWER CHAR(01))
LANGUAGE COBOL
DYNAMIC RESULT SETS 0
PARAMETER STYLE GENERAL
EXTERNAL NAME GETPREMSUM
COLLID FBELCOLL
MODIFIES SQL DATA
SECURITY DB2
WLM ENVIRONMENT WLMTENV1
STAY RESIDENT YES;

How WLM set up in your shop?

#14: Author: Bithead, Location: Michigan, USA

Posted: Mon Jun 06, 2005 2:46 pm
—
Nascar9,

Make sure that you have changed the pass area in the calling program and the stored procedure and you have added the parameter in the CREATE PROCEDURE step.

#15: Author: NASCAR9, Location: California

Posted: Mon Jun 06, 2005 2:53 pm
—
schintala, I'm looking up the parm you suggested. I tried to take the default wherever possible.

Bithead, I think I have.
Cobol SP:*----------------------------------------------------
LINKAGE SECTION.
*----------------------------------------------------
01 V-SSN PIC S9(9) USAGE COMP
VALUE ZEROES.
01 V-DATE PIC X(10) VALUE SPACES.
01 V-ANSWER PIC X(01) VALUE SPACES.
01 V-SQLCD PIC S9(9) USAGE COMP
VALUE ZEROES.
*----------------------------------------------------
PROCEDURE DIVISION USING V-SSN
V-DATE
V-ANSWER
V-SQLCD.
*----------------------------------------------------

Callin pgm:
01 V-SSN PIC S9(9) USAGE COMP
VALUE 107057549.
01 V-DATE PIC X(10) VALUE '2004-08-01'
01 V-ANSWER PIC X(01) VALUE SPACES.
01 V-SQLCD PIC S9(9) USAGE COMP
VALUE ZEROES.

------------------------------------------------------
PROCEDURE DIVISION.
------------------------------------------------------

EXEC SQL
CALL HOURS.GETPREMSUM (:V-SSN,
:V-DATE,
:V-ANSWER,
:V-SQLCD)
END-EXEC.

SP:

CREATE PROCEDURE HOURS.GETPREMSUM
(INOUT SSN INTEGER,
INOUT PERIOD DATE,
INOUT ANSWER CHAR(01),
INOUT SQLCD INTEGER)
LANGUAGE COBOL
EXTERNAL NAME FSPS0001
COLLID FBELCOLL
SECURITY DB2
WLM ENVIRONMENT WLMTENV1;

#16: Author: Bithead, Location: Michigan, USA

Posted: Mon Jun 06, 2005 2:59 pm
—
Nascar9,

The code looks fine. I also don't see a problem with the definition. Please post the results.

#17: Author: NASCAR9, Location: California

Posted: Mon Jun 06, 2005 3:03 pm
—
schintala,
CREATE PROCEDURE HOURS.GETPREMSUM
(IN SSN INTEGER,
IN PERIOD DATE,
OUT ANSWER CHAR(01))
LANGUAGE COBOL
DYNAMIC RESULT SETS 0 - This is the default
PARAMETER STYLE GENERAL - I will test this DB2SQL is default
EXTERNAL NAME GETPREMSUM - Should'nt this be my COBOL PGM(FSPS0001)?
COLLID FBELCOLL
MODIFIES SQL DATA - This is the default
SECURITY DB2
WLM ENVIRONMENT WLMTENV1
STAY RESIDENT YES; - When we move tor PROD this will be set to YES

#18: Author: NASCAR9, Location: California

Posted: Mon Jun 06, 2005 10:40 pm
—
Bithead, Here are the results:

V-SSN = 0107057549
V-DATE = 2004-08-01
V-ANSWER = X
V-SQLCD = 000000080N = -804

#19: Author: Bithead, Location: Michigan, USA

Posted: Tue Jun 07, 2005 10:09 am
—
Nascar9,

I can't see why you are getting a -804. Please can you post the code again?

#20: Author: kolusu, Location: San Jose

Posted: Tue Jun 07, 2005 10:31 am
—
Nascar9,

For some reason I beleive that might be due to an internal table overflow which led to a corruption of SQLCA. check this link which discusses a similar issue

http://mvsforums.com/helpboards/viewtopic.php?t=987&highlight=804

Kolusu

#21: Author: NASCAR9, Location: California

Posted: Tue Jun 07, 2005 10:36 am
—
Bithead, I really appreciate the help, Thanks!

SP:

Code:

CREATE PROCEDURE HOURS.GETPREMSUM
(INOUT SSN INTEGER,
INOUT PERIOD DATE,
INOUT ANSWER CHAR(01),
INOUT SQLCD INTEGER)
LANGUAGE COBOL
PARAMETER STYLE GENERAL
EXTERNAL NAME FSPS0001
COLLID FBELCOLL
SECURITY DB2
WLM ENVIRONMENT WLMTENV1;

GRANT EXECUTE ON PROCEDURE HOURS.GETPREMSUM TO PUBLIC ;

COBOL SP PGM:

Code:

CBL NODYNAM
IDENTIFICATION DIVISION.
PROGRAM-ID. FSPS0001.
AUTHOR. RON LEWIS.
*REMARKS. CREATE Stored Procedure
*mm/dd/yy x. xxxxxxxxxx - xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
****************************************************************
*
* FILES:
* +===========================================================+
* | DLBL NAME | I-O | DESCRIPTION |SEQ|RND|DYN|
* +===========+=====+=============================+===+===+===+
* +-----------------------------------------------------------+
* DATABASE TABLES:
* +===========================================================+
* | PROCEDURE | I-O | DESCRIPTION |
* +====================+=====+================================+
* | GETPREMSUM | IN | HOURS PREMSUM |
* +-----------------------------------------------------------+
* | SYSIBM.SYSDUMM1 | IN | IBM DUMMY TABLE |
* +--------------------+-----+--------------------------------+
****************************************************************
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SPECIAL-NAMES.
C01 IS NEW-PAGE.
INPUT-OUTPUT SECTION.
FILE-CONTROL.

DATA DIVISION.
FILE SECTION.

WORKING-STORAGE SECTION.

EXEC SQL
INCLUDE PRMSUM
END-EXEC.

EXEC SQL
INCLUDE SQLCA
END-EXEC.

COPY CCSQLMSG.
/
*---------------------------------------------------------------
LINKAGE SECTION.
*---------------------------------------------------------------
01 V-SSN PIC S9(9) USAGE COMP
VALUE ZEROES.
01 V-DATE PIC X(10) VALUE SPACES.
01 V-ANSWER PIC X(01) VALUE SPACES.
01 V-SQLCD PIC S9(9) USAGE COMP
VALUE ZEROES.
*---------------------------------------------------------------
PROCEDURE DIVISION USING V-SSN
V-DATE
V-ANSWER
V-SQLCD.
*---------------------------------------------------------------

MOVE V-SSN TO PREMSUM-SSNO.
MOVE V-DATE TO PREMSUM-PSTPERIOD.

EXEC SQL
SELECT BAL_MED
INTO :PREMSUM-BAL-MED
FROM HOURS.PREMSUM
WHERE SSNO = :PREMSUM-SSNO AND
PSTPERIOD = :PREMSUM-PSTPERIOD
END-EXEC.

MOVE SQLCODE TO CCSQLMSG-SQLCODE
V-SQLCD.

IF CCSQLMSG-SQLCODE-OK
MOVE PREMSUM-BAL-MED TO V-ANSWER
ELSE
IF CCSQLMSG-SQLCODE-NOTFND
MOVE 'N' TO V-ANSWER
ELSE
MOVE 'X' TO V-ANSWER
123456* MOVE TABLE-PREMSUM TO CCSQLMSG-TABLE
123456* MOVE OPER-SELECT TO CCSQLMSG-OPERATION
123456* MOVE SQLCODE TO CCSQLMSG-SQLCODE-Z
123456* MOVE SQLSTATE TO CCSQLMSG-SQLSTATE
123456* DISPLAY PGMNAME CCSQLMSG-ERR-MSG
END-IF.

IF V-ANSWER > SPACES
CONTINUE
ELSE
MOVE 'G' TO V-ANSWER.

MOVE PREMSUM-SSNO TO V-SSN.
MOVE PREMSUM-PSTPERIOD TO V-DATE.

EXEC SQL
COMMIT
END-EXEC.

STOP RUN.

Code:

Calling PGM:
************************* Top of Data ****************************
IDENTIFICATION DIVISION.
PROGRAM-ID. TESTPROC.
AUTHOR.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SPECIAL-NAMES.
C01 IS NEW-PAGE.
INPUT-OUTPUT SECTION.
FILE-CONTROL.

DATA DIVISION.
FILE SECTION.

WORKING-STORAGE SECTION.

01 WORK-AREA.
05 SUB PIC 99 VALUE ZEROES.

01 CONSTANTS.
05 PGMNAME PIC X(11) VALUE 'FSPS0001 '.
05 TABLE-PREMSUM PIC X(30) VALUE
'HOURS.PREMSUM '.
05 TABLE-SYSDUMMY PIC X(30) VALUE
'SYSIBM.SYSDUMMY1 '.
05 OPER-SELECT PIC X(09) VALUE 'SELECT '.

*01 WS-VARIBLES.
01 V-SSN PIC S9(9) USAGE COMP
VALUE 107057549.
01 V-DATE PIC X(10) VALUE '2004-08-01'.
01 V-ANSWER PIC X(01) VALUE SPACES.
01 V-SQLCD PIC S9(9) USAGE COMP
VALUE ZEROES.

EXEC SQL
INCLUDE SQLCA
END-EXEC.

COPY CCSQLMSG.

*---------------------------------------------------------------
PROCEDURE DIVISION.
*---------------------------------------------------------------

EXEC SQL
CALL HOURS.GETPREMSUM (:V-SSN,
:V-DATE,
:V-ANSWER,
:V-SQLCD)
END-EXEC.

MOVE SQLCODE TO CCSQLMSG-SQLCODE.

IF CCSQLMSG-SQLCODE-OK
CONTINUE
ELSE
IF CCSQLMSG-SQLCODE-NOTFND
MOVE 'W' TO V-ANSWER
ELSE
MOVE TABLE-PREMSUM TO CCSQLMSG-TABLE
MOVE OPER-SELECT TO CCSQLMSG-OPERATION
MOVE SQLCODE TO CCSQLMSG-SQLCODE-Z
MOVE SQLSTATE TO CCSQLMSG-SQLSTATE
DISPLAY PGMNAME CCSQLMSG-ERR-MSG
* PERFORM 9999-CALL-ABORT-SUBROUTINE
END-IF.

DISPLAY 'V-SSN = ' V-SSN.
DISPLAY 'V-DATE = ' V-DATE.
DISPLAY 'V-ANSWER = ' V-ANSWER.
DISPLAY 'V-SQLCD = ' V-SQLCD.

EXEC SQL
COMMIT
END-EXEC.

STOP RUN.

#22: Author: NASCAR9, Location: California

Posted: Tue Jun 07, 2005 10:46 am
—
kolusu, No internal tables, I moved WS around a little, No change.

#23: Author: Bithead, Location: Michigan, USA

Posted: Tue Jun 07, 2005 3:33 pm
—
I still don't see where the problem is. Try hard-coding variables in the SP to see if that works. If it does, then the variables are not being passed correctly.

Also, try getting rid of the SPECIAL NAMES. I am not sure if you can use it.

#24: Author: NASCAR9, Location: California

Posted: Tue Jun 07, 2005 3:45 pm
—
Bithead, My systems programmer got involved and fixed my problem. I will post the pgm. later. Thanks for all your help!!! Note, it is my fault.

#25: Author: NASCAR9, Location: California

Posted: Tue Jun 07, 2005 4:07 pm
—
SP - The COLLID was incorrect

Code:

CREATE PROCEDURE HOURS.GETPREMSUM
(INOUT SSN INTEGER,
INOUT PERIOD DATE,
INOUT ANSWER CHAR(01),
INOUT SQLMSG VARCHAR (720))
LANGUAGE COBOL
PARAMETER STYLE GENERAL
EXTERNAL NAME FSPS0001
COLLID FOELCOLL
SECURITY DB2
WLM ENVIRONMENT WLMTENV1;

GRANT EXECUTE ON PROCEDURE HOURS.GETPREMSUM TO PUBLIC ;

Calling PGM STOP RUN Changed for GO BACK
Different error routine

Code:

Calling program just changed to handle V-SQLCD

Thanks to everyone!!

#26: Author: Pknair,

Posted: Thu Oct 12, 2006 11:13 pm
—
Came in late SQLCODE 80N is not -804 but -805

MVSFORUMS.com -> Database

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1