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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
|
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML082
-- SQL Test Suite, V6.0, Interactive SQL, dml082.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION SCHANZLE
set schema SCHANZLE;
0 rows inserted/updated/deleted
ij> --O SELECT USER FROM HU.ECCO;
VALUES USER;
1
--------------------------------------------------------------------------------------------------------------------------------
SCHANZLE
ij> -- 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."
-- NO_TEST:0491 SQLSTATE 22022: data exception/indicator overflow!
-- Testing indicators
-- *********************************************
-- TEST:0492 SQLSTATE 22019: data exception/invalid escape char!
-- setup
--O UPDATE HU.STAFF
--O SET CITY = 'Percent%Xunder_'
--O WHERE EMPNUM = 'E1';
--O SELECT COUNT(*)
--O FROM HU.STAFF
--O WHERE CITY LIKE '%XX%X_%' ESCAPE 'XX';
-- PASS:0492 If ERROR, data exception/invalid escape char?
-- PASS:0492 0 rows selected OR SQLSTATE = 22019 OR SQLCODE < 0?
--O SELECT COUNT(*)
--O FROM HU.STAFF
--O WHERE CITY LIKE '%XX%X_%' ESCAPE 'X';
-- PASS:0492 If count = 1?
--O SELECT COUNT(*)
--O FROM HU.STAFF
--O WHERE CITY LIKE '%XX_%' ESCAPE 'XX';
-- PASS:0492 If ERROR, data exception/invalid escape char?
-- PASS:0492 0 rows selected OR SQLSTATE = 22019 OR SQLCODE < 0?
--O SELECT COUNT(*)
--O FROM HU.STAFF WHERE CITY
--O LIKE '%XX_%' ESCAPE 'X';
-- PASS:0492 If count = 1?
-- restore
--O ROLLBACK WORK;
-- END TEST >>> 0492 <<< END TEST
-- *********************************************
-- TEST:0493 SQLSTATE 22025: data exception/invalid escape seq.!
-- setup
--O DELETE FROM CPBASE;
--O INSERT INTO CPBASE
--O VALUES(82,'Per%X&und_');
--O SELECT COUNT(*)
--O FROM CPBASE WHERE JUNK1
--O LIKE 'P%X%%X' ESCAPE 'X';
-- PASS:0493 If ERROR, data exception/invalid escape seq.?
-- PASS:0493 0 rows selected OR SQLSTATE = 22025 OR SQLCODE < 0?
--O SELECT COUNT(*)
--O FROM CPBASE WHERE JUNK1
--O LIKE 'P%X%%' ESCAPE 'X';
-- PASS:0493 If count = 1?
--O INSERT INTO HU.STAFF
--O SELECT 'E12','ff',KC,'gg'
--O FROM CPBASE
--O WHERE JUNK1 LIKE '%X%%Xd_' ESCAPE 'X';
-- PASS:0493 If ERROR, data exception/invalid escape seq.?
-- PASS:0493 0 rows inserted OR SQLSTATE = 22025 OR SQLCODE < 0?
--O INSERT INTO HU.STAFF
--O SELECT 'E13','ff',KC,'gg'
--O FROM CPBASE
--O WHERE JUNK1 LIKE '%X%%X_' ESCAPE 'X';
-- PASS:0493 If 1 row is inserted?
--O UPDATE CPBASE
--O SET KC = -1
--O WHERE JUNK1 LIKE '%?X%' ESCAPE '?';
-- PASS:0493 If ERROR, data exception/invalid escape seq.?
-- PASS:0493 0 rows updated OR SQLSTATE = 22025 OR SQLCODE < 0?
--O UPDATE CPBASE
--O SET KC = -1
--O WHERE JUNK1 LIKE '%?%X%' ESCAPE '?';
-- PASS:0493 If 1 row is updated?
--O DELETE FROM CPBASE
--O WHERE JUNK1 LIKE '_e%&u%' ESCAPE '&';
-- PASS:0493 If ERROR, data exception/invalid escape seq.?
-- PASS:0493 0 rows deleted OR SQLSTATE = 22025 OR SQLCODE < 0?
--O DELETE FROM CPBASE
--O WHERE JUNK1 LIKE '_e%&&u%' ESCAPE '&';
-- PASS:0493 If 1 row is deleted?
-- restore
--O ROLLBACK WORK;
-- END TEST >>> 0493 <<< END TEST
-- *********************************************
-- TEST:0494 SQLSTATE 22003: data exception/numeric value out of range!
-- setup
DELETE FROM HU.HH;
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> INSERT INTO HU.HH
VALUES (10);
1 row inserted/updated/deleted
ij> -- PASS:0494 If 1 row is inserted?
-- PASS:0494 OR ERROR, data exception/numeric value out of range?
-- PASS:0494 OR 0 rows inserted OR SQLSTATE = 22003 OR SQLCODE < 0?
INSERT INTO HU.HH
VALUES (100);
1 row inserted/updated/deleted
ij> -- PASS:0494 If 1 row is inserted?
-- PASS:0494 OR ERROR, data exception/numeric value out of range?
-- PASS:0494 OR 0 rows inserted OR SQLSTATE = 22003 OR SQLCODE < 0?
INSERT INTO HU.HH
VALUES (1000);
1 row inserted/updated/deleted
ij> -- PASS:0494 If 1 row is inserted?
-- PASS:0494 OR ERROR, data exception/numeric value out of range?
-- PASS:0494 OR 0 rows inserted OR SQLSTATE = 22003 OR SQLCODE < 0?
INSERT INTO HU.HH
VALUES (10000);
1 row inserted/updated/deleted
ij> -- PASS:0494 If 1 row is inserted?
-- PASS:0494 OR ERROR, data exception/numeric value out of range?
-- PASS:0494 OR 0 rows inserted OR SQLSTATE = 22003 OR SQLCODE < 0?
INSERT INTO HU.HH
VALUES (100000);
ERROR 22003: The resulting value is outside the range for the data type SMALLINT.
ij> -- PASS:0494 If 1 row is inserted?
-- PASS:0494 OR ERROR, data exception/numeric value out of range?
-- PASS:0494 OR 0 rows inserted OR SQLSTATE = 22003 OR SQLCODE < 0?
INSERT INTO HU.HH
VALUES (1000000);
ERROR 22003: The resulting value is outside the range for the data type SMALLINT.
ij> -- PASS:0494 If 1 row is inserted?
-- PASS:0494 OR ERROR, data exception/numeric value out of range?
-- PASS:0494 OR 0 rows inserted OR SQLSTATE = 22003 OR SQLCODE < 0?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0494 <<< END TEST
-- *********************************************
-- TEST:0505 SQLSTATE 44000: with check option violation!
--O INSERT INTO FLATER.WCOV
--O VALUES (0);
-- PASS:0505 If ERROR, with check option violation?
-- PASS:0505 0 rows inserted OR SQLSTATE = 44000 OR SQLCODE < 0?
--O INSERT INTO FLATER.WCOV
--O VALUES (75);
-- PASS:0505 If 1 row is inserted?
--O UPDATE FLATER.WCOV
--O SET C1 = -C1
--O WHERE C1 = 75;
-- PASS:0505 If ERROR, with check option violation?
-- PASS:0505 0 rows updated OR SQLSTATE = 44000 OR SQLCODE < 0?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0505 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>
|
