|
1
21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23
24 import java.sql.Connection  ;
25 import java.sql.ParameterMetaData ;
26 import java.sql.PreparedStatement ;
27 import java.sql.ResultSet ;
28 import java.sql.SQLException ;
29 import java.sql.Statement ;
30
31 import org.apache.derby.tools.ij;
32 import org.apache.derbyTesting.functionTests.util.TestUtil;
33
34
35
39 public class unaryArithmeticDynamicParameter {
40
41 public static void main (String [] argv) throws Throwable
42 {
43 System.out.println("Test using parameters for unary minus and unary plus");
44 ij.getPropertyArg(argv);
45 Connection conn = ij.startJBMS();
46 Statement s = conn.createStatement();
47 s.executeUpdate("create table t1 (c11 int, c12 smallint, c13 double, c14 char(3))");
48 s.execute("create table t2 (c21 int)");
49 s.execute("insert into t2 values (-1),(-2)");
50
51 System.out.println("insert into t1 values(?,+?,-?,?)");
52 PreparedStatement ps = conn.prepareStatement("insert into t1 values(?,+?,-?,?)");
53 ps.setInt(1,1);
54 ps.setInt(2,1);
55 ps.setDouble(3,-1.1);
56 ps.setString(4,"abc");
57 ParameterMetaData pmd = ps.getParameterMetaData();
58 System.out.println("? parameter type is " + pmd.getParameterTypeName(1));
59 System.out.println("unary +? parameter type is " + pmd.getParameterTypeName(2));
60 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(3));
61 ps.executeUpdate();
62 ps.setInt(1,-1);
63 ps.setInt(2,-1);
64 ps.setDouble(3,1.0);
65 ps.setString(4,"def");
66 ps.executeUpdate();
67
68 System.out.println("select * from t1 where -? in (select c21 from t2)");
69 ps = conn.prepareStatement("select * from t1 where -? in (select c21 from t2)");
70 ps.setInt(1,1);
71 pmd = ps.getParameterMetaData();
72 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
73 dumpRS(ps.executeQuery());
74
75 System.out.println("select * from t1 where c11 = -? and c12 = +? and c13 = ?");
76 ps = conn.prepareStatement("select * from t1 where c11 = -? and c12 = +? and c13 = ?");
77 ps.setInt(1,-1);
78 ps.setInt(2,1);
79 ps.setDouble(3,1.1);
80 pmd = ps.getParameterMetaData();
81 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
82 System.out.println("unary +? parameter type is " + pmd.getParameterTypeName(2));
83 System.out.println("? parameter type is " + pmd.getParameterTypeName(3));
84 dumpRS(ps.executeQuery());
85 ps.setShort(1,(short) 1);
86 ps.setInt(2,-1);
87 ps.setInt(3,-1);
88 dumpRS(ps.executeQuery());
89
90 System.out.println("select * from t1 where -? = ABS_FUNCT(+?)");
91 s.execute("CREATE FUNCTION ABS_FUNCT(P1 INT) RETURNS INT CALLED ON NULL INPUT EXTERNAL NAME 'java.lang.Math.abs' LANGUAGE JAVA PARAMETER STYLE JAVA");
92 ps = conn.prepareStatement("select * from t1 where -? = abs_funct(+?)");
93 ps.setInt(1,-1);
94 ps.setInt(2,1);
95 pmd = ps.getParameterMetaData();
96 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
97 System.out.println("unary +? parameter type is " + pmd.getParameterTypeName(2));
98 dumpRS(ps.executeQuery());
99
100 System.out.println("select * from t1 where -? = max_cni(-5,-1)");
101 s.execute("CREATE FUNCTION MAX_CNI(P1 INT, P2 INT) RETURNS INT CALLED ON NULL INPUT EXTERNAL NAME 'java.lang.Math.max' LANGUAGE JAVA PARAMETER STYLE JAVA");
102 ps = conn.prepareStatement("select * from t1 where -? = max_cni(-5,-1)");
103 ps.setInt(1,1);
104 pmd = ps.getParameterMetaData();
105 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
106 dumpRS(ps.executeQuery());
107
108 System.out.println("select * from t1 where -? = max_cni(-?,+?)");
109 ps = conn.prepareStatement("select * from t1 where -? = max_cni(-?,+?)");
110 ps.setInt(1,-1);
111 ps.setInt(2,1);
112 ps.setInt(3,1);
113 pmd = ps.getParameterMetaData();
114 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
115 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(2));
116 System.out.println("unary +? parameter type is " + pmd.getParameterTypeName(3));
117 dumpRS(ps.executeQuery());
118
119 System.out.println("Try the function again. But use, use sqrt(+?) & abs(-?) functions to send params");
120 System.out.println("select * from t1 where -? = max_cni(abs(-?), sqrt(+?))");
121 ps = conn.prepareStatement("select * from t1 where -? = max_cni(abs(-?), sqrt(+?))");
122 ps.setInt(1,-2);
123 ps.setInt(2,1);
124 ps.setInt(3,4);
125 pmd = ps.getParameterMetaData();
126 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
127 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(2));
128 System.out.println("unary +? parameter type is " + pmd.getParameterTypeName(3));
129 dumpRS(ps.executeQuery());
130
131 System.out.println("select * from t1 where c11 between -? and +?");
132 ps = conn.prepareStatement("select * from t1 where c11 between -? and +?");
133 ps.setInt(1,-1);
134 ps.setInt(2,1);
135 pmd = ps.getParameterMetaData();
136 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
137 System.out.println("unary +? parameter type is " + pmd.getParameterTypeName(2));
138 dumpRS(ps.executeQuery());
139
140 System.out.println("select * from t1 where +? not in (-?, +?, 2, ?)");
141 ps = conn.prepareStatement("select * from t1 where +? not in (-?, +?, 2, ?)");
142 ps.setInt(1,-11);
143 ps.setInt(2,1);
144 ps.setInt(3,1);
145 ps.setInt(4,4);
146 pmd = ps.getParameterMetaData();
147 System.out.println("unary +? parameter type is " + pmd.getParameterTypeName(1));
148 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(2));
149 System.out.println("unary +? parameter type is " + pmd.getParameterTypeName(3));
150 System.out.println("? parameter type is " + pmd.getParameterTypeName(4));
151 dumpRS(ps.executeQuery());
152
153 System.out.println("select * from t1 where +? < c12");
154 ps = conn.prepareStatement("select * from t1 where +? < c12");
155 ps.setInt(1,0);
156 pmd = ps.getParameterMetaData();
157 System.out.println("unary +? parameter type is " + pmd.getParameterTypeName(1));
158 dumpRS(ps.executeQuery());
159
160 System.out.println("select * from t1 where -? = c11 + ?");
161 ps = conn.prepareStatement("select * from t1 where -? = c11 + ?");
162 ps.setInt(1,2);
163 ps.setInt(2,-1);
164 pmd = ps.getParameterMetaData();
165 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
166 System.out.println("? parameter type is " + pmd.getParameterTypeName(1));
167 dumpRS(ps.executeQuery());
168
169 System.out.println("select * from t1 where c11 + ? = -?");
170 ps = conn.prepareStatement("select * from t1 where c11 + ? = -?");
171 ps.setInt(1,-1);
172 ps.setInt(2,2);
173 pmd = ps.getParameterMetaData();
174 System.out.println("? parameter type is " + pmd.getParameterTypeName(1));
175 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
176 dumpRS(ps.executeQuery());
177
178 System.out.println("select * from t1 where c11 + c12 = -?");
179 ps = conn.prepareStatement("select * from t1 where c11 + c12 = -?");
180 ps.setInt(1,2);
181 pmd = ps.getParameterMetaData();
182 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
183 dumpRS(ps.executeQuery());
184
185 System.out.println("select * from t1 where -? not in (select c21+? from t2)");
186 ps = conn.prepareStatement("select * from t1 where -? not in (select c21+? from t2)");
187 ps.setInt(1,1);
188 ps.setInt(2,2);
189 pmd = ps.getParameterMetaData();
190 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
191 System.out.println("? parameter type is " + pmd.getParameterTypeName(1));
192 dumpRS(ps.executeQuery());
193
194 System.out.println("select cast(-? as smallint), cast(+? as int) from t1");
195 ps = conn.prepareStatement("select cast(-? as smallint), cast(+? as int) from t1");
196 ps.setInt(1,2);
197 ps.setInt(2,2);
198 pmd = ps.getParameterMetaData();
199 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
200 System.out.println("unary +? parameter type is " + pmd.getParameterTypeName(2));
201 dumpRS(ps.executeQuery());
202
203 System.out.println("select nullif(-?,c11) from t1");
204 ps = conn.prepareStatement("select nullif(-?,c11) from t1");
205 ps.setInt(1,22);
206 pmd = ps.getParameterMetaData();
207 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
208 dumpRS(ps.executeQuery());
209
210 System.out.println("select sqrt(-?) from t1");
211 ps = conn.prepareStatement("select sqrt(-?) from t1");
212 ps.setInt(1,-64);
213 pmd = ps.getParameterMetaData();
214 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
215 dumpRS(ps.executeQuery());
216
217 System.out.println("select * from t1 where c11 = any (select -? from t2)");
218 try {
219 ps = conn.prepareStatement("select * from t1 where c11 = any (select -? from t2)");
220 ps.setInt(1,1);
221 pmd = ps.getParameterMetaData();
222 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
223 dumpRS(ps.executeQuery());
224 }
225 catch (SQLException e) {
226 System.out.println("SQL State : " + e.getSQLState());
227 System.out.println("Got expected exception " + e.getMessage());
228 }
229
230 System.out.println("Negative test - -?/+? at the beginning and/ at the end of where clause");
231 System.out.println("select * from t1 where -? and c11=c11 or +?");
232 try {
233 ps = conn.prepareStatement("select * from t1 where -? and c11=c11 or +?");
234 ps.setString(1,"SYS%");
235 ps.setString(2,"");
236 pmd = ps.getParameterMetaData();
237 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
238 System.out.println("unary +? parameter type is " + pmd.getParameterTypeName(2));
239 dumpRS(ps.executeQuery());
240 System.out.println("FAIL-test should have failed");
241 }
242 catch (SQLException e) {
243 System.out.println("SQL State : " + e.getSQLState());
244 System.out.println("Got expected exception " + e.getMessage());
245 }
246
247 System.out.println("Negative test - -?/+? in like escape function");
248 System.out.println("select * from sys.systables where tablename like -? escape +?");
249 try {
250 ps = conn.prepareStatement("select * from sys.systables where tablename like -? escape +?");
251 ps.setString(1,"SYS%");
252 ps.setString(2,"");
253 pmd = ps.getParameterMetaData();
254 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
255 System.out.println("unary +? parameter type is " + pmd.getParameterTypeName(2));
256 dumpRS(ps.executeQuery());
257 System.out.println("FAIL-test should have failed");
258 }
259 catch (SQLException e) {
260 System.out.println("SQL State : " + e.getSQLState());
261 System.out.println("Got expected exception " + e.getMessage());
262 }
263
264 System.out.println("Negative test - -?/+? in binary timestamp function");
265 System.out.println("select timestamp(-?,+?) from t1");
266 try {
267 ps = conn.prepareStatement("select timestamp(-?,+?) from t1");
268 ps.setInt(1,22);
269 ps.setInt(2,22);
270 pmd = ps.getParameterMetaData();
271 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
272 dumpRS(ps.executeQuery());
273 System.out.println("FAIL-test should have failed");
274 }
275 catch (SQLException e) {
276 System.out.println("SQL State : " + e.getSQLState());
277 System.out.println("Got expected exception " + e.getMessage());
278 }
279
280 System.out.println("Negative test - -? in unary timestamp function");
281 System.out.println("select timestamp(-?) from t1");
282 try {
283 ps = conn.prepareStatement("select timestamp(-?) from t1");
284 ps.setInt(1,22);
285 pmd = ps.getParameterMetaData();
286 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
287 dumpRS(ps.executeQuery());
288 System.out.println("FAIL-test should have failed");
289 }
290 catch (SQLException e) {
291 System.out.println("SQL State : " + e.getSQLState());
292 System.out.println("Got expected exception " + e.getMessage());
293 }
294
295 System.out.println("Negative test - -? in views");
296 System.out.println("create view v1 as select * from t1 where c11 = -?");
297 try {
298 ps = conn.prepareStatement("create view v1 as select * from t1 where c11 = -?");
299 ps.setInt(1,22);
300 pmd = ps.getParameterMetaData();
301 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
302 dumpRS(ps.executeQuery());
303 System.out.println("FAIL-test should have failed");
304 }
305 catch (SQLException e) {
306 System.out.println("SQL State : " + e.getSQLState());
307 System.out.println("Got expected exception " + e.getMessage());
308 }
309
310 System.out.println("Negative test - -? in inner join");
311 System.out.println("select * from t1 inner join t1 as t333 on -?");
312 try {
313 ps = conn.prepareStatement("select * from t1 inner join t1 as t333 on -?");
314 ps.setInt(1,22);
315 pmd = ps.getParameterMetaData();
316 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
317 dumpRS(ps.executeQuery());
318 System.out.println("FAIL-test should have failed");
319 }
320 catch (SQLException e) {
321 System.out.println("SQL State : " + e.getSQLState());
322 System.out.println("Got expected exception " + e.getMessage());
323 }
324
325 System.out.println("Negative test - -? by itself in where clause");
326 System.out.println("select * from t1 where -?");
327 try {
328 ps = conn.prepareStatement("select * from t1 where -?");
329 ps.setInt(1,22);
330 pmd = ps.getParameterMetaData();
331 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
332 dumpRS(ps.executeQuery());
333 System.out.println("FAIL-test should have failed");
334 }
335 catch (SQLException e) {
336 System.out.println("SQL State : " + e.getSQLState());
337 System.out.println("Got expected exception " + e.getMessage());
338 }
339
340 System.out.println("Negative test - -? is null not allowed because is null allowed on char types only");
341 System.out.println("select * from t1 where -? is null");
342 try {
343 ps = conn.prepareStatement("select * from t1 where -? is null");
344 ps.setInt(1,22);
345 pmd = ps.getParameterMetaData();
346 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
347 dumpRS(ps.executeQuery());
348 System.out.println("FAIL-test should have failed");
349 }
350 catch (SQLException e) {
351 System.out.println("SQL State : " + e.getSQLState());
352 System.out.println("Got expected exception " + e.getMessage());
353 }
354
355 System.out.println("select case when -?=c11 then -? else c12 end from t1");
356 ps = conn.prepareStatement("select case when -?=c11 then -? else c12 end from t1");
357 ps.setInt(1,1);
358 ps.setInt(2,22);
359 pmd = ps.getParameterMetaData();
360 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
361 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(2));
362 dumpRS(ps.executeQuery());
363
364 System.out.println("Negative test - unary plus parameters on both sides of / operator");
365 System.out.println("select * from t1 where c11 = ?/-?");
366 try {
367 ps = conn.prepareStatement("select * from t1 where c11 = ?/-?");
368 ps.setInt(1,0);
369 ps.setInt(2,0);
370 pmd = ps.getParameterMetaData();
371 System.out.println("? parameter type is " + pmd.getParameterTypeName(1));
372 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(2));
373 dumpRS(ps.executeQuery());
374 System.out.println("FAIL-test should have failed");
375 }
376 catch (SQLException e) {
377 System.out.println("SQL State : " + e.getSQLState());
378 System.out.println("Got expected exception " + e.getMessage());
379 }
380
381 System.out.println("Negative test - unary plus in || operation");
382 System.out.println("select c11 || +? from t1");
383 try {
384 ps = conn.prepareStatement("select c11 || +? from t1");
385 ps.setInt(1,0);
386 pmd = ps.getParameterMetaData();
387 System.out.println("? parameter type is " + pmd.getParameterTypeName(1));
388 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(2));
389 dumpRS(ps.executeQuery());
390 System.out.println("FAIL-test should have failed");
391 }
392 catch (SQLException e) {
393 System.out.println("SQL State : " + e.getSQLState());
394 System.out.println("Got expected exception " + e.getMessage());
395 }
396
397 System.out.println("Negative test - unary minus for char column");
398 System.out.println("select * from t1 where c14 = -?");
399 try {
400 ps = conn.prepareStatement("select * from t1 where c14 = -?");
401 ps.setInt(1,-1);
402 pmd = ps.getParameterMetaData();
403 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
404 dumpRS(ps.executeQuery());
405 System.out.println("FAIL-test should have failed");
406 }
407 catch (SQLException e) {
408 System.out.println("SQL State : " + e.getSQLState());
409 System.out.println("Got expected exception " + e.getMessage());
410 }
411
412 System.out.println("Negative test - unary plus for char column");
413 System.out.println("select * from t1 where c14 like +?");
414 try {
415 ps = conn.prepareStatement("select * from t1 where c14 like +?");
416 ps.setInt(1,-1);
417 pmd = ps.getParameterMetaData();
418 System.out.println("unary -? parameter type is " + pmd.getParameterTypeName(1));
419 dumpRS(ps.executeQuery());
420 System.out.println("FAIL-test should have failed");
421 }
422 catch (SQLException e) {
423 System.out.println("SQL State : " + e.getSQLState());
424 System.out.println("Got expected exception " + e.getMessage());
425 }
426 finally {
427 cleanUp(conn);
428 }
429 };
430
431 private static void cleanUp(Connection conn) throws SQLException
432 {
433 Statement stmt = conn.createStatement();
434 String [] testObjects = { "table t1", "table t2", "view v1",
435 "procedure abs_funct", "procedure max_cni"};
436 TestUtil.cleanUpTest(stmt, testObjects);
438 stmt.close();
439 conn.close();
440 }
441
442 private static void dumpRS(ResultSet s) throws SQLException
443 {
444 if (s == null)
445 {
446 System.out.println("<NULL>");
447 return;
448 }
449
450 java.sql.ResultSetMetaData rsmd = s.getMetaData();
451
452 int numCols = rsmd.getColumnCount();
454
455 if (numCols <= 0)
456 {
457 System.out.println("(no columns!)");
458 return;
459 }
460
461 StringBuffer heading = new StringBuffer ("\t ");
462 StringBuffer underline = new StringBuffer ("\t ");
463
464 int len;
465 for (int i=1; i<=numCols; i++)
467 {
468 if (i > 1)
469 {
470 heading.append(",");
471 underline.append(" ");
472 }
473 len = heading.length();
474 heading.append(rsmd.getColumnLabel(i));
475 len = heading.length() - len;
476 for (int j = len; j > 0; j--)
477 {
478 underline.append("-");
479 }
480 }
481 System.out.println(heading.toString());
482 System.out.println(underline.toString());
483
484
485 StringBuffer row = new StringBuffer ();
486 while (s.next())
488 {
489 row.append("\t{");
490 for (int i=1; i<=numCols; i++)
493 {
494 if (i > 1) row.append(",");
495 row.append(s.getString(i));
496 }
497 row.append("}\n");
498 }
499 System.out.println(row.toString());
500 s.close();
501 }
502 }
503
504 |
Popular Tags |
Java API By Example, From Geeks To Geeks.