|
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.106 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.
|
