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
|
ij(CONNECTION1)> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
-- this test shows some ij abilities against an active database
create table t (i int);
0 rows inserted/updated/deleted
ij(CONNECTION1)> insert into t values (3), (4);
2 rows inserted/updated/deleted
ij(CONNECTION1)> prepare s as 'select * from t';
ij(CONNECTION1)> execute s;
I
-----------
3
4
ij(CONNECTION1)> remove s;
ij(CONNECTION1)> -- now it won't find s
execute s;
IJ ERROR: Unable to establish prepared statement S@CONNECTION1
ij(CONNECTION1)> prepare s as 'select * from t where i=?';
ij(CONNECTION1)> -- fails, needs parameter
execute s;
ERROR 07000: At least one parameter to the current statement is uninitialized.
ij(CONNECTION1)> -- works, finds value
execute s using 'values 3';
IJ WARNING: Autocommit may close using result set
I
-----------
3
ij(CONNECTION1)> prepare t as 'values 3';
ij(CONNECTION1)> -- same as last execute
execute s using t;
IJ WARNING: Autocommit may close using result set
I
-----------
3
ij(CONNECTION1)> -- same as last execute
execute 'select * from t where i=?' using 'values 3';
IJ WARNING: Autocommit may close using result set
I
-----------
3
ij(CONNECTION1)> -- same as last execute
execute 'select * from t where i=?' using t;
IJ WARNING: Autocommit may close using result set
I
-----------
3
ij(CONNECTION1)> -- param that is not needed gets out of range message
execute 'select * from t where i=?' using 'values (3,4)';
IJ WARNING: Autocommit may close using result set
ERROR XCL13: The parameter position '2' is out of range. The number of parameters for this prepared statement is '1'.
ij(CONNECTION1)> -- ignores rows that are not needed
execute 'select * from t where i=?' using 'values 3,4';
IJ WARNING: Autocommit may close using result set
I
-----------
3
ij(CONNECTION1)> -- with autocommit off, extra rows are processed and no warning results
autocommit off;
ij(CONNECTION1)> execute 'select * from t where i=?' using 'values 3,4';
I
-----------
3
I
-----------
4
ij(CONNECTION1)> execute 'select * from t where i=?' using 'values 3';
I
-----------
3
ij(CONNECTION1)> autocommit on;
ij(CONNECTION1)> -- will say params not set when no rows in using values
execute 'select * from t where i=?' using 'select * from t where i=9';
IJ ERROR: Using clause had no results
ij(CONNECTION1)> -- will say params not set when using values is not a query
execute 'select * from t where i=?' using 'create table s (i int)';
IJ ERROR: Using clause had no results
ij(CONNECTION1)> -- note that the using part was, however, executed...
drop table s;
0 rows inserted/updated/deleted
ij(CONNECTION1)> -- DERBY-2558: Verify that we get a reasonable message when the 'dimension'
-- of the 'using-set' does not match the 'dimension' of the prepared statement:
create table t2558 (i int);
0 rows inserted/updated/deleted
ij(CONNECTION1)> insert into t2558 values (3), (4);
2 rows inserted/updated/deleted
ij(CONNECTION1)> -- First two statements below should fail. Third one should work.
execute 'select * from t2558 where i = ?' using 'values (3,4)';
IJ WARNING: Autocommit may close using result set
ERROR XCL13: The parameter position '2' is out of range. The number of parameters for this prepared statement is '1'.
ij(CONNECTION1)> execute 'select * from t2558 where i in (?,?,?)' using 'values (3,4)';
IJ WARNING: Autocommit may close using result set
ERROR 07000: At least one parameter to the current statement is uninitialized.
ij(CONNECTION1)> execute 'select * from t2558 where i = ? or i = ?' using 'values (3,4)';
IJ WARNING: Autocommit may close using result set
I
-----------
3
4
ij(CONNECTION1)> -- bug 5926 - make sure the using clause result set got closed
drop table t;
0 rows inserted/updated/deleted
ij(CONNECTION1)> create table t(c1 int);
0 rows inserted/updated/deleted
ij(CONNECTION1)> insert into t values(1);
1 row inserted/updated/deleted
ij(CONNECTION1)> execute 'select * from t where c1=?' using 'select * from t where c1=1';
IJ WARNING: Autocommit may close using result set
C1
-----------
1
ij(CONNECTION1)> drop table t;
0 rows inserted/updated/deleted
ij(CONNECTION1)> create table t(c1 int);
0 rows inserted/updated/deleted
ij(CONNECTION1)> insert into t values(1);
1 row inserted/updated/deleted
ij(CONNECTION1)> insert into t values(2);
1 row inserted/updated/deleted
ij(CONNECTION1)> execute 'select * from t where c1=?' using 'select * from t where c1>=1';
IJ WARNING: Autocommit may close using result set
C1
-----------
1
ij(CONNECTION1)> drop table t;
0 rows inserted/updated/deleted
ij(CONNECTION1)> -- Bug 4694 Test automatic rollback with close of connection
-- in ij
connect 'wombat';
ij(CONNECTION2)> autocommit off;
ij(CONNECTION2)> create table a (a int);
0 rows inserted/updated/deleted
ij(CONNECTION2)> select count(*) from a;
1
-----------
0
ij(CONNECTION2)> disconnect;
ij> set connection connection0;
WARNING 01J01: Database 'wombat' not created, connection made to existing database instead.
ij(CONNECTION0)> select count(*) from a;
ERROR 42X05: Table/View 'A' does not exist.
ij(CONNECTION0)> create table t ( c char(50));
0 rows inserted/updated/deleted
ij(CONNECTION0)> insert into t values('hello');
1 row inserted/updated/deleted
ij(CONNECTION0)> select cast(c as varchar(20)) from t;
1
--------------------
hello
ij(CONNECTION0)> drop table t;
0 rows inserted/updated/deleted
ij(CONNECTION0)> -- DERBY-3408: Unknown command error should suggest referring to server docs:
show schema;
ERROR 42X01: Syntax error: Encountered "show" at line 2, column 1.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij(CONNECTION0)> xxx;
ERROR 42X01: Syntax error: Encountered "xxx" at line 1, column 1.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij(CONNECTION0)>
|
