IDENTIFICATION DIVISION .PROGRAM-ID . YTS813.ENVIRONMENT DIVISION .CONFIGURATION SECTION .SOURCE-COMPUTER . xyz.OBJECT-COMPUTER . xyz.DATA DIVISION .WORKING-STORAGE SECTION . * EMBEDDED COBOL (file "YTS813.PCO") *Copyright 1996 National Computing Centre Ltd, *and Computer Logic R&D S.A *on behalf of 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 * * YTS813.PCO * WRITTEN BY: Susan Watters * TRANSLATED AUTOMATICALLY FROM EMBEDDED C BY CHRIS SCHANZLE * * This routine tests <null predicate> with * (numeric value expression> in <row value constructor> * * * REFERENCES * 8.6 GR2 <null predicate> - IS NULL * 8.6 GR3 <null predicate> - IS NOT NULL * 6.12 GR1 <numeric value expression> * 6.2 GR7 <value specification> and <target specification> * 13.5 GR2b <select statement : single row> * F#48 Expanded null predicate * * DATE LAST ALTERED 02.01.96 CTS5 Hand-over Test * * QA Status: Full FC * * Revised by DWF 1996-04-03 * Added rollback after authid * Removed EXEC SQL from printfs * Renamed variable * Fixed expected results **************************************************************** EXEC SQL BEGIN DECLARE SECTION END-EXEC PIC S9(9) COMP .PIC X(5).PIC S9(4) DISPLAY SIGN LEADING SEPARATE .PIC S9(4) DISPLAY SIGN LEADING SEPARATE .PIC X(18).PIC X(18).EXEC SQL END DECLARE SECTION END-EXEC PIC S9(9) DISPLAY SIGN LEADING SEPARATE .PIC S9(9) DISPLAY SIGN LEADING SEPARATE .VALUE IS "01234ABCDEFGH56789IJKLMNOPQRSTUVWXYZ" .PIC X OCCURS 36 TIMES.PIC X OCCURS 5 TIMES.PIC S9(9) DISPLAY SIGN LEADING SEPARATE . *date_time declaration PIC 9(6).PIC 9(8).PIC S9(9) DISPLAY SIGN LEADING SEPARATE .PIC S9(9) DISPLAY SIGN LEADING SEPARATE .PROCEDURE DIVISION .MOVE "CTS1 " TO uidCALL "AUTHID" USING uidMOVE "not logged in, not" TO uidxEXEC SQL SELECT USER INTO :uidx FROM CTS1.ECCO END-EXEC MOVE SQLCODE TO SQL-CODEXEC SQL ROLLBACK WORK END-EXEC MOVE SQLCODE TO SQL-CODif (uid NOT = uidx) then DISPLAY "ERROR: User " , uid, " expected. User " , uidx, " " connected" STOP RUN END-IF MOVE 0 TO errcntDISPLAY "SQL Test Suite, V6.0, Embedded COBOL, yts813.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 ******************** BEGIN TEST7570 ******************* MOVE 1 TO flagDISPLAY " TEST7570 " DISPLAY " , in " cons>" DISPLAY "References:" DISPLAY " 8.6 GR2 - IS NULL" DISPLAY " 8.6 GR3 - IS NOT NULL" DISPLAY " 6.12 GR1 " DISPLAY " 6.2 GR7 and" DISPLAY " " DISPLAY " 13.5 GR2b DISPLAY " F#48 Expanded null predicate" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1MOVE "xxxxx" TO SQLSTATE *Prepare database for test DISPLAY "INSERT INTO CTS1.PROJ_DURATION (MONTHS, " EMP_HOURS)" DISPLAY "VALUES (1, 2);" EXEC SQL INSERT INTO CTS1.PROJ_DURATION (MONTHS, EMP_HOURS)END-EXEC MOVE SQLCODE TO SQL-CODPERFORM CHCKOKDISPLAY " " DISPLAY "INSERT INTO CTS1.PROJ_DURATION VALUES (2,3,4);" EXEC SQL INSERT INTO CTS1.PROJ_DURATION VALUES (2,3,4)END-EXEC MOVE SQLCODE TO SQL-CODPERFORM CHCKOKDISPLAY " " DISPLAY "INSERT INTO CTS1.PROJ_DURATION (MONTHS, " TIME_LEFT)" DISPLAY "VALUES (3,5);" EXEC SQL INSERT INTO CTS1.PROJ_DURATION (MONTHS, TIME_LEFT)END-EXEC MOVE SQLCODE TO SQL-CODPERFORM CHCKOKDISPLAY " " DISPLAY "INSERT INTO CTS1.PROJ_DURATION VALUES (4, 6, 7);" EXEC SQL INSERT INTO CTS1.PROJ_DURATION VALUES (4, 6, 7)END-EXEC MOVE SQLCODE TO SQL-CODPERFORM CHCKOKDISPLAY " " *count rows where TIME_LEFT + EMP_HOURS is not NULL *initialise host variable MOVE 99 TO val1DISPLAY "SELECT COUNT (*) INTO :val1" DISPLAY "FROM CTS1.PROJ_DURATION" DISPLAY "WHERE TIME_LEFT + EMP_HOURS IS NOT NULL;" EXEC SQL SELECT COUNT (*) INTO :val1FROM CTS1.PROJ_DURATIONIS NOT NULL END-EXEC MOVE SQLCODE TO SQL-CODPERFORM CHCKOKDISPLAY "value expected was 2, value returned was " , val1if (val1 NOT = 2) then MOVE 0 TO flagEND-IF *declare a cursor to return MONTHS when TIME_LEFT- EMP_HOURS is DISPLAY "DECLARE data813 CURSOR FOR" DISPLAY "SELECT MONTHS FROM CTS1.PROJ_DURATION" DISPLAY "WHERE EMP_HOURS - TIME_LEFT IS NULL" DISPLAY "ORDER BY MONTHS;" EXEC SQL DECLARE data813 CURSOR FOR SELECT MONTHS FROM CTS1.PROJ_DURATIONIS NULL ORDER BY MONTHS2 END-EXEC DISPLAY "OPEN data813;" EXEC SQL OPEN data813 END-EXEC MOVE SQLCODE TO SQL-CODPERFORM CHCKOKDISPLAY " " *fetch all rows defined by cursor *initialise host variables MOVE 99 TO val1DISPLAY "FETCH data813 INTO :val1;" EXEC SQL FETCH data813 INTO :val1 END-EXEC MOVE SQLCODE TO SQL-CODPERFORM CHCKOKDISPLAY "value 1 expected, " , val1, " returned" if (val1 NOT = 1) then MOVE 0 TO flagEND-IF *initialise host variables MOVE 99 TO val1DISPLAY "FETCH data813 INTO :val1;" EXEC SQL FETCH data813 INTO :val1 END-EXEC MOVE SQLCODE TO SQL-CODPERFORM CHCKOKDISPLAY "value 3 expected, " , val1, " returned" if (val1 NOT = 3) then MOVE 0 TO flagEND-IF DISPLAY "FETCH data813 INTO :val1;" EXEC SQL FETCH data813 INTO :val1 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-CODif (SQLCODE NOT = 100) then MOVE 0 TO flagEND-IF DISPLAY "SQLSTATE should be 02000; its value is " , SQLSTATEPERFORM NOSUBCLASS THRU EXIT-NOSUBCLASSif (NORMSQ NOT = "02000" ) then MOVE 0 TO flagEND-IF if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)then DISPLAY "Valid implementation-defined SQLSTATE accepted." END-IF DISPLAY " " *execute update statement *put negative value into null indicator DISPLAY "indic1 = -99;" COMPUTE indic1 = -99 *put random value into host variable DISPLAY "val1 = 34;" MOVE 34 TO val1DISPLAY "UPDATE CTS1.PROJ_DURATION" DISPLAY "SET TIME_LEFT = 9" DISPLAY "WHERE EMP_HOURS * :val1 INDICATOR :indic1 IS NOT " NULL;" EXEC SQL UPDATE CTS1.PROJ_DURATIONSET TIME_LEFT = 9IS NOT NULL END-EXEC MOVE SQLCODE TO SQL-CODDISPLAY "SQLCODE should be 100; its value is " , SQL-CODif (SQLCODE NOT = 100) then MOVE 0 TO flagEND-IF DISPLAY "SQLSTATE should be 02000; its value is " , SQLSTATEPERFORM NOSUBCLASS THRU EXIT-NOSUBCLASSif (NORMSQ NOT = "02000" ) then MOVE 0 TO flagEND-IF if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)then DISPLAY "Valid implementation-defined SQLSTATE accepted." END-IF DISPLAY " " *check no rows have been updated MOVE 99 TO val1DISPLAY "SELECT COUNT (*) INTO :val1" DISPLAY "FROM CTS1.PROJ_DURATION" DISPLAY "WHERE TIME_LEFT = 9;" EXEC SQL SELECT COUNT (*) INTO :val1FROM CTS1.PROJ_DURATIONEND-EXEC MOVE SQLCODE TO SQL-CODPERFORM CHCKOKDISPLAY "value expected is 0, value returned is " , val1if (val1 NOT = 0) then MOVE 0 TO flagEND-IF *execute an update statement where indic1 is positive *initialise host variables DISPLAY "indic1 = 9;" DISPLAY "val1 = 99;" MOVE 9 TO indic1MOVE 99 TO val1DISPLAY "UPDATE CTS1.PROJ_DURATION " DISPLAY "SET EMP_HOURS = 3 " DISPLAY "WHERE EMP_HOURS * :val1 INDICATOR :indic1 IS NOT " NULL;" EXEC SQL UPDATE CTS1.PROJ_DURATION SET EMP_HOURS = 3 IS NOT NULL END-EXEC MOVE SQLCODE TO SQL-CODPERFORM CHCKOKDISPLAY " " *check relevent rows have been updated MOVE 99 TO val1DISPLAY "SELECT COUNT (*) INTO :val1" DISPLAY "FROM CTS1.PROJ_DURATION" DISPLAY "WHERE EMP_HOURS = 3;" EXEC SQL SELECT COUNT (*) INTO :val1FROM CTS1.PROJ_DURATIONEND-EXEC MOVE SQLCODE TO SQL-CODPERFORM CHCKOKDISPLAY "value expected is 3, value returned is " , val1if (val1 NOT = 3) then MOVE 0 TO flagEND-IF *check row containing null value in EMP_HOURS has not been updat *initialise host variables MOVE 99 TO indic1MOVE 99 TO val1DISPLAY "SELECT EMP_HOURS INTO :val1:indic1" DISPLAY "FROM CTS1.PROJ_DURATION" DISPLAY "WHERE MONTHS = 3;" EXEC SQL SELECT EMP_HOURS INTO :val1:indic1FROM CTS1.PROJ_DURATIONEND-EXEC MOVE SQLCODE TO SQL-CODPERFORM CHCKOKDISPLAY "indic1 should be -1; its value is " , indic1if (indic1 NOT = -1) then MOVE 0 TO flagEND-IF *ensure database is left in a consistent state DISPLAY "ROLLBACK WORK" EXEC SQL ROLLBACK WORK END-EXEC MOVE SQLCODE TO SQL-CODPERFORM CHCKOKDISPLAY " " if ( flag = 1 ) then DISPLAY " yts813.pco *** pass *** " EXEC SQL INSERT INTO CTS1.TESTREPORT'7570' ,'pass' ,'PCO' ) END-EXEC MOVE SQLCODE TO SQL-CODelse DISPLAY " yts813.pco *** fail *** " EXEC SQL INSERT INTO CTS1.TESTREPORT'7570' ,'fail' ,'PCO' ) END-EXEC MOVE SQLCODE TO SQL-CODCOMPUTE errcnt = errcnt + 1END-IF DISPLAY "===============================================" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD ******************** END TEST7570 ******************** **** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOP RUN . * **** Procedures for PERFORM statements *Test SQLCODE and SQLSTATE for normal completion. DISPLAY "SQLCODE should be 0; its value is " , SQL-CODDISPLAY "SQLSTATE should be 00000; its value is " , SQLSTATEPERFORM NOSUBCLASS THRU EXIT-NOSUBCLASSif (SQLCODE NOT = 0 OR NORMSQ NOT = "00000" ) then MOVE 0 TO flagEND-IF if (NORMSQ = "00000" AND NORMSQ NOT = SQLSTATE)then DISPLAY "Valid implementation-defined SQLSTATE accepted." END-IF *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 NORMSQMOVE 3 TO norm1 *subclass begins in position 3 of char array NORMSQ *valid subclass begins with 5-9, I-Z, end of ALPNUM table PERFORM VARYING norm2 FROM 14 BY 1 UNTIL norm2 > 36if (NORMSQX(norm1) = ALPNUM(norm2)) then MOVE "0" TO NORMSQX(norm1)END-IF END-PERFORM *Quit if NORMSQ is unchanged. Subclass is not impl.-def. *Changed NORMSQ means implementation-defined subclass, *so proceed to zero it out, if valid (0-9,A-Z) if (NORMSQ = SQLSTATE) then GO TO EXIT-NOSUBCLASSEND-IF MOVE 4 TO norm1 *examining position 4 of char array NORMSQ *valid characters are 0-9, A-Z PERFORM VARYING norm2 FROM 1 BY 1 UNTIL norm2 > 36if (NORMSQX(norm1) = ALPNUM(norm2)) then MOVE "0" TO NORMSQX(norm1)END-IF END-PERFORM MOVE 5 TO norm1 *valid characters are 0-9, A-Z *examining position 5 of char array NORMSQ PERFORM VARYING norm2 FROM 1 BY 1 UNTIL norm2 > 36if (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 . ¤ Dauer der Verarbeitung: 0.34 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.
