* 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 ****************************************************************
*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
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
STOPRUN.
* **** 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 PERFORMVARYING 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 GOTO EXIT-NOSUBCLASS END-IF
MOVE 4 TO norm1 *examining position 4 of char array NORMSQ *valid characters are 0-9, A-Z PERFORMVARYING 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 PERFORMVARYING 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.18 Sekunden
(vorverarbeitet)
¤
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.