*Copyright 1996 National Computing Centre Limited, *and Computer Logic R&D S.A *on behalf of the CTS5 SQL2 Project. *All rights reserved. *The CTS5 SQL2 Project is sponsored by the European Community. * *The National Computing Centre Limited and Computer Logic R&D *have given permission to NIST to distribute this program *over the World Wide Web in order to promote SQL standards. *DISCLAIMER: *This program was reviewed by employees of NIST for *conformance to the SQL standards. *NIST assumes no responsibility for any party's use of *this program.
**************************************************************** * * COMMENT SECTION * * SQL VALIDATION TEST SUITE V6.0 * * YTS762.PCO * WRITTEN BY: Susan Watters * TRANSLATED AUTOMATICALLY FROM EMBEDDED C BY CHRIS SCHANZLE * * This routine tests <query expression> with EXCEPT * * * REFERENCES * 7.10 <query expression> * 7.10 GR.1b * 7.3 <table expression> * 13.1 <declare cursor> * 13.5 <select statement : single row> * F#30 Table operations * * DATE LAST ALTERED 02.01.96 CTS5 Hand-over Test * flags correctly initialised 20/11/95 * EXCEPT ALL statements updated to make testing more rigourous * * QA Status: Full FC * * Revised by DWF 1996-03-06 * Added rollback after authid * Cleanup * Removed EXEC SQL from inside printf * Fixed string lengths * Fixed SQL syntax error * Tightened pass criteria * Fixed expected results * More output ****************************************************************
MOVE"CTS1 "TO uid CALL"AUTHID"USING uid MOVE"not logged in, not"TO uidx EXECSQLSELECT USER INTO :uidx FROM CTS1.ECCO END-EXEC MOVE SQLCODE TO SQL-COD EXECSQL ROLLBACK WORK END-EXEC MOVE SQLCODE TO SQL-COD if (uid NOT = uidx) then DISPLAY"ERROR: User ", uid, " expected. User ", uidx, "
- " connected" STOPRUN END-IF MOVE 0 TO errcnt
DISPLAY"SQL Test Suite, V6.0, Embedded COBOL, yts762.pco" DISPLAY "59-byte ID" DISPLAY"TEd Version #" *date_time print ACCEPT TO-DAY FROMDATE ACCEPT THE-TIME FROMTIME DISPLAY"Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME
******************** BEGIN TEST7517 ******************* MOVE 1 TO flag
*initialise variables MOVE 1 TO flag3 MOVE 1 TO flag4 MOVE 1 TO flag5
*STAFF1 and STAFF4 are two tables where *the data type of the i-th col of STAFF1 and the data type of *the i-th column of STAFF4 are comparable. STAFF1 *contains three or more copies of some rows, two *copies of other rows and some rows that are not duplicated.
*STAFF4 contains some rows that are duplicates of rows in *STAFF1 and some that are not. Cases are included where the *multiplicity of a row is more than, equal to and less than *the multiplicity of that row in STAFF1
DISPLAY"Insert values into CTS1.STAFF1 and CTS1.STAFF4"
*insert row into STAFF1 three times
MOVE 0 TO j PERFORM P50 UNTIL j > 2
*insert row into STAFF4 three times *same number of repetitions as STAFF1
MOVE 0 TO j PERFORM P49 UNTIL j > 2
*insert row into STAFF1 three times
MOVE 0 TO j PERFORM P48 UNTIL j > 2
*insert row into STAFF4 twice *fewer repetitions than STAFF1
MOVE 0 TO j PERFORM P47 UNTIL j > 1
*insert a row into STAFF1 twice
MOVE 0 TO j PERFORM P46 UNTIL j > 1
*insert a row into STAFF1 twice
MOVE 0 TO j PERFORM P45 UNTIL j > 1
*insert one row into STAFF1
DISPLAY"INSERT INTO CTS1.STAFF1 VALUES" DISPLAY"('E11','Keith',10,'Vienna');" EXECSQLINSERTINTO CTS1.STAFF1 VALUES
('E11','Keith',10,'Vienna') END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*insert row into STAFF4 twice *more repetitions than STAFF1
MOVE 0 TO j PERFORM P44 UNTIL j > 1
*insert row into STAFF1 once
DISPLAY"INSERT INTO CTS1.STAFF1 VALUES" DISPLAY"('E12','Laura',20,'Deale');" EXECSQLINSERTINTO CTS1.STAFF1 VALUES
('E12','Laura',20,'Deale') END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*insert row into STAFF4 *this row is unique to STAFF4
DISPLAY"INSERT INTO CTS1.STAFF4 VALUES" DISPLAY"('E13','Mike',30,'Vienna');" EXECSQLINSERTINTO CTS1.STAFF4 VALUES
('E13','Mike',30,'Vienna') END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*declare cursor DISPLAY"DECLARE data62 CURSOR FOR " DISPLAY"SELECT * FROM CTS1.STAFF1" DISPLAY"EXCEPT SELECT * FROM CTS1.STAFF4;" EXECSQL DECLARE data62 CURSOR FOR SELECT * FROM CTS1.STAFF1
-- EXCEPT SELECT * FROM CTS1.STAFF4 END-EXEC
DISPLAY"Should get these three rows, in any order:" DISPLAY"E9 Imogen 10 Prague" DISPLAY"E10 John 20 Brussels" DISPLAY"E12 Laura 20 Deale"
*Fetch rows defined by cursor MOVE 0 TO i PERFORM P43 UNTIL i > 2
if (flag3 NOT = 0 OR flag4 NOT = 0 OR flag5 NOT =
0) then DISPLAY"Values fetched were incorrect" MOVE 0 TO flag END-IF
*check no data condition is raised DISPLAY"FETCH data62 INTO :eno, :ename, :egrad, :ecit" EXECSQL FETCH data62 INTO :eno, :ename, :egrad, :ecit END-EXEC MOVE SQLCODE TO SQL-COD
*That should generate a no data exception *which is SQLSTATE 02000
DISPLAY"SQLCODE should be 100; its value is ", SQL-COD if (SQLCODE NOT = 100) then MOVE 0 TO flag END-IF
DISPLAY"SQLSTATE should be 02000; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (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" "
DISPLAY"CLOSE data62" EXECSQLCLOSE data62 END-EXEC MOVE SQLCODE TO SQL-COD
*first check for EXCEPT ALL
DISPLAY"INSERT INTO CTS1.ET (col1, col2, col3, col4)" DISPLAY"SELECT * FROM CTS1.STAFF1 EXCEPT ALL" DISPLAY"SELECT * FROM CTS1.STAFF4;" EXECSQLINSERTINTO CTS1.ET (col1, col2, col3, col4) SELECT * FROM CTS1.STAFF1 -- EXCEPT ALL
-- SELECT * FROM CTS1.STAFF4 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
MOVE 0 TO j DISPLAY"SELECT COUNT (DISTINCT COL1) INTO :j FROM
- " CTS1.ET;" EXECSQLSELECTCOUNT (DISTINCT COL1) INTO :j FROM CTS1.ET END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"j should be 4; its value is ", j if (j NOT = 4) then MOVE 0 TO flag END-IF
MOVE 0 TO j DISPLAY"SELECT COUNT (*) INTO :j FROM CTS1.ET;" EXECSQLSELECTCOUNT (*) INTO :j FROM CTS1.ET END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"j should be 6; its value is ", j if (j NOT = 6) then MOVE 0 TO flag END-IF
*Second test For EXCEPT ALL
DISPLAY"DELETE FROM CTS1.ET;" EXECSQLDELETEFROM CTS1.ET END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
DISPLAY"INSERT INTO CTS1.ET (col1, col2, col3, col4)" DISPLAY"SELECT DISTINCT * FROM CTS1.STAFF1 EXCEPT ALL" DISPLAY" SELECT * FROM CTS1.STAFF4);" EXECSQLINSERTINTO CTS1.ET (col1, col2, col3, col4) SELECT * FROM CTS1.STAFF1 --EXCEPT ALL
-- SELECT * FROM CTS1.STAFF4 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
MOVE 0 TO j DISPLAY"SELECT COUNT (*) INTO :j FROM CTS1.ET;" EXECSQLSELECTCOUNT (*) INTO :j FROM CTS1.ET END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"j should be 3; its value is ", j if (j NOT = 3) then MOVE 0 TO flag END-IF
*Third test For EXCEPT ALL
DISPLAY"DELETE FROM CTS1.ET;" EXECSQLDELETEFROM CTS1.ET END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
DISPLAY"INSERT INTO CTS1.ET (col1, col2, col3, col4)" DISPLAY"SELECT * FROM CTS1.STAFF1 EXCEPT ALL" DISPLAY" SELECT DISTINCT * FROM CTS1.STAFF4);" EXECSQLINSERTINTO CTS1.ET (col1, col2, col3, col4) SELECT * FROM CTS1.STAFF1 -- EXCEPT ALL
--SELECT DISTINCT * FROM CTS1.STAFF4 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
MOVE 0 TO j DISPLAY"SELECT COUNT (*) INTO :j FROM CTS1.ET;" EXECSQLSELECTCOUNT (*) INTO :j FROM CTS1.ET END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"j should be 9; its value is ", j if (j NOT = 9) then MOVE 0 TO flag END-IF
DISPLAY"ROLLBACK WORK;" EXECSQL ROLLBACK WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
if ( flag = 1 ) then DISPLAY" yts762.pco *** pass ***" EXECSQLINSERTINTO CTS1.TESTREPORT
VALUES('7517','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" yts762.pco *** fail ***" EXECSQLINSERTINTO CTS1.TESTREPORT
VALUES('7517','fail','PCO') END-EXEC MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF
EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD ******************** END TEST7517 ********************
**** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOPRUN.
* **** Procedures for PERFORM statements
*Test SQLCODE and SQLSTATE for normal completion.
CHCKOK. DISPLAY"SQLCODE should be 0; its value is ", SQL-COD DISPLAY"SQLSTATE should be 00000; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (SQLCODE NOT = 0 OR NORMSQ NOT = "00000") then MOVE 0 TO flag END-IF if (NORMSQ = "00000"AND NORMSQ NOT = SQLSTATE) then DISPLAY"Valid implementation-defined SQLSTATE accepted." END-IF
.
NOSUBCLASS.
*This routine replaces valid implementation-defined *subclasses with 000. This replacement equates valid *implementation-defined subclasses with the 000 value *expected by the test case; otherwise the test will fail. *After calling NOSUBCLASS, NORMSQ will be tested * SQLSTATE will be printed.
MOVE SQLSTATE TO NORMSQ
MOVE 3 TO norm1 *subclass begins in position 3 of char array NORMSQ *valid subclass begins with 5-9, I-Z, end of ALPNUM table PERFORMVARYING norm2 FROM 14 BY 1 UNTIL norm2 > 36 if (NORMSQX(norm1) = ALPNUM(norm2)) then MOVE"0"TO NORMSQX(norm1) END-IF END-PERFORM
*Quit if NORMSQ is unchanged. Subclass is not impl.-def. *Changed NORMSQ means implementation-defined subclass, *so proceed to zero it out, if valid (0-9,A-Z) if (NORMSQ = SQLSTATE) then GOTO EXIT-NOSUBCLASS END-IF
MOVE 4 TO norm1 *examining position 4 of char array NORMSQ *valid characters are 0-9, A-Z PERFORMVARYING norm2 FROM 1 BY 1 UNTIL norm2 > 36 if (NORMSQX(norm1) = ALPNUM(norm2)) then MOVE"0"TO NORMSQX(norm1) END-IF END-PERFORM
MOVE 5 TO norm1 *valid characters are 0-9, A-Z *examining position 5 of char array NORMSQ PERFORMVARYING norm2 FROM 1 BY 1 UNTIL norm2 > 36 if (NORMSQX(norm1) = ALPNUM(norm2)) then MOVE"0"TO NORMSQX(norm1) END-IF END-PERFORM
*implementation-defined subclasses are allowed for warnings *(class = 01). These equate to successful completion *SQLSTATE values of 00000. *Reference SQL-92 4.28 SQL-transactions, paragraph 2
if (NORMSQX(1) = "0"AND NORMSQX(2) = "1") then MOVE"0"TO NORMSQX(2) END-IF
.
EXIT-NOSUBCLASS. EXIT.
P50. DISPLAY"INSERT INTO CTS1.STAFF1 VALUES" DISPLAY" ('E7','Grace',10,'Paris');" EXECSQLINSERTINTO CTS1.STAFF1 VALUES
('E7','Grace',10,'Paris') END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" " ADD 1 TO j
.
P49. DISPLAY"INSERT INTO CTS1.STAFF4 VALUES" DISPLAY"('E7','Grace',10,'Paris');" EXECSQLINSERTINTO CTS1.STAFF4 VALUES
('E7','Grace',10,'Paris') END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" " ADD 1 TO j
.
P48. DISPLAY"INSERT INTO CTS1.STAFF1 VALUES" DISPLAY"('E8','Henry',20,'Prague');" EXECSQLINSERTINTO CTS1.STAFF1 VALUES
('E8','Henry',20,'Prague') END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" " ADD 1 TO j
.
P47. DISPLAY"INSERT INTO CTS1.STAFF4 VALUES" DISPLAY"('E8','Henry',20,'Prague');" EXECSQLINSERTINTO CTS1.STAFF4 VALUES
('E8','Henry',20,'Prague') END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" " ADD 1 TO j
.
P46. DISPLAY"INSERT INTO CTS1.STAFF1 VALUES" DISPLAY"('E9','Imogen',10,'Prague');" EXECSQLINSERTINTO CTS1.STAFF1 VALUES
('E9','Imogen',10,'Prague') END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" " ADD 1 TO j
.
P45. DISPLAY"INSERT INTO CTS1.STAFF1 VALUES" DISPLAY"('E10','John',20,'Brussels');" EXECSQLINSERTINTO CTS1.STAFF1 VALUES
('E10','John',20,'Brussels') END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" " ADD 1 TO j
.
P44. DISPLAY"INSERT INTO CTS1.STAFF4 VALUES" DISPLAY"('E11','Keith',10,'Vienna');" EXECSQLINSERTINTO CTS1.STAFF4 VALUES
('E11','Keith',10,'Vienna') END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" " ADD 1 TO j
.
P43. MOVE"vvv"TO eno MOVE"yyyyyyyyyyyyyyyyyyyx"TO ename MOVE"zzzzzzzzzzzzzzx"TO ecit MOVE 0 TO egrad DISPLAY"FETCH data62 INTO :eno, :ename, :egrad, :ecit" EXECSQL FETCH data62 INTO :eno, :ename, :egrad, :ecit END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"eno is ", eno DISPLAY"ename is ", ename DISPLAY"egrad is ", egrad DISPLAY"ecit is ", ecit
if (eno = "E9"AND flag3 = 1) then MOVE 0 TO flag3 if (ename NOT = "Imogen") then MOVE 0 TO flag END-IF if (egrad NOT = 10) then MOVE 0 TO flag END-IF if (ecit NOT = "Prague") then MOVE 0 TO flag END-IF END-IF
if (eno = "E10"AND flag4 = 1) then MOVE 0 TO flag4 if (ename NOT = "John") then MOVE 0 TO flag END-IF if (egrad NOT = 20) then MOVE 0 TO flag END-IF if (ecit NOT = "Brussels") then MOVE 0 TO flag END-IF END-IF
if (eno = "E12"AND flag5 = 1) then MOVE 0 TO flag5 if (ename NOT = "Laura") then MOVE 0 TO flag END-IF if (egrad NOT = 20) then MOVE 0 TO flag END-IF if (ecit NOT = "Deale") then MOVE 0 TO flag END-IF END-IF ADD 1 TO i
.
¤ Dauer der Verarbeitung: 0.29 Sekunden
(vorverarbeitet)
¤
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.
