1 21 22 package org.apache.derbyTesting.functionTests.tests.derbynet; 23 24 import java.math.BigDecimal ; 25 import java.sql.Date ; 26 import java.sql.Time ; 27 import java.sql.Timestamp ; 28 import java.sql.CallableStatement ; 29 import java.sql.Statement ; 30 import java.sql.PreparedStatement ; 31 import java.sql.ResultSet ; 32 import java.sql.SQLException ; 33 import java.sql.BatchUpdateException ; 34 import java.sql.DriverManager ; 35 import java.sql.Connection ; 36 import org.apache.derby.tools.ij; 37 import org.apache.derbyTesting.functionTests.util.TestUtil; 38 41 42 public class callable 43 { 44 45 public static void main (String args[]) 46 { 47 try 48 { 49 System.out.println("CallableStatement Test Starts"); 50 51 ij.getPropertyArg(args); 52 53 String protocol = System.getProperty("ij.protocol"); 55 String 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 conn = ij.startJBMS(); 59 if (conn == null) 60 { 61 System.out.println("conn didn't work"); 62 return; 63 } 64 Statement stmt = conn.createStatement(); 65 66 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 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 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 93 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 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 113 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 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 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 ("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 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 ("33.333")); 195 cs.registerOutParameter (2, java.sql.Types.DECIMAL); 196 cs.setBigDecimal(3, new BigDecimal ("-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 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 258 cleanUp(stmt); 259 stmt.close(); 260 conn.close(); 261 System.out.println("CallableStatement Test Ends"); 262 } 263 catch (Exception e) 264 { 265 e.printStackTrace(); 266 } 267 } 268 269 static void testLongBinary(Connection conn) 270 { 271 try { 272 String createTabSql = "create table Longvarbinary_Tab (lvbc Long varchar for bit data)"; 273 PreparedStatement ps = conn.prepareStatement(createTabSql); 274 int updcount = ps.executeUpdate(); 275 String 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 sbyteval=null; 282 283 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 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 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 stmt = conn.createStatement(); 305 String Longvarbinary_Query="Select lvbc from Longvarbinary_Tab"; 306 System.out.println(Longvarbinary_Query); 307 ResultSet 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 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 conn) 334 { 335 try { 336 conn.setAutoCommit(true); 337 int i=0; 338 int retValue[]={0,0,0}; 339 int updCountLength=0; 340 Statement stmt = conn.createStatement(); 341 PreparedStatement 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 e) {} 353 354 String 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 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 se) { 375 do { 376 System.out.println("Exception chain: "+se.getMessage()); 377 se = se.getNextException(); 378 } while (se != null); 379 } 380 381 ResultSet 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 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 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 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 prepString= "update tab2 set tab2pk=?, vcc2=? where vcc2=?"; 444 PreparedStatement 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 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 sqle) 485 { 486 System.out.println("Call to continueUpdate failed!" + sqle); 487 } 488 catch ( Exception 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 try 507 { 508 String 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 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 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 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 bd, Date dt, Time t, Timestamp ts, byte[] ba, 571 short[] sr, int[] ir, 572 long[] lr, float[] fr, double[] dr, BigDecimal [] bdr, 573 Date [] dtr, Time [] tr, Timestamp [] 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 bd1, BigDecimal bdr1[], BigDecimal bd2, BigDecimal bdr2[], 597 BigDecimal bdr3[], BigDecimal bdr4[], BigDecimal bdr5[], BigDecimal bdr6[], 598 BigDecimal bdr7[]) 599 { 600 bdr1[0] = bd1; 601 bdr2[0] = bd1.multiply(bd2); 602 bdr3[0] = bd1.add(bd2); 603 bdr4[0] = new BigDecimal (".00000"); 604 bdr5[0] = new BigDecimal ("-.00000"); 605 bdr6[0] = new BigDecimal ("99999999."); 606 bdr7[0] = new BigDecimal ("-99999999."); 607 } 608 609 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 t1, Time 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 public static void Longvarbinary_Proc_In (byte[] in_param) throws SQLException 623 { 624 625 Connection conn = DriverManager.getConnection("jdbc:default:connection"); 626 PreparedStatement 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 public static void UpdTable_Proc (BigDecimal type_param) throws SQLException 638 { 639 Connection conn = DriverManager.getConnection("jdbc:default:connection"); 640 PreparedStatement 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 public static void Numeric_Proc (BigDecimal [] param1,BigDecimal [] param2, 651 BigDecimal [] param3) throws SQLException 652 { 653 Connection conn = DriverManager.getConnection("jdbc:default:connection"); 654 Statement stmt = conn.createStatement(); 655 ResultSet 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 ("Data not found"); 666 } 667 668 rs.close();rs=null; 669 stmt.close();stmt=null; 670 conn.close();conn=null; 671 } 672 673 static void testBigDec(Connection conn) throws Exception 675 { 676 PreparedStatement 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 e) {} 686 int tabSize = 10; 687 String 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 insertTabSql = "insert into Num_Tab values(999999999999999,0.000000000000001, null)"; 692 ps = conn.prepareStatement(insertTabSql); 693 updcount = ps.executeUpdate(); 694 try { 695 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"; 696 ps = conn.prepareStatement(alias); 697 updcount = ps.executeUpdate(); 698 } catch (SQLException se) {} 699 700 701 CallableStatement 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 cstmt.execute(); 710 711 BigDecimal retVal = cstmt.getBigDecimal(1); 712 BigDecimal retVal2 = cstmt.getBigDecimal(2); 713 BigDecimal 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 e) { 722 System.out.println(e.getMessage()); 723 e.printStackTrace(); 724 } 725 726 } 727 728 729 public static void cleanUp (Statement stmt) throws SQLException 731 { 732 String [] 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 |