| ij> AUTOCOMMIT OFF; |
| ij> -- MODULE DML058 |
| |
| -- SQL Test Suite, V6.0, Interactive SQL, dml058.sql |
| -- 59-byte ID |
| -- TEd Version # |
| |
| -- AUTHORIZATION HU |
| set schema HU; |
| 0 rows inserted/updated/deleted |
| ij> --O SELECT USER FROM HU.ECCO; |
| VALUES USER; |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| HU |
| ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment |
| |
| -- date_time print |
| |
| -- TEST:0251 COMMIT keeps changes of current transaction! |
| |
| DELETE FROM STAFF1; |
| 0 rows inserted/updated/deleted |
| WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. |
| ij> -- Making sure the table is empty |
| |
| -- setup |
| INSERT INTO STAFF1 |
| SELECT * |
| FROM STAFF; |
| 5 rows inserted/updated/deleted |
| ij> -- PASS:0251 If 5 rows are inserted? |
| |
| --O SELECT COUNT(*) |
| SELECT * |
| FROM STAFF1; |
| EM&|EMPNAME |GRADE|CITY |
| ---------------------------------------------- |
| E1 |Alice |12 |Deale |
| E2 |Betty |10 |Vienna |
| E3 |Carmen |13 |Vienna |
| E4 |Don |12 |Deale |
| E5 |Ed |13 |Akron |
| ij> -- PASS:0251 If count = 5? |
| |
| INSERT INTO STAFF1 |
| VALUES('E9','Tom',50,'London'); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0251 If 1 row is inserted? |
| |
| UPDATE STAFF1 |
| SET GRADE = 40 |
| WHERE EMPNUM = 'E2'; |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0251 If 1 row is updated? |
| |
| COMMIT WORK; |
| ij> DELETE FROM STAFF1; |
| 6 rows inserted/updated/deleted |
| ij> -- PASS:0251 If 6 rows are deleted? |
| |
| -- verify |
| ROLLBACK WORK; |
| ij> -- verify previous commit |
| --O SELECT COUNT(*) |
| SELECT * |
| FROM STAFF1 |
| WHERE GRADE > 12; |
| EM&|EMPNAME |GRADE|CITY |
| ---------------------------------------------- |
| E2 |Betty |40 |Vienna |
| E3 |Carmen |13 |Vienna |
| E5 |Ed |13 |Akron |
| E9 |Tom |50 |London |
| ij> -- PASS:0251 If count = 4? |
| |
| -- restore |
| DELETE FROM STAFF1; |
| 6 rows inserted/updated/deleted |
| ij> COMMIT WORK; |
| ij> -- END TEST >>> 0251 <<< END TEST |
| |
| -- *************************************************************** |
| |
| -- TEST:0252 ROLLBACK cancels changes of current transaction! |
| |
| DELETE FROM STAFF1; |
| 0 rows inserted/updated/deleted |
| WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. |
| ij> -- Making sure the table is empty |
| |
| -- setup |
| INSERT INTO STAFF1 |
| SELECT * |
| FROM STAFF; |
| 5 rows inserted/updated/deleted |
| ij> -- PASS:0252 If 5 rows are inserted? |
| |
| COMMIT WORK; |
| ij> INSERT INTO STAFF1 |
| VALUES('E10','Tom',50,'London'); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0252 If 1 row is inserted? |
| |
| UPDATE STAFF1 |
| SET GRADE = 40 |
| WHERE EMPNUM = 'E1'; |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0252 If 1 row is updated? |
| |
| DELETE FROM STAFF1 |
| WHERE EMPNUM = 'E2'; |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0252 If 1 row is deleted? |
| |
| ROLLBACK WORK; |
| ij> -- verify |
| SELECT SUM(GRADE) |
| FROM STAFF1; |
| 1 |
| --------- |
| 60 |
| ij> -- PASS:0252 If SUM(GRADE) = 60? |
| |
| -- restore |
| DELETE FROM STAFF1; |
| 5 rows inserted/updated/deleted |
| ij> COMMIT WORK; |
| ij> -- END TEST >>> 0252 <<< END TEST |
| |
| -- **************************************************************** |
| |
| -- TEST:0253 TEST0124 workaround (key = key+1)! |
| |
| SELECT NUMKEY |
| FROM UPUNIQ |
| ORDER BY NUMKEY DESC; |
| NUM& |
| ---- |
| 8 |
| 6 |
| 4 |
| 3 |
| 2 |
| 1 |
| ij> -- PASS:0253 If 6 rows are selected and first NUMKEY = 8 ? |
| |
| UPDATE UPUNIQ |
| SET NUMKEY = 8 + 1 |
| WHERE NUMKEY = 8; |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0253 If 1 row is updated? |
| |
| UPDATE UPUNIQ |
| SET NUMKEY = 6 + 1 |
| WHERE NUMKEY = 6; |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0253 If 1 row is updated? |
| |
| UPDATE UPUNIQ |
| SET NUMKEY = 4 + 1 |
| WHERE NUMKEY = 4; |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0253 If 1 row is updated? |
| |
| UPDATE UPUNIQ |
| SET NUMKEY = 3 + 1 |
| WHERE NUMKEY = 3; |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0253 If 1 row is updated? |
| |
| UPDATE UPUNIQ |
| SET NUMKEY = 2 + 1 |
| WHERE NUMKEY = 2; |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0253 If 1 row is updated? |
| |
| UPDATE UPUNIQ |
| SET NUMKEY = 1 + 1 |
| WHERE NUMKEY = 1; |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0253 If 1 row is updated? |
| |
| |
| SELECT MAX(NUMKEY), MIN(NUMKEY) |
| FROM UPUNIQ; |
| 1 |2 |
| --------- |
| 9 |2 |
| ij> -- PASS:0253 If MAX(NUMKEY) = 9 AND MIN(NUMKEY) = 2? |
| |
| -- restore |
| ROLLBACK WORK; |
| ij> -- END TEST >>> 0253 <<< END TEST |
| |
| -- ************************************************************** |
| |
| -- TEST:0254 Column name in SET clause! |
| |
| DELETE FROM PROJ1; |
| 0 rows inserted/updated/deleted |
| WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. |
| ij> -- Making sure the table is empty |
| |
| -- setup |
| INSERT INTO PROJ1 |
| SELECT * |
| FROM PROJ; |
| 6 rows inserted/updated/deleted |
| ij> -- PASS:0254 If 6 rows are inserted? |
| |
| UPDATE PROJ1 |
| SET CITY = PTYPE; |
| 6 rows inserted/updated/deleted |
| ij> -- PASS:0254 If 6 rows are updated? |
| |
| SELECT CITY |
| FROM PROJ1 |
| WHERE PNUM = 'P1'; |
| CITY |
| --------------- |
| Design |
| ij> -- PASS:0254 If CITY = 'Design'? |
| |
| -- restore |
| ROLLBACK WORK; |
| ij> -- END TEST >>> 0254 <<< END TEST |
| |
| -- ************************************************************** |
| |
| |
| -- TEST:0255 Key word USER for INSERT, UPDATE! |
| |
| DELETE FROM T4; |
| 0 rows inserted/updated/deleted |
| WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. |
| ij> -- Making sure the table is empty |
| |
| -- setup |
| INSERT INTO T4 |
| VALUES(CAST(USER AS VARCHAR(128)),100,'good','luck'); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0255 If 1 row is inserted? |
| |
| SELECT STR110 |
| FROM T4 |
| WHERE NUM6 = 100; |
| STR110 |
| -------------------------------------------------------------------------------------------------------------- |
| HU |
| ij> -- PASS:0255 If STR110 = 'HU'? |
| |
| -- setup |
| INSERT INTO T4 |
| VALUES('Hello',101,'good','luck'); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0255 If 1 row is inserted? |
| |
| UPDATE T4 |
| SET STR110 = CAST(USER AS VARCHAR(128)) |
| WHERE NUM6 = 101; |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0255 If 1 row is updated? |
| |
| SELECT STR110 |
| FROM T4 |
| WHERE NUM6 = 101; |
| STR110 |
| -------------------------------------------------------------------------------------------------------------- |
| HU |
| ij> -- PASS:0255 If STR110 = 'HU'? |
| |
| -- restore |
| ROLLBACK WORK; |
| ij> -- END TEST >>> 0255 <<< END TEST |
| |
| -- *************************************************************** |
| |
| -- TEST:0256 Key word USER in WHERE clause! |
| |
| DELETE FROM T4; |
| 0 rows inserted/updated/deleted |
| WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. |
| ij> -- Making sure the table is empty |
| |
| -- setup |
| INSERT INTO T4 |
| VALUES('HU',100,'good','luck'); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0256 If 1 row is inserted? |
| |
| SELECT STR110 |
| FROM T4 |
| WHERE STR110 = CAST(USER AS VARCHAR(128)); |
| STR110 |
| -------------------------------------------------------------------------------------------------------------- |
| HU |
| ij> -- PASS:0256 If STR110 = 'HU'? |
| |
| -- setup |
| INSERT INTO T4 |
| VALUES('Hello',101,'good','luck'); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0256 If 1 row is inserted? |
| |
| DELETE FROM T4 |
| WHERE STR110 = CAST(USER AS VARCHAR(128)); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0256 If 1 row is deleted? |
| |
| SELECT COUNT(*) |
| FROM T4 |
| WHERE STR110 LIKE '%HU%'; |
| 1 |
| ----------- |
| 0 |
| ij> -- PASS:0256 If count = 0? |
| |
| -- restore |
| ROLLBACK WORK; |
| ij> -- END TEST >>> 0256 <<< END TEST |
| -- *************************************************////END-OF-MODULE |
| ; |
| ij> |
