001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava
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.lang;
023:
024: import java.sql.CallableStatement;
025: import java.sql.Connection;
026: import java.sql.DriverManager;
027: import java.sql.PreparedStatement;
028: import java.sql.ResultSet;
029: import java.sql.ResultSetMetaData;
030: import java.sql.SQLException;
031: import java.sql.Statement;
032: import java.sql.Types;
033:
034: import org.apache.derby.tools.ij;
035: import org.apache.derby.tools.JDBCDisplayUtil;
036:
037: import org.apache.derbyTesting.functionTests.util.TestUtil;
038:
039: /**
040: * Test hold cursor after commit
041: */
042: public class holdCursorJava {
043: private static String[] databaseObjects = { "PROCEDURE MYPROC",
044: "TABLE T1", "TABLE T2", "TABLE TESTTABLE1",
045: "TABLE TESTTABLE2", "TABLE BUG4385" };
046: private static boolean HAVE_DRIVER_MANAGER_CLASS;
047:
048: static {
049: try {
050: Class.forName("java.sql.DriverManager");
051: HAVE_DRIVER_MANAGER_CLASS = true;
052: } catch (ClassNotFoundException e) {
053: //Used for JSR169
054: HAVE_DRIVER_MANAGER_CLASS = false;
055: }
056: }
057:
058: public static void main(String args[]) {
059: try {
060: /* Load the JDBC Driver class */
061: // use the ij utility to read the property file and
062: // make the initial connection.
063: ij.getPropertyArg(args);
064: Connection conn = ij.startJBMS();
065:
066: createAndPopulateTable(conn);
067:
068: //set autocommit to off after creating table and inserting data
069: conn.setAutoCommit(false);
070:
071: if (HAVE_DRIVER_MANAGER_CLASS) {
072: testHoldability(conn,
073: ResultSet.HOLD_CURSORS_OVER_COMMIT);
074: testHoldability(conn, ResultSet.CLOSE_CURSORS_AT_COMMIT);
075: }
076:
077: testHoldCursorOnMultiTableQuery(conn);
078: testIsolationLevelChange(conn);
079: testCloseCursor(conn);
080: testDropTable(conn);
081:
082: conn.rollback();
083: conn.setAutoCommit(true);
084:
085: Statement stmt = conn.createStatement();
086: TestUtil.cleanUpTest(stmt, databaseObjects);
087: conn.close();
088:
089: } catch (Exception e) {
090: System.out.println("FAIL -- unexpected exception " + e);
091: JDBCDisplayUtil.ShowException(System.out, e);
092: e.printStackTrace();
093: }
094: }
095:
096: //create table and insert couple of rows
097: private static void createAndPopulateTable(Connection conn)
098: throws SQLException {
099: Statement stmt = conn.createStatement();
100:
101: // first drop the objects, in case something is left over from past runs or other tests
102: TestUtil.cleanUpTest(stmt, databaseObjects);
103:
104: System.out.println("Creating table...");
105: final int stringLength = 400;
106: stmt
107: .executeUpdate("CREATE TABLE T1 (c11 int, c12 int, junk varchar("
108: + stringLength + "))");
109: PreparedStatement insertStmt = conn
110: .prepareStatement("INSERT INTO T1 VALUES(?,1,?)");
111: // We need to ensure that there is more data in the table than the
112: // client can fetch in one message (about 32K). Otherwise, the
113: // cursor might be closed on the server and we are not testing the
114: // same thing in embedded mode and client/server mode.
115: 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
131: .execute("create table testtable1 (id integer, vc varchar(100))");
132: stmt
133: .execute("insert into testtable1 values (11, 'testtable1-one'), (12, 'testtable1-two')");
134: stmt
135: .execute("create table testtable2 (id integer, vc varchar(100))");
136: stmt
137: .execute("insert into testtable2 values (21, 'testtable2-one'), (22, 'testtable2-two')");
138: stmt
139: .execute("create procedure MYPROC() language java parameter style java external name "
140: + "'org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava.testProc' result sets 2");
141: System.out.println("done creating table and inserting data.");
142:
143: stmt.close();
144: }
145:
146: //drop tables
147: private static void cleanUpTest(Connection conn)
148: throws SQLException {
149: Statement stmt = conn.createStatement();
150: //System.out.println("dropping test objects...");
151: stmt.executeUpdate("DROP PROCEDURE MYPROC");
152: stmt.executeUpdate("DROP TABLE T1");
153: stmt.executeUpdate("DROP TABLE T2");
154: stmt.executeUpdate("DROP TABLE testtable1");
155: stmt.executeUpdate("DROP TABLE testtable2");
156: stmt.executeUpdate("DROP TABLE BUG4385");
157: stmt.close();
158: }
159:
160: //test cursor holdability after commit on multi table query
161: private static void testHoldCursorOnMultiTableQuery(Connection conn)
162: throws Exception {
163: Statement s;
164: ResultSet rs;
165:
166: System.out
167: .println("Start multi table query with holdability true test");
168: s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
169: ResultSet.CONCUR_READ_ONLY,
170: ResultSet.HOLD_CURSORS_OVER_COMMIT);
171:
172: //open a cursor with multiple rows resultset
173: rs = s
174: .executeQuery("select t1.c11, t2.c22 from t1, t2 where t1.c11=t2.c21");
175: rs.next();
176: System.out.println("value of t2.c22 is " + rs.getString(2));
177: conn.commit();
178: rs.next(); //because holdability is true, should be able to navigate the cursor after commit
179: System.out.println("value of t2.c22 is " + rs.getString(2));
180: rs.close();
181: System.out
182: .println("Multi table query with holdability true test over");
183: }
184:
185: //test cursor holdability after commit
186: private static void testIsolationLevelChange(Connection conn)
187: throws Exception {
188: Statement s;
189: ResultSet rs;
190:
191: System.out.println("Start isolation level change test");
192: //set current isolation to read committed
193: conn
194: .setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
195:
196: s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
197: ResultSet.CONCUR_READ_ONLY,
198: ResultSet.HOLD_CURSORS_OVER_COMMIT);
199:
200: //open a cursor with multiple rows resultset
201: rs = s.executeQuery("select * from t1");
202: rs.next();
203:
204: //Changing to different isolation from the current isolation for connection
205: //will give an exception because there are held cursors
206: try {
207: System.out
208: .println("Switch isolation while there are open cursors");
209: conn
210: .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
211: } catch (SQLException se) {
212:
213: System.out.println("Should see exceptions");
214: String m = se.getSQLState();
215: JDBCDisplayUtil.ShowSQLException(System.out, se);
216:
217: if ("X0X03".equals(m)) {
218: System.out
219: .println("PASS: Can't change isolation if they are open cursor");
220: } else {
221: System.out
222: .println("FAIL: Shouldn't able to change isolation because there are open cursor");
223: }
224: }
225:
226: //Close open cursors and then try changing to different isolation.
227: //It should work.
228: rs.close();
229: conn
230: .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
231:
232: // set the default holdability for the Connection and try setting the isolation level
233:
234: conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
235:
236: conn
237: .setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
238: conn.createStatement().executeUpdate("SET ISOLATION RS");
239:
240: // test for bug4385 - internal ResultSets were being re-used incorrectly
241: // will occur in with JDBC 2.0,1.2 but the first statement I found that
242: // failed was an insert with generated keys.
243: conn
244: .createStatement()
245: .executeUpdate(
246: "Create table bug4385 (i int not null primary key, c int generated always as identity)");
247: conn.commit();
248:
249: PreparedStatement ps = conn.prepareStatement(
250: "insert into bug4385(i) values(?)",
251: Statement.RETURN_GENERATED_KEYS);
252:
253: ps.setInt(1, 199);
254: ps.executeUpdate();
255:
256: rs = ps.getGeneratedKeys();
257: int count = 0;
258: while (rs.next()) {
259: rs.getInt(1);
260: count++;
261: }
262: rs.close();
263: if (count != 1)
264: System.out
265: .println("FAIL returned more than one row for generated keys");
266:
267: ps.setInt(1, 299);
268: ps.executeUpdate();
269: rs = ps.getGeneratedKeys();
270: count = 0;
271: while (rs.next()) {
272: rs.getInt(1);
273: count++;
274: }
275: if (count != 1)
276: System.out
277: .println("FAIL returned more than one row for generated keys on re-execution");
278: rs.close();
279: ps.close();
280: conn.rollback();
281:
282: //switch back to default isolation & holdability
283: conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
284:
285: System.out.println("Isolation level change test over");
286: conn
287: .setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
288: }
289:
290: /**
291: * Test that drop table cannot be performed when there is an open
292: * cursor on that table.
293: *
294: * @param conn a <code>Connection</code> object
295: * @exception SQLException if an error occurs
296: */
297: private static void testDropTable(Connection conn)
298: throws SQLException {
299: conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
300: final String dropTable = "DROP TABLE T1";
301: Statement stmt1 = conn.createStatement();
302: Statement stmt2 = conn.createStatement();
303: ResultSet rs = stmt1.executeQuery("SELECT * FROM T1");
304: rs.next();
305:
306: // dropping t1 should fail because there is an open cursor on t1
307: boolean ok = false;
308: try {
309: stmt2.executeUpdate(dropTable);
310: } catch (SQLException sqle) {
311: ok = true;
312: }
313: if (!ok) {
314: System.out.println("FAIL: Expected DROP TABLE to fail "
315: + "because of open cursor.");
316: }
317:
318: conn.commit();
319:
320: // cursors are held over commit, so dropping should still fail
321: ok = false;
322: try {
323: stmt2.executeUpdate(dropTable);
324: } catch (SQLException sqle) {
325: ok = true;
326: }
327: if (!ok) {
328: System.out.println("FAIL: Expected DROP TABLE to fail "
329: + "because of held cursor.");
330: }
331:
332: rs.close();
333:
334: // cursor is closed, so this one should succeed
335: stmt2.executeUpdate(dropTable);
336: stmt1.close();
337: stmt2.close();
338: conn.rollback();
339: }
340:
341: //set connection holdability and test holdability of statements inside and outside procedures
342: //test that holdability of statements always overrides holdability of connection
343: private static void testHoldability(Connection conn, int holdability)
344: throws SQLException {
345:
346: conn.setHoldability(holdability);
347:
348: switch (holdability) {
349: case ResultSet.HOLD_CURSORS_OVER_COMMIT:
350: System.out
351: .println("\ntestHoldability with HOLD_CURSORS_OVER_COMMIT\n");
352: break;
353: case ResultSet.CLOSE_CURSORS_AT_COMMIT:
354: System.out
355: .println("\ntestHoldability with CLOSE_CURSORS_AT_COMMIT\n");
356: break;
357: }
358:
359: testStatements(conn);
360: testStatementsInProcedure(conn);
361: }
362:
363: //test holdability of statements outside procedures
364: private static void testStatements(Connection conn)
365: throws SQLException {
366: System.out.println("\ntestStatements()\n");
367:
368: //HOLD_CURSORS_OVER_COMMIT
369: Statement st1 = conn.createStatement(
370: ResultSet.TYPE_SCROLL_INSENSITIVE,
371: ResultSet.CONCUR_READ_ONLY,
372: ResultSet.HOLD_CURSORS_OVER_COMMIT);
373: ResultSet rs1 = st1.executeQuery("select * from testtable1");
374: checkResultSet(rs1, "before");
375: conn.commit();
376: checkResultSet(rs1, "after");
377: st1.close();
378:
379: //CLOSE_CURSORS_AT_COMMIT
380: Statement st2 = conn.createStatement(
381: ResultSet.TYPE_SCROLL_INSENSITIVE,
382: ResultSet.CONCUR_READ_ONLY,
383: ResultSet.CLOSE_CURSORS_AT_COMMIT);
384: ResultSet rs2 = st2.executeQuery("select * from testtable2");
385: checkResultSet(rs2, "before");
386: conn.commit();
387: checkResultSet(rs2, "after");
388: st2.close();
389: }
390:
391: //test holdability of statements in procedures
392: private static void testStatementsInProcedure(Connection conn)
393: throws SQLException {
394: System.out.println("\ntestStatementsInProcedure()\n");
395:
396: CallableStatement cs1 = conn.prepareCall("call MYPROC()");
397: cs1.execute();
398: do {
399: checkResultSet(cs1.getResultSet(), "before");
400: } while (cs1.getMoreResults());
401:
402: CallableStatement cs2 = conn.prepareCall("call MYPROC()");
403: cs2.execute();
404: conn.commit();
405: do {
406: checkResultSet(cs2.getResultSet(), "after");
407: } while (cs2.getMoreResults());
408:
409: cs1.close();
410: cs2.close();
411: }
412:
413: // DERBY-821: Test that cursors are closed when close() is
414: // called. Since the network server implicitly closes a
415: // forward-only result set when all rows are read, the call to
416: // close() might be a no-op.
417: private static void testCloseCursor(Connection conn)
418: throws SQLException {
419: System.out.println("\ntestCloseCursor()\n");
420: // Run this test on one large table (T1) where the network
421: // server won't close the cursor implicitly, and on one small
422: // table (T2) where the network server will close the cursor
423: // implicitly.
424: final String[] tables = { "T1", "T2" };
425: Statement stmt1 = conn.createStatement();
426: Statement stmt2 = conn.createStatement();
427: for (int i = 0; i < tables.length; i++) {
428: String table = tables[i];
429: ResultSet rs = stmt1.executeQuery("SELECT * FROM " + table);
430: rs.next();
431: rs.close();
432: // Cursor is closed, so this should succeed. If the cursor
433: // is open, it will fail because an table cannot be
434: // dropped when there are open cursors depending on it.
435: stmt2.executeUpdate("DROP TABLE " + table);
436: }
437: stmt1.close();
438: stmt2.close();
439: conn.rollback();
440: }
441:
442: //check if resultset is accessible
443: private static void checkResultSet(ResultSet rs,
444: String beforeOrAfter) throws SQLException {
445: System.out.println("checkResultSet " + beforeOrAfter
446: + " commit");
447: try {
448: if (rs != null) {
449: rs.next();
450: System.out.println(rs.getString(1) + ", "
451: + rs.getString(2));
452: } else {
453: System.out.println("EXPECTED:ResultSet is null");
454: }
455: } catch (SQLException se) {
456: System.out.println("EXPECTED EXCEPTION:" + se.getMessage());
457: }
458: }
459:
460: //Java method for stored procedure
461: public static void testProc(ResultSet[] rs1, ResultSet[] rs2)
462: throws Exception {
463: Connection conn = DriverManager
464: .getConnection("jdbc:default:connection");
465:
466: //HOLD_CURSORS_OVER_COMMIT
467: Statement st1 = conn.createStatement(
468: ResultSet.TYPE_SCROLL_INSENSITIVE,
469: ResultSet.CONCUR_READ_ONLY,
470: ResultSet.HOLD_CURSORS_OVER_COMMIT);
471: rs1[0] = st1.executeQuery("select * from testtable1");
472:
473: //CLOSE_CURSORS_AT_COMMIT
474: Statement st2 = conn.createStatement(
475: ResultSet.TYPE_SCROLL_INSENSITIVE,
476: ResultSet.CONCUR_READ_ONLY,
477: ResultSet.CLOSE_CURSORS_AT_COMMIT);
478: rs2[0] = st2.executeQuery("select * from testtable2");
479:
480: }
481: }
|