KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > derbynet > callable


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.derbynet.callable
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to You under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */

21
22 package org.apache.derbyTesting.functionTests.tests.derbynet;
23
24 import java.math.BigDecimal JavaDoc;
25 import java.sql.Date JavaDoc;
26 import java.sql.Time JavaDoc;
27 import java.sql.Timestamp JavaDoc;
28 import java.sql.CallableStatement JavaDoc;
29 import java.sql.Statement JavaDoc;
30 import java.sql.PreparedStatement JavaDoc;
31 import java.sql.ResultSet JavaDoc;
32 import java.sql.SQLException JavaDoc;
33 import java.sql.BatchUpdateException JavaDoc;
34 import java.sql.DriverManager JavaDoc;
35 import java.sql.Connection JavaDoc;
36 import org.apache.derby.tools.ij;
37 import org.apache.derbyTesting.functionTests.util.TestUtil;
38 /**
39     This test tests the JDBC CallableStatement.
40 */

41
42 public class callable
43 {
44
45     public static void main (String JavaDoc args[])
46     {
47         try
48         {
49             System.out.println("CallableStatement Test Starts");
50
51             ij.getPropertyArg(args);
52
53             // This also tests quoted pathname in database name portion of URL, beetle 4781.
54
String JavaDoc protocol = System.getProperty("ij.protocol");
55             String JavaDoc hostName = TestUtil.getHostName();
56             System.setProperty("ij.database", protocol + "//" + hostName + "/\"" + System.getProperty("derby.system.home") + java.io.File.separator + "wombat;create=true\"");
57             ij.getPropertyArg(args);
58             Connection JavaDoc conn = ij.startJBMS();
59             if (conn == null)
60             {
61                 System.out.println("conn didn't work");
62                 return;
63             }
64             Statement JavaDoc stmt = conn.createStatement();
65
66             // 2 input, 1 output
67
stmt.execute("CREATE PROCEDURE method1(IN P1 INT, IN P2 INT, OUT P3 INT) " +
68                     "EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method1'" +
69                     " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
70             CallableStatement JavaDoc cs = conn.prepareCall("call method1 (?, ?, ?)");
71             cs.setInt(1, 6);
72             cs.setInt(2, 9);
73             cs.registerOutParameter (3, java.sql.Types.INTEGER);
74             cs.execute();
75             int sum = cs.getInt(3);
76             System.out.println("Sum of 6 and 9 is: " + sum);
77             cs.close();
78             stmt.execute("DROP PROCEDURE method1");
79
80             // method returns calue, plus 1 input
81
stmt.execute("CREATE FUNCTION method2(P1 INT) RETURNS INT" +
82                     " EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method2'" +
83                     " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
84             cs = conn.prepareCall("? = call method2 (?)");
85             cs.registerOutParameter (1, java.sql.Types.INTEGER);
86             cs.setInt(2, 6);
87             cs.execute();
88             int ret = cs.getInt(1);
89             System.out.println("return value: Square of 6 then plus 6 is: " + ret);
90             cs.close();
91             // stmt.execute("DROP FUNCTION method2");
92

93             // no parameter
94
stmt.execute("CREATE PROCEDURE method3() " +
95                     "EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method3'" +
96                     " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
97             cs = conn.prepareCall("call method3 ()");
98             cs.execute();
99             cs.close();
100             stmt.execute("DROP PROCEDURE method3");
101
102             // only 1 return parameter
103
stmt.execute("CREATE FUNCTION method4() RETURNS INT" +
104                     " EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method4'" +
105                     " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
106             cs = conn.prepareCall("? = call method4()");
107             cs.registerOutParameter (1, java.sql.Types.INTEGER);
108             cs.execute();
109             System.out.println("return value is: " + cs.getInt(1));
110             cs.close();
111             // stmt.execute("DROP FUNCTION method4");
112

113             // DERBY-1184: User-defined output parameter not supported:
114
if (!TestUtil.isJCCFramework())
115             {
116                 cs = conn.prepareCall("? = call method4()");
117                 try
118                 {
119                     cs.registerOutParameter (1, java.sql.Types.INTEGER,
120                             "user-def");
121                     System.out.println("DERBY-1184 FAIL: Expected exception");
122                 }
123                 catch (SQLException JavaDoc expectedException)
124                 {
125                     if (! "0A000".equals(expectedException.getSQLState()))
126                     {
127                         System.out.println("DERBY-1184: Caught UNexpected: " +
128                             expectedException.getMessage());
129                         System.out.println("DERBY-1184: SQLState: " +
130                             expectedException.getSQLState() + ", errorCode: " +
131                             expectedException.getErrorCode());
132                     }
133                 }
134                 cs.close();
135             }
136
137             // different parameter types, also method overload
138
stmt.execute("CREATE PROCEDURE method4P(" +
139                     "IN P1 SMALLINT, IN P2 INT, IN P3 BIGINT, IN P4 REAL, " +
140                     "IN P5 DOUBLE, IN P6 DECIMAL(6,3), IN P7 DATE, IN P8 TIME, IN P9 TIMESTAMP, IN P10 VARCHAR(20) FOR BIT DATA, " +
141                     "OUT O1 SMALLINT, OUT O2 INT, OUT O3 BIGINT, OUT O4 REAL, " +
142                     "OUT O5 DOUBLE, OUT O6 DECIMAL(6,3), OUT O7 DATE, OUT O8 TIME, OUT O9 TIMESTAMP, OUT O10 VARCHAR(20) FOR BIT DATA" +
143                     ") " +
144                     "EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method4'" +
145                     " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
146             cs = conn.prepareCall("call method4P(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
147             cs.setShort(1, (short) 3);
148             cs.setInt(2, 4);
149             cs.setLong(3, 5);
150             cs.setFloat(4, (float) 6.0);
151             cs.setDouble(5, 7.0);
152             cs.setBigDecimal(6, new BigDecimal JavaDoc("88.88"));
153             cs.setDate(7, Date.valueOf("2002-05-12"));
154             cs.setTime(8, Time.valueOf("10:05:02"));
155             cs.setTimestamp(9, Timestamp.valueOf("2002-05-12 10:05:02.000000000"));
156             byte[] ba = new byte[2];
157             ba[0] = 1;
158             ba[1] = 2;
159             cs.setBytes(10, ba);
160             int n = 10;
161             cs.registerOutParameter (n+1, java.sql.Types.SMALLINT);
162             cs.registerOutParameter (n+2, java.sql.Types.INTEGER);
163             cs.registerOutParameter (n+3, java.sql.Types.BIGINT);
164             cs.registerOutParameter (n+4, java.sql.Types.REAL);
165             cs.registerOutParameter (n+5, java.sql.Types.DOUBLE);
166             cs.registerOutParameter (n+6, java.sql.Types.DECIMAL);
167             cs.registerOutParameter (n+7, java.sql.Types.DATE);
168             cs.registerOutParameter (n+8, java.sql.Types.TIME);
169             cs.registerOutParameter (n+9, java.sql.Types.TIMESTAMP);
170             cs.registerOutParameter (n+10, java.sql.Types.VARBINARY);
171             cs.execute();
172             System.out.println("return short: " + cs.getShort(n+1));
173             System.out.println("return int: " + cs.getInt(n+2));
174             System.out.println("return long: " + cs.getLong(n+3));
175             System.out.println("return float: " + cs.getFloat(n+4));
176             System.out.println("return double: " + cs.getDouble(n+5));
177             System.out.println("return decimal: " + cs.getBigDecimal(n+6));
178             System.out.println("return date: " + cs.getDate(n+7));
179             System.out.println("return time: " + cs.getTime(n+8));
180             System.out.println("return time stamp: " + cs.getTimestamp(n+9));
181             ba = cs.getBytes(n+10);
182             for (int i = 0; i < ba.length; i++)
183                 System.out.println("return byte["+i+"]: " + ba[i]);
184             stmt.execute("DROP PROCEDURE method4P");
185
186             // some tests on BigDecimal
187
stmt.execute("CREATE PROCEDURE method5(" +
188                     "IN P1 DECIMAL(14,4), OUT P2 DECIMAL(14,4), IN P3 DECIMAL(14,4), OUT P4 DECIMAL(14,4), " +
189                     "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) " +
190                     ") " +
191                     "EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method5'" +
192                     " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
193             cs = conn.prepareCall("call method5 (?, ?, ?, ?, ?, ?, ?, ?, ?)");
194             cs.setBigDecimal(1, new BigDecimal JavaDoc("33.333"));
195             cs.registerOutParameter (2, java.sql.Types.DECIMAL);
196             cs.setBigDecimal(3, new BigDecimal JavaDoc("-999.999999"));
197             cs.registerOutParameter (4, java.sql.Types.DECIMAL);
198             cs.registerOutParameter (5, java.sql.Types.DECIMAL);
199             cs.registerOutParameter (6, java.sql.Types.DECIMAL);
200             cs.registerOutParameter (7, java.sql.Types.DECIMAL);
201             cs.registerOutParameter (8, java.sql.Types.DECIMAL);
202             cs.registerOutParameter (9, java.sql.Types.DECIMAL);
203             cs.execute();
204             System.out.println("method 5 return decimal: " + cs.getBigDecimal(2));
205             System.out.println("method 5 return decimal: " + cs.getBigDecimal(4));
206             System.out.println("method 5 return decimal: " + cs.getBigDecimal(5));
207             System.out.println("method 5 return decimal: " + cs.getBigDecimal(6));
208             System.out.println("method 5 return decimal: " + cs.getBigDecimal(7));
209             System.out.println("method 5 return decimal: " + cs.getBigDecimal(8));
210             System.out.println("method 5 return decimal: " + cs.getBigDecimal(9));
211             cs.close();
212             stmt.execute("DROP PROCEDURE method5");
213
214             // INOUT param tests
215
stmt.execute("CREATE PROCEDURE method6(" +
216                     "IN P1 INT, INOUT P2 INT, IN P3 SMALLINT, INOUT P4 SMALLINT, " +
217                     "IN P5 BIGINT, INOUT P6 BIGINT, IN P7 REAL, INOUT P8 REAL, IN P9 DOUBLE, INOUT P10 DOUBLE, " +
218                     "IN P11 TIME, INOUT P12 TIME " +
219                     ") " +
220                     "EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method6'" +
221                     " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
222             cs = conn.prepareCall("call method6 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ?)");
223             cs.registerOutParameter (2, java.sql.Types.INTEGER);
224             cs.registerOutParameter (4, java.sql.Types.SMALLINT);
225             cs.registerOutParameter (6, java.sql.Types.BIGINT);
226             cs.registerOutParameter (8, java.sql.Types.REAL);
227             cs.registerOutParameter (10, java.sql.Types.DOUBLE);
228             cs.registerOutParameter (12, java.sql.Types.TIME);
229             cs.setInt(1, 6);
230             cs.setInt(2, 9);
231             cs.setShort(3, (short)6);
232             cs.setShort(4, (short)9);
233             cs.setLong(5, (long)99999);
234             cs.setLong(6, (long)88888888);
235             cs.setFloat(7, (float)6.123453);
236             cs.setFloat(8, (float)77777);
237             cs.setDouble(9, (double)6.123453);
238             cs.setDouble(10, (double)8888888888888.01234);
239             cs.setTime(11, Time.valueOf("11:06:03"));
240             cs.setTime(12, Time.valueOf("10:05:02"));
241
242             cs.execute();
243             System.out.println("Integer: Sum of 6 and 9 is: " + cs.getInt(2));
244             System.out.println("Short: Sum of 6 and 9 is: " + cs.getShort(4));
245             System.out.println("Long: Sum of 99999 and 88888888 is: " + cs.getLong(6));
246             System.out.println("Float: Sum of 6.123453 and 77777 is: " + cs.getFloat(8));
247             System.out.println("Double: Sum of 6.987654 and 8888888888888.01234 is: " + cs.getDouble(10));
248             System.out.println("Time: Old time of 10:05:02 changed to: " + cs.getTime(12));
249             cs.close();
250             stmt.execute("DROP PROCEDURE method6");
251
252             testBigDec(conn);
253
254             testLongBinary(conn);
255             // Temporarily take out testbatch until jcc bug is fixed (5827)
256
// testBatch(conn);
257

258                         cleanUp(stmt);
259             stmt.close();
260             conn.close();
261             System.out.println("CallableStatement Test Ends");
262         }
263         catch (Exception JavaDoc e)
264         {
265             e.printStackTrace();
266         }
267     }
268
269     static void testLongBinary(Connection JavaDoc conn)
270     {
271       try {
272         String JavaDoc createTabSql = "create table Longvarbinary_Tab (lvbc Long varchar for bit data)";
273         PreparedStatement JavaDoc ps = conn.prepareStatement(createTabSql);
274         int updcount = ps.executeUpdate();
275         String JavaDoc insertTabSql = "insert into Longvarbinary_Tab values( X'010305')";
276         ps = conn.prepareStatement(insertTabSql);
277         updcount = ps.executeUpdate();
278
279         int bytearrsize = 50;
280         byte[] bytearr=new byte[bytearrsize];
281         String JavaDoc sbyteval=null;
282
283         // to get the bytearray value
284
for (int count=0;count<bytearrsize;count++)
285         {
286             sbyteval=Integer.toString(count%255);
287             bytearr[count]=Byte.parseByte(sbyteval);
288         }
289
290         System.out.println("get the CallableStatement object");
291
292         String JavaDoc 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";
293         ps = conn.prepareStatement(createproc);
294         updcount = ps.executeUpdate();
295
296         CallableStatement JavaDoc cstmt = conn.prepareCall("{call Longvarbinary_In(?)}");
297         cstmt.setObject(1,bytearr,java.sql.Types.LONGVARBINARY);
298         System.out.println("execute the procedure with LONGVARBINARY");
299         cstmt.executeUpdate();
300         cstmt.setObject(1,bytearr,java.sql.Types.BLOB);
301         System.out.println("execute the procedure with BLOB");
302         cstmt.executeUpdate();
303
304         Statement JavaDoc stmt = conn.createStatement();
305         String JavaDoc Longvarbinary_Query="Select lvbc from Longvarbinary_Tab";
306         System.out.println(Longvarbinary_Query);
307         ResultSet JavaDoc rs=stmt.executeQuery(Longvarbinary_Query);
308
309         while (rs.next())
310         {
311             byte[] retvalue = (byte[]) rs.getObject(1);
312
313             for(int i=0;i<bytearrsize;i++)
314             {
315                 if (retvalue[i]!=bytearr[i])
316                 {
317                     System.out.println("Test Failed. setObject did not set the parameter value correctly");
318                 }
319             }
320         }
321         rs.close();
322         ps.close();
323         stmt.close();
324         cstmt.close();
325       }catch (SQLException JavaDoc e) {
326           System.out.println(e.getMessage());
327           e.printStackTrace();
328       }
329
330       System.out.println("done testing long varbinary");
331     }
332
333     static void testBatch(Connection JavaDoc conn)
334     {
335       try {
336         conn.setAutoCommit(true);
337         int i=0;
338         int retValue[]={0,0,0};
339         int updCountLength=0;
340         Statement JavaDoc stmt = conn.createStatement();
341         PreparedStatement JavaDoc ps = null;
342         int updcount;
343         
344         try {
345             ps=conn.prepareStatement("drop table tab1");
346             updcount=ps.executeUpdate();
347             ps=conn.prepareStatement("drop table tab2");
348             updcount=ps.executeUpdate();
349             ps=conn.prepareStatement("drop procedure UpdTable_Proc");
350             updcount=ps.executeUpdate();
351         }
352         catch (SQLException JavaDoc e) {}
353
354         String JavaDoc createtable = "create table tab1 (tab1pk int, vcc1 varchar(32), primary key(tab1pk))";
355         System.out.println("doing: " + createtable);
356         stmt.execute(createtable);
357
358         String JavaDoc inserttable = "insert into tab1 values(2, 'STRING_2')";
359         System.out.println("doing: " + inserttable);
360         stmt.addBatch(inserttable);
361         inserttable = "insert into tab1 values(3, 'STRING_3')";
362         System.out.println("doing: " + inserttable);
363         stmt.addBatch(inserttable);
364         inserttable = "insert into tab1 values(5, 'STRING_5')";
365         System.out.println("doing: " + inserttable);
366         stmt.addBatch(inserttable);
367         inserttable = "select * from tab1";
368         System.out.println("adding: " + inserttable);
369         stmt.addBatch(inserttable);
370
371         int[] updateCount=null;
372         try {
373             updateCount = stmt.executeBatch();
374         } catch(SQLException JavaDoc se) {
375             do {
376                 System.out.println("Exception chain: "+se.getMessage());
377                 se = se.getNextException();
378             } while (se != null);
379         }
380
381         ResultSet JavaDoc rs = stmt.executeQuery("select * from tab1");
382         while (rs.next())
383         {
384             System.out.println(" id: "+rs.getInt(1)+" desc: "+rs.getString(2));
385         }
386     
387         createtable = "create table tab2 (tab2pk int, vcc2 varchar(32), fc float, tab1pk int, primary key(tab2pk), foreign key(tab1pk) references tab1)" ;
388         System.out.println("doing: " + createtable);
389         stmt.execute(createtable);
390         
391         inserttable="insert into tab2 values(1, 'STRING-1', 1.0 , 5)";
392         System.out.println("doing: " + inserttable);
393         stmt.execute(inserttable);
394         inserttable="insert into tab2 values(2, 'STRING-2', 2.0 , 2)";
395         System.out.println("doing: " + inserttable);
396         stmt.execute(inserttable);
397         inserttable="insert into tab2 values(3, 'STRING-3', 3.0 , 5)";
398         System.out.println("doing: " + inserttable);
399         stmt.execute(inserttable);
400         inserttable="insert into tab2 values(9, 'STRING-9', 9.0 , 3)";
401         System.out.println("doing: " + inserttable);
402         stmt.execute(inserttable);
403
404         System.out.println("setup done");
405
406         String JavaDoc 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";
407         System.out.println("doing: " + createproc);
408         stmt.execute(createproc);
409         
410         System.out.println("call the proc/get the callable statement");
411         CallableStatement JavaDoc cstmt= conn.prepareCall("{call UpdTable_Proc(?)}");
412         System.out.println("set first int");
413         cstmt.setInt(1,2);
414         System.out.println("add first to batch");
415         cstmt.addBatch();
416
417         System.out.println("set second int");
418         cstmt.setInt(1,3);
419         System.out.println("add second to batch");
420         cstmt.addBatch();
421
422         System.out.println("set third int");
423         cstmt.setInt(1,4);
424         System.out.println("add third to batch");
425         cstmt.addBatch();
426
427         try {
428             System.out.println("execute the executeBatch method");
429             updateCount = cstmt.executeBatch();
430             updCountLength = updateCount.length;
431         } catch (SQLException JavaDoc e) {
432             System.out.println("EXPECTED Exception: ");
433             System.out.println(e.getMessage());
434         }
435
436         rs = stmt.executeQuery("select * from tab2");
437         while (rs.next())
438         {
439             System.out.println(" type id: "+rs.getInt(4)+" new float column value: "+rs.getInt(3));
440         }
441
442         System.out.println("prepare the proc");
443         String JavaDoc prepString= "update tab2 set tab2pk=?, vcc2=? where vcc2=?";
444         PreparedStatement JavaDoc pstmt = conn.prepareStatement(prepString);
445         int batchUpdates[]={0,0,0};
446         int buCountlen=0;
447
448         try {
449
450             System.out.println("set first values");
451             pstmt.setInt(1,1);
452             pstmt.setString(2, "Continue-1");
453             pstmt.setString(3, "STRING-1");
454             System.out.println("add first to batch");
455             pstmt.addBatch();
456     
457             System.out.println("set second values - illegal update - forces unique constr violation");
458             pstmt.setInt(1,1);
459             pstmt.setString(2,"Invalid");
460             pstmt.setString(3,"STRING-3");
461             System.out.println("add second to batch");
462             pstmt.addBatch();
463
464             System.out.println("set third values; legal update again");
465             pstmt.setInt(1,2);
466             pstmt.setString(2,"Continue-2");
467             pstmt.setString(3,"STRING-2");
468             System.out.println("add third to batch");
469             pstmt.addBatch();
470
471             System.out.println("execute the executeBatch method");
472             System.out.println("expecting batchupdateexception");
473             updateCount = pstmt.executeBatch();
474
475         }
476         catch(BatchUpdateException JavaDoc b)
477         {
478             System.out.println("b: " + b.getMessage());
479             System.out.println("Caught expected BatchUpdateException");
480             batchUpdates = b.getUpdateCounts();
481             buCountlen = batchUpdates.length;
482             System.out.println("buclen: " + buCountlen);
483         }
484         catch(SQLException JavaDoc sqle)
485         {
486             System.out.println("Call to continueUpdate failed!" + sqle);
487         }
488         catch ( Exception JavaDoc e )
489         {
490             System.out.println("Call to continueUpdate failed!" + e);
491         }
492     
493         if (buCountlen == 1)
494         {
495             System.out.println("Driver does not support continued updates - OK");
496             for (i = 0; i < buCountlen; i++)
497                 System.out.println("=== update count: "+batchUpdates[i]);
498             return;
499         }
500         else if (buCountlen == 3)
501         {
502             System.out.println("Driver supports continued updates.");
503             for (i = 0; i < buCountlen; i++)
504                 System.out.println("=== update count: "+batchUpdates[i]);
505             // Check to see if the third row from the batch was added
506
try
507             {
508                 String JavaDoc query ="Select count(*) from tab2 where vcc2 in ('Continue-2')";
509                 System.out.println("Query is: " + query);
510                 rs=stmt.executeQuery(query);
511                 System.out.println("executed, now next...");
512                 rs.next();
513                 System.out.println("next, now getInt...");
514                 int count = rs.getInt(1);
515                 rs.close();
516                 stmt.close();
517                 System.out.println("Count val is: " + count);
518
519                 // Make sure that we have the correct error code for
520
// the failed update.
521

522                 if (! (batchUpdates[1] == -3 && count == 1) )
523                 {
524                     System.out.println("Driver did not insert after error.");
525                 }
526                 System.out.println("now after errorcode check");
527                     
528             }
529             catch (SQLException JavaDoc sqle)
530             {
531                 System.out.println("Call to continueUpdate failed!" + sqle);
532                 sqle.printStackTrace();
533                     
534             }
535         }
536
537         System.out.println("Done testing executeBatch.");
538
539         rs.close();
540         cstmt.close();
541         stmt.close();
542
543       }catch (SQLException JavaDoc e) {
544           System.out.println(e.getMessage());
545           e.printStackTrace();
546       }
547     }
548
549     public static void method1 (int p1, int p2, int[] p3)
550     {
551         p3[0] = p1 + p2;
552     }
553
554     public static int method2 (int p1)
555     {
556         return (p1 * p1) + p1;
557     }
558
559     public static void method3()
560     {
561         System.out.println("I'm doing something here...");
562     }
563
564     public static int method4()
565     {
566         return 55;
567     }
568
569     public static void method4(short s, int i, long l, float f,
570                             double d, BigDecimal JavaDoc bd, Date JavaDoc dt, Time JavaDoc t, Timestamp JavaDoc ts, byte[] ba,
571                             short[] sr, int[] ir,
572                             long[] lr, float[] fr, double[] dr, BigDecimal JavaDoc[] bdr,
573                             Date JavaDoc[] dtr, Time JavaDoc[] tr, Timestamp JavaDoc[] tsr, byte[][] bar)
574     {
575         sr[0] = s;
576         ir[0] = i;
577         lr[0] = l;
578         fr[0] = f;
579         dr[0] = d;
580         bdr[0] = bd;
581         dtr[0] = dt;
582         tr[0] = t;
583         if (ts.equals(Timestamp.valueOf("2002-05-12 10:05:02.000000000")))
584         {
585             System.out.println("got the right Timestamp");
586             tsr[0] = ts;
587         }
588         else
589         {
590             System.out.println("got the wrong Timestamp");
591             tsr[0] = null;
592         }
593         bar[0] = ba;
594     }
595
596     public static void method5(BigDecimal JavaDoc bd1, BigDecimal JavaDoc bdr1[], BigDecimal JavaDoc bd2, BigDecimal JavaDoc bdr2[],
597                                 BigDecimal JavaDoc bdr3[], BigDecimal JavaDoc bdr4[], BigDecimal JavaDoc bdr5[], BigDecimal JavaDoc bdr6[],
598                                 BigDecimal JavaDoc bdr7[])
599     {
600         bdr1[0] = bd1;
601         bdr2[0] = bd1.multiply(bd2);
602         bdr3[0] = bd1.add(bd2);
603         bdr4[0] = new BigDecimal JavaDoc(".00000");
604         bdr5[0] = new BigDecimal JavaDoc("-.00000");
605         bdr6[0] = new BigDecimal JavaDoc("99999999.");
606         bdr7[0] = new BigDecimal JavaDoc("-99999999.");
607     }
608
609     // Test for INOUT params.
610
public static void method6 (int p1, int p2[], short s1, short s2[], long l1, long l2[],
611             float f1, float f2[], double d1, double d2[], Time JavaDoc t1, Time JavaDoc t2[])
612     {
613         p2[0] = p1 + p2[0];
614         s2[0] = (short) (s1 + s2[0]);
615         l2[0] = l1 + l2[0];
616         f2[0] = f1 + f2[0];
617         d2[0] = d1 + d2[0];
618         t2[0] = t1;
619     }
620
621     // Test for IN parameters with Longvarbinary column
622
public static void Longvarbinary_Proc_In (byte[] in_param) throws SQLException JavaDoc
623     {
624
625         Connection JavaDoc conn = DriverManager.getConnection("jdbc:default:connection");
626         PreparedStatement JavaDoc ps = conn.prepareStatement("update Longvarbinary_Tab set lvbc=?");
627
628         ps.setBytes(1,in_param);
629         ps.executeUpdate();
630
631         ps.close();ps=null;
632         conn.close();conn=null;
633     }
634
635
636     // test update of table in batch
637
public static void UpdTable_Proc (BigDecimal JavaDoc type_param) throws SQLException JavaDoc
638     {
639             Connection JavaDoc conn = DriverManager.getConnection("jdbc:default:connection");
640         PreparedStatement JavaDoc ps = conn.prepareStatement("update t2 set fc=fc*20 where tab1pk=?");
641         
642         ps.setBigDecimal(1,type_param);
643         ps.executeUpdate();
644
645         ps.close(); ps=null;
646         conn.close(); conn=null;
647     }
648
649     // test accessing minumum and maximum and null value for numeric columns with out params
650
public static void Numeric_Proc (BigDecimal JavaDoc[] param1,BigDecimal JavaDoc[] param2,
651                      BigDecimal JavaDoc[] param3) throws SQLException JavaDoc
652     {
653         Connection JavaDoc conn = DriverManager.getConnection("jdbc:default:connection");
654         Statement JavaDoc stmt = conn.createStatement();
655         ResultSet JavaDoc rs = stmt.executeQuery("select maxcol, mincol, nulcol from Num_Tab");
656         
657         if (rs.next())
658         {
659             param1[0]=rs.getBigDecimal(1);
660             param2[0]=rs.getBigDecimal(2);
661             param3[0]=rs.getBigDecimal(3);
662         }
663         else
664         {
665                 throw new SQLException JavaDoc("Data not found");
666         }
667         
668         rs.close();rs=null;
669         stmt.close();stmt=null;
670         conn.close();conn=null;
671     }
672
673     // Beetle 4933.
674
static void testBigDec(Connection JavaDoc conn) throws Exception JavaDoc
675     {
676         PreparedStatement JavaDoc ps = null;
677         int updcount;
678         
679       
680       try {
681           try {
682                   ps = conn.prepareStatement("drop table Num_Tab");
683                   updcount = ps.executeUpdate();
684           }
685           catch (SQLException JavaDoc e) {}
686           int tabSize = 10;
687           String JavaDoc createTabSql = "create table Num_Tab (maxcol NUMERIC(31,15), mincol NUMERIC(15,15), nulcol NUMERIC)";
688
689           ps = conn.prepareStatement(createTabSql);
690           updcount = ps.executeUpdate();
691           String JavaDoc insertTabSql = "insert into Num_Tab values(999999999999999,0.000000000000001, null)";
692           ps = conn.prepareStatement(insertTabSql);
693           updcount = ps.executeUpdate();
694           try {
695           String JavaDoc 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";
696           ps = conn.prepareStatement(alias);
697           updcount = ps.executeUpdate();
698           } catch (SQLException JavaDoc se) {}
699               
700
701           CallableStatement JavaDoc cstmt = conn.prepareCall("{call Numeric_Proc(?,?,?)}");
702           cstmt.registerOutParameter(1,java.sql.Types.NUMERIC,15);
703           cstmt.registerOutParameter(2,java.sql.Types.NUMERIC,15);
704           cstmt.registerOutParameter(3,java.sql.Types.NUMERIC,15);
705           // ParameterMetaData pmd = cstmt.getParameterMetaData();
706
//System.out.println("precision: " + pmd.getPrecision(1) +
707
// "scale: " + pmd.getScale(1));
708
//execute the procedure
709
cstmt.execute();
710
711           BigDecimal JavaDoc retVal = cstmt.getBigDecimal(1);
712           BigDecimal JavaDoc retVal2 = cstmt.getBigDecimal(2);
713           BigDecimal JavaDoc retVal3 = cstmt.getBigDecimal(3);
714
715           System.out.println("cstmt.getBigDecimal(1): " + retVal);
716           System.out.println("cstmt.getBigDecimal(2): " + retVal2);
717           System.out.println("cstmt.getBigDecimal(3): " + retVal3);
718           cstmt.close();
719           ps.close();
720           
721           }catch (SQLException JavaDoc e) {
722               System.out.println(e.getMessage());
723               e.printStackTrace();
724           }
725       
726     }
727
728
729     // test update of table in batch
730
public static void cleanUp (Statement JavaDoc stmt) throws SQLException JavaDoc
731     {
732         String JavaDoc[] testObjects = { "table longvarbinary_tab", "table num_tab",
733             "procedure method1", "function method2",
734             "function method4",
735             "procedure longvarbinary_in"};
736         TestUtil.cleanUpTest(stmt, testObjects);
737     }
738
739 }
740
Popular Tags