| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/Cobol/Test-Suite/SQL P/dml100-186/ Datei vom 4.1.2008 mit Größe 110 kB |
|
Quelle dml124.cob Sprache: Cobol |
|||||||||
|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML124. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. xyz. OBJECT-COMPUTER. xyz. DATA DIVISION. WORKING-STORAGE SECTION. * EMBEDDED COBOL (file "DML124.PCO") **************************************************************** * * COMMENT SECTION * * DATE 1994/6/20 EMBEDDED COBOL LANGUAGE * NIST SQL VALIDATION TEST SUITE V6.0 * DISCLAIMER: * This program was written by employees of NIST to test SQL * implementations for conformance to the SQL standards. * NIST assumes no responsibility for any party's use of * this program. * * DML124.PCO * WRITTEN BY: David W. Flater * TRANSLATED AUTOMATICALLY FROM EMBEDDED C BY CHRIS SCHANZLE * Bug fixes & stronger pass criteria by Joan Sullivan 2/27/95 * Corrected in accordance with X3H2-95-105 5/9/95 * * This routine tests SQL descriptors, which is a feature of * Dynamic SQL. * * 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 int3 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 int4 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 int5 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 int6 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 int7 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 flt1 PIC S9(4)V9(4) DISPLAY SIGN LEADING SEPARATE. * OK to change the precision of smint1 to match SMALLINT 01 smint1 PIC S9(2) BINARY. * OK to change the precision of bin2 to match SMALLINT 01 bin2 PIC S9(2) BINARY. * OK to change the precision of bin9 to match INTEGER 01 bin9 PIC S9(9) BINARY. 01 dstmt PIC X(50). 01 longst PIC X(240). 01 cname PIC X(20). 01 indic1 PIC S9(4) DISPLAY SIGN LEADING SEPARATE. 01 c2 PIC X(2). 01 c4 PIC X(4). 01 c6 PIC X(6). 01 c7 PIC X(7). EXEC SQL END DECLARE SECTION END-EXEC 01 FLT-1 PIC -(5).9999. 01 BIN-1 PIC -(5). 01 intprc PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 relprc PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 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 "HU " TO uid CALL "AUTHID" USING uid MOVE "not logged in, not" TO uidx EXEC SQL SELECT USER INTO :uidx FROM HU.ECCO END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL ROLLBACK WORK END-EXEC 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, Embedded COBOL, dml124.pco" 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 *This test was converted from a FLATER test to a HU test. We *need CONCATBUF. DISPLAY "CREATE TABLE CONCATBUF (ZZ CHAR(240));" EXEC SQL CREATE TABLE CONCATBUF (ZZ CHAR(240)) END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD ******************** BEGIN TEST0653 ******************* MOVE 1 TO flag DISPLAY " TEST0653 " DISPLAY " Descriptors: DESCRIBE OUTPUT" DISPLAY "References:" DISPLAY " F# 1 -- Dynamic SQL" DISPLAY " 17.8 -- DISPLAY " 17.1 -- Description of SQL item descriptor - " areas" DISPLAY " 17.2 GR.3 -- limit on descriptor areas is" DISPLAY " implementation-defined" DISPLAY " 7.9 SR.10 -- nullability" DISPLAY " 6.1 -- " DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE DISPLAY "CREATE TABLE CACHESTAT (" DISPLAY " HIT_RATIO FLOAT (17) NOT NULL," DISPLAY " FRAGMENTATION_PCT FLOAT);" EXEC SQL CREATE TABLE CACHESTAT ( HIT_RATIO FLOAT (17) NOT NULL, FRAGMENTATION_PCT FLOAT) END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "CREATE VIEW COMBINATIONS (INTTEST," DISPLAY " REALTEST, SMALLTEST, DOUBLETEST, NUMTEST," DISPLAY " DECTEST) AS SELECT * FROM HU.EE, HU.GG, HU.HH," DISPLAY " HU.II, HU.LL, HU.PP;" EXEC SQL CREATE VIEW COMBINATIONS (INTTEST, REALTEST, SMALLTEST, DOUBLETEST, NUMTEST, DECTEST) AS SELECT * FROM HU.EE, HU.GG, HU.HH, HU.II, HU.LL, HU.PP END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ALLOCATE DESCRIPTOR 'D12411' WITH MAX 8;" EXEC SQL ALLOCATE DESCRIPTOR 'D12411' WITH MAX 8 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK if (SQLSTATE = "07009") then DISPLAY "Received SQLSTATE 07009!" DISPLAY "This test must be rewritten by NIST (in the - " event of" DISPLAY "a validation) to accomodate - " implementation-defined" DISPLAY "limit on END-IF DISPLAY " " DISPLAY "dstmt=""SELECT * FROM HU.STAFF""" MOVE "SELECT * FROM HU.STAFF - " " TO dstmt DISPLAY "PREPARE S12411 FROM :dstmt;" EXEC SQL PREPARE S12411 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DESCRIBE OUTPUT S12411 USING SQL DESCRIPTOR - " 'D12411';" EXEC SQL DESCRIBE OUTPUT S12411 USING SQL DESCRIPTOR 'D12411' END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "GET DESCRIPTOR 'D12411' :int1 = COUNT;" EXEC SQL GET DESCRIPTOR 'D12411' :int1 = COUNT END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 4; its value is ", int1 if (int1 NOT = 4) then MOVE 0 TO flag END-IF *CHARACTER_SET_* and COLLATION_* are orphaned features COMPUTE int1 = -1 COMPUTE int2 = -1 COMPUTE int3 = -1 COMPUTE int4 = -1 COMPUTE int5 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12411' VALUE 1" DISPLAY " :int1 = TYPE, :int2 = NULLABLE, :int3 = - " UNNAMED," DISPLAY " :cname = NAME, :int4 = LENGTH, :int5 = - " OCTET_LENGTH;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE 1 :int1 = TYPE, :int2 = NULLABLE, :int3 = UNNAMED, :cname = NAME, :int4 = LENGTH, :int5 = OCTET_LENGTH END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 1 (CHARACTER); its value is ", int1 DISPLAY "int2 should be 0 (NOT NULL); its value is ", int2 DISPLAY "int3 should be 0 (named); its value is ", int3 DISPLAY "cname should be 'EMPNUM '; its value - " is '", cname "'" DISPLAY "int4 should be 3; its value is ", int4 DISPLAY "int5 should be > 2; its value is ", int5 if (int1 NOT = 1 OR int2 NOT = 0 OR int3 NOT = 0) then MOVE 0 TO flag END-IF if (int4 NOT = 3 OR int5 NOT > 2) then MOVE 0 TO flag END-IF if (cname NOT = "EMPNUM ") then MOVE 0 TO flag END-IF COMPUTE bin9 = -1 COMPUTE int2 = -1 COMPUTE bin2 = -1 COMPUTE int4 = -1 COMPUTE int5 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12411' VALUE 2" DISPLAY " :bin9 = TYPE, :int2 = NULLABLE, :bin2 = - " UNNAMED," DISPLAY " :cname = NAME, :int4 = LENGTH, :int5 = - " OCTET_LENGTH;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE 2 :bin9 = TYPE, :int2 = NULLABLE, :bin2 = UNNAMED, :cname = NAME, :int4 = LENGTH, :int5 = OCTET_LENGTH END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK MOVE bin9 TO BIN-1 DISPLAY "bin9 should be 1 (CHARACTER); its value is " BIN-1 DISPLAY "int2 should be 1 (NULLABLE); its value is ", int2 MOVE bin2 TO BIN-1 DISPLAY "bin2 should be 0 (named); its value is ", BIN-1 DISPLAY "cname should be 'EMPNAME '; its value - " is '", cname "'" DISPLAY "int4 should be 20; its value is ", int4 DISPLAY "int5 should be > 14; its value is ", int5 if (bin9 NOT = 1 OR int2 NOT = 1 OR bin2 NOT = 0) then MOVE 0 TO flag END-IF if (int4 NOT = 20 OR int5 NOT > 14) then MOVE 0 TO flag END-IF if (cname NOT = "EMPNAME ") then MOVE 0 TO flag END-IF COMPUTE bin2 = -1 COMPUTE int2 = -1 COMPUTE bin9 = -1 COMPUTE int4 = -1 COMPUTE int5 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12411' VALUE 3" DISPLAY " :bin2 = TYPE, :int2 = NULLABLE, :bin9 = - " UNNAMED," DISPLAY " :cname = NAME, :int4 = PRECISION, :int5 = - " SCALE;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE 3 :bin2 = TYPE, :int2 = NULLABLE, :bin9 = UNNAMED, :cname = NAME, :int4 = PRECISION, :int5 = SCALE END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK MOVE bin2 TO BIN-1 DISPLAY "bin2 should be 3 (DECIMAL); its value is ", BIN-1 DISPLAY "int2 should be 1 (NULLABLE); its value is ", int2 MOVE bin9 TO BIN-1 DISPLAY "bin9 should be 0 (named); its value is ", BIN-1 DISPLAY "cname should be 'GRADE '; its value - " is '", cname "'" DISPLAY "int4 should be >= 4; its value is ", int4 DISPLAY "int5 should be 0; its value is ", int5 if (bin2 NOT = 3 OR int2 NOT = 1 OR bin9 NOT = 0) then MOVE 0 TO flag END-IF if (int4 < 4 OR int5 NOT = 0) then MOVE 0 TO flag END-IF if (cname NOT = "GRADE ") then MOVE 0 TO flag END-IF DISPLAY "GET DESCRIPTOR 'D12411' VALUE 4" DISPLAY " :int1 = TYPE, :int2 = NULLABLE, :bin2 = - " UNNAMED," DISPLAY " :cname = NAME, :bin9 = LENGTH, :int5 = - " OCTET_LENGTH;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE 4 :int1 = TYPE, :int2 = NULLABLE, :bin2 = UNNAMED, :cname = NAME, :bin9 = LENGTH, :int5 = OCTET_LENGTH END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 1 (CHARACTER); its value is ", int1 DISPLAY "int2 should be 1 (NULLABLE); its value is ", int2 MOVE bin2 TO BIN-1 DISPLAY "bin2 should be 0 (named); its value is ", BIN-1 DISPLAY "cname should be 'CITY '; its value - " is '", cname "'" MOVE bin9 TO BIN-1 DISPLAY "bin9 should be 15; its value is ", BIN-1 DISPLAY "int5 should be > 11; its value is ", int5 if (int1 NOT = 1 OR int2 NOT = 1 OR bin2 NOT = 0) then MOVE 0 TO flag END-IF if (bin9 NOT = 15 OR int5 NOT > 11) then MOVE 0 TO flag END-IF if (cname NOT = "CITY ") then MOVE 0 TO flag END-IF EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT * FROM CACHESTAT, COMBINATIONS' || ' WHERE HIT_RATIO < .97' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S12412 FROM :longst;" EXEC SQL PREPARE S12412 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DESCRIBE S12412 USING SQL DESCRIPTOR 'D12411';" EXEC SQL DESCRIBE S12412 USING SQL DESCRIPTOR 'D12411' END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "GET DESCRIPTOR 'D12411' :int1 = COUNT;" EXEC SQL GET DESCRIPTOR 'D12411' :int1 = COUNT END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 8; its value is ", int1 if (int1 NOT = 8) then MOVE 0 TO flag END-IF COMPUTE int1 = -1 COMPUTE int2 = -1 COMPUTE int3 = -1 COMPUTE int4 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12411' VALUE 1" DISPLAY " :int1 = TYPE, :int2 = NULLABLE, :int3 = - " UNNAMED," DISPLAY " :cname = NAME, :int4 = PRECISION;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE 1 :int1 = TYPE, :int2 = NULLABLE, :int3 = UNNAMED, :cname = NAME, :int4 = PRECISION END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 6 (FLOAT); its value is ", int1 DISPLAY "int2 should be 0 (NOT NULL); its value is ", int2 DISPLAY "int3 should be 0 (named); its value is ", int3 DISPLAY "cname should be 'HIT_RATIO '; its value - " is '", cname "'" DISPLAY "int4 should be > 0; its value is ", int4 if (int1 NOT = 6 OR int2 NOT = 0 OR int3 NOT = 0) then MOVE 0 TO flag END-IF if (int4 NOT > 0) then MOVE 0 TO flag END-IF if (cname NOT = "HIT_RATIO ") then MOVE 0 TO flag END-IF COMPUTE int1 = -1 COMPUTE int2 = -1 COMPUTE int3 = -1 COMPUTE int4 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12411' VALUE 2" DISPLAY " :int1 = TYPE, :int2 = NULLABLE, :int3 = - " UNNAMED," DISPLAY " :cname = NAME, :int4 = PRECISION;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE 2 :int1 = TYPE, :int2 = NULLABLE, :int3 = UNNAMED, :cname = NAME, :int4 = PRECISION END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 6 (FLOAT); its value is ", int1 DISPLAY "int2 should be 1 (NULLABLE); its value is ", int2 DISPLAY "int3 should be 0 (named); its value is ", int3 DISPLAY "cname should be 'FRAGMENTATION_PCT '; its value - " is '", cname "'" DISPLAY "int4 should be > 0; its value is ", int4 if (int1 NOT = 6 OR int2 NOT = 1 OR int3 NOT = 0) then MOVE 0 TO flag END-IF if (int4 < 1) then MOVE 0 TO flag END-IF if (cname NOT = "FRAGMENTATION_PCT ") then MOVE 0 TO flag END-IF COMPUTE int1 = -1 COMPUTE int2 = -1 COMPUTE int3 = -1 COMPUTE int4 = -1 COMPUTE int5 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12411' VALUE 3" DISPLAY " :int1 = TYPE, :int2 = NULLABLE, :int3 = - " UNNAMED," DISPLAY " :cname = NAME, :int4 = PRECISION, :int5 = - " SCALE;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE 3 :int1 = TYPE, :int2 = NULLABLE, :int3 = UNNAMED, :cname = NAME, :int4 = PRECISION, :int5 = SCALE END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 4 (INTEGER); its value is ", int1 DISPLAY "int2 should be 1 (NULLABLE); its value is ", int2 DISPLAY "int3 should be 0 (named); its value is ", int3 DISPLAY "cname should be 'INTTEST '; its value - " is '", cname "'" DISPLAY "int4 should be > 0; its value is ", int4 DISPLAY "int5 should be 0; its value is ", int5 if (int1 NOT = 4 OR int2 NOT = 1 OR int3 NOT = 0) then MOVE 0 TO flag END-IF if (int4 < 1 OR int5 NOT = 0) then MOVE 0 TO flag END-IF if (cname NOT = "INTTEST ") then MOVE 0 TO flag END-IF MOVE int4 TO intprc COMPUTE int1 = -1 COMPUTE int2 = -1 COMPUTE int3 = -1 COMPUTE int4 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12411' VALUE 4" DISPLAY " :int1 = TYPE, :int2 = NULLABLE, :int3 = - " UNNAMED," DISPLAY " :cname = NAME, :int4 = PRECISION;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE 4 :int1 = TYPE, :int2 = NULLABLE, :int3 = UNNAMED, :cname = NAME, :int4 = PRECISION END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 7 (REAL); its value is ", int1 DISPLAY "int2 should be 1 (NULLABLE); its value is ", int2 DISPLAY "int3 should be 0 (named); its value is ", int3 DISPLAY "cname should be 'REALTEST '; its value - " is '", cname "'" DISPLAY "int4 should be > 0; its value is ", int4 if (int1 NOT = 7 OR int2 NOT = 1 OR int3 NOT = 0) then MOVE 0 TO flag END-IF if (int4 < 1) then MOVE 0 TO flag END-IF if (cname NOT = "REALTEST ") then MOVE 0 TO flag END-IF MOVE int4 TO relprc COMPUTE int1 = -1 COMPUTE int2 = -1 COMPUTE int3 = -1 COMPUTE int4 = -1 COMPUTE int5 = -1 MOVE 5 TO int6 DISPLAY "int6 = 5" MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12411' VALUE :int6" DISPLAY " :int1 = TYPE, :int2 = NULLABLE, :int3 = - " UNNAMED," DISPLAY " :cname = NAME, :int4 = PRECISION, :int5 = - " SCALE;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE :int6 :int1 = TYPE, :int2 = NULLABLE, :int3 = UNNAMED, :cname = NAME, :int4 = PRECISION, :int5 = SCALE END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 5 (SMALLINT); its value is ", int1 DISPLAY "int2 should be 1 (NULLABLE); its value is ", int2 DISPLAY "int3 should be 0 (named); its value is ", int3 DISPLAY "cname should be 'SMALLTEST '; its value - " is '", cname "'" DISPLAY "int4 should be 0 < int4 <= ", intprc "; its value - " is ", int4 DISPLAY "int5 should be 0; its value is ", int5 if (int1 NOT = 4 OR int2 NOT = 1 OR int3 NOT = 0) then MOVE 0 TO flag END-IF if (int4 < 1 OR int4 > intprc OR int5 NOT = 0) then MOVE 0 TO flag END-IF if (cname NOT = "SMALLTEST ") then MOVE 0 TO flag END-IF COMPUTE int1 = -1 COMPUTE int2 = -1 COMPUTE int3 = -1 COMPUTE int4 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12411' VALUE 6" DISPLAY " :int1 = TYPE, :int2 = NULLABLE, :int3 = - " UNNAMED," DISPLAY " :cname = NAME, :int4 = PRECISION;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE 6 :int1 = TYPE, :int2 = NULLABLE, :int3 = UNNAMED, :cname = NAME, :int4 = PRECISION END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 8 (DOUBLE); its value is ", int1 DISPLAY "int2 should be 1 (NULLABLE); its value is ", int2 DISPLAY "int3 should be 0 (named); its value is ", int3 DISPLAY "cname should be 'DOUBLETEST '; its value - " is '", cname "'" DISPLAY "int4 should be > ", relprc "; its value is ", int4 if (int1 NOT = 8 OR int2 NOT = 1 OR int3 NOT = 0) then MOVE 0 TO flag END-IF if (int4 < 1 OR int4 NOT > relprc) then MOVE 0 TO flag END-IF if (cname NOT = "DOUBLETEST ") then MOVE 0 TO flag END-IF COMPUTE int1 = -1 COMPUTE int2 = -1 COMPUTE int3 = -1 COMPUTE int4 = -1 COMPUTE int5 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12411' VALUE 7" DISPLAY " :int1 = TYPE, :int2 = NULLABLE, :int3 = - " UNNAMED," DISPLAY " :cname = NAME, :int4 = PRECISION, :int5 = - " SCALE;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE 7 :int1 = TYPE, :int2 = NULLABLE, :int3 = UNNAMED, :cname = NAME, :int4 = PRECISION, :int5 = SCALE END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 2 (NUMERIC); its value is ", int1 DISPLAY "int2 should be 1 (NULLABLE); its value is ", int2 DISPLAY "int3 should be 0 (named); its value is ", int3 DISPLAY "cname should be 'NUMTEST '; its value - " is '", cname "'" DISPLAY "int4 should be 13; its value is ", int4 DISPLAY "int5 should be 6; its value is ", int5 if (int1 NOT = 2 OR int2 NOT = 1 OR int3 NOT = 0) then MOVE 0 TO flag END-IF if (int4 NOT = 13 OR int5 NOT = 6) then MOVE 0 TO flag END-IF if (cname NOT = "NUMTEST ") then MOVE 0 TO flag END-IF COMPUTE int1 = -1 COMPUTE int2 = -1 COMPUTE int3 = -1 COMPUTE int4 = -1 COMPUTE int5 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12411' VALUE 8" DISPLAY " :int1 = TYPE, :int2 = NULLABLE, :int3 = - " UNNAMED," DISPLAY " :cname = NAME, :int4 = PRECISION, :int5 = - " SCALE;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE 8 :int1 = TYPE, :int2 = NULLABLE, :int3 = UNNAMED, :cname = NAME, :int4 = PRECISION, :int5 = SCALE END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 3 (DECIMAL); its value is ", int1 DISPLAY "int2 should be 1 (NULLABLE); its value is ", int2 DISPLAY "int3 should be 0 (named); its value is ", int3 DISPLAY "cname should be 'DECTEST '; its value - " is '", cname "'" DISPLAY "int4 should be >= 13; its value is ", int4 DISPLAY "int5 should be 6; its value is ", int5 if (int1 NOT = 3 OR int2 NOT = 1 OR int3 NOT = 0) then MOVE 0 TO flag END-IF if (int4 < 13 OR int5 NOT = 6) then MOVE 0 TO flag END-IF if (cname NOT = "DECTEST ") then MOVE 0 TO flag END-IF *Unnamed columns DISPLAY "dstmt=""SELECT USER, COUNT(*) FROM HU.ECCO""" MOVE "SELECT USER, COUNT(*) FROM HU.ECCO - " " TO dstmt DISPLAY "PREPARE S12413 FROM :dstmt;" EXEC SQL PREPARE S12413 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DESCRIBE S12413 USING SQL DESCRIPTOR 'D12411';" EXEC SQL DESCRIBE S12413 USING SQL DESCRIPTOR 'D12411' END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "GET DESCRIPTOR 'D12411' :int1 = COUNT;" EXEC SQL GET DESCRIPTOR 'D12411' :int1 = COUNT END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 2; its value is ", int1 if (int1 NOT = 2) then MOVE 0 TO flag END-IF *Nullability of columns: 7.9 GR.10 COMPUTE int1 = -1 COMPUTE int2 = -1 COMPUTE int3 = -1 COMPUTE int4 = -1 COMPUTE int5 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12411' VALUE 1" DISPLAY " :int1 = TYPE, :int2 = NULLABLE, :int3 = - " UNNAMED," DISPLAY " :cname = NAME, :int4 = LENGTH, :int5 = - " OCTET_LENGTH;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE 1 :int1 = TYPE, :int2 = NULLABLE, :int3 = UNNAMED, :cname = NAME, :int4 = LENGTH, :int5 = OCTET_LENGTH END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 1 (CHARACTER); its value is ", int1 DISPLAY "int2 should be 0 (NOT NULL); its value is ", int2 DISPLAY "int3 should be 1 (unnamed); its value is ", int3 DISPLAY "cname is '", cname "'" DISPLAY "int4 should be > 7; its value is ", int4 DISPLAY "int5 should be > 4; its value is ", int5 if (int1 NOT = 1 OR int2 NOT = 0 OR int3 NOT = 1) then MOVE 0 TO flag END-IF if (int4 NOT > 7 OR int5 NOT > 4) then DISPLAY "The correct values for int4 and int5 are - " implementation-" DISPLAY "defined, but must be justified since they are - " unexpectedly low." MOVE 0 TO flag END-IF COMPUTE int1 = -1 COMPUTE int2 = -1 COMPUTE int3 = -1 COMPUTE int4 = -1 COMPUTE int5 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12411' VALUE 2" DISPLAY " :int1 = TYPE, :int2 = NULLABLE, :int3 = - " UNNAMED," DISPLAY " :cname = NAME, :int4 = PRECISION, :int5 = - " SCALE;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE 2 :int1 = TYPE, :int2 = NULLABLE, :int3 = UNNAMED, :cname = NAME, :int4 = PRECISION, :int5 = SCALE END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 2 <= int1 <= 5; its value is ", int1 DISPLAY "int2 should be 0 (NOT NULL); its value is ", int2 DISPLAY "int3 should be 1 (unnamed); its value is ", int3 DISPLAY "cname is '", cname "'" DISPLAY "int4 should be > 0; its value is ", int4 DISPLAY "int5 should be 0; its value is ", int5 if (int1 < 2 OR int1 > 5 OR int2 NOT = 0) then MOVE 0 TO flag END-IF if (int3 NOT = 1 OR int4 < 1 OR int5 NOT = 0) then MOVE 0 TO flag END-IF *X3H2-95-103: Question marks are possibly nullable *17.9 GR.3.e.iii Unnamed columns EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT HIT_RATIO AS HR, FRAGMENTATION_PCT * ?' || ' FROM CACHESTAT' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S12414 FROM :longst;" EXEC SQL PREPARE S12414 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DESCRIBE S12414 USING SQL DESCRIPTOR 'D12411';" EXEC SQL DESCRIBE S12414 USING SQL DESCRIPTOR 'D12411' END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 COMPUTE int2 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12411' VALUE 1" DISPLAY " :int1 = NULLABLE, :int2 = UNNAMED, :cname = - " NAME;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE 1 :int1 = NULLABLE, :int2 = UNNAMED, :cname = NAME END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "int1 should be 0 (NOT NULL); its value is ", int1 DISPLAY "int2 should be 0 (named); its value is ", int2 DISPLAY "cname should be 'HR '; its value - " is '", cname "'" if (int1 NOT = 0 OR int2 NOT = 0) then MOVE 0 TO flag END-IF if (cname NOT = "HR ") then MOVE 0 TO flag END-IF COMPUTE int1 = -1 COMPUTE int2 = -1 DISPLAY "GET DESCRIPTOR 'D12411' VALUE 2" DISPLAY " :int1 = NULLABLE, :int2 = UNNAMED;" EXEC SQL GET DESCRIPTOR 'D12411' VALUE 2 :int1 = NULLABLE, :int2 = UNNAMED END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "int1 should be 1 (NULLABLE); its value is ", int1 DISPLAY "int2 should be 1 (unnamed); its value is ", int2 if (int1 NOT = 1 OR int2 NOT = 1) then MOVE 0 TO flag END-IF DISPLAY "DEALLOCATE DESCRIPTOR 'D12411';" EXEC SQL DEALLOCATE DESCRIPTOR 'D12411' END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " . P199. DISPLAY "ROLLBACK WORK;" EXEC SQL ROLLBACK WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DROP TABLE CACHESTAT CASCADE;" EXEC SQL DROP TABLE CACHESTAT CASCADE END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DROP VIEW COMBINATIONS CASCADE;" EXEC SQL DROP VIEW COMBINATIONS CASCADE END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " if ( flag = 1 ) then DISPLAY " *** pass *** " EXEC SQL INSERT INTO HU.TESTREPORT VALUES('0653','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY " dml124.pco *** fail *** " EXEC SQL INSERT INTO HU.TESTREPORT VALUES('0653','fail','PCO') END-EXEC MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD ******************** END TEST0653 ******************** ******************** BEGIN TEST0654 ******************* MOVE 1 TO flag DISPLAY " TEST0654 " DISPLAY " Descriptors: INTO SQL DESCRIPTOR" DISPLAY "References:" DISPLAY " F# 1 -- Dynamic SQL" DISPLAY " 17.9 -- DISPLAY " 17.8 -- DISPLAY " 17.1 -- Description of SQL item descriptor - " areas" DISPLAY " 17.2 GR.3 -- limit on descriptor areas is" DISPLAY " implementation-defined" DISPLAY " 6.1 -- " DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE DISPLAY "CREATE TABLE BANKTAB (" DISPLAY " NAAM CHAR (10), BALANCE NUMERIC (7, 2)," DISPLAY " INTEREST DECIMAL (3, 2), TRANSACTIONS INT," DISPLAY " PAYMENTS SMALLINT, INTEREST_RATE FLOAT," DISPLAY " ANNUAL_YIELD REAL, FRACTIONAL_CENTS DOUBLE - " PRECISION);" EXEC SQL CREATE TABLE BANKTAB ( NAAM CHAR (10), BALANCE NUMERIC (7, 2), INTEREST DECIMAL (3, 2), TRANSACTIONS INT, PAYMENTS SMALLINT, INTEREST_RATE FLOAT, ANNUAL_YIELD REAL, FRACTIONAL_CENTS DOUBLE PRECISION) END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "INSERT INTO BANKTAB VALUES (" DISPLAY " 'Bubba', 524.07, 2.42, 13, 1, 3.0, 3.1, -.256);" EXEC SQL INSERT INTO BANKTAB VALUES ( 'Bubba', 524.07, 2.42, 13, 1, 3.0, 3.1, -.256) END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Allocate one extra occurrence DISPLAY "ALLOCATE DESCRIPTOR 'D12421' WITH MAX 9;" EXEC SQL ALLOCATE DESCRIPTOR 'D12421' WITH MAX 9 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK if (SQLSTATE = "07009") then DISPLAY "Received SQLSTATE 07009!" DISPLAY "This test must be rewritten by NIST (in the - " event of" DISPLAY "a validation) to accomodate - " implementation-defined" DISPLAY "limit on END-IF DISPLAY " " DISPLAY "dstmt=""SELECT * FROM BANKTAB""" MOVE "SELECT * FROM BANKTAB - " " TO dstmt DISPLAY "PREPARE S12421 FROM :dstmt;" EXEC SQL PREPARE S12421 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DESCRIBE OUTPUT S12421 USING SQL DESCRIPTOR - " 'D12421';" EXEC SQL DESCRIBE OUTPUT S12421 USING SQL DESCRIPTOR 'D12421' END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Doctor results of describe to "match" host language variables *Implementation-dependent precisions, etc. DISPLAY "SET DESCRIPTOR 'D12421' VALUE 1" DISPLAY " LENGTH = 20;" EXEC SQL SET DESCRIPTOR 'D12421' VALUE 1 LENGTH = 20 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " * TYPE is already 2 DISPLAY "SET DESCRIPTOR 'D12421' VALUE 2" DISPLAY " PRECISION = 8, SCALE = 4;" EXEC SQL SET DESCRIPTOR 'D12421' VALUE 2 PRECISION = 8, SCALE = 4 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "SET DESCRIPTOR 'D12421' VALUE 3" DISPLAY " TYPE = 2, PRECISION = 8, SCALE = 4;" EXEC SQL SET DESCRIPTOR 'D12421' VALUE 3 TYPE = 2, PRECISION = 8, SCALE = 4 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "SET DESCRIPTOR 'D12421' VALUE 6" DISPLAY " TYPE = 2, PRECISION = 8, SCALE = 4;" EXEC SQL SET DESCRIPTOR 'D12421' VALUE 6 TYPE = 2, PRECISION = 8, SCALE = 4 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "SET DESCRIPTOR 'D12421' VALUE 7" DISPLAY " TYPE = 2, PRECISION = 8, SCALE = 4;" EXEC SQL SET DESCRIPTOR 'D12421' VALUE 7 TYPE = 2, PRECISION = 8, SCALE = 4 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "SET DESCRIPTOR 'D12421' VALUE 8" DISPLAY " TYPE = 2, PRECISION = 8, SCALE = 4;" EXEC SQL SET DESCRIPTOR 'D12421' VALUE 8 TYPE = 2, PRECISION = 8, SCALE = 4 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C12421 CURSOR FOR S12421;" EXEC SQL DECLARE C12421 CURSOR FOR S12421 END-EXEC DISPLAY " " DISPLAY "OPEN C12421;" EXEC SQL OPEN C12421 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "FETCH C12421 INTO SQL DESCRIPTOR 'D12421';" EXEC SQL FETCH C12421 INTO SQL DESCRIPTOR 'D12421' END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "GET DESCRIPTOR 'D12421' :int1 = COUNT;" EXEC SQL GET DESCRIPTOR 'D12421' :int1 = COUNT END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 8; its value is ", int1 if (int1 NOT = 8) then MOVE 0 TO flag END-IF COMPUTE int1 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12421' VALUE 1" DISPLAY " :int1 = INDICATOR, :cname = DATA;" EXEC SQL GET DESCRIPTOR 'D12421' VALUE 1 :int1 = INDICATOR, :cname = DATA END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 0; its value is ", int1 DISPLAY "cname should be 'Bubba '; its value - " is '", cname "'" if (int1 NOT = 0 OR cname NOT = "Bubba - " ") then MOVE 0 TO flag END-IF COMPUTE int1 = -1 COMPUTE flt1 = -1.0 DISPLAY "GET DESCRIPTOR 'D12421' VALUE 2" DISPLAY " :int1 = INDICATOR, :flt1 = DATA;" EXEC SQL GET DESCRIPTOR 'D12421' VALUE 2 :int1 = INDICATOR, :flt1 = DATA END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 0; its value is ", int1 MOVE flt1 TO FLT-1 DISPLAY "flt1 should be 524.07 +-.005; its value is ", FLT-1 if (int1 NOT = 0 OR flt1 < 524.065 OR flt1 > 524.075) then MOVE 0 TO flag END-IF COMPUTE int1 = -1 COMPUTE flt1 = -1.0 DISPLAY "GET DESCRIPTOR 'D12421' VALUE 3" DISPLAY " :int1 = INDICATOR, :flt1 = DATA;" EXEC SQL GET DESCRIPTOR 'D12421' VALUE 3 :int1 = INDICATOR, :flt1 = DATA END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 0; its value is ", int1 MOVE flt1 TO FLT-1 DISPLAY "flt1 should be 2.42 +-.005; its value is ", FLT-1 if (int1 NOT = 0 OR flt1 < 2.415 OR flt1 > 2.425) then MOVE 0 TO flag END-IF COMPUTE int1 = -1 COMPUTE int2 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12421' VALUE 4" DISPLAY " :int1 = INDICATOR, :bin9 = DATA;" EXEC SQL GET DESCRIPTOR 'D12421' VALUE 4 :int1 = INDICATOR, :bin9 = DATA END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 0; its value is ", int1 MOVE bin9 TO BIN-1 DISPLAY "bin9 should be 13; its value is ", BIN-1 if (int1 NOT = 0 OR bin9 NOT = 13) then MOVE 0 TO flag END-IF COMPUTE int1 = -1 COMPUTE int2 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12421' VALUE 5" DISPLAY " :int1 = INDICATOR, :bin2 = DATA;" EXEC SQL GET DESCRIPTOR 'D12421' VALUE 5 :int1 = INDICATOR, :bin2 = DATA END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 0; its value is ", int1 MOVE bin2 TO BIN-1 DISPLAY "bin2 should be 1; its value is ", BIN-1 if (int1 NOT = 0 OR bin2 NOT = 1) then MOVE 0 TO flag END-IF COMPUTE int1 = -1 COMPUTE flt1 = -1.0 DISPLAY "GET DESCRIPTOR 'D12421' VALUE 6" DISPLAY " :int1 = INDICATOR, :flt1 = DATA;" EXEC SQL GET DESCRIPTOR 'D12421' VALUE 6 :int1 = INDICATOR, :flt1 = DATA END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 0; its value is ", int1 MOVE flt1 TO FLT-1 DISPLAY "flt1 should be 3.0 +-.05; its value is ", FLT-1 if (int1 NOT = 0 OR flt1 < 2.95 OR flt1 > 3.05) then MOVE 0 TO flag END-IF COMPUTE int1 = -1 COMPUTE flt1 = -1.0 DISPLAY "GET DESCRIPTOR 'D12421' VALUE 7" DISPLAY " :int1 = INDICATOR, :flt1 = DATA;" EXEC SQL GET DESCRIPTOR 'D12421' VALUE 7 :int1 = INDICATOR, :flt1 = DATA END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 0; its value is ", int1 MOVE flt1 TO FLT-1 DISPLAY "flt1 should be 3.1 +-.05; its value is ", FLT-1 if (int1 NOT = 0 OR flt1 < 3.05 OR flt1 > 3.15) then MOVE 0 TO flag END-IF COMPUTE int1 = -1 COMPUTE flt1 = -1.0 DISPLAY "GET DESCRIPTOR 'D12421' VALUE 8" DISPLAY " :int1 = INDICATOR, :flt1 = DATA;" EXEC SQL GET DESCRIPTOR 'D12421' VALUE 8 :int1 = INDICATOR, :flt1 = DATA END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 0; its value is ", int1 MOVE flt1 TO FLT-1 DISPLAY "flt1 should be -.256 +-.0005; its value is ", FLT-1 if (int1 NOT = 0 OR flt1 < -.2565 OR flt1 > -.2555) then MOVE 0 TO flag END-IF *17.4 GR 4, no data (SQLSTATE 02000) if <item number> > COUNT COMPUTE int1 = -1 DISPLAY "GET DESCRIPTOR 'D12421' VALUE 9" DISPLAY " :int1 = INDICATOR;" EXEC SQL GET DESCRIPTOR 'D12421' VALUE 9 :int1 = INDICATOR END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "SQLCODE should be 100; its value is ", SQL-COD DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE 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 " " *Implicit casting: 17.9 GR.7 *Substitute integer for real DISPLAY "SET DESCRIPTOR 'D12421' VALUE 2" DISPLAY " TYPE = 4;" EXEC SQL SET DESCRIPTOR 'D12421' VALUE 2 TYPE = 4 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "CLOSE C12421;" EXEC SQL CLOSE C12421 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "OPEN C12421;" EXEC SQL OPEN C12421 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "FETCH C12421 INTO SQL DESCRIPTOR 'D12421';" EXEC SQL FETCH C12421 INTO SQL DESCRIPTOR 'D12421' END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Make sure it didn't munge the settings COMPUTE int1 = -1 COMPUTE int2 = -1 COMPUTE int3 = -1 COMPUTE bin9 = -1 COMPUTE int6 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12421' VALUE 2" DISPLAY " :int1 = TYPE, :int2 = NULLABLE, :int3 = - " UNNAMED," DISPLAY " :cname = NAME, :int6 = INDICATOR, :bin9 = DATA;" EXEC SQL GET DESCRIPTOR 'D12421' VALUE 2 :int1 = TYPE, :int2 = NULLABLE, :int3 = UNNAMED, :cname = NAME, :int6 = INDICATOR, :bin9 = DATA END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 4 (INTEGER); its value is ", int1 DISPLAY "int2 should be 1 (NULLABLE); its value is ", int2 DISPLAY "int3 should be 0 (named); its value is ", int3 DISPLAY "cname should be 'BALANCE '; its value - " is '", cname "'" MOVE bin9 TO BIN-1 DISPLAY "bin9 should be 524; its value is ", BIN-1 DISPLAY "int6 should be 0; its value is ", int6 if (int1 NOT = 4 OR int2 NOT = 1 OR int3 NOT = 0) then MOVE 0 TO flag END-IF if (bin9 NOT = 524 OR int6 NOT = 0) then MOVE 0 TO flag END-IF if (cname NOT = "BALANCE ") then MOVE 0 TO flag END-IF *Test null DISPLAY "CLOSE C12421;" EXEC SQL CLOSE C12421 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "UPDATE BANKTAB SET BALANCE = NULL;" EXEC SQL UPDATE BANKTAB SET BALANCE = NULL END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "OPEN C12421;" EXEC SQL OPEN C12421 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "FETCH C12421 INTO SQL DESCRIPTOR 'D12421';" EXEC SQL FETCH C12421 INTO SQL DESCRIPTOR 'D12421' END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 COMPUTE int2 = -1 COMPUTE int3 = -1 COMPUTE int6 = -1 COMPUTE bin9 = -1 MOVE "xxxxxxxxxxxxxxxxxxxx" TO cname DISPLAY "GET DESCRIPTOR 'D12421' VALUE 2" DISPLAY " :int1 = TYPE, :int2 = NULLABLE, :int3 = - " UNNAMED," DISPLAY " :cname = NAME, :int6 = INDICATOR, :bin9 = DATA;" EXEC SQL GET DESCRIPTOR 'D12421' VALUE 2 :int1 = TYPE, :int2 = NULLABLE, :int3 = UNNAMED, :cname = NAME, :int6 = INDICATOR, :bin9 = DATA END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 4 (INTEGER); its value is ", int1 DISPLAY "int2 should be 1 (NULLABLE); its value is ", int2 DISPLAY "int3 should be 0 (named); its value is ", int3 DISPLAY "cname should be 'BALANCE '; its value - " is '", cname "'" DISPLAY "int6 should be -1; its value is ", int6 if (int1 NOT = 4 OR int2 NOT = 1 OR int3 NOT = 0) then MOVE 0 TO flag END-IF if (int6 NOT = -1) then MOVE 0 TO flag END-IF if (cname NOT = "BALANCE ") then MOVE 0 TO flag END-IF . P198. DISPLAY "ROLLBACK WORK;" EXEC SQL ROLLBACK WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *TEd Hook #1 DISPLAY "DROP TABLE BANKTAB CASCADE;" EXEC SQL DROP TABLE BANKTAB CASCADE END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *TEd Hook #2 if ( flag = 1 ) then DISPLAY " *** pass *** " EXEC SQL INSERT INTO HU.TESTREPORT VALUES('0654','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY " dml124.pco *** fail *** " EXEC SQL INSERT INTO HU.TESTREPORT VALUES('0654','fail','PCO') END-EXEC MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD ******************** END TEST0654 ******************** ******************** BEGIN TEST0655 ******************* MOVE 1 TO flag DISPLAY " TEST0655 " DISPLAY " Descriptors: USING SQL DESCRIPTOR" DISPLAY "References:" DISPLAY " F# 1 -- Dynamic SQL" DISPLAY " 17.9 -- DISPLAY " 17.10 -- DISPLAY " 17.14 -- DISPLAY " 17.1 -- Description of SQL item descriptor - " areas" DISPLAY " 17.2 GR.3 -- limit on descriptor areas is" DISPLAY " implementation-defined" DISPLAY " 6.1 -- " DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE *TEd Hook #1 DISPLAY "CREATE TABLE BANKTAB (" DISPLAY " NAAM CHAR (10), BALANCE NUMERIC (7, 2)," DISPLAY " INTEREST DECIMAL (3, 2), TRANSACTIONS INT," DISPLAY " PAYMENTS SMALLINT, INTEREST_RATE FLOAT," DISPLAY " ANNUAL_YIELD REAL, FRACTIONAL_CENTS DOUBLE - " PRECISION);" EXEC SQL CREATE TABLE BANKTAB ( NAAM CHAR (10), BALANCE NUMERIC (7, 2), INTEREST DECIMAL (3, 2), TRANSACTIONS INT, PAYMENTS SMALLINT, INTEREST_RATE FLOAT, ANNUAL_YIELD REAL, FRACTIONAL_CENTS DOUBLE PRECISION) END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *TEd Hook #2 DISPLAY "ALLOCATE DESCRIPTOR 'D12431' WITH MAX 8;" EXEC SQL ALLOCATE DESCRIPTOR 'D12431' WITH MAX 8 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK if (SQLSTATE = "07009") then DISPLAY "Received SQLSTATE 07009!" DISPLAY "This test must be rewritten by NIST (in the - " event of" DISPLAY "a validation) to accomodate - " implementation-defined" DISPLAY "limit on END-IF DISPLAY " " *Now we're going to do implicit casting in the other direction DISPLAY "SET DESCRIPTOR 'D12431' COUNT = 8;" EXEC SQL SET DESCRIPTOR 'D12431' COUNT = 8 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Name is NULL DISPLAY "SET DESCRIPTOR 'D12431' VALUE 1" DISPLAY " TYPE = 2, PRECISION = 3, SCALE = 0, INDICATOR = - " -1;" EXEC SQL SET DESCRIPTOR 'D12431' VALUE 1 TYPE = 2, PRECISION = 3, SCALE = 0, INDICATOR = -1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 3785.2 TO flt1 DISPLAY "SET DESCRIPTOR 'D12431' VALUE 2" DISPLAY " DATA = :flt1, TYPE = 2," DISPLAY " PRECISION = 8, SCALE = 4," DISPLAY " INDICATOR = 0;" EXEC SQL SET DESCRIPTOR 'D12431' VALUE 2 DATA = :flt1, TYPE = 2, PRECISION = 8, SCALE = 4, INDICATOR = 0 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 1 TO int1 MOVE 4 TO int2 MOVE 0 TO int3 DISPLAY "int1 = 1" DISPLAY "int2 = 4" DISPLAY "int3 = 0" MOVE "7.67" TO c4 DISPLAY "SET DESCRIPTOR 'D12431' VALUE 3" DISPLAY " TYPE = :int1, LENGTH = :int2," DISPLAY " INDICATOR = :int3, DATA = :c4;" EXEC SQL SET DESCRIPTOR 'D12431' VALUE 3 TYPE = :int1, LENGTH = :int2, INDICATOR = :int3, DATA = :c4 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 4 TO int1 DISPLAY "int1 = 4" MOVE 5 TO int2 DISPLAY "SET DESCRIPTOR 'D12431' VALUE :int1" DISPLAY " TYPE = 2, PRECISION = 9, INDICATOR = 0, " DISPLAY " DATA = :int2;" EXEC SQL SET DESCRIPTOR 'D12431' VALUE :int1 TYPE = 2, PRECISION = 9, INDICATOR = 0, DATA = :int2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 0 TO int2 DISPLAY "SET DESCRIPTOR 'D12431' VALUE 5" DISPLAY " TYPE = 2, PRECISION = 9, " DISPLAY " INDICATOR = 0, DATA = :int2;" EXEC SQL SET DESCRIPTOR 'D12431' VALUE 5 TYPE = 2, PRECISION = 9, SCALE = 0, INDICATOR = 0, DATA = :int2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "010.95" TO c6 DISPLAY "SET DESCRIPTOR 'D12431' VALUE 6" DISPLAY " TYPE = 1, LENGTH = 6," DISPLAY " INDICATOR = 0, DATA = :c6" EXEC SQL SET DESCRIPTOR 'D12431' VALUE 6 TYPE = 1, LENGTH = 6, INDICATOR = 0, DATA = :c6 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *17.5 GR.5.a: character set fields are automatically set MOVE 2 TO int1 DISPLAY "int1 = 2" MOVE "11" TO c2 DISPLAY "c2 = '", c2 "'" DISPLAY "SET DESCRIPTOR 'D12431' VALUE 7" DISPLAY " TYPE = 1, INDICATOR = 0, LENGTH = :int1, DATA = - " :c2;" EXEC SQL SET DESCRIPTOR 'D12431' VALUE 7 TYPE = 1, INDICATOR = 0, LENGTH = :int1, DATA = :c2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE .3 TO flt1 DISPLAY "SET DESCRIPTOR 'D12431' VALUE 8 TYPE = 2," DISPLAY " PRECISION = 8, SCALE = 4," DISPLAY " INDICATOR = 0, DATA = :flt1;" EXEC SQL SET DESCRIPTOR 'D12431' VALUE 8 TYPE = 2, PRECISION = 8, SCALE = 4, INDICATOR = 0, DATA = :flt1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Here goes nothing DISPLAY "dstmt=""INSERT INTO BANKTAB VALUES - " (?,?,?,?,?,?,?,?)""" MOVE "INSERT INTO BANKTAB VALUES (?,?,?,?,?,?,?,?) " TO dstmt DISPLAY "PREPARE S12431 FROM :dstmt;" EXEC SQL PREPARE S12431 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE S12431 USING SQL DESCRIPTOR 'D12431';" EXEC SQL EXECUTE S12431 USING SQL DESCRIPTOR 'D12431' END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Verify results without using descriptors COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM BANKTAB" DISPLAY " WHERE NAAM IS NULL" DISPLAY " AND BALANCE >= 3785.19 AND BALANCE <= 3785.21" DISPLAY " AND INTEREST = 7.67" DISPLAY " AND TRANSACTIONS = 5" DISPLAY " AND PAYMENTS = 0" DISPLAY " AND INTEREST_RATE >= 10.94 AND INTEREST_RATE <= - " 10.96" DISPLAY " AND ANNUAL_YIELD >= 10.99 AND ANNUAL_YIELD <= - " 11.01" DISPLAY " AND FRACTIONAL_CENTS >= 0.29 AND - " FRACTIONAL_CENTS <= 0.31;" EXEC SQL SELECT COUNT(*) INTO :int1 FROM BANKTAB WHERE NAAM IS NULL AND BALANCE >= 3785.19 AND BALANCE <= 3785.21 AND INTEREST = 7.67 AND TRANSACTIONS = 5 AND PAYMENTS = 0 AND INTEREST_RATE >= 10.94 AND INTEREST_RATE <= 10.96 AND ANNUAL_YIELD >= 10.99 AND ANNUAL_YIELD <= 11.01 AND FRACTIONAL_CENTS >= 0.29 AND FRACTIONAL_CENTS <= 0.31 END-EXEC 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 *Now substitute some equal values in various fields in the *descriptor and test for equality. DISPLAY "SET DESCRIPTOR 'D12431' VALUE 1" DISPLAY " TYPE = 6, PRECISION = 1, INDICATOR = -1;" EXEC SQL SET DESCRIPTOR 'D12431' VALUE 1 TYPE = 6, PRECISION = 1, INDICATOR = -1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 0 TO bin2 DISPLAY "bin2 = 0" MOVE 5 TO smint1 DISPLAY "SET DESCRIPTOR 'D12431' VALUE 4" DISPLAY " TYPE = 5, INDICATOR = :bin2, DATA = :smint1;" EXEC SQL SET DESCRIPTOR 'D12431' VALUE 4 TYPE = 5, INDICATOR = :bin2, DATA = :smint1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 0 TO bin9 MOVE 0 TO smint1 DISPLAY "bin9 = 0" DISPLAY "SET DESCRIPTOR 'D12431' VALUE 5" DISPLAY " TYPE = 5, INDICATOR = :bin9, DATA = :smint1;" EXEC SQL SET DESCRIPTOR 'D12431' VALUE 5 TYPE = 5, INDICATOR = :bin9, DATA = :smint1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "010.950" TO c7 DISPLAY "SET DESCRIPTOR 'D12431' VALUE 6" DISPLAY " TYPE = 1, LENGTH = 7," DISPLAY " INDICATOR = 0, DATA = :c7;" EXEC SQL SET DESCRIPTOR 'D12431' VALUE 6 TYPE = 1, LENGTH = 7, INDICATOR = 0, DATA = :c7 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT(*) FROM BANKTAB' || ' WHERE (NAAM = ? OR NAAM IS NULL) AND BALANCE = ?' || ' AND INTEREST = ? AND TRANSACTIONS = ?' || ' AND PAYMENTS = ? AND INTEREST_RATE = ?' || ' AND ANNUAL_YIELD = ? AND FRACTIONAL_CENTS = ?' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S12432 FROM :longst;" --> -------------------- --> maximum size reached --> -------------------- ¤ Diese beiden folgenden Angebotsgruppen bietet das Unternehmen0.49Angebot Wie Sie bei der Firma Beratungs- und Dienstleistungen beauftragen können ¤*Eine klare Vorstellung vom Zielzustand |
|
2026-03-28