0001: /*
0002: *
0003: * Derby - Class SURTest
0004: *
0005: * Licensed to the Apache Software Foundation (ASF) under one or more
0006: * contributor license agreements. See the NOTICE file distributed with
0007: * this work for additional information regarding copyright ownership.
0008: * The ASF licenses this file to You under the Apache License, Version 2.0
0009: * (the "License"); you may not use this file except in compliance with
0010: * the License. You may obtain a copy of the License at
0011: *
0012: * http://www.apache.org/licenses/LICENSE-2.0
0013: *
0014: * Unless required by applicable law or agreed to in writing,
0015: * software distributed under the License is distributed on an
0016: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
0017: * either express or implied. See the License for the specific
0018: * language governing permissions and limitations under the License.
0019: */
0020: package org.apache.derbyTesting.functionTests.tests.jdbcapi;
0021:
0022: import org.apache.derbyTesting.functionTests.util.SQLStateConstants;
0023:
0024: import java.sql.Connection;
0025: import java.sql.PreparedStatement;
0026: import java.sql.ResultSet;
0027: import java.sql.SQLException;
0028: import java.sql.SQLWarning;
0029: import java.sql.Statement;
0030: import junit.extensions.TestSetup;
0031: import junit.framework.Test;
0032: import junit.framework.TestSuite;
0033: import java.util.Iterator;
0034:
0035: /**
0036: * Tests for variants of scrollable updatable resultsets.
0037: *
0038: * @author Andreas Korneliussen
0039: */
0040: public class SURTest extends SURBaseTest {
0041:
0042: /** Creates a new instance of SURTest */
0043: public SURTest(String name) {
0044: super (name);
0045: }
0046:
0047: /**
0048: * Test that you get a warning when specifying a query which is not
0049: * updatable and concurrency mode CONCUR_UPDATABLE.
0050: * In this case, the query contains an "order by"
0051: */
0052: public void testConcurrencyModeWarning1() throws SQLException {
0053: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
0054: ResultSet.CONCUR_UPDATABLE);
0055: s.setCursorName(getNextCursorName());
0056: ResultSet rs = s.executeQuery("select * from t1 order by a");
0057:
0058: SQLWarning warn = rs.getWarnings();
0059: assertEquals("Expected resultset to be read only",
0060: ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
0061: assertWarning(warn, QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
0062: scrollForward(rs);
0063: rs.close();
0064: s.close();
0065: }
0066:
0067: /**
0068: * Test that you get a warning when specifying a query which is not
0069: * updatable and concurrency mode CONCUR_UPDATABLE.
0070: * In this case, the query contains a join.
0071: */
0072: public void testConcurrencyModeWarning2() throws SQLException {
0073: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
0074: ResultSet.CONCUR_UPDATABLE);
0075: s.setCursorName(getNextCursorName());
0076: ResultSet rs = s
0077: .executeQuery("select * from t1 as table1,t1 as table2 where "
0078: + "table1.a=table2.a");
0079:
0080: SQLWarning warn = rs.getWarnings();
0081: assertEquals("Expected resultset to be read only",
0082: ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
0083: assertWarning(warn, QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
0084: scrollForward(rs);
0085: rs.close();
0086: s.close();
0087: }
0088:
0089: /**
0090: * Test that you get an exception when specifying update clause
0091: * "FOR UPDATE"
0092: * along with a query which is not updatable.
0093: * In this case, the query contains and order by.
0094: */
0095: public void testForUpdateException1() throws SQLException {
0096: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
0097: ResultSet.CONCUR_UPDATABLE);
0098: try {
0099: String queryString = "select * from t1 order by a for update";
0100: s.setCursorName(getNextCursorName());
0101: ResultSet rs = s.executeQuery(queryString);
0102:
0103: assertTrue("Expected query '" + queryString + "' to fail",
0104: false);
0105: } catch (SQLException e) {
0106: assertEquals("Unexpected SQLState",
0107: FOR_UPDATE_NOT_PERMITTED_SQL_STATE, e.getSQLState());
0108: }
0109: rollback();
0110: s.close();
0111: }
0112:
0113: /**
0114: * Test that you get an exception when specifying update clause
0115: * "FOR UPDATE" along with a query which is not updatable.
0116: * In this case, the query contains a join
0117: */
0118: public void testForUpdateException2() throws SQLException {
0119: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
0120: ResultSet.CONCUR_UPDATABLE);
0121: try {
0122: String queryString = "select * from t1 as table1,t1 as table2"
0123: + " where table1.a=table2.a for update";
0124: s.setCursorName(getNextCursorName());
0125: ResultSet rs = s.executeQuery(queryString);
0126:
0127: assertTrue("Expected query '" + queryString + "' to fail",
0128: false);
0129: } catch (SQLException e) {
0130: assertEquals("Unexpected SQLState",
0131: FOR_UPDATE_NOT_PERMITTED_SQL_STATE, e.getSQLState());
0132: }
0133: rollback();
0134: s.close();
0135: }
0136:
0137: /**
0138: * Test that you can scroll forward and read all records in the
0139: * ResultSet
0140: */
0141: public void testForwardOnlyReadOnly1() throws SQLException {
0142: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
0143: ResultSet.CONCUR_READ_ONLY);
0144: s.setCursorName(getNextCursorName());
0145: ResultSet rs = s.executeQuery("select * from t1");
0146:
0147: scrollForward(rs);
0148: rs.close();
0149: s.close();
0150: }
0151:
0152: /**
0153: * Test that you get an exception if you try to update a ResultSet
0154: * with concurrency mode CONCUR_READ_ONLY.
0155: */
0156: public void testFailOnUpdateOfReadOnlyResultSet1()
0157: throws SQLException {
0158: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
0159: ResultSet.CONCUR_READ_ONLY);
0160: s.setCursorName(getNextCursorName());
0161: ResultSet rs = s.executeQuery("select * from t1");
0162:
0163: rs.next();
0164: assertFailOnUpdate(rs);
0165: s.close();
0166: }
0167:
0168: /**
0169: * Test that you get an exception when attempting to update a
0170: * ResultSet which has been downgraded to a read only ResultSet.
0171: */
0172: public void testFailOnUpdateOfReadOnlyResultSet2()
0173: throws SQLException {
0174: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
0175: ResultSet.CONCUR_UPDATABLE);
0176: s.setCursorName(getNextCursorName());
0177: ResultSet rs = s.executeQuery("select * from t1 order by id");
0178:
0179: rs.next();
0180: assertFailOnUpdate(rs);
0181: s.close();
0182: }
0183:
0184: /**
0185: * Test that you get an exception when attempting to update a
0186: * ResultSet which has been downgraded to a read only ResultSet.
0187: */
0188: public void testFailOnUpdateOfReadOnlyResultSet3()
0189: throws SQLException {
0190: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
0191: ResultSet.CONCUR_UPDATABLE);
0192: s.setCursorName(getNextCursorName());
0193: ResultSet rs = s.executeQuery("select * from t1 for read only");
0194:
0195: rs.next();
0196: assertFailOnUpdate(rs);
0197: s.close();
0198: }
0199:
0200: /**
0201: * Test that you get an exception when attempting to update a
0202: * ResultSet which has been downgraded to a read only ResultSet.
0203: */
0204: public void testFailOnUpdateOfReadOnlyResultSet4()
0205: throws SQLException {
0206: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
0207: ResultSet.CONCUR_UPDATABLE);
0208: s.setCursorName(getNextCursorName());
0209: ResultSet rs = s
0210: .executeQuery("select * from t1 where a=1 for read only");
0211:
0212: rs.next();
0213: verifyTuple(rs);
0214: assertFailOnUpdate(rs);
0215: s.close();
0216: }
0217:
0218: /**
0219: * Test that you get an exception if you try to update a ResultSet
0220: * with concurrency mode CONCUR_READ_ONLY.
0221: */
0222: public void testFailOnUpdateOfReadOnlyResultSet5()
0223: throws SQLException {
0224: Statement s = createStatement(
0225: ResultSet.TYPE_SCROLL_INSENSITIVE,
0226: ResultSet.CONCUR_READ_ONLY);
0227: s.setCursorName(getNextCursorName());
0228: ResultSet rs = s
0229: .executeQuery("select * from t1 where a=1 for read only");
0230:
0231: rs.next();
0232: verifyTuple(rs);
0233: assertFailOnUpdate(rs);
0234: s.close();
0235: }
0236:
0237: /**
0238: * Test that when doing an update immediately after
0239: * a commit, the update fails, because the cursor has been
0240: * postioned between the current row and the next row.
0241: * The test uses a FORWARD_ONLY resultset and ResultSet update methods
0242: * when doing the update.
0243: */
0244: public void testCursorStateAfterCommit1() throws SQLException {
0245: testCursorStateAfterCommit(false, ResultSet.TYPE_FORWARD_ONLY);
0246: }
0247:
0248: /**
0249: * Test that when doing an update immediately after
0250: * a commit, the update fails, because the cursor has been
0251: * postioned between the current row and the next row.
0252: * The test uses a SCROLL_INSENSITIVE resultset and ResultSet update methods
0253: * when doing the update.
0254: */
0255: public void testCursorStateAfterCommit2() throws SQLException {
0256: testCursorStateAfterCommit(false,
0257: ResultSet.TYPE_SCROLL_INSENSITIVE);
0258: }
0259:
0260: /**
0261: * Test that when doing an update immediately after
0262: * a commit, the update fails, because the cursor has been
0263: * postioned between the current row and the next row.
0264: * The test uses a FORWARD_ONLY resultset and positioned updates.
0265: */
0266: public void testCursorStateAfterCommit3() throws SQLException {
0267: testCursorStateAfterCommit(true, ResultSet.TYPE_FORWARD_ONLY);
0268: }
0269:
0270: /**
0271: * Test that when doing an update immediately after
0272: * a commit, the update fails, because the cursor has been
0273: * postioned between the current row and the next row.
0274: * The test uses a SCROLL_INSENSITIVE resultset and positioned updates.
0275: */
0276: public void testCursorStateAfterCommit4() throws SQLException {
0277: testCursorStateAfterCommit(true,
0278: ResultSet.TYPE_SCROLL_INSENSITIVE);
0279: }
0280:
0281: /**
0282: * Test that when doing an update immediately after
0283: * a commit, the update fails, because the cursor has been
0284: * postioned between the current row and the next row.
0285: * If the cursor gets repositioned, it allows an update.
0286: * @param positioned true to use positioned update, otherwise use
0287: * ResultSet.updateRow()
0288: * @param resultSetType type of result set (as in ResultSet.getType())
0289: */
0290: private void testCursorStateAfterCommit(final boolean positioned,
0291: final int resultSetType) throws SQLException {
0292: final Statement s = createStatement(resultSetType,
0293: ResultSet.CONCUR_UPDATABLE);
0294: final String cursorName = getNextCursorName();
0295: s.setCursorName(cursorName);
0296:
0297: final ResultSet rs = s.executeQuery("select a from t1");
0298: final int recordToUpdate = 5;
0299:
0300: if (resultSetType == ResultSet.TYPE_FORWARD_ONLY) {
0301: for (int i = 0; i < recordToUpdate; i++) {
0302: rs.next();
0303: }
0304: } else {
0305: rs.absolute(recordToUpdate);
0306: }
0307:
0308: commit();
0309:
0310: PreparedStatement ps = prepareStatement("update t1 set a=? where current of "
0311: + cursorName);
0312: // First: check that we get an exception on update without repositioning:
0313: try {
0314: if (positioned) {
0315: ps.setInt(1, -1);
0316: ps.executeUpdate();
0317: fail("Expected exception to be thrown on positioned update "
0318: + "since cursor is not positioned");
0319: } else {
0320: rs.updateInt(1, -1);
0321: rs.updateRow();
0322: fail("Expected exception to be thrown on updateRow() since "
0323: + "cursor is not positioned");
0324: }
0325: } catch (SQLException e) {
0326: assertSQLState(
0327: "Unexpected SQLState when updating row after commit",
0328: SQLStateConstants.INVALID_CURSOR_STATE_NO_SUBCLASS,
0329: e);
0330: }
0331:
0332: // Check that we after a repositioning can update:
0333: if (resultSetType == ResultSet.TYPE_FORWARD_ONLY) {
0334: rs.next();
0335: } else {
0336: rs.relative(0);
0337: }
0338: if (positioned) {
0339: ps.setInt(1, -1);
0340: ps.executeUpdate();
0341: } else {
0342: rs.updateInt(1, -1);
0343: rs.updateRow();
0344: }
0345:
0346: s.close();
0347: ps.close();
0348:
0349: }
0350:
0351: /**
0352: * Test that you can correctly run multiple updateXXX() + updateRow()
0353: * combined with cancelRowUpdates().
0354: */
0355: public void testMultiUpdateRow1() throws SQLException {
0356: Statement s = createStatement(
0357: ResultSet.TYPE_SCROLL_INSENSITIVE,
0358: ResultSet.CONCUR_UPDATABLE);
0359: s.setCursorName(getNextCursorName());
0360: ResultSet rs = s.executeQuery("select * from t1");
0361: rs.absolute(5);
0362: final int oldCol2 = rs.getInt(2);
0363: final int newCol2 = -2222;
0364: final int oldCol3 = rs.getInt(3);
0365: final int newCol3 = -3333;
0366:
0367: rs.updateInt(2, newCol2);
0368: assertEquals(
0369: "Expected the resultset to be updated after updateInt",
0370: newCol2, rs.getInt(2));
0371: rs.cancelRowUpdates();
0372: assertEquals(
0373: "Expected updateXXX to have no effect after cancelRowUpdated",
0374: oldCol2, rs.getInt(2));
0375: rs.updateInt(2, newCol2);
0376: assertEquals(
0377: "Expected the resultset to be updated after updateInt",
0378: newCol2, rs.getInt(2));
0379: assertTrue(
0380: "Expected rs.rowUpdated() to be false before updateRow",
0381: !rs.rowUpdated());
0382: rs.updateRow();
0383:
0384: assertTrue(
0385: "Expected rs.rowUpdated() to be true after updateRow",
0386: rs.rowUpdated());
0387: assertEquals(
0388: "Expected the resultset detect the updates of previous "
0389: + "updateRow", newCol2, rs.getInt(2));
0390:
0391: rs.updateInt(3, newCol3);
0392:
0393: assertEquals(
0394: "Expected the resultset to be updated after updateInt",
0395: newCol3, rs.getInt(3));
0396: assertEquals(
0397: "Expected the resultset detect the updates of previous "
0398: + "updateRow", newCol2, rs.getInt(2));
0399:
0400: rs.cancelRowUpdates();
0401:
0402: assertEquals("Expected updateXXX to have no effect after "
0403: + "cancelRowUpdated", oldCol3, rs.getInt(3));
0404: assertEquals(
0405: "Expected the resultset detect the updates of previous "
0406: + "updateRow after cancelRowUpdated", newCol2,
0407: rs.getInt(2));
0408: rs.updateInt(3, newCol3);
0409: rs.updateRow();
0410: assertEquals(
0411: "Expected the resultset to be updated after updateInt",
0412: newCol3, rs.getInt(3));
0413: rs.cancelRowUpdates();
0414:
0415: assertEquals(
0416: "Expected the resultset detect the updates of previous"
0417: + "updateRow after cancelRowUpdates", newCol2,
0418: rs.getInt(2));
0419: assertEquals(
0420: "Expected the resultset detect the updates of previous"
0421: + "updateRow after cancelRowUpdates", newCol3,
0422: rs.getInt(3));
0423: assertTrue("Expected rs.rowUpdated() to be true after "
0424: + "updateRow and cancelRowUpdates", rs.rowUpdated());
0425:
0426: rs.close();
0427: s.close();
0428: }
0429:
0430: /**
0431: * Test that you can correctly run multiple updateNull() + updateRow()
0432: * combined with cancelRowUpdates().
0433: */
0434: public void testMultiUpdateRow2() throws SQLException {
0435: Statement s = createStatement(
0436: ResultSet.TYPE_SCROLL_INSENSITIVE,
0437: ResultSet.CONCUR_UPDATABLE);
0438: s.setCursorName(getNextCursorName());
0439: ResultSet rs = s.executeQuery("select * from t1");
0440: rs.absolute(5);
0441: final int oldCol2 = rs.getInt(2);
0442: final int oldCol3 = rs.getInt(3);
0443:
0444: rs.updateNull(2);
0445: assertEquals(
0446: "Expected the resultset to be updated after updateNull",
0447: 0, rs.getInt(2));
0448: assertTrue("Expected wasNull to be true after updateNull", rs
0449: .wasNull());
0450: rs.cancelRowUpdates();
0451: assertEquals(
0452: "Expected updateXXX to have no effect after cancelRowUpdated",
0453: oldCol2, rs.getInt(2));
0454: rs.updateNull(2);
0455: assertEquals(
0456: "Expected the resultset to be updated after updateNull",
0457: 0, rs.getInt(2));
0458: assertTrue("Expected wasNull to be true after updateNull", rs
0459: .wasNull());
0460: assertTrue(
0461: "Expected rs.rowUpdated() to be false before updateRow",
0462: !rs.rowUpdated());
0463: rs.updateRow();
0464:
0465: assertTrue(
0466: "Expected rs.rowUpdated() to be true after updateRow",
0467: rs.rowUpdated());
0468: assertEquals(
0469: "Expected the resultset detect the updates of previous "
0470: + "updateRow", 0, rs.getInt(2));
0471:
0472: rs.updateNull(3);
0473:
0474: assertEquals(
0475: "Expected the resultset to be updated after updateNull",
0476: 0, rs.getInt(3));
0477: assertTrue("Expected wasNull to be true after updateNull", rs
0478: .wasNull());
0479: assertEquals(
0480: "Expected the resultset detect the updates of previous "
0481: + "updateRow", 0, rs.getInt(2));
0482:
0483: rs.cancelRowUpdates();
0484:
0485: assertEquals("Expected updateXXX to have no effect after "
0486: + "cancelRowUpdated", oldCol3, rs.getInt(3));
0487: assertEquals(
0488: "Expected the resultset detect the updates of previous "
0489: + "updateRow after cancelRowUpdated", 0, rs
0490: .getInt(2));
0491: rs.updateNull(3);
0492: rs.updateRow();
0493: assertEquals(
0494: "Expected the resultset to be updated after updateNull",
0495: 0, rs.getInt(3));
0496: rs.cancelRowUpdates();
0497:
0498: assertEquals(
0499: "Expected the resultset detect the updates of previous"
0500: + "updateRow after cancelRowUpdates", 0, rs
0501: .getInt(2));
0502: assertEquals(
0503: "Expected the resultset detect the updates of previous"
0504: + "updateRow after cancelRowUpdates", 0, rs
0505: .getInt(3));
0506: assertTrue("Expected rs.rowUpdated() to be true after "
0507: + "updateRow and cancelRowUpdates", rs.rowUpdated());
0508:
0509: rs.close();
0510: s.close();
0511: }
0512:
0513: /**
0514: * Test that you get cursor operation conflict warning if updating
0515: * a row which has been deleted from the table.
0516: */
0517: public void testCursorOperationConflictWarning1()
0518: throws SQLException {
0519: Statement s = createStatement(
0520: ResultSet.TYPE_SCROLL_INSENSITIVE,
0521: ResultSet.CONCUR_UPDATABLE);
0522: s.setCursorName(getNextCursorName());
0523: ResultSet rs = s.executeQuery("select * from t1");
0524: rs.next();
0525: createStatement().executeUpdate(
0526: "delete from t1 where id=" + rs.getString("ID"));
0527: final int newValue = -3333;
0528: final int oldValue = rs.getInt(2);
0529: rs.updateInt(2, newValue);
0530: rs.updateRow();
0531:
0532: SQLWarning warn = rs.getWarnings();
0533: assertWarning(warn, CURSOR_OPERATION_CONFLICT);
0534: assertEquals("Did not expect the resultset to be updated",
0535: oldValue, rs.getInt(2));
0536: assertTrue("Expected rs.rowDeleted() to be false", !rs
0537: .rowDeleted());
0538: assertTrue("Expected rs.rowUpdated() to be false", !rs
0539: .rowUpdated());
0540:
0541: rs.clearWarnings();
0542: rs.deleteRow();
0543: warn = rs.getWarnings();
0544: assertWarning(warn, CURSOR_OPERATION_CONFLICT);
0545: rs.relative(0);
0546: assertTrue("Expected rs.rowUpdated() to be false", !rs
0547: .rowUpdated());
0548: assertTrue("Expected rs.rowDeleted() to be false", !rs
0549: .rowDeleted());
0550: assertEquals("Did not expect the resultset to be updated",
0551: oldValue, rs.getInt(2));
0552:
0553: rs.close();
0554: s.close();
0555: }
0556:
0557: /**
0558: * Test that you get cursor operation conflict warning if updating
0559: * a row which has been deleted from the table, now using
0560: * positioned updates / deletes.
0561: */
0562: public void testCursorOperationConflictWarning2()
0563: throws SQLException {
0564: Statement s = createStatement(
0565: ResultSet.TYPE_SCROLL_INSENSITIVE,
0566: ResultSet.CONCUR_UPDATABLE);
0567: s.setCursorName(getNextCursorName());
0568: ResultSet rs = s.executeQuery("select * from t1");
0569: rs.next();
0570: createStatement().executeUpdate(
0571: "delete from t1 where id=" + rs.getString("ID"));
0572:
0573: final int newValue = -3333;
0574: final int oldValue = rs.getInt(2);
0575:
0576: Statement s3 = createStatement();
0577: int updateCount = s3.executeUpdate("update t1 set A="
0578: + newValue + " where current of " + rs.getCursorName());
0579:
0580: rs.relative(0);
0581: SQLWarning warn = s3.getWarnings();
0582: assertWarning(warn, CURSOR_OPERATION_CONFLICT);
0583: assertTrue("Expected rs.rowUpdated() to be false", !rs
0584: .rowUpdated());
0585: assertTrue("Expected rs.rowDeleted() to be false", !rs
0586: .rowDeleted());
0587: assertEquals("Did not expect the resultset to be updated",
0588: oldValue, rs.getInt(2));
0589: assertEquals("Expected update count to be 0", 0, updateCount);
0590:
0591: Statement s4 = createStatement();
0592: updateCount = s4
0593: .executeUpdate("delete from t1 where current of "
0594: + rs.getCursorName());
0595:
0596: rs.relative(0);
0597: warn = s4.getWarnings();
0598: assertWarning(warn, CURSOR_OPERATION_CONFLICT);
0599: assertTrue("Expected rs.rowUpdated() to be false", !rs
0600: .rowUpdated());
0601: assertTrue("Expected rs.rowDeleted() to be false", !rs
0602: .rowDeleted());
0603: assertEquals("Did not expect the resultset to be updated",
0604: oldValue, rs.getInt(2));
0605: assertEquals("Expected update count to be 0", 0, updateCount);
0606:
0607: rs.close();
0608: s.close();
0609: s3.close();
0610: s4.close();
0611: }
0612:
0613: /**
0614: * Test that you can scroll forward and update indexed records in
0615: * the ResultSet (not using FOR UPDATE)
0616: */
0617: public void testIndexedUpdateCursor1() throws SQLException {
0618: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
0619: ResultSet.CONCUR_UPDATABLE);
0620: s.setCursorName(getNextCursorName());
0621: ResultSet rs = s.executeQuery("select * from t1 where a=1");
0622:
0623: assertTrue("Expected to get a tuple on rs.next()", rs.next());
0624: verifyTuple(rs);
0625: updateTuple(rs);
0626: s.close();
0627:
0628: }
0629:
0630: /**
0631: * Test that you can scroll forward and update indexed records
0632: * in the ResultSet (using FOR UPDATE).
0633: */
0634: public void testIndexedUpdateCursor2() throws SQLException {
0635: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
0636: ResultSet.CONCUR_UPDATABLE);
0637: s.setCursorName(getNextCursorName());
0638: ResultSet rs = s
0639: .executeQuery("select * from t1 where a=1 for update");
0640:
0641: assertTrue("Expected to get a tuple on rs.next()", rs.next());
0642: verifyTuple(rs);
0643: updateTuple(rs);
0644: s.close();
0645: }
0646:
0647: /**
0648: * Tests that it is possible to move using positioning methods after
0649: * moveToInsertRow and that it is possible to delete a row after
0650: * positioning back from insertRow. Also tests that it is possible to
0651: * insert a row when positioned on insert row, that it is not possible
0652: * to update or delete a row from insertRow and that it also is not possible
0653: * to insert a row without being on insert row.
0654: */
0655: public void testInsertRowWithScrollCursor() throws SQLException {
0656: Statement s = createStatement(
0657: ResultSet.TYPE_SCROLL_INSENSITIVE,
0658: ResultSet.CONCUR_UPDATABLE);
0659:
0660: int currentPosition, lastRow;
0661:
0662: s.setCursorName(getNextCursorName());
0663: ResultSet rs = s.executeQuery("select * from t1");
0664:
0665: rs.last();
0666: lastRow = rs.getRow();
0667:
0668: rs.beforeFirst();
0669:
0670: rs.next();
0671:
0672: // Test that it is possible to move to next row from insertRow
0673: currentPosition = rs.getRow();
0674: rs.moveToInsertRow();
0675: rs.updateInt(1, currentPosition + 1000);
0676: rs.next();
0677: assertEquals("CurrentPosition should be "
0678: + (currentPosition + 1), rs.getRow(),
0679: currentPosition + 1);
0680: // should be able to delete the row
0681: rs.deleteRow();
0682:
0683: // Test that it is possible to move using relative from insertRow
0684: currentPosition = rs.getRow();
0685: rs.moveToInsertRow();
0686: rs.updateInt(1, currentPosition + 1000);
0687: rs.relative(2);
0688: assertEquals("CurrentPosition should be "
0689: + (currentPosition + 2), rs.getRow(),
0690: currentPosition + 2);
0691: // should be able to delete the row
0692: rs.deleteRow();
0693:
0694: // Test that it is possible to move using absolute from insertRow
0695: currentPosition = rs.getRow();
0696: rs.moveToInsertRow();
0697: rs.updateInt(1, currentPosition + 1000);
0698: rs.absolute(6);
0699: assertEquals("CurrentPosition should be 6", rs.getRow(), 6);
0700: // should be able to delete the row
0701: rs.deleteRow();
0702:
0703: // Test that it is possible to move to previous row from insertRow
0704: currentPosition = rs.getRow();
0705: rs.moveToInsertRow();
0706: rs.updateInt(1, currentPosition + 1000);
0707: rs.previous();
0708: assertEquals("CurrentPosition should be "
0709: + (currentPosition - 1), rs.getRow(),
0710: currentPosition - 1);
0711: // should be able to delete the row
0712: rs.deleteRow();
0713:
0714: // Test that it is possible to move to first row from insertRow
0715: currentPosition = rs.getRow();
0716: rs.moveToInsertRow();
0717: rs.updateInt(1, currentPosition + 1000);
0718: rs.first();
0719: assertEquals("CurrentPosition should be 1", rs.getRow(), 1);
0720: assertTrue("isFirst() should return true", rs.isFirst());
0721: // should be able to delete the row
0722: rs.deleteRow();
0723:
0724: // Test that it is possible to move to last row from insertRow
0725: currentPosition = rs.getRow();
0726: rs.moveToInsertRow();
0727: rs.updateInt(1, currentPosition + 1000);
0728: rs.last();
0729: assertEquals("CurrentPosition should be " + lastRow, rs
0730: .getRow(), lastRow);
0731: assertTrue("isLast() should return true", rs.isLast());
0732: // should be able to delete the row
0733: rs.deleteRow();
0734:
0735: // Test that it is possible to move beforeFirst from insertRow
0736: currentPosition = rs.getRow();
0737: rs.moveToInsertRow();
0738: rs.updateInt(1, currentPosition + 1000);
0739: rs.beforeFirst();
0740: assertTrue("isBeforeFirst() should return true", rs
0741: .isBeforeFirst());
0742: rs.next();
0743: assertEquals("CurrentPosition should be 1", rs.getRow(), 1);
0744: assertTrue("isFirst() should return true", rs.isFirst());
0745:
0746: // Test that it is possible to move afterLast from insertRow
0747: currentPosition = rs.getRow();
0748: rs.moveToInsertRow();
0749: rs.updateInt(1, currentPosition + 1000);
0750: rs.afterLast();
0751: assertTrue("isAfterLast() should return true", rs.isAfterLast());
0752: rs.previous();
0753: assertEquals("CurrentPosition should be " + lastRow, rs
0754: .getRow(), lastRow);
0755: assertTrue("isLast() should return true", rs.isLast());
0756:
0757: // Test that it is possible to insert a row and move back to current row
0758: rs.previous();
0759: currentPosition = rs.getRow();
0760: rs.moveToInsertRow();
0761: rs.updateInt(1, currentPosition + 1000);
0762: rs.insertRow();
0763: rs.moveToCurrentRow();
0764: assertEquals("CurrentPosition should be " + currentPosition, rs
0765: .getRow(), currentPosition);
0766:
0767: try {
0768: rs.moveToInsertRow();
0769: rs.updateInt(1, currentPosition + 2000);
0770: rs.updateRow();
0771: } catch (SQLException se) {
0772: assertEquals("Expected exception", se.getSQLState()
0773: .substring(0, 5),
0774: INVALID_CURSOR_STATE_NO_CURRENT_ROW);
0775: }
0776:
0777: try {
0778: rs.moveToInsertRow();
0779: rs.updateInt(1, currentPosition + 2000);
0780: rs.deleteRow();
0781: } catch (SQLException se) {
0782: assertEquals("Expected exception", se.getSQLState()
0783: .substring(0, 5),
0784: INVALID_CURSOR_STATE_NO_CURRENT_ROW);
0785: }
0786:
0787: try {
0788: rs.moveToCurrentRow();
0789: rs.updateInt(1, currentPosition + 2000);
0790: rs.insertRow();
0791: } catch (SQLException se) {
0792: assertEquals("Expected exception", se.getSQLState()
0793: .substring(0, 5),
0794: CURSOR_NOT_POSITIONED_ON_INSERT_ROW);
0795: }
0796:
0797: rs.close();
0798:
0799: s.close();
0800: }
0801:
0802: /**
0803: * Test that you can scroll forward and update indexed records
0804: * in the scrollable ResultSet (not using FOR UPDATE).
0805: */
0806: public void testIndexedScrollInsensitiveUpdateCursorWithoutForUpdate1()
0807: throws SQLException {
0808: Statement s = createStatement(
0809: ResultSet.TYPE_SCROLL_INSENSITIVE,
0810: ResultSet.CONCUR_UPDATABLE);
0811: s.setCursorName(getNextCursorName());
0812: ResultSet rs = s
0813: .executeQuery("select * from t1 where a=1 or a=2");
0814:
0815: rs.next();
0816: rs.next();
0817: rs.previous();
0818: verifyTuple(rs);
0819: updateTuple(rs);
0820: s.close();
0821: }
0822:
0823: /**
0824: * Test that you can scroll forward and update indexed records
0825: * in the scrollable ResultSet (using FOR UPDATE).
0826: */
0827: public void testIndexedScrollInsensitiveUpdateCursorWithForUpdate1()
0828: throws SQLException {
0829: Statement s = createStatement(
0830: ResultSet.TYPE_SCROLL_INSENSITIVE,
0831: ResultSet.CONCUR_UPDATABLE);
0832: s.setCursorName(getNextCursorName());
0833: ResultSet rs = s
0834: .executeQuery("select * from t1 where a=1 or a=2 for update");
0835:
0836: rs.next();
0837: rs.next();
0838: rs.previous();
0839: verifyTuple(rs);
0840: updateTuple(rs);
0841: rs.close();
0842: s.close();
0843: }
0844:
0845: /**
0846: * Test update of a keyed record using scrollable updatable
0847: * resultset.
0848: */
0849: public void testPrimaryKeyUpdate1() throws SQLException {
0850: Statement s = createStatement(
0851: ResultSet.TYPE_SCROLL_INSENSITIVE,
0852: ResultSet.CONCUR_UPDATABLE);
0853: s.setCursorName(getNextCursorName());
0854: ResultSet rs = s.executeQuery("select * from t1");
0855:
0856: rs.last();
0857: rs.next();
0858: while (rs.previous()) {
0859: // Update the key of every second row.
0860: int key = rs.getInt(1);
0861: if (key % 2 == 0) {
0862: int newKey = -key;
0863: rs.updateInt(1, newKey);
0864: rs.updateRow();
0865: }
0866: }
0867: PreparedStatement ps = prepareStatement("select * from t1 where id=?");
0868: for (int i = 0; i < recordCount; i++) {
0869: int key = (i % 2 == 0) ? -i : i;
0870: ps.setInt(1, key);
0871: ResultSet rs2 = ps.executeQuery();
0872: assertTrue("Expected query to have 1 row", rs2.next());
0873: println("T1: Read Tuple:(" + rs2.getInt(1) + ","
0874: + rs2.getInt(2) + "," + rs2.getInt(3) + ")");
0875: assertEquals("Unexpected value of id", key, rs2.getInt(1));
0876: assertTrue("Did not expect more than 1 row, "
0877: + "however rs2.next returned another row", !rs2
0878: .next());
0879: }
0880: s.close();
0881: ps.close();
0882: }
0883:
0884: /**
0885: * Test update of a keyed record using other statement
0886: * object.
0887: */
0888: public void testOtherPrimaryKeyUpdate1() throws SQLException {
0889: Statement s = createStatement(
0890: ResultSet.TYPE_SCROLL_INSENSITIVE,
0891: ResultSet.CONCUR_UPDATABLE);
0892: s.setCursorName(getNextCursorName());
0893: ResultSet rs = s.executeQuery("select * from t1");
0894:
0895: rs.last();
0896: int primaryKey = rs.getInt(1);
0897: PreparedStatement ps = prepareStatement("update t1 set id = ? where id= ?");
0898: ps.setInt(1, -primaryKey);
0899: ps.setInt(2, primaryKey);
0900: assertEquals("Expected one row to be updated", 1, ps
0901: .executeUpdate());
0902:
0903: rs.updateInt(2, -555);
0904: rs.updateInt(3, -777);
0905: rs.updateRow();
0906:
0907: PreparedStatement ps2 = prepareStatement("select * from t1 where id=?");
0908: ps2.setInt(1, -primaryKey);
0909: ResultSet rs2 = ps2.executeQuery();
0910: assertTrue("Expected query to have 1 row", rs2.next());
0911: println("T1: Read Tuple:(" + rs2.getInt(1) + ","
0912: + rs2.getInt(2) + "," + rs2.getInt(3) + ")");
0913: assertEquals("Expected a=-555", -555, rs2.getInt(2));
0914: assertEquals("Expected b=-777", -777, rs2.getInt(3));
0915: assertTrue("Did not expect more than 1 row, however "
0916: + "rs2.next() returned another row", !rs2.next());
0917:
0918: s.close();
0919: ps.close();
0920: ps2.close();
0921: }
0922:
0923: /**
0924: * Test update of a keyed record using other both the
0925: * scrollable updatable resultset and using another statement
0926: * object.
0927: */
0928: public void testOtherAndOwnPrimaryKeyUpdate1() throws SQLException {
0929: Statement s = createStatement(
0930: ResultSet.TYPE_SCROLL_INSENSITIVE,
0931: ResultSet.CONCUR_UPDATABLE);
0932: s.setCursorName(getNextCursorName());
0933: ResultSet rs = s.executeQuery("select * from t1");
0934:
0935: rs.last();
0936: int primaryKey = rs.getInt(1);
0937: PreparedStatement ps = prepareStatement("update t1 set id = ? where id= ?");
0938: ps.setInt(1, -primaryKey);
0939: ps.setInt(2, primaryKey);
0940: assertEquals("Expected one row to be updated", 1, ps
0941: .executeUpdate());
0942: rs.updateInt(1, primaryKey * 10);
0943: rs.updateInt(2, -555);
0944: rs.updateInt(3, -777);
0945: rs.updateRow();
0946:
0947: PreparedStatement ps2 = prepareStatement("select * from t1 where id=?");
0948: ps2.setInt(1, primaryKey * 10);
0949: ResultSet rs2 = ps2.executeQuery();
0950: assertTrue("Expected query to have 1 row", rs2.next());
0951: println("T1: Read Tuple:(" + rs2.getInt(1) + ","
0952: + rs2.getInt(2) + "," + rs2.getInt(3) + ")");
0953: assertEquals("Expected a=-555", -555, rs2.getInt(2));
0954: assertEquals("Expected b=-777", -777, rs2.getInt(3));
0955: assertTrue("Did not expect more than 1 row, however "
0956: + "rs2.next() returned another row", !rs2.next());
0957:
0958: s.close();
0959: ps.close();
0960: ps2.close();
0961: }
0962:
0963: /**
0964: * Update multiple keyed records using scrollable updatable resultset
0965: */
0966: public void testMultipleKeyUpdates() throws SQLException {
0967: Statement s = createStatement(
0968: ResultSet.TYPE_SCROLL_INSENSITIVE,
0969: ResultSet.CONCUR_UPDATABLE);
0970: s.setCursorName(getNextCursorName());
0971: ResultSet rs = s.executeQuery("select * from t1");
0972:
0973: rs.last();
0974: int primaryKey = rs.getInt(1);
0975: PreparedStatement ps = s.getConnection().prepareStatement(
0976: "update t1 set id = ? where id= ?");
0977: ps.setInt(1, -primaryKey);
0978: ps.setInt(2, primaryKey);
0979: assertEquals("Expected one row to be updated", 1, ps
0980: .executeUpdate());
0981: rs.updateInt(1, primaryKey * 10);
0982: rs.updateInt(2, -555);
0983: rs.updateInt(3, -777);
0984: rs.updateRow();
0985: rs.first();
0986: rs.last();
0987: for (int i = 0; i < 10; i++) {
0988: rs.first();
0989: rs.last();
0990: rs.next();
0991: rs.previous();
0992: rs.updateInt(1, primaryKey * 10 + i);
0993: rs.updateInt(2, (-555 - i));
0994: rs.updateInt(3, (-777 - i));
0995: rs.updateRow();
0996: }
0997: rs.close();
0998: s.close();
0999: }
1000:
1001: /**
1002: * Test update indexed records using scrollable updatable resultset
1003: */
1004: public void testSecondaryIndexKeyUpdate1() throws SQLException {
1005:
1006: Statement s = createStatement(
1007: ResultSet.TYPE_SCROLL_INSENSITIVE,
1008: ResultSet.CONCUR_UPDATABLE);
1009: s.setCursorName(getNextCursorName());
1010: ResultSet rs = s.executeQuery("select * from t1");
1011:
1012: rs.last();
1013: rs.next();
1014: int newKey = 0;
1015: while (rs.previous()) {
1016: // Update the secondary key of all rows
1017: rs.updateInt(2, newKey--);
1018: rs.updateRow();
1019: }
1020: PreparedStatement ps = prepareStatement("select * from t1 where a=?");
1021: for (int i = 0; i < recordCount; i++) {
1022: int key = -i;
1023: ps.setInt(1, key);
1024: ResultSet rs2 = ps.executeQuery();
1025: assertTrue("Expected query to have 1 row", rs2.next());
1026: println("T1: Read Tuple:(" + rs2.getInt(1) + ","
1027: + rs2.getInt(2) + "," + rs2.getInt(3) + ")");
1028: assertEquals("Unexpected value of id", key, rs2.getInt(2));
1029: assertTrue("Did not expect more than 1 row, "
1030: + "however rs2.next returned another row", !rs2
1031: .next());
1032: }
1033:
1034: s.close();
1035: ps.close();
1036: }
1037:
1038: /**
1039: * Test update indexed records using other statement object
1040: * and using resultset.
1041: */
1042: public void testOtherSecondaryKeyUpdate1() throws SQLException {
1043: Statement s = createStatement(
1044: ResultSet.TYPE_SCROLL_INSENSITIVE,
1045: ResultSet.CONCUR_UPDATABLE);
1046: s.setCursorName(getNextCursorName());
1047: ResultSet rs = s.executeQuery("select * from t1");
1048:
1049: rs.last();
1050: int indexedKey = rs.getInt(2);
1051: PreparedStatement ps = prepareStatement("update t1 set a = ? where a= ?");
1052: ps.setInt(1, -indexedKey);
1053: ps.setInt(2, indexedKey);
1054: assertEquals("Expected one row to be updated", 1, ps
1055: .executeUpdate());
1056:
1057: rs.updateInt(1, -555);
1058: rs.updateInt(3, -777);
1059: rs.updateRow();
1060:
1061: PreparedStatement ps2 = prepareStatement("select * from t1 where a=?");
1062: ps2.setInt(1, -indexedKey);
1063: ResultSet rs2 = ps2.executeQuery();
1064: assertTrue("Expected query to have 1 row", rs2.next());
1065: println("T1: Read Tuple:(" + rs2.getInt(1) + ","
1066: + rs2.getInt(2) + "," + rs2.getInt(3) + ")");
1067: assertEquals("Expected id=-555", -555, rs2.getInt(1));
1068: assertEquals("Expected b=-777", -777, rs2.getInt(3));
1069: assertTrue("Did not expect more than 1 row, however "
1070: + "rs2.next() returned another row", !rs2.next());
1071:
1072: s.close();
1073: ps.close();
1074: ps2.close();
1075: }
1076:
1077: /**
1078: * Test scrolling in a read only resultset
1079: */
1080: public void testScrollInsensitiveReadOnly1() throws SQLException {
1081: Statement s = createStatement(
1082: ResultSet.TYPE_SCROLL_INSENSITIVE,
1083: ResultSet.CONCUR_READ_ONLY);
1084: s.setCursorName(getNextCursorName());
1085: ResultSet rs = s.executeQuery("select * from t1");
1086:
1087: scrollForward(rs);
1088: scrollBackward(rs);
1089: rs.close();
1090: s.close();
1091: }
1092:
1093: /**
1094: * Test updating a forward only resultset (with FOR UPDATE)
1095: */
1096: public void testForwardOnlyConcurUpdatableWithForUpdate1()
1097: throws SQLException {
1098: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
1099: ResultSet.CONCUR_UPDATABLE);
1100: s.setCursorName(getNextCursorName());
1101: ResultSet rs = s.executeQuery("select * from t1 for update");
1102:
1103: scrollForwardAndUpdate(rs);
1104: rs.close();
1105: s.close();
1106: }
1107:
1108: /**
1109: * Test updating a forward only resultset (without FOR UPDATE)
1110: */
1111: public void testForwardOnlyConcurUpdatableWithoutForUpdate1()
1112: throws SQLException {
1113: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
1114: ResultSet.CONCUR_UPDATABLE);
1115: s.setCursorName(getNextCursorName());
1116: ResultSet rs = s.executeQuery("select * from t1");
1117:
1118: scrollForwardAndUpdate(rs);
1119: rs.close();
1120: s.close();
1121: }
1122:
1123: /**
1124: * Test updating a forward only resultset (without FOR UPDATE)
1125: * and using positioned update
1126: */
1127: public void testPositionedUpdateWithoutForUpdate1()
1128: throws SQLException {
1129: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
1130: ResultSet.CONCUR_UPDATABLE);
1131: s.setCursorName("MYCURSOR");
1132: ResultSet rs = s.executeQuery("select * from t1");
1133:
1134: scrollForwardAndUpdatePositioned(rs);
1135: rs.close();
1136: s.close();
1137: }
1138:
1139: /**
1140: * Test updating a forward only resultset (with FOR UPDATE)
1141: * and using positioned update
1142: */
1143: public void testPositionedUpdateWithForUpdate1()
1144: throws SQLException {
1145: Statement s = createStatement();
1146: s.setCursorName(getNextCursorName());
1147: ResultSet rs = s.executeQuery("select * from t1 for update");
1148:
1149: scrollForwardAndUpdatePositioned(rs);
1150: rs.close();
1151: s.close();
1152: }
1153:
1154: /**
1155: * Test positioned update of a scrollable resultset (with FOR UPDATE)
1156: */
1157: public void testScrollablePositionedUpdateWithForUpdate1()
1158: throws SQLException {
1159: Statement s = createStatement(
1160: ResultSet.TYPE_SCROLL_INSENSITIVE,
1161: ResultSet.CONCUR_READ_ONLY);
1162: s.setCursorName("MYCURSOR");
1163: ResultSet rs = s.executeQuery("select * from t1 for update");
1164:
1165: rs.next();
1166: int pKey = rs.getInt(1);
1167: rs.previous();
1168: rs.next();
1169: assertEquals(
1170: "Expecting to be on the same row after previous() "
1171: + "+ next() ", pKey, rs.getInt(1));
1172: rs.next();
1173: rs.previous();
1174: assertEquals("Expecting to be on the same row after next() + "
1175: + "previous()", pKey, rs.getInt(1));
1176: final int previousA = rs.getInt(2);
1177: final int previousB = rs.getInt(3);
1178: println(rs.getCursorName());
1179: PreparedStatement ps = prepareStatement("update T1 set a=?,b=? where current of "
1180: + rs.getCursorName());
1181: ps.setInt(1, 666);
1182: ps.setInt(2, 777);
1183: ps.executeUpdate();
1184: rs.next();
1185: rs.previous();
1186: assertEquals(
1187: "Expected to be on the same row after next() + previous()",
1188: pKey, rs.getInt(1));
1189: assertEquals("Expected row to be updated by own change, "
1190: + " however did not get updated value for column a",
1191: 666, rs.getInt(2));
1192: assertEquals(
1193: "Expected row to be updated by own change, however did "
1194: + "not get updated value for column b", 777, rs
1195: .getInt(3));
1196: rs.close();
1197: s.setCursorName(getNextCursorName());
1198: rs = s.executeQuery("select * from t1 order by b");
1199:
1200: while (rs.next()) {
1201: if (rs.getInt(1) == pKey) {
1202: assertEquals("Expected row with primary key = " + pKey
1203: + " to be updated", 666, rs.getInt(2));
1204: assertEquals("Expected row with primary key = " + pKey
1205: + " to be updated", 777, rs.getInt(3));
1206: } else {
1207: println("Got tuple (" + rs.getInt(1) + ","
1208: + rs.getInt(2) + "," + rs.getInt(3) + ","
1209: + rs.getString(4) + ")");
1210: }
1211: }
1212:
1213: s.close();
1214: ps.close();
1215: }
1216:
1217: /**
1218: * Test update of a scrollable resultset (with FOR UPDATE)
1219: * Only scrolling forward
1220: */
1221: public void testScrollInsensitiveConcurUpdatableWithForUpdate1()
1222: throws SQLException {
1223: Statement s = createStatement(
1224: ResultSet.TYPE_SCROLL_INSENSITIVE,
1225: ResultSet.CONCUR_UPDATABLE);
1226: s.setCursorName(getNextCursorName());
1227: ResultSet rs = s.executeQuery("select * from t1 for update");
1228: scrollForwardAndUpdate(rs);
1229: rs.close();
1230: s.close();
1231: }
1232:
1233: /**
1234: * Test update of a scrollable resultset (with FOR UPDATE)
1235: * Scrolling forward and backward.
1236: */
1237: public void testScrollInsensitiveConcurUpdatableWithForUpdate2()
1238: throws SQLException {
1239: Statement s = createStatement(
1240: ResultSet.TYPE_SCROLL_INSENSITIVE,
1241: ResultSet.CONCUR_UPDATABLE);
1242: assertEquals("Invalid resultset concurrency on statement",
1243: ResultSet.CONCUR_UPDATABLE, s.getResultSetConcurrency());
1244: s.setCursorName(getNextCursorName());
1245: ResultSet rs = s.executeQuery("select * from t1 for update");
1246:
1247: assertEquals("Invalid resultset concurrency on resultset",
1248: ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
1249: scrollForward(rs);
1250: scrollBackwardAndUpdate(rs);
1251: rs.close();
1252: s.close();
1253: }
1254:
1255: /**
1256: * Test update of a scrollable resultset
1257: * Scrolling forward and backward. Then open another
1258: * resultset and verify the data.
1259: */
1260: private void testScrollInsensistiveConurUpdatable3(ResultSet rs)
1261: throws SQLException {
1262: while (rs.next()) {
1263: }
1264: while (rs.previous()) {
1265: int a = rs.getInt(1);
1266: int b = rs.getInt(2);
1267: int id = b - 17 - a;
1268: int newA = 1000;
1269: int newB = id + newA + 17;
1270: rs.updateInt(1, newA); // Set a to 1000
1271: rs.updateInt(2, newB); // Set b to checksum value
1272: rs.updateRow();
1273:
1274: assertEquals("Expected a to be 1000", 1000, rs.getInt(1));
1275: }
1276: int count = 0;
1277: while (rs.next()) {
1278: int a = rs.getInt(1);
1279: count++;
1280: assertEquals("Incorrect row updated for row " + count,
1281: 1000, a);
1282: }
1283: assertEquals(
1284: "Expected count to be the same as number of records",
1285: recordCount, count);
1286: while (rs.previous()) {
1287: int a = rs.getInt(1);
1288: count--;
1289: assertEquals("Incorrect row updated for row " + count,
1290: 1000, a);
1291: }
1292: rs.close();
1293: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
1294: ResultSet.CONCUR_READ_ONLY);
1295: s.setCursorName(getNextCursorName());
1296: rs = s.executeQuery("select * from t1");
1297:
1298: while (rs.next()) {
1299: int id = rs.getInt(1);
1300: int a = rs.getInt(2);
1301: int b = rs.getInt(3);
1302: println("Updated tuple:" + id + "," + a + "," + b);
1303: }
1304: s.close();
1305: }
1306:
1307: /**
1308: * Test update of a scrollable resultset (with FOR UPDATE)
1309: * Scrolling forward and backward. Then open another
1310: * resultset and verify the data.
1311: */
1312: public void testScrollInsensitiveConcurUpdatableWithForUpdate3()
1313: throws SQLException {
1314: Statement s = createStatement(
1315: ResultSet.TYPE_SCROLL_INSENSITIVE,
1316: ResultSet.CONCUR_UPDATABLE);
1317: s.setCursorName(getNextCursorName());
1318: ResultSet rs = s.executeQuery("select a,b from t1 for update");
1319:
1320: testScrollInsensistiveConurUpdatable3(rs);
1321: s.close();
1322: }
1323:
1324: /**
1325: * Test update of a scrollable resultset (without FOR UPDATE)
1326: * Scrolling forward only
1327: */
1328: public void testScrollInsensitiveConcurUpdatableWithoutForUpdate1()
1329: throws SQLException {
1330: Statement s = createStatement(
1331: ResultSet.TYPE_SCROLL_INSENSITIVE,
1332: ResultSet.CONCUR_UPDATABLE);
1333: s.setCursorName(getNextCursorName());
1334: ResultSet rs = s.executeQuery("select * from t1");
1335:
1336: scrollForwardAndUpdate(rs);
1337: rs.close();
1338: s.close();
1339: }
1340:
1341: /**
1342: * Test update of a scrollable resultset (without FOR UPDATE)
1343: * Scrolling forward and backward.
1344: */
1345: public void testScrollInsensitiveConcurUpdatableWithoutForUpdate2()
1346: throws SQLException {
1347: Statement s = createStatement(
1348: ResultSet.TYPE_SCROLL_INSENSITIVE,
1349: ResultSet.CONCUR_UPDATABLE);
1350: s.setCursorName(getNextCursorName());
1351: ResultSet rs = s.executeQuery("select * from t1");
1352:
1353: scrollForward(rs);
1354: scrollBackwardAndUpdate(rs);
1355: rs.close();
1356: s.close();
1357: }
1358:
1359: /**
1360: * Test update of a scrollable resultset (without FOR UPDATE)
1361: * Scrolling forward and backward. Then open another
1362: * resultset and verify the data.
1363: */
1364: public void testScrollInsensitiveConcurUpdatableWithoutForUpdate3()
1365: throws SQLException {
1366: Statement s = createStatement(
1367: ResultSet.TYPE_SCROLL_INSENSITIVE,
1368: ResultSet.CONCUR_UPDATABLE);
1369: s.setCursorName(getNextCursorName());
1370: ResultSet rs = s.executeQuery("select a,b from t1");
1371:
1372: testScrollInsensistiveConurUpdatable3(rs);
1373: s.close();
1374: }
1375:
1376: /**
1377: * Check that detectability methods throw the correct exception
1378: * when called in an illegal row state, that is, somehow not
1379: * positioned on a row. Minion of testDetectabilityExceptions.
1380: *
1381: * @param rs An open updatable result set.
1382: * @param state A string describing the illegal state.
1383: * @return No return value.
1384: */
1385: private void checkDetectabilityCallsOutsideRow(ResultSet rs,
1386: String state) {
1387: boolean b;
1388:
1389: try {
1390: b = rs.rowUpdated();
1391: fail("rowUpdated while " + state
1392: + " did not throw exception: " + b);
1393: } catch (SQLException e) {
1394: assertEquals(e.getSQLState(),
1395: INVALID_CURSOR_STATE_NO_CURRENT_ROW);
1396: }
1397:
1398: try {
1399: b = rs.rowDeleted();
1400: fail("rowdeleted while " + state
1401: + " did not throw exception: " + b);
1402: } catch (SQLException e) {
1403: assertEquals(e.getSQLState(),
1404: INVALID_CURSOR_STATE_NO_CURRENT_ROW);
1405: }
1406:
1407: try {
1408: b = rs.rowInserted();
1409: fail("rowInserted while " + state
1410: + " did not throw exception: " + b);
1411: } catch (SQLException e) {
1412: assertEquals(e.getSQLState(),
1413: INVALID_CURSOR_STATE_NO_CURRENT_ROW);
1414: }
1415: }
1416:
1417: /**
1418: * Test that rowUpdated() and rowDeleted() methods both return true when
1419: * the row has first been updated and then deleted using the updateRow()
1420: * and deleteRow() methods.
1421: */
1422: public void testRowUpdatedAndRowDeleted() throws SQLException {
1423: Statement s = createStatement(
1424: ResultSet.TYPE_SCROLL_INSENSITIVE,
1425: ResultSet.CONCUR_UPDATABLE);
1426: s.setCursorName(getNextCursorName());
1427: ResultSet rs = s.executeQuery("select a,b from t1");
1428: rs.next();
1429: rs.updateInt(1, rs.getInt(1) + 2 * recordCount);
1430: rs.updateRow();
1431: assertTrue("Expected rowUpdated() to return true", rs
1432: .rowUpdated());
1433: rs.deleteRow();
1434: rs.next();
1435: rs.previous();
1436: assertTrue("Expected rowUpdated() to return true", rs
1437: .rowUpdated());
1438: assertTrue("Expected rowDeleted() to return true", rs
1439: .rowDeleted());
1440: rs.next();
1441: assertFalse("Expected rowUpdated() to return false", rs
1442: .rowUpdated());
1443: assertFalse("Expected rowDeleted() to return false", rs
1444: .rowDeleted());
1445: rs.previous();
1446: assertTrue("Expected rowUpdated() to return true", rs
1447: .rowUpdated());
1448: assertTrue("Expected rowDeleted() to return true", rs
1449: .rowDeleted());
1450: rs.close();
1451: s.close();
1452: }
1453:
1454: /**
1455: * Test that the JDBC detectability calls throw correct exceptions when
1456: * called in in wrong row states.
1457: * This is done for both supported updatable result set types.
1458: */
1459: public void testDetectabilityExceptions() throws SQLException {
1460: Statement s = createStatement(
1461: ResultSet.TYPE_SCROLL_INSENSITIVE,
1462: ResultSet.CONCUR_UPDATABLE);
1463: ResultSet rs = s.executeQuery("select * from t1");
1464:
1465: checkDetectabilityCallsOutsideRow(rs, "before positioning");
1466:
1467: rs.moveToInsertRow();
1468: checkDetectabilityCallsOutsideRow(rs,
1469: "on insertRow before positioning");
1470:
1471: rs.next();
1472: rs.moveToInsertRow();
1473: checkDetectabilityCallsOutsideRow(rs, "on insertRow");
1474: rs.moveToCurrentRow(); // needed until to DERBY-1322 is fixed
1475:
1476: rs.beforeFirst();
1477: checkDetectabilityCallsOutsideRow(rs, "on beforeFirst row");
1478:
1479: rs.afterLast();
1480: checkDetectabilityCallsOutsideRow(rs, "on afterLast row");
1481:
1482: rs.first();
1483: rs.deleteRow();
1484: checkDetectabilityCallsOutsideRow(rs, "after deleteRow");
1485:
1486: rs.last();
1487: rs.deleteRow();
1488: checkDetectabilityCallsOutsideRow(rs,
1489: "after deleteRow of last row");
1490:
1491: rs.close();
1492: s.close();
1493:
1494: // Not strictly SUR, but fixed in same patch, so we test it here.
1495: s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
1496: ResultSet.CONCUR_UPDATABLE);
1497: rs = s.executeQuery("select * from t1");
1498:
1499: checkDetectabilityCallsOutsideRow(rs, "before FO positioning");
1500:
1501: rs.moveToInsertRow();
1502: checkDetectabilityCallsOutsideRow(rs,
1503: "on insertRow before FO positioning");
1504:
1505: rs.next();
1506: rs.moveToInsertRow();
1507: checkDetectabilityCallsOutsideRow(rs, "on FO insertRow");
1508:
1509: rs.next();
1510: rs.updateInt(2, 666);
1511: rs.updateRow();
1512: checkDetectabilityCallsOutsideRow(rs, "after FO updateRow");
1513:
1514: rs.next();
1515: rs.deleteRow();
1516: checkDetectabilityCallsOutsideRow(rs, "after FO deleteRow");
1517:
1518: while (rs.next()) {
1519: }
1520: ;
1521: checkDetectabilityCallsOutsideRow(rs, "after FO emptied out");
1522:
1523: rs.close();
1524: s.close();
1525: }
1526:
1527: /**
1528: * DERBY-1481 - ResultSet.beforeFirst() gives protocol error on scrollable,
1529: * updatable result sets that are downgraded to read-only
1530: *
1531: * Check that no exception is thrown when calling positioning methods on a
1532: * result set that has been downgraded to read-only.
1533: *
1534: */
1535: public void testDowngradeToScrollReadOnly() throws SQLException {
1536: Statement s = createStatement(
1537: ResultSet.TYPE_SCROLL_INSENSITIVE,
1538: ResultSet.CONCUR_UPDATABLE);
1539: ResultSet rs = s.executeQuery("select * from t1 order by b");
1540:
1541: // check that the ResultSet was downgraded
1542: assertWarning(rs.getWarnings(),
1543: QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
1544:
1545: // call positioning methods
1546: rs.next();
1547: rs.next();
1548: rs.previous();
1549: rs.relative(1);
1550: rs.absolute(3);
1551: rs.relative(-1);
1552: rs.first();
1553: rs.last();
1554: rs.beforeFirst();
1555: rs.afterLast();
1556:
1557: // close result set and statement
1558: rs.close();
1559: s.close();
1560: }
1561:
1562: /**
1563: * Get a cursor name. We use the same cursor name for all cursors.
1564: */
1565: private final String getNextCursorName() {
1566: return "MYCURSOR";
1567: }
1568:
1569: /**
1570: * The suite contains all testcases in this class running on different
1571: * data models
1572: */
1573: public static Test suite() {
1574:
1575: TestSuite mainSuite = new TestSuite();
1576:
1577: // DB2 client doesn't support this functionality
1578: if (usingDerbyNet())
1579: return mainSuite;
1580:
1581: // Iterate over all data models and decorate the tests:
1582: for (Iterator i = SURDataModelSetup.SURDataModel.values()
1583: .iterator(); i.hasNext();) {
1584:
1585: SURDataModelSetup.SURDataModel model = (SURDataModelSetup.SURDataModel) i
1586: .next();
1587:
1588: TestSuite suite = new TestSuite(SURTest.class);
1589: TestSetup decorator = new SURDataModelSetup(suite, model);
1590:
1591: mainSuite.addTest(decorator);
1592: }
1593:
1594: return mainSuite;
1595: }
1596:
1597: protected void tearDown() throws Exception {
1598: super.tearDown();
1599: con = null;
1600: }
1601: }
|