|
IDENTIFICATION DIVISION.
PROGRAM-ID. XTS700.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. xyz.
OBJECT-COMPUTER. xyz.
DATA DIVISION.
WORKING-STORAGE SECTION.
* Standard COBOL (file "XTS700.SCO") calling SQL
* procedures in file "XTS700.MCO".
*Copyright 1995 National Computing Centre Limited
*and Computer Logic R&D S.A
*on behalf of the CTS5 SQL2 Project.
*All rights reserved.
*The CTS5 SQL2 Project is sponsored by the European Community.
*
*The National Computing Centre Limited and Computer Logic R&D
*have given permission to NIST to distribute this program
*over the World Wide Web in order to promote SQL standards.
*DISCLAIMER:
*This program was reviewed by employees of NIST for
*conformance to the SQL standards.
*NIST assumes no responsibility for any party's use of
*this program.
****************************************************************
*
* COMMENT SECTION
*
* SQL VALIDATION TEST SUITE V6.0
*
* XTS700.MCO TEST7001
* WRITTEN BY: Nickos Backalidis
* TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE
*
* This routine tests NULLIF which produces null
*
*REFERENCES
*6.9 FT.2 <case abbreviation>
*6.9 SR.1
*6.9 GR.1a
*6.9 GR.2a
*6.9 LR.2a -- Raised. Entry SQL restriction which prohibited
* the use of a <case expression>
*F#26 -- CASE expression
*
* Cleanups and fixes by V. Kogakis 08/12/95
*
* Cleanups and bug fixes by DWF 11/13/95:
* Fixed incorrect pass criteria
* Reformat to be consistent with NIST tests
* Change int flag back to long flag
* Removed repeated initialization of status codes
* Cleaned up inserts
* Removed extraneous transactions, used single rollback
* Add ROLLBACK after AUTHID (V5 global change)
* Fixed character string initializations
* Fixed incorrect EMPNUM refs where should be PNUM
* Fixed syntax errors
* Fixed blank padding on character strings
* Added missing SQLCODE check.
*
* QA STATUS : QA CHECK
*
* Fixed up again 1/18/96 DWF
****************************************************************
* EXEC SQL BEGIN DECLARE SECTION END-EXEC
01 SQLCODE PIC S9(9) COMP.
01 SQLSTATE PIC X(5).
01 uid PIC X(18).
01 uidx PIC X(18).
01 sal PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 ehours PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 ename PIC X(20).
01 ecity PIC X(15).
01 epnum PIC X(3).
01 nlres PIC X(15).
01 cnif PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 indic1 PIC S9(4) DISPLAY SIGN LEADING SEPARATE.
01 indic2 PIC S9(4) DISPLAY SIGN LEADING SEPARATE.
* EXEC SQL END DECLARE SECTION END-EXEC
01 norm1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 norm2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 ALPNUM-TABLE VALUE IS
"01234ABCDEFGH56789IJKLMNOPQRSTUVWXYZ".
05 ALPNUM PIC X OCCURS 36 TIMES.
01 NORMSQ.
05 NORMSQX PIC X OCCURS 5 TIMES.
01 errcnt PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
*date_time declaration
01 TO-DAY PIC 9(6).
01 THE-TIME PIC 9(8).
01 flag PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 SQL-COD PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
PROCEDURE DIVISION.
P0.
MOVE "CTS1 " TO uid
CALL "AUTHID" USING uid
MOVE "not logged in, not" TO uidx
* EXEC SQL SELECT USER INTO :uidx FROM CTS1.ECCO;
CALL "SUB1" USING SQLCODE SQLSTATE uidx
MOVE SQLCODE TO SQL-COD
* EXEC SQL ROLLBACK WORK;
CALL "SUB2" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
if (uid NOT = uidx) then
DISPLAY "ERROR: User ", uid, " expected. User ", uidx, "
- " connected"
STOP RUN
END-IF
MOVE 0 TO errcnt
DISPLAY "SQL Test Suite, V6.0, Module COBOL, xts700.sco"
DISPLAY
"59-byte ID"
DISPLAY "TEd Version # "
*date_time print
ACCEPT TO-DAY FROM DATE
ACCEPT THE-TIME FROM TIME
DISPLAY "Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME
******************** BEGIN TEST7001 *******************
MOVE 1 TO flag
DISPLAY " TEST7001"
DISPLAY " NULLIF producing NULL"
DISPLAY " References:"
DISPLAY " 6.9 FT.2 -- "
DISPLAY " 6.9 SR.1"
DISPLAY " 6.9 GR.1a"
DISPLAY " 6.9 GR.2a"
DISPLAY " 6.9 LR.2a -- Raised Entry SQL restriction
- " which prohibited"
DISPLAY " the use of a "
DISPLAY " F#26 -- CASE expression"
DISPLAY " - - - - - - - - - - - - - - - - - - -"
*Initialise error reporting variables
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
*printf("DELETE FROM STAFFB;\n\n");
*EXEC SQL DELETE FROM STAFFB;
*insert 6 rows in the table STAFFb in order to test NULLIF
DISPLAY "INSERT INTO STAFFb
- " VALUES(10000,'Kilroy',10000,'P4','Athens','M');"
* EXEC SQL INSERT INTO STAFFb
* VALUES(10000,'Kilroy',10000,'P4','Athens','M');
CALL "SUB3" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO STAFFb
- " VALUES(15000,'Nickos',20000,'P6','Nickos','M');"
* EXEC SQL INSERT INTO STAFFb
* VALUES(15000,'Nickos',20000,'P6','Nickos','M');
CALL "SUB4" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO STAFFb
- " VALUES(NULL,'Nickos',NULL,'P5','Rhodes','M');"
* EXEC SQL INSERT INTO STAFFb
* VALUES(NULL,'Nickos',NULL,'P5','Rhodes','M');
CALL "SUB5" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO STAFFb
- " VALUES(10010,'George',NULL,'P7','Georgia','M');"
* EXEC SQL INSERT INTO STAFFb
* VALUES(10010,'George',NULL,'P7','Georgia','M');
CALL "SUB6" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO STAFFb
- " VALUES(10005,NULL,30000,'P8',NULL,'M');"
* EXEC SQL INSERT INTO STAFFb
* VALUES(10005,NULL,30000,'P8',NULL,'M');
CALL "SUB7" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO STAFFb
- " VALUES(10001,'Gregory',12000,'P9',NULL,'M');"
* EXEC SQL INSERT INTO STAFFb
* VALUES(10001,'Gregory',12000,'P9',NULL,'M');
CALL "SUB8" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*initialise host variables
MOVE 0 TO sal
MOVE 0 TO ehours
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE "xxxxxxxxxxxxxxx" TO ecity
*Test NULLIF with numeric data types
DISPLAY "* NULLIF producing NULL with numeric data types *"
DISPLAY "DECLARE ALPHA CURSOR FOR SELECT
- " SALARY,EMPNAME,HOURS,CITY"
DISPLAY "FROM CTS1.STAFFb"
DISPLAY "WHERE NULLIF(SALARY,HOURS) IS NULL"
DISPLAY "ORDER BY EMPNAME;"
* EXEC SQL DECLARE ALPHA CURSOR FOR SELECT SALARY, EMPNAME,
* HOURS, CITY
* FROM CTS1.STAFFb
* WHERE NULLIF(SALARY,HOURS) IS NULL
* ORDER BY EMPNAME END-EXEC
DISPLAY "OPEN ALPHA;"
* EXEC SQL OPEN ALPHA;
CALL "SUB9" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*two rows satisfy the <search condition> of the query
DISPLAY "FETCH ALPHA INTO :sal, :ename, :ehours, :ecity;"
* EXEC SQL FETCH ALPHA INTO :sal, :ename, :ehours, :ecity
* ;
CALL "SUB10" USING SQLCODE SQLSTATE sal ename ehours ecity
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "Salary should be 10000 it is ", sal
DISPLAY "Empname should be 'Kilroy ' it is '",
ename, "'"
DISPLAY "Hours should be 10000 it is ", ehours
DISPLAY "City should be 'Athens ' it is '",
ecity, "'"
if (ename NOT = "Kilroy ") then
MOVE 0 TO flag
END-IF
if (ecity NOT = "Athens ") then
MOVE 0 TO flag
END-IF
if (ehours NOT = 10000 OR sal NOT = 10000) then
MOVE 0 TO flag
END-IF
*fetch second row
*initialise host variables
MOVE 0 TO sal
MOVE 0 TO ehours
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE "xxxxxxxxxxxxxxx" TO ecity
MOVE 99 TO indic1
MOVE 99 TO indic2
DISPLAY "FETCH ALPHA INTO :sal:indic1
- " ,:ename,:ehours:indic2,:ecity"
* EXEC SQL FETCH ALPHA INTO
* :sal:indic1,:ename,:ehours:indic2,:ecity;
CALL "SUB11" USING SQLCODE SQLSTATE sal indic1 ename ehours
indic2 ecity
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "indic1 should be -1; its value is ", indic1
DISPLAY "indic2 should be -1; its value is ", indic2
DISPLAY "Ename should be 'Nickos '; its
- " value is '", ename, "'"
DISPLAY "City should be 'Rhodes '; its value is
- " '", ecity, "'"
if (ename NOT = "Nickos ") then
MOVE 0 TO flag
END-IF
if (ecity NOT = "Rhodes ") then
MOVE 0 TO flag
END-IF
if (indic1 NOT = -1 OR indic2 NOT = -1) then
MOVE 0 TO flag
END-IF
*end fetches
*check that no more rows were retrieved which ** fail
*check that a condition of no data is raised
DISPLAY "FETCH ALPHA INTO :sal, :ename, :ehours, :ecity;"
* EXEC SQL FETCH ALPHA INTO :sal, :ename, :ehours, :ecity
* ;
CALL "SUB12" USING SQLCODE SQLSTATE sal ename ehours ecity
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE ALPHA;"
* EXEC SQL CLOSE ALPHA;
CALL "SUB13" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
***** test NULLIF with character string values ****
*using NULLIF in the <select list>
DISPLAY "* NULLIF producing NULL with character string
- " values *"
DISPLAY "DECLARE BETA CURSOR FOR SELECT SALARY,PNUM,HOURS"
DISPLAY ",NULLIF(EMPNAME,CITY)"
DISPLAY "FROM CTS1.STAFFb"
DISPLAY "WHERE EMPNAME = CITY OR EMPNAME IS NULL"
DISPLAY "ORDER BY PNUM;"
* EXEC SQL DECLARE BETA CURSOR FOR SELECT
* SALARY,PNUM,HOURS,NULLIF(EMPNAME,CITY)
* FROM CTS1.STAFFb
* WHERE EMPNAME = CITY OR EMPNAME IS NULL
* ORDER BY PNUM END-EXEC
DISPLAY "OPEN BETA;"
* EXEC SQL OPEN BETA;
CALL "SUB14" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*initialise host variables
MOVE 0 TO sal
MOVE 0 TO ehours
MOVE "xxx" TO epnum
MOVE 99 TO indic1
*two rows satisfy the <search condition> of the query
DISPLAY "FETCH BETA INTO
- " :sal,:epnum,:ehours,:nlres:indic1;"
* EXEC SQL FETCH BETA INTO :sal,:epnum,:ehours,:nlres:indic1
* ;
CALL "SUB15" USING SQLCODE SQLSTATE sal epnum ehours nlres
indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "SALARY should be 15000; its value is ", sal
DISPLAY "PNUM should be 'P6 '; its value is '", epnum,
"'"
DISPLAY "HOURS should be 20000; its value is ", ehours
DISPLAY "indic1 should be -1; its value is ", indic1
if (sal NOT = 15000 OR epnum NOT = "P6 " OR
ehours NOT = 20000) then
MOVE 0 TO flag
END-IF
if (indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
*initialise host variables
MOVE 0 TO sal
MOVE 0 TO ehours
MOVE "xxxxxxxxxxxxxxx" TO nlres
MOVE "xxx" TO epnum
MOVE 99 TO indic1
*second row
DISPLAY "FETCH BETA INTO
- " :sal,:epnum,:ehours,:nlres:indic1;"
* EXEC SQL FETCH BETA INTO :sal,:epnum,:ehours,:nlres:indic1
* ;
CALL "SUB16" USING SQLCODE SQLSTATE sal epnum ehours nlres
indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "SALARY should be 10005; its value is ", sal
DISPLAY "PNUM should be 'P8 '; its value is '", epnum,
"'"
DISPLAY "HOURS should be 30000; its value is ", ehours
DISPLAY "indic1 should be -1; its value is ", indic1
if (sal NOT = 10005 OR epnum NOT = "P8 " OR
ehours NOT = 30000) then
MOVE 0 TO flag
END-IF
if (indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
*fetch one more row and check that a condition of no
*data is raised
DISPLAY "FETCH BETA INTO
- " :sal,:epnum,:ehours,:nlres:indic1;"
* EXEC SQL FETCH BETA INTO :sal,:epnum,:ehours,:nlres:indic1
* ;
CALL "SUB17" USING SQLCODE SQLSTATE sal epnum ehours nlres
indic1
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE BETA;"
* EXEC SQL CLOSE BETA;
CALL "SUB18" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*Test NULLIF as an operand of NULLIF
MOVE 0 TO cnif
DISPLAY "* NULLIF as operand of NULLIF
- " *"
DISPLAY "SELECT SUM(NULLIF(NULLIF(SALARY,10000),20000))"
DISPLAY "INTO :cnif FROM STAFFb;"
*cnif = sum of the salaries of all the departments
*excluding the ones of 10000, 20000 and NULL
* EXEC SQL SELECT SUM(NULLIF(NULLIF(SALARY,10000),20000))
* INTO :cnif FROM STAFFb;
CALL "SUB19" USING SQLCODE SQLSTATE cnif
MOVE SQLCODE TO SQL-COD
*SQLSTATE should be 01003
DISPLAY "SQLSTATE should be 01003; its value is ", SQLSTATE
if (SQLSTATE NOT = "01003") then
MOVE 0 TO flag
END-IF
DISPLAY "Cnif should be 195016 its value is ", cnif
if (cnif NOT = 195016) then
MOVE 0 TO flag
END-IF
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB20" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*now record results
if ( flag = 1 ) then
DISPLAY " xts700.mco *** pass *** "
* EXEC SQL INSERT INTO CTS1.TESTREPORT
* VALUES('7001','pass','MCO');
CALL "SUB21" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " xts700.mco *** fail *** "
* EXEC SQL INSERT INTO CTS1.TESTREPORT
* VALUES('7001','fail','MCO');
CALL "SUB22" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "========================================"
* EXEC SQL COMMIT WORK;
CALL "SUB23" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST7001 ********************
**** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0
STOP RUN.
* **** Procedures for PERFORM statements
*Test SQLCODE and SQLSTATE for normal completion.
CHCKOK.
DISPLAY "SQLCODE should be 0; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 00000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 0 OR NORMSQ NOT = "00000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "00000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
.
NOSUBCLASS.
*This routine replaces valid implementation-defined
*subclasses with 000. This replacement equates valid
*implementation-defined subclasses with the 000 value
*expected by the test case; otherwise the test will fail.
*After calling NOSUBCLASS, NORMSQ will be tested
* SQLSTATE will be printed.
MOVE SQLSTATE TO NORMSQ
MOVE 3 TO norm1
*subclass begins in position 3 of char array NORMSQ
*valid subclass begins with 5-9, I-Z, end of ALPNUM table
PERFORM VARYING norm2 FROM 14 BY 1 UNTIL norm2 > 36
if (NORMSQX(norm1) = ALPNUM(norm2)) then
MOVE "0" TO NORMSQX(norm1)
END-IF
END-PERFORM
*Quit if NORMSQ is unchanged. Subclass is not impl.-def.
*Changed NORMSQ means implementation-defined subclass,
*so proceed to zero it out, if valid (0-9,A-Z)
if (NORMSQ = SQLSTATE) then
GO TO EXIT-NOSUBCLASS
END-IF
MOVE 4 TO norm1
*examining position 4 of char array NORMSQ
*valid characters are 0-9, A-Z
PERFORM VARYING norm2 FROM 1 BY 1 UNTIL norm2 > 36
if (NORMSQX(norm1) = ALPNUM(norm2)) then
MOVE "0" TO NORMSQX(norm1)
END-IF
END-PERFORM
MOVE 5 TO norm1
*valid characters are 0-9, A-Z
*examining position 5 of char array NORMSQ
PERFORM VARYING norm2 FROM 1 BY 1 UNTIL norm2 > 36
if (NORMSQX(norm1) = ALPNUM(norm2)) then
MOVE "0" TO NORMSQX(norm1)
END-IF
END-PERFORM
*implementation-defined subclasses are allowed for warnings
*(class = 01). These equate to successful completion
*SQLSTATE values of 00000.
*Reference SQL-92 4.28 SQL-transactions, paragraph 2
if (NORMSQX(1) = "0" AND NORMSQX(2) = "1") then
MOVE "0" TO NORMSQX(2)
END-IF
.
EXIT-NOSUBCLASS.
EXIT.
¤ Dauer der Verarbeitung: 0.41 Sekunden
(vorverarbeitet)
¤
|
Haftungshinweis
Die Informationen auf dieser Webseite wurden
nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit,
noch Qualität der bereit gestellten Informationen zugesichert.
Bemerkung:
Die farbliche Syntaxdarstellung ist noch experimentell.
|
