| View previous topic :: View next topic |
| Author |
Message |
Bithead
Advanced
Joined: 03 Jan 2003
Posts: 550
Topics: 23
Location: Michigan, USA
|
 Posted: Mon Jun 06, 2005 2:59 pm Post subject: |
 |
|
Nascar9,
The code looks fine. I also don't see a problem with the definition. Please post the results. |
|
| Back to top |
|
 |
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Mon Jun 06, 2005 3:03 pm Post subject: |
|
|
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 |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Mon Jun 06, 2005 10:40 pm Post subject: |
|
|
Bithead, Here are the results:
V-SSN = 0107057549
V-DATE = 2004-08-01
V-ANSWER = X
V-SQLCD = 000000080N = -804 |
|
| Back to top |
|
|
Bithead
Advanced
Joined: 03 Jan 2003
Posts: 550
Topics: 23
Location: Michigan, USA
|
| Posted: Tue Jun 07, 2005 10:09 am Post subject: |
|
|
Nascar9,
I can't see why you are getting a -804. Please can you post the code again? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12366
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Tue Jun 07, 2005 10:36 am Post subject: |
|
|
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.
|
|
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Tue Jun 07, 2005 10:46 am Post subject: |
|
|
kolusu, No internal tables, I moved WS around a little, No change.
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
Bithead
Advanced
Joined: 03 Jan 2003
Posts: 550
Topics: 23
Location: Michigan, USA
|
| Posted: Tue Jun 07, 2005 3:33 pm Post subject: |
|
|
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. |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Tue Jun 07, 2005 3:45 pm Post subject: |
|
|
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.
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Tue Jun 07, 2005 4:07 pm Post subject: |
|
|
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!! |
|
| Back to top |
|
|
Pknair
Beginner
Joined: 29 Nov 2005
Posts: 26
Topics: 9
|
| Posted: Thu Oct 12, 2006 11:13 pm Post subject: |
|
|
| Came in late SQLCODE 80N is not -804 but -805 |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
