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

[^/[Print]\]
Goto page 1, 2 Next :| |:
MVSFORUMS.com -> Database

#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;

MVSFORUMS.com -> Database

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

Goto page 1, 2 Next :| |: Page 1 of 2