001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.cursor
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.Connection;
025: import java.sql.DriverManager;
026: import java.sql.Statement;
027: import java.sql.PreparedStatement;
028: import java.sql.ResultSet;
029: import java.sql.ResultSetMetaData;
030: import java.sql.SQLException;
031: import java.sql.SQLWarning;
032:
033: import org.apache.derby.tools.ij;
034: import org.apache.derby.tools.JDBCDisplayUtil;
035:
036: /**
037: This tests cursor handling
038:
039: Not done in ij since the cursor names may not be stable,
040: and we want to control navigation through the cursor rows.
041:
042: This could be more complete, but since this is SQL92 Entry
043: standard, we are assuming that some future purchase of the
044: NIST suite or some equivalent will suffice.
045: */
046:
047: public class cursor {
048:
049: private static Connection conn;
050: private static boolean passed = false;
051:
052: public static void main(String[] args) {
053: System.out.println("Test cursor starting");
054:
055: try {
056: // use the ij utility to read the property file and
057: // make the initial connection.
058: ij.getPropertyArg(args);
059: conn = ij.startJBMS();
060:
061: conn.setAutoCommit(false);
062:
063: setup(true);
064: testCursor();
065: testCursorParam();
066: testgetCursorName();
067: teardown();
068:
069: conn.commit();
070: conn.close();
071:
072: passed = true;
073:
074: } catch (Throwable e) {
075: System.out.println("FAIL: exception thrown:");
076: passed = false;
077: JDBCDisplayUtil.ShowException(System.out, e);
078: }
079:
080: if (passed)
081: System.out.println("PASS");
082: System.out.println("Test cursor finished");
083: }
084:
085: static void setup(boolean first) throws SQLException {
086: Statement stmt = conn.createStatement();
087:
088: if (first) {
089: verifyCount(
090: stmt
091: .executeUpdate("create table t (i int, c char(50))"),
092: 0);
093:
094: verifyCount(
095: stmt
096: .executeUpdate("create table s (i int, c char(50))"),
097: 0);
098: } else {
099: verifyBoolean(stmt.execute("delete from t"), false);
100: }
101:
102: verifyCount(
103: stmt
104: .executeUpdate("insert into t values (1956, 'hello world')"),
105: 1);
106:
107: verifyCount(
108: stmt
109: .executeUpdate("insert into t values (456, 'hi yourself')"),
110: 1);
111:
112: verifyCount(
113: stmt
114: .executeUpdate("insert into t values (180, 'rubber ducky')"),
115: 1);
116:
117: verifyCount(
118: stmt
119: .executeUpdate("insert into t values (3, 'you are the one')"),
120: 1);
121:
122: stmt.close();
123:
124: System.out.println("PASS: setup complete");
125: }
126:
127: static void teardown() throws SQLException {
128: Statement stmt = conn.createStatement();
129:
130: verifyCount(stmt.executeUpdate("drop table t"), 0);
131:
132: verifyCount(stmt.executeUpdate("drop table s"), 0);
133:
134: stmt.close();
135:
136: System.out.println("PASS: teardown complete");
137: }
138:
139: static void verifyCount(int count, int expect) throws SQLException {
140: if (count != expect) {
141: System.out.println("FAIL: Expected " + expect + " got "
142: + count + " rows");
143: throw new SQLException("Wrong number of rows returned");
144: } else
145: System.out.println("PASS: expected and got " + count
146: + (count == 1 ? " row" : " rows"));
147: }
148:
149: static void verifyBoolean(boolean got, boolean expect)
150: throws SQLException {
151: if (got != expect) {
152: System.out.println("FAIL: Expected " + expect + " got "
153: + got);
154: throw new SQLException("Wrong boolean returned");
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) + ","
167: + counter.getString(2));
168: }
169: select.close();
170:
171: return count;
172: }
173:
174: static void nextRow(ResultSet r) throws SQLException {
175: verifyBoolean(r.next(), true);
176: System.out
177: .println("Row: " + r.getInt(1) + "," + r.getString(2));
178: }
179:
180: static boolean ifRow(ResultSet r) throws SQLException {
181: boolean b = r.next();
182:
183: if (b)
184: System.out.println("Row: " + r.getInt(1) + ","
185: + r.getString(2));
186:
187: return b;
188: }
189:
190: static void testCursor() throws SQLException {
191: PreparedStatement select, delete;
192: Statement select2, delete2;
193: ResultSet cursor;
194: boolean caught;
195:
196: // because there is no order by (nor can there be)
197: // the fact that this test prints out rows may someday
198: // be a problem. When that day comes, the row printing
199: // can (should) be removed from this test.
200:
201: select = conn.prepareStatement("select i, c from t for update");
202: cursor = select.executeQuery(); // cursor is now open
203:
204: // TEST: fetch of a row works
205: nextRow(cursor);
206:
207: // TEST: close and then fetch gets error on fetch.
208: cursor.close();
209:
210: // bang away on the nexts for a little while,
211: // see what the error quiets out to...
212: for (int i = 0; i < 5; i++) {
213: caught = false;
214: try {
215: ifRow(cursor); // no current row / closed
216: } catch (SQLException se) {
217: JDBCDisplayUtil.ShowSQLException(System.out, se);
218: caught = true;
219: System.out
220: .println("PASS: Attempt to get next on closed cursor caught");
221: }
222: if (!caught)
223: System.out
224: .println("FAIL: No error from next on closed cursor");
225: }
226:
227: // restart the query for another test.
228: cursor = select.executeQuery();
229:
230: // TEST: next past the end of the table.
231: while (ifRow(cursor))
232: ; // keep going to the last row
233: caught = false;
234: try {
235: boolean b = ifRow(cursor); // no current row / closed (past the last row)
236: if (!b) {
237: System.out.println("No current row");
238: caught = true;
239: }
240: } catch (SQLException se) {
241: JDBCDisplayUtil.ShowSQLException(System.out, se);
242: caught = true;
243: System.out
244: .println("PASS: Attempt to get next after end of cursor caught");
245: }
246: if (!caught)
247: System.out
248: .println("FAIL: No error from next past end of cursor");
249:
250: System.out.println("PASS: cursor test complete");
251:
252: cursor.close();
253: }
254:
255: static void testCursorParam() throws SQLException {
256: PreparedStatement select, delete;
257: Statement select2, delete2;
258: ResultSet cursor;
259: boolean caught;
260:
261: // because there is no order by (nor can there be)
262: // the fact that this test prints out rows may someday
263: // be a problem. When that day comes, the row printing
264: // can (should) be removed from this test.
265:
266: select = conn
267: .prepareStatement("select i, c from t where ?=1 for update");
268: select.setInt(1, 1);
269: cursor = select.executeQuery(); // cursor is now open
270:
271: // TEST: fetch of a row works
272: nextRow(cursor);
273:
274: // TEST: close and then fetch gets error on fetch.
275: cursor.close();
276:
277: // bang away on the nexts for a little while,
278: // see what the error quiets out to...
279: for (int i = 0; i < 5; i++) {
280: caught = false;
281: try {
282: ifRow(cursor); // no current row / closed
283: } catch (SQLException se) {
284: JDBCDisplayUtil.ShowSQLException(System.out, se);
285: caught = true;
286: System.out
287: .println("PASS: Attempt to get next on closed cursor caught");
288: }
289: if (!caught)
290: System.out
291: .println("FAIL: No error from next on closed cursor");
292: }
293:
294: // restart the query for another test.
295: select.setBoolean(1, false);
296: select.setCursorName("ForCoverageSake");
297: cursor = select.executeQuery();
298:
299: if (cursor.getCursorName().equals("ForCoverageSake")) {
300: System.out.println("PASS: cursor name set");
301: } else {
302: System.out.println("FAIL: cursor name not set, is still: "
303: + cursor.getCursorName());
304: }
305:
306: // TEST: next past the end of the table -- expect no rows at all.
307: caught = false;
308: try {
309: boolean b = ifRow(cursor); // no current row / closed (past the last row)
310: if (!b) {
311: System.out.println("No current row");
312: caught = true;
313: }
314: } catch (SQLException se) {
315: JDBCDisplayUtil.ShowSQLException(System.out, se);
316: caught = true;
317: System.out
318: .println("PASS: Attempt to get next after end of cursor caught");
319: }
320: if (!caught)
321: System.out
322: .println("FAIL: No error from next past end of cursor");
323:
324: System.out.println("PASS: cursor test complete");
325:
326: cursor.close();
327: }
328:
329: static void testgetCursorName() throws SQLException {
330: // test cursor name
331: testCursorName("select * from t", (String) null);
332: testCursorName("select * from t for update", (String) null);
333: testCursorName("select * from t for update of i", (String) null);
334: testCursorName("select * from t", "myselect");
335: testCursorName("select * from t for update", "myselect");
336: testCursorName("select * from t for update of i", "myselect");
337: }
338:
339: static private void testCursorName(String statement,
340: String cursorname) throws SQLException {
341: System.out.println("Test cursor name for " + statement
342: + " Cursor name = " + cursorname);
343: Statement s = conn.createStatement();
344: if (cursorname != null)
345: s.setCursorName(cursorname);
346: ResultSet rs = s.executeQuery(statement);
347: if (rs != null) {
348: String cursorName = rs.getCursorName();
349: System.out.println("cursor name = " + cursorName);
350: }
351: rs.close();
352: s.close();
353:
354: }
355:
356: }
|