|
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
--> --------------------
¤ Dauer der Verarbeitung: 0.138 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.
|
