|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML105.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. xyz.
OBJECT-COMPUTER. xyz.
DATA DIVISION.
WORKING-STORAGE SECTION.
* Standard COBOL (file "DML105.SCO") calling SQL
* procedures in file "DML105.MCO".
****************************************************************
*
* COMMENT SECTION
*
* DATE 1993/08/16 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.
*
* DML105.SCO
* WRITTEN BY: David W. Flater
* TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE
*
* This routine tests Fips Feature #4 for dynamic TSQL. Fips
* Feature #4 is "joined table."
*
* REFERENCES
* FIPS PUB 127-2 14.1 Transitional SQL
* ANSI SQL-1992 7.5 <joined table>
*
****************************************************************
* 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 ivar1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 ivar2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 cvar1 PIC X(5).
01 dstmt PIC X(50).
01 longst PIC X(240).
* 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 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, dml105.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 TEST0595 *******************
MOVE 1 TO flag
DISPLAY " TEST0595 "
DISPLAY " NATURAL JOIN (feature 4) (dynamic)"
DISPLAY "References:"
DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature
- " 4"
DISPLAY " ANSI SQL-1992 7.5 "
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "dstmt=""CREATE TABLE GROUPS1 (EMPNUM INT, GRP
- " INT)"""
MOVE "CREATE TABLE GROUPS1 (EMPNUM INT, GRP INT) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB3" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB4" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""CREATE TABLE NAMES1 (EMPNUM INT, NAME
- " CHAR(5))"""
MOVE "CREATE TABLE NAMES1 (EMPNUM INT, NAAM CHAR(5)) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB5" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB6" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
* EXEC SQL DELETE FROM CONCATBUF;
CALL "SUB7" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL INSERT INTO CONCATBUF VALUES (
* 'CREATE VIEW NAMGRP1 AS SELECT * ' ||
* ' FROM NAMES1 NATURAL JOIN GROUPS1'
* );
CALL "SUB8" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF;
CALL "SUB9" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
DISPLAY "longst=""", longst """"
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB10" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "EXECUTE IMMEDIATE :longst;"
* EXEC SQL EXECUTE IMMEDIATE :longst;
CALL "SUB11" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB12" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO GROUPS1 VALUES (0, 10)"""
MOVE "INSERT INTO GROUPS1 VALUES (0, 10) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB13" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO GROUPS1 VALUES (1, 20)"""
MOVE "INSERT INTO GROUPS1 VALUES (1, 20) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB14" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO GROUPS1 VALUES (2, 30)"""
MOVE "INSERT INTO GROUPS1 VALUES (2, 30) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB15" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO GROUPS1 VALUES (3, 40)"""
MOVE "INSERT INTO GROUPS1 VALUES (3, 40) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB16" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO NAMES1 VALUES (5, 'HARRY')"""
MOVE "INSERT INTO NAMES1 VALUES (5, 'HARRY') "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB17" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO NAMES1 VALUES (1, 'MARY')"""
MOVE "INSERT INTO NAMES1 VALUES (1, 'MARY') "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB18" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO NAMES1 VALUES (7, 'LARRY')"""
MOVE "INSERT INTO NAMES1 VALUES (7, 'LARRY') "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB19" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO NAMES1 VALUES (0, 'KERI')"""
MOVE "INSERT INTO NAMES1 VALUES (0, 'KERI') "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB20" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO NAMES1 VALUES (9, 'BARRY')"""
MOVE "INSERT INTO NAMES1 VALUES (9, 'BARRY') "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB21" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
* EXEC SQL DELETE FROM CONCATBUF;
CALL "SUB22" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL INSERT INTO CONCATBUF VALUES (
* 'SELECT EMPNUM FROM NAMGRP1 WHERE NAAM = ? AND GRP = ?'
* );
CALL "SUB23" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF;
CALL "SUB24" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
DISPLAY "longst=""", longst """"
DISPLAY "PREPARE S10511 FROM :longst;"
* EXEC SQL PREPARE S10511 FROM :longst;
CALL "SUB25" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C10511 CURSOR FOR S10511;"
* EXEC SQL DECLARE C10511 CURSOR FOR S10511 END-EXEC
DISPLAY " "
DISPLAY "ivar1 = 10;"
MOVE 10 TO ivar1
MOVE "KERI " TO cvar1
DISPLAY "OPEN C10511 USING :cvar1, :ivar1;"
* EXEC SQL OPEN C10511 USING :cvar1, :ivar1;
CALL "SUB26" USING SQLCODE SQLSTATE cvar1 ivar1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10511 INTO :int1;"
* EXEC SQL FETCH C10511 INTO :int1;
CALL "SUB27" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 0; its value is ", int1
if (int1 NOT = 0) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10511;"
* EXEC SQL CLOSE C10511;
CALL "SUB28" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ivar1 = 20;"
MOVE 20 TO ivar1
MOVE "MARY " TO cvar1
DISPLAY "OPEN C10511 USING :cvar1, :ivar1;"
* EXEC SQL OPEN C10511 USING :cvar1, :ivar1;
CALL "SUB29" USING SQLCODE SQLSTATE cvar1 ivar1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10511 INTO :int1;"
* EXEC SQL FETCH C10511 INTO :int1;
CALL "SUB30" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 1; its value is ", int1
if (int1 NOT = 1) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10511;"
* EXEC SQL CLOSE C10511;
CALL "SUB31" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
* EXEC SQL DELETE FROM CONCATBUF;
CALL "SUB32" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL INSERT INTO CONCATBUF VALUES (
* 'SELECT COUNT(*) FROM NAMGRP1 ' ||
* ' WHERE (NAAM <> ''MARY''' ||
* ' AND NAAM <> ''KERI'')' ||
* ' OR (GRP <> 20 ' ||
* ' AND GRP <> 10) ' ||
* ' OR (EMPNUM <> 0 ' ||
* ' AND EMPNUM <> 1) ' ||
* ' OR NAAM IS NULL ' ||
* ' OR GRP IS NULL ' ||
* ' OR EMPNUM IS NULL'
* );
CALL "SUB33" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF;
CALL "SUB34" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
DISPLAY "longst=""", longst """"
DISPLAY "PREPARE S10512 FROM :longst;"
* EXEC SQL PREPARE S10512 FROM :longst;
CALL "SUB35" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C10512 CURSOR FOR S10512;"
* EXEC SQL DECLARE C10512 CURSOR FOR S10512 END-EXEC
DISPLAY " "
DISPLAY "OPEN C10512;"
* EXEC SQL OPEN C10512;
CALL "SUB36" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10512 INTO :int1;"
* EXEC SQL FETCH C10512 INTO :int1;
CALL "SUB37" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 0; its value is ", int1
if (int1 NOT = 0) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10512;"
* EXEC SQL CLOSE C10512;
CALL "SUB38" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB39" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
DISPLAY "dstmt=""DROP TABLE NAMES1 CASCADE"""
MOVE "DROP TABLE NAMES1 CASCADE "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB40" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB41" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""DROP TABLE GROUPS1 CASCADE"""
MOVE "DROP TABLE GROUPS1 CASCADE "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB42" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB43" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*NAMGRP should go away on its own due to CASCADE.
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0595','pass','MCO');
CALL "SUB44" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml105.sco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0595','fail','MCO');
CALL "SUB45" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
* EXEC SQL COMMIT WORK;
CALL "SUB46" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST0595 ********************
******************** BEGIN TEST0596 *******************
MOVE 1 TO flag
DISPLAY " TEST0596 "
DISPLAY " INNER JOIN (feature 4) (dynamic)"
DISPLAY "References:"
DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature
- " 4"
DISPLAY " ANSI SQL-1992 7.5 "
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "dstmt=""CREATE TABLE GROUPS2 (EMPNUM INT, GRP
- " INT)"""
MOVE "CREATE TABLE GROUPS2 (EMPNUM INT, GRP INT) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB47" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB48" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""CREATE TABLE NAMES2 (EMPNUM INT, NAME
- " CHAR(5))"""
MOVE "CREATE TABLE NAMES2 (EMPNUM INT, NAAM CHAR(5)) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB49" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB50" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
* EXEC SQL DELETE FROM CONCATBUF;
CALL "SUB51" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL INSERT INTO CONCATBUF VALUES (
* 'CREATE VIEW NAMGRP2 AS SELECT * ' ||
* ' FROM NAMES2 INNER JOIN GROUPS2 USING (EMPNUM)'
* );
CALL "SUB52" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF;
CALL "SUB53" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
DISPLAY "longst=""", longst """"
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB54" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "EXECUTE IMMEDIATE :longst;"
* EXEC SQL EXECUTE IMMEDIATE :longst;
CALL "SUB55" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB56" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*Inner is implicit -- 7.5 SR.3
* EXEC SQL DELETE FROM CONCATBUF;
CALL "SUB57" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL INSERT INTO CONCATBUF VALUES (
* 'CREATE VIEW NMGRP2 AS SELECT * ' ||
* 'FROM NAMES2 JOIN GROUPS2 USING (EMPNUM) WHERE EMPNUM'||
* '> 0'
* );
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 " "
DISPLAY "dstmt=""INSERT INTO GROUPS2 VALUES (0, 10)"""
MOVE "INSERT INTO GROUPS2 VALUES (0, 10) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB63" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO GROUPS2 VALUES (1, 20)"""
MOVE "INSERT INTO GROUPS2 VALUES (1, 20) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB64" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO GROUPS2 VALUES (2, 30)"""
MOVE "INSERT INTO GROUPS2 VALUES (2, 30) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB65" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO GROUPS2 VALUES (3, 40)"""
MOVE "INSERT INTO GROUPS2 VALUES (3, 40) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB66" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO NAMES2 VALUES (5, 'HARRY')"""
MOVE "INSERT INTO NAMES2 VALUES (5, 'HARRY') "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB67" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO NAMES2 VALUES (1, 'MARY')"""
MOVE "INSERT INTO NAMES2 VALUES (1, 'MARY') "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB68" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO NAMES2 VALUES (7, 'LARRY')"""
MOVE "INSERT INTO NAMES2 VALUES (7, 'LARRY') "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB69" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO NAMES2 VALUES (0, 'KERI')"""
MOVE "INSERT INTO NAMES2 VALUES (0, 'KERI') "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB70" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO NAMES2 VALUES (9, 'BARRY')"""
MOVE "INSERT INTO NAMES2 VALUES (9, 'BARRY') "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB71" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
* EXEC SQL DELETE FROM CONCATBUF;
CALL "SUB72" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL INSERT INTO CONCATBUF VALUES (
* 'SELECT EMPNUM FROM NAMGRP2 WHERE NAAM = ? AND GRP = ?'
* );
CALL "SUB73" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF;
CALL "SUB74" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
DISPLAY "longst=""", longst """"
DISPLAY "PREPARE S10521 FROM :longst;"
* EXEC SQL PREPARE S10521 FROM :longst;
CALL "SUB75" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C10521 CURSOR FOR S10521;"
* EXEC SQL DECLARE C10521 CURSOR FOR S10521 END-EXEC
DISPLAY " "
DISPLAY "ivar1 = 10;"
MOVE 10 TO ivar1
MOVE "KERI " TO cvar1
DISPLAY "OPEN C10521 USING :cvar1, :ivar1;"
* EXEC SQL OPEN C10521 USING :cvar1, :ivar1;
CALL "SUB76" USING SQLCODE SQLSTATE cvar1 ivar1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10521 INTO :int1;"
* EXEC SQL FETCH C10521 INTO :int1;
CALL "SUB77" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 0; its value is ", int1
if (int1 NOT = 0) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10521;"
* EXEC SQL CLOSE C10521;
CALL "SUB78" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ivar1 = 20;"
MOVE 20 TO ivar1
MOVE "MARY " TO cvar1
DISPLAY "OPEN C10521 USING :cvar1, :ivar1;"
* EXEC SQL OPEN C10521 USING :cvar1, :ivar1;
CALL "SUB79" USING SQLCODE SQLSTATE cvar1 ivar1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10521 INTO :int1;"
* EXEC SQL FETCH C10521 INTO :int1;
CALL "SUB80" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 1; its value is ", int1
if (int1 NOT = 1) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10521;"
* EXEC SQL CLOSE C10521;
CALL "SUB81" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
* EXEC SQL DELETE FROM CONCATBUF;
CALL "SUB82" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL INSERT INTO CONCATBUF VALUES (
* 'SELECT COUNT(*) FROM NAMGRP2 ' ||
* ' WHERE NAAM <> ''MARY''' ||
* ' AND NAAM <> ''KERI''' ||
* ' OR GRP <> 20 ' ||
* ' AND GRP <> 10 ' ||
* ' OR EMPNUM <> 0 ' ||
* ' AND EMPNUM <> 1 ' ||
* ' OR NAAM IS NULL ' ||
* ' OR GRP IS NULL ' ||
* ' OR EMPNUM IS NULL'
* );
CALL "SUB83" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF;
CALL "SUB84" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
DISPLAY "longst=""", longst """"
DISPLAY "PREPARE S10522 FROM :longst;"
* EXEC SQL PREPARE S10522 FROM :longst;
CALL "SUB85" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C10522 CURSOR FOR S10522;"
* EXEC SQL DECLARE C10522 CURSOR FOR S10522 END-EXEC
DISPLAY " "
DISPLAY "OPEN C10522;"
* EXEC SQL OPEN C10522;
CALL "SUB86" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10522 INTO :int1;"
* EXEC SQL FETCH C10522 INTO :int1;
CALL "SUB87" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 0; its value is ", int1
if (int1 NOT = 0) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10522;"
* EXEC SQL CLOSE C10522;
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 FROM NMGRP2 WHERE NAAM = ? AND GRP = ?'
* );
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 S10524 FROM :longst;"
* EXEC SQL PREPARE S10524 FROM :longst;
CALL "SUB92" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C10524 CURSOR FOR S10524;"
* EXEC SQL DECLARE C10524 CURSOR FOR S10524 END-EXEC
DISPLAY " "
DISPLAY "ivar1 = 20;"
MOVE 20 TO ivar1
MOVE "MARY " TO cvar1
DISPLAY "OPEN C10524 USING :cvar1, :ivar1;"
* EXEC SQL OPEN C10524 USING :cvar1, :ivar1;
CALL "SUB93" USING SQLCODE SQLSTATE cvar1 ivar1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10524 INTO :int1;"
* EXEC SQL FETCH C10524 INTO :int1;
CALL "SUB94" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 1; its value is ", int1
if (int1 NOT = 1) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10524;"
* EXEC SQL CLOSE C10524;
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 COUNT(*) FROM NMGRP2 ' ||
* ' WHERE NAAM <> ''MARY''' ||
* ' OR GRP <> 20 ' ||
* ' OR EMPNUM <> 1 ' ||
* ' OR NAAM IS NULL ' ||
* ' OR GRP IS NULL ' ||
* ' OR EMPNUM IS NULL'
* );
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 S10523 FROM :longst;"
* EXEC SQL PREPARE S10523 FROM :longst;
CALL "SUB99" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C10523 CURSOR FOR S10523;"
* EXEC SQL DECLARE C10523 CURSOR FOR S10523 END-EXEC
DISPLAY " "
DISPLAY "OPEN C10523;"
* EXEC SQL OPEN C10523;
CALL "SUB100" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10523 INTO :int1;"
* EXEC SQL FETCH C10523 INTO :int1;
CALL "SUB101" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 0; its value is ", int1
if (int1 NOT = 0) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10523;"
* EXEC SQL CLOSE C10523;
CALL "SUB102" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB103" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
DISPLAY "dstmt=""DROP TABLE NAMES2 CASCADE"""
MOVE "DROP TABLE NAMES2 CASCADE "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB104" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB105" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""DROP TABLE GROUPS2 CASCADE"""
MOVE "DROP TABLE GROUPS2 CASCADE "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB106" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB107" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*NAMGRP and NMGRP2 should go away on their own due to CASCADE.
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0596','pass','MCO');
CALL "SUB108" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml105.sco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0596','fail','MCO');
CALL "SUB109" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
* EXEC SQL COMMIT WORK;
CALL "SUB110" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST0596 ********************
******************** BEGIN TEST0597 *******************
MOVE 1 TO flag
DISPLAY " TEST0597 "
DISPLAY " LEFT OUTER JOIN (feature 4) (dynamic)"
DISPLAY "References:"
DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature
- " 4"
DISPLAY " ANSI SQL-1992 7.5 "
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "dstmt=""CREATE TABLE GROUPS3 (EMPNUM INT, GRP
- " INT)"""
MOVE "CREATE TABLE GROUPS3 (EMPNUM INT, GRP INT) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB111" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB112" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""CREATE TABLE NAMES3 (EMPNUM INT, NAME
- " CHAR(5))"""
MOVE "CREATE TABLE NAMES3 (EMPNUM INT, NAAM CHAR(5)) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB113" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB114" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
* EXEC SQL DELETE FROM CONCATBUF;
CALL "SUB115" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL INSERT INTO CONCATBUF VALUES (
* 'CREATE VIEW NAMGRP3 AS '||
* 'SELECT * FROM NAMES3 LEFT OUTER JOIN GROUPS3 USING'||
* '(EMPNUM)'
* );
CALL "SUB116" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF;
CALL "SUB117" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
DISPLAY "longst=""", longst """"
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB118" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "EXECUTE IMMEDIATE :longst;"
* EXEC SQL EXECUTE IMMEDIATE :longst;
CALL "SUB119" USING SQLCODE SQLSTATE longst
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 " "
*This is just to see if ON is also supported
* EXEC SQL DELETE FROM CONCATBUF;
CALL "SUB121" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL INSERT INTO CONCATBUF VALUES (
* 'CREATE VIEW NMGRP3 AS'
* ||
* ' SELECT NAAM, GRP FROM NAMES3 LEFT OUTER JOIN GROUPS3'
* ||
* ' ON NAMES3.EMPNUM < GROUPS3.EMPNUM'
* ||
* ' WHERE NAAM <> ''KERI'''
* );
CALL "SUB122" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF;
CALL "SUB123" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
DISPLAY "longst=""", longst """"
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB124" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "EXECUTE IMMEDIATE :longst;"
* EXEC SQL EXECUTE IMMEDIATE :longst;
CALL "SUB125" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB126" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO GROUPS3 VALUES (0, 10)"""
MOVE "INSERT INTO GROUPS3 VALUES (0, 10) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB127" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO GROUPS3 VALUES (1, 20)"""
MOVE "INSERT INTO GROUPS3 VALUES (1, 20) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB128" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO GROUPS3 VALUES (2, 30)"""
MOVE "INSERT INTO GROUPS3 VALUES (2, 30) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB129" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO GROUPS3 VALUES (3, 40)"""
MOVE "INSERT INTO GROUPS3 VALUES (3, 40) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB130" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO NAMES3 VALUES (5, 'HARRY')"""
MOVE "INSERT INTO NAMES3 VALUES (5, 'HARRY') "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB131" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO NAMES3 VALUES (1, 'MARY')"""
MOVE "INSERT INTO NAMES3 VALUES (1, 'MARY') "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB132" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO NAMES3 VALUES (7, 'LARRY')"""
MOVE "INSERT INTO NAMES3 VALUES (7, 'LARRY') "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB133" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO NAMES3 VALUES (0, 'KERI')"""
MOVE "INSERT INTO NAMES3 VALUES (0, 'KERI') "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB134" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""INSERT INTO NAMES3 VALUES (9, 'BARRY')"""
MOVE "INSERT INTO NAMES3 VALUES (9, 'BARRY') "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB135" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
* EXEC SQL DELETE FROM CONCATBUF;
CALL "SUB136" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL INSERT INTO CONCATBUF VALUES (
* 'SELECT COUNT(*) FROM NAMGRP3 WHERE EMPNUM = ? AND ' ||
* ' NAAM = ? AND GRP = ?'
* );
CALL "SUB137" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF;
CALL "SUB138" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
DISPLAY "longst=""", longst """"
DISPLAY "PREPARE S10531 FROM :longst;"
* EXEC SQL PREPARE S10531 FROM :longst;
CALL "SUB139" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C10531 CURSOR FOR S10531;"
* EXEC SQL DECLARE C10531 CURSOR FOR S10531 END-EXEC
DISPLAY " "
DISPLAY "ivar1 = 10;"
DISPLAY "ivar2 = 0;"
MOVE 10 TO ivar1
MOVE 0 TO ivar2
MOVE "KERI " TO cvar1
DISPLAY "OPEN C10531 USING :ivar2, :cvar1, :ivar1;"
* EXEC SQL OPEN C10531 USING :ivar2, :cvar1, :ivar1;
CALL "SUB140" USING SQLCODE SQLSTATE ivar2 cvar1 ivar1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10531 INTO :int1;"
* EXEC SQL FETCH C10531 INTO :int1;
CALL "SUB141" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 1; its value is ", int1
if (int1 NOT = 1) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10531;"
* EXEC SQL CLOSE C10531;
CALL "SUB142" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ivar1 = 20;"
DISPLAY "ivar2 = 1;"
MOVE 20 TO ivar1
MOVE 1 TO ivar2
MOVE "MARY " TO cvar1
DISPLAY "OPEN C10531 USING :ivar2, :cvar1, :ivar1;"
* EXEC SQL OPEN C10531 USING :ivar2, :cvar1, :ivar1;
CALL "SUB143" USING SQLCODE SQLSTATE ivar2 cvar1 ivar1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10531 INTO :int1;"
* EXEC SQL FETCH C10531 INTO :int1;
CALL "SUB144" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 1; its value is ", int1
if (int1 NOT = 1) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10531;"
* EXEC SQL CLOSE C10531;
CALL "SUB145" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
* EXEC SQL DELETE FROM CONCATBUF;
CALL "SUB146" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL INSERT INTO CONCATBUF VALUES (
* 'SELECT COUNT(*) FROM NAMGRP3 WHERE EMPNUM = ? AND ' ||
* ' NAAM = ? AND GRP IS NULL'
* );
CALL "SUB147" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF;
CALL "SUB148" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
DISPLAY "longst=""", longst """"
DISPLAY "PREPARE S10533 FROM :longst;"
* EXEC SQL PREPARE S10533 FROM :longst;
CALL "SUB149" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C10533 CURSOR FOR S10533;"
* EXEC SQL DECLARE C10533 CURSOR FOR S10533 END-EXEC
DISPLAY " "
DISPLAY "ivar1 = 5;"
MOVE 5 TO ivar1
MOVE "HARRY" TO cvar1
DISPLAY "OPEN C10533 USING :ivar1, :cvar1;"
* EXEC SQL OPEN C10533 USING :ivar1, :cvar1;
CALL "SUB150" USING SQLCODE SQLSTATE ivar1 cvar1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10533 INTO :int1;"
* EXEC SQL FETCH C10533 INTO :int1;
CALL "SUB151" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 1; its value is ", int1
if (int1 NOT = 1) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10533;"
* EXEC SQL CLOSE C10533;
CALL "SUB152" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ivar1 = 7;"
MOVE 7 TO ivar1
MOVE "LARRY" TO cvar1
DISPLAY "OPEN C10533 USING :ivar1, :cvar1;"
* EXEC SQL OPEN C10533 USING :ivar1, :cvar1;
CALL "SUB153" USING SQLCODE SQLSTATE ivar1 cvar1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10533 INTO :int1;"
* EXEC SQL FETCH C10533 INTO :int1;
CALL "SUB154" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 1; its value is ", int1
if (int1 NOT = 1) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10533;"
* EXEC SQL CLOSE C10533;
CALL "SUB155" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ivar1 = 9;"
MOVE 9 TO ivar1
MOVE "BARRY" TO cvar1
DISPLAY "OPEN C10533 USING :ivar1, :cvar1;"
* EXEC SQL OPEN C10533 USING :ivar1, :cvar1;
CALL "SUB156" USING SQLCODE SQLSTATE ivar1 cvar1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10533 INTO :int1;"
* EXEC SQL FETCH C10533 INTO :int1;
CALL "SUB157" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 1; its value is ", int1
if (int1 NOT = 1) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10533;"
* EXEC SQL CLOSE C10533;
CALL "SUB158" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""SELECT COUNT(*) FROM NAMGRP3"""
MOVE "SELECT COUNT(*) FROM NAMGRP3
- " " TO dstmt
DISPLAY "PREPARE S10532 FROM :dstmt;"
* EXEC SQL PREPARE S10532 FROM :dstmt;
CALL "SUB159" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C10532 CURSOR FOR S10532;"
* EXEC SQL DECLARE C10532 CURSOR FOR S10532 END-EXEC
DISPLAY " "
DISPLAY "OPEN C10532;"
* EXEC SQL OPEN C10532;
CALL "SUB160" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10532 INTO :int1;"
* EXEC SQL FETCH C10532 INTO :int1;
CALL "SUB161" 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 C10532;"
* EXEC SQL CLOSE C10532;
CALL "SUB162" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
* EXEC SQL DELETE FROM CONCATBUF;
CALL "SUB163" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL INSERT INTO CONCATBUF VALUES (
* 'SELECT COUNT(*) FROM NMGRP3 WHERE' ||
* ' NAAM = ''MARY'' AND GRP = ?'
* );
CALL "SUB164" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF;
CALL "SUB165" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
DISPLAY "longst=""", longst """"
DISPLAY "PREPARE S10534 FROM :longst;"
* EXEC SQL PREPARE S10534 FROM :longst;
CALL "SUB166" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C10534 CURSOR FOR S10534;"
* EXEC SQL DECLARE C10534 CURSOR FOR S10534 END-EXEC
DISPLAY " "
DISPLAY "ivar1 = 30;"
MOVE 30 TO ivar1
DISPLAY "OPEN C10534 USING :ivar1;"
* EXEC SQL OPEN C10534 USING :ivar1;
CALL "SUB167" USING SQLCODE SQLSTATE ivar1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10534 INTO :int1;"
* EXEC SQL FETCH C10534 INTO :int1;
CALL "SUB168" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 1; its value is ", int1
if (int1 NOT = 1) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10534;"
* EXEC SQL CLOSE C10534;
CALL "SUB169" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ivar1 = 40;"
MOVE 40 TO ivar1
DISPLAY "OPEN C10534 USING :ivar1;"
* EXEC SQL OPEN C10534 USING :ivar1;
CALL "SUB170" USING SQLCODE SQLSTATE ivar1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10534 INTO :int1;"
* EXEC SQL FETCH C10534 INTO :int1;
CALL "SUB171" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 1; its value is ", int1
if (int1 NOT = 1) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10534;"
* EXEC SQL CLOSE C10534;
CALL "SUB172" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
* EXEC SQL DELETE FROM CONCATBUF;
CALL "SUB173" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL INSERT INTO CONCATBUF VALUES (
* 'SELECT COUNT(*) FROM NMGRP3 WHERE' ||
* ' NAAM = ? AND GRP IS NULL'
* );
CALL "SUB174" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF;
CALL "SUB175" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
DISPLAY "longst=""", longst """"
DISPLAY "PREPARE S10535 FROM :longst;"
* EXEC SQL PREPARE S10535 FROM :longst;
CALL "SUB176" USING SQLCODE SQLSTATE longst
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C10535 CURSOR FOR S10535;"
* EXEC SQL DECLARE C10535 CURSOR FOR S10535 END-EXEC
DISPLAY " "
MOVE "BARRY" TO cvar1
DISPLAY "OPEN C10535 USING :cvar1;"
* EXEC SQL OPEN C10535 USING :cvar1;
CALL "SUB177" USING SQLCODE SQLSTATE cvar1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10535 INTO :int1;"
* EXEC SQL FETCH C10535 INTO :int1;
CALL "SUB178" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 1; its value is ", int1
if (int1 NOT = 1) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10535;"
* EXEC SQL CLOSE C10535;
CALL "SUB179" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "HARRY" TO cvar1
DISPLAY "OPEN C10535 USING :cvar1;"
* EXEC SQL OPEN C10535 USING :cvar1;
CALL "SUB180" USING SQLCODE SQLSTATE cvar1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10535 INTO :int1;"
* EXEC SQL FETCH C10535 INTO :int1;
CALL "SUB181" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 1; its value is ", int1
if (int1 NOT = 1) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10535;"
* EXEC SQL CLOSE C10535;
CALL "SUB182" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "LARRY" TO cvar1
DISPLAY "OPEN C10535 USING :cvar1;"
* EXEC SQL OPEN C10535 USING :cvar1;
CALL "SUB183" USING SQLCODE SQLSTATE cvar1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10535 INTO :int1;"
* EXEC SQL FETCH C10535 INTO :int1;
CALL "SUB184" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 1; its value is ", int1
if (int1 NOT = 1) then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C10535;"
* EXEC SQL CLOSE C10535;
CALL "SUB185" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""SELECT COUNT(*) FROM NMGRP3"""
MOVE "SELECT COUNT(*) FROM NMGRP3
- " " TO dstmt
DISPLAY "PREPARE S10536 FROM :dstmt;"
* EXEC SQL PREPARE S10536 FROM :dstmt;
CALL "SUB186" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C10536 CURSOR FOR S10536;"
* EXEC SQL DECLARE C10536 CURSOR FOR S10536 END-EXEC
DISPLAY " "
DISPLAY "OPEN C10536;"
* EXEC SQL OPEN C10536;
CALL "SUB187" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "FETCH C10536 INTO :int1;"
* EXEC SQL FETCH C10536 INTO :int1;
CALL "SUB188" 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 C10536;"
* EXEC SQL CLOSE C10536;
CALL "SUB189" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB190" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
DISPLAY "dstmt=""DROP TABLE NAMES3 CASCADE"""
MOVE "DROP TABLE NAMES3 CASCADE "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB191" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB192" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""DROP TABLE GROUPS3 CASCADE"""
MOVE "DROP TABLE GROUPS3 CASCADE "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB193" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB194" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*NAMGRP should go away on its own due to CASCADE.
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0597','pass','MCO');
CALL "SUB195" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml105.sco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0597','fail','MCO');
CALL "SUB196" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
* EXEC SQL COMMIT WORK;
CALL "SUB197" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST0597 ********************
******************** BEGIN TEST0598 *******************
MOVE 1 TO flag
DISPLAY " TEST0598 "
DISPLAY " RIGHT OUTER JOIN (feature 4) (dynamic)"
DISPLAY "References:"
DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature
- " 4"
DISPLAY " ANSI SQL-1992 7.5 "
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "dstmt=""CREATE TABLE GROUPS4 (EMPNUM INT, GRP
- " INT)"""
MOVE "CREATE TABLE GROUPS4 (EMPNUM INT, GRP INT) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB198" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB199" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "dstmt=""CREATE TABLE NAMES4 (EMPNUM INT, NAME
- " CHAR(5))"""
MOVE "CREATE TABLE NAMES4 (EMPNUM INT, NAAM CHAR(5)) "
TO dstmt
DISPLAY "EXECUTE IMMEDIATE :dstmt;"
* EXEC SQL EXECUTE IMMEDIATE :dstmt;
CALL "SUB200" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB201" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
* EXEC SQL DELETE FROM CONCATBUF;
CALL "SUB202" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
* EXEC SQL INSERT INTO CONCATBUF VALUES (
* 'CREATE VIEW NAMGRP4 AS '||
* 'SELECT * FROM NAMES4 RIGHT OUTER JOIN GROUPS4 USING '||
* '(EMPNUM)'
* );
CALL "SUB203" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
--> --------------------
--> maximum size reached
--> --------------------
¤ Dauer der Verarbeitung: 0.201 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.
|
