|
1
21
22 package org.apache.derbyTesting.functionTests.tests.jdbcapi;
23
24 import java.sql.CallableStatement  ;
25 import java.sql.Connection ;
26 import java.sql.DriverManager ;
27 import java.sql.ParameterMetaData ;
28 import java.sql.PreparedStatement ;
29 import java.sql.ResultSet ;
30 import java.sql.SQLException ;
31 import java.sql.Statement ;
32 import java.util.Properties ;
33
34 import javax.sql.ConnectionPoolDataSource ;
35 import javax.sql.PooledConnection ;
36 import javax.sql.XAConnection ;
37 import javax.sql.XADataSource ;
38 import javax.transaction.xa.XAException ;
39 import javax.transaction.xa.XAResource ;
40 import javax.transaction.xa.Xid ;
41
42 import org.apache.derby.tools.JDBCDisplayUtil;
43 import org.apache.derbyTesting.functionTests.util.SecurityCheck;
44 import org.apache.derbyTesting.functionTests.util.TestUtil;
45
46
47
53 public class checkDataSource30 extends checkDataSource
54 {
55
56 private static boolean stmtHoldabilityError =
61 TestUtil.isDerbyNetClientFramework();
62
63 public static void main(String [] args) throws Exception {
64
65 checkDataSource30 tester = new checkDataSource30();
66 tester.runTest(args);
67 tester.checkXAHoldability();
68
69 testDerby1144();
70 SecurityCheck.report();
73
74
75 System.out.println("Completed checkDataSource30");
76
77 }
78
79
80 public checkDataSource30() {
81 }
82
83 public void checkConnection(String dsName, Connection conn) throws SQLException {
84
85 System.out.println("Running JDBC 3.0 connection checks on " + dsName);
86
87
88 System.out.println(" holdability " + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
89
90 try {
92 conn.releaseSavepoint(conn.setSavepoint());
93 System.out.println("JDBC 3.0 savepoint OK");
94 } catch (SQLException sqle) {
95 System.out.println("JDBC 3.0 savepoint SQL Exception: (" +
98 sqle.getSQLState() + ") " + sqle.getMessage());
99 }
100
101
102 super.checkConnection(dsName, conn);
103 }
104
105 protected void checkConnectionPreClose(String dsName, Connection conn) throws SQLException {
106
107 conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
108
109 super.checkConnectionPreClose(dsName, conn);
110
111 }
112 protected void setHoldability(Connection conn, boolean hold) throws SQLException {
113
114 conn.setHoldability(hold ? ResultSet.HOLD_CURSORS_OVER_COMMIT : ResultSet.CLOSE_CURSORS_AT_COMMIT);
115 }
116 protected void getHoldability(Connection conn) throws SQLException {
117
118 System.out.println(" holdability " + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
119 }
120
121 protected Statement internalCreateFloatStatementForStateChecking(Connection conn) throws SQLException {
122 return conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
123 }
124 protected PreparedStatement internalCreateFloatStatementForStateChecking(Connection conn, String sql) throws SQLException {
125 return conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
126 }
127 protected CallableStatement internalCreateFloatCallForStateChecking(Connection conn, String sql) throws SQLException {
128 return conn.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
129 }
130
131
132 protected void showStatementState(String when, Statement s) throws SQLException {
133 super.showStatementState(when, s);
134 System.out.println(" getResultSetHoldability() " + rsHoldability(s.getResultSetHoldability()));
135 if (s instanceof PreparedStatement ) {
136 PreparedStatement ps = (PreparedStatement ) s;
137 ParameterMetaData psmd = ps.getParameterMetaData();
138 System.out.println(" Parameter Count " + psmd.getParameterCount());
139 for (int i = 1; i <= psmd.getParameterCount(); i++) {
140 System.out.println(" " + i + " type " + psmd.getParameterType(i));
141 }
142 }
143 }
144
145 protected void showXAException(String tag, XAException xae) {
146
147 super.showXAException(tag, xae);
148 Throwable t = xae.getCause();
149 if (t instanceof SQLException )
150 JDBCDisplayUtil.ShowSQLException(System.out, (SQLException ) t);
151 }
152
153
154 static String rsHoldability(int type) {
155 switch (type) {
156 case ResultSet.HOLD_CURSORS_OVER_COMMIT :
157 return "HOLD_CURSORS_OVER_COMMIT ";
158 case ResultSet.CLOSE_CURSORS_AT_COMMIT :
159 return "CLOSE_CURSORS_AT_COMMIT ";
160 default:
161 return "?? HOLDABILITY UNKNOWN ??";
162
163 }
164 }
165
166 private void checkXAHoldability() {
167 System.out.println("START XA HOLDABILITY TEST");
168 try {
169 Properties attrs = new Properties ();
170 attrs.setProperty("databaseName", "wombat");
171 attrs.setProperty("connectionAttributes", "create=true");
172 XADataSource dscsx = TestUtil.getXADataSource(attrs);
173
174 XAConnection xac = dscsx.getXAConnection("fred", "wilma");
175 XAResource xr = xac.getXAResource();
176 Xid xid = getXid(25, (byte) 21, (byte) 01);
177 Connection conn1 = xac.getConnection();
178 System.out.println("By default, autocommit is " + conn1.getAutoCommit() + " for a connection");
179 System.out.println("Default holdability for a connection is HOLD_CURSORS_OVER_COMMIT");
180 System.out.println("CONNECTION(not in xa transaction yet) HOLDABILITY " + (conn1.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
181 xr.start(xid, XAResource.TMNOFLAGS);
183 System.out.println("Notice that autocommit now is " + conn1.getAutoCommit() + " for connection because it is part of the global transaction");
184 System.out.println("Notice that connection's holdability at this point is CLOSE_CURSORS_AT_COMMIT because it is part of the global transaction");
185 System.out.println("CONNECTION(in xa transaction) HOLDABILITY " + (conn1.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
186 xr.end(xid, XAResource.TMSUCCESS);
187 conn1.commit();
188 conn1.close();
189
190 xid = getXid(27, (byte) 21, (byte) 01);
191 xr.start(xid, XAResource.TMNOFLAGS);
192 conn1 = xac.getConnection();
193 System.out.println("CONNECTION(in xa transaction) HOLDABILITY " + (conn1.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
194 System.out.println("Autocommit on Connection inside global transaction has been set correctly to " + conn1.getAutoCommit());
195 xr.end(xid, XAResource.TMSUCCESS);
196 conn1.rollback();
197
198 Connection conn = xac.getConnection();
199 conn.setAutoCommit(false);
200 conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
201 System.out.println("CONNECTION(non-xa) HOLDABILITY " + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
202
203 Statement s = conn.createStatement();
204 System.out.println("STATEMENT HOLDABILITY " + (s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
205
206
207 s.executeUpdate("create table hold_30 (id int not null primary key, b char(30))");
208 s.executeUpdate("insert into hold_30 values (1,'init2'), (2, 'init3'), (3,'init3')");
209 s.executeUpdate("insert into hold_30 values (4,'init4'), (5, 'init5'), (6,'init6')");
210 s.executeUpdate("insert into hold_30 values (7,'init7'), (8, 'init8'), (9,'init9')");
211
212 System.out.println("STATEMENT HOLDABILITY " + (s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
213
214 Statement sh = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
215 PreparedStatement psh = conn.prepareStatement("select id from hold_30 for update",
216 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
217 CallableStatement csh = conn.prepareCall("select id from hold_30 for update",
218 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
219
220 System.out.println("STATEMENT HOLDABILITY " + (sh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
221 System.out.println("PREPARED STATEMENT HOLDABILITY " + (psh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
222 System.out.println("CALLABLE STATEMENT HOLDABILITY " + (csh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
223
224 ResultSet rsh = sh.executeQuery("select id from hold_30 for update");
225 rsh.next(); System.out.println("H@1 id " + rsh.getInt(1));
226 rsh.next(); System.out.println("H@2 id " + rsh.getInt(1));
227 conn.commit();
228 rsh.next(); System.out.println("H@3 id " + rsh.getInt(1));
229 conn.commit();
230
231
232 xid = getXid(23, (byte) 21, (byte) 01);
233 xr.start(xid, XAResource.TMNOFLAGS);
234 Statement stmtInsideGlobalTransaction = conn.createStatement();
235 PreparedStatement prepstmtInsideGlobalTransaction = conn.prepareStatement("select id from hold_30");
236 CallableStatement callablestmtInsideGlobalTransaction = conn.prepareCall("select id from hold_30");
237
238 System.out.println("CONNECTION(xa) HOLDABILITY " + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
239 System.out.println("STATEMENT(this one was created with holdability false, outside the global transaction. Check it's holdability inside global transaction) HOLDABILITY " + (s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
240 System.out.println("STATEMENT(this one was created with holdability true, outside the global transaction. Check it's holdability inside global transaction) HOLDABILITY " + (sh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
241 System.out.println("STATEMENT(this one was created with default holdability inside this global transaction. Check it's holdability) HOLDABILITY " + (stmtInsideGlobalTransaction.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
242 System.out.println("PREPAREDSTATEMENT(this one was created with default holdability inside this global transaction. Check it's holdability) HOLDABILITY " + (prepstmtInsideGlobalTransaction.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
243 System.out.println("CALLABLESTATEMENT(this one was created with default holdability inside this global transaction. Check it's holdability) HOLDABILITY " + (callablestmtInsideGlobalTransaction.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
244
245 ResultSet rsx = s.executeQuery("select id from hold_30 for update");
246
247 rsx.next(); System.out.println("X@1 id " + rsx.getInt(1));
248 rsx.next(); System.out.println("X@2 id " + rsx.getInt(1));
249 xr.end(xid, XAResource.TMSUCCESS);
250
251 try {
254 rsx.next(); System.out.println("FAIL - rsx's connection not active id " + rsx.getInt(1));
255 } catch (SQLException sqle) {
256 System.out.println("Expected SQLException " + sqle.getMessage());
257 }
258
259 try {
261 rsh.next(); System.out.println("FAIL - rsh's should be closed " + rsx.getInt(1));
262 } catch (SQLException sqle) {
263 System.out.println("Expected SQLException " + sqle.getMessage());
264 }
265
266 System.out.println("resume XA transaction and keep using rs");
267 xr.start(xid, XAResource.TMJOIN);
268 Statement stmtAfterGlobalTransactionResume = conn.createStatement();
269 PreparedStatement prepstmtAfterGlobalTransactionResume = conn.prepareStatement("select id from hold_30");
270 CallableStatement callablestmtAfterGlobalTransactionResume = conn.prepareCall("select id from hold_30");
271
272 System.out.println("Check holdability of various jdbc objects after resuming XA transaction");
273 System.out.println("CONNECTION(xa) HOLDABILITY " + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
274 System.out.println("STATEMENT(this one was created with holdability false, outside the global transaction. Check it's holdability inside global transaction) HOLDABILITY " + (s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
275 System.out.println("STATEMENT(this one was created with holdability true, outside the global transaction. Check it's holdability inside global transaction) HOLDABILITY " + (sh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
276 System.out.println("STATEMENT(this one was created with default holdability inside the global transaction when it was first started. Check it's holdability) HOLDABILITY " + (stmtInsideGlobalTransaction.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
277 System.out.println("PREPAREDSTATEMENT(this one was created with default holdability inside the global transaction when it was first started. Check it's holdability) HOLDABILITY " + (prepstmtInsideGlobalTransaction.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
278 System.out.println("CALLABLESTATEMENT(this one was created with default holdability inside the global transaction when it was first started. Check it's holdability) HOLDABILITY " + (callablestmtInsideGlobalTransaction.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
279 System.out.println("STATEMENT(this one was created with default holdability after the global transaction was resumed. Check it's holdability) HOLDABILITY " + (stmtAfterGlobalTransactionResume.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
280 System.out.println("PREPAREDSTATEMENT(this one was created with default holdability after the global transaction was resumed. Check it's holdability) HOLDABILITY " + (prepstmtAfterGlobalTransactionResume.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
281 System.out.println("CALLABLESTATEMENT(this one was created with default holdability after the global transaction was resumed. Check it's holdability) HOLDABILITY " + (callablestmtAfterGlobalTransactionResume.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
282 if (! stmtHoldabilityError)
284 {
285 rsx.next(); System.out.println("X@3 id " + rsx.getInt(1));
287 }
288 xr.end(xid, XAResource.TMSUCCESS);
289
290
291 if (xr.prepare(xid) != XAResource.XA_RDONLY)
292 xr.commit(xid, false);
293
294 try {
296 rsx.next(); System.out.println("FAIL - rsx's connection not active id (B)" + rsx.getInt(1));
297 } catch (SQLException sqle) {
298 System.out.println("Expected SQLException " + sqle.getMessage());
299 }
300 try {
301 rsh.next(); System.out.println("FAIL - rsh's should be closed (B) " + rsx.getInt(1));
302 } catch (SQLException sqle) {
303 System.out.println("Expected SQLException " + sqle.getMessage());
304 }
305
306 System.out.println("Set connection to hold ");
307 conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
308 System.out.println("CONNECTION(held) HOLDABILITY " + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
309
310 xid = getXid(24, (byte) 21, (byte) 01);
311 xr.start(xid, XAResource.TMNOFLAGS);
312 System.out.println("CONNECTION(xa) HOLDABILITY " + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
313 try {
314 conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
315 System.out.println("FAIL allowed to set hold mode in xa transaction");
316 } catch (SQLException sqle) {
317 System.out.println("Expected SQLException(setHoldability) " + sqle.getMessage());
318 }
319
320
325 Statement shxa = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
326 ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
327 System.out.println("HOLDABLE Statement in global xact " +
328 (s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT) +
329 " connection warning " + conn.getWarnings().getMessage());
330 shxa.close();
331
332
333 shxa = conn.prepareStatement("select id from hold_30",
334 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
335 System.out.println("HOLDABLE PreparedStatement in global xact " +
336 (s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT) +
337 " connection warning " + conn.getWarnings().getMessage());
338 shxa.close();
339
340 shxa = conn.prepareCall("CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE()",
341 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
342 System.out.println("HOLDABLE CallableStatement in global xact " +
343 (s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT) +
344 " connection warning " + conn.getWarnings().getMessage());
345 shxa.close();
346
347
348 if(! stmtHoldabilityError) {
352 System.out.println("STATEMENT HOLDABILITY " + (sh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
353 sh.executeQuery("select id from hold_30").close();
354 sh.execute("select id from hold_30");
355 sh.getResultSet().close();
356
357 System.out.println("PREPARED STATEMENT HOLDABILITY " + (psh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
358 psh.executeQuery().close();
359 psh.execute();
360 psh.getResultSet().close();
361
362 System.out.println("CALLABLE STATEMENT HOLDABILITY " + (csh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
363 csh.executeQuery().close();
364 csh.execute();
365 csh.getResultSet().close();
366 }
367
368 sh.executeUpdate("insert into hold_30 values(10, 'init10')");
370
371 xr.end(xid, XAResource.TMSUCCESS);
372
373 System.out.println("CONNECTION(held) HOLDABILITY " + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
374
375 conn.close();
376 System.out.println("PASS XA HOLDABILITY TEST");
377
378 } catch (XAException xae) {
379 System.out.println("XAException error code " + xae.errorCode);
380 xae.printStackTrace(System.out);
381 Throwable t = xae.getCause();
382 if (t instanceof SQLException )
383 JDBCDisplayUtil.ShowSQLException(System.out, (SQLException ) t);
384
385 } catch (SQLException sqle) {
386 JDBCDisplayUtil.ShowSQLException(System.out, sqle);
387 } catch (Throwable t) {
388 t.printStackTrace(System.out);
389 }
390 System.out.flush();
391 }
392
393
397 public static void checkNesConn30(String dsName) throws SQLException {
398 Connection conn = DriverManager.getConnection("jdbc:default:connection");
399 new checkDataSource30().checkConnection(dsName, conn);
400 }
401
402
403
407 protected String getNestedMethodName()
408 {
409 return "checkDataSource30.checkNesConn30";
410 }
411
412
413
423 public static void testDerby1144() throws SQLException
424 {
425 Connection conn = null;
426 PooledConnection pc1 = null;
427 Properties p = new Properties ();
428
429 p.put("databaseName","sample");
430 p.put("connectionAttributes","create=true");
431 p.put("user","APP");
432 p.put("password","pw");
433
434 ConnectionPoolDataSource ds = TestUtil.getConnectionPoolDataSource(p);
436 pc1 = ds.getPooledConnection();
437 testPooledConnHoldability("PooledConnection", pc1);
438 pc1.close();
439
440 pc1 = ds.getPooledConnection();
442 testPooledConnAutoCommit("PooledConnection", pc1);
443 pc1.close();
444
445 ds = TestUtil.getConnectionPoolDataSource(p);
447 pc1 = ds.getPooledConnection();
448 testPooledConnIso("PooledConnection" , pc1);
449 pc1.close();
450
451 XADataSource xds = TestUtil.getXADataSource(p);
453 XAConnection xpc1 = xds.getXAConnection();
454 testPooledConnIso("XAConnection", xpc1);
455 xpc1.close();
456 }
457
458
459
465 private static void testPooledConnAutoCommit(String desc, PooledConnection pc1) throws SQLException {
466 System.out.println("\n** Test autoCommit state for: " + desc + "**");
467 Connection conn = pc1.getConnection();
468 conn.setAutoCommit(true);
469 conn = pc1.getConnection();
471 boolean autocommit = conn.getAutoCommit();
472 if (autocommit != true)
473 {
474 new Exception ("FAIL: autoCommit not reset on getConnection").printStackTrace(System.out);
475 }
476 else
477 {
478 System.out.println("PASS: autoCommit reset on getConnection");
479 }
480 conn.close();
481 }
482
483
484
490 private static void testPooledConnHoldability(String desc, PooledConnection pc1)
491 throws SQLException {
492 System.out.println("\n**Test holdability state for: " + desc + " **");
493 Connection conn = pc1.getConnection();
494 conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
495 conn = pc1.getConnection();
498 checkConnHoldability(conn, ResultSet.HOLD_CURSORS_OVER_COMMIT);
499 conn.close();
500 }
501
502
503
504
510 private static void checkConnHoldability(Connection conn,
511 int expectedHoldability) throws SQLException {
512 int holdability = conn.getHoldability();
513 if (holdability != expectedHoldability)
514 {
515 new Exception ("FAIL: Holdability:" + translateHoldability(holdability) +
516 " does not match expected holdability:" +
517 translateHoldability(expectedHoldability)).printStackTrace(System.out);
518 }
519 else
520 {
521 System.out.println("PASS: Holdability matches expected holdability:" +
522 translateHoldability(expectedHoldability));
523 }
524 }
525
526
527
533 private static void testPooledConnIso(String pooledConnType, PooledConnection pc)
534 throws SQLException {
535 Connection conn = pc.getConnection();
536
537 setupDerby1144Table(conn);
538 System.out.println("*** Test isolation level reset on " + pooledConnType+ ".getConnection()***");;
539 System.out.println("\nsetTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED");
540 conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
541 checkIsoLocks(conn,Connection.TRANSACTION_READ_UNCOMMITTED);
542
543 conn.close();
544 System.out.println("\nGet a new connection with " + pooledConnType+ ".getConnection()");
545 System.out.println("Isolation level should be reset to READ_COMMITTED");
546 Connection newconn = pc.getConnection();
547 checkIsoLocks(newconn,Connection.TRANSACTION_READ_COMMITTED);
548
549
550 }
551
552
553
558 private static void setupDerby1144Table(Connection conn) throws SQLException
559 {
560 Statement stmt = conn.createStatement();
561 try {
562 stmt.executeUpdate("DROP TABLE TAB1");
563 }
564 catch (SQLException e)
565 {
566 }
568 stmt.executeUpdate("CREATE TABLE TAB1(COL1 INT NOT NULL)");
569 stmt.executeUpdate("INSERT INTO TAB1 VALUES(1)");
570 stmt.executeUpdate("INSERT INTO TAB1 VALUES(2)");
571
572 System.out.println("done creating table");
573 conn.commit ();
574 }
575
576
577
578
579
587 private static void checkIsoLocks(Connection conn, int expectedIsoLevel)
588 {
589 try {
590 int conniso = conn.getTransactionIsolation();
591 if (conniso != expectedIsoLevel)
592 {
593 new Exception ("FAIL: Connection isolation level " +
594 translateIso(conniso) +
595 " does not match expected level " +
596 translateIso(expectedIsoLevel));
597 }
598
599 boolean selectTimedOut = selectTimesoutDuringUpdate(conn);
600 switch (conniso) {
601 case Connection.TRANSACTION_READ_UNCOMMITTED:
602 if (selectTimedOut)
603 new Exception ("FAIL: Unexpected lock timeout for READ_UNCOMMITTED").printStackTrace(System.out);
604 else
605 System.out.println("PASS: No lock timeout occurs for READ_UNCOMMITTED");
606 case Connection.TRANSACTION_READ_COMMITTED:
607 if (selectTimedOut)
608 System.out.println("PASS: Expected lock timeout for READ_COMMITTED");
609 else
610 new Exception ("FAIL: Did not get lock timeout for READ_COMMITTED");
611 default:
612 new Exception ("No test support for isolation level" +
613 translateIso(conniso));
614 }
615 } catch (SQLException se) {
616 se.printStackTrace();
617 }
618 }
619
620
629 private static boolean selectTimesoutDuringUpdate(Connection conn) {
630 Connection updateConn = null;
631
632 try {
633
634 conn.setAutoCommit(false);
635 updateConn = TestUtil.getConnection("sample","create=true");
637 updateConn.setAutoCommit(false);
638
639
640 Statement upStmt = updateConn.createStatement();
642 upStmt.executeUpdate("update tab1 set col1 = 3");
643
644
646 Statement stmt = conn.createStatement();
647 ResultSet rs = stmt.executeQuery("Select * from tab1");
648 while (rs.next()){};
649 rs.close();
650
651 }
652 catch (SQLException e)
653 {
654 if (e.getSQLState().equals("40XL1"))
655 {
656 return true;
658 }
659
660 }
661 finally {
662
663 try {
664 conn.rollback();
665 updateConn.rollback();
666 }catch (SQLException se) {
667 se.printStackTrace();
668 }
669 }
670 return false;
671
672 }
673
674
680 public static String translateHoldability(int holdability)
681 {
682 switch (holdability)
683 {
684 case ResultSet.HOLD_CURSORS_OVER_COMMIT : return "HOLD_CURSORS_OVER_COMMIT";
685 case ResultSet.CLOSE_CURSORS_AT_COMMIT : return "CLOSE_CURSORS_AT_COMMIT";
686 }
687 return "UNKNOWN_HOLDABILTY";
688 }
689
690 }
691 |
Popular Tags |
Java API By Example, From Geeks To Geeks.