| 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: distance_3D.pvs Sprache: Unknown |
|
||||||
|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML115. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. xyz. OBJECT-COMPUTER. xyz. DATA DIVISION. WORKING-STORAGE SECTION. * Standard COBOL (file "DML115.SCO") calling SQL * procedures in file "DML115.MCO". **************************************************************** * * COMMENT SECTION * * DATE 1994/4/8 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. * * DML115.SCO * WRITTEN BY: David W. Flater * TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE * * This routine tests FIPS features 13, 14, 15, and 16. * This is the dynamic version of DML114.PC. * * REFERENCES * FIPS PUB 127-2 14.1 Transitional SQL * ANSI SQL-1992 * **************************************************************** * 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 int1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 int2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 ch1 PIC X(3). 01 ch2 PIC X(20). 01 ch3 PIC X(15). 01 ch4 PIC X(3). 01 dstmt PIC X(50). 01 longst PIC X(240). 01 indic1 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 ctr1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 ctr2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 ctr3 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 SQL-COD PIC S9(9) DISPLAY SIGN LEADING SEPARATE. PROCEDURE DIVISION. P0. MOVE "FLATER " TO uid CALL "AUTHID" USING uid MOVE "not logged in, not" TO uidx * EXEC SQL SELECT USER INTO :uidx FROM HU.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, dml115.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 TEST0636 ******************* MOVE 1 TO flag DISPLAY " TEST0636 " DISPLAY " Feature 13, grouped operations (dynamic)" DISPLAY "References:" DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature - " 13" DISPLAY " 7.3 LR.2.a" DISPLAY " 7.4 LR.2.a" DISPLAY " 7.9 LR.2.c" DISPLAY " 7.11 LR.2.a" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB3" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'CREATE VIEW WORKWEEK AS' || * ' SELECT EMPNUM, HOURS FROM HU.WORKS' || * ' GROUP BY HOURS, EMPNUM' * ); CALL "SUB4" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB5" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB6" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" * EXEC SQL EXECUTE IMMEDIATE :longst; CALL "SUB7" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB8" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Removal of leveling rule 2a of Subclause 7.3 *Removal of leveling rule 2c of Subclause 7.9 *Removal of leveling rule 2b of Subclause 13.5 * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB9" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT EMPNUM, SUM (HOURS) FROM WORKWEEK' || * ' WHERE HOURS > 20 GROUP BY EMPNUM' || * ' HAVING EMPNUM = ''E1''' * ); CALL "SUB10" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB11" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11511 FROM :longst;" * EXEC SQL PREPARE S11511 FROM :longst; CALL "SUB12" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11511 CURSOR FOR S11511;" * EXEC SQL DECLARE C11511 CURSOR FOR S11511 END-EXEC DISPLAY " " DISPLAY "OPEN C11511;" * EXEC SQL OPEN C11511; CALL "SUB13" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "xyz" TO ch1 DISPLAY "FETCH C11511 INTO :ch1, :int1;" * EXEC SQL FETCH C11511 INTO :ch1, :int1; CALL "SUB14" USING SQLCODE SQLSTATE ch1 int1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "ch1 should be 'E1 '; its value is '", ch1 "'" DISPLAY "int1 should be 120; its value is ", int1 if (ch1 NOT = "E1 " OR int1 NOT = 120) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11511;" * EXEC SQL CLOSE C11511; CALL "SUB15" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *This is more meaningful -- how many people work too hard? DISPLAY "dstmt=""SELECT COUNT(*) FROM WORKWEEK WHERE HOURS - " > 40""" MOVE "SELECT COUNT(*) FROM WORKWEEK WHERE HOURS - " > 40 " TO dstmt DISPLAY "PREPARE S11512 FROM :dstmt;" * EXEC SQL PREPARE S11512 FROM :dstmt; CALL "SUB16" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11512 CURSOR FOR S11512;" * EXEC SQL DECLARE C11512 CURSOR FOR S11512 END-EXEC DISPLAY " " DISPLAY "OPEN C11512;" * EXEC SQL OPEN C11512; CALL "SUB17" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11512 INTO :int1;" * EXEC SQL FETCH C11512 INTO :int1; CALL "SUB18" USING SQLCODE SQLSTATE int1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 3; its value is ", int1 if (int1 NOT = 3) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11512;" * EXEC SQL CLOSE C11512; CALL "SUB19" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Removal of leveling rule 2a of Subclause 7.4 * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB20" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT EMPNAME FROM HU.STAFF, WORKWEEK' || * ' WHERE HU.STAFF.EMPNUM = WORKWEEK.EMPNUM' || * ' AND HOURS = 12' * ); CALL "SUB21" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB22" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11513 FROM :longst;" * EXEC SQL PREPARE S11513 FROM :longst; CALL "SUB23" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11513 CURSOR FOR S11513;" * EXEC SQL DECLARE C11513 CURSOR FOR S11513 END-EXEC DISPLAY " " DISPLAY "OPEN C11513;" * EXEC SQL OPEN C11513; CALL "SUB24" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "aaaaaaaaaaaaaaaaaaaa" TO ch2 DISPLAY "FETCH C11513 INTO :ch2;" * EXEC SQL FETCH C11513 INTO :ch2; CALL "SUB25" USING SQLCODE SQLSTATE ch2 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "ch2 should be Alice; its value is ", ch2 if (ch2 NOT = "Alice") then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11513;" * EXEC SQL CLOSE C11513; CALL "SUB26" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Removal of leveling rule 2c of Subclause 7.9 * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB27" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT COUNT(*), MAX(EMPNUM), MIN(EMPNUM), AVG(HOURS)' * || * ' FROM WORKWEEK' * ); CALL "SUB28" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB29" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11514 FROM :longst;" * EXEC SQL PREPARE S11514 FROM :longst; CALL "SUB30" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11514 CURSOR FOR S11514;" * EXEC SQL DECLARE C11514 CURSOR FOR S11514 END-EXEC DISPLAY " " DISPLAY "OPEN C11514;" * EXEC SQL OPEN C11514; CALL "SUB31" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 COMPUTE int2 = -1 MOVE "xxx" TO ch1 MOVE "xxx" TO ch4 DISPLAY "FETCH C11514 INTO :int1, :ch1, :ch4, :int2;" * EXEC SQL FETCH C11514 INTO :int1, :ch1, :ch4, :int2 * ; CALL "SUB32" USING SQLCODE SQLSTATE int1 ch1 ch4 int2 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 10; its value is ", int1 DISPLAY "ch1 should be 'E4 '; its value is '", ch1 "'" DISPLAY "ch4 should be 'E1 '; its value is '", ch4 "'" DISPLAY "int2 should be 43; its value is ", int2 if (int1 NOT = 10 OR ch1 NOT = "E4 ") then MOVE 0 TO flag END-IF if (ch4 NOT = "E1 " OR int2 NOT = 43) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11514;" * EXEC SQL CLOSE C11514; CALL "SUB33" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Removal of leveling rule 2a of Subclause 7.11 * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB34" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT EMPNAME FROM HU.STAFF WHERE EMPNUM =' || * ' (SELECT EMPNUM FROM WORKWEEK' || * ' WHERE HOURS = 12)' * ); CALL "SUB35" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB36" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11515 FROM :longst;" * EXEC SQL PREPARE S11515 FROM :longst; CALL "SUB37" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11515 CURSOR FOR S11515;" * EXEC SQL DECLARE C11515 CURSOR FOR S11515 END-EXEC DISPLAY " " DISPLAY "OPEN C11515;" * EXEC SQL OPEN C11515; CALL "SUB38" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "aaaaaaaaaaaaaaaaaaaa" TO ch2 DISPLAY "FETCH C11515 INTO :ch2;" * EXEC SQL FETCH C11515 INTO :ch2; CALL "SUB39" USING SQLCODE SQLSTATE ch2 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "ch2 should be Alice; its value is ", ch2 if (ch2 NOT = "Alice") then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11515;" * EXEC SQL CLOSE C11515; CALL "SUB40" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB41" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT EMPNAME FROM HU.STAFF WHERE EMPNUM =' || * ' (SELECT EMPNUM FROM HU.WORKS' || * ' GROUP BY EMPNUM, HOURS' || * ' HAVING HOURS = 12)' * ); CALL "SUB42" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB43" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11516 FROM :longst;" * EXEC SQL PREPARE S11516 FROM :longst; CALL "SUB44" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11516 CURSOR FOR S11516;" * EXEC SQL DECLARE C11516 CURSOR FOR S11516 END-EXEC DISPLAY " " DISPLAY "OPEN C11516;" * EXEC SQL OPEN C11516; CALL "SUB45" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "aaaaaaaaaaaaaaaaaaaa" TO ch2 DISPLAY "FETCH C11516 INTO :ch2;" * EXEC SQL FETCH C11516 INTO :ch2; CALL "SUB46" USING SQLCODE SQLSTATE ch2 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "ch2 should be Alice; its value is ", ch2 if (ch2 NOT = "Alice") then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11516;" * EXEC SQL CLOSE C11516; CALL "SUB47" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Generic test case MOVE 3 TO ctr1 MOVE 1 TO ctr2 MOVE 4 TO ctr3 DISPLAY "The following cursor scan should return 3 three - " times" DISPLAY "and 1 once, then we should get NO DATA (02000)." DISPLAY "dstmt=""SELECT COUNT(*) FROM WORKWEEK GROUP BY - " HOURS""" MOVE "SELECT COUNT(*) FROM WORKWEEK GROUP BY - " HOURS " TO dstmt DISPLAY "PREPARE S11517 FROM :dstmt;" * EXEC SQL PREPARE S11517 FROM :dstmt; CALL "SUB48" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11517 CURSOR FOR S11517;" * EXEC SQL DECLARE C11517 CURSOR FOR S11517 END-EXEC DISPLAY " " DISPLAY "OPEN C11517;" * EXEC SQL OPEN C11517; CALL "SUB49" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " . P100. COMPUTE int1 = -1 DISPLAY "FETCH C11517 INTO :int1;" * EXEC SQL FETCH C11517 INTO :int1; CALL "SUB50" USING SQLCODE SQLSTATE int1 MOVE SQLCODE TO SQL-COD DISPLAY "SQLCODE is ", SQL-COD DISPLAY "SQLSTATE is ", SQLSTATE if (SQLCODE NOT = 0) then GO TO P101 END-IF DISPLAY "int1 is ", int1 COMPUTE ctr3 = ctr3 - 1 if (int1 = 1) then COMPUTE ctr2 = ctr2 - 1 END-IF if (int1 = 3) then COMPUTE ctr1 = ctr1 - 1 END-IF GO TO P100 . P101. if (ctr1 NOT = 0 OR ctr2 NOT = 0 OR ctr3 NOT = 0) then MOVE 0 TO flag END-IF 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 " " *Let the commit close the cursor DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB51" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""DROP VIEW WORKWEEK CASCADE""" MOVE "DROP VIEW WORKWEEK CASCADE " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" * EXEC SQL EXECUTE IMMEDIATE :dstmt; CALL "SUB52" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB53" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0636','pass','MCO'); CALL "SUB54" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY " dml115.sco *** fail *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0636','fail','MCO'); CALL "SUB55" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB56" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST0636 ******************** ******************** BEGIN TEST0638 ******************* MOVE 1 TO flag DISPLAY " TEST0638 " DISPLAY "Feature 14, Qualified * in select list (dynamic)" DISPLAY "References:" DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature - " 14" DISPLAY " 7.9 LR.2.b" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB57" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'CREATE VIEW QUALSTAR AS' || * ' SELECT HU.STAFF.*, HOURS FROM HU.STAFF, HU.WORKS' || * ' WHERE HU.STAFF.EMPNUM = HU.WORKS.EMPNUM' * ); CALL "SUB58" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB59" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB60" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" * EXEC SQL EXECUTE IMMEDIATE :longst; CALL "SUB61" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB62" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB63" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'CREATE VIEW CORRQUALSTAR AS' || * ' SELECT BLAH.*, HOURS FROM HU.STAFF BLAH, HU.WORKS' || * ' WHERE BLAH.EMPNUM = HU.WORKS.EMPNUM' * ); CALL "SUB64" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB65" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB66" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" * EXEC SQL EXECUTE IMMEDIATE :longst; CALL "SUB67" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB68" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB69" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'CREATE VIEW SUBQ2 AS' || * ' SELECT DISTINCT * FROM QUALSTAR' * ); CALL "SUB70" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB71" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB72" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" * EXEC SQL EXECUTE IMMEDIATE :longst; CALL "SUB73" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB74" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB75" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'CREATE VIEW CORRSUBQ2 AS' || * ' SELECT DISTINCT * FROM CORRQUALSTAR' * ); CALL "SUB76" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB77" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB78" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" * EXEC SQL EXECUTE IMMEDIATE :longst; CALL "SUB79" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB80" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT COUNT(*) FROM QUALSTAR""" MOVE "SELECT COUNT(*) FROM QUALSTAR - " " TO dstmt DISPLAY "PREPARE S11521 FROM :dstmt;" * EXEC SQL PREPARE S11521 FROM :dstmt; CALL "SUB81" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11521 CURSOR FOR S11521;" * EXEC SQL DECLARE C11521 CURSOR FOR S11521 END-EXEC DISPLAY " " DISPLAY "OPEN C11521;" * EXEC SQL OPEN C11521; CALL "SUB82" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11521 INTO :int1;" * EXEC SQL FETCH C11521 INTO :int1; CALL "SUB83" USING SQLCODE SQLSTATE int1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 12; its value is ", int1 if (int1 NOT = 12) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11521;" * EXEC SQL CLOSE C11521; CALL "SUB84" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT COUNT(*) FROM SUBQ2""" MOVE "SELECT COUNT(*) FROM SUBQ2 - " " TO dstmt DISPLAY "PREPARE S11522 FROM :dstmt;" * EXEC SQL PREPARE S11522 FROM :dstmt; CALL "SUB85" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11522 CURSOR FOR S11522;" * EXEC SQL DECLARE C11522 CURSOR FOR S11522 END-EXEC DISPLAY " " DISPLAY "OPEN C11522;" * EXEC SQL OPEN C11522; CALL "SUB86" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11522 INTO :int1;" * EXEC SQL FETCH C11522 INTO :int1; CALL "SUB87" USING SQLCODE SQLSTATE int1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 10; its value is ", int1 if (int1 NOT = 10) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11522;" * EXEC SQL CLOSE C11522; CALL "SUB88" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB89" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT EMPNUM, GRADE, CITY, HOURS' || * ' FROM QUALSTAR WHERE EMPNAME = ''Carmen''' * ); CALL "SUB90" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB91" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11523 FROM :longst;" * EXEC SQL PREPARE S11523 FROM :longst; CALL "SUB92" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11523 CURSOR FOR S11523;" * EXEC SQL DECLARE C11523 CURSOR FOR S11523 END-EXEC DISPLAY " " DISPLAY "OPEN C11523;" * EXEC SQL OPEN C11523; CALL "SUB93" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "xxx" TO ch1 MOVE "xxxxxxxxxxxxxxx" TO ch3 COMPUTE int1 = -1 COMPUTE int2 = -1 DISPLAY "FETCH C11523 INTO :ch1, :int1, :ch3, :int2;" * EXEC SQL FETCH C11523 INTO :ch1, :int1, :ch3, :int2 * ; CALL "SUB94" USING SQLCODE SQLSTATE ch1 int1 ch3 int2 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "ch1 should be 'E3 '; its value is '", ch1 "'" if (ch1 NOT = "E3 ") then MOVE 0 TO flag END-IF DISPLAY "int1 should be 13; its value is ", int1 if (int1 NOT = 13) then MOVE 0 TO flag END-IF DISPLAY "ch3 should be 'Vienna '; its value is '", ch3 "'" if (ch3 NOT = "Vienna ") then MOVE 0 TO flag END-IF DISPLAY "int2 should be 20; its value is ", int2 if (int2 NOT = 20) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11523;" * EXEC SQL CLOSE C11523; CALL "SUB95" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB96" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT HU.STAFF.*, HOURS FROM HU.STAFF, HU.WORKS' || * ' WHERE HU.STAFF.EMPNUM = HU.WORKS.EMPNUM' || * ' AND EMPNAME > ''Betty'' ORDER BY EMPNAME' * ); CALL "SUB97" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB98" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE SJOANS FROM :longst;" * EXEC SQL PREPARE SJOANS FROM :longst; CALL "SUB99" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE CJOANS CURSOR FOR SJOANS;" * EXEC SQL DECLARE CJOANS CURSOR FOR SJOANS END-EXEC DISPLAY "OPEN CJOANS;" * EXEC SQL OPEN CJOANS; CALL "SUB100" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "xxx" TO ch1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO ch2 MOVE "xxxxxxxxxxxxxxx" TO ch3 COMPUTE int1 = -1 COMPUTE int2 = -1 DISPLAY "FETCH CJOANS INTO :ch1, :ch2, :int1, :ch3, :int2;" * EXEC SQL FETCH CJOANS INTO :ch1, :ch2, :int1, :ch3, :int2 * ; CALL "SUB101" USING SQLCODE SQLSTATE ch1 ch2 int1 ch3 int2 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "ch1 should be 'E3 '; its value is '", ch1 "'" if (ch1 NOT = "E3 ") then MOVE 0 TO flag END-IF DISPLAY "ch2 should be 'Carmen '; its value is - " '", ch2 "'" if (ch2 NOT = "Carmen ") then MOVE 0 TO flag END-IF DISPLAY "int1 should be 13; its value is ", int1 if (int1 NOT = 13) then MOVE 0 TO flag END-IF DISPLAY "ch3 should be 'Vienna '; its value is '", ch3 "'" if (ch3 NOT = "Vienna ") then MOVE 0 TO flag END-IF DISPLAY "int2 should be 20; its value is ", int2 if (int2 NOT = 20) then MOVE 0 TO flag END-IF DISPLAY "CLOSE CJOANS;" * EXEC SQL CLOSE CJOANS; CALL "SUB102" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT COUNT(*) FROM CORRQUALSTAR""" MOVE "SELECT COUNT(*) FROM CORRQUALSTAR - " " TO dstmt DISPLAY "PREPARE S11524 FROM :dstmt;" * EXEC SQL PREPARE S11524 FROM :dstmt; CALL "SUB103" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11524 CURSOR FOR S11524;" * EXEC SQL DECLARE C11524 CURSOR FOR S11524 END-EXEC DISPLAY " " DISPLAY "OPEN C11524;" * EXEC SQL OPEN C11524; CALL "SUB104" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11524 INTO :int1;" * EXEC SQL FETCH C11524 INTO :int1; CALL "SUB105" USING SQLCODE SQLSTATE int1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 12; its value is ", int1 if (int1 NOT = 12) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11524;" * EXEC SQL CLOSE C11524; CALL "SUB106" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT COUNT(*) FROM CORRSUBQ2""" MOVE "SELECT COUNT(*) FROM CORRSUBQ2 - " " TO dstmt DISPLAY "PREPARE S11525 FROM :dstmt;" * EXEC SQL PREPARE S11525 FROM :dstmt; CALL "SUB107" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11525 CURSOR FOR S11525;" * EXEC SQL DECLARE C11525 CURSOR FOR S11525 END-EXEC DISPLAY " " DISPLAY "OPEN C11525;" * EXEC SQL OPEN C11525; CALL "SUB108" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11525 INTO :int1;" * EXEC SQL FETCH C11525 INTO :int1; CALL "SUB109" USING SQLCODE SQLSTATE int1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 10; its value is ", int1 if (int1 NOT = 10) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11525;" * EXEC SQL CLOSE C11525; CALL "SUB110" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB111" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT EMPNUM, GRADE, CITY, HOURS' || * ' FROM CORRQUALSTAR WHERE EMPNAME = ''Carmen''' * ); CALL "SUB112" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB113" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11526 FROM :longst;" * EXEC SQL PREPARE S11526 FROM :longst; CALL "SUB114" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11526 CURSOR FOR S11526;" * EXEC SQL DECLARE C11526 CURSOR FOR S11526 END-EXEC DISPLAY " " DISPLAY "OPEN C11526;" * EXEC SQL OPEN C11526; CALL "SUB115" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "xxx" TO ch1 MOVE "xxxxxxxxxxxxxxx" TO ch3 COMPUTE int1 = -1 COMPUTE int2 = -1 DISPLAY "FETCH C11526 INTO :ch1, :int1, :ch3, :int2;" * EXEC SQL FETCH C11526 INTO :ch1, :int1, :ch3, :int2 * ; CALL "SUB116" USING SQLCODE SQLSTATE ch1 int1 ch3 int2 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "ch1 should be 'E3 '; its value is '", ch1 "'" if (ch1 NOT = "E3 ") then MOVE 0 TO flag END-IF DISPLAY "int1 should be 13; its value is ", int1 if (int1 NOT = 13) then MOVE 0 TO flag END-IF DISPLAY "ch3 should be 'Vienna '; its value is '", ch3 "'" if (ch3 NOT = "Vienna ") then MOVE 0 TO flag END-IF DISPLAY "int2 should be 20; its value is ", int2 if (int2 NOT = 20) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11526;" * EXEC SQL CLOSE C11526; CALL "SUB117" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB118" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""DROP VIEW QUALSTAR CASCADE""" MOVE "DROP VIEW QUALSTAR CASCADE " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" * EXEC SQL EXECUTE IMMEDIATE :dstmt; CALL "SUB119" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB120" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""DROP VIEW CORRQUALSTAR CASCADE""" MOVE "DROP VIEW CORRQUALSTAR CASCADE " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" * EXEC SQL EXECUTE IMMEDIATE :dstmt; CALL "SUB121" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB122" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0638','pass','MCO'); CALL "SUB123" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY " dml115.sco *** fail *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0638','fail','MCO'); CALL "SUB124" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB125" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST0638 ******************** ******************** BEGIN TEST0640 ******************* MOVE 1 TO flag DISPLAY " TEST0640 " DISPLAY " Feature 15, Lowercase Identifiers (dynamic)" DISPLAY "References:" DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature - " 15" DISPLAY " 5.2 LR.2.b" DISPLAY " 5.2 SR.15 -- Lowercase keywords are Entry SQL" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB126" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'Create view Staff (Empnum, empname, Grade, City) as' || * ' select empnum, EMPNAME, Grade, cItY from Hu.Staff' * ); CALL "SUB127" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB128" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB129" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" * EXEC SQL EXECUTE IMMEDIATE :longst; CALL "SUB130" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "exec sql commit work;" * exec sql commit work; CALL "SUB131" USING SQLCODE SQLSTATE PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT Count(*) FROM STAFF""" MOVE "SELECT Count(*) FROM STAFF - " " TO dstmt DISPLAY "Prepare s11531 FROM :dstmt;" * EXEC SQL Prepare s11531 FROM :dstmt; CALL "SUB132" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXEC SQL Declare c11531 cursor for s11531;" * Exec SQL Declare c11531 cursor for s11531; DISPLAY " " DISPLAY "OPEN C11531;" * EXEC SQL OPEN C11531; CALL "SUB134" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11531 INTO :int1;" * EXEC SQL FETCH C11531 INTO :int1; CALL "SUB135" USING SQLCODE SQLSTATE int1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 5; its value is ", int1 if (int1 NOT = 5) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11531;" * EXEC SQL CLOSE C11531; CALL "SUB136" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB137" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT EMPNUM, GRADE, CITY' || * ' FROM Flater.staff WHERE EMPNAME > ''Betty''' || * ' ORDER BY EMPNUM' * ); CALL "SUB138" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB139" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11532 FROM :longst;" * EXEC SQL PREPARE S11532 FROM :longst; CALL "SUB140" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE c11532 CURSOR FOR S11532;" * EXEC SQL DECLARE c11532 CURSOR FOR S11532 END-EXEC DISPLAY " " DISPLAY "OPEN C11532;" * EXEC SQL OPEN C11532; CALL "SUB141" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "xxx" TO ch1 MOVE "xxxxxxxxxxxxxxx" TO ch3 COMPUTE int1 = -1 DISPLAY "FETCH C11532 INTO :ch1, :int1, :ch3;" * EXEC SQL FETCH C11532 INTO :ch1, :int1, :ch3; CALL "SUB142" USING SQLCODE SQLSTATE ch1 int1 ch3 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "ch1 should be 'E3 '; its value is '", ch1 "'" if (ch1 NOT = "E3 ") then MOVE 0 TO flag END-IF DISPLAY "int1 should be 13; its value is ", int1 if (int1 NOT = 13) then MOVE 0 TO flag END-IF DISPLAY "ch3 should be 'Vienna '; its value is '", ch3 "'" if (ch3 NOT = "Vienna ") then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11532;" * EXEC SQL CLOSE C11532; CALL "SUB143" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB144" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""DROP VIEW STAFF CASCADE""" MOVE "DROP VIEW STAFF CASCADE " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" * EXEC SQL EXECUTE IMMEDIATE :dstmt; CALL "SUB145" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB146" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0640','pass','MCO'); CALL "SUB147" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY " dml115.sco *** fail *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0640','fail','MCO'); CALL "SUB148" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB149" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST0640 ******************** ******************** BEGIN TEST0642 ******************* MOVE 1 TO flag DISPLAY " TEST0642 " DISPLAY " Feature 16, PRIMARY KEY enhancement (dynamic)" DISPLAY "References:" DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature - " 16" DISPLAY " 11.7 LR.2.a" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB150" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'CREATE TABLE FEAT16 (' || * ' EMPNUM INT PRIMARY KEY,' || * ' PNUM INT UNIQUE)' * ); CALL "SUB151" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB152" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB153" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" * EXEC SQL EXECUTE IMMEDIATE :longst; CALL "SUB154" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB155" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""INSERT INTO FEAT16 VALUES (?, ?)""" MOVE "INSERT INTO FEAT16 VALUES (?, ?) " TO dstmt DISPLAY "PREPARE S11541 FROM :dstmt;" * EXEC SQL PREPARE S11541 FROM :dstmt; CALL "SUB156" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 1 TO int1 MOVE 10 TO int2 DISPLAY "int1 = ", int1 "; int2 = ", int2 DISPLAY "EXECUTE S11541 USING :int1, :int2;" * EXEC SQL EXECUTE S11541 USING :int1, :int2; CALL "SUB157" USING SQLCODE SQLSTATE int1 int2 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 2 TO int1 MOVE 20 TO int2 DISPLAY "int1 = ", int1 "; int2 = ", int2 DISPLAY "EXECUTE S11541 USING :int1, :int2;" * EXEC SQL EXECUTE S11541 USING :int1, :int2; CALL "SUB158" USING SQLCODE SQLSTATE int1 int2 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 1 TO int1 MOVE 30 TO int2 DISPLAY "int1 = ", int1 "; int2 = ", int2 DISPLAY "EXECUTE S11541 USING :int1, :int2;" * EXEC SQL EXECUTE S11541 USING :int1, :int2; CALL "SUB159" USING SQLCODE SQLSTATE int1 int2 MOVE SQLCODE TO SQL-COD DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD if (SQLCODE NOT < 0) then MOVE 0 TO flag END-IF DISPLAY "SQLSTATE should be 23000; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (NORMSQ NOT = "23000") then MOVE 0 TO flag END-IF if (NORMSQ = "23000" AND NORMSQ NOT = SQLSTATE) then DISPLAY "Valid implementation-defined SQLSTATE accepted." END-IF DISPLAY " " MOVE 3 TO int1 MOVE 20 TO int2 DISPLAY "int1 = ", int1 "; int2 = ", int2 DISPLAY "EXECUTE S11541 USING :int1, :int2;" * EXEC SQL EXECUTE S11541 USING :int1, :int2; CALL "SUB160" USING SQLCODE SQLSTATE int1 int2 MOVE SQLCODE TO SQL-COD DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD if (SQLCODE NOT < 0) then MOVE 0 TO flag END-IF DISPLAY "SQLSTATE should be 23000; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (NORMSQ NOT = "23000") then MOVE 0 TO flag END-IF if (NORMSQ = "23000" AND NORMSQ NOT = SQLSTATE) then DISPLAY "Valid implementation-defined SQLSTATE accepted." END-IF DISPLAY " " *Multiple nulls are OK -- See 8.9 GR.2 MOVE 3 TO int1 COMPUTE indic1 = -1 DISPLAY "int1 = ", int1 "; indic1 = -1" DISPLAY "EXECUTE S11541 USING :int1, :int2:indic1;" * EXEC SQL EXECUTE S11541 USING :int1, :int2:indic1; CALL "SUB161" USING SQLCODE SQLSTATE int1 int2 indic1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 4 TO int1 COMPUTE indic1 = -1 DISPLAY "int1 = ", int1 "; indic1 = -1" DISPLAY "EXECUTE S11541 USING :int1, :int2:indic1;" * EXEC SQL EXECUTE S11541 USING :int1, :int2:indic1; CALL "SUB162" USING SQLCODE SQLSTATE int1 int2 indic1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 5 TO int1 COMPUTE indic1 = -1 DISPLAY "int1 = ", int1 "; indic1 = -1" DISPLAY "EXECUTE S11541 USING :int1, :int2:indic1;" * EXEC SQL EXECUTE S11541 USING :int1, :int2:indic1; CALL "SUB163" USING SQLCODE SQLSTATE int1 int2 indic1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB164" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""DROP TABLE FEAT16 CASCADE""" MOVE "DROP TABLE FEAT16 CASCADE " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" * EXEC SQL EXECUTE IMMEDIATE :dstmt; CALL "SUB165" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB166" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0642','pass','MCO'); CALL "SUB167" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY " dml115.sco *** fail *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0642','fail','MCO'); CALL "SUB168" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB169" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST0642 ******************** **** 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.117 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 |
