001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.procedureJdbc30
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.*;
025:
026: import org.apache.derby.tools.ij;
027: import java.io.PrintStream;
028: import java.math.BigInteger;
029: import java.math.BigDecimal;
030:
031: import org.apache.derbyTesting.functionTests.tests.jdbcapi.parameterMetaDataJdbc30;
032: import org.apache.derbyTesting.functionTests.util.TestUtil;
033:
034: public class procedureJdbc30 {
035:
036: static private boolean isDerbyNet = false;
037: static private String[] testObjects = { "TABLE MRS.FIVERS",
038: "PROCEDURE MRS.FIVEJP" };
039:
040: public static void main(String[] argv) throws Throwable {
041: ij.getPropertyArg(argv);
042: Connection conn = ij.startJBMS();
043: isDerbyNet = TestUtil.isNetFramework();
044:
045: runTests(conn);
046: }
047:
048: public static void runTests(Connection conn) throws Throwable {
049: try {
050: testMoreResults(conn);
051: } catch (SQLException sqle) {
052: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
053: System.out, sqle);
054: sqle.printStackTrace(System.out);
055: }
056:
057: }
058:
059: private static void testMoreResults(Connection conn)
060: throws SQLException {
061:
062: Statement s = conn.createStatement();
063: TestUtil.cleanUpTest(s, testObjects);
064:
065: s.executeUpdate("create table MRS.FIVERS(i integer)");
066: PreparedStatement ps = conn
067: .prepareStatement("insert into MRS.FIVERS values (?)");
068: for (int i = 1; i <= 20; i++) {
069: ps.setInt(1, i);
070: ps.executeUpdate();
071: }
072: ps.close();
073:
074: // create a procedure that returns 5 result sets.
075:
076: s
077: .executeUpdate("create procedure MRS.FIVEJP() parameter style JAVA READS SQL DATA dynamic result sets 5 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.fivejp'");
078:
079: CallableStatement cs = conn.prepareCall("CALL MRS.FIVEJP()");
080: ResultSet[] allRS = new ResultSet[5];
081:
082: // execute the procedure that returns 5 result sets and then use the various
083: // options of getMoreResults().
084:
085: System.out
086: .println("\n\nFetching result sets with getMoreResults()");
087: int pass = 0;
088: cs.execute();
089: do {
090:
091: allRS[pass++] = cs.getResultSet();
092: System.out.println(" PASS " + pass + " got result set "
093: + (allRS[pass - 1] != null));
094: // expect everything except the current result set to be closed.
095: showResultSetStatus(allRS);
096:
097: } while (cs.getMoreResults());
098: // check last one got closed
099: showResultSetStatus(allRS);
100: java.util.Arrays.fill(allRS, null);
101:
102: System.out
103: .println("\n\nFetching result sets with getMoreResults(Statement.CLOSE_CURRENT_RESULT)");
104: pass = 0;
105: cs.execute();
106: do {
107:
108: allRS[pass++] = cs.getResultSet();
109: System.out.println(" PASS " + pass + " got result set "
110: + (allRS[pass - 1] != null));
111: // expect everything except the current result set to be closed.
112: showResultSetStatus(allRS);
113:
114: } while (cs.getMoreResults(Statement.CLOSE_CURRENT_RESULT));
115: // check last one got closed
116: showResultSetStatus(allRS);
117: java.util.Arrays.fill(allRS, null);
118:
119: System.out
120: .println("\n\nFetching result sets with getMoreResults(Statement.CLOSE_ALL_RESULTS)");
121: pass = 0;
122: cs.execute();
123: do {
124:
125: allRS[pass++] = cs.getResultSet();
126: System.out.println(" PASS " + pass + " got result set "
127: + (allRS[pass - 1] != null));
128: // expect everything except the current result set to be closed.
129: showResultSetStatus(allRS);
130:
131: } while (cs.getMoreResults(Statement.CLOSE_ALL_RESULTS));
132: // check last one got closed
133: showResultSetStatus(allRS);
134: java.util.Arrays.fill(allRS, null);
135:
136: System.out
137: .println("\n\nFetching result sets with getMoreResults(Statement.KEEP_CURRENT_RESULT)");
138: pass = 0;
139: cs.execute();
140: do {
141:
142: allRS[pass++] = cs.getResultSet();
143: System.out.println(" PASS " + pass + " got result set "
144: + (allRS[pass - 1] != null));
145: // expect everything to stay open.
146: showResultSetStatus(allRS);
147:
148: } while (cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
149: // All should still be open.
150: showResultSetStatus(allRS);
151: // now close them all.
152: for (int i = 0; i < allRS.length; i++) {
153: allRS[i].close();
154: }
155: java.util.Arrays.fill(allRS, null);
156:
157: System.out
158: .println("\n\nFetching result sets with getMoreResults(<mixture>)");
159: cs.execute();
160:
161: System.out.println(" first two with KEEP_CURRENT_RESULT");
162: allRS[0] = cs.getResultSet();
163: boolean moreRS = cs
164: .getMoreResults(Statement.KEEP_CURRENT_RESULT);
165: if (!moreRS)
166: System.out.println("FAIL - no second result set");
167: allRS[1] = cs.getResultSet();
168: // two open
169: showResultSetStatus(allRS);
170:
171: System.out.println(" third with CLOSE_CURRENT_RESULT");
172: moreRS = cs.getMoreResults(Statement.CLOSE_CURRENT_RESULT);
173: if (!moreRS)
174: System.out.println("FAIL - no third result set");
175: allRS[2] = cs.getResultSet();
176: // first and third open, second closed
177: showResultSetStatus(allRS);
178:
179: System.out.println(" fourth with KEEP_CURRENT_RESULT");
180: moreRS = cs.getMoreResults(Statement.KEEP_CURRENT_RESULT);
181: if (!moreRS)
182: System.out.println("FAIL - no fourth result set");
183: allRS[3] = cs.getResultSet();
184: // first, third and fourth open, second closed
185: showResultSetStatus(allRS);
186:
187: System.out.println(" fifth with CLOSE_ALL_RESULTS");
188: moreRS = cs.getMoreResults(Statement.CLOSE_ALL_RESULTS);
189: if (!moreRS)
190: System.out.println("FAIL - no fifth result set");
191: allRS[4] = cs.getResultSet();
192: // only fifth open
193: showResultSetStatus(allRS);
194:
195: System.out
196: .println(" no more results with with KEEP_CURRENT_RESULT");
197: moreRS = cs.getMoreResults(Statement.KEEP_CURRENT_RESULT);
198: if (moreRS)
199: System.out.println("FAIL - too many result sets");
200: // only fifth open
201: showResultSetStatus(allRS);
202: allRS[4].close();
203: java.util.Arrays.fill(allRS, null);
204:
205: System.out
206: .println("\n\nFetching result sets with getMoreResults(Statement.KEEP_CURRENT_RESULT) and checking that cs.execute() closes them");
207: pass = 0;
208: cs.execute();
209: do {
210:
211: allRS[pass++] = cs.getResultSet();
212: System.out.println(" PASS " + pass + " got result set "
213: + (allRS[pass - 1] != null));
214: // expect everything to stay open.
215: showResultSetStatus(allRS);
216:
217: } while (cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
218: System.out.println(" fetched all results");
219: // All should still be open.
220: showResultSetStatus(allRS);
221: System.out.println(" executing statement");
222: cs.execute();
223: // all should be closed.
224: showResultSetStatus(allRS);
225: java.util.Arrays.fill(allRS, null);
226:
227: System.out
228: .println("\n\nFetching result sets with getMoreResults(Statement.KEEP_CURRENT_RESULT) and checking that cs.close() closes them");
229: pass = 0;
230: // using execute from above.
231: do {
232:
233: allRS[pass++] = cs.getResultSet();
234: System.out.println(" PASS " + pass + " got result set "
235: + (allRS[pass - 1] != null));
236: // expect everything to stay open.
237: showResultSetStatus(allRS);
238:
239: } while (cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
240: System.out.println(" fetched all results");
241: // All should still be open.
242: showResultSetStatus(allRS);
243: System.out.println(" closing statement");
244: cs.close();
245: // all should be closed.
246: showResultSetStatus(allRS);
247: java.util.Arrays.fill(allRS, null);
248:
249: }
250:
251: private static void showResultSetStatus(ResultSet[] allRS) {
252: for (int i = 0; i < allRS.length; i++) {
253: try {
254: ResultSet rs = allRS[i];
255: if (rs == null)
256: continue;
257: rs.next();
258: System.out.println(" RS (" + (i + 1) + ") val "
259: + rs.getInt(1));
260: } catch (SQLException sqle) {
261: System.out.println(" Exception - "
262: + sqle.getMessage());
263: }
264: }
265: }
266: }
|