| products/sources/formale sprachen/Cobol/Test-Suite/SQL M/ |
|
Quellcode-Bibliothek© Kompilation durch diese Firma[Weder Korrektheit noch Funktionsfähigkeit der Software werden zugesichert.]Datei: xts700.cob Sprache: Cobol |
|
||||||
|
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 - " *" 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.30 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.Bot Zugriff |
