001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.checkDataSource30
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.jdbcapi;
023:
024: import java.sql.CallableStatement;
025: import java.sql.Connection;
026: import java.sql.DriverManager;
027: import java.sql.ParameterMetaData;
028: import java.sql.PreparedStatement;
029: import java.sql.ResultSet;
030: import java.sql.SQLException;
031: import java.sql.Statement;
032: import java.util.Properties;
033:
034: import javax.sql.ConnectionPoolDataSource;
035: import javax.sql.PooledConnection;
036: import javax.sql.XAConnection;
037: import javax.sql.XADataSource;
038: import javax.transaction.xa.XAException;
039: import javax.transaction.xa.XAResource;
040: import javax.transaction.xa.Xid;
041:
042: import org.apache.derby.tools.JDBCDisplayUtil;
043: import org.apache.derbyTesting.functionTests.util.SecurityCheck;
044: import org.apache.derbyTesting.functionTests.util.TestUtil;
045:
046: /**
047: * Extends checkDataSource to provide testing of JDBC 3.0 specific
048: * methods for the embedded DataSource implementations.
049: * @author djd
050: *
051: */
052: public class checkDataSource30 extends checkDataSource {
053:
054: // DERBY-1370 - Embedded and client driver differ in the holdability
055: // reported inside a global transaction for statements that were created
056: // with HOLD_CURSORS_OVER_COMMIT outside the transaction
057: // It looks like embedded behaviour is correct.
058: private static boolean stmtHoldabilityError = TestUtil
059: .isDerbyNetClientFramework();
060:
061: public static void main(String[] args) throws Exception {
062:
063: checkDataSource30 tester = new checkDataSource30();
064: tester.runTest(args);
065: tester.checkXAHoldability();
066:
067: testDerby1144();
068: // Print a report on System.out of the issues
069: // found with the security checks.
070: SecurityCheck.report();
071:
072: System.out.println("Completed checkDataSource30");
073:
074: }
075:
076: public checkDataSource30() {
077: }
078:
079: public void checkConnection(String dsName, Connection conn)
080: throws SQLException {
081:
082: System.out.println("Running JDBC 3.0 connection checks on "
083: + dsName);
084:
085: System.out
086: .println(" holdability "
087: + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
088:
089: // check it's a 3.0 connection object
090: try {
091: conn.releaseSavepoint(conn.setSavepoint());
092: System.out.println("JDBC 3.0 savepoint OK");
093: } catch (SQLException sqle) {
094: // we expect savepoints exceptions because either
095: // it's a global transaction, or it's in auto commit mode.
096: System.out.println("JDBC 3.0 savepoint SQL Exception: ("
097: + sqle.getSQLState() + ") " + sqle.getMessage());
098: }
099:
100: super .checkConnection(dsName, conn);
101: }
102:
103: protected void checkConnectionPreClose(String dsName,
104: Connection conn) throws SQLException {
105:
106: conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
107:
108: super .checkConnectionPreClose(dsName, conn);
109:
110: }
111:
112: protected void setHoldability(Connection conn, boolean hold)
113: throws SQLException {
114:
115: conn.setHoldability(hold ? ResultSet.HOLD_CURSORS_OVER_COMMIT
116: : ResultSet.CLOSE_CURSORS_AT_COMMIT);
117: }
118:
119: protected void getHoldability(Connection conn) throws SQLException {
120:
121: System.out
122: .println(" holdability "
123: + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
124: }
125:
126: protected Statement internalCreateFloatStatementForStateChecking(
127: Connection conn) throws SQLException {
128: return conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
129: ResultSet.CONCUR_READ_ONLY,
130: ResultSet.HOLD_CURSORS_OVER_COMMIT);
131: }
132:
133: protected PreparedStatement internalCreateFloatStatementForStateChecking(
134: Connection conn, String sql) throws SQLException {
135: return conn.prepareStatement(sql,
136: ResultSet.TYPE_SCROLL_INSENSITIVE,
137: ResultSet.CONCUR_READ_ONLY,
138: ResultSet.HOLD_CURSORS_OVER_COMMIT);
139: }
140:
141: protected CallableStatement internalCreateFloatCallForStateChecking(
142: Connection conn, String sql) throws SQLException {
143: return conn.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
144: ResultSet.CONCUR_READ_ONLY,
145: ResultSet.HOLD_CURSORS_OVER_COMMIT);
146: }
147:
148: protected void showStatementState(String when, Statement s)
149: throws SQLException {
150: super .showStatementState(when, s);
151: System.out.println(" getResultSetHoldability() "
152: + rsHoldability(s.getResultSetHoldability()));
153: if (s instanceof PreparedStatement) {
154: PreparedStatement ps = (PreparedStatement) s;
155: ParameterMetaData psmd = ps.getParameterMetaData();
156: System.out.println(" Parameter Count "
157: + psmd.getParameterCount());
158: for (int i = 1; i <= psmd.getParameterCount(); i++) {
159: System.out.println(" " + i + " type "
160: + psmd.getParameterType(i));
161: }
162: }
163: }
164:
165: protected void showXAException(String tag, XAException xae) {
166:
167: super .showXAException(tag, xae);
168: Throwable t = xae.getCause();
169: if (t instanceof SQLException)
170: JDBCDisplayUtil.ShowSQLException(System.out,
171: (SQLException) t);
172: }
173:
174: static String rsHoldability(int type) {
175: switch (type) {
176: case ResultSet.HOLD_CURSORS_OVER_COMMIT:
177: return "HOLD_CURSORS_OVER_COMMIT ";
178: case ResultSet.CLOSE_CURSORS_AT_COMMIT:
179: return "CLOSE_CURSORS_AT_COMMIT ";
180: default:
181: return "?? HOLDABILITY UNKNOWN ??";
182:
183: }
184: }
185:
186: private void checkXAHoldability() {
187: System.out.println("START XA HOLDABILITY TEST");
188: try {
189: Properties attrs = new Properties();
190: attrs.setProperty("databaseName", "wombat");
191: attrs.setProperty("connectionAttributes", "create=true");
192: XADataSource dscsx = TestUtil.getXADataSource(attrs);
193:
194: XAConnection xac = dscsx.getXAConnection("fred", "wilma");
195: XAResource xr = xac.getXAResource();
196: Xid xid = getXid(25, (byte) 21, (byte) 01);
197: Connection conn1 = xac.getConnection();
198: System.out.println("By default, autocommit is "
199: + conn1.getAutoCommit() + " for a connection");
200: System.out
201: .println("Default holdability for a connection is HOLD_CURSORS_OVER_COMMIT");
202: System.out
203: .println("CONNECTION(not in xa transaction yet) HOLDABILITY "
204: + (conn1.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
205: //start a global transaction and default holdability and autocommit will be switched to match Derby XA restrictions
206: xr.start(xid, XAResource.TMNOFLAGS);
207: System.out
208: .println("Notice that autocommit now is "
209: + conn1.getAutoCommit()
210: + " for connection because it is part of the global transaction");
211: System.out
212: .println("Notice that connection's holdability at this point is CLOSE_CURSORS_AT_COMMIT because it is part of the global transaction");
213: System.out
214: .println("CONNECTION(in xa transaction) HOLDABILITY "
215: + (conn1.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
216: xr.end(xid, XAResource.TMSUCCESS);
217: conn1.commit();
218: conn1.close();
219:
220: xid = getXid(27, (byte) 21, (byte) 01);
221: xr.start(xid, XAResource.TMNOFLAGS);
222: conn1 = xac.getConnection();
223: System.out
224: .println("CONNECTION(in xa transaction) HOLDABILITY "
225: + (conn1.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
226: System.out
227: .println("Autocommit on Connection inside global transaction has been set correctly to "
228: + conn1.getAutoCommit());
229: xr.end(xid, XAResource.TMSUCCESS);
230: conn1.rollback();
231:
232: Connection conn = xac.getConnection();
233: conn.setAutoCommit(false);
234: conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
235: System.out
236: .println("CONNECTION(non-xa) HOLDABILITY "
237: + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
238:
239: Statement s = conn.createStatement();
240: System.out
241: .println("STATEMENT HOLDABILITY "
242: + (s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
243:
244: s
245: .executeUpdate("create table hold_30 (id int not null primary key, b char(30))");
246: s
247: .executeUpdate("insert into hold_30 values (1,'init2'), (2, 'init3'), (3,'init3')");
248: s
249: .executeUpdate("insert into hold_30 values (4,'init4'), (5, 'init5'), (6,'init6')");
250: s
251: .executeUpdate("insert into hold_30 values (7,'init7'), (8, 'init8'), (9,'init9')");
252:
253: System.out
254: .println("STATEMENT HOLDABILITY "
255: + (s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
256:
257: Statement sh = conn.createStatement(
258: ResultSet.TYPE_FORWARD_ONLY,
259: ResultSet.CONCUR_READ_ONLY,
260: ResultSet.HOLD_CURSORS_OVER_COMMIT);
261: PreparedStatement psh = conn.prepareStatement(
262: "select id from hold_30 for update",
263: ResultSet.TYPE_FORWARD_ONLY,
264: ResultSet.CONCUR_READ_ONLY,
265: ResultSet.HOLD_CURSORS_OVER_COMMIT);
266: CallableStatement csh = conn.prepareCall(
267: "select id from hold_30 for update",
268: ResultSet.TYPE_FORWARD_ONLY,
269: ResultSet.CONCUR_READ_ONLY,
270: ResultSet.HOLD_CURSORS_OVER_COMMIT);
271:
272: System.out
273: .println("STATEMENT HOLDABILITY "
274: + (sh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
275: System.out
276: .println("PREPARED STATEMENT HOLDABILITY "
277: + (psh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
278: System.out
279: .println("CALLABLE STATEMENT HOLDABILITY "
280: + (csh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
281:
282: ResultSet rsh = sh
283: .executeQuery("select id from hold_30 for update");
284: rsh.next();
285: System.out.println("H@1 id " + rsh.getInt(1));
286: rsh.next();
287: System.out.println("H@2 id " + rsh.getInt(1));
288: conn.commit();
289: rsh.next();
290: System.out.println("H@3 id " + rsh.getInt(1));
291: conn.commit();
292:
293: xid = getXid(23, (byte) 21, (byte) 01);
294: xr.start(xid, XAResource.TMNOFLAGS);
295: Statement stmtInsideGlobalTransaction = conn
296: .createStatement();
297: PreparedStatement prepstmtInsideGlobalTransaction = conn
298: .prepareStatement("select id from hold_30");
299: CallableStatement callablestmtInsideGlobalTransaction = conn
300: .prepareCall("select id from hold_30");
301:
302: System.out
303: .println("CONNECTION(xa) HOLDABILITY "
304: + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
305: System.out
306: .println("STATEMENT(this one was created with holdability false, outside the global transaction. Check it's holdability inside global transaction) HOLDABILITY "
307: + (s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
308: System.out
309: .println("STATEMENT(this one was created with holdability true, outside the global transaction. Check it's holdability inside global transaction) HOLDABILITY "
310: + (sh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
311: System.out
312: .println("STATEMENT(this one was created with default holdability inside this global transaction. Check it's holdability) HOLDABILITY "
313: + (stmtInsideGlobalTransaction
314: .getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
315: System.out
316: .println("PREPAREDSTATEMENT(this one was created with default holdability inside this global transaction. Check it's holdability) HOLDABILITY "
317: + (prepstmtInsideGlobalTransaction
318: .getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
319: System.out
320: .println("CALLABLESTATEMENT(this one was created with default holdability inside this global transaction. Check it's holdability) HOLDABILITY "
321: + (callablestmtInsideGlobalTransaction
322: .getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
323:
324: ResultSet rsx = s
325: .executeQuery("select id from hold_30 for update");
326:
327: rsx.next();
328: System.out.println("X@1 id " + rsx.getInt(1));
329: rsx.next();
330: System.out.println("X@2 id " + rsx.getInt(1));
331: xr.end(xid, XAResource.TMSUCCESS);
332:
333: // result set should not be useable, since it is part of a detached
334: // XAConnection
335: try {
336: rsx.next();
337: System.out
338: .println("FAIL - rsx's connection not active id "
339: + rsx.getInt(1));
340: } catch (SQLException sqle) {
341: System.out.println("Expected SQLException "
342: + sqle.getMessage());
343: }
344:
345: // result set should not be useable, it should have been closed by the xa start.
346: try {
347: rsh.next();
348: System.out.println("FAIL - rsh's should be closed "
349: + rsx.getInt(1));
350: } catch (SQLException sqle) {
351: System.out.println("Expected SQLException "
352: + sqle.getMessage());
353: }
354:
355: System.out
356: .println("resume XA transaction and keep using rs");
357: xr.start(xid, XAResource.TMJOIN);
358: Statement stmtAfterGlobalTransactionResume = conn
359: .createStatement();
360: PreparedStatement prepstmtAfterGlobalTransactionResume = conn
361: .prepareStatement("select id from hold_30");
362: CallableStatement callablestmtAfterGlobalTransactionResume = conn
363: .prepareCall("select id from hold_30");
364:
365: System.out
366: .println("Check holdability of various jdbc objects after resuming XA transaction");
367: System.out
368: .println("CONNECTION(xa) HOLDABILITY "
369: + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
370: System.out
371: .println("STATEMENT(this one was created with holdability false, outside the global transaction. Check it's holdability inside global transaction) HOLDABILITY "
372: + (s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
373: System.out
374: .println("STATEMENT(this one was created with holdability true, outside the global transaction. Check it's holdability inside global transaction) HOLDABILITY "
375: + (sh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
376: System.out
377: .println("STATEMENT(this one was created with default holdability inside the global transaction when it was first started. Check it's holdability) HOLDABILITY "
378: + (stmtInsideGlobalTransaction
379: .getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
380: System.out
381: .println("PREPAREDSTATEMENT(this one was created with default holdability inside the global transaction when it was first started. Check it's holdability) HOLDABILITY "
382: + (prepstmtInsideGlobalTransaction
383: .getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
384: System.out
385: .println("CALLABLESTATEMENT(this one was created with default holdability inside the global transaction when it was first started. Check it's holdability) HOLDABILITY "
386: + (callablestmtInsideGlobalTransaction
387: .getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
388: System.out
389: .println("STATEMENT(this one was created with default holdability after the global transaction was resumed. Check it's holdability) HOLDABILITY "
390: + (stmtAfterGlobalTransactionResume
391: .getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
392: System.out
393: .println("PREPAREDSTATEMENT(this one was created with default holdability after the global transaction was resumed. Check it's holdability) HOLDABILITY "
394: + (prepstmtAfterGlobalTransactionResume
395: .getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
396: System.out
397: .println("CALLABLESTATEMENT(this one was created with default holdability after the global transaction was resumed. Check it's holdability) HOLDABILITY "
398: + (callablestmtAfterGlobalTransactionResume
399: .getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
400: // DERBY-1370
401: if (!stmtHoldabilityError) {
402: // Network XA BUG gives result set closed
403: rsx.next();
404: System.out.println("X@3 id " + rsx.getInt(1));
405: }
406: xr.end(xid, XAResource.TMSUCCESS);
407:
408: if (xr.prepare(xid) != XAResource.XA_RDONLY)
409: xr.commit(xid, false);
410:
411: // try again once the xa transaction has been committed.
412: try {
413: rsx.next();
414: System.out
415: .println("FAIL - rsx's connection not active id (B)"
416: + rsx.getInt(1));
417: } catch (SQLException sqle) {
418: System.out.println("Expected SQLException "
419: + sqle.getMessage());
420: }
421: try {
422: rsh.next();
423: System.out.println("FAIL - rsh's should be closed (B) "
424: + rsx.getInt(1));
425: } catch (SQLException sqle) {
426: System.out.println("Expected SQLException "
427: + sqle.getMessage());
428: }
429:
430: System.out.println("Set connection to hold ");
431: conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
432: System.out
433: .println("CONNECTION(held) HOLDABILITY "
434: + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
435:
436: xid = getXid(24, (byte) 21, (byte) 01);
437: xr.start(xid, XAResource.TMNOFLAGS);
438: System.out
439: .println("CONNECTION(xa) HOLDABILITY "
440: + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
441: try {
442: conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
443: System.out
444: .println("FAIL allowed to set hold mode in xa transaction");
445: } catch (SQLException sqle) {
446: System.out
447: .println("Expected SQLException(setHoldability) "
448: + sqle.getMessage());
449: }
450:
451: // JDBC 4.0 (proposed final draft) section 16.1.3.1 allows Statements to
452: // be created with a different holdability if the driver cannot support it.
453: // In this case the driver does not support holdability in a global transaction
454: // so a valid statement is returned with close cursors on commit,
455:
456: Statement shxa = conn.createStatement(
457: ResultSet.TYPE_FORWARD_ONLY,
458: ResultSet.CONCUR_READ_ONLY,
459: ResultSet.HOLD_CURSORS_OVER_COMMIT);
460: System.out
461: .println("HOLDABLE Statement in global xact "
462: + (s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT)
463: + " connection warning "
464: + conn.getWarnings().getMessage());
465: shxa.close();
466:
467: shxa = conn.prepareStatement("select id from hold_30",
468: ResultSet.TYPE_FORWARD_ONLY,
469: ResultSet.CONCUR_READ_ONLY,
470: ResultSet.HOLD_CURSORS_OVER_COMMIT);
471: System.out
472: .println("HOLDABLE PreparedStatement in global xact "
473: + (s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT)
474: + " connection warning "
475: + conn.getWarnings().getMessage());
476: shxa.close();
477:
478: shxa = conn.prepareCall(
479: "CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE()",
480: ResultSet.TYPE_FORWARD_ONLY,
481: ResultSet.CONCUR_READ_ONLY,
482: ResultSet.HOLD_CURSORS_OVER_COMMIT);
483: System.out
484: .println("HOLDABLE CallableStatement in global xact "
485: + (s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT)
486: + " connection warning "
487: + conn.getWarnings().getMessage());
488: shxa.close();
489:
490: // check we can use a holdable statement set up in local mode.
491: // holdability is downgraded, tested in XATest.java
492: // DERBY-1370
493: if (!stmtHoldabilityError) {
494: System.out
495: .println("STATEMENT HOLDABILITY "
496: + (sh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
497: sh.executeQuery("select id from hold_30").close();
498: sh.execute("select id from hold_30");
499: sh.getResultSet().close();
500:
501: System.out
502: .println("PREPARED STATEMENT HOLDABILITY "
503: + (psh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
504: psh.executeQuery().close();
505: psh.execute();
506: psh.getResultSet().close();
507:
508: System.out
509: .println("CALLABLE STATEMENT HOLDABILITY "
510: + (csh.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
511: csh.executeQuery().close();
512: csh.execute();
513: csh.getResultSet().close();
514: }
515:
516: // but an update works
517: sh
518: .executeUpdate("insert into hold_30 values(10, 'init10')");
519:
520: xr.end(xid, XAResource.TMSUCCESS);
521:
522: System.out
523: .println("CONNECTION(held) HOLDABILITY "
524: + (conn.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT));
525:
526: conn.close();
527: System.out.println("PASS XA HOLDABILITY TEST");
528:
529: } catch (XAException xae) {
530: System.out.println("XAException error code "
531: + xae.errorCode);
532: xae.printStackTrace(System.out);
533: Throwable t = xae.getCause();
534: if (t instanceof SQLException)
535: JDBCDisplayUtil.ShowSQLException(System.out,
536: (SQLException) t);
537:
538: } catch (SQLException sqle) {
539: JDBCDisplayUtil.ShowSQLException(System.out, sqle);
540: } catch (Throwable t) {
541: t.printStackTrace(System.out);
542: }
543: System.out.flush();
544: }
545:
546: /**
547: * Perform connection checks on the default connection
548: * using checkDataSourc30.
549: */
550: public static void checkNesConn30(String dsName)
551: throws SQLException {
552: Connection conn = DriverManager
553: .getConnection("jdbc:default:connection");
554: new checkDataSource30().checkConnection(dsName, conn);
555: }
556:
557: /**
558: * USe checkNesConn30 for the procedure, will
559: * cause the 30 checks to be invoked as well.
560: */
561: protected String getNestedMethodName() {
562: return "checkDataSource30.checkNesConn30";
563: }
564:
565: /**
566: * Tests for DERBY-1144
567: *
568: * This test tests that holdability, autocomit, and transactionIsolation are
569: * reset on getConnection for PooledConnections obtaind from connectionPoolDataSources
570: *
571: * DERBY-1134 has been filed for more comprehensive testing of client connection state.
572: *
573: * @throws SQLException
574: */
575: public static void testDerby1144() throws SQLException {
576: Connection conn = null;
577: PooledConnection pc1 = null;
578: Properties p = new Properties();
579:
580: p.put("databaseName", "sample");
581: p.put("connectionAttributes", "create=true");
582: p.put("user", "APP");
583: p.put("password", "pw");
584:
585: // Test holdability
586: ConnectionPoolDataSource ds = TestUtil
587: .getConnectionPoolDataSource(p);
588: pc1 = ds.getPooledConnection();
589: testPooledConnHoldability("PooledConnection", pc1);
590: pc1.close();
591:
592: // Test autocommit
593: pc1 = ds.getPooledConnection();
594: testPooledConnAutoCommit("PooledConnection", pc1);
595: pc1.close();
596:
597: // Test pooled connection isolation
598: ds = TestUtil.getConnectionPoolDataSource(p);
599: pc1 = ds.getPooledConnection();
600: testPooledConnIso("PooledConnection", pc1);
601: pc1.close();
602:
603: // Test xa connection isolation
604: XADataSource xds = TestUtil.getXADataSource(p);
605: XAConnection xpc1 = xds.getXAConnection();
606: testPooledConnIso("XAConnection", xpc1);
607: xpc1.close();
608: }
609:
610: /**
611: * Make sure autocommit gets reset on PooledConnection.getConnection()
612: * @param desc description of connection
613: * @param pc1 pooled connection to test
614: * @throws SQLException
615: */
616: private static void testPooledConnAutoCommit(String desc,
617: PooledConnection pc1) throws SQLException {
618: System.out.println("\n** Test autoCommit state for: " + desc
619: + "**");
620: Connection conn = pc1.getConnection();
621: conn.setAutoCommit(true);
622: // reset the connection and see if the autocommit
623: conn = pc1.getConnection();
624: boolean autocommit = conn.getAutoCommit();
625: if (autocommit != true) {
626: new Exception("FAIL: autoCommit not reset on getConnection")
627: .printStackTrace(System.out);
628: } else {
629: System.out
630: .println("PASS: autoCommit reset on getConnection");
631: }
632: conn.close();
633: }
634:
635: /**
636: * Test Holdability gets reset on PooledConnection.getConnection()
637: * @param desc
638: * @param pc1
639: * @throws SQLException
640: */
641: private static void testPooledConnHoldability(String desc,
642: PooledConnection pc1) throws SQLException {
643: System.out.println("\n**Test holdability state for: " + desc
644: + " **");
645: Connection conn = pc1.getConnection();
646: conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
647: // reset the connection and see if the holdability gets reset
648: // to HOLD_CURSORS_OVER_COMMIT
649: conn = pc1.getConnection();
650: checkConnHoldability(conn, ResultSet.HOLD_CURSORS_OVER_COMMIT);
651: conn.close();
652: }
653:
654: /**
655: * Verify connection holdablity is expected holdability
656: * @param conn
657: * @param expectedHoldability
658: * * @throws SQLException
659: */
660: private static void checkConnHoldability(Connection conn,
661: int expectedHoldability) throws SQLException {
662: int holdability = conn.getHoldability();
663: if (holdability != expectedHoldability) {
664: new Exception("FAIL: Holdability:"
665: + translateHoldability(holdability)
666: + " does not match expected holdability:"
667: + translateHoldability(expectedHoldability))
668: .printStackTrace(System.out);
669: } else {
670: System.out
671: .println("PASS: Holdability matches expected holdability:"
672: + translateHoldability(expectedHoldability));
673: }
674: }
675:
676: /**
677: * Test that isolation is reset on PooledConnection.getConnection()
678: * @param pooledConnType Descripiton of the type of pooled connection
679: * @param pc
680: * @throws SQLException
681: */
682: private static void testPooledConnIso(String pooledConnType,
683: PooledConnection pc) throws SQLException {
684: Connection conn = pc.getConnection();
685:
686: setupDerby1144Table(conn);
687: System.out.println("*** Test isolation level reset on "
688: + pooledConnType + ".getConnection()***");
689: ;
690: System.out
691: .println("\nsetTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED");
692: conn
693: .setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
694: checkIsoLocks(conn, Connection.TRANSACTION_READ_UNCOMMITTED);
695:
696: conn.close();
697: System.out.println("\nGet a new connection with "
698: + pooledConnType + ".getConnection()");
699: System.out
700: .println("Isolation level should be reset to READ_COMMITTED");
701: Connection newconn = pc.getConnection();
702: checkIsoLocks(newconn, Connection.TRANSACTION_READ_COMMITTED);
703:
704: }
705:
706: /**
707: * Make a simple table for DERBY-1144 tests
708: * @param conn
709: * @throws SQLException
710: */
711: private static void setupDerby1144Table(Connection conn)
712: throws SQLException {
713: Statement stmt = conn.createStatement();
714: try {
715: stmt.executeUpdate("DROP TABLE TAB1");
716: } catch (SQLException e) {
717: // ignore drop error
718: }
719: stmt.executeUpdate("CREATE TABLE TAB1(COL1 INT NOT NULL)");
720: stmt.executeUpdate("INSERT INTO TAB1 VALUES(1)");
721: stmt.executeUpdate("INSERT INTO TAB1 VALUES(2)");
722:
723: System.out.println("done creating table");
724: conn.commit();
725: }
726:
727: /*
728: * Checks locks for designated isolation level on the connection.
729: * Currently only supports TRANSACTION_READ_COMMITTED and
730: * TRANSACTION_READ_UNCOMMITTED
731: * @param conn Connection to test
732: * @param isoLevel expected isolation level
733: *
734: */
735: private static void checkIsoLocks(Connection conn,
736: int expectedIsoLevel) {
737: try {
738: int conniso = conn.getTransactionIsolation();
739: if (conniso != expectedIsoLevel) {
740: new Exception("FAIL: Connection isolation level "
741: + translateIso(conniso)
742: + " does not match expected level "
743: + translateIso(expectedIsoLevel));
744: }
745:
746: boolean selectTimedOut = selectTimesoutDuringUpdate(conn);
747: switch (conniso) {
748: case Connection.TRANSACTION_READ_UNCOMMITTED:
749: if (selectTimedOut)
750: new Exception(
751: "FAIL: Unexpected lock timeout for READ_UNCOMMITTED")
752: .printStackTrace(System.out);
753: else
754: System.out
755: .println("PASS: No lock timeout occurs for READ_UNCOMMITTED");
756: case Connection.TRANSACTION_READ_COMMITTED:
757: if (selectTimedOut)
758: System.out
759: .println("PASS: Expected lock timeout for READ_COMMITTED");
760: else
761: new Exception(
762: "FAIL: Did not get lock timeout for READ_COMMITTED");
763: default:
764: new Exception("No test support for isolation level"
765: + translateIso(conniso));
766: }
767: } catch (SQLException se) {
768: se.printStackTrace();
769: }
770: }
771:
772: /**
773: * Determine if a select on this connection during update will timeout.
774: * Used to establish isolation level. If the connection isolation level
775: * is <code> Connection.TRANSACTION_READ_UNCOMMITTED </code> it will not
776: * timeout. Otherwise it should.
777: *
778: * @param conn Connection to test.
779: * @return true if the select got a lock timeout, false otherwise.
780: */
781: private static boolean selectTimesoutDuringUpdate(Connection conn) {
782: Connection updateConn = null;
783:
784: try {
785:
786: conn.setAutoCommit(false);
787: // create another connection and do an update but don't commit
788: updateConn = TestUtil
789: .getConnection("sample", "create=true");
790: updateConn.setAutoCommit(false);
791:
792: // First update the rows on the update connection
793: Statement upStmt = updateConn.createStatement();
794: upStmt.executeUpdate("update tab1 set col1 = 3");
795:
796: // now see if we can select them
797:
798: Statement stmt = conn.createStatement();
799: ResultSet rs = stmt.executeQuery("Select * from tab1");
800: while (rs.next()) {
801: }
802: ;
803: rs.close();
804:
805: } catch (SQLException e) {
806: if (e.getSQLState().equals("40XL1")) {
807: // If we got a lock timeout this is not read uncommitted
808: return true;
809: }
810:
811: } finally {
812:
813: try {
814: conn.rollback();
815: updateConn.rollback();
816: } catch (SQLException se) {
817: se.printStackTrace();
818: }
819: }
820: return false;
821:
822: }
823:
824: /**
825: * Translate holdability int readable format.
826: *
827: * @param holdability holdability to translate.
828: * @return "HOLD_CURSORS_OVER_COMMIT" or "CLOSE_CURSORS_AT_COMMIT"
829: */
830: public static String translateHoldability(int holdability) {
831: switch (holdability) {
832: case ResultSet.HOLD_CURSORS_OVER_COMMIT:
833: return "HOLD_CURSORS_OVER_COMMIT";
834: case ResultSet.CLOSE_CURSORS_AT_COMMIT:
835: return "CLOSE_CURSORS_AT_COMMIT";
836: }
837: return "UNKNOWN_HOLDABILTY";
838: }
839:
840: }
|