001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.holdCursorJavaReflection
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.lang.reflect.*;
025:
026: import java.sql.CallableStatement;
027: import java.sql.Connection;
028: import java.sql.PreparedStatement;
029: import java.sql.ResultSet;
030: import java.sql.ResultSetMetaData;
031: import java.sql.SQLException;
032: import java.sql.Statement;
033: import java.sql.Types;
034:
035: import org.apache.derby.tools.ij;
036: import org.apache.derby.tools.JDBCDisplayUtil;
037:
038: /**
039: * Test hold cursor after commit using reflection. This test is specifically to test
040: * this feature under jdk13.
041: */
042: public class holdCursorJavaReflection {
043:
044: //we implemented hold cursor functionality in EmbedConnection20 package and hence
045: //the functionality is available under both jdk14 and jdk13 (though, jdbc in jdk13
046: //doesn't have the api to access it).
047: //An internal project in TIVOLI needed access to holdability under jdk13 and we
048: //recommended them to use reflection to get to holdability apis under jdk13.
049: //This will also be documented on our website under faq
050: //In order to have a test for that workaround in our test suite, I am using reflection
051: //for createStatement and prepareStatement and prepareCall.
052:
053: //prepareStatement and prepareCall take 4 parameters
054: private static Class[] PREP_STMT_PARAM = { String.class,
055: Integer.TYPE, Integer.TYPE, Integer.TYPE };
056: private static Object[] PREP_STMT_ARG = {
057: "select * from t1 where c12 = ?",
058: new Integer(ResultSet.TYPE_FORWARD_ONLY),
059: new Integer(ResultSet.CONCUR_READ_ONLY),
060: new Integer(ResultSet.HOLD_CURSORS_OVER_COMMIT) };
061:
062: private static Object[] PREP_STMT_ERROR_ARG = {
063: "select * from t1NotThere where c12 = ?",
064: new Integer(ResultSet.TYPE_FORWARD_ONLY),
065: new Integer(ResultSet.CONCUR_READ_ONLY),
066: new Integer(ResultSet.HOLD_CURSORS_OVER_COMMIT) };
067:
068: //createStatement takes 3 parameters
069: private static Class[] STMT_PARAM = { Integer.TYPE, Integer.TYPE,
070: Integer.TYPE };
071: private static Object[] STMT_ARG = {
072: new Integer(ResultSet.TYPE_FORWARD_ONLY),
073: new Integer(ResultSet.CONCUR_READ_ONLY),
074: new Integer(ResultSet.HOLD_CURSORS_OVER_COMMIT) };
075:
076: public static void main(String args[]) {
077: try {
078: /* Load the JDBC Driver class */
079: // use the ij utility to read the property file and
080: // make the initial connection.
081: ij.getPropertyArg(args);
082: Connection conn = ij.startJBMS();
083:
084: dropTable(conn);
085: createAndPopulateTable(conn);
086:
087: //set autocommit to off after creating table and inserting data
088: conn.setAutoCommit(false);
089: testHoldability(conn);
090: testPreparedStatement(conn);
091: testCallableStatement(conn);
092: conn.rollback();
093: conn.setAutoCommit(true);
094: dropTable(conn);
095: conn.close();
096: } catch (Exception e) {
097: System.out.println("FAIL -- unexpected exception " + e);
098: JDBCDisplayUtil.ShowException(System.out, e);
099: e.printStackTrace();
100: }
101: }
102:
103: //create table and insert couple of rows
104: private static void createAndPopulateTable(Connection conn)
105: throws SQLException {
106: Statement stmt = conn.createStatement();
107:
108: System.out.println("Creating table...");
109: stmt.executeUpdate("CREATE TABLE T1 (c11 int, c12 int)");
110: stmt.executeUpdate("INSERT INTO T1 VALUES(1,1)");
111: stmt.executeUpdate("INSERT INTO T1 VALUES(2,1)");
112: System.out.println("done creating table and inserting data.");
113:
114: stmt.close();
115: }
116:
117: //drop table
118: private static void dropTable(Connection conn)
119: // throws SQLException
120: {
121: try {
122: Statement stmt = conn.createStatement();
123: stmt.executeUpdate("DROP TABLE T1");
124: stmt.close();
125: } catch (SQLException se) {
126: } // assume any error is because table doesn't exist
127: }
128:
129: //test cursor holdability for callable statements
130: private static void testCallableStatement(Connection conn)
131: throws Exception {
132: CallableStatement cs;
133: ResultSet rs;
134:
135: System.out
136: .println("Start hold cursor for callable statements test");
137:
138: //create a callable statement with hold cursor over commit using reflection.
139: Method sh = conn.getClass().getMethod("prepareCall",
140: PREP_STMT_PARAM);
141: cs = (CallableStatement) (sh.invoke(conn, PREP_STMT_ARG));
142: cs.setInt(1, 1);
143: rs = cs.executeQuery();
144:
145: System.out.println("do next() before commit");
146: rs.next();
147: System.out.println("look at first column's value: "
148: + rs.getInt(1));
149: conn.commit();
150: System.out
151: .println("After commit, look at first column's value: "
152: + rs.getInt(1));
153: System.out
154: .println("do next() after commit. Should be at the end of resultset");
155: rs.next();
156: System.out
157: .println("one more next() here will give no more rows");
158: rs.next();
159: System.out
160: .println("Holdable cursor after commit for callable statements test over");
161: rs.close();
162: }
163:
164: //test cursor holdability after commit
165: private static void testHoldability(Connection conn)
166: throws Exception {
167: Statement s;
168: PreparedStatement ps;
169: ResultSet rs;
170:
171: System.out.println("Start holdable cursor after commit test");
172: //create a statement with hold cursor over commit using reflection.
173: Method sh = conn.getClass().getMethod("createStatement",
174: STMT_PARAM);
175: s = (Statement) (sh.invoke(conn, STMT_ARG));
176:
177: //open a cursor with multiple rows resultset
178: rs = s.executeQuery("select * from t1");
179: System.out.println("do next() before commit");
180: rs.next();
181: System.out.println("look at first column's value: "
182: + rs.getInt(1));
183: conn.commit();
184: System.out
185: .println("After commit, look at first column's value: "
186: + rs.getInt(1));
187: System.out
188: .println("do next() after commit. Should be at the end of resultset");
189: rs.next();
190: System.out
191: .println("one more next() here will give no more rows");
192: rs.next();
193: System.out.println("Holdable cursor after commit test over");
194: rs.close();
195: }
196:
197: //test cursor holdability for prepared statements
198: private static void testPreparedStatement(Connection conn)
199: throws Exception {
200: PreparedStatement ps;
201: ResultSet rs;
202:
203: System.out
204: .println("Start hold cursor for prepared statements test");
205:
206: //create a prepared statement with hold cursor over commit using reflection.
207: Method sh = conn.getClass().getMethod("prepareStatement",
208: PREP_STMT_PARAM);
209: ps = (PreparedStatement) (sh.invoke(conn, PREP_STMT_ARG));
210:
211: ps.setInt(1, 1);
212: rs = ps.executeQuery();
213:
214: System.out.println("do next() before commit");
215: rs.next();
216: System.out.println("look at first column's value: "
217: + rs.getInt(1));
218: conn.commit();
219: System.out
220: .println("After commit, look at first column's value: "
221: + rs.getInt(1));
222: System.out
223: .println("do next() after commit. Should be at the end of resultset");
224: rs.next();
225: System.out
226: .println("one more next() here will give no more rows");
227: rs.next();
228: System.out
229: .println("Holdable cursor after commit for prepared statements test over");
230: rs.close();
231:
232: // Create a prepared statement that will fail on prepare. Make sure we
233: // handle errors ok.
234: sh = conn.getClass().getMethod("prepareStatement",
235: PREP_STMT_PARAM);
236: try {
237: ps = (PreparedStatement) (sh.invoke(conn,
238: PREP_STMT_ERROR_ARG));
239:
240: ps.setInt(1, 1);
241: rs = ps.executeQuery();
242: } catch (SQLException se) {
243: System.out.println("Expected Exception:" + se.getMessage());
244: } catch (InvocationTargetException itex) {
245: Throwable e = itex.getTargetException();
246: //prepareStatement Can only throw SQLExcepton
247: if (e instanceof SQLException) {
248: SQLException se = (SQLException) e;
249: System.out.println("Expected Exception:"
250: + se.getMessage());
251: } else
252: throw itex;
253: }
254: // make sure our connection is still ok.
255: conn.commit();
256: }
257:
258: }
|