1 21 22 package org.apache.derbyTesting.functionTests.tests.jdbcapi; 23 24 import java.sql.Connection ; 25 import java.sql.DriverManager ; 26 import java.sql.PreparedStatement ; 27 import java.sql.ResultSet ; 28 import java.sql.ResultSetMetaData ; 29 import java.sql.Savepoint ; 30 import java.sql.Statement ; 31 import java.sql.SQLException ; 32 33 import org.apache.derby.tools.ij; 34 import org.apache.derby.tools.JDBCDisplayUtil; 35 36 import org.apache.derbyTesting.functionTests.util.TestUtil; 37 38 43 44 public class autoGeneratedJdbc30 { 45 46 private static String [] testObjects= {"table t11","table t21","table t31"}; 47 48 49 private static boolean HAVE_DRIVER_MANAGER_CLASS; 50 51 static{ 52 try{ 53 Class.forName("java.sql.DriverManager"); 54 HAVE_DRIVER_MANAGER_CLASS = true; 55 } 56 catch(ClassNotFoundException e){ 57 HAVE_DRIVER_MANAGER_CLASS = false; 59 } 60 } 61 62 public static void main(String [] args) { 63 64 Connection con = null; 65 Statement s; 66 PreparedStatement ps; 67 68 System.out.println("Test autoGeneratedJdbc30 starting"); 69 70 try 71 { 72 ij.getPropertyArg(args); 73 con = ij.startJBMS(); 74 75 s = con.createStatement(); 76 77 setUpTest(s); 78 con.setAutoCommit(false); 79 80 positiveTests(con); 81 82 if(HAVE_DRIVER_MANAGER_CLASS) 83 doTest1920(s, con); 84 85 negativeTests(con); 86 87 s = con.createStatement(); 88 TestUtil.cleanUpTest(s, testObjects); 89 con.commit(); 90 con.close(); 91 } 92 catch (SQLException e) { 93 JDBCDisplayUtil.ShowSQLException(System.out,e); 94 } 95 catch (Throwable e) { 96 System.out.println("FAIL -- unexpected exception:"); 97 e.printStackTrace(System.out); 98 } 99 100 System.out.println("Test autoGeneratedJdbc30 finished"); 101 } 102 103 public static String MyMethodWithNoInsert() throws SQLException { 104 System.out.println("Inside server-side method with no insert statement"); 105 Connection conn = DriverManager.getConnection("jdbc:default:connection"); 106 Statement s = conn.createStatement(); 107 s.executeQuery("select * from t11"); 108 return "true"; 109 } 110 111 public static String MyMethodWithInsert() throws SQLException { 112 System.out.println("Inside server-side method with couple insert statement with various combination of auto generated keys flag"); 113 Connection conn = DriverManager.getConnection("jdbc:default:connection"); 114 Statement s = conn.createStatement(); 115 s.execute("insert into t11(c11) values(999)", Statement.RETURN_GENERATED_KEYS); 116 dumpRS(s.getGeneratedKeys()); 117 s.execute("insert into t11(c11) values(999)", Statement.NO_GENERATED_KEYS); 118 try { 119 dumpRS(s.getGeneratedKeys()); 120 } catch (SQLException e) { 121 dumpExpectedSQLExceptions(e); 122 } 123 dumpRS(s.executeQuery("select * from t11")); 124 return "true"; 125 } 126 127 public static int count(Connection con, Statement s) throws SQLException { 128 int count = 0; 129 ResultSet rs = s.executeQuery("select count(*) from t11"); 130 rs.next(); 131 count = rs.getInt(1); 132 rs.close(); 133 return count; 134 } 135 136 public static void setUpTest(Statement s) 138 throws SQLException { 139 try { 140 s.execute("drop table t11"); 142 s.execute("drop table t21"); 143 s.execute("drop table t31"); 144 } catch (SQLException se) { 145 } 147 148 149 s.execute("create table t11 (c11 int, c12 int generated always as identity)"); 153 s.execute("alter table t11 alter c12 set increment by 1"); 154 s.execute("create table t21 (c21 int not null unique, c22 char(5))"); 155 s.execute("insert into t21 values(21, 'true')"); 156 s.execute("insert into t21 values(22, 'true')"); 157 s.execute("create table t31 (c31 int, c32 int generated always as identity, c33 int default 2)"); 158 s.execute("alter table t31 alter c32 set increment by 1"); 159 } 160 161 public static void dumpExpectedSQLExceptions (SQLException se) { 162 System.out.println("PASS -- expected exception"); 163 while (se != null) 164 { 165 System.out.println("SQLSTATE("+se.getSQLState()+"): "+se.getMessage()); 166 se = se.getNextException(); 167 } 168 } 169 170 171 public static void dumpRS(ResultSet s) throws SQLException 173 { 174 if (s == null) 175 { 176 System.out.println("<NULL>"); 177 return; 178 } 179 180 ResultSetMetaData rsmd = s.getMetaData(); 181 182 int numCols = rsmd.getColumnCount(); 184 185 if (numCols <= 0) 186 { 187 System.out.println("(no columns!)"); 188 return; 189 } 190 191 StringBuffer heading = new StringBuffer ("\t "); 192 StringBuffer underline = new StringBuffer ("\t "); 193 194 int len; 195 for (int i=1; i<=numCols; i++) 197 { 198 if (i > 1) 199 { 200 heading.append(","); 201 underline.append(" "); 202 } 203 len = heading.length(); 204 heading.append(rsmd.getColumnLabel(i)); 205 len = heading.length() - len; 206 for (int j = len; j > 0; j--) 207 { 208 underline.append("-"); 209 } 210 } 211 System.out.println(heading.toString()); 212 System.out.println(underline.toString()); 213 214 215 StringBuffer row = new StringBuffer (); 216 while (s.next()) 218 { 219 row.append("\t{"); 220 for (int i=1; i<=numCols; i++) 223 { 224 if (i > 1) row.append(","); 225 row.append(s.getString(i)); 226 } 227 row.append("}\n"); 228 } 229 System.out.println(row.toString()); 230 s.close(); 231 } 232 233 public static void disabledTestsBecauseOfBug5580(Statement s, Connection con, PreparedStatement ps) throws SQLException 234 { 235 System.out.println("Test11 - insert select with columnIndexes[] array"); 238 int colPositions[] = new int[1]; 239 colPositions[0] = 1; 240 s.execute("insert into t11(c11) select c21 from t21", colPositions); 241 dumpRS(s.getGeneratedKeys()); 242 s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions); 243 dumpRS(s.getGeneratedKeys()); 244 245 System.out.println("Test11ps - insert select with columnIndexes[] array"); 246 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions); 247 ps.execute(); 248 dumpRS(ps.getGeneratedKeys()); 249 ps.executeUpdate(); 250 dumpRS(ps.getGeneratedKeys()); 251 252 colPositions[0] = 2; 254 s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions); 255 try { 256 dumpRS(s.getGeneratedKeys()); 257 } catch (SQLException e) { 258 dumpExpectedSQLExceptions(e); 259 } 260 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions); 261 ps.executeUpdate(); 262 dumpRS(ps.getGeneratedKeys()); 263 264 if(count(con,s) != 12) { 266 System.out.println("Test failed"); 267 return; 268 } 269 s.execute("delete from t11"); 270 271 System.out.println("Test12 - insert select with columnIndexes[] array with duplicate column positions"); 273 colPositions = new int[2]; 274 colPositions[0] = 1; 275 colPositions[1] = 1; 276 s.execute("insert into t11(c11) select c21 from t21", colPositions); 277 dumpRS(s.getGeneratedKeys()); 278 s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions); 279 dumpRS(s.getGeneratedKeys()); 280 281 System.out.println("Test12ps - insert select with columnIndexes[] array with duplicate column positions"); 282 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions); 283 ps.execute(); 284 dumpRS(ps.getGeneratedKeys()); 285 ps.executeUpdate(); 286 dumpRS(ps.getGeneratedKeys()); 287 288 if(count(con,s) != 8) { 290 System.out.println("Test failed"); 291 return; 292 } 293 s.execute("delete from t11"); 294 295 System.out.println("Test13 - insert select with columnIndexes[] array with invalid column position"); 297 colPositions[0] = 3; 298 try { 299 s.execute("insert into t11(c11) select c21 from t21", colPositions); 300 } catch (SQLException e) { 301 dumpExpectedSQLExceptions(e); 302 } 303 try { 304 dumpRS(s.getGeneratedKeys()); 305 } catch (SQLException e) { 306 dumpExpectedSQLExceptions(e); 307 } 308 try { 309 s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions); 310 } catch (SQLException e) { 311 dumpExpectedSQLExceptions(e); 312 } 313 try { 314 dumpRS(s.getGeneratedKeys()); 315 } catch (SQLException e) { 316 dumpExpectedSQLExceptions(e); 317 } 318 319 System.out.println("Test13ps - insert select with columnIndexes[] array with invalid column position"); 320 try { 321 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions); 322 ps.execute(); 323 } catch (SQLException e) { 324 dumpExpectedSQLExceptions(e); 325 } 326 try { 327 dumpRS(ps.getGeneratedKeys()); 328 } catch (SQLException e) { 329 dumpExpectedSQLExceptions(e); 330 } 331 try { 332 ps.executeUpdate(); 333 } catch (SQLException e) { 334 dumpExpectedSQLExceptions(e); 335 } 336 try { 337 dumpRS(ps.getGeneratedKeys()); 338 } catch (SQLException e) { 339 dumpExpectedSQLExceptions(e); 340 } 341 342 if(count(con,s) != 0) { 344 System.out.println("Test failed"); 345 return; 346 } 347 s.execute("delete from t11"); 348 349 System.out.println("Test14 - insert select with columnNames[] array"); 351 String colNames[] = new String [1]; 352 colNames[0] = "C11"; 353 s.execute("insert into t11(c11) select c21 from t21", colNames); 354 dumpRS(s.getGeneratedKeys()); 355 s.executeUpdate("insert into t11(c11) select c21 from t21", colNames); 356 dumpRS(s.getGeneratedKeys()); 357 358 System.out.println("Test14ps - insert select with columnNames[] array"); 359 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames); 360 ps.execute(); 361 dumpRS(ps.getGeneratedKeys()); 362 ps.executeUpdate(); 363 dumpRS(ps.getGeneratedKeys()); 364 365 colNames[0] = "C12"; 367 s.executeUpdate("insert into t11(c11) select c21 from t21", colNames); 368 dumpRS(s.getGeneratedKeys()); 369 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames); 370 ps.executeUpdate(); 371 dumpRS(ps.getGeneratedKeys()); 372 373 if(count(con,s) != 12) { 375 System.out.println("Test failed"); 376 return; 377 } 378 s.execute("delete from t11"); 379 380 System.out.println("Test15 - insert select with columnNames[] array with duplicate column names"); 382 colNames = new String [2]; 383 colNames[0] = "C11"; 384 colNames[1] = "C11"; 385 s.execute("insert into t11(c11) select c21 from t21", colNames); 386 dumpRS(s.getGeneratedKeys()); 387 s.executeUpdate("insert into t11(c11) select c21 from t21", colNames); 388 dumpRS(s.getGeneratedKeys()); 389 390 System.out.println("Test15ps - insert select with columnNames[] array with duplicate column names"); 391 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames); 392 ps.execute(); 393 dumpRS(ps.getGeneratedKeys()); 394 ps.executeUpdate(); 395 dumpRS(ps.getGeneratedKeys()); 396 397 if(count(con,s) != 8) { 399 System.out.println("Test failed"); 400 return; 401 } 402 s.execute("delete from t11"); 403 404 colNames = new String [1]; 406 System.out.println("Test16 - insert select with columnNames[] array with invalid column name"); 407 colNames[0] = "C13"; 408 try { 409 s.execute("insert into t11(c11) select c21 from t21", colNames); 410 } catch (SQLException e) { 411 dumpExpectedSQLExceptions(e); 412 } 413 try { 414 dumpRS(s.getGeneratedKeys()); 415 } catch (SQLException e) { 416 dumpExpectedSQLExceptions(e); 417 } 418 try { 419 s.executeUpdate("insert into t11(c11) select c21 from t21", colNames); 420 } catch (SQLException e) { 421 dumpExpectedSQLExceptions(e); 422 } 423 try { 424 dumpRS(s.getGeneratedKeys()); 425 } catch (SQLException e) { 426 dumpExpectedSQLExceptions(e); 427 } 428 429 System.out.println("Test16ps - insert select with columnNames[] array with invalid column name"); 430 try { 431 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames); 432 ps.execute(); 433 } catch (SQLException e) { 434 dumpExpectedSQLExceptions(e); 435 } 436 try { 437 dumpRS(ps.getGeneratedKeys()); 438 } catch (SQLException e) { 439 dumpExpectedSQLExceptions(e); 440 } 441 try { 442 ps.executeUpdate(); 443 } catch (SQLException e) { 444 dumpExpectedSQLExceptions(e); 445 } 446 try { 447 dumpRS(ps.getGeneratedKeys()); 448 } catch (SQLException e) { 449 dumpExpectedSQLExceptions(e); 450 } 451 452 if(count(con,s) != 0) { 454 System.out.println("Test failed"); 455 return; 456 } 457 s.execute("delete from t11"); 458 459 System.out.println("Test17 - insert select with null columnIndexes[] internally switches to Statement.NO_GENERATED_KEYS"); 461 colPositions = null; 462 s.execute("insert into t11(c11) select c21 from t21", colPositions); 463 try { 464 dumpRS(s.getGeneratedKeys()); 465 } catch (SQLException e) { 466 dumpExpectedSQLExceptions(e); 467 } 468 s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions); 469 try { 470 dumpRS(s.getGeneratedKeys()); 471 } catch (SQLException e) { 472 dumpExpectedSQLExceptions(e); 473 } 474 475 System.out.println("Test17ps - insert select with null columnIndexes[] internally switches to Statement.NO_GENERATED_KEYS"); 476 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions); 477 ps.execute(); 478 try { 479 dumpRS(ps.getGeneratedKeys()); 480 } catch (SQLException e) { 481 dumpExpectedSQLExceptions(e); 482 } 483 ps.executeUpdate(); 484 try { 485 dumpRS(ps.getGeneratedKeys()); 486 } catch (SQLException e) { 487 dumpExpectedSQLExceptions(e); 488 } 489 490 if(count(con,s) != 8) { 492 System.out.println("Test failed"); 493 return; 494 } 495 s.execute("delete from t11"); 496 497 System.out.println("Test18 - insert select with null columnNames[] internally switches to Statement.NO_GENERATED_KEYS"); 499 colNames = null; 500 s.execute("insert into t11(c11) select c21 from t21", colNames); 501 try { 502 dumpRS(s.getGeneratedKeys()); 503 } catch (SQLException e) { 504 dumpExpectedSQLExceptions(e); 505 } 506 s.executeUpdate("insert into t11(c11) select c21 from t21", colNames); 507 try { 508 dumpRS(s.getGeneratedKeys()); 509 } catch (SQLException e) { 510 dumpExpectedSQLExceptions(e); 511 } 512 513 System.out.println("Test18ps - insert select with null columnNames[] internally switches to Statement.NO_GENERATED_KEYS"); 514 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames); 515 ps.execute(); 516 try { 517 dumpRS(ps.getGeneratedKeys()); 518 } catch (SQLException e) { 519 dumpExpectedSQLExceptions(e); 520 } 521 ps.executeUpdate(); 522 try { 523 dumpRS(ps.getGeneratedKeys()); 524 } catch (SQLException e) { 525 dumpExpectedSQLExceptions(e); 526 } 527 528 if(count(con,s) != 8) { 530 System.out.println("Test failed"); 531 return; 532 } 533 s.execute("delete from t11"); 534 535 System.out.println("Test19a - insert values with column position order which doesn't match column positions in the actual table"); 538 colPositions = new int[1]; 539 colPositions[0] = 1; 540 s.execute("insert into t21(c22,c21) values('true', 23)", colPositions); 541 dumpRS(s.getGeneratedKeys()); 542 s.executeUpdate("insert into t21(c22,c21) values('true', 23)", colPositions); 543 dumpRS(s.getGeneratedKeys()); 544 545 System.out.println("Test19aps - insert values with column position order which doesn't match column positions in the actual table"); 547 ps = con.prepareStatement("insert into t21(c22,c21) values('true', 23)", colPositions); 548 ps.execute(); 549 dumpRS(ps.getGeneratedKeys()); 550 ps.executeUpdate(); 551 dumpRS(ps.getGeneratedKeys()); 552 553 dumpRS(s.executeQuery("select count(*) from t21")); 555 s.execute("delete from t11"); 556 } 557 558 private static void positiveTests(Connection conn) throws SQLException 559 { 560 System.out.println("Test 1 - request for generated keys resultset on a brand new statement with no sql executed on it yet"); 561 System.out.println("We will get a resultset with no rows because it is a non-insert sql"); 562 Statement s = conn.createStatement(); 563 dumpRS(s.getGeneratedKeys()); 564 565 System.out.println("Test2 - request for generated keys on a statement which does select from a table ie a non-insert sql"); 566 s.execute("select * from t11", Statement.RETURN_GENERATED_KEYS); 567 System.out.println("We will get a resultset with no rows because it is a non-insert sql"); 568 dumpRS(s.getGeneratedKeys()); 569 System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same"); 570 PreparedStatement ps = conn.prepareStatement("select * from t11", Statement.RETURN_GENERATED_KEYS); 571 ps.execute(); 572 dumpRS(ps.getGeneratedKeys()); 573 574 System.out.println("Test 3 - insert multiple rows into a table with autogenerated key and request generated keys resultset"); 575 System.out.println(" We will get a row with NULL value because this insert sql inserted more than one row and "); 576 System.out.println(" there was no prior one-row insert into a table with autogenerated key"); 577 s.execute("insert into t31(c31) values (99), (98), (97)", Statement.RETURN_GENERATED_KEYS); 578 dumpRS(s.getGeneratedKeys()); 579 System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same"); 580 s.executeUpdate("insert into t31(c31) values (99), (98), (97)", Statement.RETURN_GENERATED_KEYS); 581 dumpRS(s.getGeneratedKeys()); 582 System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same"); 583 ps = conn.prepareStatement("insert into t31(c31) values (99), (98), (97)", Statement.RETURN_GENERATED_KEYS); 584 ps.execute(); 585 dumpRS(ps.getGeneratedKeys()); 586 System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same"); 587 ps.executeUpdate(); 588 dumpRS(ps.getGeneratedKeys()); 589 590 System.out.println("Test 4 - request for generated keys after doing an insert into a table with no auto generated keys"); 591 System.out.println(" And there has been no one-row insert into a table with auto-generated keys yet."); 592 s.execute("insert into t21 values(23, 'true')", Statement.RETURN_GENERATED_KEYS); 593 System.out.println("We should get a resultset with one row of NULL value from getGeneratedKeys"); 594 dumpRS(s.getGeneratedKeys()); 595 System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same"); 596 s.executeUpdate("insert into t21 values(24, 'true')", Statement.RETURN_GENERATED_KEYS); 597 dumpRS(s.getGeneratedKeys()); 598 System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same"); 599 ps = conn.prepareStatement("insert into t21 values(25, 'true')", Statement.RETURN_GENERATED_KEYS); 600 ps.execute(); 601 dumpRS(ps.getGeneratedKeys()); 602 System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same"); 603 ps = conn.prepareStatement("insert into t21 values(26, 'true')", Statement.RETURN_GENERATED_KEYS); 604 ps.executeUpdate(); 605 dumpRS(ps.getGeneratedKeys()); 606 607 System.out.println("Test 5a - request for generated keys after doing a one-row insert(using a subquery) into a table with autogenerated key using a subquery with auto generated keys"); 608 s.execute("insert into t11(c11) select c21 from t21", Statement.RETURN_GENERATED_KEYS); 609 System.out.println("Even though this is a one-row insert but since insert has a sub-select, get generated keys will return one row of NULL value"); 610 dumpRS(s.getGeneratedKeys()); 611 System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same"); 612 s.executeUpdate("insert into t11(c11) select c21 from t21", Statement.RETURN_GENERATED_KEYS); 613 dumpRS(s.getGeneratedKeys()); 614 System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same"); 615 ps = conn.prepareStatement("insert into t11(c11) select c21 from t21", Statement.RETURN_GENERATED_KEYS); 616 ps.execute(); 617 dumpRS(ps.getGeneratedKeys()); 618 System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same"); 619 ps.executeUpdate(); 620 dumpRS(ps.getGeneratedKeys()); 621 622 System.out.println("Test 5b - request for generated keys after doing a one-row insert(using a subquery) into a table with autogenerated key using a subquery with auto generated keys"); 623 s.execute("insert into t11(c11) select c21 from t21 where 1=2", Statement.RETURN_GENERATED_KEYS); 624 System.out.println("Even though this is a one-row insert but since insert has a sub-select, get generated keys will return one row of NULL value"); 625 dumpRS(s.getGeneratedKeys()); 626 System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same"); 627 s.executeUpdate("insert into t11(c11) select c21 from t21 where 1=2", Statement.RETURN_GENERATED_KEYS); 628 dumpRS(s.getGeneratedKeys()); 629 System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same"); 630 ps = conn.prepareStatement("insert into t11(c11) select c21 from t21 where 1=2", Statement.RETURN_GENERATED_KEYS); 631 ps.execute(); 632 dumpRS(ps.getGeneratedKeys()); 633 System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same"); 634 ps.executeUpdate(); 635 dumpRS(ps.getGeneratedKeys()); 636 637 System.out.println("Test 5c - request for generated keys after doing a one-row insert(using a subquery) into a table with autogenerated key using a subquery with auto generated keys"); 638 s.execute("insert into t11(c11) select c21 from t21 where c21=23", Statement.RETURN_GENERATED_KEYS); 639 System.out.println("Even though this is a one-row insert but since insert has a sub-select, get generated keys will return one row of NULL value"); 640 dumpRS(s.getGeneratedKeys()); 641 System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same"); 642 s.executeUpdate("insert into t11(c11) select c21 from t21 where c21=23", Statement.RETURN_GENERATED_KEYS); 643 dumpRS(s.getGeneratedKeys()); 644 System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same"); 645 ps = conn.prepareStatement("insert into t11(c11) select c21 from t21 where c21=23", Statement.RETURN_GENERATED_KEYS); 646 ps.execute(); 647 dumpRS(ps.getGeneratedKeys()); 648 System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same"); 649 ps.executeUpdate(); 650 dumpRS(ps.getGeneratedKeys()); 651 652 System.out.println("Test 6 - request for generated keys after doing a one-row insert into a table with auto generated keys"); 653 s.execute("insert into t11(c11) values (99)", Statement.RETURN_GENERATED_KEYS); 654 System.out.println("We should get a resultset with one row of non-NULL value"); 655 dumpRS(s.getGeneratedKeys()); 656 System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same"); 657 s.executeUpdate("insert into t11(c11) values (99)", Statement.RETURN_GENERATED_KEYS); 658 dumpRS(s.getGeneratedKeys()); 659 System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same"); 660 ps = conn.prepareStatement("insert into t11(c11) values (99)", Statement.RETURN_GENERATED_KEYS); 661 ps.execute(); 662 dumpRS(ps.getGeneratedKeys()); 663 System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same"); 664 ps.executeUpdate(); 665 dumpRS(ps.getGeneratedKeys()); 666 667 System.out.println("Test 7 - Now try again inserting multiple rows into a table with autogenerated key and request generated keys resultset"); 668 System.out.println(" This time we will get a row of non-NULL value because there has been a prior one-row insert into table with auto-generated key "); 669 s.execute("insert into t31(c31) values (99), (98), (97)", Statement.RETURN_GENERATED_KEYS); 670 dumpRS(s.getGeneratedKeys()); 671 System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same"); 672 s.executeUpdate("insert into t31(c31) values (99), (98), (97)", Statement.RETURN_GENERATED_KEYS); 673 dumpRS(s.getGeneratedKeys()); 674 System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same"); 675 ps = conn.prepareStatement("insert into t31(c31) values (99), (98), (97)", Statement.RETURN_GENERATED_KEYS); 676 ps.execute(); 677 dumpRS(ps.getGeneratedKeys()); 678 System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same"); 679 ps.executeUpdate(); 680 dumpRS(ps.getGeneratedKeys()); 681 682 System.out.println("Test 8 - create a new statement and request for generated keys on it after doing an insert into "); 683 System.out.println(" a table with no auto generated keys"); 684 Statement s1 = conn.createStatement(); 685 s1.execute("insert into t21 values(27, 'true')", Statement.RETURN_GENERATED_KEYS); 686 System.out.println("We should get a resultset with one row of non-NULL value"); 687 dumpRS(s1.getGeneratedKeys()); 688 System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same"); 689 s1.executeUpdate("insert into t21 values(28, 'true')", Statement.RETURN_GENERATED_KEYS); 690 dumpRS(s1.getGeneratedKeys()); 691 System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same"); 692 ps = conn.prepareStatement("insert into t21 values(29, 'true')", Statement.RETURN_GENERATED_KEYS); 693 ps.execute(); 694 dumpRS(ps.getGeneratedKeys()); 695 System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same"); 696 ps = conn.prepareStatement("insert into t21 values(30, 'true')", Statement.RETURN_GENERATED_KEYS); 697 ps.executeUpdate(); 698 dumpRS(ps.getGeneratedKeys()); 699 700 System.out.println("Test 9 - request for generated keys on a statement which does a update "); 701 s.execute("update t11 set c11=1", Statement.RETURN_GENERATED_KEYS); 702 System.out.println("We should get a resultset with no rows from getGeneratedKeys because we executed a non-insert sql"); 703 dumpRS(s.getGeneratedKeys()); 704 System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same"); 705 s.executeUpdate("update t11 set c11=1", Statement.RETURN_GENERATED_KEYS); 706 dumpRS(s.getGeneratedKeys()); 707 System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same"); 708 ps = conn.prepareStatement("update t11 set c11=1", Statement.RETURN_GENERATED_KEYS); 709 ps.execute(); 710 dumpRS(ps.getGeneratedKeys()); 711 System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same"); 712 ps.executeUpdate(); 713 dumpRS(ps.getGeneratedKeys()); 714 715 System.out.println("Test 10 - request for generated keys on a statement which does a delete "); 716 s.execute("delete from t11", Statement.RETURN_GENERATED_KEYS); 717 System.out.println("We should get a resultset with no rows from getGeneratedKeys because we executed a non-insert sql"); 718 dumpRS(s.getGeneratedKeys()); 719 System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same"); 720 s.executeUpdate("delete from t11", Statement.RETURN_GENERATED_KEYS); 721 dumpRS(s.getGeneratedKeys()); 722 System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same"); 723 ps = conn.prepareStatement("delete from t11", Statement.RETURN_GENERATED_KEYS); 724 ps.execute(); 725 dumpRS(ps.getGeneratedKeys()); 726 System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same"); 727 ps.executeUpdate(); 728 dumpRS(ps.getGeneratedKeys()); 729 730 System.out.println("Test 11 - do a commit and request for generated keys on a statement which does insert into a table with "); 731 System.out.println(" no auto generated keys (previous transaction had a one-row insert on a table with auto-generated keys)"); 732 conn.commit(); 733 s.execute("insert into t21 values(31, 'true')", Statement.RETURN_GENERATED_KEYS); 734 System.out.println("expected to see resultset with one row of NULL value but instead get one row of non-NULL value from getGeneratedKeys"); 735 dumpRS(s.getGeneratedKeys()); 736 System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same"); 737 s.executeUpdate("insert into t21 values(32, 'true')", Statement.RETURN_GENERATED_KEYS); 738 dumpRS(s.getGeneratedKeys()); 739 System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same"); 740 ps = conn.prepareStatement("insert into t21 values(33, 'true')", Statement.RETURN_GENERATED_KEYS); 741 ps.execute(); 742 dumpRS(ps.getGeneratedKeys()); 743 System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same"); 744 ps = conn.prepareStatement("insert into t21 values(34, 'true')", Statement.RETURN_GENERATED_KEYS); 745 ps.executeUpdate(); 746 dumpRS(ps.getGeneratedKeys()); 747 748 System.out.println("Test 12 - do a rollback and request for generated keys on a statement which does insert into a table with "); 749 System.out.println(" no auto generated keys (previous transaction had a one-row insert on a table with auto-generated keys)"); 750 conn.rollback(); 751 s.execute("insert into t21 values(35, 'true')", Statement.RETURN_GENERATED_KEYS); 752 System.out.println("had expected to see resultset with one row of NULL value but instead get one row of non-NULL value from getGeneratedKeys"); 753 dumpRS(s.getGeneratedKeys()); 754 System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same"); 755 s.executeUpdate("insert into t21 values(36, 'true')", Statement.RETURN_GENERATED_KEYS); 756 dumpRS(s.getGeneratedKeys()); 757 System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same"); 758 ps = conn.prepareStatement("insert into t21 values(37, 'true')", Statement.RETURN_GENERATED_KEYS); 759 ps.execute(); 760 dumpRS(ps.getGeneratedKeys()); 761 System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same"); 762 ps = conn.prepareStatement("insert into t21 values(38, 'true')", Statement.RETURN_GENERATED_KEYS); 763 ps.executeUpdate(); 764 dumpRS(ps.getGeneratedKeys()); 765 766 System.out.println("Test 13 - try savepoint rollback and see what happens to auto generated keys resultset"); 767 Savepoint savepoint1; 768 System.out.println("Inside the savepoint unit, issue a one-row insert into a table with auto generated keys"); 769 savepoint1 = conn.setSavepoint(); 770 s.execute("insert into t11(c11) values (99)", Statement.RETURN_GENERATED_KEYS); 771 System.out.println("We should get a resultset with one row of non-NULL value"); 772 dumpRS(s.getGeneratedKeys()); 773 System.out.println("Now rollback the savepoint unit, and make sure that autogenerated keys resultset still holds on to "); 774 System.out.println(" value that got set inside the rolled back savepoint unit"); 775 conn.rollback(savepoint1); 776 s.execute("insert into t21 values(39, 'true')", Statement.RETURN_GENERATED_KEYS); 777 dumpRS(s.getGeneratedKeys()); 778 779 System.out.println("Test 14 - Look at metadata of a getGeneratedKeys resultset"); 780 s = conn.createStatement(); 781 s.execute("insert into t31(c31) values (99)", Statement.RETURN_GENERATED_KEYS); 782 ResultSet rs = s.getGeneratedKeys(); 783 ResultSetMetaData rsmd = rs.getMetaData(); 784 System.out.println("The resultset will have one column only"); 785 System.out.println("Found " + rsmd.getColumnCount() + " column in the resultset"); 786 System.out.println("Type of the column is " + rsmd.getColumnTypeName(1)); 787 System.out.println("Precision of the column is " + rsmd.getPrecision(1)); 788 System.out.println("Scale of the column is " + rsmd.getScale(1)); 789 dumpRS(rs); 790 791 System.out.println("Test 15 - Can not see the auto generated keys if insert is with NO_GENERATED_KEYS"); 792 s = conn.createStatement(); 793 s.execute("insert into t31(c31) values (99)", Statement.NO_GENERATED_KEYS); 794 dumpRS(s.getGeneratedKeys()); 795 s.executeUpdate("insert into t31(c31) values (99)", Statement.NO_GENERATED_KEYS); 796 dumpRS(s.getGeneratedKeys()); 797 ps = conn.prepareStatement("insert into t31(c31) values (99)", Statement.NO_GENERATED_KEYS); 798 ps.execute(); 799 dumpRS(ps.getGeneratedKeys()); 800 ps.executeUpdate(); 801 dumpRS(ps.getGeneratedKeys()); 802 803 System.out.println("Test 16 - Can not see the auto generated keys if insert is done jdbc 2.0 way ie with no generated key feature"); 804 s.execute("insert into t31(c31) values (99)"); 805 dumpRS(s.getGeneratedKeys()); 806 s.executeUpdate("insert into t31(c31) values (99)"); 807 dumpRS(s.getGeneratedKeys()); 808 ps = conn.prepareStatement("insert into t31(c31) values (99)"); 809 ps.execute(); 810 dumpRS(ps.getGeneratedKeys()); 811 ps.executeUpdate(); 812 dumpRS(ps.getGeneratedKeys()); 813 814 System.out.println("Test 17 - non-insert with NO_GENERATED_KEYS"); 815 s = conn.createStatement(); 816 s.execute("update t31 set c31=98", Statement.NO_GENERATED_KEYS); 817 dumpRS(s.getGeneratedKeys()); 818 s.executeUpdate("update t31 set c31=98", Statement.NO_GENERATED_KEYS); 819 dumpRS(s.getGeneratedKeys()); 820 ps = conn.prepareStatement("update t31 set c31=98", Statement.NO_GENERATED_KEYS); 821 ps.execute(); 822 dumpRS(ps.getGeneratedKeys()); 823 ps.executeUpdate(); 824 dumpRS(ps.getGeneratedKeys()); 825 826 System.out.println("Test 18 - non-insert is done jdbc 2.0 way ie with no generated key feature"); 827 s.execute("delete from t31"); 828 dumpRS(s.getGeneratedKeys()); 829 s.executeUpdate("delete from t31"); 830 dumpRS(s.getGeneratedKeys()); 831 ps = conn.prepareStatement("delete from t31"); 832 ps.execute(); 833 dumpRS(ps.getGeneratedKeys()); 834 ps.executeUpdate(); 835 dumpRS(ps.getGeneratedKeys()); 836 837 } 838 839 public static void negativeTests(Connection con) throws SQLException 840 { 841 Statement s = con.createStatement(); 842 PreparedStatement ps; 843 System.out.println("Test21 - insert select with columnIndexes[] array should fail"); 845 int colPositions[] = new int[1]; 846 colPositions[0] = 1; 847 try { 848 System.out.println("Try passing array with Statement.execute"); 849 s.execute("insert into t11(c11) select c21 from t21", colPositions); 850 System.out.println("ERROR: shouldn't be able to pass array with Statement.execute"); 851 852 } catch (SQLException e) { 853 if ((e.getMessage() != null && 854 e.getMessage().indexOf("Driver not capable") >= 0) 855 || (e.getSQLState() != null && 856 (e.getSQLState().startsWith("0A")))) 857 System.out.println("PASS - expected exception - Feature not implemented"); 858 else System.out.println("Unexpected FAILURE at " +e); 859 860 } 861 try { 862 System.out.println("Try passing array with Statement.executeUpdate"); 863 s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions); 864 System.out.println("ERROR: shouldn't be able to pass array with Statement.executeUpdate"); 865 } catch (SQLException e) { 866 if (e.getSQLState() == null || e.getSQLState().startsWith("0A")) 867 dumpExpectedSQLExceptions(e); 868 else 869 JDBCDisplayUtil.ShowSQLException(System.out,e); 870 } 871 872 System.out.println("Test21ps - insert select with columnIndexes[] array should fail"); 873 try { 874 System.out.println("Try passing array with Connection.prepareStatement"); 875 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions); 876 System.out.println("ERROR: shouldn't be able to pass array with Connection.prepareStatement"); 877 } catch (SQLException e) { 878 if (e.getSQLState() == null || e.getSQLState().startsWith("0A")) 879 dumpExpectedSQLExceptions(e); 880 else 881 JDBCDisplayUtil.ShowSQLException(System.out,e); 882 } 883 884 System.out.println("Test22 - insert select with columnNames[] array should fail"); 886 String colNames[] = new String [1]; 887 colNames[0] = "C11"; 888 try { 889 System.out.println("Try passing array with Statement.execute"); 890 s.execute("insert into t11(c11) select c21 from t21", colNames); 891 System.out.println("ERROR: shouldn't be able to pass array with Statement.execute"); 892 } catch (SQLException e) { 893 if (e.getSQLState() == null || e.getSQLState().startsWith("0A")) 894 dumpExpectedSQLExceptions(e); 895 else 896 JDBCDisplayUtil.ShowSQLException(System.out,e); 897 } 898 try { 899 System.out.println("Try passing array with Statement.executeUpdate"); 900 s.executeUpdate("insert into t11(c11) select c21 from t21", colNames); 901 System.out.println("ERROR: shouldn't be able to pass array with Statement.executeUpdate"); 902 } catch (SQLException e) { 903 if (e.getSQLState() == null || e.getSQLState().startsWith("0A")) 904 dumpExpectedSQLExceptions(e); 905 else 906 JDBCDisplayUtil.ShowSQLException(System.out,e); 907 } 908 909 System.out.println("Test22ps - insert select with columnNames[] array should fail"); 910 try { 911 System.out.println("Try passing array with Connection.prepareStatement"); 912 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames); 913 System.out.println("ERROR: shouldn't be able to pass array with Connection.prepareStatement"); 914 } catch (SQLException e) { 915 if (e.getSQLState() == null || e.getSQLState().startsWith("0A")) 916 dumpExpectedSQLExceptions(e); 917 else 918 JDBCDisplayUtil.ShowSQLException(System.out,e); 919 } 920 con.rollback(); 921 } 922 923 public static void doTest1920(Statement s, Connection con) throws SQLException 924 { 925 System.out.println("Test19 - fix the no auto generated key resultset generated for INSERT with " + 930 "generated keys if server-side methods are invoked"); 931 s.execute("CREATE FUNCTION MMWNI() RETURNS VARCHAR(20) LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.jdbcapi.autoGeneratedJdbc30.MyMethodWithNoInsert' PARAMETER STYLE JAVA READS SQL DATA"); 932 con.commit(); 933 s.execute("insert into t21 values(40,MMWNI())", Statement.RETURN_GENERATED_KEYS); 934 System.out.println("Back to client side looking for auto generated keys"); 935 dumpRS(s.getGeneratedKeys()); 936 937 dumpRS(s.executeQuery("select count(*) from t21")); 939 s.execute("delete from t11"); 940 941 s.execute("DROP FUNCTION MMWNI"); 942 con.commit(); 943 s.close(); 944 945 System.out.println("Test20 - bug 4837garbage collection of the generated key result sets was closing the activation."); 947 PreparedStatement ps = con.prepareStatement("insert into t11(c11) values(?)", Statement.RETURN_GENERATED_KEYS); 948 949 for (int i = 0; i < 100; i++) { 950 ps.setInt(1, 100+i); 951 ps.executeUpdate(); 952 953 ResultSet rs = ps.getGeneratedKeys(); 954 while (rs.next()) { 955 rs.getInt(1); 956 } 957 rs.close(); 958 con.commit(); 959 960 System.runFinalization(); 961 System.gc(); 962 System.runFinalization(); 963 System.gc(); 964 } 965 } 966 } 967 | Popular Tags |