001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.currentof
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 the current of statements, i.e.
038: delete where current of and update where current of.
039:
040: Not done in ij since the cursor names may not be stable.
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 currentof {
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 currentof 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: testDelete();
065: setup(false);
066: testUpdate();
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: errorPrint(e);
077: }
078:
079: if (passed)
080: System.out.println("PASS");
081: System.out.println("Test currentof finished");
082: }
083:
084: static void errorPrint(Throwable e) {
085: if (e == null)
086: return;
087:
088: e.printStackTrace();
089:
090: if (e instanceof SQLException)
091: errorPrint(((SQLException) e).getNextException());
092: }
093:
094: static void setup(boolean first) throws SQLException {
095: Statement stmt = conn.createStatement();
096:
097: if (first) {
098: verifyCount(
099: "create table t (i int, c char(50))",
100: stmt
101: .executeUpdate("create table t (i int, c char(50))"),
102: 0);
103:
104: verifyCount(
105: "create table s (i int, c char(50))",
106: stmt
107: .executeUpdate("create table s (i int, c char(50))"),
108: 0);
109: } else {
110: verifyBoolean(stmt.execute("delete from t"), false);
111: }
112:
113: verifyCount(
114: "insert into t values (1956, 'hello world')",
115: stmt
116: .executeUpdate("insert into t values (1956, 'hello world')"),
117: 1);
118:
119: verifyCount(
120: "insert into t values (456, 'hi yourself')",
121: stmt
122: .executeUpdate("insert into t values (456, 'hi yourself')"),
123: 1);
124:
125: verifyCount(
126: "insert into t values (180, 'rubber ducky')",
127: stmt
128: .executeUpdate("insert into t values (180, 'rubber ducky')"),
129: 1);
130:
131: verifyCount(
132: "insert into t values (3, 'you are the one')",
133: stmt
134: .executeUpdate("insert into t values (3, 'you are the one')"),
135: 1);
136:
137: stmt.close();
138:
139: System.out.println("PASS: setup complete");
140: }
141:
142: static void teardown() throws SQLException {
143: Statement stmt = conn.createStatement();
144:
145: verifyCount("drop table t", stmt.executeUpdate("drop table t"),
146: 0);
147:
148: verifyCount("drop table s", stmt.executeUpdate("drop table s"),
149: 0);
150:
151: stmt.close();
152:
153: System.out.println("PASS: teardown complete");
154: }
155:
156: static void verifyCount(String text, int count, int expect)
157: throws SQLException {
158: if (count != expect) {
159: System.out.println("FAIL: Expected " + expect + " got "
160: + count + " rows on stmt: " + text);
161: throw new SQLException("Wrong number of rows returned");
162: } else
163: System.out.println("PASS: expected and got " + count
164: + (count == 1 ? " row" : " rows") + "on stmt: "
165: + text);
166: }
167:
168: static void verifyBoolean(boolean got, boolean expect)
169: throws SQLException {
170: if (got != expect) {
171: System.out.println("FAIL: Expected " + expect + " got "
172: + got);
173: throw new SQLException("Wrong boolean returned");
174: } else
175: System.out.println("PASS: expected and got " + got);
176: }
177:
178: static int countRows(String query) throws SQLException {
179: Statement select = conn.createStatement();
180: ResultSet counter = select.executeQuery(query);
181: int count = 0;
182:
183: while (counter.next()) {
184: count++;
185: System.out.println("Row: " + counter.getInt(1) + ","
186: + counter.getString(2));
187: }
188: counter.close();
189: select.close();
190:
191: return count;
192: }
193:
194: static void nextRow(ResultSet r) throws SQLException {
195: verifyBoolean(r.next(), true);
196: System.out
197: .println("Row: " + r.getInt(1) + "," + r.getString(2));
198: }
199:
200: static void testDelete() throws SQLException {
201: PreparedStatement select, delete;
202: Statement select2, delete2;
203: ResultSet cursor;
204: int startCount, endCount;
205: boolean caught;
206:
207: startCount = countRows("select i, c from t for read only");
208: System.out.println("Have " + startCount
209: + " rows in table at start");
210:
211: // because there is no order by (nor can there be)
212: // the fact that this test prints out rows may someday
213: // be a problem. When that day comes, the row printing
214: // can (should) be removed from this test.
215:
216: select = conn.prepareStatement("select i, c from t for update");
217: cursor = select.executeQuery(); // cursor is now open
218:
219: // would like to test a delete attempt before the cursor
220: // is open, but finagling to get the cursor name would
221: // destroy the spirit of the rest of the tests,
222: // which want to operate against the generated name.
223:
224: // TEST: cursor and target table mismatch
225: caught = false;
226: try {
227: delete = conn
228: .prepareStatement("delete from s where current of "
229: + cursor.getCursorName());
230: } catch (SQLException se) {
231:
232: String m = se.getSQLState();
233: JDBCDisplayUtil.ShowSQLException(System.out, se);
234:
235: if ("42X28".equals(m)) {
236: caught = true;
237: System.out
238: .println("PASS: delete table and cursor table mismatch caught");
239: } else {
240: System.out
241: .println("MAYBE FAIL: delete table and cursor table mismatch got unexpected exception");
242: }
243: } finally {
244: if (!caught)
245: System.out
246: .println("FAIL: delete table and cursor table mismatch not caught");
247: }
248:
249: // TEST: find the cursor during compilation
250: delete = conn
251: .prepareStatement("delete from t where current of "
252: + cursor.getCursorName());
253:
254: // TEST: delete before the cursor is on a row
255: caught = false;
256: try {
257: delete.executeUpdate();
258: } catch (SQLException se) {
259: String m = se.getSQLState();
260: JDBCDisplayUtil.ShowSQLException(System.out, se);
261: if ("24000".equals(m)) {
262: caught = true;
263: System.out
264: .println("PASS: Attempt to delete cursor before first row caught");
265: } else {
266: System.out.println("...surprise error " + se);
267: throw se;
268: }
269: } finally {
270: if (!caught)
271: System.out
272: .println("FAIL: No error from delete on cursor before first row");
273: }
274:
275: // TEST: find the cursor during execution and it is on a row
276: nextRow(cursor);
277: verifyCount("delete, ok", delete.executeUpdate(), 1);
278:
279: // TEST: delete an already deleted row; expect an error
280: // expect second delete to throw a no current row exception
281: // REMIND: currently it is ugly, hopefully it will get better.
282: caught = false;
283: /* try {
284: verifyCount("<delete cursor on deleted row>", delete.executeUpdate(),
285: 0);
286: } catch (SQLException se) {
287: // will replace this with SQLState check some day,
288: // at present this is a temporary message expectation.
289: String m = se.getMessage();
290: if (m.startsWith("\"Record ") && m.endsWith(" not found.\"")) {
291: caught = true;
292: System.out.println("PASS: Attempt to repeat delete did not find row");
293: } else {
294: throw se;
295: }
296: } finally {
297: if (! caught)
298: System.out.println("FAIL: No error from repeat delete");
299: }*/
300:
301: // skip a row and delete another row so that two rows will
302: // have been removed from the table when we are done.
303: nextRow(cursor); // skip this row
304: nextRow(cursor);
305:
306: verifyCount("<delete after skipping>", delete.executeUpdate(),
307: 1);
308:
309: // TEST: delete past the last row
310: nextRow(cursor); // skip this row
311: verifyBoolean(cursor.next(), false); // past last row now
312: caught = false;
313: try {
314: delete.executeUpdate(); // no current row / closed
315: } catch (SQLException se) {
316: String m = se.getSQLState();
317: JDBCDisplayUtil.ShowSQLException(System.out, se);
318: if ("24000".equals(m)) {
319: caught = true;
320: System.out
321: .println("PASS: Attempt to delete cursor past last row caught");
322: } else {
323: throw se;
324: }
325: } finally {
326: if (!caught)
327: System.out
328: .println("FAIL: No error from delete on cursor past last row");
329: }
330:
331: // TEST: delete off a closed cursor
332: // Once this is closed then the cursor no longer exists.
333: cursor.close();
334: caught = false;
335: try {
336: delete.executeUpdate();
337: } catch (SQLException se) {
338: String m = se.getSQLState();
339: JDBCDisplayUtil.ShowSQLException(System.out, se);
340: if ("XCL07".equals(se.getSQLState())) {
341: caught = true;
342: System.out
343: .println("PASS: Attempt to delete closed cursor caught");
344: }
345: if ("42X30".equals(se.getSQLState())) {
346: caught = true;
347: System.out
348: .println("PASS: Attempt to delete closed cursor caught");
349: }
350: if (!caught)
351: throw se;
352: } finally {
353: if (!caught)
354: System.out
355: .println("FAIL: No error from delete on closed cursor");
356: }
357:
358: endCount = countRows("select i, c from t for read only");
359: System.out
360: .println("Have " + endCount + " rows in table at end");
361:
362: verifyCount("startCount-endCount", startCount - endCount, 2);
363:
364: // TEST: no cursor with that name exists
365: delete2 = conn.createStatement();
366: caught = false;
367: try {
368: delete2
369: .execute("delete from t where current of nosuchcursor");
370: } catch (SQLException se) {
371: String m = se.getSQLState();
372: JDBCDisplayUtil.ShowSQLException(System.out, se);
373: if ("42X30".equals(m)) {
374: caught = true;
375: System.out
376: .println("PASS: Attempt to delete nonexistent cursor caught");
377: } else {
378: throw se;
379: }
380: } finally {
381: if (!caught)
382: System.out
383: .println("FAIL: No error from delete on nonexistent cursor");
384: }
385:
386: delete.close();
387: delete2.close();
388: select.close();
389:
390: // TEST: attempt to do positioned delete before cursor execute'd
391: // TBD
392:
393: System.out.println("PASS: delete test complete");
394: }
395:
396: static void testUpdate() throws SQLException {
397: PreparedStatement select = null;
398: PreparedStatement update = null;
399: Statement select2, update2;
400: ResultSet cursor = null;
401: int startCount, endCount;
402: boolean caught;
403:
404: // these are basic tests without a where clause on the select.
405: // all rows are in and stay in the cursor's set when updated.
406:
407: // because there is no order by (nor can there be)
408: // the fact that this test prints out rows may someday
409: // be a problem. When that day comes, the row printing
410: // can (should) be removed from this test.
411:
412: endCount = countRows("select i, c from t for read only");
413: System.out.println("Have " + endCount
414: + " rows in table at start");
415:
416: // TEST: Updated column not found in for update of list
417: caught = false;
418: try {
419: select = conn
420: .prepareStatement("select I, C from t for update of I");
421: cursor = select.executeQuery(); // cursor is now open
422: update = conn
423: .prepareStatement("update t set C = 'abcde' where current of "
424: + cursor.getCursorName());
425: } catch (SQLException se) {
426: String m = se.getSQLState();
427: JDBCDisplayUtil.ShowSQLException(System.out, se);
428: if ("42X31".equals(m)) {
429: caught = true;
430: System.out
431: .println("PASS: update of non-existant column caught");
432: } else {
433: throw se;
434: }
435: } finally {
436: if (!caught)
437: System.out
438: .println("FAIL: update of non-existant column not caught");
439: }
440: cursor.close();
441: select.close();
442:
443: // TEST: Update of cursor declared READ ONLY
444: caught = false;
445: try {
446: select = conn
447: .prepareStatement("select I, C from t for read only");
448: cursor = select.executeQuery(); // cursor is now open
449: if (cursor.getCursorName() == null) {
450: caught = true;
451: System.out
452: .println("PASS: update of read-only cursor caught");
453: }
454: } catch (SQLException se) {
455: String m = se.getSQLState();
456: JDBCDisplayUtil.ShowSQLException(System.out, se);
457: throw se;
458: } finally {
459: if (!caught)
460: System.out
461: .println("FAIL: update of read-only cursor not caught");
462: }
463: cursor.close();
464: select.close();
465:
466: // TEST: Update of cursor declared FETCH ONLY
467: caught = false;
468: try {
469: select = conn
470: .prepareStatement("select I, C from t for fetch only");
471: cursor = select.executeQuery(); // cursor is now open
472: if (cursor.getCursorName() == null) {
473: caught = true;
474: System.out
475: .println("PASS: update of fetch-only cursor caught");
476: }
477: } catch (SQLException se) {
478: String m = se.getSQLState();
479: JDBCDisplayUtil.ShowSQLException(System.out, se);
480: throw se;
481: } finally {
482: if (!caught)
483: System.out
484: .println("FAIL: update of fetch-only cursor not caught");
485: }
486: cursor.close();
487: select.close();
488:
489: // TEST: Update of cursor with a union
490: caught = false;
491: try {
492: select = conn
493: .prepareStatement("select I, C from t union all select I, C from t");
494: cursor = select.executeQuery(); // cursor is now open
495: if (cursor.getCursorName() == null) {
496: System.out
497: .println("PASS: update of union cursor caught");
498: caught = true;
499: }
500: } catch (SQLException se) {
501: JDBCDisplayUtil.ShowSQLException(System.out, se);
502: String m = se.getSQLState();
503: throw se;
504: } finally {
505: if (!caught)
506: System.out
507: .println("FAIL: update of union cursor not caught");
508: }
509: cursor.close();
510: select.close();
511:
512: // TEST: Update of cursor with a join
513: caught = false;
514: try {
515: select = conn
516: .prepareStatement("select t1.I, t1.C from t t1, t t2 where t1.I = t2.I");
517: cursor = select.executeQuery(); // cursor is now open
518: if (cursor.getCursorName() == null) {
519: System.out
520: .println("PASS: update of join cursor caught");
521: caught = true;
522: }
523: } catch (SQLException se) {
524: String m = se.getSQLState();
525: JDBCDisplayUtil.ShowSQLException(System.out, se);
526: throw se;
527: } finally {
528: if (!caught)
529: System.out
530: .println("FAIL: update of join cursor not caught");
531: }
532: cursor.close();
533: select.close();
534:
535: // TEST: Update of cursor with a derived table
536: caught = false;
537: try {
538: select = conn
539: .prepareStatement("select I, C from (select * from t) t1");
540: cursor = select.executeQuery(); // cursor is now open
541: if (cursor.getCursorName() == null) {
542: System.out
543: .println("PASS: update of derived table cursor caught");
544: caught = true;
545: }
546: } catch (SQLException se) {
547: String m = se.getSQLState();
548: JDBCDisplayUtil.ShowSQLException(System.out, se);
549: throw se;
550: } finally {
551: if (!caught)
552: System.out
553: .println("FAIL: update of derived table cursor not caught");
554: }
555: cursor.close();
556: select.close();
557:
558: // TEST: Update of cursor with a values clause
559: caught = false;
560: try {
561: select = conn.prepareStatement("values (1, 2, 3)");
562: cursor = select.executeQuery(); // cursor is now open
563: if (cursor.getCursorName() == null) {
564: caught = true;
565: System.out
566: .println("PASS: update of values clause cursor caught");
567: }
568: } catch (SQLException se) {
569: String m = se.getSQLState();
570: JDBCDisplayUtil.ShowSQLException(System.out, se);
571: throw se;
572: } finally {
573: if (!caught)
574: System.out
575: .println("FAIL: update of values clause cursor not caught");
576: }
577: cursor.close();
578: select.close();
579:
580: // TEST: Update of cursor with a subquery
581: caught = false;
582: try {
583: select = conn
584: .prepareStatement("select I, C from t where I in (select I from t)");
585: cursor = select.executeQuery(); // cursor is now open
586: if (cursor.getCursorName() == null) {
587: caught = true;
588: System.out
589: .println("PASS: update of subquery cursor caught");
590: }
591: } catch (SQLException se) {
592: JDBCDisplayUtil.ShowSQLException(System.out, se);
593: throw se;
594: } finally {
595: if (!caught)
596: System.out
597: .println("FAIL: update of subquery cursor not caught");
598: }
599: cursor.close();
600: select.close();
601:
602: select = conn.prepareStatement("select I, C from t for update");
603: cursor = select.executeQuery(); // cursor is now open
604:
605: // would like to test a update attempt before the cursor
606: // is open, but finagling to get the cursor name would
607: // destroy the spirit of the rest of the tests,
608: // which want to operate against the generated name.
609:
610: // TEST: cursor and target table mismatch
611: caught = false;
612:
613: try {
614: update = conn
615: .prepareStatement("update s set i=1 where current of "
616: + cursor.getCursorName());
617: } catch (SQLException se) {
618: JDBCDisplayUtil.ShowSQLException(System.out, se);
619: String m = se.getSQLState();
620: if ("42X29".equals(m)) {
621: caught = true;
622: System.out
623: .println("PASS: update table and cursor table mismatch caught");
624: } else {
625: throw se;
626: }
627: } finally {
628: if (!caught)
629: System.out
630: .println("FAIL: update table and cursor table mismatch not caught");
631: }
632: // TEST: find the cursor during compilation
633: update = conn
634: .prepareStatement("update t set i=i+10, c='Gumby was here' where current of "
635: + cursor.getCursorName());
636:
637: // TEST: update before the cursor is on a row
638: caught = false;
639: try {
640: verifyCount("update before the cursor", update
641: .executeUpdate(), 0); // no current row / closed
642: } catch (SQLException se) {
643: String m = se.getSQLState();
644: JDBCDisplayUtil.ShowSQLException(System.out, se);
645: if ("24000".equals(m)) {
646: caught = true;
647: System.out
648: .println("PASS: Attempt to update cursor before first row caught");
649: } else {
650: throw se;
651: }
652: } finally {
653: if (!caught)
654: System.out
655: .println("FAIL: No error from update on cursor before first row");
656: }
657:
658: // TEST: find the cursor during execution and it is on a row
659: nextRow(cursor);
660: verifyCount("update on row", update.executeUpdate(), 1);
661:
662: // TEST: update an already updated row; expect it to succeed.
663: // will it have a cumulative effect?
664: verifyCount("2nd update on row", update.executeUpdate(), 1);
665:
666: // skip a row and update another row so that two rows will
667: // have been removed from the table when we are done.
668: nextRow(cursor); // skip this row
669: nextRow(cursor);
670:
671: verifyCount("update after skipping", update.executeUpdate(), 1);
672:
673: // TEST: update past the last row
674: nextRow(cursor); // skip this row
675: verifyBoolean(cursor.next(), false); // past last row now
676: caught = false;
677: try {
678: verifyCount("update: no current row", update
679: .executeUpdate(), 0); // no current row / closed
680: } catch (SQLException se) {
681: String m = se.getSQLState();
682: JDBCDisplayUtil.ShowSQLException(System.out, se);
683: if ("24000".equals(m)) {
684: caught = true;
685: System.out
686: .println("PASS: Attempt to update cursor past last row caught");
687: } else {
688: throw se;
689: }
690: } finally {
691: if (!caught)
692: System.out
693: .println("FAIL: No error from update on cursor past last row");
694: }
695:
696: // TEST: update off a closed cursor
697: cursor.close();
698: select.close();
699: caught = false;
700: try {
701: verifyCount("update on closed cursor", update
702: .executeUpdate(), 0);
703: } catch (SQLException se) {
704: String m = se.getSQLState();
705: JDBCDisplayUtil.ShowSQLException(System.out, se);
706: if ("XCL07".equals(m)) {
707: caught = true;
708: System.out
709: .println("PASS: Attempt to update closed cursor caught");
710: }
711: if ("42X30".equals(m)) {
712: caught = true;
713: System.out
714: .println("PASS: Attempt to update closed cursor caught");
715: }
716:
717: if (!caught) {
718: throw se;
719: }
720: } finally {
721: if (!caught)
722: System.out
723: .println("FAIL: No error from update on closed cursor");
724: }
725: update.close();
726:
727: // TEST: no cursor with that name exists
728: update2 = conn.createStatement();
729: caught = false;
730: try {
731: update2
732: .execute("update t set i=1 where current of nosuchcursor");
733: } catch (SQLException se) {
734: String m = se.getSQLState();
735: JDBCDisplayUtil.ShowSQLException(System.out, se);
736: if ("42X30".equals(m)) {
737: caught = true;
738: System.out
739: .println("PASS: Attempt to update nonexistent cursor caught");
740: } else {
741: throw se;
742: }
743: } finally {
744: if (!caught)
745: System.out
746: .println("FAIL: No error from update on nonexistent cursor");
747: }
748:
749: endCount = countRows("select i, c from t for read only");
750: System.out
751: .println("Have " + endCount + " rows in table at end");
752:
753: // TEST: attempt to do positioned update before cursor execute'd
754: // TBD
755:
756: // TEST closing a cursor will close the related update
757: bug4395(conn, "CS4395"); // Application provided cursor name
758: bug4395(conn, null); // system provided cursor name
759:
760: System.out.println("PASS: update test complete");
761: }
762:
763: private static void bug4395(Connection conn, String cursorName)
764: throws SQLException {
765:
766: System.out.println("bug4395 Cursor Name "
767: + (cursorName == null ? "System Generated"
768: : "Application Defined"));
769:
770: PreparedStatement select = conn
771: .prepareStatement("select I, C from t for update");
772: if (cursorName != null)
773: select.setCursorName(cursorName);
774:
775: ResultSet cursor = select.executeQuery(); // cursor is now open
776: // TEST: find the cursor during compilation
777: cursorName = cursor.getCursorName();
778: PreparedStatement update = conn
779: .prepareStatement("update t set i=i+?, c=? where current of "
780: + cursorName);
781:
782: nextRow(cursor);
783: update.setInt(1, 10);
784: update.setString(2, "Dan was here");
785: verifyCount("update: valid update", update.executeUpdate(), 1);
786: cursor.close();
787:
788: // now prepare the a cursor with the same name but only column I for update
789: PreparedStatement selectdd = conn
790: .prepareStatement("select I, C from t for update of I");
791: selectdd.setCursorName(cursorName);
792: cursor = selectdd.executeQuery();
793: nextRow(cursor);
794:
795: try {
796: update.setInt(1, 7);
797: update.setString(2, "no update");
798: update.executeUpdate();
799: System.out
800: .println("FAIL update succeeded after cursor has been changed");
801: } catch (SQLException se) {
802: String m = se.getSQLState();
803: JDBCDisplayUtil.ShowSQLException(System.out, se);
804: if ("42X31".equals(m)) {
805: System.out
806: .println("PASS: Attempt to update changed invalid cursor caught");
807: } else {
808: throw se;
809: }
810: }
811:
812: cursor.close();
813: cursor = selectdd.executeQuery();
814: nextRow(cursor);
815: cursor.close();
816:
817: }
818: }
|