|
1
21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23
24 import java.sql.Connection  ;
25 import java.sql.DriverManager ;
26 import java.sql.Statement ;
27 import java.sql.PreparedStatement ;
28 import java.sql.ResultSet ;
29 import java.sql.ResultSetMetaData ;
30 import java.sql.SQLException ;
31 import java.sql.SQLWarning ;
32
33 import org.apache.derby.tools.ij;
34 import org.apache.derby.tools.JDBCDisplayUtil;
35
36
46
47 public class cursor {
48
49 private static Connection conn;
50 private static boolean passed = false;
51
52 public static void main(String [] args) {
53 System.out.println("Test cursor starting");
54
55 try {
56 ij.getPropertyArg(args);
59 conn = ij.startJBMS();
60
61 conn.setAutoCommit(false);
62
63 setup(true);
64 testCursor();
65 testCursorParam();
66 testgetCursorName();
67 teardown();
68
69 conn.commit();
70 conn.close();
71
72 passed = true;
73
74 } catch (Throwable e) {
75 System.out.println("FAIL: exception thrown:");
76 passed = false;
77 JDBCDisplayUtil.ShowException(System.out,e);
78 }
79
80 if (passed)
81 System.out.println("PASS");
82 System.out.println("Test cursor finished");
83 }
84
85 static void setup(boolean first) throws SQLException {
86 Statement stmt = conn.createStatement();
87
88 if (first) {
89 verifyCount(
90 stmt.executeUpdate("create table t (i int, c char(50))"),
91 0);
92
93 verifyCount(
94 stmt.executeUpdate("create table s (i int, c char(50))"),
95 0);
96 } else {
97 verifyBoolean(
98 stmt.execute("delete from t"),
99 false);
100 }
101
102 verifyCount(
103 stmt.executeUpdate("insert into t values (1956, 'hello world')"),
104 1);
105
106 verifyCount(
107 stmt.executeUpdate("insert into t values (456, 'hi yourself')"),
108 1);
109
110 verifyCount(
111 stmt.executeUpdate("insert into t values (180, 'rubber ducky')"),
112 1);
113
114 verifyCount(
115 stmt.executeUpdate("insert into t values (3, 'you are the one')"),
116 1);
117
118 stmt.close();
119
120 System.out.println("PASS: setup complete");
121 }
122
123
124 static void teardown() throws SQLException {
125 Statement stmt = conn.createStatement();
126
127 verifyCount(
128 stmt.executeUpdate("drop table t"),
129 0);
130
131 verifyCount(
132 stmt.executeUpdate("drop table s"),
133 0);
134
135 stmt.close();
136
137 System.out.println("PASS: teardown complete");
138 }
139
140 static void verifyCount(int count, int expect) throws SQLException {
141 if (count!=expect) {
142 System.out.println("FAIL: Expected "+expect+" got "+count+" rows");
143 throw new SQLException ("Wrong number of rows returned");
144 }
145 else
146 System.out.println("PASS: expected and got "+count+
147 (count == 1? " row":" rows"));
148 }
149
150 static void verifyBoolean(boolean got, boolean expect) throws SQLException {
151 if (got!=expect) {
152 System.out.println("FAIL: Expected "+expect+" got "+got);
153 throw new SQLException ("Wrong boolean returned");
154 }
155 else
156 System.out.println("PASS: expected and got "+got);
157 }
158
159 static int countRows(String query) throws SQLException {
160 Statement select = conn.createStatement();
161 ResultSet counter = select.executeQuery(query);
162 int count = 0;
163
164 while (counter.next()) {
165 count++;
166 System.out.println("Row: "+counter.getInt(1)+","+counter.getString(2));
167 }
168 select.close();
169
170 return count;
171 }
172
173 static void nextRow(ResultSet r) throws SQLException {
174 verifyBoolean(r.next(), true);
175 System.out.println("Row: "+r.getInt(1)+","+r.getString(2));
176 }
177
178 static boolean ifRow(ResultSet r) throws SQLException {
179 boolean b = r.next();
180
181 if (b)
182 System.out.println("Row: "+r.getInt(1)+","+r.getString(2));
183
184 return b;
185 }
186
187 static void testCursor() throws SQLException {
188 PreparedStatement select, delete;
189 Statement select2, delete2;
190 ResultSet cursor;
191 boolean caught;
192
193
198 select = conn.prepareStatement("select i, c from t for update");
199 cursor = select.executeQuery();
201 nextRow(cursor);
203
204 cursor.close();
206
207 for (int i=0;i<5;i++) {
210 caught = false;
211 try {
212 ifRow(cursor); } catch (SQLException se) {
214 JDBCDisplayUtil.ShowSQLException(System.out,se);
215 caught = true;
216 System.out.println("PASS: Attempt to get next on closed cursor caught");
217 }
218 if (! caught)
219 System.out.println("FAIL: No error from next on closed cursor");
220 }
221
222 cursor = select.executeQuery();
224
225 while (ifRow(cursor)); caught = false;
228 try {
229 boolean b = ifRow(cursor); if (!b) {
231 System.out.println("No current row");
232 caught = true;
233 }
234 } catch (SQLException se) {
235 JDBCDisplayUtil.ShowSQLException(System.out,se);
236 caught = true;
237 System.out.println("PASS: Attempt to get next after end of cursor caught");
238 }
239 if (! caught)
240 System.out.println("FAIL: No error from next past end of cursor");
241
242 System.out.println("PASS: cursor test complete");
243
244 cursor.close();
245 }
246
247 static void testCursorParam() throws SQLException {
248 PreparedStatement select, delete;
249 Statement select2, delete2;
250 ResultSet cursor;
251 boolean caught;
252
253
258 select = conn.prepareStatement("select i, c from t where ?=1 for update");
259 select.setInt(1,1);
260 cursor = select.executeQuery();
262 nextRow(cursor);
264
265 cursor.close();
267
268 for (int i=0;i<5;i++) {
271 caught = false;
272 try {
273 ifRow(cursor); } catch (SQLException se) {
275 JDBCDisplayUtil.ShowSQLException(System.out,se);
276 caught = true;
277 System.out.println("PASS: Attempt to get next on closed cursor caught");
278 }
279 if (! caught)
280 System.out.println("FAIL: No error from next on closed cursor");
281 }
282
283 select.setBoolean(1,false);
285 select.setCursorName("ForCoverageSake");
286 cursor = select.executeQuery();
287
288 if (cursor.getCursorName().equals("ForCoverageSake")) {
289 System.out.println("PASS: cursor name set");
290 }
291 else
292 {
293 System.out.println("FAIL: cursor name not set, is still: "+cursor.getCursorName());
294 }
295
296 caught = false;
298 try {
299 boolean b = ifRow(cursor); if (!b) {
301 System.out.println("No current row");
302 caught = true;
303 }
304 } catch (SQLException se) {
305 JDBCDisplayUtil.ShowSQLException(System.out,se);
306 caught = true;
307 System.out.println("PASS: Attempt to get next after end of cursor caught");
308 }
309 if (! caught)
310 System.out.println("FAIL: No error from next past end of cursor");
311
312 System.out.println("PASS: cursor test complete");
313
314 cursor.close();
315 }
316 static void testgetCursorName() throws SQLException {
317 testCursorName("select * from t", (String )null);
319 testCursorName("select * from t for update", (String )null);
320 testCursorName("select * from t for update of i", (String )null);
321 testCursorName("select * from t", "myselect");
322 testCursorName("select * from t for update", "myselect");
323 testCursorName("select * from t for update of i", "myselect");
324 }
325 static private void testCursorName(String statement, String cursorname) throws SQLException {
326 System.out.println("Test cursor name for " + statement +
327 " Cursor name = " + cursorname);
328 Statement s = conn.createStatement();
329 if (cursorname != null)
330 s.setCursorName(cursorname);
331 ResultSet rs = s.executeQuery(statement);
332 if (rs != null)
333 {
334 String cursorName = rs.getCursorName();
335 System.out.println("cursor name = " + cursorName);
336 }
337 rs.close();
338 s.close();
339
340 }
341
342 }
343 |
Popular Tags |
Java API By Example, From Geeks To Geeks.