*Copyright 1995 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 * * XTS739.PCO TEST7039 * WRITTEN BY: Nickos Backalidis * TRANSLATED AUTOMATICALLY FROM EMBEDDED C BY CHRIS SCHANZLE * * Scrolled cursor with ORDER BY int, name ASC, * FETCH FIRST, FETCH LAST * * REFERENCES * 13.1 -- <Declare cursor> * 13.1 SR.10b * 13.1 GR.3 * 13.3 -- <fetch statement> * 13.3 GR.2b * 13.3 GR.2c * F#43 -- Scrolled cursors * F#24 -- Keyword relaxations * * DATE LAST ALTERED 18/12/95 CTS5 Hand-over Test * * Cleanups and fixes by V. Kogakis 07/12/95 * Print timestamp * Include Files * Define NOSUBCLASS/CHCKOK at test beginning * * QA STATUS : FC * * Revised by DWF 1996-02-09 * Removed status checks after cursor declarations * Fixed checks for wrong SQLCODE * Fixed char string lengths ****************************************************************
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, xts739.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
*USE table CTS1.CL_STANDARD from the standard schema *declare a scrolled cursor with ORDER BY int,name *ASC and FETCH FIRST, FETCH LAST DISPLAY"DECLARE a SCROLL CURSOR " DISPLAY"FOR SELECT COL_NUM1, COL_CH1, COL_NUM2, COL_CH2" DISPLAY"FROM CL_STANDARD ORDER BY 4, COL_CH1 ASC;" EXECSQL DECLARE a SCROLL CURSOR FORSELECT COL_NUM1,COL_CH1,COL_NUM2,COL_CH2 FROM CL_STANDARD ORDERBY 4, COL_CH1 ASC END-EXEC
DISPLAY"OPEN a;" EXECSQLOPEN a END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*start fetching rows in the cursor 5 in total *in reverse order
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch last row from CL_STANDARD DISPLAY"FETCH LAST FROM a INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH LAST FROM a INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1001; its value is ", varnm1 DISPLAY"COL_CH1 should be MARIA; its value is ", varch1 DISPLAY"COL_NUM2 should be 4001; its value is ", varnm2 DISPLAY"COL_CH2 should be RHODES; its value is ", varch2 if (varnm1 NOT = 1001 OR varnm2 NOT = 4001) then MOVE 0 TO flag END-IF if (varch2 NOT = "RHODES"OR varch1 NOT = "MARIA") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch previous row DISPLAY"FETCH PRIOR FROM a INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH PRIOR FROM a INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1004; its value is ", varnm1 DISPLAY"COL_CH1 should be MORRIS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4004; its value is ", varnm2 DISPLAY"COL_CH2 should be PARGA; its value is ", varch2 if (varnm1 NOT = 1004 OR varnm2 NOT = 4004) then MOVE 0 TO flag END-IF if (varch2 NOT = "PARGA"OR varch1 NOT = "MORRIS") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch previous row DISPLAY"FETCH PRIOR FROM a INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH PRIOR FROM a INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1002; its value is ", varnm1 DISPLAY"COL_CH1 should be MAKIS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4002; its value is ", varnm2 DISPLAY"COL_CH2 should be HANIA; its value is ", varch2 if (varnm1 NOT = 1002 OR varnm2 NOT = 4002) then MOVE 0 TO flag END-IF if (varch2 NOT = "HANIA"OR varch1 NOT = "MAKIS") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch previous row DISPLAY"FETCH PRIOR FROM a INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH PRIOR FROM a INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1000; its value is ", varnm1 DISPLAY"COL_CH1 should be NICKOS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4000; its value is ", varnm2 DISPLAY"COL_CH2 should be ATHENS; its value is ", varch2 if (varnm1 NOT = 1000 OR varnm2 NOT = 4000) then MOVE 0 TO flag END-IF if (varch2 NOT = "ATHENS"OR varch1 NOT = "NICKOS") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch previous row DISPLAY"FETCH PRIOR FROM a INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH PRIOR FROM a INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1003; its value is ", varnm1 DISPLAY"COL_CH1 should be GEORGE; its value is ", varch1 DISPLAY"COL_NUM2 should be 4003; its value is ", varnm2 DISPLAY"COL_CH2 should be ARTA; its value is ", varch2 if (varnm1 NOT = 1003 OR varnm2 NOT = 4003) then MOVE 0 TO flag END-IF if (varch2 NOT = "ARTA"OR varch1 NOT = "GEORGE") then MOVE 0 TO flag END-IF DISPLAY" "
*** attempt to read the row before the first row *** and check that a completion condition of no data *** will be raised indicated by SQLSTATE = "02000" DISPLAY"FETCH PRIOR FROM a INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH PRIOR FROM a INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD 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" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*** FETCH LAST ROW again ** DISPLAY"FETCH LAST FROM a INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH LAST FROM a INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1001; its value is ", varnm1 DISPLAY"COL_CH1 should be MARIA; its value is ", varch1 DISPLAY"COL_NUM2 should be 4001; its value is ", varnm2 DISPLAY"COL_CH2 should be RHODES; its value is ", varch2 if (varnm1 NOT = 1001 OR varnm2 NOT = 4001) then MOVE 0 TO flag END-IF if (varch2 NOT = "RHODES"OR varch1 NOT = "MARIA") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*Now retrieve the first row of the table DISPLAY"FETCH FIRST FROM a INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH FIRSTFROM a INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1003; its value is ", varnm1 DISPLAY"COL_CH1 should be GEORGE; its value is ", varch1 DISPLAY"COL_NUM2 should be 4003; its value is ", varnm2 DISPLAY"COL_CH2 should be ARTA; its value is ", varch2 if (varnm1 NOT = 1003 OR varnm2 NOT = 4003) then MOVE 0 TO flag END-IF if (varch2 NOT = "ARTA"OR varch1 NOT = "GEORGE") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*now retrieve the rest of the rows but from first to last *fetch orientation. Fetch the second row DISPLAY"FETCH NEXT FROM a INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH NEXTFROM a INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1000; its value is ", varnm1 DISPLAY"COL_CH1 should be NICKOS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4000; its value is ", varnm2 DISPLAY"COL_CH2 should be ATHENS; its value is ", varch2 if (varnm1 NOT = 1000 OR varnm2 NOT = 4000) then MOVE 0 TO flag END-IF if (varch2 NOT = "ATHENS"OR varch1 NOT = "NICKOS") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch next row DISPLAY"FETCH NEXT FROM a INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH NEXTFROM a INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1002; its value is ", varnm1 DISPLAY"COL_CH1 should be MAKIS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4002; its value is ", varnm2 DISPLAY"COL_CH2 should be HANIA; its value is ", varch2 if (varnm1 NOT = 1002 OR varnm2 NOT = 4002) then MOVE 0 TO flag END-IF if (varch2 NOT = "HANIA"OR varch1 NOT = "MAKIS") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch next row DISPLAY"FETCH NEXT FROM a INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH NEXTFROM a INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1004; its value is ", varnm1 DISPLAY"COL_CH1 should be MORRIS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4004; its value is ", varnm2 DISPLAY"COL_CH2 should be PARGA; its value is ", varch2 if (varnm1 NOT = 1004 OR varnm2 NOT = 4004) then MOVE 0 TO flag END-IF if (varch2 NOT = "PARGA"OR varch1 NOT = "MORRIS") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch final row DISPLAY"FETCH NEXT FROM a INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH NEXTFROM a INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1001; its value is ", varnm1 DISPLAY"COL_CH1 should be MARIA; its value is ", varch1 DISPLAY"COL_NUM2 should be 4001; its value is ", varnm2 DISPLAY"COL_CH2 should be RHODES; its value is ", varch2 if (varnm1 NOT = 1001 OR varnm2 NOT = 4001) then MOVE 0 TO flag END-IF if (varch2 NOT = "RHODES"OR varch1 NOT = "MARIA") then MOVE 0 TO flag END-IF DISPLAY" "
*** attempt to read the next row after the last row *** and check that a completion condition of no data *** will be raised indicated by SQLSTATE = "02000" DISPLAY"FETCH NEXT FROM a INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH NEXTFROM a INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD 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 a;" EXECSQLCLOSE a END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*********** Start tests with numeric cursor **********
*USE table CTS1.CL_STANDARD from the standard schema *declare a scrolled cursor with ORDER BY int,name *ASC and FETCH FIRST, FETCH LAST *with numeric sort key this time
DISPLAY"DECLARE b SCROLL CURSOR " DISPLAY"FOR SELECT COL_NUM1, COL_CH1, COL_NUM2, COL_CH2" DISPLAY"FROM CL_STANDARD" DISPLAY"ORDER BY 3, COL_NUM1 ASC;" EXECSQL DECLARE b SCROLL CURSOR FORSELECT COL_NUM1,COL_CH1,COL_NUM2,COL_CH2 FROM CL_STANDARD ORDERBY 3, COL_NUM1 ASC END-EXEC
DISPLAY"OPEN b;" EXECSQLOPEN b END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*start fetching rows in the cursor 5 in total *in reverse order
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch last row from CL_STANDARD DISPLAY"FETCH LAST FROM b INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH LAST FROM b INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1004; its value is ", varnm1 DISPLAY"COL_CH1 should be MORRIS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4004; its value is ", varnm2 DISPLAY"COL_CH2 should be PARGA; its value is ", varch2 if (varnm1 NOT = 1004 OR varnm2 NOT = 4004) then MOVE 0 TO flag END-IF if (varch2 NOT = "PARGA"OR varch1 NOT = "MORRIS") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch previous row DISPLAY"FETCH PRIOR FROM b INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH PRIOR FROM b INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1003; its value is ", varnm1 DISPLAY"COL_CH1 should be GEORGE; its value is ", varch1 DISPLAY"COL_NUM2 should be 4003; its value is ", varnm2 DISPLAY"COL_CH2 should be ARTA; its value is ", varch2 if (varnm1 NOT = 1003 OR varnm2 NOT = 4003) then MOVE 0 TO flag END-IF if (varch2 NOT = "ARTA"OR varch1 NOT = "GEORGE") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch previous row DISPLAY"FETCH PRIOR FROM b INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH PRIOR FROM b INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1002; its value is ", varnm1 DISPLAY"COL_CH1 should be MAKIS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4002; its value is ", varnm2 DISPLAY"COL_CH2 should be HANIA; its value is ", varch2 if (varnm1 NOT = 1002 OR varnm2 NOT = 4002) then MOVE 0 TO flag END-IF if (varch2 NOT = "HANIA"OR varch1 NOT = "MAKIS") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch previous row DISPLAY"FETCH PRIOR FROM b INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH PRIOR FROM b INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1001; its value is ", varnm1 DISPLAY"COL_CH1 should be MARIA; its value is ", varch1 DISPLAY"COL_NUM2 should be 4001; its value is ", varnm2 DISPLAY"COL_CH2 should be RHODES; its value is ", varch2 if (varnm1 NOT = 1001 OR varnm2 NOT = 4001) then MOVE 0 TO flag END-IF if (varch2 NOT = "RHODES"OR varch1 NOT = "MARIA") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch previous row DISPLAY"FETCH PRIOR FROM b INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH PRIOR FROM b INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1000; its value is ", varnm1 DISPLAY"COL_CH1 should be NICKOS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4000; its value is ", varnm2 DISPLAY"COL_CH2 should be ATHENS; its value is ", varch2 if (varnm1 NOT = 1000 OR varnm2 NOT = 4000) then MOVE 0 TO flag END-IF if (varch2 NOT = "ATHENS"OR varch1 NOT = "NICKOS") then MOVE 0 TO flag END-IF DISPLAY" "
*** attempt to read the row before the first row *** and check that a completion condition of no data *** will be raised indicated by SQLSTATE = "02000" DISPLAY"FETCH PRIOR FROM b INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH PRIOR FROM b INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD 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" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*** FETCH LAST ROW again ** DISPLAY"FETCH LAST FROM b INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH LAST FROM b INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1004; its value is ", varnm1 DISPLAY"COL_CH1 should be MORRIS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4004; its value is ", varnm2 DISPLAY"COL_CH2 should be PARGA; its value is ", varch2 if (varnm1 NOT = 1004 OR varnm2 NOT = 4004) then MOVE 0 TO flag END-IF if (varch2 NOT = "PARGA"OR varch1 NOT = "MORRIS") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*Now retrieve the first row of the table DISPLAY"FETCH FIRST FROM b INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH FIRSTFROM b INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1000; its value is ", varnm1 DISPLAY"COL_CH1 should be NICKOS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4000; its value is ", varnm2 DISPLAY"COL_CH2 should be ATHENS; its value is ", varch2 if (varnm1 NOT = 1000 OR varnm2 NOT = 4000) then MOVE 0 TO flag END-IF if (varch2 NOT = "ATHENS"OR varch1 NOT = "NICKOS") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*now retrieve the rest of the rows but from first to last *fetch orientation. Fetch the second row
DISPLAY"FETCH NEXT FROM b INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH NEXTFROM b INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1001; its value is ", varnm1 DISPLAY"COL_CH1 should be MARIA; its value is ", varch1 DISPLAY"COL_NUM2 should be 4001; its value is ", varnm2 DISPLAY"COL_CH2 should be RHODES; its value is ", varch2 if (varnm1 NOT = 1001 OR varnm2 NOT = 4001) then MOVE 0 TO flag END-IF if (varch2 NOT = "RHODES"OR varch1 NOT = "MARIA") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch next row DISPLAY"FETCH NEXT FROM b INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH NEXTFROM b INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1002; its value is ", varnm1 DISPLAY"COL_CH1 should be MAKIS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4002; its value is ", varnm2 DISPLAY"COL_CH2 should be HANIA; its value is ", varch2 if (varnm1 NOT = 1002 OR varnm2 NOT = 4002) then MOVE 0 TO flag END-IF if (varch2 NOT = "HANIA"OR varch1 NOT = "MAKIS") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch next row DISPLAY"FETCH NEXT FROM b INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH NEXTFROM b INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1003; its value is ", varnm1 DISPLAY"COL_CH1 should be GEORGE; its value is ", varch1 DISPLAY"COL_NUM2 should be 4003; its value is ", varnm2 DISPLAY"COL_CH2 should be ARTA; its value is ", varch2 if (varnm1 NOT = 1003 OR varnm2 NOT = 4003) then MOVE 0 TO flag END-IF if (varch2 NOT = "ARTA"OR varch1 NOT = "GEORGE") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch final row DISPLAY"FETCH NEXT FROM b INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH NEXTFROM b INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1004; its value is ", varnm1 DISPLAY"COL_CH1 should be MORRIS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4004; its value is ", varnm2 DISPLAY"COL_CH2 should be PARGA; its value is ", varch2 if (varnm1 NOT = 1004 OR varnm2 NOT = 4004) then MOVE 0 TO flag END-IF if (varch2 NOT = "PARGA"OR varch1 NOT = "MORRIS") then MOVE 0 TO flag END-IF DISPLAY" "
*** attempt to read the next row after the last row *** and check that a completion condition of no data *** will be raised indicated by SQLSTATE = "02000" DISPLAY"FETCH NEXT FROM b INTO
- " :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH NEXTFROM b INTO
:varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD 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 b;" EXECSQLCLOSE b END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*declare a third cursor and check that all the rows *of table CTS1.CL_STANDARD have remained unaffected DISPLAY"DECLARE Z CURSOR FOR SELECT
- " COL_NUM1,COL_CH1,COL_NUM2,COL_CH2 " DISPLAY"FROM CL_STANDARD ORDER BY COL_NUM1;" EXECSQL DECLARE Z CURSOR FORSELECT COL_NUM1,COL_CH1,COL_NUM2,COL_CH2 FROM CL_STANDARD ORDERBY COL_NUM1 END-EXEC
DISPLAY"OPEN Z;" EXECSQLOPEN Z END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*start fetching rows in the cursor 5 in total *fetch first row from CL_STANDARD DISPLAY"FETCH Z INTO :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH Z INTO :varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1000; its value is ", varnm1 DISPLAY"COL_CH1 should be NICKOS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4000; its value is ", varnm2 DISPLAY"COL_CH2 should be ATHENS; its value is ", varch2 if (varnm1 NOT = 1000 OR varnm2 NOT = 4000) then MOVE 0 TO flag END-IF if (varch2 NOT = "ATHENS"OR varch1 NOT = "NICKOS") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch second row DISPLAY"FETCH Z INTO :varnm1,varch1,varnm2,varch2;" EXECSQL FETCH Z INTO :varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1001; its value is ", varnm1 DISPLAY"COL_CH1 should be MARIA; its value is ", varch1 DISPLAY"COL_NUM2 should be 4001; its value is ", varnm2 DISPLAY"COL_CH2 should be RHODES; its value is ", varch2 if (varnm1 NOT = 1001 OR varnm2 NOT = 4001) then MOVE 0 TO flag END-IF if (varch2 NOT = "RHODES"OR varch1 NOT = "MARIA") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch third row DISPLAY"FETCH Z INTO :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH Z INTO :varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1002; its value is ", varnm1 DISPLAY"COL_CH1 should be MAKIS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4002; its value is ", varnm2 DISPLAY"COL_CH2 should be HANIA; its value is ", varch2 if (varnm1 NOT = 1002 OR varnm2 NOT = 4002) then MOVE 0 TO flag END-IF if (varch2 NOT = "HANIA"OR varch1 NOT = "MAKIS") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch fourth row DISPLAY"FETCH Z INTO :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH Z INTO :varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1003; its value is ", varnm1 DISPLAY"COL_CH1 should be GEORGE; its value is ", varch1 DISPLAY"COL_NUM2 should be 4003; its value is ", varnm2 DISPLAY"COL_CH2 should be ARTA; its value is ", varch2 if (varnm1 NOT = 1003 OR varnm2 NOT = 4003) then MOVE 0 TO flag END-IF if (varch2 NOT = "ARTA"OR varch1 NOT = "GEORGE") then MOVE 0 TO flag END-IF DISPLAY" "
*initialise host variables MOVE 0 TO varnm1 MOVE 0 TO varnm2 MOVE"xxxxxxxxxx"TO varch1 MOVE"xxxxxxxxxx"TO varch2
*fetch fifth row DISPLAY"FETCH Z INTO :varnm1,:varch1,:varnm2,:varch2;" EXECSQL FETCH Z INTO :varnm1,:varch1,:varnm2,:varch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"COL_NUM1 should be 1004; its value is ", varnm1 DISPLAY"COL_CH1 should be MORRIS; its value is ", varch1 DISPLAY"COL_NUM2 should be 4004; its value is ", varnm2 DISPLAY"COL_CH2 should be PARGA; its value is ", varch2 if (varnm1 NOT = 1004 OR varnm2 NOT = 4004) then MOVE 0 TO flag END-IF if (varch2 NOT = "PARGA"OR varch1 NOT = "MORRIS") then MOVE 0 TO flag END-IF DISPLAY" "
DISPLAY"CLOSE Z;" EXECSQLCLOSE Z END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
DISPLAY"ROLLBACK WORK;" EXECSQL ROLLBACK WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
if ( flag = 1 ) then DISPLAY" xts739.pco *** pass *** " EXECSQLINSERTINTO CTS1.TESTREPORT
VALUES('7039','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" xts739.pco *** fail *** " EXECSQLINSERTINTO CTS1.TESTREPORT
VALUES('7039','fail','PCO') END-EXEC MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF
DISPLAY"========================================"
EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD ******************** END TEST7039 ********************
**** 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.
¤ 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.0.34Bemerkung:
(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.