Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > jdbcapi > SURTest


1 /*
2  *
3  * Derby - Class SURTest
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,
15  * software distributed under the License is distributed on an
16  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
17  * either express or implied. See the License for the specific
18  * language governing permissions and limitations under the License.
19  */
20 package org.apache.derbyTesting.functionTests.tests.jdbcapi;
21 import org.apache.derbyTesting.functionTests.util.SQLStateConstants;
22
23 import java.sql.Connection ;
24 import java.sql.PreparedStatement ;
25 import java.sql.ResultSet ;
26 import java.sql.SQLException ;
27 import java.sql.SQLWarning ;
28 import java.sql.Statement ;
29 import junit.extensions.TestSetup;
30 import junit.framework.Test;
31 import junit.framework.TestSuite;
32 import java.util.Iterator ;
33
34 /**
35  * Tests for variants of scrollable updatable resultsets.
36  *
37  * @author Andreas Korneliussen
38  */
39 public class SURTest extends SURBaseTest {
40     
41     /** Creates a new instance of SURTest */
42     public SURTest(String name) {
43         super(name);
44     }
45
46     /**
47      * Test that you get a warning when specifying a query which is not
48      * updatable and concurrency mode CONCUR_UPDATABLE.
49      * In this case, the query contains an "order by"
50      */
51     public void testConcurrencyModeWarning1()
52         throws SQLException
53     {
54         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
55                                           ResultSet.CONCUR_UPDATABLE);
56         s.setCursorName(getNextCursorName());
57         ResultSet rs = s.executeQuery("select * from t1 order by a");
58         
59         SQLWarning warn = rs.getWarnings();
60         assertEquals("Expected resultset to be read only",
61                      ResultSet.CONCUR_READ_ONLY,
62                      rs.getConcurrency());
63         assertWarning(warn, QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
64         scrollForward(rs);
65         rs.close();
66         s.close();
67     }
68     
69     /**
70      * Test that you get a warning when specifying a query which is not
71      * updatable and concurrency mode CONCUR_UPDATABLE.
72      * In this case, the query contains a join.
73      */
74     public void testConcurrencyModeWarning2()
75         throws SQLException
76     {
77         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
78                                           ResultSet.CONCUR_UPDATABLE);
79         s.setCursorName(getNextCursorName());
80         ResultSet rs = s.executeQuery
81             ("select * from t1 as table1,t1 as table2 where " +
82              "table1.a=table2.a");
83         
84         SQLWarning warn = rs.getWarnings();
85         assertEquals("Expected resultset to be read only",
86                      ResultSet.CONCUR_READ_ONLY,
87                      rs.getConcurrency());
88         assertWarning(warn, QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
89         scrollForward(rs);
90         rs.close();
91         s.close();
92     }
93     
94     /**
95      * Test that you get an exception when specifying update clause
96      * "FOR UPDATE"
97      * along with a query which is not updatable.
98      * In this case, the query contains and order by.
99      */
100     public void testForUpdateException1()
101         throws SQLException
102     {
103         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
104                                           ResultSet.CONCUR_UPDATABLE);
105         try {
106             String queryString =
107                 "select * from t1 order by a for update";
108             s.setCursorName(getNextCursorName());
109             ResultSet rs = s.executeQuery(queryString);
110             
111             assertTrue("Expected query '" + queryString +
112                        "' to fail", false);
113         } catch (SQLException e) {
114             assertEquals("Unexpected SQLState",
115                          FOR_UPDATE_NOT_PERMITTED_SQL_STATE,
116                          e.getSQLState());
117         }
118         rollback();
119         s.close();
120     }
121     
122     /**
123      * Test that you get an exception when specifying update clause
124      * "FOR UPDATE" along with a query which is not updatable.
125      * In this case, the query contains a join
126      */
127     public void testForUpdateException2()
128         throws SQLException
129     {
130         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
131                                           ResultSet.CONCUR_UPDATABLE);
132         try {
133             String queryString =
134                 "select * from t1 as table1,t1 as table2" +
135                 " where table1.a=table2.a for update";
136             s.setCursorName(getNextCursorName());
137             ResultSet rs = s.executeQuery(queryString);
138             
139             assertTrue("Expected query '" + queryString + "' to fail",
140                        false);
141         } catch (SQLException e) {
142             assertEquals("Unexpected SQLState",
143                          FOR_UPDATE_NOT_PERMITTED_SQL_STATE,
144                          e.getSQLState());
145         }
146         rollback();
147         s.close();
148     }
149     
150     /**
151      * Test that you can scroll forward and read all records in the
152      * ResultSet
153      */
154     public void testForwardOnlyReadOnly1()
155         throws SQLException
156     {
157         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
158                                           ResultSet.CONCUR_READ_ONLY);
159         s.setCursorName(getNextCursorName());
160         ResultSet rs = s.executeQuery("select * from t1");
161         
162         scrollForward(rs);
163         rs.close();
164         s.close();
165     }
166     
167     
168     /**
169      * Test that you get an exception if you try to update a ResultSet
170      * with concurrency mode CONCUR_READ_ONLY.
171      */
172     public void testFailOnUpdateOfReadOnlyResultSet1()
173         throws SQLException
174     {
175         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
176                                           ResultSet.CONCUR_READ_ONLY);
177         s.setCursorName(getNextCursorName());
178         ResultSet rs = s.executeQuery("select * from t1");
179         
180         rs.next();
181         assertFailOnUpdate(rs);
182         s.close();
183     }
184     
185     /**
186      * Test that you get an exception when attempting to update a
187      * ResultSet which has been downgraded to a read only ResultSet.
188      */
189     public void testFailOnUpdateOfReadOnlyResultSet2()
190         throws SQLException
191     {
192         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
193                                           ResultSet.CONCUR_UPDATABLE);
194         s.setCursorName(getNextCursorName());
195         ResultSet rs = s.executeQuery("select * from t1 order by id");
196         
197         rs.next();
198         assertFailOnUpdate(rs);
199         s.close();
200     }
201     
202     /**
203      * Test that you get an exception when attempting to update a
204      * ResultSet which has been downgraded to a read only ResultSet.
205      */
206     public void testFailOnUpdateOfReadOnlyResultSet3()
207         throws SQLException
208     {
209         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
210                                           ResultSet.CONCUR_UPDATABLE);
211         s.setCursorName(getNextCursorName());
212         ResultSet rs =
213             s.executeQuery("select * from t1 for read only");
214         
215         rs.next();
216         assertFailOnUpdate(rs);
217         s.close();
218     }
219     
220     /**
221      * Test that you get an exception when attempting to update a
222      * ResultSet which has been downgraded to a read only ResultSet.
223      */
224     public void testFailOnUpdateOfReadOnlyResultSet4()
225         throws SQLException
226     {
227         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
228                                           ResultSet.CONCUR_UPDATABLE);
229         s.setCursorName(getNextCursorName());
230         ResultSet rs = s.executeQuery
231             ("select * from t1 where a=1 for read only");
232         
233         rs.next();
234         verifyTuple(rs);
235         assertFailOnUpdate(rs);
236         s.close();
237     }
238     
239     
240     /**
241      * Test that you get an exception if you try to update a ResultSet
242      * with concurrency mode CONCUR_READ_ONLY.
243      */
244     public void testFailOnUpdateOfReadOnlyResultSet5()
245         throws SQLException
246     {
247         Statement s = createStatement(ResultSet.
248                                           TYPE_SCROLL_INSENSITIVE,
249                                           ResultSet.CONCUR_READ_ONLY);
250         s.setCursorName(getNextCursorName());
251         ResultSet rs = s.executeQuery
252             ("select * from t1 where a=1 for read only");
253         
254         rs.next();
255         verifyTuple(rs);
256         assertFailOnUpdate(rs);
257         s.close();
258     }
259
260     /**
261      * Test that when doing an update immediately after
262      * a commit, the update fails, because the cursor has been
263      * postioned between the current row and the next row.
264      * The test uses a FORWARD_ONLY resultset and ResultSet update methods
265      * when doing the update.
266      */
267     public void testCursorStateAfterCommit1()
268         throws SQLException
269     {
270         testCursorStateAfterCommit(false, ResultSet.TYPE_FORWARD_ONLY);
271     }
272
273     /**
274      * Test that when doing an update immediately after
275      * a commit, the update fails, because the cursor has been
276      * postioned between the current row and the next row.
277      * The test uses a SCROLL_INSENSITIVE resultset and ResultSet update methods
278      * when doing the update.
279      */
280     public void testCursorStateAfterCommit2()
281         throws SQLException
282     {
283         testCursorStateAfterCommit(false, ResultSet.TYPE_SCROLL_INSENSITIVE);
284     }
285     
286      /**
287      * Test that when doing an update immediately after
288      * a commit, the update fails, because the cursor has been
289      * postioned between the current row and the next row.
290      * The test uses a FORWARD_ONLY resultset and positioned updates.
291      */
292     public void testCursorStateAfterCommit3()
293         throws SQLException
294     {
295         testCursorStateAfterCommit(true, ResultSet.TYPE_FORWARD_ONLY);
296     }
297
298     /**
299      * Test that when doing an update immediately after
300      * a commit, the update fails, because the cursor has been
301      * postioned between the current row and the next row.
302      * The test uses a SCROLL_INSENSITIVE resultset and positioned updates.
303      */
304     public void testCursorStateAfterCommit4()
305         throws SQLException
306     {
307         testCursorStateAfterCommit(true, ResultSet.TYPE_SCROLL_INSENSITIVE);
308     }
309     
310     /**
311      * Test that when doing an update immediately after
312      * a commit, the update fails, because the cursor has been
313      * postioned between the current row and the next row.
314      * If the cursor gets repositioned, it allows an update.
315      * @param positioned true to use positioned update, otherwise use
316      * ResultSet.updateRow()
317      * @param resultSetType type of result set (as in ResultSet.getType())
318      */
319     private void testCursorStateAfterCommit(final boolean positioned,
320                                             final int resultSetType)
321         throws SQLException
322     {
323         final Statement s = createStatement(resultSetType,
324                                                 ResultSet.CONCUR_UPDATABLE);
325         final String cursorName = getNextCursorName();
326         s.setCursorName(cursorName);
327         
328         final ResultSet rs = s.executeQuery("select a from t1");
329         final int recordToUpdate = 5;
330         
331         if (resultSetType==ResultSet.TYPE_FORWARD_ONLY) {
332             for (int i = 0; i < recordToUpdate; i++) {
333                 rs.next();
334             }
335         } else {
336             rs.absolute(recordToUpdate);
337         }
338         
339         commit();
340         
341         PreparedStatement ps =
342             prepareStatement("update t1 set a=? where current of " +
343                                  cursorName);
344         // First: check that we get an exception on update without repositioning:
345 try {
346             if (positioned) {
347                 ps.setInt(1, -1);
348                 ps.executeUpdate();
349                 fail("Expected exception to be thrown on positioned update " +
350                      "since cursor is not positioned");
351             } else {
352                 rs.updateInt(1, -1);
353                 rs.updateRow();
354                 fail("Expected exception to be thrown on updateRow() since " +
355                      "cursor is not positioned");
356             }
357         } catch (SQLException e) {
358             assertSQLState("Unexpected SQLState when updating row after commit",
359                            SQLStateConstants.INVALID_CURSOR_STATE_NO_SUBCLASS,
360                            e);
361         }
362         
363         // Check that we after a repositioning can update:
364 if (resultSetType==ResultSet.TYPE_FORWARD_ONLY) {
365             rs.next();
366         } else {
367             rs.relative(0);
368         }
369         if (positioned) {
370             ps.setInt(1, -1);
371             ps.executeUpdate();
372         } else {
373             rs.updateInt(1, -1);
374             rs.updateRow();
375         }
376         
377         s.close();
378         ps.close();
379         
380     }
381
382     /**
383      * Test that you can correctly run multiple updateXXX() + updateRow()
384      * combined with cancelRowUpdates().
385      */
386     public void testMultiUpdateRow1()
387         throws SQLException
388     {
389         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
390                                           ResultSet.CONCUR_UPDATABLE);
391         s.setCursorName(getNextCursorName());
392         ResultSet rs = s.executeQuery("select * from t1");
393         rs.absolute(5);
394         final int oldCol2 = rs.getInt(2);
395         final int newCol2 = -2222;
396         final int oldCol3 = rs.getInt(3);
397         final int newCol3 = -3333;
398                 
399         rs.updateInt(2, newCol2);
400         assertEquals("Expected the resultset to be updated after updateInt",
401                      newCol2, rs.getInt(2));
402         rs.cancelRowUpdates();
403         assertEquals("Expected updateXXX to have no effect after cancelRowUpdated",
404                      oldCol2, rs.getInt(2));
405         rs.updateInt(2, newCol2);
406         assertEquals("Expected the resultset to be updated after updateInt",
407                      newCol2, rs.getInt(2));
408         assertTrue("Expected rs.rowUpdated() to be false before updateRow",
409                    !rs.rowUpdated());
410         rs.updateRow();
411         
412         assertTrue("Expected rs.rowUpdated() to be true after updateRow",
413                    rs.rowUpdated());
414         assertEquals("Expected the resultset detect the updates of previous " +
415                      "updateRow", newCol2, rs.getInt(2));
416         
417         rs.updateInt(3, newCol3);
418         
419         assertEquals("Expected the resultset to be updated after updateInt",
420                      newCol3, rs.getInt(3));
421         assertEquals("Expected the resultset detect the updates of previous " +
422                      "updateRow", newCol2, rs.getInt(2));
423         
424         rs.cancelRowUpdates();
425         
426         assertEquals("Expected updateXXX to have no effect after " +
427                      "cancelRowUpdated", oldCol3, rs.getInt(3));
428         assertEquals("Expected the resultset detect the updates of previous " +
429                      "updateRow after cancelRowUpdated", newCol2, rs.getInt(2));
430         rs.updateInt(3, newCol3);
431         rs.updateRow();
432         assertEquals("Expected the resultset to be updated after updateInt",
433                      newCol3, rs.getInt(3));
434         rs.cancelRowUpdates();
435         
436         assertEquals("Expected the resultset detect the updates of previous" +
437                      "updateRow after cancelRowUpdates", newCol2, rs.getInt(2));
438         assertEquals("Expected the resultset detect the updates of previous" +
439                      "updateRow after cancelRowUpdates", newCol3, rs.getInt(3));
440         assertTrue("Expected rs.rowUpdated() to be true after " +
441                    "updateRow and cancelRowUpdates", rs.rowUpdated());
442         
443         rs.close();
444         s.close();
445     }
446
447     /**
448      * Test that you can correctly run multiple updateNull() + updateRow()
449      * combined with cancelRowUpdates().
450      */
451     public void testMultiUpdateRow2()
452         throws SQLException
453     {
454         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
455                                           ResultSet.CONCUR_UPDATABLE);
456         s.setCursorName(getNextCursorName());
457         ResultSet rs = s.executeQuery("select * from t1");
458         rs.absolute(5);
459         final int oldCol2 = rs.getInt(2);
460         final int oldCol3 = rs.getInt(3);
461         
462         rs.updateNull(2);
463         assertEquals("Expected the resultset to be updated after updateNull",
464                      0, rs.getInt(2));
465         assertTrue("Expected wasNull to be true after updateNull", rs.wasNull());
466         rs.cancelRowUpdates();
467         assertEquals("Expected updateXXX to have no effect after cancelRowUpdated",
468                      oldCol2, rs.getInt(2));
469         rs.updateNull(2);
470         assertEquals("Expected the resultset to be updated after updateNull",
471                      0, rs.getInt(2));
472         assertTrue("Expected wasNull to be true after updateNull", rs.wasNull());
473         assertTrue("Expected rs.rowUpdated() to be false before updateRow",
474                    !rs.rowUpdated());
475         rs.updateRow();
476         
477         assertTrue("Expected rs.rowUpdated() to be true after updateRow",
478                    rs.rowUpdated());
479         assertEquals("Expected the resultset detect the updates of previous " +
480                      "updateRow", 0, rs.getInt(2));
481         
482         rs.updateNull(3);
483         
484         assertEquals("Expected the resultset to be updated after updateNull",
485                      0, rs.getInt(3));
486         assertTrue("Expected wasNull to be true after updateNull", rs.wasNull());
487         assertEquals("Expected the resultset detect the updates of previous " +
488                      "updateRow", 0, rs.getInt(2));
489         
490         rs.cancelRowUpdates();
491         
492         assertEquals("Expected updateXXX to have no effect after " +
493                      "cancelRowUpdated", oldCol3, rs.getInt(3));
494         assertEquals("Expected the resultset detect the updates of previous " +
495                      "updateRow after cancelRowUpdated", 0, rs.getInt(2));
496         rs.updateNull(3);
497         rs.updateRow();
498         assertEquals("Expected the resultset to be updated after updateNull",
499                      0, rs.getInt(3));
500         rs.cancelRowUpdates();
501         
502         assertEquals("Expected the resultset detect the updates of previous" +
503                      "updateRow after cancelRowUpdates", 0, rs.getInt(2));
504         assertEquals("Expected the resultset detect the updates of previous" +
505                      "updateRow after cancelRowUpdates", 0, rs.getInt(3));
506         assertTrue("Expected rs.rowUpdated() to be true after " +
507                    "updateRow and cancelRowUpdates", rs.rowUpdated());
508         
509         rs.close();
510         s.close();
511     }
512
513     /**
514      * Test that you get cursor operation conflict warning if updating
515      * a row which has been deleted from the table.
516      */
517     public void testCursorOperationConflictWarning1()
518         throws SQLException
519     {
520         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
521                                           ResultSet.CONCUR_UPDATABLE);
522         s.setCursorName(getNextCursorName());
523         ResultSet rs = s.executeQuery("select * from t1");
524         rs.next();
525         createStatement().executeUpdate("delete from t1 where id=" +
526                                             rs.getString("ID"));
527         final int newValue = -3333;
528         final int oldValue = rs.getInt(2);
529         rs.updateInt(2, newValue);
530         rs.updateRow();
531         
532         SQLWarning warn = rs.getWarnings();
533         assertWarning(warn, CURSOR_OPERATION_CONFLICT);
534         assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
535         assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
536         assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
537         
538         rs.clearWarnings();
539         rs.deleteRow();
540         warn = rs.getWarnings();
541         assertWarning(warn, CURSOR_OPERATION_CONFLICT);
542         rs.relative(0);
543         assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
544         assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
545         assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
546         
547         rs.close();
548         s.close();
549     }
550
551     /**
552      * Test that you get cursor operation conflict warning if updating
553      * a row which has been deleted from the table, now using
554      * positioned updates / deletes.
555      */
556     public void testCursorOperationConflictWarning2()
557         throws SQLException
558     {
559         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
560                                           ResultSet.CONCUR_UPDATABLE);
561         s.setCursorName(getNextCursorName());
562         ResultSet rs = s.executeQuery("select * from t1");
563         rs.next();
564         createStatement().executeUpdate ("delete from t1 where id=" +
565                                              rs.getString("ID"));
566         
567         final int newValue = -3333;
568         final int oldValue = rs.getInt(2);
569         
570         Statement s3 = createStatement();
571         int updateCount = s3.executeUpdate
572             ("update t1 set A=" + newValue +
573              " where current of " + rs.getCursorName());
574         
575         rs.relative(0);
576         SQLWarning warn = s3.getWarnings();
577         assertWarning(warn, CURSOR_OPERATION_CONFLICT);
578         assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
579         assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
580         assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
581         assertEquals("Expected update count to be 0", 0, updateCount);
582         
583         Statement s4 = createStatement();
584         updateCount = s4.executeUpdate("delete from t1 where current of " +
585                                        rs.getCursorName());
586         
587         rs.relative(0);
588         warn = s4.getWarnings();
589         assertWarning(warn, CURSOR_OPERATION_CONFLICT);
590         assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
591         assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
592         assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
593         assertEquals("Expected update count to be 0", 0, updateCount);
594         
595         rs.close();
596         s.close();
597         s3.close();
598         s4.close();
599     }
600     
601     /**
602      * Test that you can scroll forward and update indexed records in
603      * the ResultSet (not using FOR UPDATE)
604      */
605     public void testIndexedUpdateCursor1()
606         throws SQLException
607     {
608         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
609                                           ResultSet.CONCUR_UPDATABLE);
610         s.setCursorName(getNextCursorName());
611         ResultSet rs = s.executeQuery("select * from t1 where a=1");
612         
613         assertTrue("Expected to get a tuple on rs.next()", rs.next());
614         verifyTuple(rs);
615         updateTuple(rs);
616         s.close();
617         
618     }
619     
620     /**
621      * Test that you can scroll forward and update indexed records
622      * in the ResultSet (using FOR UPDATE).
623      */
624     public void testIndexedUpdateCursor2()
625         throws SQLException
626     {
627         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
628                                           ResultSet.CONCUR_UPDATABLE);
629         s.setCursorName(getNextCursorName());
630         ResultSet rs =
631             s.executeQuery("select * from t1 where a=1 for update");
632         
633         assertTrue("Expected to get a tuple on rs.next()", rs.next());
634         verifyTuple(rs);
635         updateTuple(rs);
636         s.close();
637     }
638     
639     /**
640      * Tests that it is possible to move using positioning methods after
641      * moveToInsertRow and that it is possible to delete a row after
642      * positioning back from insertRow. Also tests that it is possible to
643      * insert a row when positioned on insert row, that it is not possible
644      * to update or delete a row from insertRow and that it also is not possible
645      * to insert a row without being on insert row.
646      */
647     public void testInsertRowWithScrollCursor() throws SQLException {
648         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
649                                           ResultSet.CONCUR_UPDATABLE);
650         
651         int currentPosition, lastRow;
652         
653         s.setCursorName(getNextCursorName());
654         ResultSet rs =
655             s.executeQuery("select * from t1");
656         
657         rs.last();
658         lastRow = rs.getRow();
659         
660         rs.beforeFirst();
661         
662         rs.next();
663         
664         // Test that it is possible to move to next row from insertRow
665 currentPosition = rs.getRow();
666         rs.moveToInsertRow();
667         rs.updateInt(1, currentPosition + 1000);
668         rs.next();
669         assertEquals("CurrentPosition should be " + (currentPosition + 1),
670                 rs.getRow(), currentPosition + 1);
671         // should be able to delete the row
672 rs.deleteRow();
673
674         // Test that it is possible to move using relative from insertRow
675 currentPosition = rs.getRow();
676         rs.moveToInsertRow();
677         rs.updateInt(1, currentPosition + 1000);
678         rs.relative(2);
679         assertEquals("CurrentPosition should be " + (currentPosition + 2),
680                 rs.getRow(), currentPosition + 2);
681         // should be able to delete the row
682 rs.deleteRow();
683
684         // Test that it is possible to move using absolute from insertRow
685 currentPosition = rs.getRow();
686         rs.moveToInsertRow();
687         rs.updateInt(1, currentPosition + 1000);
688         rs.absolute(6);
689         assertEquals("CurrentPosition should be 6", rs.getRow(), 6);
690         // should be able to delete the row
691 rs.deleteRow();
692
693         // Test that it is possible to move to previous row from insertRow
694 currentPosition = rs.getRow();
695         rs.moveToInsertRow();
696         rs.updateInt(1, currentPosition + 1000);
697         rs.previous();
698         assertEquals("CurrentPosition should be " + (currentPosition - 1),
699                 rs.getRow(), currentPosition - 1);
700         // should be able to delete the row
701 rs.deleteRow();
702
703         // Test that it is possible to move to first row from insertRow
704 currentPosition = rs.getRow();
705         rs.moveToInsertRow();
706         rs.updateInt(1, currentPosition + 1000);
707         rs.first();
708         assertEquals("CurrentPosition should be 1", rs.getRow(), 1);
709         assertTrue("isFirst() should return true", rs.isFirst());
710         // should be able to delete the row
711 rs.deleteRow();
712
713         // Test that it is possible to move to last row from insertRow
714 currentPosition = rs.getRow();
715         rs.moveToInsertRow();
716         rs.updateInt(1, currentPosition + 1000);
717         rs.last();
718         assertEquals("CurrentPosition should be " + lastRow,
719                 rs.getRow(), lastRow);
720         assertTrue("isLast() should return true", rs.isLast());
721         // should be able to delete the row
722 rs.deleteRow();
723
724         // Test that it is possible to move beforeFirst from insertRow
725 currentPosition = rs.getRow();
726         rs.moveToInsertRow();
727         rs.updateInt(1, currentPosition + 1000);
728         rs.beforeFirst();
729         assertTrue("isBeforeFirst() should return true", rs.isBeforeFirst());
730         rs.next();
731         assertEquals("CurrentPosition should be 1", rs.getRow(), 1);
732         assertTrue("isFirst() should return true", rs.isFirst());
733
734         // Test that it is possible to move afterLast from insertRow
735 currentPosition = rs.getRow();
736         rs.moveToInsertRow();
737         rs.updateInt(1, currentPosition + 1000);
738         rs.afterLast();
739         assertTrue("isAfterLast() should return true", rs.isAfterLast());
740         rs.previous();
741         assertEquals("CurrentPosition should be " + lastRow,
742                 rs.getRow(), lastRow);
743         assertTrue("isLast() should return true", rs.isLast());
744
745         // Test that it is possible to insert a row and move back to current row
746 rs.previous();
747         currentPosition = rs.getRow();
748         rs.moveToInsertRow();
749         rs.updateInt(1, currentPosition + 1000);
750         rs.insertRow();
751         rs.moveToCurrentRow();
752         assertEquals("CurrentPosition should be " + currentPosition,
753                 rs.getRow(), currentPosition);
754
755         
756         try {
757             rs.moveToInsertRow();
758             rs.updateInt(1, currentPosition + 2000);
759             rs.updateRow();
760         } catch (SQLException se) {
761             assertEquals("Expected exception",
762                     se.getSQLState().substring(0, 5),
763                     INVALID_CURSOR_STATE_NO_CURRENT_ROW);
764         }
765         
766         try {
767             rs.moveToInsertRow();
768             rs.updateInt(1, currentPosition + 2000);
769             rs.deleteRow();
770         } catch (SQLException se) {
771             assertEquals("Expected exception",
772                     se.getSQLState().substring(0, 5),
773                     INVALID_CURSOR_STATE_NO_CURRENT_ROW);
774         }
775         
776         try {
777             rs.moveToCurrentRow();
778             rs.updateInt(1, currentPosition + 2000);
779             rs.insertRow();
780         } catch (SQLException se) {
781             assertEquals("Expected exception",
782                     se.getSQLState().substring(0, 5),
783                     CURSOR_NOT_POSITIONED_ON_INSERT_ROW);
784         }
785         
786         rs.close();
787         
788         s.close();
789     }
790     
791     /**
792      * Test that you can scroll forward and update indexed records
793      * in the scrollable ResultSet (not using FOR UPDATE).
794      */
795     public void
796         testIndexedScrollInsensitiveUpdateCursorWithoutForUpdate1()
797         throws SQLException
798     {
799         Statement s = createStatement
800             (ResultSet.TYPE_SCROLL_INSENSITIVE,
801              ResultSet.CONCUR_UPDATABLE);
802         s.setCursorName(getNextCursorName());
803         ResultSet rs =
804             s.executeQuery("select * from t1 where a=1 or a=2");
805         
806         rs.next();
807         rs.next();
808         rs.previous();
809         verifyTuple(rs);
810         updateTuple(rs);
811         s.close();
812     }
813     
814     /**
815      * Test that you can scroll forward and update indexed records
816      * in the scrollable ResultSet (using FOR UPDATE).
817      */
818     public void
819         testIndexedScrollInsensitiveUpdateCursorWithForUpdate1()
820         throws SQLException
821     {
822         Statement s = createStatement
823             (ResultSet.TYPE_SCROLL_INSENSITIVE,
824              ResultSet.CONCUR_UPDATABLE);
825         s.setCursorName(getNextCursorName());
826         ResultSet rs = s.executeQuery
827             ("select * from t1 where a=1 or a=2 for update");
828         
829         rs.next();
830         rs.next();
831         rs.previous();
832         verifyTuple(rs);
833         updateTuple(rs);
834         rs.close();
835         s.close();
836     }
837    
838     /**
839      * Test update of a keyed record using scrollable updatable
840      * resultset.
841      */
842     public void testPrimaryKeyUpdate1()
843         throws SQLException
844     {
845         Statement s = createStatement
846             (ResultSet.TYPE_SCROLL_INSENSITIVE,
847              ResultSet.CONCUR_UPDATABLE);
848         s.setCursorName(getNextCursorName());
849         ResultSet rs = s.executeQuery("select * from t1");
850         
851         rs.last();
852         rs.next();
853         while(rs.previous()) {
854             // Update the key of every second row.
855 int key = rs.getInt(1);
856             if (key%2==0) {
857                 int newKey = -key;
858                 rs.updateInt(1, newKey);
859                 rs.updateRow();
860             }
861         }
862         PreparedStatement ps = prepareStatement
863             ("select * from t1 where id=?");
864         for (int i=0; i<recordCount; i++) {
865             int key = (i%2==0) ? -i : i;
866             ps.setInt(1, key);
867             ResultSet rs2 = ps.executeQuery();
868             assertTrue("Expected query to have 1 row", rs2.next());
869             println("T1: Read Tuple:(" + rs2.getInt(1) + "," +
870                     rs2.getInt(2) + "," +
871                     rs2.getInt(3) + ")");
872             assertEquals("Unexpected value of id", key, rs2.getInt(1));
873             assertTrue("Did not expect more than 1 row, " +
874                        "however rs2.next returned another row",
875                        !rs2.next());
876         }
877         s.close();
878         ps.close();
879     }
880         
881     /**
882      * Test update of a keyed record using other statement
883      * object.
884      */
885     public void testOtherPrimaryKeyUpdate1()
886         throws SQLException
887     {
888         Statement s = createStatement
889             (ResultSet.TYPE_SCROLL_INSENSITIVE,
890              ResultSet.CONCUR_UPDATABLE);
891         s.setCursorName(getNextCursorName());
892         ResultSet rs = s.executeQuery("select * from t1");
893         
894         rs.last();
895         int primaryKey = rs.getInt(1);
896         PreparedStatement ps = prepareStatement
897             ("update t1 set id = ? where id= ?");
898         ps.setInt(1, -primaryKey);
899         ps.setInt(2, primaryKey);
900         assertEquals("Expected one row to be updated", 1,
901                      ps.executeUpdate());
902         
903         rs.updateInt(2, -555);
904         rs.updateInt(3, -777);
905         rs.updateRow();
906         
907         PreparedStatement ps2 = prepareStatement
908             ("select * from t1 where id=?");
909         ps2.setInt(1, -primaryKey);
910         ResultSet rs2 = ps2.executeQuery();
911         assertTrue("Expected query to have 1 row", rs2.next());
912         println("T1: Read Tuple:(" + rs2.getInt(1) + "," +
913                 rs2.getInt(2) + "," +
914                 rs2.getInt(3) + ")");
915         assertEquals("Expected a=-555", -555, rs2.getInt(2));
916         assertEquals("Expected b=-777", -777, rs2.getInt(3));
917         assertTrue("Did not expect more than 1 row, however " +
918                    "rs2.next() returned another row", !rs2.next());
919         
920         
921         s.close();
922         ps.close();
923         ps2.close();
924     }
925     
926     /**
927      * Test update of a keyed record using other both the
928      * scrollable updatable resultset and using another statement
929      * object.
930      */
931     public void testOtherAndOwnPrimaryKeyUpdate1()
932         throws SQLException
933     {
934         Statement s = createStatement
935             (ResultSet.TYPE_SCROLL_INSENSITIVE,
936              ResultSet.CONCUR_UPDATABLE);
937         s.setCursorName(getNextCursorName());
938         ResultSet rs = s.executeQuery("select * from t1");
939         
940         rs.last();
941         int primaryKey = rs.getInt(1);
942         PreparedStatement ps = prepareStatement
943             ("update t1 set id = ? where id= ?");
944         ps.setInt(1, -primaryKey);
945         ps.setInt(2, primaryKey);
946         assertEquals("Expected one row to be updated", 1,
947                      ps.executeUpdate());
948         rs.updateInt(1, primaryKey*10);
949         rs.updateInt(2, -555);
950         rs.updateInt(3, -777);
951         rs.updateRow();
952         
953         PreparedStatement ps2 =
954             prepareStatement("select * from t1 where id=?");
955         ps2.setInt(1, primaryKey*10);
956         ResultSet rs2 = ps2.executeQuery();
957         assertTrue("Expected query to have 1 row", rs2.next());
958         println("T1: Read Tuple:(" + rs2.getInt(1) + "," +
959                 rs2.getInt(2) + "," +
960                 rs2.getInt(3) + ")");
961         assertEquals("Expected a=-555", -555, rs2.getInt(2));
962         assertEquals("Expected b=-777", -777, rs2.getInt(3));
963         assertTrue("Did not expect more than 1 row, however " +
964                    "rs2.next() returned another row", !rs2.next());
965         
966         s.close();
967         ps.close();
968         ps2.close();
969     }
970     
971     /**
972      * Update multiple keyed records using scrollable updatable resultset
973      */
974     public void testMultipleKeyUpdates()
975         throws SQLException
976     {
977         Statement s = createStatement
978             (ResultSet.TYPE_SCROLL_INSENSITIVE,
979              ResultSet.CONCUR_UPDATABLE);
980         s.setCursorName(getNextCursorName());
981         ResultSet rs = s.executeQuery("select * from t1");
982         
983         rs.last();
984         int primaryKey = rs.getInt(1);
985         PreparedStatement ps = s.getConnection().prepareStatement
986             ("update t1 set id = ? where id= ?");
987         ps.setInt(1, -primaryKey);
988         ps.setInt(2, primaryKey);
989         assertEquals("Expected one row to be updated", 1,
990                      ps.executeUpdate());
991         rs.updateInt(1, primaryKey*10);
992         rs.updateInt(2, -555);
993         rs.updateInt(3, -777);
994         rs.updateRow();
995         rs.first();
996         rs.last();
997         for (int i=0; i<10; i++) {
998             rs.first();
999             rs.last();
1000            rs.next();
1001            rs.previous();
1002            rs.updateInt(1, primaryKey*10 +i);
1003            rs.updateInt(2, (-555 -i));
1004            rs.updateInt(3, (-777 -i));
1005            rs.updateRow();
1006        }
1007        rs.close();
1008        s.close();
1009    }
1010    
1011    /**
1012     * Test update indexed records using scrollable updatable resultset
1013     */
1014    public void testSecondaryIndexKeyUpdate1()
1015        throws SQLException
1016    {
1017        
1018        Statement s = createStatement
1019            (ResultSet.TYPE_SCROLL_INSENSITIVE,
1020             ResultSet.CONCUR_UPDATABLE);
1021        s.setCursorName(getNextCursorName());
1022        ResultSet rs = s.executeQuery("select * from t1");
1023        
1024        rs.last();
1025        rs.next();
1026        int newKey = 0;
1027        while(rs.previous()) {
1028            // Update the secondary key of all rows
1029 rs.updateInt(2, newKey--);
1030            rs.updateRow();
1031        }
1032        PreparedStatement ps = prepareStatement
1033            ("select * from t1 where a=?");
1034        for (int i=0; i<recordCount; i++) {
1035            int key = -i;
1036            ps.setInt(1, key);
1037            ResultSet rs2 = ps.executeQuery();
1038            assertTrue("Expected query to have 1 row", rs2.next());
1039            println("T1: Read Tuple:(" + rs2.getInt(1) + "," +
1040                    rs2.getInt(2) + "," +
1041                    rs2.getInt(3) + ")");
1042            assertEquals("Unexpected value of id", key, rs2.getInt(2));
1043            assertTrue("Did not expect more than 1 row, " +
1044                       "however rs2.next returned another row",
1045                       !rs2.next());
1046        }
1047        
1048        s.close();
1049        ps.close();
1050    }
1051    
1052    /**
1053     * Test update indexed records using other statement object
1054     * and using resultset.
1055     */
1056    public void testOtherSecondaryKeyUpdate1()
1057        throws SQLException
1058    {
1059        Statement s = createStatement
1060            (ResultSet.TYPE_SCROLL_INSENSITIVE,
1061             ResultSet.CONCUR_UPDATABLE);
1062        s.setCursorName(getNextCursorName());
1063        ResultSet rs = s.executeQuery("select * from t1");
1064        
1065        rs.last();
1066        int indexedKey = rs.getInt(2);
1067        PreparedStatement ps =
1068            prepareStatement("update t1 set a = ? where a= ?");
1069        ps.setInt(1, -indexedKey);
1070        ps.setInt(2, indexedKey);
1071        assertEquals("Expected one row to be updated", 1,
1072                     ps.executeUpdate());
1073        
1074        rs.updateInt(1, -555);
1075        rs.updateInt(3, -777);
1076        rs.updateRow();
1077        
1078        PreparedStatement ps2 =
1079            prepareStatement("select * from t1 where a=?");
1080        ps2.setInt(1, -indexedKey);
1081        ResultSet rs2 = ps2.executeQuery();
1082        assertTrue("Expected query to have 1 row", rs2.next());
1083        println("T1: Read Tuple:(" + rs2.getInt(1) + "," +
1084                rs2.getInt(2) + "," +
1085                rs2.getInt(3) + ")");
1086        assertEquals("Expected id=-555", -555, rs2.getInt(1));
1087        assertEquals("Expected b=-777", -777, rs2.getInt(3));
1088        assertTrue("Did not expect more than 1 row, however " +
1089                   "rs2.next() returned another row", !rs2.next());
1090        
1091        s.close();
1092        ps.close();
1093        ps2.close();
1094    }
1095    
1096    /**
1097     * Test scrolling in a read only resultset
1098     */
1099    public void testScrollInsensitiveReadOnly1()
1100        throws SQLException
1101    {
1102        Statement s = createStatement
1103            (ResultSet.TYPE_SCROLL_INSENSITIVE,
1104             ResultSet.CONCUR_READ_ONLY);
1105        s.setCursorName(getNextCursorName());
1106        ResultSet rs = s.executeQuery("select * from t1");
1107        
1108        scrollForward(rs);
1109        scrollBackward(rs);
1110        rs.close();
1111        s.close();
1112    }
1113    
1114    /**
1115     * Test updating a forward only resultset (with FOR UPDATE)
1116     */
1117    public void testForwardOnlyConcurUpdatableWithForUpdate1()
1118        throws SQLException
1119    {
1120        Statement s = createStatement
1121            (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1122        s.setCursorName(getNextCursorName());
1123        ResultSet rs = s.executeQuery("select * from t1 for update");
1124        
1125        scrollForwardAndUpdate(rs);
1126        rs.close();
1127        s.close();
1128    }
1129    
1130    /**
1131     * Test updating a forward only resultset (without FOR UPDATE)
1132     */
1133    public void testForwardOnlyConcurUpdatableWithoutForUpdate1()
1134        throws SQLException
1135    {
1136        Statement s = createStatement
1137            (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1138        s.setCursorName(getNextCursorName());
1139        ResultSet rs = s.executeQuery("select * from t1");
1140        
1141        scrollForwardAndUpdate(rs);
1142        rs.close();
1143        s.close();
1144    }
1145    
1146    /**
1147     * Test updating a forward only resultset (without FOR UPDATE)
1148     * and using positioned update
1149     */
1150    public void testPositionedUpdateWithoutForUpdate1()
1151        throws SQLException
1152    {
1153        Statement s = createStatement
1154            (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1155        s.setCursorName("MYCURSOR");
1156        ResultSet rs = s.executeQuery("select * from t1");
1157        
1158        scrollForwardAndUpdatePositioned(rs);
1159        rs.close();
1160        s.close();
1161    }
1162    
1163    /**
1164     * Test updating a forward only resultset (with FOR UPDATE)
1165     * and using positioned update
1166     */
1167    public void testPositionedUpdateWithForUpdate1()
1168        throws SQLException
1169    {
1170        Statement s = createStatement();
1171        s.setCursorName(getNextCursorName());
1172        ResultSet rs = s.executeQuery("select * from t1 for update");
1173        
1174        scrollForwardAndUpdatePositioned(rs);
1175        rs.close();
1176        s.close();
1177    }
1178    
1179    /**
1180     * Test positioned update of a scrollable resultset (with FOR UPDATE)
1181     */
1182    public void testScrollablePositionedUpdateWithForUpdate1()
1183        throws SQLException
1184    {
1185        Statement s = createStatement
1186            (ResultSet.TYPE_SCROLL_INSENSITIVE,
1187             ResultSet.CONCUR_READ_ONLY);
1188        s.setCursorName("MYCURSOR");
1189        ResultSet rs = s.executeQuery("select * from t1 for update");
1190        
1191        rs.next();
1192        int pKey = rs.getInt(1);
1193        rs.previous();
1194        rs.next();
1195        assertEquals("Expecting to be on the same row after previous() " +
1196                     "+ next() ", pKey, rs.getInt(1));
1197        rs.next();
1198        rs.previous();
1199        assertEquals("Expecting to be on the same row after next() + " +
1200                     "previous()", pKey, rs.getInt(1));
1201        final int previousA = rs.getInt(2);
1202        final int previousB = rs.getInt(3);
1203        println(rs.getCursorName());
1204        PreparedStatement ps = prepareStatement
1205            ("update T1 set a=?,b=? where current of " + rs.getCursorName());
1206        ps.setInt(1, 666);
1207        ps.setInt(2, 777);
1208        ps.executeUpdate();
1209        rs.next();
1210        rs.previous();
1211        assertEquals("Expected to be on the same row after next() + previous()",
1212                     pKey, rs.getInt(1));
1213        assertEquals("Expected row to be updated by own change, " +
1214                     " however did not get updated value for column a",
1215                     666, rs.getInt(2));
1216        assertEquals("Expected row to be updated by own change, however did " +
1217                     "not get updated value for column b", 777, rs.getInt(3));
1218        rs.close();
1219        s.setCursorName(getNextCursorName());
1220        rs = s.executeQuery("select * from t1 order by b");
1221        
1222        while (rs.next()) {
1223            if (rs.getInt(1)==pKey) {
1224                assertEquals("Expected row with primary key = " + pKey +
1225                             " to be updated", 666, rs.getInt(2));
1226                assertEquals("Expected row with primary key = " + pKey +
1227                             " to be updated", 777, rs.getInt(3));
1228            } else {
1229                println("Got tuple (" + rs.getInt(1) + "," + rs.getInt(2) +
1230                        "," + rs.getInt(3) + "," + rs.getString(4)+ ")");
1231            }
1232        }
1233
1234        s.close();
1235        ps.close();
1236    }
1237    
1238    /**
1239     * Test update of a scrollable resultset (with FOR UPDATE)
1240     * Only scrolling forward
1241     */
1242    public void testScrollInsensitiveConcurUpdatableWithForUpdate1()
1243        throws SQLException
1244    {
1245        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
1246                                          ResultSet.CONCUR_UPDATABLE);
1247        s.setCursorName(getNextCursorName());
1248        ResultSet rs = s.executeQuery("select * from t1 for update");
1249        scrollForwardAndUpdate(rs);
1250        rs.close();
1251        s.close();
1252    }
1253    
1254    /**
1255     * Test update of a scrollable resultset (with FOR UPDATE)
1256     * Scrolling forward and backward.
1257     */
1258    public void testScrollInsensitiveConcurUpdatableWithForUpdate2()
1259        throws SQLException
1260    {
1261        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
1262                                          ResultSet.CONCUR_UPDATABLE);
1263        assertEquals("Invalid resultset concurrency on statement",
1264                     ResultSet.CONCUR_UPDATABLE, s.getResultSetConcurrency());
1265        s.setCursorName(getNextCursorName());
1266        ResultSet rs = s.executeQuery("select * from t1 for update");
1267        
1268        assertEquals("Invalid resultset concurrency on resultset",
1269                     ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
1270        scrollForward(rs);
1271        scrollBackwardAndUpdate(rs);
1272        rs.close();
1273        s.close();
1274    }
1275    
1276    /**
1277     * Test update of a scrollable resultset
1278     * Scrolling forward and backward. Then open another
1279     * resultset and verify the data.
1280     */
1281    private void testScrollInsensistiveConurUpdatable3(ResultSet rs)
1282        throws SQLException
1283    {
1284        while (rs.next()) {
1285        }
1286        while (rs.previous()) {
1287            int a = rs.getInt(1);
1288            int b = rs.getInt(2);
1289            int id = b - 17 - a;
1290            int newA = 1000;
1291            int newB = id + newA + 17;
1292            rs.updateInt(1, newA); // Set a to 1000
1293 rs.updateInt(2, newB); // Set b to checksum value
1294 rs.updateRow();
1295            
1296            assertEquals("Expected a to be 1000", 1000, rs.getInt(1));
1297        }
1298        int count = 0;
1299        while (rs.next()) {
1300            int a = rs.getInt(1);
1301            count++;
1302            assertEquals("Incorrect row updated for row " + count, 1000, a);
1303        }
1304        assertEquals("Expected count to be the same as number of records",
1305                     recordCount, count);
1306        while (rs.previous()) {
1307            int a = rs.getInt(1);
1308            count--;
1309            assertEquals("Incorrect row updated for row " + count, 1000, a);
1310        }
1311        rs.close();
1312        Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
1313                                          ResultSet.CONCUR_READ_ONLY);
1314        s.setCursorName(getNextCursorName());
1315        rs = s.executeQuery("select * from t1");
1316        
1317        while (rs.next()) {
1318            int id = rs.getInt(1);
1319            int a = rs.getInt(2);
1320            int b = rs.getInt(3);
1321            println("Updated tuple:" + id + "," + a + "," + b);
1322        }
1323        s.close();
1324    }
1325    
1326    /**
1327     * Test update of a scrollable resultset (with FOR UPDATE)
1328     * Scrolling forward and backward. Then open another
1329     * resultset and verify the data.
1330     */
1331    public void testScrollInsensitiveConcurUpdatableWithForUpdate3()
1332        throws SQLException
1333    {
1334        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
1335                                          ResultSet.CONCUR_UPDATABLE);
1336        s.setCursorName(getNextCursorName());
1337        ResultSet rs = s.executeQuery("select a,b from t1 for update");
1338        
1339        testScrollInsensistiveConurUpdatable3(rs);
1340        s.close();
1341    }
1342    
1343    /**
1344     * Test update of a scrollable resultset (without FOR UPDATE)
1345     * Scrolling forward only
1346     */
1347    public void testScrollInsensitiveConcurUpdatableWithoutForUpdate1()
1348        throws SQLException
1349    {
1350        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
1351                                          ResultSet.CONCUR_UPDATABLE);
1352        s.setCursorName(getNextCursorName());
1353        ResultSet rs = s.executeQuery("select * from t1");
1354        
1355        scrollForwardAndUpdate(rs);
1356        rs.close();
1357        s.close();
1358    }
1359    
1360    /**
1361     * Test update of a scrollable resultset (without FOR UPDATE)
1362     * Scrolling forward and backward.
1363     */
1364    public void testScrollInsensitiveConcurUpdatableWithoutForUpdate2()
1365        throws SQLException
1366    {
1367        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
1368                                          ResultSet.CONCUR_UPDATABLE);
1369        s.setCursorName(getNextCursorName());
1370        ResultSet rs = s.executeQuery("select * from t1");
1371        
1372        scrollForward(rs);
1373        scrollBackwardAndUpdate(rs);
1374        rs.close();
1375        s.close();
1376    }
1377    
1378    /**
1379     * Test update of a scrollable resultset (without FOR UPDATE)
1380     * Scrolling forward and backward. Then open another
1381     * resultset and verify the data.
1382     */
1383    public void testScrollInsensitiveConcurUpdatableWithoutForUpdate3()
1384        throws SQLException
1385    {
1386        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
1387                                          ResultSet.CONCUR_UPDATABLE);
1388        s.setCursorName(getNextCursorName());
1389        ResultSet rs = s.executeQuery("select a,b from t1");
1390        
1391        testScrollInsensistiveConurUpdatable3(rs);
1392        s.close();
1393    }
1394    
1395    /**
1396     * Check that detectability methods throw the correct exception
1397     * when called in an illegal row state, that is, somehow not
1398     * positioned on a row. Minion of testDetectabilityExceptions.
1399     *
1400     * @param rs An open updatable result set.
1401     * @param state A string describing the illegal state.
1402     * @return No return value.
1403     */
1404    private void checkDetectabilityCallsOutsideRow(ResultSet rs,
1405                                                   String state)
1406    {
1407        boolean b;
1408        
1409        try {
1410            b = rs.rowUpdated();
1411            fail("rowUpdated while " + state +
1412                 " did not throw exception: " + b);
1413        } catch (SQLException e) {
1414            assertEquals(e.getSQLState(),
1415                         INVALID_CURSOR_STATE_NO_CURRENT_ROW);
1416        }
1417
1418        try {
1419            b = rs.rowDeleted();
1420            fail("rowdeleted while " + state +
1421                 " did not throw exception: " + b);
1422        } catch (SQLException e) {
1423            assertEquals(e.getSQLState(),
1424                         INVALID_CURSOR_STATE_NO_CURRENT_ROW);
1425        }
1426
1427        try {
1428            b = rs.rowInserted();
1429            fail("rowInserted while " + state +
1430                 " did not throw exception: " + b);
1431        } catch (SQLException e) {
1432            assertEquals(e.getSQLState(),
1433                         INVALID_CURSOR_STATE_NO_CURRENT_ROW);
1434        }
1435    }
1436
1437
1438    /**
1439     * Test that rowUpdated() and rowDeleted() methods both return true when
1440     * the row has first been updated and then deleted using the updateRow()
1441     * and deleteRow() methods.
1442     */
1443    public void testRowUpdatedAndRowDeleted() throws SQLException {
1444        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
1445                                           ResultSet.CONCUR_UPDATABLE);
1446        s.setCursorName(getNextCursorName());
1447        ResultSet rs = s.executeQuery("select a,b from t1");
1448        rs.next();
1449        rs.updateInt(1, rs.getInt(1) + 2 * recordCount);
1450        rs.updateRow();
1451        assertTrue("Expected rowUpdated() to return true", rs.rowUpdated());
1452        rs.deleteRow();
1453        rs.next();
1454        rs.previous();
1455        assertTrue("Expected rowUpdated() to return true", rs.rowUpdated());
1456        assertTrue("Expected rowDeleted() to return true", rs.rowDeleted());
1457        rs.next();
1458        assertFalse("Expected rowUpdated() to return false", rs.rowUpdated());
1459        assertFalse("Expected rowDeleted() to return false", rs.rowDeleted());
1460        rs.previous();
1461        assertTrue("Expected rowUpdated() to return true", rs.rowUpdated());
1462        assertTrue("Expected rowDeleted() to return true", rs.rowDeleted());
1463        rs.close();
1464        s.close();
1465    }
1466
1467
1468    /**
1469     * Test that the JDBC detectability calls throw correct exceptions when
1470     * called in in wrong row states.
1471     * This is done for both supported updatable result set types.
1472     */
1473    public void testDetectabilityExceptions() throws SQLException
1474    {
1475        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
1476                                          ResultSet.CONCUR_UPDATABLE);
1477        ResultSet rs = s.executeQuery("select * from t1");
1478        
1479        checkDetectabilityCallsOutsideRow(rs, "before positioning");
1480
1481        rs.moveToInsertRow();
1482        checkDetectabilityCallsOutsideRow(rs,
1483                                          "on insertRow before positioning");
1484
1485        rs.next();
1486        rs.moveToInsertRow();
1487        checkDetectabilityCallsOutsideRow(rs, "on insertRow");
1488        rs.moveToCurrentRow(); // needed until to DERBY-1322 is fixed
1489
1490        rs.beforeFirst();
1491        checkDetectabilityCallsOutsideRow(rs, "on beforeFirst row");
1492
1493        rs.afterLast();
1494        checkDetectabilityCallsOutsideRow(rs, "on afterLast row");
1495
1496        rs.first();
1497        rs.deleteRow();
1498        checkDetectabilityCallsOutsideRow(rs, "after deleteRow");
1499
1500        rs.last();
1501        rs.deleteRow();
1502        checkDetectabilityCallsOutsideRow(rs, "after deleteRow of last row");
1503
1504        rs.close();
1505        s.close();
1506
1507        // Not strictly SUR, but fixed in same patch, so we test it here.
1508 s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
1509                                ResultSet.CONCUR_UPDATABLE);
1510        rs = s.executeQuery("select * from t1");
1511
1512        checkDetectabilityCallsOutsideRow(rs, "before FO positioning");
1513
1514        rs.moveToInsertRow();
1515        checkDetectabilityCallsOutsideRow(rs,
1516                                          "on insertRow before FO positioning");
1517
1518        rs.next();
1519        rs.moveToInsertRow();
1520        checkDetectabilityCallsOutsideRow(rs, "on FO insertRow");
1521
1522        rs.next();
1523        rs.updateInt(2, 666);
1524        rs.updateRow();
1525        checkDetectabilityCallsOutsideRow(rs, "after FO updateRow");
1526
1527        rs.next();
1528        rs.deleteRow();
1529        checkDetectabilityCallsOutsideRow(rs, "after FO deleteRow");
1530
1531        while (rs.next()) {};
1532        checkDetectabilityCallsOutsideRow(rs, "after FO emptied out");
1533
1534        rs.close();
1535        s.close();
1536    }
1537
1538    /**
1539     * DERBY-1481 - ResultSet.beforeFirst() gives protocol error on scrollable,
1540     * updatable result sets that are downgraded to read-only
1541     *
1542     * Check that no exception is thrown when calling positioning methods on a
1543     * result set that has been downgraded to read-only.
1544     *
1545     */
1546    public void testDowngradeToScrollReadOnly() throws SQLException {
1547        Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
1548                                          ResultSet.CONCUR_UPDATABLE);
1549        ResultSet rs = s.executeQuery("select * from t1 order by b");
1550
1551        // check that the ResultSet was downgraded
1552 assertWarning(rs.getWarnings(),
1553                QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
1554        
1555        // call positioning methods
1556 rs.next();
1557        rs.next();
1558        rs.previous();
1559        rs.relative(1);
1560        rs.absolute(3);
1561        rs.relative(-1);
1562        rs.first();
1563        rs.last();
1564        rs.beforeFirst();
1565        rs.afterLast();
1566        
1567        // close result set and statement
1568 rs.close();
1569        s.close();
1570    }
1571
1572    /**
1573     * Get a cursor name. We use the same cursor name for all cursors.
1574     */
1575    private final String getNextCursorName() {
1576        return "MYCURSOR";
1577    }
1578    
1579    
1580    /**
1581     * The suite contains all testcases in this class running on different
1582     * data models
1583     */
1584    public static Test suite() {
1585        
1586        TestSuite mainSuite = new TestSuite();
1587        
1588        // DB2 client doesn't support this functionality
1589 if (usingDerbyNet())
1590            return mainSuite;
1591        
1592        // Iterate over all data models and decorate the tests:
1593 for (Iterator i = SURDataModelSetup.SURDataModel.values().iterator();
1594             i.hasNext();) {
1595            
1596            SURDataModelSetup.SURDataModel model =
1597                (SURDataModelSetup.SURDataModel) i.next();
1598            
1599            TestSuite suite = new TestSuite(SURTest.class);
1600            TestSetup decorator = new SURDataModelSetup
1601                (suite, model);
1602            
1603            mainSuite.addTest(decorator);
1604        }
1605        
1606        return mainSuite;
1607    }
1608
1609    protected void tearDown() throws Exception {
1610        super.tearDown();
1611        con = null;
1612    }
1613}
1614

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags