1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
AUTOCOMMIT OFF;
-- MODULE DML081
-- SQL Test Suite, V6.0, Interactive SQL, dml081.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION SCHANZLE
set schema SCHANZLE;
--O SELECT USER FROM HU.ECCO;
VALUES USER;
-- RERUN if USER value does not match preceding AUTHORIZATION comment
-- date_time print
-- NOTE Direct support for SQLCODE or SQLSTATE is not required
-- NOTE in Interactive Direct SQL, as defined in FIPS 127-2.
-- NOTE ********************* instead ***************************
-- NOTE If a statement raises an exception condition,
-- NOTE then the system shall display a message indicating that
-- NOTE the statement failed, giving a textual description
-- NOTE of the failure.
-- NOTE If a statement raises a completion condition that is a
-- NOTE "warning" or "no data", then the system shall display
-- NOTE a message indicating that the statement completed,
-- NOTE giving a textual description of the "warning" or "no data."
-- TEST:0487 SQLSTATE 00000: successful completion!
--O SELECT COUNT(*)
SELECT *
FROM HU.WORKS;
-- PASS:0487 If count = 12?
-- PASS:0487 OR SQLSTATE = 00000: successful completion?
-- END TEST >>> 0487 <<< END TEST
-- *********************************************
-- TEST:0488 SQLSTATE 21000: cardinality violation!
--O SELECT COUNT(*)
SELECT *
FROM HU.WORKS
WHERE PNUM = (SELECT PNUM
FROM HU.WORKS
WHERE HOURS = 80);
-- PASS:0488 If ERROR, cardinality violation, 0 rows selected?
-- PASS:0488 OR SQLSTATE = 21000 OR SQLCODE < 0?
-- END TEST >>> 0488 <<< END TEST
-- *********************************************
-- TEST:0489 SQLSTATE 02000: no data!
SELECT GRADE
FROM HU.STAFF
WHERE EMPNUM = 'xx';
-- PASS:0489 If 0 rows selected?
-- PASS:0489 OR SQLSTATE = 02000: no data OR SQLCODE = 100?
DELETE FROM HU.STAFF
WHERE GRADE = 11;
-- PASS:0489 If 0 rows deleted?
-- PASS:0489 OR SQLSTATE = 02000: no data OR SQLCODE = 100?
INSERT INTO HU.STAFF (EMPNUM,GRADE)
SELECT EMPNUM, 9
FROM HU.WORKS
WHERE PNUM = 'x9';
-- PASS:0489 If 0 rows inserted?
-- PASS:0489 OR SQLSTATE = 02000: no data OR SQLCODE = 100?
UPDATE HU.STAFF SET CITY = 'Ho'
WHERE GRADE = 15;
-- PASS:0489 If 0 rows updated?
-- PASS:0489 OR SQLSTATE = 02000: no data OR SQLCODE = 100?
-- restore
ROLLBACK WORK;
-- END TEST >>> 0489 <<< END TEST
-- *********************************************
-- TEST:0490 SQLSTATE 22012: data exception/division by zero!
-- setup
INSERT INTO HU.STAFF
VALUES ('E6','Fidel',0,'Havana');
--O SELECT COUNT(*)
SELECT *
FROM HU.STAFF
WHERE EMPNAME = 'Fidel' AND 16/GRADE > 2;
-- PASS:0490 If ERROR, data exception/division by zero, 0 rows selected?
-- PASS:0490 OR SQLSTATE = 22012 OR SQLCODE < 0?
SELECT 16/GRADE
FROM HU.STAFF
WHERE EMPNAME = 'Fidel';
-- PASS:0490 If ERROR, data exception/division by zero, 0 rows selected?
-- PASS:0490 OR SQLSTATE = 22012 OR SQLCODE < 0?
SELECT COUNT(*)
FROM HU.STAFF
GROUP BY CITY HAVING SUM(GRADE/0) > 44;
-- PASS:0490 If ERROR, data exception/division by zero, 0 rows selected?
-- PASS:0490 OR SQLSTATE = 22012 OR SQLCODE < 0?
--O SELECT COUNT(*)
SELECT *
FROM HU.STAFF WHERE GRADE =
(SELECT 16/GRADE
FROM HU.STAFF
WHERE EMPNUM = 'E6');
-- PASS:0490 If ERROR, data exception/division by zero, 0 rows selected?
-- PASS:0490 OR SQLSTATE = 22012 OR SQLCODE < 0?
UPDATE HU.STAFF
SET GRADE = GRADE/0
WHERE GRADE = 12;
-- PASS:0490 If ERROR, data exception/division by zero, 0 rows updated?
-- PASS:0490 OR SQLSTATE = 22012 OR SQLCODE < 0?
INSERT INTO HU.STAFF
SELECT 'X','Y',HOURS/0,'z'
FROM HU.WORKS
WHERE PNUM = 'P6';
-- PASS:0490 If ERROR, data exception/division by zero, 0 rows inserted?
-- PASS:0490 OR SQLSTATE = 22012 OR SQLCODE < 0?
-- restore
ROLLBACK WORK;
-- END TEST >>> 0490 <<< END TEST
-- *********************************************
-- NO_TEST:0502 SQLSTATE 24000: invalid cursor state!
-- Testing cursors
-- *************************************************////END-OF-MODULE
|
