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 Previous 1, 2 :| |:
MVSFORUMS.com -> Database

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

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 CONSTANTS.
05 PGMNAME PIC X(11) VALUE 'FSPS0001 '.
05 TABLE-PREMSUM PIC X(30) VALUE
'HOURS.PREMSUM '.

01 ERROR-MESSAGE.
05 ERROR-LEN PIC S9(4) COMP VALUE +720.
05 ERROR-TEXT-COMP PIC X(720) VALUE SPACES.
05 ERROR-TEXT REDEFINES ERROR-TEXT-COMP
PIC X(72) OCCURS 10 TIMES
INDEXED BY ERROR-INDEX.

77 ERROR-TEXT-LEN PIC S9(9) COMP VALUE +72.

COPY CCSQLMSG.

EXEC SQL
INCLUDE PRMSUM
END-EXEC.

EXEC SQL
INCLUDE SQLCA
END-EXEC.
/
*---------------------------------------------------------------
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-SQLMSG PIC X(720) VALUE SPACES.
*01 V-SQLCD PIC S9(9) USAGE COMP
* VALUE ZEROES.
*---------------------------------------------------------------
PROCEDURE DIVISION USING V-SSN
V-DATE
V-ANSWER
V-SQLMSG.
*---------------------------------------------------------------

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.

CALL 'DSNTIAR' USING SQLCA ERROR-MESSAGE ERROR-TEXT-LEN.

MOVE ERROR-TEXT-COMP TO V-SQLMSG.
IF PREMSUM-BAL-MED NOT EQUAL SPACES
MOVE PREMSUM-BAL-MED TO V-ANSWER
ELSE
MOVE 'G' TO V-ANSWER.

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

EXEC SQL
COMMIT
END-EXEC.

GOBACK.

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

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