* Standard COBOL (file "DML013.SCO") calling SQL * procedures in file "DML013.MCO"
**************************************************************** * * COMMENT SECTION * * DATE 1987/08/21 STANDARD COBOL LANGUAGE * NIST SQL VALIDATION TEST SUITE V6.0 * DISCLAIMER: * This program was written by employees of NIST to test SQL * implementations for conformance to the SQL standards. * NIST assumes no responsibility for any party's use of * this program. * * DML013.SCO * WRITTEN BY: HU YANPING * TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE * * THIS ROUTINE TESTS THE SET FUNCTION SPECIFICATION IN * COMMON ELEMENTS OF THE SQL LANGUAGE. * * REFERENCES * AMERICAN NATIONAL STANDARD database language - SQL * X3.135-1989 * * SECTION 5.8 <set function specification> * SECTION 8. Data manipulation language * ****************************************************************
MOVE"HU"TO uid CALL"AUTHID"USING uid MOVE"not logged in, not"TO uidx CALL"AUTHCK"USING SQLCODE uidx MOVE SQLCODE TO SQL-COD if (uid NOT = uidx) then DISPLAY"ERROR: User " uid " expected." DISPLAY"User " uidx " connected." DISPLAY" " STOPRUN END-IF
MOVE 0 TO errcnt DISPLAY "SQL Test Suite, V6.0, Module COBOL, dml013.sco" DISPLAY" " DISPLAY "59-byte ID" DISPLAY"TEd Version #" DISPLAY" " * date_time print * ACCEPT TO-DAY FROMDATE ACCEPT THE-TIME FROMTIME DISPLAY"Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME
******************** BEGIN TEST0039 *******************
DISPLAY" TEST0039 - TEST0044 " MOVE 0 TO i DISPLAY" Set Function Specification " DISPLAY"Reference X3.135-1989 section 5.8 General Rules " DISPLAY" ------------------------------------------ " DISPLAY" TEST0039 " DISPLAY" Reference 5.8 General Rules 1)" DISPLAY" - - - - - - - - - - - - - - - - - - - - - "
DISPLAY" *** INSERT INTO WORKS VALUES('E5','P5',NULL) " DISPLAY" *** SELECT COUNT(DISTINCT HOURS) " DISPLAY" *** INTO :i " DISPLAY" *** FROM WORKS " * EXEC SQL INSERT INTO WORKS * VALUES('E5','P5',NULL) END-EXEC CALL"SUB1"USING SQLCODE MOVE SQLCODE TO SQL-COD
* EXEC SQL SELECT COUNT(DISTINCT HOURS) * INTO :i * FROM WORKS END-EXEC CALL"SUB2"USING SQLCODE i MOVE SQLCODE TO SQL-COD
DISPLAY" Test COUNT DISTINCT function i=", i " " DISPLAY" the correct answer should be i=4 " if (i = 4) then DISPLAY" *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0039','pass','MCO') END-EXEC CALL"SUB4"USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY" dml013.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0039','fail','MCO') END-EXEC ADD 1 TO errcnt CALL"SUB5"USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY"==============================================="
DISPLAY" Test SUM ALL function i=", i " " DISPLAY" the correct answer should be i=464 " if (i = 464) then DISPLAY" *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0167','pass','MCO') END-EXEC CALL"SUB10"USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY" dml013.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0167','fail','MCO') END-EXEC ADD 1 TO errcnt CALL"SUB11"USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY"================================================="
DISPLAY" Test SUM function i=", i " " DISPLAY" the correct answer should be i=464 " if (i = 464) then DISPLAY" *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0168','pass','MCO') END-EXEC CALL"SUB16"USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY" dml013.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0168','fail','MCO') END-EXEC ADD 1 TO errcnt CALL"SUB17"USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY"=============================================="
DISPLAY" Test COUNT * function i=", i " " DISPLAY" the correct answer should be i=13 " if (i = 13) then DISPLAY" *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0169','pass','MCO') END-EXEC CALL"SUB22"USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY" dml013.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0169','fail','MCO') END-EXEC ADD 1 TO errcnt CALL"SUB23"USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY"============================================="
******************** END TEST0169 *******************
******************** BEGIN TEST0040 *******************
MOVE 0 TO i DISPLAY" TEST0040 " DISPLAY" Reference 5.8 General Rules 2) " DISPLAY" - - - - - - - - - - - - - - - - - - - " DISPLAY" *** SELECT SUM(HOURS) " DISPLAY" *** INTO :i " DISPLAY" *** FROM WORKS " DISPLAY" *** WHERE PNUM='P2' "
* EXEC SQL SELECT SUM(HOURS) * INTO :i * FROM WORKS * WHERE PNUM='P2' END-EXEC CALL"SUB25"USING SQLCODE i MOVE SQLCODE TO SQL-COD DISPLAY" Test SUM function i=", i " " DISPLAY" the correct answer should be i=140 " if (i = 140) then DISPLAY" *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0040','pass','MCO') END-EXEC CALL"SUB26"USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY" dml013.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0040','fail','MCO') END-EXEC ADD 1 TO errcnt CALL"SUB27"USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY"================================================"
******************** END TEST0040 *******************
******************** BEGIN TEST0170 *******************
MOVE 0 TO i DISPLAY" TEST0170 " DISPLAY" Reference 5.8 General Rules 2) " DISPLAY" - - - - - - - - - - - - - - - - - - - " DISPLAY" *** SELECT SUM(DISTINCT HOURS) " DISPLAY" *** INTO :i " DISPLAY" *** FROM WORKS " DISPLAY" *** WHERE PNUM='P2' "
* EXEC SQL SELECT SUM (DISTINCT HOURS) * INTO :i * FROM WORKS * WHERE PNUM='P2' END-EXEC CALL"SUB29"USING SQLCODE i MOVE SQLCODE TO SQL-COD DISPLAY" Test SUM DISTINCT function i=", i " " DISPLAY" the correct answer should be i=100 " if (i = 100) then DISPLAY" *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0170','pass','MCO') END-EXEC CALL"SUB30"USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY" dml013.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0170','fail','MCO') END-EXEC ADD 1 TO errcnt CALL"SUB31"USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY"===============================================" * EXEC SQL COMMIT WORK; CALL"SUB32"USING SQLCODE MOVE SQLCODE TO SQL-COD
******************** END TEST0170 *******************
******************** BEGIN TEST0171 *******************
MOVE 0 TO i DISPLAY" TEST0171 " DISPLAY" Reference 5.8, 5.9, 5.25, 8.10 " DISPLAY" - - - - - - - - - - - - - - - - - - - " DISPLAY" *** SELECT SUM(HOURS)+10 " DISPLAY" *** INTO :i " DISPLAY" *** FROM WORKS " DISPLAY" *** WHERE PNUM='P2' "
* EXEC SQL SELECT SUM(HOURS)+10 * INTO :i * FROM WORKS * WHERE PNUM='P2' END-EXEC CALL"SUB33"USING SQLCODE i MOVE SQLCODE TO SQL-COD DISPLAY" Test SUM function + some value i=", i " " DISPLAY" the correct answer should be i=150 " if (i = 150) then DISPLAY" *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0171','pass','MCO') END-EXEC CALL"SUB34"USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY" dml013.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0171','fail','MCO') END-EXEC ADD 1 TO errcnt CALL"SUB35"USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY"================================================" * EXEC SQL COMMIT WORK; CALL"SUB36"USING SQLCODE MOVE SQLCODE TO SQL-COD
******************** END TEST0171 *******************
******************** BEGIN TEST0041 ******************* DISPLAY" TEST0041 " DISPLAY" Reference 5.8 General Rules 4) d)" DISPLAY" - - - - - - - - - - - - - - - - - - - - " DISPLAY" *** SELECT EMPNUM " DISPLAY" *** FROM STAFF " DISPLAY" *** WHERE GRADE = " DISPLAY" *** (SELECT MAX(GRADE) " DISPLAY" *** FROM STAFF) " DISPLAY" *** ORDER BY EMPNUM "
* EXEC SQL DECLARE X CURSOR * FOR SELECT EMPNUM * FROM STAFF * WHERE GRADE = * (SELECT MAX( GRADE ) * FROM STAFF ) * ORDER BY EMPNUM END-EXEC
MOVE" "TO EMPNO1 * EXEC SQL OPEN X; CALL"SUB37"USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL FETCH X INTO :EMPNO1; CALL"SUB38"USING SQLCODE EMPNO1 MOVE SQLCODE TO SQL-COD DISPLAY" Test MAX function EMPNO1=", EMPNO1 " " DISPLAY" the correct answer should be EMPNO1=E3 " if (EMPNO1 = "E3") then DISPLAY" *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0041','pass','MCO') END-EXEC CALL"SUB39"USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY" dml013.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0041','fail','MCO') END-EXEC ADD 1 TO errcnt CALL"SUB40"USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY"==============================================" * EXEC SQL COMMIT WORK; CALL"SUB41"USING SQLCODE MOVE SQLCODE TO SQL-COD
******************** END TEST0041 *******************
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.