| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/Cobol/Test-Suite/SQL M/ Datei vom 4.1.2008 mit Größe 13 kB |
|
Quelle dml009.cob Sprache: Cobol |
|||||||||
|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML009. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. xyz. OBJECT-COMPUTER. xyz. DATA DIVISION. WORKING-STORAGE SECTION. * Standard COBOL (file "DML009.SCO") calling SQL * procedures in file "DML009.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. * * DML009.SCO * WRITTEN BY: HU YANPING * TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE * * THIS ROUTINE TESTS THE INSERT STATEMENT IN THE LANGUAGE OF * SQL. THE FORMAT: * INSERT INTO <table name> ((<insert list>)) * {VALUES(<insert value list>) | <query specification>} * * REFERENCES * AMERICAN NATIONAL STANDARD database language - SQL * X3.135-1989 * * SECTION 8.7 <insert statement> * SECTION 8.10 <select statement> * **************************************************************** * EXEC SQL BEGIN DECLARE SECTION END-EXEC 01 PNO1 PIC X(3). 01 EMPNO1 PIC X(3). 01 HOURS1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 i PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 ii PIC S9(9) DISPLAY SIGN LEADING SEPARATE. * EXEC SQL END DECLARE SECTION END-EXEC 01 iii PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 uid PIC X(18). 01 uidx PIC X(18). 01 SQL-COD PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 SQLCODE PIC S9(9) COMP. 01 errcnt PIC S9(4) DISPLAY SIGN LEADING SEPARATE. * date_time declaration * 01 TO-DAY PIC 9(6). 01 THE-TIME PIC 9(8). PROCEDURE DIVISION. P0. 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 " " STOP RUN END-IF MOVE 0 TO errcnt DISPLAY "SQL Test Suite, V6.0, Module COBOL, dml009.sco" DISPLAY " " DISPLAY "59-byte ID" DISPLAY "TEd Version #" DISPLAY " " * date_time print * ACCEPT TO-DAY FROM DATE ACCEPT THE-TIME FROM TIME DISPLAY "Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME ******************** BEGIN TEST0022 ******************* DISPLAY " TEST0022 " DISPLAY " insert one record " DISPLAY "reference X3.135-1989 section 8.7 General Rules 3)" DISPLAY " - - - - - - - - - - - - - - - - - - -" * EXEC SQL INSERT INTO WORKS(PNUM,EMPNUM,HOURS) * VALUES ('P22','E22',NULL) END-EXEC CALL "SUB1" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT EMPNUM,PNUM * INTO :EMPNO1,:PNO1 * FROM WORKS * WHERE HOURS IS NULL END-EXEC CALL "SUB2" USING SQLCODE EMPNO1 PNO1 MOVE SQLCODE TO SQL-COD DISPLAY " EMPNO1=", EMPNO1 " and PNO1=", PNO1 " - " " DISPLAY " SQLCODE=", SQL-COD " " DISPLAY "The answer should be SQLCODE = 0 & EMPNO1 = E22." if (SQLCODE = 0 AND EMPNO1 = "E22") then * EXEC SQL ROLLBACK WORK; CALL "SUB3" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0022','pass','MCO') END-EXEC CALL "SUB4" USING SQLCODE MOVE SQLCODE TO SQL-COD else * EXEC SQL ROLLBACK WORK; CALL "SUB5" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " dml009.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0022','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB6" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "===================================================" DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB7" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0022 ******************* ******************** BEGIN TEST0023 ******************* DISPLAY " TEST0023 " DISPLAY " insert with query spec. " DISPLAY "reference X3.135-1989 section 8.7 GR7c & 5.5 SR7." DISPLAY " - - - - - - - - - - - - - - - - - - -" *CREATE TABLE TEMP_S * (EMPNUM CHAR(3), * GRADE DECIMAL(4), * CITY CHAR(15)) MOVE 23234 to HOURS1 * EXEC SQL DELETE FROM TEMP_S; CALL "SUB8" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL COMMIT WORK; CALL "SUB9" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO TEMP_S(EMPNUM,GRADE,CITY) * VALUES('E23',2323.4,'China') END-EXEC CALL "SUB10" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT COUNT(*) * INTO :i * FROM TEMP_S END-EXEC CALL "SUB11" USING SQLCODE i MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO TEMP_S * VALUES('E23',:HOURS1,'China') END-EXEC CALL "SUB12" USING SQLCODE, HOURS1 MOVE SQLCODE TO SQL-COD MOVE SQLCODE TO iii MOVE 0 TO ii * EXEC SQL SELECT COUNT(*) * INTO :ii * FROM TEMP_S END-EXEC CALL "SUB13" USING SQLCODE ii MOVE SQLCODE TO SQL-COD DISPLAY " iii = ", iii "; ii = ", ii "; i = ", i " " DISPLAY "The correct answer should be iii < 0, ii=1 & i=1 or" DISPLAY " iii = 0, ii=2 & i=1. " * EXEC SQL ROLLBACK WORK; CALL "SUB14" USING SQLCODE MOVE SQLCODE TO SQL-COD if (iii < 0 AND ii = 1 AND i = 1 OR iii = 0 AND ii = 2 AND i = 1) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0023','pass','MCO') END-EXEC CALL "SUB15" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml009.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0023','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB16" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "==================================================" DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB17" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0023 ******************* ******************** BEGIN TEST0024 ******************* DISPLAY " TEST0024 " DISPLAY "reference X3.135-1989 section 8.7 General Rules 4)" DISPLAY " the result of the query is empty " DISPLAY " - - - - - - - - - - - - - - - - - - -" * EXEC SQL INSERT INTO TEMP_S * SELECT EMPNUM,GRADE,CITY * FROM STAFF * WHERE GRADE > 13 END-EXEC CALL "SUB18" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " SQLCODE=", SQL-COD " " DISPLAY "The answer should be SQLCODE = 100. " if (SQLCODE = 100) then * EXEC SQL ROLLBACK WORK; CALL "SUB19" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0024','pass','MCO') END-EXEC CALL "SUB20" USING SQLCODE MOVE SQLCODE TO SQL-COD else * EXEC SQL ROLLBACK WORK; CALL "SUB21" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " dml009.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0024','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB22" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "=================================================" DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB23" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0024 ******************* ******************** BEGIN TEST0025 ******************* DISPLAY " TEST0025 " DISPLAY "reference X3.135-1989 section 8.7 General Rules 4)" DISPLAY " the result of the query is not empty " DISPLAY " - - - - - - - - - - - - - - - - - - -" * EXEC SQL DELETE FROM TEMP_S; CALL "SUB24" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 0 TO ii * EXEC SQL INSERT INTO TEMP_S(EMPNUM,GRADE,CITY) * SELECT EMPNUM,GRADE,CITY * FROM STAFF * WHERE GRADE > 12 END-EXEC CALL "SUB25" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE SQLCODE TO iii * EXEC SQL SELECT COUNT(*) * INTO :ii * FROM TEMP_S END-EXEC CALL "SUB26" USING SQLCODE ii MOVE SQLCODE TO SQL-COD DISPLAY " iii=", iii ", ii=", ii DISPLAY "The answer should be iii=0 & ii=2." * EXEC SQL ROLLBACK WORK; CALL "SUB27" USING SQLCODE MOVE SQLCODE TO SQL-COD if (ii = 2 AND iii = 0) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0025','pass','MCO') END-EXEC CALL "SUB28" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml009.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0025','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB29" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "=================================================" DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB30" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0025 ******************* ******************** BEGIN TEST0026 ******************* DISPLAY " TEST0026 " DISPLAY " insert with query spec. " DISPLAY "reference X3.135-1989 section 8.7 General Rules 2)" DISPLAY " - - - - - - - - - - - - - - - - - - -" *CREATE VIEW TEMP_SS(EMPNUM,GRADE,CITY) * AS SELECT EMPNUM,GRADE,CITY * FROM STAFF3 * WHERE GRADE > 12 * WITH CHECK OPTION * EXEC SQL INSERT INTO TEMP_SS * SELECT EMPNUM,GRADE,CITY * FROM STAFF3 * WHERE GRADE = 10 END-EXEC CALL "SUB31" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " SQLCODE=", SQL-COD " " DISPLAY "The answer should be SQLCODE < 0." MOVE SQLCODE TO i * EXEC SQL ROLLBACK WORK; CALL "SUB32" USING SQLCODE MOVE SQLCODE TO SQL-COD if (i < 0) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0026','pass','MCO') END-EXEC CALL "SUB33" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml009.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0026','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB34" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "=================================================" DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB35" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0026 ******************* **** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOP RUN. * **** Procedures for PERFORM statements ¤ Dauer der Verarbeitung: 0.3 Sekunden (vorverarbeitet) ¤*© Formatika GbR, Deutschland |
|
2026-03-28