KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > lang > holdCursorJava


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to You under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */

21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23
24 import java.sql.CallableStatement JavaDoc;
25 import java.sql.Connection JavaDoc;
26 import java.sql.DriverManager JavaDoc;
27 import java.sql.PreparedStatement JavaDoc;
28 import java.sql.ResultSet JavaDoc;
29 import java.sql.ResultSetMetaData JavaDoc;
30 import java.sql.SQLException JavaDoc;
31 import java.sql.Statement JavaDoc;
32 import java.sql.Types JavaDoc;
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 /**
40  * Test hold cursor after commit
41  */

42 public class holdCursorJava {
43   private static String JavaDoc[] 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 JavaDoc e){
54       //Used for JSR169
55
HAVE_DRIVER_MANAGER_CLASS = false;
56     }
57   }
58
59   public static void main (String JavaDoc args[])
60   {
61     try {
62         /* Load the JDBC Driver class */
63         // use the ij utility to read the property file and
64
// make the initial connection.
65
ij.getPropertyArg(args);
66         Connection JavaDoc conn = ij.startJBMS();
67
68         createAndPopulateTable(conn);
69
70         //set autocommit to off after creating table and inserting data
71
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 JavaDoc stmt = conn.createStatement();
87                 TestUtil.cleanUpTest(stmt, databaseObjects);
88         conn.close();
89                
90
91     } catch (Exception JavaDoc e) {
92         System.out.println("FAIL -- unexpected exception "+e);
93         JDBCDisplayUtil.ShowException(System.out, e);
94         e.printStackTrace();
95     }
96   }
97
98   //create table and insert couple of rows
99
private static void createAndPopulateTable(Connection JavaDoc conn) throws SQLException JavaDoc {
100     Statement JavaDoc stmt = conn.createStatement();
101
102     // first drop the objects, in case something is left over from past runs or other tests
103
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 JavaDoc insertStmt =
110         conn.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 JavaDoc buff = new StringBuffer JavaDoc(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   //drop tables
142
private static void cleanUpTest(Connection JavaDoc conn) throws SQLException JavaDoc {
143     Statement JavaDoc stmt = conn.createStatement();
144     //System.out.println("dropping test objects...");
145
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   //test cursor holdability after commit on multi table query
155
private static void testHoldCursorOnMultiTableQuery(Connection JavaDoc conn) throws Exception JavaDoc
156   {
157     Statement JavaDoc s;
158     ResultSet JavaDoc 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     //open a cursor with multiple rows resultset
165
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(); //because holdability is true, should be able to navigate the cursor after commit
170
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   //test cursor holdability after commit
176
private static void testIsolationLevelChange(Connection JavaDoc conn) throws Exception JavaDoc
177   {
178     Statement JavaDoc s;
179     ResultSet JavaDoc rs;
180
181     System.out.println("Start isolation level change test");
182     //set current isolation to read committed
183
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     //open a cursor with multiple rows resultset
189
rs = s.executeQuery("select * from t1");
190     rs.next();
191
192     //Changing to different isolation from the current isolation for connection
193
//will give an exception because there are held cursors
194
try {
195             System.out.println("Switch isolation while there are open cursors");
196             conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
197         } catch (SQLException JavaDoc se) {
198
199             System.out.println("Should see exceptions");
200             String JavaDoc 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     //Close open cursors and then try changing to different isolation.
211
//It should work.
212
rs.close();
213     conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
214
215     // set the default holdability for the Connection and try setting the isolation level
216

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         // test for bug4385 - internal ResultSets were being re-used incorrectly
224
// will occur in with JDBC 2.0,1.2 but the first statement I found that
225
// failed was an insert with generated keys.
226
conn.createStatement().executeUpdate("Create table bug4385 (i int not null primary key, c int generated always as identity)");
227         conn.commit();
228
229         PreparedStatement JavaDoc 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     //switch back to default isolation & holdability
259
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     /**
266      * Test that drop table cannot be performed when there is an open
267      * cursor on that table.
268      *
269      * @param conn a <code>Connection</code> object
270      * @exception SQLException if an error occurs
271      */

272     private static void testDropTable(Connection JavaDoc conn) throws SQLException JavaDoc {
273         conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
274         final String JavaDoc dropTable = "DROP TABLE T1";
275         Statement JavaDoc stmt1 = conn.createStatement();
276         Statement JavaDoc stmt2 = conn.createStatement();
277         ResultSet JavaDoc rs = stmt1.executeQuery("SELECT * FROM T1");
278         rs.next();
279
280         // dropping t1 should fail because there is an open cursor on t1
281
boolean ok = false;
282         try {
283             stmt2.executeUpdate(dropTable);
284         } catch (SQLException JavaDoc 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         // cursors are held over commit, so dropping should still fail
295
ok = false;
296         try {
297             stmt2.executeUpdate(dropTable);
298         } catch (SQLException JavaDoc 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         // cursor is closed, so this one should succeed
309
stmt2.executeUpdate(dropTable);
310         stmt1.close();
311         stmt2.close();
312         conn.rollback();
313     }
314
315     //set connection holdability and test holdability of statements inside and outside procedures
316
//test that holdability of statements always overrides holdability of connection
317
private static void testHoldability(Connection JavaDoc conn,int holdability) throws SQLException JavaDoc{
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     //test holdability of statements outside procedures
335
private static void testStatements(Connection JavaDoc conn) throws SQLException JavaDoc{
336         System.out.println("\ntestStatements()\n");
337         
338         //HOLD_CURSORS_OVER_COMMIT
339
Statement JavaDoc st1 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE ,
340                     ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
341         ResultSet JavaDoc rs1 = st1.executeQuery("select * from testtable1");
342         checkResultSet(rs1, "before");
343         conn.commit();
344         checkResultSet(rs1, "after");
345         st1.close();
346         
347         //CLOSE_CURSORS_AT_COMMIT
348
Statement JavaDoc st2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE ,
349                     ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
350         ResultSet JavaDoc rs2 = st2.executeQuery("select * from testtable2");
351         checkResultSet(rs2, "before");
352         conn.commit();
353         checkResultSet(rs2, "after");
354         st2.close();
355      }
356     
357     //test holdability of statements in procedures
358
private static void testStatementsInProcedure(Connection JavaDoc conn) throws SQLException JavaDoc{
359         System.out.println("\ntestStatementsInProcedure()\n");
360         
361         CallableStatement JavaDoc cs1 = conn.prepareCall("call MYPROC()");
362         cs1.execute();
363         do{
364             checkResultSet(cs1.getResultSet(), "before");
365         }while(cs1.getMoreResults());
366                 
367         CallableStatement JavaDoc 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     // DERBY-821: Test that cursors are closed when close() is
379
// called. Since the network server implicitly closes a
380
// forward-only result set when all rows are read, the call to
381
// close() might be a no-op.
382
private static void testCloseCursor(Connection JavaDoc conn)
383         throws SQLException JavaDoc
384     {
385         System.out.println("\ntestCloseCursor()\n");
386         // Run this test on one large table (T1) where the network
387
// server won't close the cursor implicitly, and on one small
388
// table (T2) where the network server will close the cursor
389
// implicitly.
390
final String JavaDoc[] tables = { "T1", "T2" };
391         Statement JavaDoc stmt1 = conn.createStatement();
392         Statement JavaDoc stmt2 = conn.createStatement();
393         for (int i = 0; i < tables.length; i++) {
394             String JavaDoc table = tables[i];
395             ResultSet JavaDoc rs = stmt1.executeQuery("SELECT * FROM " + table);
396             rs.next();
397             rs.close();
398             // Cursor is closed, so this should succeed. If the cursor
399
// is open, it will fail because an table cannot be
400
// dropped when there are open cursors depending on it.
401
stmt2.executeUpdate("DROP TABLE " + table);
402         }
403         stmt1.close();
404         stmt2.close();
405         conn.rollback();
406     }
407     
408     //check if resultset is accessible
409
private static void checkResultSet(ResultSet JavaDoc rs, String JavaDoc beforeOrAfter) throws SQLException JavaDoc{
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 JavaDoc se){
420             System.out.println("EXPECTED EXCEPTION:"+se.getMessage());
421         }
422     }
423       
424     //Java method for stored procedure
425
public static void testProc(ResultSet JavaDoc[] rs1, ResultSet JavaDoc[] rs2) throws Exception JavaDoc
426     {
427         Connection JavaDoc conn = DriverManager.getConnection("jdbc:default:connection");
428         
429         //HOLD_CURSORS_OVER_COMMIT
430
Statement JavaDoc 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         //CLOSE_CURSORS_AT_COMMIT
435
Statement JavaDoc 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