001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.unaryArithmeticDynamicParameter
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.lang;
023:
024: import java.sql.Connection;
025: import java.sql.ParameterMetaData;
026: import java.sql.PreparedStatement;
027: import java.sql.ResultSet;
028: import java.sql.SQLException;
029: import java.sql.Statement;
030:
031: import org.apache.derby.tools.ij;
032: import org.apache.derbyTesting.functionTests.util.TestUtil;
033:
034: /**
035: This tests unary minus and unary plus as dynamic parameters in PreparedStatements.
036: Derby-582
037: */
038: public class unaryArithmeticDynamicParameter {
039:
040: public static void main(String[] argv) throws Throwable {
041: System.out
042: .println("Test using parameters for unary minus and unary plus");
043: ij.getPropertyArg(argv);
044: Connection conn = ij.startJBMS();
045: Statement s = conn.createStatement();
046: s
047: .executeUpdate("create table t1 (c11 int, c12 smallint, c13 double, c14 char(3))");
048: s.execute("create table t2 (c21 int)");
049: s.execute("insert into t2 values (-1),(-2)");
050:
051: System.out.println("insert into t1 values(?,+?,-?,?)");
052: PreparedStatement ps = conn
053: .prepareStatement("insert into t1 values(?,+?,-?,?)");
054: ps.setInt(1, 1);
055: ps.setInt(2, 1);
056: ps.setDouble(3, -1.1);
057: ps.setString(4, "abc");
058: ParameterMetaData pmd = ps.getParameterMetaData();
059: System.out.println("? parameter type is "
060: + pmd.getParameterTypeName(1));
061: System.out.println("unary +? parameter type is "
062: + pmd.getParameterTypeName(2));
063: System.out.println("unary -? parameter type is "
064: + pmd.getParameterTypeName(3));
065: ps.executeUpdate();
066: ps.setInt(1, -1);
067: ps.setInt(2, -1);
068: ps.setDouble(3, 1.0);
069: ps.setString(4, "def");
070: ps.executeUpdate();
071:
072: System.out
073: .println("select * from t1 where -? in (select c21 from t2)");
074: ps = conn
075: .prepareStatement("select * from t1 where -? in (select c21 from t2)");
076: ps.setInt(1, 1);
077: pmd = ps.getParameterMetaData();
078: System.out.println("unary -? parameter type is "
079: + pmd.getParameterTypeName(1));
080: dumpRS(ps.executeQuery());
081:
082: System.out
083: .println("select * from t1 where c11 = -? and c12 = +? and c13 = ?");
084: ps = conn
085: .prepareStatement("select * from t1 where c11 = -? and c12 = +? and c13 = ?");
086: ps.setInt(1, -1);
087: ps.setInt(2, 1);
088: ps.setDouble(3, 1.1);
089: pmd = ps.getParameterMetaData();
090: System.out.println("unary -? parameter type is "
091: + pmd.getParameterTypeName(1));
092: System.out.println("unary +? parameter type is "
093: + pmd.getParameterTypeName(2));
094: System.out.println("? parameter type is "
095: + pmd.getParameterTypeName(3));
096: dumpRS(ps.executeQuery());
097: ps.setShort(1, (short) 1);
098: ps.setInt(2, -1);
099: ps.setInt(3, -1);
100: dumpRS(ps.executeQuery());
101:
102: System.out.println("select * from t1 where -? = ABS_FUNCT(+?)");
103: s
104: .execute("CREATE FUNCTION ABS_FUNCT(P1 INT) RETURNS INT CALLED ON NULL INPUT EXTERNAL NAME 'java.lang.Math.abs' LANGUAGE JAVA PARAMETER STYLE JAVA");
105: ps = conn
106: .prepareStatement("select * from t1 where -? = abs_funct(+?)");
107: ps.setInt(1, -1);
108: ps.setInt(2, 1);
109: pmd = ps.getParameterMetaData();
110: System.out.println("unary -? parameter type is "
111: + pmd.getParameterTypeName(1));
112: System.out.println("unary +? parameter type is "
113: + pmd.getParameterTypeName(2));
114: dumpRS(ps.executeQuery());
115:
116: System.out
117: .println("select * from t1 where -? = max_cni(-5,-1)");
118: s
119: .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");
120: ps = conn
121: .prepareStatement("select * from t1 where -? = max_cni(-5,-1)");
122: ps.setInt(1, 1);
123: pmd = ps.getParameterMetaData();
124: System.out.println("unary -? parameter type is "
125: + pmd.getParameterTypeName(1));
126: dumpRS(ps.executeQuery());
127:
128: System.out
129: .println("select * from t1 where -? = max_cni(-?,+?)");
130: ps = conn
131: .prepareStatement("select * from t1 where -? = max_cni(-?,+?)");
132: ps.setInt(1, -1);
133: ps.setInt(2, 1);
134: ps.setInt(3, 1);
135: pmd = ps.getParameterMetaData();
136: System.out.println("unary -? parameter type is "
137: + pmd.getParameterTypeName(1));
138: System.out.println("unary -? parameter type is "
139: + pmd.getParameterTypeName(2));
140: System.out.println("unary +? parameter type is "
141: + pmd.getParameterTypeName(3));
142: dumpRS(ps.executeQuery());
143:
144: System.out
145: .println("Try the function again. But use, use sqrt(+?) & abs(-?) functions to send params");
146: System.out
147: .println("select * from t1 where -? = max_cni(abs(-?), sqrt(+?))");
148: ps = conn
149: .prepareStatement("select * from t1 where -? = max_cni(abs(-?), sqrt(+?))");
150: ps.setInt(1, -2);
151: ps.setInt(2, 1);
152: ps.setInt(3, 4);
153: pmd = ps.getParameterMetaData();
154: System.out.println("unary -? parameter type is "
155: + pmd.getParameterTypeName(1));
156: System.out.println("unary -? parameter type is "
157: + pmd.getParameterTypeName(2));
158: System.out.println("unary +? parameter type is "
159: + pmd.getParameterTypeName(3));
160: dumpRS(ps.executeQuery());
161:
162: System.out
163: .println("select * from t1 where c11 between -? and +?");
164: ps = conn
165: .prepareStatement("select * from t1 where c11 between -? and +?");
166: ps.setInt(1, -1);
167: ps.setInt(2, 1);
168: pmd = ps.getParameterMetaData();
169: System.out.println("unary -? parameter type is "
170: + pmd.getParameterTypeName(1));
171: System.out.println("unary +? parameter type is "
172: + pmd.getParameterTypeName(2));
173: dumpRS(ps.executeQuery());
174:
175: System.out
176: .println("select * from t1 where +? not in (-?, +?, 2, ?)");
177: ps = conn
178: .prepareStatement("select * from t1 where +? not in (-?, +?, 2, ?)");
179: ps.setInt(1, -11);
180: ps.setInt(2, 1);
181: ps.setInt(3, 1);
182: ps.setInt(4, 4);
183: pmd = ps.getParameterMetaData();
184: System.out.println("unary +? parameter type is "
185: + pmd.getParameterTypeName(1));
186: System.out.println("unary -? parameter type is "
187: + pmd.getParameterTypeName(2));
188: System.out.println("unary +? parameter type is "
189: + pmd.getParameterTypeName(3));
190: System.out.println("? parameter type is "
191: + pmd.getParameterTypeName(4));
192: dumpRS(ps.executeQuery());
193:
194: System.out.println("select * from t1 where +? < c12");
195: ps = conn.prepareStatement("select * from t1 where +? < c12");
196: ps.setInt(1, 0);
197: pmd = ps.getParameterMetaData();
198: System.out.println("unary +? parameter type is "
199: + pmd.getParameterTypeName(1));
200: dumpRS(ps.executeQuery());
201:
202: System.out.println("select * from t1 where -? = c11 + ?");
203: ps = conn
204: .prepareStatement("select * from t1 where -? = c11 + ?");
205: ps.setInt(1, 2);
206: ps.setInt(2, -1);
207: pmd = ps.getParameterMetaData();
208: System.out.println("unary -? parameter type is "
209: + pmd.getParameterTypeName(1));
210: System.out.println("? parameter type is "
211: + pmd.getParameterTypeName(1));
212: dumpRS(ps.executeQuery());
213:
214: System.out.println("select * from t1 where c11 + ? = -?");
215: ps = conn
216: .prepareStatement("select * from t1 where c11 + ? = -?");
217: ps.setInt(1, -1);
218: ps.setInt(2, 2);
219: pmd = ps.getParameterMetaData();
220: System.out.println("? parameter type is "
221: + pmd.getParameterTypeName(1));
222: System.out.println("unary -? parameter type is "
223: + pmd.getParameterTypeName(1));
224: dumpRS(ps.executeQuery());
225:
226: System.out.println("select * from t1 where c11 + c12 = -?");
227: ps = conn
228: .prepareStatement("select * from t1 where c11 + c12 = -?");
229: ps.setInt(1, 2);
230: pmd = ps.getParameterMetaData();
231: System.out.println("unary -? parameter type is "
232: + pmd.getParameterTypeName(1));
233: dumpRS(ps.executeQuery());
234:
235: System.out
236: .println("select * from t1 where -? not in (select c21+? from t2)");
237: ps = conn
238: .prepareStatement("select * from t1 where -? not in (select c21+? from t2)");
239: ps.setInt(1, 1);
240: ps.setInt(2, 2);
241: pmd = ps.getParameterMetaData();
242: System.out.println("unary -? parameter type is "
243: + pmd.getParameterTypeName(1));
244: System.out.println("? parameter type is "
245: + pmd.getParameterTypeName(1));
246: dumpRS(ps.executeQuery());
247:
248: System.out
249: .println("select cast(-? as smallint), cast(+? as int) from t1");
250: ps = conn
251: .prepareStatement("select cast(-? as smallint), cast(+? as int) from t1");
252: ps.setInt(1, 2);
253: ps.setInt(2, 2);
254: pmd = ps.getParameterMetaData();
255: System.out.println("unary -? parameter type is "
256: + pmd.getParameterTypeName(1));
257: System.out.println("unary +? parameter type is "
258: + pmd.getParameterTypeName(2));
259: dumpRS(ps.executeQuery());
260:
261: System.out.println("select nullif(-?,c11) from t1");
262: ps = conn.prepareStatement("select nullif(-?,c11) from t1");
263: ps.setInt(1, 22);
264: pmd = ps.getParameterMetaData();
265: System.out.println("unary -? parameter type is "
266: + pmd.getParameterTypeName(1));
267: dumpRS(ps.executeQuery());
268:
269: System.out.println("select sqrt(-?) from t1");
270: ps = conn.prepareStatement("select sqrt(-?) from t1");
271: ps.setInt(1, -64);
272: pmd = ps.getParameterMetaData();
273: System.out.println("unary -? parameter type is "
274: + pmd.getParameterTypeName(1));
275: dumpRS(ps.executeQuery());
276:
277: System.out
278: .println("select * from t1 where c11 = any (select -? from t2)");
279: try {
280: ps = conn
281: .prepareStatement("select * from t1 where c11 = any (select -? from t2)");
282: ps.setInt(1, 1);
283: pmd = ps.getParameterMetaData();
284: System.out.println("unary -? parameter type is "
285: + pmd.getParameterTypeName(1));
286: dumpRS(ps.executeQuery());
287: } catch (SQLException e) {
288: System.out.println("SQL State : " + e.getSQLState());
289: System.out.println("Got expected exception "
290: + e.getMessage());
291: }
292:
293: System.out
294: .println("Negative test - -?/+? at the beginning and/ at the end of where clause");
295: System.out
296: .println("select * from t1 where -? and c11=c11 or +?");
297: try {
298: ps = conn
299: .prepareStatement("select * from t1 where -? and c11=c11 or +?");
300: ps.setString(1, "SYS%");
301: ps.setString(2, "");
302: pmd = ps.getParameterMetaData();
303: System.out.println("unary -? parameter type is "
304: + pmd.getParameterTypeName(1));
305: System.out.println("unary +? parameter type is "
306: + pmd.getParameterTypeName(2));
307: dumpRS(ps.executeQuery());
308: System.out.println("FAIL-test should have failed");
309: } catch (SQLException e) {
310: System.out.println("SQL State : " + e.getSQLState());
311: System.out.println("Got expected exception "
312: + e.getMessage());
313: }
314:
315: System.out
316: .println("Negative test - -?/+? in like escape function");
317: System.out
318: .println("select * from sys.systables where tablename like -? escape +?");
319: try {
320: ps = conn
321: .prepareStatement("select * from sys.systables where tablename like -? escape +?");
322: ps.setString(1, "SYS%");
323: ps.setString(2, "");
324: pmd = ps.getParameterMetaData();
325: System.out.println("unary -? parameter type is "
326: + pmd.getParameterTypeName(1));
327: System.out.println("unary +? parameter type is "
328: + pmd.getParameterTypeName(2));
329: dumpRS(ps.executeQuery());
330: System.out.println("FAIL-test should have failed");
331: } catch (SQLException e) {
332: System.out.println("SQL State : " + e.getSQLState());
333: System.out.println("Got expected exception "
334: + e.getMessage());
335: }
336:
337: System.out
338: .println("Negative test - -?/+? in binary timestamp function");
339: System.out.println("select timestamp(-?,+?) from t1");
340: try {
341: ps = conn
342: .prepareStatement("select timestamp(-?,+?) from t1");
343: ps.setInt(1, 22);
344: ps.setInt(2, 22);
345: pmd = ps.getParameterMetaData();
346: System.out.println("unary -? parameter type is "
347: + pmd.getParameterTypeName(1));
348: dumpRS(ps.executeQuery());
349: System.out.println("FAIL-test should have failed");
350: } catch (SQLException e) {
351: System.out.println("SQL State : " + e.getSQLState());
352: System.out.println("Got expected exception "
353: + e.getMessage());
354: }
355:
356: System.out
357: .println("Negative test - -? in unary timestamp function");
358: System.out.println("select timestamp(-?) from t1");
359: try {
360: ps = conn.prepareStatement("select timestamp(-?) from t1");
361: ps.setInt(1, 22);
362: pmd = ps.getParameterMetaData();
363: System.out.println("unary -? parameter type is "
364: + pmd.getParameterTypeName(1));
365: dumpRS(ps.executeQuery());
366: System.out.println("FAIL-test should have failed");
367: } catch (SQLException e) {
368: System.out.println("SQL State : " + e.getSQLState());
369: System.out.println("Got expected exception "
370: + e.getMessage());
371: }
372:
373: System.out.println("Negative test - -? in views");
374: System.out
375: .println("create view v1 as select * from t1 where c11 = -?");
376: try {
377: ps = conn
378: .prepareStatement("create view v1 as select * from t1 where c11 = -?");
379: ps.setInt(1, 22);
380: pmd = ps.getParameterMetaData();
381: System.out.println("unary -? parameter type is "
382: + pmd.getParameterTypeName(1));
383: dumpRS(ps.executeQuery());
384: System.out.println("FAIL-test should have failed");
385: } catch (SQLException e) {
386: System.out.println("SQL State : " + e.getSQLState());
387: System.out.println("Got expected exception "
388: + e.getMessage());
389: }
390:
391: System.out.println("Negative test - -? in inner join");
392: System.out
393: .println("select * from t1 inner join t1 as t333 on -?");
394: try {
395: ps = conn
396: .prepareStatement("select * from t1 inner join t1 as t333 on -?");
397: ps.setInt(1, 22);
398: pmd = ps.getParameterMetaData();
399: System.out.println("unary -? parameter type is "
400: + pmd.getParameterTypeName(1));
401: dumpRS(ps.executeQuery());
402: System.out.println("FAIL-test should have failed");
403: } catch (SQLException e) {
404: System.out.println("SQL State : " + e.getSQLState());
405: System.out.println("Got expected exception "
406: + e.getMessage());
407: }
408:
409: System.out
410: .println("Negative test - -? by itself in where clause");
411: System.out.println("select * from t1 where -?");
412: try {
413: ps = conn.prepareStatement("select * from t1 where -?");
414: ps.setInt(1, 22);
415: pmd = ps.getParameterMetaData();
416: System.out.println("unary -? parameter type is "
417: + pmd.getParameterTypeName(1));
418: dumpRS(ps.executeQuery());
419: System.out.println("FAIL-test should have failed");
420: } catch (SQLException e) {
421: System.out.println("SQL State : " + e.getSQLState());
422: System.out.println("Got expected exception "
423: + e.getMessage());
424: }
425:
426: System.out
427: .println("Negative test - -? is null not allowed because is null allowed on char types only");
428: System.out.println("select * from t1 where -? is null");
429: try {
430: ps = conn
431: .prepareStatement("select * from t1 where -? is null");
432: ps.setInt(1, 22);
433: pmd = ps.getParameterMetaData();
434: System.out.println("unary -? parameter type is "
435: + pmd.getParameterTypeName(1));
436: dumpRS(ps.executeQuery());
437: System.out.println("FAIL-test should have failed");
438: } catch (SQLException e) {
439: System.out.println("SQL State : " + e.getSQLState());
440: System.out.println("Got expected exception "
441: + e.getMessage());
442: }
443:
444: System.out
445: .println("select case when -?=c11 then -? else c12 end from t1");
446: ps = conn
447: .prepareStatement("select case when -?=c11 then -? else c12 end from t1");
448: ps.setInt(1, 1);
449: ps.setInt(2, 22);
450: pmd = ps.getParameterMetaData();
451: System.out.println("unary -? parameter type is "
452: + pmd.getParameterTypeName(1));
453: System.out.println("unary -? parameter type is "
454: + pmd.getParameterTypeName(2));
455: dumpRS(ps.executeQuery());
456:
457: System.out
458: .println("Negative test - unary plus parameters on both sides of / operator");
459: System.out.println("select * from t1 where c11 = ?/-?");
460: try {
461: ps = conn
462: .prepareStatement("select * from t1 where c11 = ?/-?");
463: ps.setInt(1, 0);
464: ps.setInt(2, 0);
465: pmd = ps.getParameterMetaData();
466: System.out.println("? parameter type is "
467: + pmd.getParameterTypeName(1));
468: System.out.println("unary -? parameter type is "
469: + pmd.getParameterTypeName(2));
470: dumpRS(ps.executeQuery());
471: System.out.println("FAIL-test should have failed");
472: } catch (SQLException e) {
473: System.out.println("SQL State : " + e.getSQLState());
474: System.out.println("Got expected exception "
475: + e.getMessage());
476: }
477:
478: System.out
479: .println("Negative test - unary plus in || operation");
480: System.out.println("select c11 || +? from t1");
481: try {
482: ps = conn.prepareStatement("select c11 || +? from t1");
483: ps.setInt(1, 0);
484: pmd = ps.getParameterMetaData();
485: System.out.println("? parameter type is "
486: + pmd.getParameterTypeName(1));
487: System.out.println("unary -? parameter type is "
488: + pmd.getParameterTypeName(2));
489: dumpRS(ps.executeQuery());
490: System.out.println("FAIL-test should have failed");
491: } catch (SQLException e) {
492: System.out.println("SQL State : " + e.getSQLState());
493: System.out.println("Got expected exception "
494: + e.getMessage());
495: }
496:
497: System.out
498: .println("Negative test - unary minus for char column");
499: System.out.println("select * from t1 where c14 = -?");
500: try {
501: ps = conn
502: .prepareStatement("select * from t1 where c14 = -?");
503: ps.setInt(1, -1);
504: pmd = ps.getParameterMetaData();
505: System.out.println("unary -? parameter type is "
506: + pmd.getParameterTypeName(1));
507: dumpRS(ps.executeQuery());
508: System.out.println("FAIL-test should have failed");
509: } catch (SQLException e) {
510: System.out.println("SQL State : " + e.getSQLState());
511: System.out.println("Got expected exception "
512: + e.getMessage());
513: }
514:
515: System.out
516: .println("Negative test - unary plus for char column");
517: System.out.println("select * from t1 where c14 like +?");
518: try {
519: ps = conn
520: .prepareStatement("select * from t1 where c14 like +?");
521: ps.setInt(1, -1);
522: pmd = ps.getParameterMetaData();
523: System.out.println("unary -? parameter type is "
524: + pmd.getParameterTypeName(1));
525: dumpRS(ps.executeQuery());
526: System.out.println("FAIL-test should have failed");
527: } catch (SQLException e) {
528: System.out.println("SQL State : " + e.getSQLState());
529: System.out.println("Got expected exception "
530: + e.getMessage());
531: } finally {
532: cleanUp(conn);
533: }
534: };
535:
536: private static void cleanUp(Connection conn) throws SQLException {
537: Statement stmt = conn.createStatement();
538: String[] testObjects = { "table t1", "table t2", "view v1",
539: "procedure abs_funct", "procedure max_cni" };
540: // this will drop all testobjects listed
541: TestUtil.cleanUpTest(stmt, testObjects);
542: stmt.close();
543: conn.close();
544: }
545:
546: private static void dumpRS(ResultSet s) throws SQLException {
547: if (s == null) {
548: System.out.println("<NULL>");
549: return;
550: }
551:
552: java.sql.ResultSetMetaData rsmd = s.getMetaData();
553:
554: // Get the number of columns in the result set
555: int numCols = rsmd.getColumnCount();
556:
557: if (numCols <= 0) {
558: System.out.println("(no columns!)");
559: return;
560: }
561:
562: StringBuffer heading = new StringBuffer("\t ");
563: StringBuffer underline = new StringBuffer("\t ");
564:
565: int len;
566: // Display column headings
567: for (int i = 1; i <= numCols; i++) {
568: if (i > 1) {
569: heading.append(",");
570: underline.append(" ");
571: }
572: len = heading.length();
573: heading.append(rsmd.getColumnLabel(i));
574: len = heading.length() - len;
575: for (int j = len; j > 0; j--) {
576: underline.append("-");
577: }
578: }
579: System.out.println(heading.toString());
580: System.out.println(underline.toString());
581:
582: StringBuffer row = new StringBuffer();
583: // Display data, fetching until end of the result set
584: while (s.next()) {
585: row.append("\t{");
586: // Loop through each column, getting the
587: // column data and displaying
588: for (int i = 1; i <= numCols; i++) {
589: if (i > 1)
590: row.append(",");
591: row.append(s.getString(i));
592: }
593: row.append("}\n");
594: }
595: System.out.println(row.toString());
596: s.close();
597: }
598: }
|