1 21 22 package org.apache.derbyTesting.functionTests.tests.lang; 23 24 import java.sql.CallableStatement ; 25 import java.sql.Connection ; 26 import java.sql.DriverManager ; 27 import java.sql.PreparedStatement ; 28 import java.sql.ResultSet ; 29 import java.sql.ResultSetMetaData ; 30 import java.sql.SQLException ; 31 import java.sql.Statement ; 32 import java.sql.Types ; 33 34 import org.apache.derby.tools.ij; 35 import org.apache.derby.tools.JDBCDisplayUtil; 36 37 import org.apache.derbyTesting.functionTests.util.TestUtil; 38 39 42 public class holdCursorJava { 43 private static String [] databaseObjects = {"PROCEDURE MYPROC", "TABLE T1", "TABLE T2", 44 "TABLE TESTTABLE1", "TABLE TESTTABLE2", 45 "TABLE BUG4385"}; 46 private static boolean HAVE_DRIVER_MANAGER_CLASS; 47 48 static{ 49 try{ 50 Class.forName("java.sql.DriverManager"); 51 HAVE_DRIVER_MANAGER_CLASS = true; 52 } 53 catch(ClassNotFoundException e){ 54 HAVE_DRIVER_MANAGER_CLASS = false; 56 } 57 } 58 59 public static void main (String args[]) 60 { 61 try { 62 63 ij.getPropertyArg(args); 66 Connection conn = ij.startJBMS(); 67 68 createAndPopulateTable(conn); 69 70 conn.setAutoCommit(false); 72 73 if(HAVE_DRIVER_MANAGER_CLASS){ 74 testHoldability(conn,ResultSet.HOLD_CURSORS_OVER_COMMIT); 75 testHoldability(conn,ResultSet.CLOSE_CURSORS_AT_COMMIT); 76 } 77 78 testHoldCursorOnMultiTableQuery(conn); 79 testIsolationLevelChange(conn); 80 testCloseCursor(conn); 81 testDropTable(conn); 82 83 conn.rollback(); 84 conn.setAutoCommit(true); 85 86 Statement stmt = conn.createStatement(); 87 TestUtil.cleanUpTest(stmt, databaseObjects); 88 conn.close(); 89 90 91 } catch (Exception e) { 92 System.out.println("FAIL -- unexpected exception "+e); 93 JDBCDisplayUtil.ShowException(System.out, e); 94 e.printStackTrace(); 95 } 96 } 97 98 private static void createAndPopulateTable(Connection conn) throws SQLException { 100 Statement stmt = conn.createStatement(); 101 102 TestUtil.cleanUpTest(stmt, databaseObjects); 104 105 System.out.println("Creating table..."); 106 final int stringLength = 400; 107 stmt.executeUpdate("CREATE TABLE T1 (c11 int, c12 int, junk varchar(" + 108 stringLength + "))"); 109 PreparedStatement insertStmt = 110 conn.prepareStatement("INSERT INTO T1 VALUES(?,1,?)"); 111 final int rows = 40000 / stringLength; 116 StringBuffer buff = new StringBuffer (stringLength); 117 for (int i = 0; i < stringLength; i++) { 118 buff.append(" "); 119 } 120 for (int i = 1; i <= rows; i++) { 121 insertStmt.setInt(1, i); 122 insertStmt.setString(2, buff.toString()); 123 insertStmt.executeUpdate(); 124 } 125 insertStmt.close(); 126 stmt.executeUpdate( "CREATE TABLE T2 (c21 int, c22 int)" ); 127 stmt.executeUpdate("INSERT INTO T2 VALUES(1,1)"); 128 stmt.executeUpdate("INSERT INTO T2 VALUES(1,2)"); 129 stmt.executeUpdate("INSERT INTO T2 VALUES(1,3)"); 130 stmt.execute("create table testtable1 (id integer, vc varchar(100))"); 131 stmt.execute("insert into testtable1 values (11, 'testtable1-one'), (12, 'testtable1-two')"); 132 stmt.execute("create table testtable2 (id integer, vc varchar(100))"); 133 stmt.execute("insert into testtable2 values (21, 'testtable2-one'), (22, 'testtable2-two')"); 134 stmt.execute("create procedure MYPROC() language java parameter style java external name " + 135 "'org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava.testProc' result sets 2"); 136 System.out.println("done creating table and inserting data."); 137 138 stmt.close(); 139 } 140 141 private static void cleanUpTest(Connection conn) throws SQLException { 143 Statement stmt = conn.createStatement(); 144 stmt.executeUpdate( "DROP PROCEDURE MYPROC" ); 146 stmt.executeUpdate( "DROP TABLE T1" ); 147 stmt.executeUpdate( "DROP TABLE T2" ); 148 stmt.executeUpdate( "DROP TABLE testtable1" ); 149 stmt.executeUpdate( "DROP TABLE testtable2" ); 150 stmt.executeUpdate( "DROP TABLE BUG4385" ); 151 stmt.close(); 152 } 153 154 private static void testHoldCursorOnMultiTableQuery(Connection conn) throws Exception 156 { 157 Statement s; 158 ResultSet rs; 159 160 System.out.println("Start multi table query with holdability true test"); 161 s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, 162 ResultSet.HOLD_CURSORS_OVER_COMMIT ); 163 164 rs = s.executeQuery("select t1.c11, t2.c22 from t1, t2 where t1.c11=t2.c21"); 166 rs.next(); 167 System.out.println("value of t2.c22 is " + rs.getString(2)); 168 conn.commit(); 169 rs.next(); System.out.println("value of t2.c22 is " + rs.getString(2)); 171 rs.close(); 172 System.out.println("Multi table query with holdability true test over"); 173 } 174 175 private static void testIsolationLevelChange(Connection conn) throws Exception 177 { 178 Statement s; 179 ResultSet rs; 180 181 System.out.println("Start isolation level change test"); 182 conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); 184 185 s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, 186 ResultSet.HOLD_CURSORS_OVER_COMMIT ); 187 188 rs = s.executeQuery("select * from t1"); 190 rs.next(); 191 192 try { 195 System.out.println("Switch isolation while there are open cursors"); 196 conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); 197 } catch (SQLException se) { 198 199 System.out.println("Should see exceptions"); 200 String m = se.getSQLState(); 201 JDBCDisplayUtil.ShowSQLException(System.out,se); 202 203 if ("X0X03".equals(m)) { 204 System.out.println("PASS: Can't change isolation if they are open cursor"); 205 } else { 206 System.out.println("FAIL: Shouldn't able to change isolation because there are open cursor"); 207 } 208 } 209 210 rs.close(); 213 conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); 214 215 217 218 conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT); 219 220 conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED); 221 conn.createStatement().executeUpdate("SET ISOLATION RS"); 222 223 conn.createStatement().executeUpdate("Create table bug4385 (i int not null primary key, c int generated always as identity)"); 227 conn.commit(); 228 229 PreparedStatement ps = conn.prepareStatement("insert into bug4385(i) values(?)", Statement.RETURN_GENERATED_KEYS); 230 231 ps.setInt(1, 199); 232 ps.executeUpdate(); 233 234 rs = ps.getGeneratedKeys(); 235 int count = 0; 236 while (rs.next()) { 237 rs.getInt(1); 238 count++; 239 } 240 rs.close(); 241 if (count != 1) 242 System.out.println("FAIL returned more than one row for generated keys"); 243 244 ps.setInt(1, 299); 245 ps.executeUpdate(); 246 rs = ps.getGeneratedKeys(); 247 count = 0; 248 while (rs.next()) { 249 rs.getInt(1); 250 count++; 251 } 252 if (count != 1) 253 System.out.println("FAIL returned more than one row for generated keys on re-execution"); 254 rs.close(); 255 ps.close(); 256 conn.rollback(); 257 258 conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT); 260 261 System.out.println("Isolation level change test over"); 262 conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); 263 } 264 265 272 private static void testDropTable(Connection conn) throws SQLException { 273 conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT); 274 final String dropTable = "DROP TABLE T1"; 275 Statement stmt1 = conn.createStatement(); 276 Statement stmt2 = conn.createStatement(); 277 ResultSet rs = stmt1.executeQuery("SELECT * FROM T1"); 278 rs.next(); 279 280 boolean ok = false; 282 try { 283 stmt2.executeUpdate(dropTable); 284 } catch (SQLException sqle) { 285 ok = true; 286 } 287 if (!ok) { 288 System.out.println("FAIL: Expected DROP TABLE to fail " + 289 "because of open cursor."); 290 } 291 292 conn.commit(); 293 294 ok = false; 296 try { 297 stmt2.executeUpdate(dropTable); 298 } catch (SQLException sqle) { 299 ok = true; 300 } 301 if (!ok) { 302 System.out.println("FAIL: Expected DROP TABLE to fail " + 303 "because of held cursor."); 304 } 305 306 rs.close(); 307 308 stmt2.executeUpdate(dropTable); 310 stmt1.close(); 311 stmt2.close(); 312 conn.rollback(); 313 } 314 315 private static void testHoldability(Connection conn,int holdability) throws SQLException { 318 319 conn.setHoldability(holdability); 320 321 switch(holdability){ 322 case ResultSet.HOLD_CURSORS_OVER_COMMIT: 323 System.out.println("\ntestHoldability with HOLD_CURSORS_OVER_COMMIT\n"); 324 break; 325 case ResultSet.CLOSE_CURSORS_AT_COMMIT: 326 System.out.println("\ntestHoldability with CLOSE_CURSORS_AT_COMMIT\n"); 327 break; 328 } 329 330 testStatements(conn); 331 testStatementsInProcedure(conn); 332 } 333 334 private static void testStatements(Connection conn) throws SQLException { 336 System.out.println("\ntestStatements()\n"); 337 338 Statement st1 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE , 340 ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT); 341 ResultSet rs1 = st1.executeQuery("select * from testtable1"); 342 checkResultSet(rs1, "before"); 343 conn.commit(); 344 checkResultSet(rs1, "after"); 345 st1.close(); 346 347 Statement st2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE , 349 ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT); 350 ResultSet rs2 = st2.executeQuery("select * from testtable2"); 351 checkResultSet(rs2, "before"); 352 conn.commit(); 353 checkResultSet(rs2, "after"); 354 st2.close(); 355 } 356 357 private static void testStatementsInProcedure(Connection conn) throws SQLException { 359 System.out.println("\ntestStatementsInProcedure()\n"); 360 361 CallableStatement cs1 = conn.prepareCall("call MYPROC()"); 362 cs1.execute(); 363 do{ 364 checkResultSet(cs1.getResultSet(), "before"); 365 }while(cs1.getMoreResults()); 366 367 CallableStatement cs2 = conn.prepareCall("call MYPROC()"); 368 cs2.execute(); 369 conn.commit(); 370 do{ 371 checkResultSet(cs2.getResultSet(),"after"); 372 }while(cs2.getMoreResults()); 373 374 cs1.close(); 375 cs2.close(); 376 } 377 378 private static void testCloseCursor(Connection conn) 383 throws SQLException 384 { 385 System.out.println("\ntestCloseCursor()\n"); 386 final String [] tables = { "T1", "T2" }; 391 Statement stmt1 = conn.createStatement(); 392 Statement stmt2 = conn.createStatement(); 393 for (int i = 0; i < tables.length; i++) { 394 String table = tables[i]; 395 ResultSet rs = stmt1.executeQuery("SELECT * FROM " + table); 396 rs.next(); 397 rs.close(); 398 stmt2.executeUpdate("DROP TABLE " + table); 402 } 403 stmt1.close(); 404 stmt2.close(); 405 conn.rollback(); 406 } 407 408 private static void checkResultSet(ResultSet rs, String beforeOrAfter) throws SQLException { 410 System.out.println("checkResultSet "+ beforeOrAfter + " commit"); 411 try{ 412 if(rs != null){ 413 rs.next(); 414 System.out.println(rs.getString(1) + ", " + rs.getString(2)); 415 } 416 else{ 417 System.out.println("EXPECTED:ResultSet is null"); 418 } 419 } catch(SQLException se){ 420 System.out.println("EXPECTED EXCEPTION:"+se.getMessage()); 421 } 422 } 423 424 public static void testProc(ResultSet [] rs1, ResultSet [] rs2) throws Exception 426 { 427 Connection conn = DriverManager.getConnection("jdbc:default:connection"); 428 429 Statement st1 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE , 431 ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT); 432 rs1[0] = st1.executeQuery("select * from testtable1"); 433 434 Statement st2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE , 436 ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT); 437 rs2[0] = st2.executeQuery("select * from testtable2"); 438 439 } 440 } 441 | Popular Tags |