KickJava   Java API By Example, From Geeks To Geeks.

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


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.lang.procedureJdbc30
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 import java.sql.*;
24
25 import org.apache.derby.tools.ij;
26 import java.io.PrintStream JavaDoc;
27 import java.math.BigInteger JavaDoc;
28 import java.math.BigDecimal JavaDoc;
29
30 import org.apache.derbyTesting.functionTests.tests.jdbcapi.parameterMetaDataJdbc30;
31 import org.apache.derbyTesting.functionTests.util.TestUtil;
32 public class procedureJdbc30
33 {
34
35     static private boolean isDerbyNet = false;
36     static private String JavaDoc[] testObjects = { "TABLE MRS.FIVERS", "PROCEDURE MRS.FIVEJP"};
37
38     public static void main (String JavaDoc[] argv) throws Throwable JavaDoc
39     {
40         ij.getPropertyArg(argv);
41         Connection conn = ij.startJBMS();
42         isDerbyNet = TestUtil.isNetFramework();
43
44         runTests( conn);
45     }
46
47     public static void runTests( Connection conn) throws Throwable JavaDoc
48     {
49         try {
50             testMoreResults(conn);
51         } catch (SQLException sqle) {
52             org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
53             sqle.printStackTrace(System.out);
54         }
55         
56     }
57
58     private static void testMoreResults(Connection conn) throws SQLException {
59
60         Statement s = conn.createStatement();
61         TestUtil.cleanUpTest(s, testObjects);
62
63         s.executeUpdate("create table MRS.FIVERS(i integer)");
64         PreparedStatement ps = conn.prepareStatement("insert into MRS.FIVERS values (?)");
65         for (int i = 1; i <= 20; i++) {
66             ps.setInt(1, i);
67             ps.executeUpdate();
68         }
69         ps.close();
70
71         // create a procedure that returns 5 result sets.
72
73         s.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'");
74
75
76         CallableStatement cs = conn.prepareCall("CALL MRS.FIVEJP()");
77         ResultSet[] allRS = new ResultSet[5];
78
79         // execute the procedure that returns 5 result sets and then use the various
80 // options of getMoreResults().
81
82
83
84
85         System.out.println("\n\nFetching result sets with getMoreResults()");
86         int pass = 0;
87         cs.execute();
88         do {
89
90             allRS[pass++] = cs.getResultSet();
91             System.out.println(" PASS " + pass + " got result set " + (allRS[pass -1] != null));
92             // expect everything except the current result set to be closed.
93 showResultSetStatus(allRS);
94
95         } while (cs.getMoreResults());
96         // check last one got closed
97 showResultSetStatus(allRS);
98         java.util.Arrays.fill(allRS, null);
99
100         System.out.println("\n\nFetching result sets with getMoreResults(Statement.CLOSE_CURRENT_RESULT)");
101         pass = 0;
102         cs.execute();
103         do {
104
105             allRS[pass++] = cs.getResultSet();
106             System.out.println(" PASS " + pass + " got result set " + (allRS[pass -1] != null));
107             // expect everything except the current result set to be closed.
108 showResultSetStatus(allRS);
109
110         } while (cs.getMoreResults(Statement.CLOSE_CURRENT_RESULT));
111         // check last one got closed
112 showResultSetStatus(allRS);
113         java.util.Arrays.fill(allRS, null);
114
115         System.out.println("\n\nFetching result sets with getMoreResults(Statement.CLOSE_ALL_RESULTS)");
116         pass = 0;
117         cs.execute();
118         do {
119
120             allRS[pass++] = cs.getResultSet();
121             System.out.println(" PASS " + pass + " got result set " + (allRS[pass -1] != null));
122             // expect everything except the current result set to be closed.
123 showResultSetStatus(allRS);
124
125         } while (cs.getMoreResults(Statement.CLOSE_ALL_RESULTS));
126         // check last one got closed
127 showResultSetStatus(allRS);
128         java.util.Arrays.fill(allRS, null);
129
130         System.out.println("\n\nFetching result sets with getMoreResults(Statement.KEEP_CURRENT_RESULT)");
131         pass = 0;
132         cs.execute();
133         do {
134
135             allRS[pass++] = cs.getResultSet();
136             System.out.println(" PASS " + pass + " got result set " + (allRS[pass -1] != null));
137             // expect everything to stay open.
138 showResultSetStatus(allRS);
139
140         } while (cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
141         // All should still be open.
142 showResultSetStatus(allRS);
143         // now close them all.
144 for (int i = 0; i < allRS.length; i++) {
145             allRS[i].close();
146         }
147         java.util.Arrays.fill(allRS, null);
148
149         System.out.println("\n\nFetching result sets with getMoreResults(<mixture>)");
150         cs.execute();
151
152         System.out.println(" first two with KEEP_CURRENT_RESULT");
153         allRS[0] = cs.getResultSet();
154         boolean moreRS = cs.getMoreResults(Statement.KEEP_CURRENT_RESULT);
155         if (!moreRS)
156             System.out.println("FAIL - no second result set");
157         allRS[1] = cs.getResultSet();
158         // two open
159 showResultSetStatus(allRS);
160         
161         System.out.println(" third with CLOSE_CURRENT_RESULT");
162         moreRS = cs.getMoreResults(Statement.CLOSE_CURRENT_RESULT);
163         if (!moreRS)
164             System.out.println("FAIL - no third result set");
165         allRS[2] = cs.getResultSet();
166         // first and third open, second closed
167 showResultSetStatus(allRS);
168
169         
170         System.out.println(" fourth with KEEP_CURRENT_RESULT");
171         moreRS = cs.getMoreResults(Statement.KEEP_CURRENT_RESULT);
172         if (!moreRS)
173             System.out.println("FAIL - no fourth result set");
174         allRS[3] = cs.getResultSet();
175         // first, third and fourth open, second closed
176 showResultSetStatus(allRS);
177
178         System.out.println(" fifth with CLOSE_ALL_RESULTS");
179         moreRS = cs.getMoreResults(Statement.CLOSE_ALL_RESULTS);
180         if (!moreRS)
181             System.out.println("FAIL - no fifth result set");
182         allRS[4] = cs.getResultSet();
183         // only fifth open
184 showResultSetStatus(allRS);
185
186         System.out.println(" no more results with with KEEP_CURRENT_RESULT");
187         moreRS = cs.getMoreResults(Statement.KEEP_CURRENT_RESULT);
188         if (moreRS)
189             System.out.println("FAIL - too many result sets");
190         // only fifth open
191 showResultSetStatus(allRS);
192         allRS[4].close();
193         java.util.Arrays.fill(allRS, null);
194
195         System.out.println("\n\nFetching result sets with getMoreResults(Statement.KEEP_CURRENT_RESULT) and checking that cs.execute() closes them");
196         pass = 0;
197         cs.execute();
198         do {
199
200             allRS[pass++] = cs.getResultSet();
201             System.out.println(" PASS " + pass + " got result set " + (allRS[pass -1] != null));
202             // expect everything to stay open.
203 showResultSetStatus(allRS);
204
205         } while (cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
206         System.out.println(" fetched all results");
207         // All should still be open.
208 showResultSetStatus(allRS);
209         System.out.println(" executing statement");
210         cs.execute();
211         // all should be closed.
212 showResultSetStatus(allRS);
213         java.util.Arrays.fill(allRS, null);
214
215
216         System.out.println("\n\nFetching result sets with getMoreResults(Statement.KEEP_CURRENT_RESULT) and checking that cs.close() closes them");
217         pass = 0;
218         // using execute from above.
219 do {
220
221             allRS[pass++] = cs.getResultSet();
222             System.out.println(" PASS " + pass + " got result set " + (allRS[pass -1] != null));
223             // expect everything to stay open.
224 showResultSetStatus(allRS);
225
226         } while (cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
227         System.out.println(" fetched all results");
228         // All should still be open.
229 showResultSetStatus(allRS);
230         System.out.println(" closing statement");
231         cs.close();
232         // all should be closed.
233 showResultSetStatus(allRS);
234         java.util.Arrays.fill(allRS, null);
235
236     }
237
238     private static void showResultSetStatus(ResultSet[] allRS) {
239         for (int i = 0; i < allRS.length; i++) {
240             try {
241                 ResultSet rs = allRS[i];
242                 if (rs == null)
243                     continue;
244                 rs.next();
245                 System.out.println(" RS (" + (i + 1) + ") val " + rs.getInt(1));
246             } catch (SQLException sqle) {
247                 System.out.println(" Exception - " + sqle.getMessage());
248             }
249         }
250     }
251 }
252
Popular Tags