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


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

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags