001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.derbynet.callable
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.derbynet;
023:
024: import java.math.BigDecimal;
025: import java.sql.Date;
026: import java.sql.Time;
027: import java.sql.Timestamp;
028: import java.sql.CallableStatement;
029: import java.sql.Statement;
030: import java.sql.PreparedStatement;
031: import java.sql.ResultSet;
032: import java.sql.SQLException;
033: import java.sql.BatchUpdateException;
034: import java.sql.DriverManager;
035: import java.sql.Connection;
036: import org.apache.derby.tools.ij;
037: import org.apache.derbyTesting.functionTests.util.TestUtil;
038:
039: /**
040: This test tests the JDBC CallableStatement.
041: */
042:
043: public class callable {
044:
045: public static void main(String args[]) {
046: try {
047: System.out.println("CallableStatement Test Starts");
048:
049: ij.getPropertyArg(args);
050:
051: // This also tests quoted pathname in database name portion of URL, beetle 4781.
052: String protocol = System.getProperty("ij.protocol");
053: String hostName = TestUtil.getHostName();
054: System.setProperty("ij.database", protocol + "//"
055: + hostName + "/\""
056: + System.getProperty("derby.system.home")
057: + java.io.File.separator + "wombat;create=true\"");
058: ij.getPropertyArg(args);
059: Connection conn = ij.startJBMS();
060: if (conn == null) {
061: System.out.println("conn didn't work");
062: return;
063: }
064: Statement stmt = conn.createStatement();
065:
066: // 2 input, 1 output
067: stmt
068: .execute("CREATE PROCEDURE method1(IN P1 INT, IN P2 INT, OUT P3 INT) "
069: + "EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method1'"
070: + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
071: CallableStatement cs = conn
072: .prepareCall("call method1 (?, ?, ?)");
073: cs.setInt(1, 6);
074: cs.setInt(2, 9);
075: cs.registerOutParameter(3, java.sql.Types.INTEGER);
076: cs.execute();
077: int sum = cs.getInt(3);
078: System.out.println("Sum of 6 and 9 is: " + sum);
079: cs.close();
080: stmt.execute("DROP PROCEDURE method1");
081:
082: // method returns calue, plus 1 input
083: stmt
084: .execute("CREATE FUNCTION method2(P1 INT) RETURNS INT"
085: + " EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method2'"
086: + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
087: cs = conn.prepareCall("? = call method2 (?)");
088: cs.registerOutParameter(1, java.sql.Types.INTEGER);
089: cs.setInt(2, 6);
090: cs.execute();
091: int ret = cs.getInt(1);
092: System.out
093: .println("return value: Square of 6 then plus 6 is: "
094: + ret);
095: cs.close();
096: // stmt.execute("DROP FUNCTION method2");
097:
098: // no parameter
099: stmt
100: .execute("CREATE PROCEDURE method3() "
101: + "EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method3'"
102: + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
103: cs = conn.prepareCall("call method3 ()");
104: cs.execute();
105: cs.close();
106: stmt.execute("DROP PROCEDURE method3");
107:
108: // only 1 return parameter
109: stmt
110: .execute("CREATE FUNCTION method4() RETURNS INT"
111: + " EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method4'"
112: + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
113: cs = conn.prepareCall("? = call method4()");
114: cs.registerOutParameter(1, java.sql.Types.INTEGER);
115: cs.execute();
116: System.out.println("return value is: " + cs.getInt(1));
117: cs.close();
118: // stmt.execute("DROP FUNCTION method4");
119:
120: // DERBY-1184: User-defined output parameter not supported:
121: if (!TestUtil.isJCCFramework()) {
122: cs = conn.prepareCall("? = call method4()");
123: try {
124: cs.registerOutParameter(1, java.sql.Types.INTEGER,
125: "user-def");
126: System.out
127: .println("DERBY-1184 FAIL: Expected exception");
128: } catch (SQLException expectedException) {
129: if (!"0A000"
130: .equals(expectedException.getSQLState())) {
131: System.out
132: .println("DERBY-1184: Caught UNexpected: "
133: + expectedException
134: .getMessage());
135: System.out.println("DERBY-1184: SQLState: "
136: + expectedException.getSQLState()
137: + ", errorCode: "
138: + expectedException.getErrorCode());
139: }
140: }
141: cs.close();
142: }
143:
144: // different parameter types, also method overload
145: stmt
146: .execute("CREATE PROCEDURE method4P("
147: + "IN P1 SMALLINT, IN P2 INT, IN P3 BIGINT, IN P4 REAL, "
148: + "IN P5 DOUBLE, IN P6 DECIMAL(6,3), IN P7 DATE, IN P8 TIME, IN P9 TIMESTAMP, IN P10 VARCHAR(20) FOR BIT DATA, "
149: + "OUT O1 SMALLINT, OUT O2 INT, OUT O3 BIGINT, OUT O4 REAL, "
150: + "OUT O5 DOUBLE, OUT O6 DECIMAL(6,3), OUT O7 DATE, OUT O8 TIME, OUT O9 TIMESTAMP, OUT O10 VARCHAR(20) FOR BIT DATA"
151: + ") "
152: + "EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method4'"
153: + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
154: cs = conn
155: .prepareCall("call method4P(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
156: cs.setShort(1, (short) 3);
157: cs.setInt(2, 4);
158: cs.setLong(3, 5);
159: cs.setFloat(4, (float) 6.0);
160: cs.setDouble(5, 7.0);
161: cs.setBigDecimal(6, new BigDecimal("88.88"));
162: cs.setDate(7, Date.valueOf("2002-05-12"));
163: cs.setTime(8, Time.valueOf("10:05:02"));
164: cs.setTimestamp(9, Timestamp
165: .valueOf("2002-05-12 10:05:02.000000000"));
166: byte[] ba = new byte[2];
167: ba[0] = 1;
168: ba[1] = 2;
169: cs.setBytes(10, ba);
170: int n = 10;
171: cs.registerOutParameter(n + 1, java.sql.Types.SMALLINT);
172: cs.registerOutParameter(n + 2, java.sql.Types.INTEGER);
173: cs.registerOutParameter(n + 3, java.sql.Types.BIGINT);
174: cs.registerOutParameter(n + 4, java.sql.Types.REAL);
175: cs.registerOutParameter(n + 5, java.sql.Types.DOUBLE);
176: cs.registerOutParameter(n + 6, java.sql.Types.DECIMAL);
177: cs.registerOutParameter(n + 7, java.sql.Types.DATE);
178: cs.registerOutParameter(n + 8, java.sql.Types.TIME);
179: cs.registerOutParameter(n + 9, java.sql.Types.TIMESTAMP);
180: cs.registerOutParameter(n + 10, java.sql.Types.VARBINARY);
181: cs.execute();
182: System.out.println("return short: " + cs.getShort(n + 1));
183: System.out.println("return int: " + cs.getInt(n + 2));
184: System.out.println("return long: " + cs.getLong(n + 3));
185: System.out.println("return float: " + cs.getFloat(n + 4));
186: System.out.println("return double: " + cs.getDouble(n + 5));
187: System.out.println("return decimal: "
188: + cs.getBigDecimal(n + 6));
189: System.out.println("return date: " + cs.getDate(n + 7));
190: System.out.println("return time: " + cs.getTime(n + 8));
191: System.out.println("return time stamp: "
192: + cs.getTimestamp(n + 9));
193: ba = cs.getBytes(n + 10);
194: for (int i = 0; i < ba.length; i++)
195: System.out.println("return byte[" + i + "]: " + ba[i]);
196: stmt.execute("DROP PROCEDURE method4P");
197:
198: // some tests on BigDecimal
199: stmt
200: .execute("CREATE PROCEDURE method5("
201: + "IN P1 DECIMAL(14,4), OUT P2 DECIMAL(14,4), IN P3 DECIMAL(14,4), OUT P4 DECIMAL(14,4), "
202: + "OUT P5 DECIMAL(14,4), OUT P6 DECIMAL(14,4), OUT P7 DECIMAL(14,4), OUT P8 DECIMAL(14,4), OUT P9 DECIMAL(14,4) "
203: + ") "
204: + "EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method5'"
205: + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
206: cs = conn
207: .prepareCall("call method5 (?, ?, ?, ?, ?, ?, ?, ?, ?)");
208: cs.setBigDecimal(1, new BigDecimal("33.333"));
209: cs.registerOutParameter(2, java.sql.Types.DECIMAL);
210: cs.setBigDecimal(3, new BigDecimal("-999.999999"));
211: cs.registerOutParameter(4, java.sql.Types.DECIMAL);
212: cs.registerOutParameter(5, java.sql.Types.DECIMAL);
213: cs.registerOutParameter(6, java.sql.Types.DECIMAL);
214: cs.registerOutParameter(7, java.sql.Types.DECIMAL);
215: cs.registerOutParameter(8, java.sql.Types.DECIMAL);
216: cs.registerOutParameter(9, java.sql.Types.DECIMAL);
217: cs.execute();
218: System.out.println("method 5 return decimal: "
219: + cs.getBigDecimal(2));
220: System.out.println("method 5 return decimal: "
221: + cs.getBigDecimal(4));
222: System.out.println("method 5 return decimal: "
223: + cs.getBigDecimal(5));
224: System.out.println("method 5 return decimal: "
225: + cs.getBigDecimal(6));
226: System.out.println("method 5 return decimal: "
227: + cs.getBigDecimal(7));
228: System.out.println("method 5 return decimal: "
229: + cs.getBigDecimal(8));
230: System.out.println("method 5 return decimal: "
231: + cs.getBigDecimal(9));
232: cs.close();
233: stmt.execute("DROP PROCEDURE method5");
234:
235: // INOUT param tests
236: stmt
237: .execute("CREATE PROCEDURE method6("
238: + "IN P1 INT, INOUT P2 INT, IN P3 SMALLINT, INOUT P4 SMALLINT, "
239: + "IN P5 BIGINT, INOUT P6 BIGINT, IN P7 REAL, INOUT P8 REAL, IN P9 DOUBLE, INOUT P10 DOUBLE, "
240: + "IN P11 TIME, INOUT P12 TIME "
241: + ") "
242: + "EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method6'"
243: + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
244: cs = conn
245: .prepareCall("call method6 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ?)");
246: cs.registerOutParameter(2, java.sql.Types.INTEGER);
247: cs.registerOutParameter(4, java.sql.Types.SMALLINT);
248: cs.registerOutParameter(6, java.sql.Types.BIGINT);
249: cs.registerOutParameter(8, java.sql.Types.REAL);
250: cs.registerOutParameter(10, java.sql.Types.DOUBLE);
251: cs.registerOutParameter(12, java.sql.Types.TIME);
252: cs.setInt(1, 6);
253: cs.setInt(2, 9);
254: cs.setShort(3, (short) 6);
255: cs.setShort(4, (short) 9);
256: cs.setLong(5, (long) 99999);
257: cs.setLong(6, (long) 88888888);
258: cs.setFloat(7, (float) 6.123453);
259: cs.setFloat(8, (float) 77777);
260: cs.setDouble(9, (double) 6.123453);
261: cs.setDouble(10, (double) 8888888888888.01234);
262: cs.setTime(11, Time.valueOf("11:06:03"));
263: cs.setTime(12, Time.valueOf("10:05:02"));
264:
265: cs.execute();
266: System.out.println("Integer: Sum of 6 and 9 is: "
267: + cs.getInt(2));
268: System.out.println("Short: Sum of 6 and 9 is: "
269: + cs.getShort(4));
270: System.out.println("Long: Sum of 99999 and 88888888 is: "
271: + cs.getLong(6));
272: System.out.println("Float: Sum of 6.123453 and 77777 is: "
273: + cs.getFloat(8));
274: System.out
275: .println("Double: Sum of 6.987654 and 8888888888888.01234 is: "
276: + cs.getDouble(10));
277: System.out
278: .println("Time: Old time of 10:05:02 changed to: "
279: + cs.getTime(12));
280: cs.close();
281: stmt.execute("DROP PROCEDURE method6");
282:
283: testBigDec(conn);
284:
285: testLongBinary(conn);
286: // Temporarily take out testbatch until jcc bug is fixed (5827)
287: // testBatch(conn);
288:
289: cleanUp(stmt);
290: stmt.close();
291: conn.close();
292: System.out.println("CallableStatement Test Ends");
293: } catch (Exception e) {
294: e.printStackTrace();
295: }
296: }
297:
298: static void testLongBinary(Connection conn) {
299: try {
300: String createTabSql = "create table Longvarbinary_Tab (lvbc Long varchar for bit data)";
301: PreparedStatement ps = conn.prepareStatement(createTabSql);
302: int updcount = ps.executeUpdate();
303: String insertTabSql = "insert into Longvarbinary_Tab values( X'010305')";
304: ps = conn.prepareStatement(insertTabSql);
305: updcount = ps.executeUpdate();
306:
307: int bytearrsize = 50;
308: byte[] bytearr = new byte[bytearrsize];
309: String sbyteval = null;
310:
311: // to get the bytearray value
312: for (int count = 0; count < bytearrsize; count++) {
313: sbyteval = Integer.toString(count % 255);
314: bytearr[count] = Byte.parseByte(sbyteval);
315: }
316:
317: System.out.println("get the CallableStatement object");
318:
319: String createproc = "create procedure Longvarbinary_In(P1 VARCHAR(10000) FOR BIT DATA) MODIFIES SQL DATA external name 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.Longvarbinary_Proc_In' language java parameter style java";
320: ps = conn.prepareStatement(createproc);
321: updcount = ps.executeUpdate();
322:
323: CallableStatement cstmt = conn
324: .prepareCall("{call Longvarbinary_In(?)}");
325: cstmt.setObject(1, bytearr, java.sql.Types.LONGVARBINARY);
326: System.out
327: .println("execute the procedure with LONGVARBINARY");
328: cstmt.executeUpdate();
329: cstmt.setObject(1, bytearr, java.sql.Types.BLOB);
330: System.out.println("execute the procedure with BLOB");
331: cstmt.executeUpdate();
332:
333: Statement stmt = conn.createStatement();
334: String Longvarbinary_Query = "Select lvbc from Longvarbinary_Tab";
335: System.out.println(Longvarbinary_Query);
336: ResultSet rs = stmt.executeQuery(Longvarbinary_Query);
337:
338: while (rs.next()) {
339: byte[] retvalue = (byte[]) rs.getObject(1);
340:
341: for (int i = 0; i < bytearrsize; i++) {
342: if (retvalue[i] != bytearr[i]) {
343: System.out
344: .println("Test Failed. setObject did not set the parameter value correctly");
345: }
346: }
347: }
348: rs.close();
349: ps.close();
350: stmt.close();
351: cstmt.close();
352: } catch (SQLException e) {
353: System.out.println(e.getMessage());
354: e.printStackTrace();
355: }
356:
357: System.out.println("done testing long varbinary");
358: }
359:
360: static void testBatch(Connection conn) {
361: try {
362: conn.setAutoCommit(true);
363: int i = 0;
364: int retValue[] = { 0, 0, 0 };
365: int updCountLength = 0;
366: Statement stmt = conn.createStatement();
367: PreparedStatement ps = null;
368: int updcount;
369:
370: try {
371: ps = conn.prepareStatement("drop table tab1");
372: updcount = ps.executeUpdate();
373: ps = conn.prepareStatement("drop table tab2");
374: updcount = ps.executeUpdate();
375: ps = conn
376: .prepareStatement("drop procedure UpdTable_Proc");
377: updcount = ps.executeUpdate();
378: } catch (SQLException e) {
379: }
380:
381: String createtable = "create table tab1 (tab1pk int, vcc1 varchar(32), primary key(tab1pk))";
382: System.out.println("doing: " + createtable);
383: stmt.execute(createtable);
384:
385: String inserttable = "insert into tab1 values(2, 'STRING_2')";
386: System.out.println("doing: " + inserttable);
387: stmt.addBatch(inserttable);
388: inserttable = "insert into tab1 values(3, 'STRING_3')";
389: System.out.println("doing: " + inserttable);
390: stmt.addBatch(inserttable);
391: inserttable = "insert into tab1 values(5, 'STRING_5')";
392: System.out.println("doing: " + inserttable);
393: stmt.addBatch(inserttable);
394: inserttable = "select * from tab1";
395: System.out.println("adding: " + inserttable);
396: stmt.addBatch(inserttable);
397:
398: int[] updateCount = null;
399: try {
400: updateCount = stmt.executeBatch();
401: } catch (SQLException se) {
402: do {
403: System.out.println("Exception chain: "
404: + se.getMessage());
405: se = se.getNextException();
406: } while (se != null);
407: }
408:
409: ResultSet rs = stmt.executeQuery("select * from tab1");
410: while (rs.next()) {
411: System.out.println(" id: " + rs.getInt(1) + " desc: "
412: + rs.getString(2));
413: }
414:
415: createtable = "create table tab2 (tab2pk int, vcc2 varchar(32), fc float, tab1pk int, primary key(tab2pk), foreign key(tab1pk) references tab1)";
416: System.out.println("doing: " + createtable);
417: stmt.execute(createtable);
418:
419: inserttable = "insert into tab2 values(1, 'STRING-1', 1.0 , 5)";
420: System.out.println("doing: " + inserttable);
421: stmt.execute(inserttable);
422: inserttable = "insert into tab2 values(2, 'STRING-2', 2.0 , 2)";
423: System.out.println("doing: " + inserttable);
424: stmt.execute(inserttable);
425: inserttable = "insert into tab2 values(3, 'STRING-3', 3.0 , 5)";
426: System.out.println("doing: " + inserttable);
427: stmt.execute(inserttable);
428: inserttable = "insert into tab2 values(9, 'STRING-9', 9.0 , 3)";
429: System.out.println("doing: " + inserttable);
430: stmt.execute(inserttable);
431:
432: System.out.println("setup done");
433:
434: String createproc = "create procedure UpdTable_Proc(P1 INT) MODIFIES SQL DATA external name 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.UpdTable_Proc' langauge java parameter style java";
435: System.out.println("doing: " + createproc);
436: stmt.execute(createproc);
437:
438: System.out
439: .println("call the proc/get the callable statement");
440: CallableStatement cstmt = conn
441: .prepareCall("{call UpdTable_Proc(?)}");
442: System.out.println("set first int");
443: cstmt.setInt(1, 2);
444: System.out.println("add first to batch");
445: cstmt.addBatch();
446:
447: System.out.println("set second int");
448: cstmt.setInt(1, 3);
449: System.out.println("add second to batch");
450: cstmt.addBatch();
451:
452: System.out.println("set third int");
453: cstmt.setInt(1, 4);
454: System.out.println("add third to batch");
455: cstmt.addBatch();
456:
457: try {
458: System.out.println("execute the executeBatch method");
459: updateCount = cstmt.executeBatch();
460: updCountLength = updateCount.length;
461: } catch (SQLException e) {
462: System.out.println("EXPECTED Exception: ");
463: System.out.println(e.getMessage());
464: }
465:
466: rs = stmt.executeQuery("select * from tab2");
467: while (rs.next()) {
468: System.out.println(" type id: " + rs.getInt(4)
469: + " new float column value: " + rs.getInt(3));
470: }
471:
472: System.out.println("prepare the proc");
473: String prepString = "update tab2 set tab2pk=?, vcc2=? where vcc2=?";
474: PreparedStatement pstmt = conn.prepareStatement(prepString);
475: int batchUpdates[] = { 0, 0, 0 };
476: int buCountlen = 0;
477:
478: try {
479:
480: System.out.println("set first values");
481: pstmt.setInt(1, 1);
482: pstmt.setString(2, "Continue-1");
483: pstmt.setString(3, "STRING-1");
484: System.out.println("add first to batch");
485: pstmt.addBatch();
486:
487: System.out
488: .println("set second values - illegal update - forces unique constr violation");
489: pstmt.setInt(1, 1);
490: pstmt.setString(2, "Invalid");
491: pstmt.setString(3, "STRING-3");
492: System.out.println("add second to batch");
493: pstmt.addBatch();
494:
495: System.out
496: .println("set third values; legal update again");
497: pstmt.setInt(1, 2);
498: pstmt.setString(2, "Continue-2");
499: pstmt.setString(3, "STRING-2");
500: System.out.println("add third to batch");
501: pstmt.addBatch();
502:
503: System.out.println("execute the executeBatch method");
504: System.out.println("expecting batchupdateexception");
505: updateCount = pstmt.executeBatch();
506:
507: } catch (BatchUpdateException b) {
508: System.out.println("b: " + b.getMessage());
509: System.out
510: .println("Caught expected BatchUpdateException");
511: batchUpdates = b.getUpdateCounts();
512: buCountlen = batchUpdates.length;
513: System.out.println("buclen: " + buCountlen);
514: } catch (SQLException sqle) {
515: System.out.println("Call to continueUpdate failed!"
516: + sqle);
517: } catch (Exception e) {
518: System.out
519: .println("Call to continueUpdate failed!" + e);
520: }
521:
522: if (buCountlen == 1) {
523: System.out
524: .println("Driver does not support continued updates - OK");
525: for (i = 0; i < buCountlen; i++)
526: System.out.println("=== update count: "
527: + batchUpdates[i]);
528: return;
529: } else if (buCountlen == 3) {
530: System.out
531: .println("Driver supports continued updates.");
532: for (i = 0; i < buCountlen; i++)
533: System.out.println("=== update count: "
534: + batchUpdates[i]);
535: // Check to see if the third row from the batch was added
536: try {
537: String query = "Select count(*) from tab2 where vcc2 in ('Continue-2')";
538: System.out.println("Query is: " + query);
539: rs = stmt.executeQuery(query);
540: System.out.println("executed, now next...");
541: rs.next();
542: System.out.println("next, now getInt...");
543: int count = rs.getInt(1);
544: rs.close();
545: stmt.close();
546: System.out.println("Count val is: " + count);
547:
548: // Make sure that we have the correct error code for
549: // the failed update.
550:
551: if (!(batchUpdates[1] == -3 && count == 1)) {
552: System.out
553: .println("Driver did not insert after error.");
554: }
555: System.out.println("now after errorcode check");
556:
557: } catch (SQLException sqle) {
558: System.out.println("Call to continueUpdate failed!"
559: + sqle);
560: sqle.printStackTrace();
561:
562: }
563: }
564:
565: System.out.println("Done testing executeBatch.");
566:
567: rs.close();
568: cstmt.close();
569: stmt.close();
570:
571: } catch (SQLException e) {
572: System.out.println(e.getMessage());
573: e.printStackTrace();
574: }
575: }
576:
577: public static void method1(int p1, int p2, int[] p3) {
578: p3[0] = p1 + p2;
579: }
580:
581: public static int method2(int p1) {
582: return (p1 * p1) + p1;
583: }
584:
585: public static void method3() {
586: System.out.println("I'm doing something here...");
587: }
588:
589: public static int method4() {
590: return 55;
591: }
592:
593: public static void method4(short s, int i, long l, float f,
594: double d, BigDecimal bd, Date dt, Time t, Timestamp ts,
595: byte[] ba, short[] sr, int[] ir, long[] lr, float[] fr,
596: double[] dr, BigDecimal[] bdr, Date[] dtr, Time[] tr,
597: Timestamp[] tsr, byte[][] bar) {
598: sr[0] = s;
599: ir[0] = i;
600: lr[0] = l;
601: fr[0] = f;
602: dr[0] = d;
603: bdr[0] = bd;
604: dtr[0] = dt;
605: tr[0] = t;
606: if (ts.equals(Timestamp
607: .valueOf("2002-05-12 10:05:02.000000000"))) {
608: System.out.println("got the right Timestamp");
609: tsr[0] = ts;
610: } else {
611: System.out.println("got the wrong Timestamp");
612: tsr[0] = null;
613: }
614: bar[0] = ba;
615: }
616:
617: public static void method5(BigDecimal bd1, BigDecimal bdr1[],
618: BigDecimal bd2, BigDecimal bdr2[], BigDecimal bdr3[],
619: BigDecimal bdr4[], BigDecimal bdr5[], BigDecimal bdr6[],
620: BigDecimal bdr7[]) {
621: bdr1[0] = bd1;
622: bdr2[0] = bd1.multiply(bd2);
623: bdr3[0] = bd1.add(bd2);
624: bdr4[0] = new BigDecimal(".00000");
625: bdr5[0] = new BigDecimal("-.00000");
626: bdr6[0] = new BigDecimal("99999999.");
627: bdr7[0] = new BigDecimal("-99999999.");
628: }
629:
630: // Test for INOUT params.
631: public static void method6(int p1, int p2[], short s1, short s2[],
632: long l1, long l2[], float f1, float f2[], double d1,
633: double d2[], Time t1, Time t2[]) {
634: p2[0] = p1 + p2[0];
635: s2[0] = (short) (s1 + s2[0]);
636: l2[0] = l1 + l2[0];
637: f2[0] = f1 + f2[0];
638: d2[0] = d1 + d2[0];
639: t2[0] = t1;
640: }
641:
642: // Test for IN parameters with Longvarbinary column
643: public static void Longvarbinary_Proc_In(byte[] in_param)
644: throws SQLException {
645:
646: Connection conn = DriverManager
647: .getConnection("jdbc:default:connection");
648: PreparedStatement ps = conn
649: .prepareStatement("update Longvarbinary_Tab set lvbc=?");
650:
651: ps.setBytes(1, in_param);
652: ps.executeUpdate();
653:
654: ps.close();
655: ps = null;
656: conn.close();
657: conn = null;
658: }
659:
660: // test update of table in batch
661: public static void UpdTable_Proc(BigDecimal type_param)
662: throws SQLException {
663: Connection conn = DriverManager
664: .getConnection("jdbc:default:connection");
665: PreparedStatement ps = conn
666: .prepareStatement("update t2 set fc=fc*20 where tab1pk=?");
667:
668: ps.setBigDecimal(1, type_param);
669: ps.executeUpdate();
670:
671: ps.close();
672: ps = null;
673: conn.close();
674: conn = null;
675: }
676:
677: // test accessing minumum and maximum and null value for numeric columns with out params
678: public static void Numeric_Proc(BigDecimal[] param1,
679: BigDecimal[] param2, BigDecimal[] param3)
680: throws SQLException {
681: Connection conn = DriverManager
682: .getConnection("jdbc:default:connection");
683: Statement stmt = conn.createStatement();
684: ResultSet rs = stmt
685: .executeQuery("select maxcol, mincol, nulcol from Num_Tab");
686:
687: if (rs.next()) {
688: param1[0] = rs.getBigDecimal(1);
689: param2[0] = rs.getBigDecimal(2);
690: param3[0] = rs.getBigDecimal(3);
691: } else {
692: throw new SQLException("Data not found");
693: }
694:
695: rs.close();
696: rs = null;
697: stmt.close();
698: stmt = null;
699: conn.close();
700: conn = null;
701: }
702:
703: // Beetle 4933.
704: static void testBigDec(Connection conn) throws Exception {
705: PreparedStatement ps = null;
706: int updcount;
707:
708: try {
709: try {
710: ps = conn.prepareStatement("drop table Num_Tab");
711: updcount = ps.executeUpdate();
712: } catch (SQLException e) {
713: }
714: int tabSize = 10;
715: String createTabSql = "create table Num_Tab (maxcol NUMERIC(31,15), mincol NUMERIC(15,15), nulcol NUMERIC)";
716:
717: ps = conn.prepareStatement(createTabSql);
718: updcount = ps.executeUpdate();
719: String insertTabSql = "insert into Num_Tab values(999999999999999,0.000000000000001, null)";
720: ps = conn.prepareStatement(insertTabSql);
721: updcount = ps.executeUpdate();
722: try {
723: String alias = "create procedure Numeric_Proc(OUT P1 DECIMAL(31,15), OUT P2 DECIMAL(31,15), OUT P3 DECIMAL(31,15)) READS SQL DATA external name 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.Numeric_Proc' language java parameter style java";
724: ps = conn.prepareStatement(alias);
725: updcount = ps.executeUpdate();
726: } catch (SQLException se) {
727: }
728:
729: CallableStatement cstmt = conn
730: .prepareCall("{call Numeric_Proc(?,?,?)}");
731: cstmt.registerOutParameter(1, java.sql.Types.NUMERIC, 15);
732: cstmt.registerOutParameter(2, java.sql.Types.NUMERIC, 15);
733: cstmt.registerOutParameter(3, java.sql.Types.NUMERIC, 15);
734: // ParameterMetaData pmd = cstmt.getParameterMetaData();
735: //System.out.println("precision: " + pmd.getPrecision(1) +
736: // "scale: " + pmd.getScale(1));
737: //execute the procedure
738: cstmt.execute();
739:
740: BigDecimal retVal = cstmt.getBigDecimal(1);
741: BigDecimal retVal2 = cstmt.getBigDecimal(2);
742: BigDecimal retVal3 = cstmt.getBigDecimal(3);
743:
744: System.out.println("cstmt.getBigDecimal(1): " + retVal);
745: System.out.println("cstmt.getBigDecimal(2): " + retVal2);
746: System.out.println("cstmt.getBigDecimal(3): " + retVal3);
747: cstmt.close();
748: ps.close();
749:
750: } catch (SQLException e) {
751: System.out.println(e.getMessage());
752: e.printStackTrace();
753: }
754:
755: }
756:
757: // test update of table in batch
758: public static void cleanUp(Statement stmt) throws SQLException {
759: String[] testObjects = { "table longvarbinary_tab",
760: "table num_tab", "procedure method1",
761: "function method2", "function method4",
762: "procedure longvarbinary_in" };
763: TestUtil.cleanUpTest(stmt, testObjects);
764: }
765:
766: }
|