Quelle  dml062.cob   Sprache: Cobol

       IDENTIFICATION DIVISION.
       PROGRAM-ID.  DML062.
       ENVIRONMENT DIVISION.
       CONFIGURATION SECTION.
       SOURCE-COMPUTER.  xyz.
       OBJECT-COMPUTER.  xyz.
       DATA DIVISION.
       WORKING-STORAGE SECTION.


      * EMBEDDED COBOL (file "DML062.PCO")  

      ****************************************************************
      *                                                              
      *                 COMMENT SECTION                              
      *                                                              
      * DATE 1989/07/21 EMBEDDED 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.
      *                                                              
      * DML062.PCO                                                    
      * WRITTEN BY: SUN DAJUN                                        
      * TRANSLATED AUTOMATICALLY FROM EMBEDDED C BY CHRIS SCHANZLE
      *                                                              
      *     THIS ROUTINE TESTS COMMIT AND VIEW                       
      *                                                              
      * REFERENCES                                                   
      *       AMERICAN NATIONAL STANDARD database language - SQL     
      *                         X3.135-1989                          
      *                                                              
      *                SECTION 8.2  <commit statement>               
      *                SECTION 6.9  <view definition>                
      *                                                              
      ****************************************************************



           EXEC SQL BEGIN DECLARE SECTION END-EXEC
       01  test1 PIC  X(20).
       01  HOURS1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
       01  count2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
       01  count3 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
       01  count4 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
       01  count5 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
       01  count1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
       01  SECND1 PIC  X(1).
       01  SECND2 PIC  X(1).
       01  PNUM1 PIC  X(3).
       01  EMPN1 PIC  X(3).
       01  EMPN2 PIC  X(3).
       01  uid PIC X(18).
       01  uidx PIC X(18).
           EXEC SQL END DECLARE SECTION END-EXEC
       01  SQLCODE PIC S9(9) COMP.
       01  errcnt PIC S9(4) DISPLAY SIGN LEADING SEPARATE.
       01  i PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
       01  flag PIC S9(9) DISPLAY SIGN LEADING SEPARATE.


       01  SQL-COD PIC S9(9) 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
           EXEC SQL SELECT 
                 USER INTO :uidx FROM HU.ECCO END-EXEC
           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, Embedded COBOL, dml062.pco"
           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 TEST0274 *******************
      *COMMIT AND ROLLBACK across schemas                   


           DISPLAY " COMMIT & ROLLBACK across schemas "
           DISPLAY "Reference X3.135-1989 section 8.2 General Rules "
           DISPLAY " ------------------------------------------ "
           DISPLAY " TEST0274 "
           DISPLAY " Reference 8.2 General Rules )"
           DISPLAY " - - - - - - - - - - - - - - - - - - - - - "
           DISPLAY  " "
           DISPLAY " DELETE FROM SULLIVAN1.AUTH_TABLE;"
           DISPLAY  " "
           DISPLAY " INSERT INTO SULLIVAN1.AUTH_TABLE"
           DISPLAY " VALUES (10,'A');"
           DISPLAY  " "
           DISPLAY " INSERT INTO SULLIVAN1.AUTH_TABLE"
           DISPLAY " VALUES (100,'B');"
           DISPLAY  " "
           DISPLAY " DELETE FROM AA;"
           DISPLAY  " "
           DISPLAY " INSERT INTO AA"
           DISPLAY " VALUES ('In God we trust');"
           DISPLAY  " "
           DISPLAY " COMMIT WORK;"
           DISPLAY  " "
           DISPLAY " DELETE FROM SULLIVAN1.AUTH_TABLE;"
           DISPLAY  " "
           DISPLAY " DELETE FROM AA;"
           DISPLAY " ROLLBACK WORK;"
           DISPLAY  " "
           DISPLAY " SELECT COUNT(*) INTO :count1 FROM
      -    " SULLIVAN1.AUTH_TABLE;"
           DISPLAY  " "
           COMPUTE count1 = -1
           EXEC SQL DELETE FROM SULLIVAN1.AUTH_TABLE END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL INSERT INTO SULLIVAN1.AUTH_TABLE
             VALUES (10,'A') END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL INSERT INTO SULLIVAN1.AUTH_TABLE
             VALUES (100,'B') END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL DELETE FROM AA END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL INSERT INTO AA
             VALUES ('In God we trust') END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL COMMIT WORK END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL DELETE FROM SULLIVAN1.AUTH_TABLE END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL DELETE FROM AA END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL ROLLBACK WORK END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL SELECT COUNT(*) INTO :count1 FROM
             SULLIVAN1.AUTH_TABLE END-EXEC
           MOVE SQLCODE TO SQL-COD
           MOVE "NV" TO test1
           EXEC SQL SELECT CHARTEST INTO :test1 FROM AA END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL DELETE FROM AA END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL DELETE FROM SULLIVAN1.AUTH_TABLE END-EXEC
           MOVE SQLCODE TO SQL-COD
           DISPLAY " The correct answer is :"
           DISPLAY " count1 = 2"
           DISPLAY " test1 = In God we trust"
           DISPLAY " Your answer is :"
           DISPLAY " count1 = ", count1
           DISPLAY " test1 =", test1


           if (count1  =  2  AND  test1   =   "In God we trust") then
             DISPLAY " *** pass *** "
             EXEC SQL INSERT INTO TESTREPORT
               VALUES('0274','pass','PCO') END-EXEC
             MOVE SQLCODE TO SQL-COD
           else
             DISPLAY " dml062.pco *** fail *** "
             EXEC SQL INSERT INTO TESTREPORT
               VALUES('0274','fail','PCO') END-EXEC
             ADD 1 TO errcnt
             MOVE SQLCODE TO SQL-COD
           END-IF
           DISPLAY "==============================================="

           EXEC SQL COMMIT WORK END-EXEC
           MOVE SQLCODE TO SQL-COD

      ******************** END TEST0274 *******************




      ******************** BEGIN TEST0275 *******************
      *COMMIT and ROLLBACK of multiple cursors              

           DISPLAY " COMMIT & ROLLBACK of multiple cursors "
           DISPLAY "Reference X3.135-1989 section 8.2 General Rules "
           DISPLAY " ------------------------------------------ "
           DISPLAY " TEST0275 "
           DISPLAY " Reference 8.2 General Rules )"
           DISPLAY " - - - - - - - - - - - - - - - - - - - - - "
           DISPLAY  " "
           DISPLAY " DECLARE CHICHI CURSOR FOR SELECT EMPNUM FROM
      -    " STAFF1"
           DISPLAY " WHERE EMPNUM = 'E3';"
           DISPLAY  " "
           DISPLAY " DECLARE MAC CURSOR FOR SELECT EMPNUM FROM
      -    " WORKS1"
           DISPLAY " WHERE EMPNUM = 'E3';"
           DISPLAY  " "
           DISPLAY " DECLARE KING CURSOR FOR SELECT PNUM FROM
      -    " PROJ1"
           DISPLAY " WHERE PNUM = 'P2';"
           DISPLAY  " "
           DISPLAY " UPDATE STAFF1"
           DISPLAY " SET EMPNUM = 'E9'"
           DISPLAY " WHERE CURRENT OF CHICHI;"
           DISPLAY  " "
           DISPLAY " UPDATE WORKS1"
           DISPLAY " SET EMPNUM = 'E9', PNUM = 'P9'"
           DISPLAY " WHERE CURRENT OF MAC;"
           DISPLAY  " "
           DISPLAY " UPDATE PROJ1"
           DISPLAY " SET PNUM = 'P9'"
           DISPLAY " WHERE CURRENT OF KING;"
           DISPLAY  " "
           DISPLAY " COMMIT WORK;"
           DISPLAY  " "
           DISPLAY " SELECT COUNT(*) INTO :count1 FROM "
           DISPLAY " STAFF1,WORKS1,PROJ1"
           DISPLAY " WHERE STAFF1.EMPNUM = 'E9' AND"
           DISPLAY " STAFF1.EMPNUM = WORKS1.EMPNUM AND"
           DISPLAY " PROJ1.PNUM = WORKS1.PNUM;"
           EXEC SQL DELETE FROM STAFF1 END-EXEC
           MOVE SQLCODE TO SQL-COD
           DISPLAY  " "
           COMPUTE count1 = -1
           EXEC SQL INSERT INTO STAFF1
             SELECT * FROM STAFF END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL INSERT INTO WORKS1
             SELECT * FROM WORKS END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL INSERT INTO PROJ1
             SELECT * FROM PROJ END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL DECLARE CHICHI CURSOR FOR SELECT EMPNUM FROM STAFF1
             WHERE EMPNUM = 'E3' END-EXEC
      *FOR UPDATE OF EMPNUM;
           EXEC SQL DECLARE MAC CURSOR FOR SELECT EMPNUM FROM WORKS1
             WHERE EMPNUM = 'E3' END-EXEC
      *FOR UPDATE  OF EMPNUM, PNUM;
           EXEC SQL DECLARE KING CURSOR FOR SELECT PNUM FROM PROJ1
             WHERE PNUM = 'P2' END-EXEC
      *FOR UPDATE OF PNUM;
           EXEC SQL OPEN CHICHI END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL OPEN MAC END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL OPEN KING END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL FETCH CHICHI INTO :EMPN1 END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL FETCH MAC INTO :EMPN2 END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL FETCH KING INTO :PNUM1 END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL UPDATE STAFF1
             SET EMPNUM = 'E9'
             WHERE CURRENT OF CHICHI END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL UPDATE WORKS1
             SET EMPNUM = 'E9', PNUM = 'P9'
             WHERE CURRENT OF MAC END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL UPDATE PROJ1
             SET PNUM = 'P9'
             WHERE CURRENT OF KING END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL COMMIT WORK END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL SELECT COUNT(*) INTO :count1 FROM 
             STAFF1,WORKS1,PROJ1
             WHERE STAFF1.EMPNUM = 'E9' AND
             STAFF1.EMPNUM = WORKS1.EMPNUM AND
             PROJ1.PNUM = WORKS1.PNUM END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL DELETE FROM STAFF1 END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL DELETE FROM PROJ1 END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL DELETE FROM WORKS1 END-EXEC
           MOVE SQLCODE TO SQL-COD
           DISPLAY " The correct answer is :"
           DISPLAY " count1 = 1"
           DISPLAY " Your answer is :"
           DISPLAY " count1 = ", count1


           if (count1  =  1) then
             DISPLAY " *** pass *** "
             EXEC SQL INSERT INTO TESTREPORT
               VALUES('0275','pass','PCO') END-EXEC
             MOVE SQLCODE TO SQL-COD
           else
             DISPLAY " dml062.pco *** fail *** "
             EXEC SQL INSERT INTO TESTREPORT
               VALUES('0275','fail','PCO') END-EXEC
             ADD 1 TO errcnt
             MOVE SQLCODE TO SQL-COD
           END-IF
           DISPLAY "==============================================="

           EXEC SQL COMMIT WORK END-EXEC
           MOVE SQLCODE TO SQL-COD

      ******************** END TEST0275 *******************




      ******************** BEGIN TEST0276 *******************
      *View across schemas                                  

           DISPLAY " VIEW across schemas "
           DISPLAY "Reference X3.135-1989 section 6.9 General Rules "
           DISPLAY " ------------------------------------------ "
           DISPLAY " TEST0276 "
           DISPLAY " Reference 6.9 General Rules )"
           DISPLAY " - - - - - - - - - - - - - - - - - - - - - "
           DISPLAY  " "
           DISPLAY " DELETE FROM SULLIVAN1.AUTH_TABLE;"
           DISPLAY  " "
           DISPLAY " INSERT INTO SULLIVAN1.AUTH_TABLE"
           DISPLAY " VALUES (12,'A');"
           DISPLAY  " "
           DISPLAY " DECLARE PIGGY CURSOR FOR"
           DISPLAY " SELECT EMPNUM,SECOND2 FROM
      -    " SULLIVAN1.MUL_SCH"
           DISPLAY " ORDER BY EMPNUM;"
           DISPLAY  " "
           EXEC SQL DELETE FROM SULLIVAN1.AUTH_TABLE END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL INSERT INTO SULLIVAN1.AUTH_TABLE
             VALUES (12,'A') END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL DECLARE PIGGY CURSOR FOR
             SELECT EMPNUM,SECOND2 FROM SULLIVAN1.MUL_SCH
             ORDER BY EMPNUM END-EXEC
           EXEC SQL OPEN PIGGY END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL FETCH PIGGY INTO :EMPN1, :SECND1 END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL FETCH PIGGY INTO :EMPN2, :SECND2 END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL CLOSE PIGGY END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL ROLLBACK WORK END-EXEC
           MOVE SQLCODE TO SQL-COD
           DISPLAY " The correct answer is :"
           DISPLAY " EMPN1 = E1"
           DISPLAY " SECND1 = A"
           DISPLAY " EMPN2 = E4"
           DISPLAY " SECND2 = A"
           DISPLAY " Your answer is :"
           DISPLAY " EMPN1 = ", EMPN1
           DISPLAY " SECND1 = ", SECND1
           DISPLAY " EMPN2 = ", EMPN2
           DISPLAY " SECND2 = ", SECND2
           MOVE 0 TO flag
           if (EMPN1  NOT  =   "E1") then
             COMPUTE flag = flag + 1
           END-IF
           if (EMPN2  NOT  =   "E4") then
             COMPUTE flag = flag + 1
           END-IF

              if (SECND1  =  "A"  AND  SECND2  =  "A"  AND  flag  =
             0) then
             DISPLAY " *** pass *** "
             EXEC SQL INSERT INTO TESTREPORT
               VALUES('0276','pass','PCO') END-EXEC
             MOVE SQLCODE TO SQL-COD
           else
             DISPLAY " dml062.pco *** fail *** "
             EXEC SQL INSERT INTO TESTREPORT
               VALUES('0276','fail','PCO') END-EXEC
             ADD 1 TO errcnt
             MOVE SQLCODE TO SQL-COD
           END-IF
           DISPLAY "==============================================="

           EXEC SQL COMMIT WORK END-EXEC
           MOVE SQLCODE TO SQL-COD

      ******************** END TEST0276 *******************







      ******************** BEGIN TEST0279 *******************
      *IN is a 3-valued predicate, EXISTS is 2-valued        

           DISPLAY " IN is a 3-valued predicate, EXISTS is 2-valued"
           DISPLAY " Reference X3.135-1989 :"
           DISPLAY " section 5.13 General Rules "
           DISPLAY " section 5.17 General Rules "
           DISPLAY " ------------------------------------------ "
           DISPLAY " TEST0279 "
           DISPLAY " Reference 5.13 5.17 General Rules )"
           DISPLAY " - - - - - - - - - - - - - - - - - - - - - "
           DISPLAY  " "
           DISPLAY " UPDATE WORKS"
           DISPLAY " SET HOURS = NULL"
           DISPLAY " WHERE PNUM = 'P5' OR EMPNUM = 'E4';"
           DISPLAY  " "
           DISPLAY " SELECT COUNT(*) INTO :count1 FROM STAFF;"
           DISPLAY  " "
           DISPLAY " SELECT COUNT(*) INTO :count2 FROM STAFF"
           DISPLAY " WHERE 40 IN (SELECT HOURS FROM WORKS"
           DISPLAY " WHERE STAFF.EMPNUM =
      -    " WORKS.EMPNUM);"
           DISPLAY  " "
           DISPLAY " SELECT COUNT(*) INTO :count3 FROM STAFF"
           DISPLAY " WHERE 40 NOT IN (SELECT HOURS FROM WORKS"
           DISPLAY " WHERE STAFF.EMPNUM =
      -    " WORKS.EMPNUM);"
           DISPLAY  " "
           DISPLAY " SELECT COUNT(*) INTO :count4 FROM STAFF"
           DISPLAY " WHERE EXISTS (SELECT * FROM WORKS"
           DISPLAY " WHERE HOURS = 40 AND STAFF.EMPNUM =
      -    " WORKS.EMPNUM);"
           DISPLAY  " "
           DISPLAY " SELECT COUNT(*) INTO :count5 FROM STAFF"
           DISPLAY " WHERE NOT EXISTS (SELECT * FROM WORKS"
           DISPLAY " WHERE HOURS = 40 AND STAFF.EMPNUM =
      -    " WORKS.EMPNUM);"
           DISPLAY  " "
           COMPUTE count1 = -1
           COMPUTE count2 = -1
           COMPUTE count3 = -1
           COMPUTE count4 = -1
           COMPUTE count5 = -1
           EXEC SQL UPDATE WORKS
             SET HOURS = NULL
             WHERE PNUM = 'P5' OR EMPNUM = 'E4' END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL SELECT COUNT(*) INTO :count1 FROM STAFF END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL SELECT COUNT(*) INTO :count2 FROM STAFF
             WHERE 40 IN (SELECT HOURS FROM WORKS
             WHERE STAFF.EMPNUM = WORKS.EMPNUM) END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL SELECT COUNT(*) INTO :count3 FROM STAFF
             WHERE 40 NOT IN (SELECT HOURS FROM WORKS
             WHERE STAFF.EMPNUM = WORKS.EMPNUM) END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL SELECT COUNT(*) INTO :count4 FROM STAFF
             WHERE EXISTS (SELECT * FROM WORKS
             WHERE HOURS = 40 AND STAFF.EMPNUM = WORKS.EMPNUM) END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL SELECT COUNT(*) INTO :count5 FROM STAFF
             WHERE NOT EXISTS (SELECT * FROM WORKS
             WHERE HOURS = 40 AND STAFF.EMPNUM = WORKS.EMPNUM) END-EXEC
           MOVE SQLCODE TO SQL-COD
           EXEC SQL ROLLBACK WORK END-EXEC
           MOVE SQLCODE TO SQL-COD
           DISPLAY " The correct answer is :"
           DISPLAY " count1 = 5"
           DISPLAY " count2 = 2"
           DISPLAY " count3 = 2"
           DISPLAY " count4 = 2"
           DISPLAY " count5 = 3"
           DISPLAY " Your answer is :"
           DISPLAY " count1 = ", count1
           DISPLAY " count2 = ", count2
           DISPLAY " count3 = ", count3
           DISPLAY " count4 = ", count4
           DISPLAY " count5 = ", count5
           MOVE 0 TO flag
           if (count1  NOT =  5  OR  count2  NOT =  2  OR  count3  NOT =
              2) then
             MOVE 1 TO flag
           END-IF
           if (count4  NOT =  2  OR  count5  NOT =  3) then
             MOVE 1 TO flag
           END-IF
           if (flag  =  0) then
             DISPLAY " *** pass *** "
             EXEC SQL INSERT INTO TESTREPORT
               VALUES('0279','pass','PCO') END-EXEC
             MOVE SQLCODE TO SQL-COD
           else
             DISPLAY " dml062.pco *** fail *** "
             EXEC SQL INSERT INTO TESTREPORT
               VALUES('0279','fail','PCO') END-EXEC
             ADD 1 TO errcnt
             MOVE SQLCODE TO SQL-COD
           END-IF
           DISPLAY "==============================================="

           EXEC SQL COMMIT WORK END-EXEC
           MOVE SQLCODE TO SQL-COD

      ******************** END TEST0279 *******************





      **** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0
           STOP RUN.

      *    ****  Procedures for PERFORM statements