001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.util.ProcedureTest
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.util;
023:
024: import org.apache.derbyTesting.functionTests.util.Formatters;
025:
026: import java.sql.*;
027: import java.math.BigDecimal;
028:
029: /**
030: Java procedures for the procedure.sql test.
031: */
032: public abstract class ProcedureTest extends SimpleProcedureTest
033: implements ResultSet {
034:
035: public static void zeroArg() {
036: System.out.println("zeroArg() called");
037: }
038:
039: public static void insertRow(int p1) throws SQLException {
040: insertRow(p1, "int");
041: }
042:
043: public static void insertRow(int p1, String p2) throws SQLException {
044: Connection conn = DriverManager
045: .getConnection("jdbc:default:connection");
046: PreparedStatement ps = conn
047: .prepareStatement("insert into t1 values (?, ?)");
048: ps.setInt(1, p1);
049: ps.setString(2, p2);
050: ps.executeUpdate();
051: ps.close();
052: conn.close();
053: }
054:
055: public static void updateRow(int p1) throws SQLException {
056: Connection conn = DriverManager
057: .getConnection("jdbc:default:connection");
058: PreparedStatement ps = conn
059: .prepareStatement("update t1 set i=i+?");
060: ps.setInt(1, p1);
061: ps.executeUpdate();
062: ps.close();
063: conn.close();
064: }
065:
066: public static void deleteRow(int p1) throws SQLException {
067: Connection conn = DriverManager
068: .getConnection("jdbc:default:connection");
069: PreparedStatement ps = conn
070: .prepareStatement("delete from t1 where i=?");
071: ps.setInt(1, p1);
072: ps.executeUpdate();
073: ps.close();
074: conn.close();
075: }
076:
077: public static void alterTable() throws SQLException {
078: Connection conn = DriverManager
079: .getConnection("jdbc:default:connection");
080: PreparedStatement ps = conn
081: .prepareStatement("alter table t1 add column test integer");
082: ps.execute();
083: ps.close();
084: conn.close();
085: }
086:
087: public static void dropTable() throws SQLException {
088: Connection conn = DriverManager
089: .getConnection("jdbc:default:connection");
090: PreparedStatement ps = conn.prepareStatement("drop table t1");
091: ps.execute();
092: ps.close();
093: conn.close();
094: }
095:
096: public static void createIndex() throws SQLException {
097: Connection conn = DriverManager
098: .getConnection("jdbc:default:connection");
099: PreparedStatement ps = conn
100: .prepareStatement("create index ix on t1(i,b)");
101: ps.execute();
102: ps.close();
103: conn.close();
104: }
105:
106: public static void dropIndex() throws SQLException {
107: Connection conn = DriverManager
108: .getConnection("jdbc:default:connection");
109: PreparedStatement ps = conn.prepareStatement("drop index ix");
110: ps.execute();
111: ps.close();
112: conn.close();
113: }
114:
115: public static void createTrigger() throws SQLException {
116: Connection conn = DriverManager
117: .getConnection("jdbc:default:connection");
118: PreparedStatement ps = conn
119: .prepareStatement("create trigger test_trig"
120: + " AFTER delete on t1 for each STATEMENT mode db2sql insert into"
121: + " t1 values(20, 'twenty')");
122: ps.execute();
123: ps.close();
124: conn.close();
125: }
126:
127: public static void dropTrigger() throws SQLException {
128: Connection conn = DriverManager
129: .getConnection("jdbc:default:connection");
130: PreparedStatement ps = conn
131: .prepareStatement("drop trigger test_trig");
132: ps.execute();
133: ps.close();
134: conn.close();
135: }
136:
137: public static int selectFromSpecificSchema(int p1)
138: throws SQLException {
139: Connection conn = DriverManager
140: .getConnection("jdbc:default:connection");
141: PreparedStatement ps = conn
142: .prepareStatement("select * from mamta1.t12RoutineTest");
143: ps.executeQuery();
144: ps.close();
145: conn.close();
146: return (1);
147: }
148:
149: private static void insertInBig(Connection conn, String A,
150: String B, String C, String D) throws SQLException {
151: PreparedStatement ps = conn
152: .prepareStatement("insert into big values (?, ?, ?, ?)");
153: ps.setString(1, A);
154: ps.setString(2, B);
155: ps.setString(3, C);
156: ps.setString(4, D);
157: ps.executeUpdate();
158: ps.close();
159: }
160:
161: public static void bigTestData(int i) throws SQLException {
162: Connection conn = DriverManager
163: .getConnection("jdbc:default:connection");
164: switch (i) {
165: case 1:
166: String largeStringA10000 = new String(Formatters
167: .repeatChar("a", 10000));
168: String largeStringB10000 = new String(Formatters
169: .repeatChar("b", 10000));
170: String largeStringC10000 = new String(Formatters
171: .repeatChar("c", 10000));
172: String largeStringD10000 = new String(Formatters
173: .repeatChar("d", 10000));
174: insertInBig(conn, largeStringA10000, largeStringB10000,
175: largeStringC10000, largeStringD10000);
176: break;
177: case 2:
178: largeStringA10000 = new String(Formatters.repeatChar("e",
179: 10000));
180: largeStringB10000 = new String(Formatters.repeatChar("f",
181: 10000));
182: largeStringC10000 = new String(Formatters.repeatChar("g",
183: 10000));
184: largeStringD10000 = new String(Formatters.repeatChar("h",
185: 10000));
186: insertInBig(conn, largeStringA10000, largeStringB10000,
187: largeStringC10000, largeStringD10000);
188: break;
189: case 3:
190: largeStringA10000 = new String(Formatters.repeatChar("i",
191: 10000));
192: largeStringB10000 = new String(Formatters.repeatChar("j",
193: 10000));
194: largeStringC10000 = new String(Formatters.repeatChar("k",
195: 10000));
196: largeStringD10000 = new String(Formatters.repeatChar("l",
197: 10000));
198: insertInBig(conn, largeStringA10000, largeStringB10000,
199: largeStringC10000, largeStringD10000);
200: break;
201: case 4:
202: largeStringA10000 = new String(Formatters.repeatChar("m",
203: 10000));
204: largeStringB10000 = new String(Formatters.repeatChar("n",
205: 10000));
206: largeStringC10000 = new String(Formatters.repeatChar("o",
207: 10000));
208: largeStringD10000 = new String(Formatters.repeatChar("p",
209: 10000));
210: insertInBig(conn, largeStringA10000, largeStringB10000,
211: largeStringC10000, largeStringD10000);
212: break;
213: case 5:
214: String largeStringA30000 = new String(Formatters
215: .repeatChar("a", 30000));
216: String largeStringB2752 = new String(Formatters.repeatChar(
217: "b", 2752));
218: PreparedStatement ps = conn
219: .prepareStatement("insert into big values (?, ?)");
220: ps.setString(1, largeStringA30000);
221: ps.setString(2, largeStringB2752);
222: ps.executeUpdate();
223: ps.close();
224: break;
225: case 6:
226: largeStringA30000 = new String(Formatters.repeatChar("a",
227: 30000));
228: String largeStringB2750 = new String(Formatters.repeatChar(
229: "b", 2750));
230: ps = conn.prepareStatement("insert into big values (?, ?)");
231: ps.setString(1, largeStringA30000);
232: ps.setString(2, largeStringB2750);
233: ps.executeUpdate();
234: ps.close();
235: break;
236: case 7:
237: String largeStringA40000 = new String(Formatters
238: .repeatChar("a", 40000));
239: ps = conn.prepareStatement("insert into big values (?)");
240: ps.setString(1, largeStringA40000);
241: ps.executeUpdate();
242: ps.close();
243: break;
244: case 8:
245: largeStringA40000 = new String(Formatters.repeatChar("a",
246: 40000));
247: String largeStringB40000 = new String(Formatters
248: .repeatChar("b", 40000));
249: String largeStringC40000 = new String(Formatters
250: .repeatChar("c", 40000));
251: ps = conn
252: .prepareStatement("insert into big values (?, ?, ?)");
253: ps.setString(1, largeStringA40000);
254: ps.setString(2, largeStringB40000);
255: ps.setString(3, largeStringC40000);
256: ps.executeUpdate();
257: largeStringA40000 = new String(Formatters.repeatChar("d",
258: 40000));
259: largeStringB40000 = new String(Formatters.repeatChar("e",
260: 40000));
261: largeStringC40000 = new String(Formatters.repeatChar("f",
262: 40000));
263: ps.setString(1, largeStringA40000);
264: ps.setString(2, largeStringB40000);
265: ps.setString(3, largeStringC40000);
266: ps.executeUpdate();
267: ps.close();
268: break;
269: case 9:
270: String lStringA32672 = new String(Formatters.repeatChar(
271: "a", 32672));
272: String lStringB32672 = new String(Formatters.repeatChar(
273: "b", 32672));
274: String lStringC32672 = new String(Formatters.repeatChar(
275: "c", 32672));
276: String lStringD32672 = new String(Formatters.repeatChar(
277: "d", 32672));
278: insertInBig(conn, lStringA32672, lStringB32672,
279: lStringC32672, lStringD32672);
280: break;
281: }
282: conn.close();
283: }
284:
285: //public static void selectRows_coll(int p1, java.util.Collection rs) throws SQLException {
286:
287: // ResultSet[] d1 = new ResultSet[1];
288: // selectRows(p1, d1);
289: // rs.add(d1[0]);
290: //}
291:
292: public static void selectRows(int p1, ResultSet[] data)
293: throws SQLException {
294:
295: System.out.println("selectRows - 1 arg - 1 rs");
296:
297: Connection conn = DriverManager
298: .getConnection("jdbc:default:connection");
299: PreparedStatement ps = conn
300: .prepareStatement("select * from t1 where i = ?");
301: ps.setInt(1, p1);
302: data[0] = ps.executeQuery();
303: conn.close();
304: }
305:
306: public static void selectRows(int p1, int p2, ResultSet[] data1,
307: ResultSet[] data2) throws SQLException {
308:
309: System.out.println("selectRows - 2 arg - 2 rs");
310:
311: Connection conn = DriverManager
312: .getConnection("jdbc:default:connection");
313: PreparedStatement ps = conn
314: .prepareStatement("select * from t1 where i = ?");
315: ps.setInt(1, p1);
316: data1[0] = ps.executeQuery();
317:
318: ps = conn.prepareStatement("select * from t1 where i >= ?");
319: ps.setInt(1, p2);
320: data2[0] = ps.executeQuery();
321:
322: if (p2 == 99)
323: data2[0].close();
324:
325: // return no results
326: if (p2 == 199) {
327: data1[0].close();
328: data1[0] = null;
329: data2[0].close();
330: data2[0] = null;
331: }
332:
333: // swap results
334: if (p2 == 299) {
335: ResultSet rs = data1[0];
336: data1[0] = data2[0];
337: data2[0] = rs;
338: }
339:
340: conn.close();
341: }
342:
343: // select all rows from a table
344: public static void selectRows(String table, ResultSet[] rs)
345: throws SQLException {
346: Connection conn = DriverManager
347: .getConnection("jdbc:default:connection");
348: Statement stmt = conn.createStatement();
349: rs[0] = stmt.executeQuery("SELECT * FROM " + table);
350: conn.close();
351: }
352:
353: public static void fivejp(ResultSet[] data1, ResultSet[] data2,
354: ResultSet[] data3, ResultSet[] data4, ResultSet[] data5)
355: throws SQLException {
356:
357: Connection conn = DriverManager
358: .getConnection("jdbc:default:connection");
359:
360: PreparedStatement ps1 = conn
361: .prepareStatement("select * from MRS.FIVERS where i > ?");
362: ps1.setInt(1, 1);
363: data1[0] = ps1.executeQuery();
364:
365: PreparedStatement ps2 = conn
366: .prepareStatement("select * from MRS.FIVERS where i > ?");
367: ps2.setInt(1, 2);
368: data2[0] = ps2.executeQuery();
369:
370: PreparedStatement ps3 = conn
371: .prepareStatement("select * from MRS.FIVERS where i > ?");
372: ps3.setInt(1, 3);
373: data3[0] = ps3.executeQuery();
374:
375: PreparedStatement ps4 = conn
376: .prepareStatement("select * from MRS.FIVERS where i > ?");
377: ps4.setInt(1, 4);
378: data4[0] = ps4.executeQuery();
379:
380: PreparedStatement ps5 = conn
381: .prepareStatement("select * from MRS.FIVERS where i > ?");
382: ps5.setInt(1, 5);
383: data5[0] = ps5.executeQuery();
384:
385: conn.close();
386: }
387:
388: public static void parameter1(int a, String b, String c,
389: java.sql.ResultSet[] rs) throws SQLException {
390:
391: System.out.print("PT1 a=" + a);
392: if (b == null)
393: System.out.println(" b = null");
394: else
395: System.out.print(" b=<" + b + ">(" + b.length() + ")");
396: if (c == null)
397: System.out.println(" c = null");
398: else
399: System.out.print(" c=<" + c + ">(" + c.length() + ")");
400:
401: System.out.println("");
402:
403: Connection conn = DriverManager
404: .getConnection("jdbc:default:connection");
405: PreparedStatement ps = conn
406: .prepareStatement("insert into PT1 values (?, ?, ?)");
407: ps.setInt(1, a);
408: ps.setString(2, b);
409: ps.setString(3, c);
410: ps.executeUpdate();
411: ps.close();
412: ps = conn
413: .prepareStatement("select a,b, length(b), c, length(c) from PT1 where a = ?");
414: ps.setInt(1, a);
415: rs[0] = ps.executeQuery();
416: conn.close();
417: }
418:
419: public static void parameter2(int a, java.math.BigDecimal b,
420: java.math.BigDecimal c, java.sql.ResultSet[] rs)
421: throws SQLException {
422: Connection conn = DriverManager
423: .getConnection("jdbc:default:connection");
424: PreparedStatement ps = conn
425: .prepareStatement("insert into PT1 values (?, ?, ?)");
426: ps.setInt(1, a);
427: ps.setString(2, b.toString());
428: ps.setString(3, c.toString());
429: ps.executeUpdate();
430: ps.close();
431: ps = conn.prepareStatement("select a,b,c from PT1 where a = ?");
432: ps.setInt(1, a);
433: rs[0] = ps.executeQuery();
434: conn.close();
435: }
436:
437: public static void outparams1(int[] p1, int p2) {
438:
439: p1[0] = p2 * 2;
440: }
441:
442: // Test for CLOB being returned.
443: public static void clobselect(ResultSet[] results,
444: ResultSet[] results1, ResultSet[] results2)
445: throws Exception {
446:
447: Connection conn = DriverManager
448: .getConnection("jdbc:default:connection");
449: PreparedStatement st = conn
450: .prepareStatement("select * from lobCheckOne");
451: results[0] = st.executeQuery();
452: // Just some regular data
453: PreparedStatement st1 = conn
454: .prepareStatement("select count(*) from lobCheckOne");
455: results1[0] = st1.executeQuery();
456: // Now more Clobs
457: PreparedStatement st2 = conn
458: .prepareStatement("select * from lobCheckOne");
459: results2[0] = st2.executeQuery();
460: conn.close();
461: return;
462:
463: }
464:
465: // Test for BLOB being returned.
466: public static void blobselect(ResultSet[] results) throws Exception {
467:
468: Connection conn = DriverManager
469: .getConnection("jdbc:default:connection");
470: PreparedStatement st = conn
471: .prepareStatement("select * from lobCheckTwo");
472: results[0] = st.executeQuery();
473: conn.close();
474: return;
475:
476: }
477:
478: public static void inoutparams2(int[] p1, int p2) {
479:
480: p1[0] = p1[0] + (p2 * 2);
481: }
482:
483: public static void inoutparams3(String[] p1, int p2) {
484:
485: if (p1[0] == null)
486: System.out.println("p1 is NULL");
487: else
488: System.out.println("p1= >" + p1[0] + "< length "
489: + p1[0].length());
490:
491: if (p2 == 8)
492: p1[0] = "nad";
493: else if (p2 == 9)
494: p1[0] = null;
495: else if (p2 == 10)
496: p1[0] = "abcdefghijklmnopqrstuvwzyz";
497: }
498:
499: public static void inoutparams4(java.math.BigDecimal[] p1, String p2) {
500: if (p2 == null)
501: p1[0] = null;
502: else {
503: if (p1[0] == null)
504: p1[0] = new BigDecimal(p2).add(new BigDecimal("17"));
505: else
506: p1[0] = new BigDecimal(p2).add(p1[0]);
507: }
508: }
509:
510: public static void ambigious1(int p1, String p2, ResultSet[] data1,
511: ResultSet[] data2) {
512: }
513:
514: public static void ambigious1(int p1, String p2, ResultSet[] data1) {
515: }
516:
517: public static void ambigious2(int p1, Integer p2) {
518: System.out.println("ambigious2(int,Integer) called");
519: };
520:
521: public static void ambigious2(Integer p1, int p2) {
522: System.out.println("ambigious2(Integer,int) called");
523: };
524:
525: public static void missingDynamicParameter(int p1) {
526: }
527:
528: public static void missingDynamicParameter(int p1, Object p2) {
529: }
530:
531: public static void badDynamicParameter(int p1, ProcedureTest[] data) {
532: }
533:
534: public static void zeroArgDynamicResult(ResultSet[] data1,
535: ResultSet[] data2, ResultSet[] data3, ResultSet[] data4) {
536: System.out.println("zeroArgDynamicResult called");
537: }
538:
539: public static void sqlControl(String[] e1, String[] e2,
540: String[] e3, String[] e4, String[] e5, String[] e6,
541: String[] e7) throws SQLException {
542:
543: Connection conn = DriverManager
544: .getConnection("jdbc:default:connection");
545:
546: Statement s = conn.createStatement();
547:
548: executeStatement(s, "CREATE TABLE SQLCONTROL_DDL (I INT)", e1);
549: executeStatement(
550: s,
551: "ALTER TABLE SQLC.SQLCONTROL_DML ADD COLUMN B INT DEFAULT NULL",
552: e2);
553:
554: executeStatement(s,
555: "INSERT INTO SQLC.SQLCONTROL_DML(I) VALUES (1)", e3);
556: executeStatement(s,
557: "UPDATE SQLC.SQLCONTROL_DML SET I = I + 11", e4);
558: executeStatement(s, "SELECT * FROM SQLC.SQLCONTROL_DML", e5);
559: executeStatement(s, "DELETE FROM SQLC.SQLCONTROL_DML", e6);
560:
561: executeStatement(s, "DROP TABLE SQLC.SQLCONTROL_DML", e7);
562:
563: conn.close();
564:
565: }
566:
567: public static void sqlControl2(String[] e1, String[] e2,
568: String[] e3, String[] e4, String[] e5, String[] e6,
569: String[] e7) throws SQLException {
570:
571: Connection conn = DriverManager
572: .getConnection("jdbc:default:connection");
573:
574: Statement s = conn.createStatement();
575:
576: executeStatement(
577: s,
578: "CREATE VIEW SQLCONTROL_VIEW AS SELECT * FROM SQLC.SQLCONTROL_DML",
579: e1);
580: executeStatement(s, "DROP VIEW SQLCONTROL_VIEW", e2);
581:
582: executeStatement(s,
583: "LOCK TABLE SQLC.SQLCONTROL_DML IN EXCLUSIVE MODE", e3);
584: executeStatement(s, "VALUES 1,2,3", e4);
585: executeStatement(s, "SET SCHEMA SQLC", e5);
586: executeStatement(s, "CREATE SCHEMA SQLC_M", e6);
587: executeStatement(s, "DROP SCHEMA SQLC_M RESTRICT", e7);
588:
589: conn.close();
590:
591: }
592:
593: public static void sqlControl3(String[] e1, String[] e2,
594: String[] e3, String[] e4, String[] e5, String[] e6,
595: String[] e7) throws SQLException {
596:
597: Connection conn = DriverManager
598: .getConnection("jdbc:default:connection");
599:
600: Statement s = conn.createStatement();
601:
602: e1[0] = "IBM CS FEATURE";
603: e2[0] = "IBM CS FEATURE";
604:
605: executeStatement(s, "SET ISOLATION CS", e3);
606: executeStatement(s, "SET RUNTIMESTATISTICS OFF", e4);
607: executeStatement(s, "SET STATISTICS TIMING OFF", e5);
608: executeStatement(s, "VALUES 1", e6);
609:
610: executeStatement(s, "VALUES 1", e7);
611:
612: conn.close();
613:
614: }
615:
616: public static void sqlControl4(int sqlc, String[] e1, String[] e2,
617: String[] e3, String[] e4, String[] e5, String[] e6,
618: String[] e7, String[] e8) throws SQLException {
619:
620: Connection conn = DriverManager
621: .getConnection("jdbc:default:connection");
622:
623: Statement s = conn.createStatement();
624:
625: String sql = "CALL SQLC.SQLCONTROL2_" + sqlc
626: + " (?, ?, ?, ?, ?, ?, ?) ";
627:
628: e1[0] = sql;
629:
630: CallableStatement cs1 = conn.prepareCall(sql);
631: try {
632: for (int rop = 1; rop <= 7; rop++) {
633: cs1.registerOutParameter(rop, Types.VARCHAR);
634: }
635: cs1.execute();
636:
637: e2[0] = cs1.getString(1);
638: e3[0] = cs1.getString(2);
639: e4[0] = cs1.getString(3);
640: e5[0] = cs1.getString(4);
641: e6[0] = cs1.getString(5);
642: e7[0] = cs1.getString(6);
643: e8[0] = cs1.getString(7);
644: } catch (SQLException sqle) {
645: StringBuffer sb = new StringBuffer(128);
646: sb.append("STATE");
647: do {
648: sb.append("-");
649: String ss = sqle.getSQLState();
650: if (ss == null)
651: ss = "?????";
652: sb.append(ss);
653: sqle = sqle.getNextException();
654: } while (sqle != null);
655: e2[0] = sb.toString();
656: }
657:
658: cs1.close();
659:
660: conn.close();
661:
662: }
663:
664: private static void executeStatement(Statement s, String sql,
665: String[] result) {
666:
667: StringBuffer sb = new StringBuffer(128);
668:
669: int len = sql.length();
670: if (len > 15)
671: len = 15;
672:
673: sb.append(sql.substring(0, len));
674: try {
675: if (s.execute(sql)) {
676: ResultSet rs = s.getResultSet();
677: while (rs.next())
678: sb.append("- ROW(" + rs.getString(1) + ")");
679: rs.close();
680: } else {
681: sb.append("-UPDATE " + s.getUpdateCount());
682: }
683:
684: sb.append("-EXECUTE OK");
685:
686: } catch (SQLException sqle) {
687:
688: do {
689: sb.append("-");
690: String ss = sqle.getSQLState();
691: if (ss == null)
692: ss = "?????";
693: sb.append(ss);
694: sqle = sqle.getNextException();
695: } while (sqle != null);
696:
697: }
698: result[0] = sb.toString();
699: }
700:
701: public static void oBOOLEAN(Boolean in, Boolean[] inout,
702: Boolean[] out) throws SQLException {
703:
704: if (out[0] != null)
705: throw new SQLException("oBOOLEAN expected out[] to be null");
706:
707: out[0] = in;
708: if (in == null)
709: inout[0] = null;
710: else
711: inout[0] = new Boolean(inout[0].booleanValue()
712: && in.booleanValue());
713:
714: }
715:
716: public static void pBOOLEAN(boolean in, boolean[] inout,
717: boolean[] out) throws SQLException {
718:
719: if (out[0] != false)
720: throw new SQLException("pBOOLEAN expected out[] to be null");
721:
722: out[0] = in;
723: inout[0] = inout[0] && in;
724:
725: }
726:
727: public static void oALLINT(Integer in, Integer[] inout,
728: Integer[] out) throws SQLException {
729:
730: if (out[0] != null)
731: throw new SQLException("oALLINT expected out[] to be null");
732:
733: out[0] = in;
734: if (in == null)
735: ;//inout[0] = null;
736: else if (inout[0] == null)
737: inout[0] = new Integer(3 * in.intValue());
738: else
739: inout[0] = new Integer(inout[0].intValue() + in.intValue());
740: }
741:
742: public static void pTINYINT(byte in, byte[] inout, byte[] out)
743: throws SQLException {
744:
745: out[0] = in;
746: inout[0] += in;
747: }
748:
749: public static void pSMALLINT(short in, short[] inout, short[] out)
750: throws SQLException {
751:
752: out[0] = in;
753: inout[0] += in;
754: }
755:
756: /*
757: ** Procedures for testing literals passed to procedures as IN parameters
758: */
759:
760: public static void literalTest(int p1, String[] p2) {
761: p2[0] = ">" + Integer.toString(p1) + "<";
762: }
763:
764: public static void literalTest(long p1, String[] p2) {
765: p2[0] = ">" + Long.toString(p1) + "<";
766: }
767:
768: public static void literalTest(float p1, String[] p2) {
769: p2[0] = ">" + Float.toString(p1) + "<";
770: }
771:
772: public static void literalTest(double p1, String[] p2) {
773: p2[0] = ">" + Double.toString(p1) + "<";
774: }
775:
776: public static void literalTest(BigDecimal p1, String[] p2) {
777: String s = p1 == null ? "NULL" : p1.toString();
778: p2[0] = ">" + s + "<";
779: }
780:
781: public static void literalTest(String p1, String[] p2) {
782: String s = p1 == null ? "NULL" : p1.toString();
783: p2[0] = ">" + s + "<";
784: }
785:
786: public static void literalTest(java.sql.Date p1, String[] p2) {
787: String s = p1 == null ? "NULL" : p1.toString();
788: p2[0] = ">" + s + "<";
789: }
790:
791: public static void literalTest(java.sql.Time p1, String[] p2) {
792: String s = p1 == null ? "NULL" : p1.toString();
793: p2[0] = ">" + s + "<";
794: }
795:
796: public static void literalTest(java.sql.Timestamp p1, String[] p2) {
797:
798: String s = p1 == null ? "NULL" : p1.toString();
799: p2[0] = ">" + s + "<";
800: }
801:
802: /*
803: ** Procedure which uses BigDecimal - for parameter mapping testing.
804: */
805:
806: public static void pmap(BigDecimal in, BigDecimal[] inout,
807: BigDecimal[] out) {
808: inout[0] = inout[0].add(new BigDecimal(2.3));
809: out[0] = new BigDecimal(84.1);
810: }
811:
812: public static int countRows(String schema, String table)
813: throws SQLException {
814: Connection conn = DriverManager
815: .getConnection("jdbc:default:connection");
816: Statement s = conn.createStatement();
817: ResultSet rs = s.executeQuery("SELECT COUNT(*) FROM " + schema
818: + "." + table);
819: rs.next();
820: int count = rs.getInt(1);
821: rs.close();
822: s.close();
823: conn.close();
824: return count;
825: }
826:
827: /**
828: * Procedure installed to test multiple ResultSets in the lang/procedure.java
829: * test class.
830: *
831: * @param p1 Number parameter for the first ResultSet
832: * @param p2 Number parameter for the second ResultSet
833: * @param data1 The first ResultSet to be returned.
834: * @param data2 The Second ResultSet to be returned
835: * @throws SQLException
836: */
837: public static void multiResult(int p1, int p2, ResultSet[] data1,
838: ResultSet[] data2) throws SQLException {
839:
840: Connection conn = DriverManager
841: .getConnection("jdbc:default:connection");
842: PreparedStatement ps = conn
843: .prepareStatement("select * from AutoCommitTable where num = ?");
844: ps.setInt(1, p1);
845: data1[0] = ps.executeQuery();
846:
847: ps = conn
848: .prepareStatement("select * from AutoCommitTable where num = ?");
849: ps.setInt(1, p2);
850: data2[0] = ps.executeQuery();
851:
852: conn.close();
853: }
854:
855: // Procedure used by the test for bug JIRA-491. The client side part
856: // of this test is in lang/procedure.java
857:
858: public static void BIG_COL_491(int i, ResultSet[] rs1,
859: ResultSet[] rs2) throws SQLException {
860: Connection conn = DriverManager
861: .getConnection("jdbc:default:connection");
862: Statement st1 = conn.createStatement();
863: rs1[0] = st1
864: .executeQuery("select int1, varchar32k from jira491 where int1 < "
865: + i + " order by 1");
866:
867: Statement st2 = conn.createStatement();
868: rs2[0] = st2
869: .executeQuery("select int1, varchar32k from jira491 where int1 > "
870: + i + " order by 1");
871: }
872:
873: // Procedure used by the test for bug JIRA-492. The client side part of
874: // this test is in lang/procedure.java
875:
876: public static void LOTS_O_COLS_492(ResultSet[] rs)
877: throws SQLException {
878: Connection conn = DriverManager
879: .getConnection("jdbc:default:connection");
880: Statement st1 = conn.createStatement();
881:
882: StringBuffer query = new StringBuffer("SELECT ");
883: for (int i = 0; i < 100; i++) {
884: int cno = 1000 + (i * 10);
885: if (i > 0)
886: query.append(", ");
887: query.append("id AS col").append(cno)
888: .append(", nsi as col").append(cno + 1).append(
889: ", ni AS col").append(cno + 2).append(
890: ", nbi AS col").append(cno + 3).append(
891: ", nd AS col").append(cno + 4).append(
892: ", nr AS col").append(cno + 5).append(
893: ", ndo AS col").append(cno + 6).append(" ");
894: }
895: query.append("FROM jira492 a WHERE a.id = 0");
896:
897: rs[0] = st1.executeQuery(query.toString());
898: }
899:
900: public static void grantSelect() throws SQLException {
901: Connection conn = DriverManager
902: .getConnection("jdbc:default:connection");
903: PreparedStatement ps = conn
904: .prepareStatement("grant select on t1 to user2");
905: ps.execute();
906: ps.close();
907: conn.close();
908: }
909:
910: public static void revokeSelect() throws SQLException {
911: Connection conn = DriverManager
912: .getConnection("jdbc:default:connection");
913: PreparedStatement ps = conn
914: .prepareStatement("revoke select on t1 from user2");
915: ps.execute();
916: ps.close();
917: conn.close();
918: }
919: }
|