0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.lang.updatableResultSet
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, software
0015: distributed under the License is distributed on an "AS IS" BASIS,
0016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017: See the License for the specific language governing permissions and
0018: limitations under the License.
0019:
0020: */
0021:
0022: package org.apache.derbyTesting.functionTests.tests.lang;
0023:
0024: import java.sql.CallableStatement;
0025: import java.sql.Connection;
0026: import java.sql.DatabaseMetaData;
0027: import java.sql.DriverManager;
0028: import java.sql.PreparedStatement;
0029: import java.sql.ResultSet;
0030: import java.sql.ResultSetMetaData;
0031: import java.sql.Statement;
0032: import java.sql.SQLException;
0033: import java.sql.SQLWarning;
0034:
0035: import org.apache.derby.tools.ij;
0036: import org.apache.derby.tools.JDBCDisplayUtil;
0037: import org.apache.derby.iapi.services.info.JVMInfo;
0038: import org.apache.derbyTesting.functionTests.util.BigDecimalHandler;
0039: import org.apache.derbyTesting.functionTests.util.TestUtil;
0040:
0041: import java.math.BigDecimal;
0042: import java.sql.Array;
0043: import java.sql.Blob;
0044: import java.sql.Clob;
0045: import java.sql.Date;
0046: import java.sql.Time;
0047: import java.sql.Timestamp;
0048:
0049: /**
0050: This tests JDBC 2.0 updateable resutlset - deleteRow, updateRow api
0051: */
0052: public class updatableResultSet {
0053:
0054: private static boolean HAVE_BIG_DECIMAL;
0055:
0056: static {
0057: if (BigDecimalHandler.representation != BigDecimalHandler.BIGDECIMAL_REPRESENTATION)
0058: HAVE_BIG_DECIMAL = false;
0059: else
0060: HAVE_BIG_DECIMAL = true;
0061: }
0062:
0063: private static Connection conn;
0064: private static DatabaseMetaData dbmt;
0065: private static Statement stmt, stmt1;
0066: private static ResultSet rs, rs1;
0067: private static PreparedStatement pStmt = null;
0068: private static CallableStatement callStmt = null;
0069: static SQLWarning warnings;
0070:
0071: //test all the supported SQL datatypes using updateXXX methods
0072: private static String[] allSQLTypes = { "SMALLINT", "INTEGER",
0073: "BIGINT", "DECIMAL(10,5)", "REAL", "DOUBLE", "CHAR(60)",
0074: "VARCHAR(60)", "LONG VARCHAR", "CHAR(2) FOR BIT DATA",
0075: "VARCHAR(2) FOR BIT DATA", "LONG VARCHAR FOR BIT DATA",
0076: "CLOB(1k)", "DATE", "TIME", "TIMESTAMP", "BLOB(1k)",
0077:
0078: };
0079:
0080: //names for column names to test all the supported SQL datatypes using updateXXX methods
0081: private static String[] ColumnNames = { "SMALLINTCOL",
0082: "INTEGERCOL", "BIGINTCOL", "DECIMALCOL", "REALCOL",
0083: "DOUBLECOL", "CHARCOL", "VARCHARCOL", "LONGVARCHARCOL",
0084: "CHARFORBITCOL", "VARCHARFORBITCOL", "LVARCHARFORBITCOL",
0085: "CLOBCOL", "DATECOL", "TIMECOL", "TIMESTAMPCOL", "BLOBCOL",
0086:
0087: };
0088:
0089: //data to test all the supported SQL datatypes using updateXXX methods
0090: private static String[][] SQLData = { { "11", "22" }, // SMALLINT
0091: { "111", "1111" }, // INTEGER
0092: { "22", "222" }, // BIGINT
0093: { "3.3", "3.33" }, // DECIMAL(10,5)
0094: { "4.4", "4.44" }, // REAL,
0095: { "5.5", "5.55" }, // DOUBLE
0096: { "'1992-01-06'", "'1992'" }, // CHAR(60)
0097: { "'1992-01-07'", "'1992'" }, //VARCHAR(60)",
0098: { "'1992-01-08'", "'1992'" }, // LONG VARCHAR
0099: { "X'10'", "X'10aa'" }, // CHAR(2) FOR BIT DATA
0100: { "X'10'", "X'10bb'" }, // VARCHAR(2) FOR BIT DATA
0101: { "X'10'", "X'10cc'" }, //LONG VARCHAR FOR BIT DATA
0102: { "'13'", "'14'" }, //CLOB(1k)
0103: { "'2000-01-01'", "'2000-01-01'" }, // DATE
0104: { "'15:30:20'", "'15:30:20'" }, // TIME
0105: { "'2000-01-01 15:30:20'", "'2000-01-01 15:30:20'" }, // TIMESTAMP
0106: { "X'1020'", "X'10203040'" } // BLOB
0107: };
0108:
0109: //used for printing useful messages about the test
0110: private static String[] allUpdateXXXNames = { "updateShort",
0111: "updateInt", "updateLong", "updateBigDecimal",
0112: "updateFloat", "updateDouble", "updateString",
0113: "updateAsciiStream", "updateCharacterStream", "updateByte",
0114: "updateBytes", "updateBinaryStream", "updateClob",
0115: "updateDate", "updateTime", "updateTimestamp",
0116: "updateBlob", "updateBoolean", "updateNull", "updateArray",
0117: "updateRef"
0118:
0119: };
0120:
0121: //I have constructed following table based on if combination of datatype and updateXXX method would work or not.
0122: public static final String[][] updateXXXRulesTableForEmbedded = {
0123:
0124: // Types. u u u u u u u u u u u u u u u u u u u u u
0125: // p p p p p p p p p p p p p p p p p p p p p
0126: // d d d d d d d d d d d d d d d d d d d d d
0127: // a a a a a a a a a a a a a a a a a a a a a
0128: // t t t t t t t t t t t t t t t t t t t t t
0129: // e e e e e e e e e e e e e e e e e e e e e
0130: // S I L B F D S A C B B B C D T T B B N A R
0131: // h n o i l o t s h y y i l a i i l o u r e
0132: // o t n g o u r c a t t n o t m m o o l r f
0133: // r g D a b i i r e e a b e e e b l l a
0134: // t e t l n i c s r s e y
0135: // c e g S t y t a
0136: // i t e S a n
0137: // m r r t m
0138: // a e S r p
0139: // l a t e
0140: // m r a
0141: // e m
0142: // a
0143: // m
0144: /* 0 SMALLINT */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0145: "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR",
0146: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0147: "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
0148: /* 1 INTEGER */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0149: "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR",
0150: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0151: "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
0152: /* 2 BIGINT */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0153: "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR",
0154: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0155: "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
0156: /* 3 DECIMAL */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0157: "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR",
0158: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0159: "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
0160: /* 4 REAL */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0161: "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR",
0162: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0163: "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
0164: /* 5 DOUBLE */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0165: "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR",
0166: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0167: "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
0168: /* 6 CHAR */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0169: "PASS", "PASS", "PASS", "PASS", "PASS", "PASS",
0170: "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR",
0171: "PASS", "PASS", "ERROR", "ERROR" },
0172: /* 7 VARCHAR */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0173: "PASS", "PASS", "PASS", "PASS", "PASS", "PASS",
0174: "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR",
0175: "PASS", "PASS", "ERROR", "ERROR" },
0176: /* 8 LONGVARCHAR */{ "PASS", "PASS", "PASS", "PASS",
0177: "PASS", "PASS", "PASS", "PASS", "PASS", "PASS",
0178: "PASS", "ERROR", "ERROR", "PASS", "PASS", "PASS",
0179: "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
0180: /* 9 CHAR FOR BIT */{ "ERROR", "ERROR", "ERROR", "ERROR",
0181: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0182: "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR",
0183: "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
0184: /* 10 VARCH. BIT */{ "ERROR", "ERROR", "ERROR", "ERROR",
0185: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0186: "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR",
0187: "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
0188: /* 11 LONGVAR. BIT */{ "ERROR", "ERROR", "ERROR", "ERROR",
0189: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0190: "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR",
0191: "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
0192: /* 12 CLOB */{ "ERROR", "ERROR", "ERROR", "ERROR",
0193: "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR",
0194: "ERROR", "ERROR", "PASS", "ERROR", "ERROR",
0195: "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
0196: /* 13 DATE */{ "ERROR", "ERROR", "ERROR", "ERROR",
0197: "ERROR", "ERROR", "PASS", "ERROR", "ERROR",
0198: "ERROR", "ERROR", "ERROR", "ERROR", "PASS",
0199: "ERROR", "PASS", "ERROR", "ERROR", "PASS", "ERROR",
0200: "ERROR" },
0201: /* 14 TIME */{ "ERROR", "ERROR", "ERROR", "ERROR",
0202: "ERROR", "ERROR", "PASS", "ERROR", "ERROR",
0203: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0204: "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR",
0205: "ERROR" },
0206: /* 15 TIMESTAMP */{ "ERROR", "ERROR", "ERROR", "ERROR",
0207: "ERROR", "ERROR", "PASS", "ERROR", "ERROR",
0208: "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS",
0209: "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
0210: /* 16 BLOB */{ "ERROR", "ERROR", "ERROR", "ERROR",
0211: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0212: "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR",
0213: "ERROR", "PASS", "ERROR", "PASS", "ERROR", "ERROR" },
0214:
0215: };
0216:
0217: //I have constructed following table for network server based on if combination of datatype and updateXXX method would work or not.
0218: public static final String[][] updateXXXRulesTableForNetworkServer = {
0219:
0220: // Types. u u u u u u u u u u u u u u u u u u u u u
0221: // p p p p p p p p p p p p p p p p p p p p p
0222: // d d d d d d d d d d d d d d d d d d d d d
0223: // a a a a a a a a a a a a a a a a a a a a a
0224: // t t t t t t t t t t t t t t t t t t t t t
0225: // e e e e e e e e e e e e e e e e e e e e e
0226: // S I L B F D S A C B B B C D T T B B N A R
0227: // h n o i l o t s h y y i l a i i l o u r e
0228: // o t n g o u r c a t t n o t m m o o l r f
0229: // r g D a b i i r e e a b e e e b l l a
0230: // t e t l n i c s r s e y
0231: // c e g S t y t a
0232: // i t e S a n
0233: // m r r t m
0234: // a e S r p
0235: // l a t e
0236: // m r a
0237: // e m
0238: // a
0239: // m
0240: /* 0 SMALLINT */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0241: "PASS", "ERROR", "ERROR", "ERROR", "PASS", "ERROR",
0242: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0243: "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
0244: /* 1 INTEGER */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0245: "PASS", "ERROR", "ERROR", "ERROR", "PASS", "ERROR",
0246: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0247: "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
0248: /* 2 BIGINT */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0249: "PASS", "ERROR", "ERROR", "ERROR", "PASS", "ERROR",
0250: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0251: "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
0252: /* 3 DECIMAL */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0253: "PASS", "ERROR", "ERROR", "ERROR", "PASS", "ERROR",
0254: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0255: "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
0256: /* 4 REAL */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0257: "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR",
0258: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0259: "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
0260: /* 5 DOUBLE */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0261: "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR",
0262: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0263: "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
0264: /* 6 CHAR */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0265: "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR",
0266: "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR",
0267: "PASS", "PASS", "ERROR", "ERROR" },
0268: /* 7 VARCHAR */{ "PASS", "PASS", "PASS", "PASS", "PASS",
0269: "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR",
0270: "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR",
0271: "PASS", "PASS", "ERROR", "ERROR" },
0272: /* 8 LONGVARCHAR */{ "PASS", "PASS", "PASS", "PASS",
0273: "PASS", "PASS", "PASS", "PASS", "PASS", "PASS",
0274: "ERROR", "ERROR", "ERROR", "PASS", "PASS", "PASS",
0275: "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
0276: /* 9 CHAR FOR BIT */{ "ERROR", "ERROR", "ERROR", "ERROR",
0277: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0278: "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR",
0279: "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
0280: /* 10 VARCH. BIT */{ "ERROR", "ERROR", "ERROR", "ERROR",
0281: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0282: "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR",
0283: "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
0284: /* 11 LONGVAR. BIT */{ "ERROR", "ERROR", "ERROR", "ERROR",
0285: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0286: "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR",
0287: "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
0288: /* 12 CLOB */{ "ERROR", "ERROR", "ERROR", "ERROR",
0289: "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR",
0290: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0291: "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
0292: /* 13 DATE */{ "ERROR", "ERROR", "ERROR", "ERROR",
0293: "ERROR", "ERROR", "PASS", "ERROR", "ERROR",
0294: "ERROR", "ERROR", "ERROR", "ERROR", "PASS",
0295: "ERROR", "PASS", "ERROR", "ERROR", "PASS", "ERROR",
0296: "ERROR" },
0297: /* 14 TIME */{ "ERROR", "ERROR", "ERROR", "ERROR",
0298: "ERROR", "ERROR", "PASS", "ERROR", "ERROR",
0299: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0300: "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR",
0301: "ERROR" },
0302: /* 15 TIMESTAMP */{ "ERROR", "ERROR", "ERROR", "ERROR",
0303: "ERROR", "ERROR", "PASS", "ERROR", "ERROR",
0304: "ERROR", "ERROR", "ERROR", "ERROR", "PASS",
0305: "ERROR", "PASS", "ERROR", "ERROR", "PASS", "ERROR",
0306: "ERROR" },
0307: /* 16 BLOB */{ "ERROR", "ERROR", "ERROR", "ERROR",
0308: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0309: "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR",
0310: "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
0311:
0312: };
0313:
0314: public static void main(String[] args) {
0315: System.out
0316: .println("Start testing delete and update using JDBC2.0 updateable resultset apis");
0317:
0318: try {
0319: // use the ij utility to read the property file and
0320: // make the initial connection.
0321: ij.getPropertyArg(args);
0322: conn = ij.startJBMS();
0323:
0324: setup(true);
0325:
0326: System.out
0327: .println("Negative Testl - request for scroll insensitive updatable resultset will give a read only scroll insensitive resultset");
0328: System.out
0329: .println("This test has been removed because scrollable "
0330: + "insensitive updatable result sets have been "
0331: + "implemented.");
0332:
0333: System.out
0334: .println("Negative Test2 - request for scroll sensitive "
0335: + "updatable resultset will give an updatable "
0336: + "scroll insensitive resultset");
0337: stmt = conn.createStatement(
0338: ResultSet.TYPE_SCROLL_SENSITIVE,
0339: ResultSet.CONCUR_UPDATABLE);
0340: JDBCDisplayUtil.ShowWarnings(System.out, conn);
0341: System.out
0342: .println("requested TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE but that is not supported");
0343: System.out
0344: .println("Jira issue Derby-154 : When client connects to Network Server using JCC, it incorrectly shows support for scroll sensitive updatable resultsets");
0345: System.out
0346: .println("Make sure that we got TYPE_SCROLL_INSENSITIVE? "
0347: + (stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE));
0348: System.out
0349: .println("Make sure that we got CONCUR_UPDATABLE? "
0350: + (stmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
0351: System.out
0352: .println("Rest of the test removed because scrollable "
0353: + "insensitive updatable result sets have been "
0354: + "implemented.");
0355:
0356: System.out
0357: .println("Negative Test3 - request a read only resultset and attempt deleteRow and updateRow on it");
0358: stmt = conn.createStatement();//the default is a read only forward only resultset
0359: rs = stmt.executeQuery("select * from t1");
0360: System.out
0361: .println("Make sure that we got CONCUR_READ_ONLY? "
0362: + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
0363: rs.next();
0364: System.out
0365: .println("Now attempting to send a deleteRow on a read only resultset.");
0366: try {
0367: rs.deleteRow();
0368: System.out
0369: .println("FAIL!!! deleteRow should have failed because this is a read only resultset");
0370: } catch (SQLException e) {
0371: System.out.println("SQL State : " + e.getSQLState());
0372: System.out.println("Got expected exception "
0373: + e.getMessage());
0374: }
0375: System.out
0376: .println("Now attempting to send an updateRow on a read only resultset.");
0377: try {
0378: rs.updateRow();
0379: System.out
0380: .println("FAIL!!! updateRow should have failed because this is a read only resultset");
0381: } catch (SQLException e) {
0382: System.out.println("SQL State : " + e.getSQLState());
0383: System.out.println("Got expected exception "
0384: + e.getMessage());
0385: }
0386: //have to close the resultset because by default, resultsets are held open over commit
0387: rs.close();
0388:
0389: System.out
0390: .println("Negative Test4 - request a read only resultset and send a sql with FOR UPDATE clause and attempt deleteRow/updateRow on it");
0391: stmt = conn.createStatement();//the default is a read only forward only resultset
0392: rs = stmt.executeQuery("select * from t1 FOR UPDATE");
0393: System.out
0394: .println("Make sure that we got CONCUR_READ_ONLY? "
0395: + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
0396: rs.next();
0397: System.out
0398: .println("Now attempting to send a deleteRow on a read only resultset with FOR UPDATE clause in the SELECT sql.");
0399: try {
0400: rs.deleteRow();
0401: System.out
0402: .println("FAIL!!! deleteRow should have failed because this is a read only resultset");
0403: } catch (SQLException e) {
0404: System.out.println("SQL State : " + e.getSQLState());
0405: System.out.println("Got expected exception "
0406: + e.getMessage());
0407: }
0408: System.out
0409: .println("Now attempting to send a updateRow on a read only resultset with FOR UPDATE clause in the SELECT sql.");
0410: try {
0411: rs.updateRow();
0412: System.out
0413: .println("FAIL!!! updateRow should have failed because this is a read only resultset");
0414: } catch (SQLException e) {
0415: System.out.println("SQL State : " + e.getSQLState());
0416: System.out.println("Got expected exception "
0417: + e.getMessage());
0418: }
0419: //have to close the resultset because by default, resultsets are held open over commit
0420: rs.close();
0421:
0422: System.out
0423: .println("Negative Test5 - request resultset with no FOR UPDATE clause and CONCUR_READ_ONLY");
0424: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0425: ResultSet.CONCUR_READ_ONLY);
0426: rs = stmt.executeQuery("select * from t1");//notice that we forgot to give mandatory FOR UPDATE clause for updatable resultset
0427: System.out
0428: .println("Make sure that we got CONCUR_READ_ONLY? "
0429: + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
0430:
0431: System.out
0432: .println("Now attempting to send a delete on a sql with no FOR UPDATE clause and CONCUR_READ_ONLY.");
0433: try {
0434: rs.deleteRow();
0435: System.out
0436: .println("FAIL!!! deleteRow should have failed on sql with no FOR UPDATE clause and CONCUR_READ_ONLY.");
0437: } catch (SQLException e) {
0438: System.out.println("SQL State : " + e.getSQLState());
0439: System.out.println("Got expected exception "
0440: + e.getMessage());
0441: }
0442: System.out
0443: .println("Now attempting to send a updateRow on a sql with no FOR UPDATE clause and CONCUR_READ_ONLY.");
0444: try {
0445: rs.updateRow();
0446: System.out
0447: .println("FAIL!!! updateRow should have failed on sql with no FOR UPDATE clause and CONCUR_READ_ONLY.");
0448: } catch (SQLException e) {
0449: System.out.println("SQL State : " + e.getSQLState());
0450: System.out.println("Got expected exception "
0451: + e.getMessage());
0452: }
0453: //have to close the resultset because by default, resultsets are held open over commit
0454: rs.close();
0455:
0456: System.out
0457: .println("Negative Test6 - request updatable resultset for sql with FOR READ ONLY clause");
0458: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0459: ResultSet.CONCUR_UPDATABLE);
0460: rs = stmt.executeQuery("select * from t1 FOR READ ONLY");
0461: System.out
0462: .println("Make sure that we got CONCUR_READ_ONLY? "
0463: + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
0464: System.out
0465: .println("Jira issue Derby-159 : Warnings raised by Derby are not getting passed to the Client in Network Server Mode");
0466: JDBCDisplayUtil.ShowWarnings(System.out, rs);
0467: rs.next();
0468: System.out
0469: .println("Now attempting to send a delete on a sql with FOR READ ONLY clause.");
0470: try {
0471: rs.deleteRow();
0472: System.out
0473: .println("FAIL!!! deleteRow should have failed on sql with FOR READ ONLY clause");
0474: } catch (SQLException e) {
0475: System.out.println("SQL State : " + e.getSQLState());
0476: System.out.println("Got expected exception "
0477: + e.getMessage());
0478: }
0479: System.out
0480: .println("Now attempting to send a updateRow on a sql with FOR READ ONLY clause.");
0481: try {
0482: rs.updateRow();
0483: System.out
0484: .println("FAIL!!! updateRow should have failed on sql with FOR READ ONLY clause");
0485: } catch (SQLException e) {
0486: System.out.println("SQL State : " + e.getSQLState());
0487: System.out.println("Got expected exception "
0488: + e.getMessage());
0489: }
0490: //have to close the resultset because by default, resultsets are held open over commit
0491: rs.close();
0492:
0493: System.out
0494: .println("Negative Test7 - attempt to deleteRow & updateRow on updatable resultset when the resultset is not positioned on a row");
0495: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0496: ResultSet.CONCUR_UPDATABLE);
0497: rs = stmt.executeQuery("SELECT * FROM t1");
0498: System.out
0499: .println("Make sure that we got CONCUR_UPDATABLE? "
0500: + (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE));
0501: System.out
0502: .println("Now attempt a deleteRow without first doing next on the resultset.");
0503: try {
0504: rs.deleteRow();
0505: System.out
0506: .println("FAIL!!! deleteRow should have failed because resultset is not on a row");
0507: } catch (SQLException e) {
0508: System.out.println("SQL State : " + e.getSQLState());
0509: System.out.println("Got expected exception "
0510: + e.getMessage());
0511: }
0512: System.out
0513: .println("Now attempt a updateRow without first doing next on the resultset.");
0514: System.out
0515: .println("updateRow will check if it is on a row or not even "
0516: + "though no changes have been made to the row using updateXXX");
0517: try {
0518: rs.updateRow();
0519: System.out
0520: .println("FAIL!!! updateRow should have failed because "
0521: + "resultset is not on a row");
0522: } catch (SQLException e) {
0523: System.out.println("SQL State : " + e.getSQLState());
0524: System.out.println("Got expected exception "
0525: + e.getMessage());
0526: }
0527: while (rs.next())
0528: ;//read all the rows from the resultset and position after the last row
0529: System.out
0530: .println("ResultSet is positioned after the last row. attempt to deleteRow at this point should fail!");
0531: try {
0532: rs.deleteRow();
0533: System.out
0534: .println("FAIL!!! deleteRow should have failed because resultset is after the last row");
0535: } catch (SQLException e) {
0536: System.out.println("SQL State : " + e.getSQLState());
0537: System.out.println("Got expected exception "
0538: + e.getMessage());
0539: }
0540: System.out
0541: .println("ResultSet is positioned after the last row. attempt to updateRow at this point should fail!");
0542: try {
0543: rs.updateRow();
0544: System.out
0545: .println("FAIL!!! updateRow should have failed because resultset is after the last row");
0546: } catch (SQLException e) {
0547: System.out.println("SQL State : " + e.getSQLState());
0548: System.out.println("Got expected exception "
0549: + e.getMessage());
0550: }
0551: rs.close();
0552:
0553: System.out
0554: .println("Negative Test8 - attempt deleteRow & updateRow on updatable resultset after closing the resultset");
0555: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0556: ResultSet.CONCUR_UPDATABLE);
0557: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
0558: System.out
0559: .println("Make sure that we got CONCUR_UPDATABLE? "
0560: + (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE));
0561: rs.next();
0562: rs.close();
0563: try {
0564: rs.deleteRow();
0565: System.out
0566: .println("FAIL!!! deleteRow should have failed because resultset is closed");
0567: } catch (SQLException e) {
0568: System.out.println("SQL State : " + e.getSQLState());
0569: System.out.println("Got expected exception "
0570: + e.getMessage());
0571: }
0572: try {
0573: rs.updateRow();
0574: System.out
0575: .println("FAIL!!! updateRow should have failed because resultset is closed");
0576: } catch (SQLException e) {
0577: System.out.println("SQL State : " + e.getSQLState());
0578: System.out.println("Got expected exception "
0579: + e.getMessage());
0580: }
0581:
0582: System.out
0583: .println("Negative Test9 - try updatable resultset on system table");
0584: try {
0585: rs = stmt
0586: .executeQuery("SELECT * FROM sys.systables FOR UPDATE");
0587: System.out
0588: .println("FAIL!!! trying to open an updatable resultset on a system table should have failed because system tables can't be updated by a user");
0589: } catch (SQLException e) {
0590: System.out.println("SQL State : " + e.getSQLState());
0591: System.out.println("Got expected exception "
0592: + e.getMessage());
0593: }
0594:
0595: System.out
0596: .println("Negative Test10 - try updatable resultset on a view");
0597: try {
0598: rs = stmt.executeQuery("SELECT * FROM v1 FOR UPDATE");
0599: System.out
0600: .println("FAIL!!! trying to open an updatable resultset on a view should have failed because Derby doesnot support updates to views yet");
0601: } catch (SQLException e) {
0602: System.out.println("SQL State : " + e.getSQLState());
0603: System.out.println("Got expected exception "
0604: + e.getMessage());
0605: }
0606: stmt.executeUpdate("drop view v1");
0607:
0608: System.out
0609: .println("Negative Test11 - attempt to open updatable resultset when there is join in the select query should fail");
0610: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0611: ResultSet.CONCUR_UPDATABLE);
0612: try {
0613: rs = stmt
0614: .executeQuery("SELECT c1 FROM t1,t2 where t1.c1 = t2.c21 FOR UPDATE");
0615: System.out
0616: .println("FAIL!!! trying to open an updatable resultset should have failed because updatable resultset donot support join in the select query");
0617: } catch (SQLException e) {
0618: System.out.println("SQL State : " + e.getSQLState());
0619: System.out.println("Got expected exception "
0620: + e.getMessage());
0621: }
0622:
0623: System.out
0624: .println("Negative Test12 - With autocommit on, attempt to drop a table when there is an open updatable resultset on it");
0625: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0626: ResultSet.CONCUR_UPDATABLE);
0627: rs = stmt.executeQuery("SELECT c1 FROM t1 FOR UPDATE");
0628: rs.next();
0629: rs.updateInt(1, 123);
0630: System.out
0631: .println("Opened an updatable resultset. Now trying to drop that table through another Statement");
0632: stmt1 = conn.createStatement();
0633: try {
0634: stmt1.executeUpdate("drop table t1");
0635: System.out
0636: .println("FAIL!!! drop table should have failed because the updatable resultset is still open");
0637: } catch (SQLException e) {
0638: System.out.println("SQL State : " + e.getSQLState());
0639: System.out.println("Got expected exception "
0640: + e.getMessage());
0641: }
0642: System.out
0643: .println("Since autocommit is on, the drop table exception resulted in a runtime rollback causing updatable resultset object to close");
0644: try {
0645: rs.updateRow();
0646: System.out
0647: .println("FAIL!!! resultset should have been closed at this point and updateRow should have failed");
0648: } catch (SQLException e) {
0649: System.out.println("SQL State : " + e.getSQLState());
0650: System.out.println("Got expected exception "
0651: + e.getMessage());
0652: }
0653: try {
0654: rs.deleteRow();
0655: System.out
0656: .println("FAIL!!! resultset should have been closed at this point and deleteRow should have failed");
0657: } catch (SQLException e) {
0658: System.out.println("SQL State : " + e.getSQLState());
0659: System.out.println("Got expected exception "
0660: + e.getMessage());
0661: }
0662:
0663: System.out
0664: .println("Negative Test13 - foreign key constraint failure will cause deleteRow to fail");
0665: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0666: ResultSet.CONCUR_UPDATABLE);
0667: rs = stmt
0668: .executeQuery("SELECT * FROM tableWithPrimaryKey FOR UPDATE");
0669: rs.next();
0670: try {
0671: rs.deleteRow();
0672: System.out
0673: .println("FAIL!!! deleteRow should have failed because it will cause foreign key constraint failure");
0674: } catch (SQLException e) {
0675: System.out.println("SQL State : " + e.getSQLState());
0676: System.out.println("Got expected exception "
0677: + e.getMessage());
0678: }
0679: System.out
0680: .println("Since autocommit is on, the constraint exception resulted in a runtime rollback causing updatable resultset object to close");
0681: try {
0682: rs.next();
0683: if (TestUtil.isNetFramework())
0684: System.out
0685: .println("Jira entry Derby-160 : for Network Server because next should have failed");
0686: System.out
0687: .println("FAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollback");
0688: } catch (SQLException e) {
0689: System.out.println("SQL State : " + e.getSQLState());
0690: System.out.println("Got expected exception "
0691: + e.getMessage());
0692: }
0693:
0694: System.out
0695: .println("Negative Test14 - foreign key constraint failure will cause updateRow to fail");
0696: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0697: ResultSet.CONCUR_UPDATABLE);
0698: rs = stmt
0699: .executeQuery("SELECT c1, c2 FROM tableWithPrimaryKey FOR UPDATE");
0700: rs.next();
0701: rs.updateInt(1, 11);
0702: rs.updateInt(2, 22);
0703: try {
0704: rs.updateRow();
0705: System.out
0706: .println("FAIL!!! updateRow should have failed because it will cause foreign key constraint failure");
0707: } catch (SQLException e) {
0708: System.out.println("SQL State : " + e.getSQLState());
0709: System.out.println("Got expected exception "
0710: + e.getMessage());
0711: }
0712: System.out
0713: .println("Since autocommit is on, the constraint exception resulted in a runtime rollback causing updatable resultset object to close");
0714: try {
0715: rs.next();
0716: if (TestUtil.isNetFramework())
0717: System.out
0718: .println("Jira entry Derby-160 : for Network Server because next should have failed");
0719: System.out
0720: .println("FAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollback");
0721: } catch (SQLException e) {
0722: System.out.println("SQL State : " + e.getSQLState());
0723: System.out.println("Got expected exception "
0724: + e.getMessage());
0725: }
0726:
0727: System.out
0728: .println("Negative Test15 - Can't call updateXXX methods on columns that do not correspond to a column in the table");
0729: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0730: ResultSet.CONCUR_UPDATABLE);
0731: rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
0732: rs.next();
0733: try {
0734: rs.updateInt(1, 22);
0735: System.out
0736: .println("FAIL!!! updateInt should have failed because it is trying to update a column that does not correspond to column in base table");
0737: } catch (SQLException e) {
0738: System.out.println("SQL State : " + e.getSQLState());
0739: System.out.println("Got expected exception "
0740: + e.getMessage());
0741: }
0742:
0743: System.out
0744: .println("Negative Test16 - Call updateXXX method on out of the range column");
0745: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0746: ResultSet.CONCUR_UPDATABLE);
0747: rs = stmt.executeQuery("SELECT c1, c2 FROM t1 FOR UPDATE");
0748: rs.next();
0749: System.out
0750: .println("There are only 2 columns in the select list and we are trying to send updateXXX on column position 3");
0751: try {
0752: rs.updateInt(3, 22);
0753: System.out
0754: .println("FAIL!!! updateInt should have failed because there are only 2 columns in the select list");
0755: } catch (SQLException e) {
0756: System.out.println("SQL State : " + e.getSQLState());
0757: System.out.println("Got expected exception "
0758: + e.getMessage());
0759: }
0760:
0761: reloadData();
0762:
0763: System.out
0764: .println("Positive Test1a - request updatable resultset for forward only type resultset");
0765: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0766: ResultSet.CONCUR_UPDATABLE);
0767: JDBCDisplayUtil.ShowWarnings(System.out, conn);
0768: System.out
0769: .println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
0770: System.out
0771: .println("got TYPE_FORWARD_ONLY? "
0772: + (stmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
0773: System.out
0774: .println("got CONCUR_UPDATABLE? "
0775: + (stmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
0776: rs = stmt.executeQuery("SELECT * FROM t1");
0777: System.out
0778: .println("JDBC 2.0 updatable resultset apis on this ResultSet object will pass because this is an updatable resultset");
0779: rs.next();
0780: System.out
0781: .println("column 1 on this row before deleteRow is "
0782: + rs.getInt(1));
0783: System.out
0784: .println("column 2 on this row before deleteRow is "
0785: + rs.getString(2));
0786: rs.deleteRow();
0787: System.out
0788: .println("Since after deleteRow(), in embedded mode and Network "
0789: + "Server mode using Derby Net Client, ResultSet is positioned before "
0790: + "the next row, getXXX will fail");
0791: try {
0792: System.out.println("column 1 on this deleted row is "
0793: + rs.getInt(1));
0794: } catch (SQLException e) {
0795: System.out.println("SQL State : " + e.getSQLState());
0796: System.out.println("Got expected exception "
0797: + e.getMessage());
0798: }
0799: System.out
0800: .println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
0801: try {
0802: rs.deleteRow();
0803: System.out
0804: .println("FAIL!!! deleteRow should have failed because ResultSet is not positioned on a row");
0805: } catch (SQLException e) {
0806: System.out.println("SQL State : " + e.getSQLState());
0807: System.out.println("Got expected exception "
0808: + e.getMessage());
0809: }
0810: System.out.println("Position the ResultSet with next()");
0811: rs.next();
0812: System.out
0813: .println("Should be able to deletRow() on the current row now");
0814: rs.deleteRow();
0815: //have to close the resultset because by default, resultsets are held open over commit
0816: rs.close();
0817:
0818: System.out
0819: .println("Positive Test1b - request updatable resultset for forward only type resultset");
0820: reloadData();
0821: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0822: ResultSet.CONCUR_UPDATABLE);
0823: JDBCDisplayUtil.ShowWarnings(System.out, conn);
0824: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
0825: rs.next();
0826: System.out
0827: .println("column 1 on this row before updateInt is "
0828: + rs.getInt(1));
0829: rs.updateInt(1, 234);
0830: System.out
0831: .println("column 1 on this row after updateInt is "
0832: + rs.getInt(1));
0833: System.out
0834: .println("column 2 on this row before updateString is "
0835: + rs.getString(2));
0836: System.out.println("now updateRow on the row");
0837: rs.updateRow();
0838: System.out
0839: .println("Since after updateRow(), in embedded mode and Network "
0840: + "Server mode using Derby Net Client, ResultSet is positioned before "
0841: + "the next row, getXXX will fail");
0842: try {
0843: System.out.println("column 1 on this updateRow row is "
0844: + rs.getInt(1));
0845: } catch (SQLException e) {
0846: System.out.println("SQL State : " + e.getSQLState());
0847: System.out.println("Got expected exception "
0848: + e.getMessage());
0849: }
0850: System.out
0851: .println("calling updateRow again w/o first positioning the ResultSet on the next row will fail");
0852: try {
0853: rs.updateRow();
0854: System.out
0855: .println("FAIL!!! updateRow should have failed because ResultSet is not positioned on a row");
0856: } catch (SQLException e) {
0857: System.out.println("SQL State : " + e.getSQLState());
0858: System.out.println("Got expected exception "
0859: + e.getMessage());
0860: }
0861: System.out.println("Position the ResultSet with next()");
0862: rs.next();
0863: System.out
0864: .println("Should be able to updateRow() on the current row now");
0865: rs.updateString(2, "234");
0866: rs.updateRow();
0867: //have to close the resultset because by default, resultsets are held open over commit
0868: rs.close();
0869:
0870: System.out
0871: .println("Positive Test1c - use updatable resultset to do postitioned delete");
0872: conn.setAutoCommit(false);
0873: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0874: ResultSet.CONCUR_UPDATABLE);
0875: JDBCDisplayUtil.ShowWarnings(System.out, conn);
0876:
0877: System.out
0878: .println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
0879: System.out
0880: .println("got TYPE_FORWARD_ONLY? "
0881: + (stmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
0882: System.out
0883: .println("got CONCUR_UPDATABLE? "
0884: + (stmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
0885:
0886: rs = stmt.executeQuery("SELECT * FROM t1");
0887: rs.next();
0888:
0889: System.out
0890: .println("column 1 on this row before positioned delete "
0891: + rs.getInt(1));
0892: System.out
0893: .println("column 2 on this row before positioned delete "
0894: + rs.getString(2));
0895:
0896: pStmt = conn
0897: .prepareStatement("DELETE FROM T1 WHERE CURRENT OF "
0898: + rs.getCursorName());
0899: pStmt.executeUpdate();
0900: try {
0901: System.out.println("column 1 on this deleted row is "
0902: + rs.getInt(1));
0903: System.out.println("column 2 on this deleted row is "
0904: + rs.getString(2));
0905: } catch (SQLException e) {
0906: System.out.println("SQL State : " + e.getSQLState());
0907: System.out.println("Got expected exception "
0908: + e.getMessage());
0909: }
0910:
0911: System.out
0912: .println("doing positioned delete again w/o first positioning the ResultSet on the next row will fail");
0913:
0914: try {
0915: pStmt.executeUpdate();
0916: System.out
0917: .println("FAIL!!! positioned delete should have failed because ResultSet is not positioned on a row");
0918: } catch (SQLException e) {
0919: System.out.println("SQL State : " + e.getSQLState());
0920: System.out.println("Got expected exception "
0921: + e.getMessage());
0922: }
0923:
0924: System.out.println("Position the ResultSet with next()");
0925:
0926: rs.next();
0927:
0928: System.out
0929: .println("Should be able to do positioned delete on the current row now");
0930:
0931: pStmt.executeUpdate();
0932: //have to close the resultset because by default, resultsets are held open over commit
0933: rs.close();
0934: conn.rollback();
0935:
0936: System.out
0937: .println("Positive Test1d - updatable resultset to do positioned update");
0938: reloadData();
0939:
0940: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0941: ResultSet.CONCUR_UPDATABLE);
0942: JDBCDisplayUtil.ShowWarnings(System.out, conn);
0943:
0944: rs = stmt.executeQuery("SELECT * FROM t1");
0945: rs.next();
0946:
0947: System.out
0948: .println("column 1 on this row before positioned update is "
0949: + rs.getInt(1));
0950:
0951: pStmt = conn
0952: .prepareStatement("UPDATE T1 SET C1=?,C2=? WHERE CURRENT OF "
0953: + rs.getCursorName());
0954: final int c1 = 2345;
0955: final String c2 = "UUU";
0956:
0957: pStmt.setInt(1, c1);
0958: pStmt.setString(2, c2); // current value
0959: System.out.println("now dow positioned update on the row");
0960:
0961: pStmt.executeUpdate();
0962:
0963: System.out.println("column 1 on the updated row is "
0964: + rs.getInt(1));
0965: System.out.println("column 1 on the updated row is "
0966: + rs.getString(2));
0967:
0968: try {
0969: System.out.println("Refreshing the row");
0970: rs.refreshRow(); // MAY FAIL HERE
0971:
0972: // If not, verify that it worked..
0973: if (c1 != rs.getInt(1)) {
0974: System.out
0975: .println("FAIL!!! Expected column 1 to be update to "
0976: + c1);
0977: }
0978: if (!c2.equals(rs.getString(2))) {
0979: System.out
0980: .println("FAIL!!! Expected column 1 to be update to "
0981: + c2);
0982: }
0983: } catch (SQLException e) {
0984: System.out.println("SQL State : " + e.getSQLState());
0985: System.out.println("Got expected exception "
0986: + e.getMessage());
0987: }
0988:
0989: System.out
0990: .println("doing positioned update again w/o positioning the RS will succeed");
0991: System.out
0992: .println("because the cursor is still positioned");
0993:
0994: pStmt.setInt(1, c1);
0995: pStmt.setString(2, c2); // current value
0996: pStmt.executeUpdate();
0997:
0998: System.out.println("Position the ResultSet with next()");
0999:
1000: rs.next();
1001:
1002: System.out
1003: .println("Should still be able to do positioned update");
1004:
1005: pStmt.setInt(1, rs.getInt(1)); // current value
1006: pStmt.setString(2, "abc");
1007: pStmt.executeUpdate();
1008:
1009: //have to close the resultset because by default, resultsets are held open over commit
1010: rs.close();
1011: conn.rollback();
1012:
1013: conn.setAutoCommit(true);
1014: System.out
1015: .println("Positive Test2 - even if no columns from table "
1016: + "specified in the column list, we should be able to get updatable "
1017: + "resultset");
1018: reloadData();
1019: System.out
1020: .println("Will work in embedded mode because target table is "
1021: + "not derived from the columns in the select list");
1022: System.out
1023: .println("Will not work in network server mode because it "
1024: + "derives the target table from the columns in the select list");
1025: System.out.println("total number of rows in T1 ");
1026: dumpRS(stmt.executeQuery("select count(*) from t1"));
1027: rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
1028: rs.next();
1029: System.out.println("column 1 on this row is "
1030: + rs.getInt(1));
1031: try {
1032: rs.deleteRow();
1033: if (TestUtil.isNetFramework())
1034: System.out
1035: .println("FAIL!!! should have failed in network server");
1036: else
1037: System.out
1038: .println("PASS!!! passed in embedded mode");
1039: } catch (SQLException e) {
1040: if (TestUtil.isNetFramework()) {
1041: System.out
1042: .println("SQL State : " + e.getSQLState());
1043: System.out.println("Got expected exception "
1044: + e.getMessage());
1045: } else
1046: System.out.println("Got unexpected exception "
1047: + e.getMessage());
1048: }
1049: //have to close the resultset because by default, resultsets are held open over commit
1050: rs.close();
1051: System.out
1052: .println("total number of rows in T1 after one deleteRow is ");
1053: dumpRS(stmt.executeQuery("select count(*) from t1"));
1054:
1055: System.out
1056: .println("Positive Test3a - use prepared statement with concur updatable status to test deleteRow");
1057: reloadData();
1058: pStmt = conn.prepareStatement(
1059: "select * from t1 where c1>?",
1060: ResultSet.TYPE_FORWARD_ONLY,
1061: ResultSet.CONCUR_UPDATABLE);
1062: System.out
1063: .println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
1064: System.out
1065: .println("got TYPE_FORWARD_ONLY? "
1066: + (pStmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
1067: System.out
1068: .println("got CONCUR_UPDATABLE? "
1069: + (pStmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
1070: pStmt.setInt(1, 0);
1071: rs = pStmt.executeQuery();
1072: rs.next();
1073: System.out.println("column 1 on this row is "
1074: + rs.getInt(1));
1075: rs.deleteRow();
1076: System.out
1077: .println("Since after deleteRow(), ResultSet is positioned before the next row, getXXX will fail");
1078: try {
1079: System.out.println("column 1 on this deleted row is "
1080: + rs.getInt(1));
1081: } catch (SQLException e) {
1082: System.out.println("SQL State : " + e.getSQLState());
1083: System.out.println("Got expected exception "
1084: + e.getMessage());
1085: }
1086: System.out
1087: .println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
1088: try {
1089: rs.deleteRow();
1090: System.out
1091: .println("FAIL!!! deleteRow should have failed because it can't be called more than once on the same row");
1092: } catch (SQLException e) {
1093: System.out.println("SQL State : " + e.getSQLState());
1094: System.out.println("Got expected exception "
1095: + e.getMessage());
1096: }
1097: System.out.println("Position the ResultSet with next()");
1098: rs.next();
1099: //Derby-718 check that column values are not null after next()
1100: if (rs.getInt(1) == 0) {
1101: System.out.println("First column should not be 0");
1102: }
1103: // Derby-718
1104: System.out
1105: .println("Should be able to deletRow() on the current row now");
1106: rs.deleteRow();
1107: //have to close the resultset because by default, resultsets are held open over commit
1108: rs.close();
1109:
1110: System.out
1111: .println("Positive Test3b - use prepared statement with concur updatable status to test updateXXX");
1112: reloadData();
1113: pStmt = conn.prepareStatement(
1114: "select * from t1 where c1>? for update",
1115: ResultSet.TYPE_FORWARD_ONLY,
1116: ResultSet.CONCUR_UPDATABLE);
1117: System.out
1118: .println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
1119: System.out
1120: .println("got TYPE_FORWARD_ONLY? "
1121: + (pStmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
1122: System.out
1123: .println("got CONCUR_UPDATABLE? "
1124: + (pStmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
1125: pStmt.setInt(1, 0);
1126: rs = pStmt.executeQuery();
1127: rs.next();
1128: System.out.println("column 1 on this row is "
1129: + rs.getInt(1));
1130: rs.updateInt(1, 5);
1131: System.out
1132: .println("column 1 on this row after updateInt is "
1133: + rs.getInt(1));
1134: rs.updateRow();
1135: System.out
1136: .println("Since after updateRow(), ResultSet is positioned before the next row, getXXX will fail");
1137: try {
1138: System.out.println("column 1 on this updated row is "
1139: + rs.getInt(1));
1140: } catch (SQLException e) {
1141: System.out.println("SQL State : " + e.getSQLState());
1142: System.out.println("Got expected exception "
1143: + e.getMessage());
1144: }
1145: System.out
1146: .println("calling updateRow/updateXXX again w/o first positioning the ResultSet on the next row will fail");
1147: try {
1148: rs.updateInt(1, 0);
1149: System.out
1150: .println("FAIL!!! updateXXX should have failed because resultset is not positioned on a row");
1151: } catch (SQLException e) {
1152: System.out.println("SQL State : " + e.getSQLState());
1153: System.out.println("Got expected exception "
1154: + e.getMessage());
1155: }
1156: try {
1157: rs.updateRow();
1158: System.out
1159: .println("FAIL!!! updateRow should have failed because resultset is not positioned on a row");
1160: } catch (SQLException e) {
1161: System.out.println("SQL State : " + e.getSQLState());
1162: System.out.println("Got expected exception "
1163: + e.getMessage());
1164: }
1165: try {
1166: rs.cancelRowUpdates();
1167: System.out
1168: .println("FAIL!!! cancelRowUpdates should have failed because resultset is not positioned on a row");
1169: } catch (SQLException e) {
1170: System.out.println("SQL State : " + e.getSQLState());
1171: System.out.println("Got expected exception "
1172: + e.getMessage());
1173: }
1174: System.out.println("Position the ResultSet with next()");
1175: rs.next();
1176: System.out
1177: .println("Should be able to cancelRowUpdates() on the current row now");
1178: rs.cancelRowUpdates();
1179: //have to close the resultset because by default, resultsets are held open over commit
1180: rs.close();
1181:
1182: System.out
1183: .println("Positive Test4 - use callable statement with concur updatable status");
1184: reloadData();
1185: callStmt = conn.prepareCall("select * from t1",
1186: ResultSet.TYPE_FORWARD_ONLY,
1187: ResultSet.CONCUR_UPDATABLE);
1188: rs = callStmt.executeQuery();
1189: System.out
1190: .println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
1191: System.out
1192: .println("got TYPE_FORWARD_ONLY? "
1193: + (callStmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
1194: System.out
1195: .println("got CONCUR_UPDATABLE? "
1196: + (callStmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
1197: rs.next();
1198: System.out.println("column 1 on this row is "
1199: + rs.getInt(1));
1200: rs.deleteRow();
1201: System.out
1202: .println("Since after deleteRow(), ResultSet is positioned before the next row, getXXX will fail");
1203: try {
1204: System.out.println("column 1 on this deleted row is "
1205: + rs.getInt(1));
1206: } catch (SQLException e) {
1207: System.out.println("SQL State : " + e.getSQLState());
1208: System.out.println("Got expected exception "
1209: + e.getMessage());
1210: }
1211: System.out
1212: .println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
1213: try {
1214: rs.deleteRow();
1215: System.out
1216: .println("FAIL!!! deleteRow should have failed because it can't be called more than once on the same row");
1217: } catch (SQLException e) {
1218: System.out.println("SQL State : " + e.getSQLState());
1219: System.out.println("Got expected exception "
1220: + e.getMessage());
1221: }
1222: System.out.println("Position the ResultSet with next()");
1223: rs.next();
1224: System.out
1225: .println("Should be able to deletRow() on the current row now");
1226: rs.deleteRow();
1227: //have to close the resultset because by default, resultsets are held open over commit
1228: rs.close();
1229:
1230: System.out
1231: .println("Positive Test5 - donot have to select primary key to get an updatable resultset");
1232: reloadData();
1233: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1234: ResultSet.CONCUR_UPDATABLE);
1235: rs = stmt.executeQuery("SELECT c32 FROM t3");
1236: rs.next();
1237: System.out.println("column 1 on this row is "
1238: + rs.getInt(1));
1239: System.out
1240: .println("now try to delete row when primary key is not selected for that row");
1241: rs.deleteRow();
1242: rs.next();
1243: rs.updateLong(1, 123);
1244: rs.updateRow();
1245: //have to close the resultset because by default, resultsets are held open over commit
1246: rs.close();
1247:
1248: System.out
1249: .println("Positive Test6a - For Forward Only resultsets, DatabaseMetaData will return false for ownDeletesAreVisible and deletesAreDetected");
1250: System.out
1251: .println("This is because, after deleteRow, we position the ResultSet before the next row. We don't make a hole for the deleted row and then stay on that deleted hole");
1252: dbmt = conn.getMetaData();
1253: System.out
1254: .println("ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? "
1255: + dbmt
1256: .ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
1257: System.out
1258: .println("othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? "
1259: + dbmt
1260: .othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
1261: System.out
1262: .println("deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? "
1263: + dbmt
1264: .deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY));
1265: reloadData();
1266: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1267: ResultSet.CONCUR_UPDATABLE);
1268: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
1269: rs.next();
1270: System.out
1271: .println("The JDBC program should look at rowDeleted only if deletesAreDetected returns true");
1272: System.out
1273: .println("Since Derby returns false for detlesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowDeleted() for FORWARD_ONLY updatable resultsets");
1274: System.out
1275: .println("Have this call to rs.rowDeleted() just to make sure the method does always return false? "
1276: + rs.rowDeleted());
1277: rs.deleteRow();
1278: rs.close();
1279:
1280: System.out
1281: .println("Positive Test6b - For Forward Only resultsets, DatabaseMetaData will return false for ownUpdatesAreVisible and updatesAreDetected");
1282: System.out
1283: .println("This is because, after updateRow, we position the ResultSet before the next row");
1284: dbmt = conn.getMetaData();
1285: System.out
1286: .println("ownUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? "
1287: + dbmt
1288: .ownUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
1289: System.out
1290: .println("othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? "
1291: + dbmt
1292: .othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
1293: System.out
1294: .println("updatesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? "
1295: + dbmt
1296: .updatesAreDetected(ResultSet.TYPE_FORWARD_ONLY));
1297: reloadData();
1298: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1299: ResultSet.CONCUR_UPDATABLE);
1300: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
1301: rs.next();
1302: System.out
1303: .println("The JDBC program should look at rowUpdated only if updatesAreDetected returns true");
1304: System.out
1305: .println("Since Derby returns false for updatesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowUpdated() for FORWARD_ONLY updatable resultsets");
1306: System.out
1307: .println("Have this call to rs.rowUpdated() just to make sure the method does always return false? "
1308: + rs.rowUpdated());
1309: rs.updateLong(1, 123);
1310: rs.updateRow();
1311: rs.close();
1312:
1313: System.out
1314: .println("Positive Test7a - delete using updatable resultset api from a temporary table");
1315: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1316: ResultSet.CONCUR_UPDATABLE);
1317: stmt
1318: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
1319: stmt.executeUpdate("insert into SESSION.t2 values(21, 1)");
1320: stmt.executeUpdate("insert into SESSION.t2 values(22, 1)");
1321: System.out
1322: .println("following rows in temp table before deleteRow");
1323: dumpRS(stmt.executeQuery("select * from SESSION.t2"));
1324: rs = stmt
1325: .executeQuery("select c21 from session.t2 for update");
1326: rs.next();
1327: rs.deleteRow();
1328: rs.next();
1329: //Derby-718 check that column values are not null after next()
1330: if (rs.getInt(1) == 0) {
1331: System.out.println("Column c21 should not be 0");
1332: }
1333: // Derby-718
1334: rs.deleteRow();
1335: System.out
1336: .println("As expected, no rows in temp table after deleteRow");
1337: dumpRS(stmt.executeQuery("select * from SESSION.t2"));
1338: rs.close();
1339: stmt.executeUpdate("DROP TABLE SESSION.t2");
1340:
1341: System.out
1342: .println("Positive Test7b - update using updatable resultset api from a temporary table");
1343: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1344: ResultSet.CONCUR_UPDATABLE);
1345: stmt
1346: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged");
1347: stmt.executeUpdate("insert into SESSION.t3 values(21, 1)");
1348: stmt.executeUpdate("insert into SESSION.t3 values(22, 1)");
1349: System.out
1350: .println("following rows in temp table before deleteRow");
1351: dumpRS(stmt.executeQuery("select * from SESSION.t3"));
1352: rs = stmt.executeQuery("select c31 from session.t3");
1353: rs.next();
1354: rs.updateLong(1, 123);
1355: rs.updateRow();
1356: rs.next();
1357: rs.updateLong(1, 123);
1358: rs.updateRow();
1359: System.out
1360: .println("As expected, updated rows in temp table after updateRow");
1361: dumpRS(stmt.executeQuery("select * from SESSION.t3"));
1362: rs.close();
1363: stmt.executeUpdate("DROP TABLE SESSION.t3");
1364:
1365: System.out
1366: .println("Positive Test8a - change the name of the statement "
1367: + "when the resultset is open and see if deleteRow still works");
1368: System.out
1369: .println("This test works in embedded mode since Derby can "
1370: + "handle the change in the name of the statement with an open resultset");
1371: System.out
1372: .println("But it fails under Network Server mode because JCC "
1373: + "and Derby Net Client do not allow statement name change when there "
1374: + "an open resultset against it");
1375: reloadData();
1376: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1377: ResultSet.CONCUR_UPDATABLE);
1378: System.out
1379: .println("change the cursor name(case sensitive name) with setCursorName and then try to deleteRow");
1380: stmt.setCursorName("CURSORNOUPDATe");//notice this name is case sensitive
1381: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
1382: rs.next();
1383: rs.deleteRow();
1384: System.out
1385: .println("change the cursor name one more time with setCursorName and then try to deleteRow");
1386: try {
1387: stmt.setCursorName("CURSORNOUPDATE1");
1388: rs.next();
1389: rs.deleteRow();
1390: if (TestUtil.isNetFramework())
1391: System.out
1392: .println("FAIL!!! should have failed in network server");
1393: else
1394: System.out
1395: .println("PASS!!! passed in embedded mode");
1396: } catch (SQLException e) {
1397: if (TestUtil.isNetFramework()) {
1398: System.out
1399: .println("SQL State : " + e.getSQLState());
1400: System.out.println("Got expected exception "
1401: + e.getMessage());
1402: } else
1403: System.out.println("Got unexpected exception "
1404: + e.getMessage());
1405: }
1406: rs.close();
1407:
1408: System.out
1409: .println("Positive Test8b - change the name of the statement "
1410: + "when the resultset is open and see if updateRow still works");
1411: System.out
1412: .println("This test works in embedded mode since Derby can "
1413: + "handle the change in the name of the statement with an open resultset");
1414: System.out
1415: .println("But it fails under Network Server mode because JCC "
1416: + "and Derby Net Client do not allow statement name change when there "
1417: + "an open resultset against it");
1418: reloadData();
1419: System.out
1420: .println("change the cursor name one more time with setCursorName and then try to updateRow");
1421: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1422: ResultSet.CONCUR_UPDATABLE);
1423: System.out
1424: .println("change the cursor name(case sensitive name) with setCursorName and then try to updateRow");
1425: stmt.setCursorName("CURSORNOUPDATe");//notice this name is case sensitive
1426: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
1427: rs.next();
1428: rs.updateLong(1, 123);
1429: try {
1430: stmt.setCursorName("CURSORNOUPDATE1");
1431: rs.updateRow();
1432: if (TestUtil.isNetFramework())
1433: System.out
1434: .println("FAIL!!! should have failed in network server");
1435: else
1436: System.out
1437: .println("PASS!!! passed in embedded mode");
1438: } catch (SQLException e) {
1439: if (TestUtil.isNetFramework()) {
1440: System.out
1441: .println("SQL State : " + e.getSQLState());
1442: System.out.println("Got expected exception "
1443: + e.getMessage());
1444: } else
1445: System.out.println("Got unexpected exception "
1446: + e.getMessage());
1447: }
1448: rs.close();
1449:
1450: System.out
1451: .println("Positive Test9a - using correlation name for the "
1452: + "table in the select sql works in embedded mode and Network Server "
1453: + "using Derby Net Client driver");
1454: System.out
1455: .println("Correlation name for table does not work in Network "
1456: + "Server mode (using JCC) because the drivers construct the delete sql "
1457: + "with the correlation name rather than the base table name");
1458: reloadData();
1459: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1460: ResultSet.CONCUR_UPDATABLE);
1461: rs = stmt
1462: .executeQuery("SELECT * FROM t1 abcde FOR UPDATE of c1");
1463: rs.next();
1464: System.out.println("column 1 on this row is "
1465: + rs.getInt(1));
1466: System.out.println("now try to deleteRow");
1467: try {
1468: rs.deleteRow();
1469: if (TestUtil.isJCCFramework())
1470: System.out
1471: .println("FAIL!!! should have failed in network server");
1472: else
1473: System.out
1474: .println("PASS!!! passed in embedded mode");
1475: } catch (SQLException e) {
1476: if (TestUtil.isJCCFramework()) {
1477: System.out
1478: .println("SQL State : " + e.getSQLState());
1479: System.out.println("Got expected exception "
1480: + e.getMessage());
1481: } else
1482: System.out.println("Got unexpected exception "
1483: + e.getMessage());
1484: }
1485: rs.close();
1486:
1487: System.out
1488: .println("Positive Test9b - using correlation name for "
1489: + "updatable columns is not allowed.");
1490: reloadData();
1491: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1492: ResultSet.CONCUR_UPDATABLE);
1493: System.out.println("Table t1 has following rows");
1494: dumpRS(stmt.executeQuery("select * from t1"));
1495: try {
1496: System.out
1497: .println("attempt to get an updatable resultset using correlation name for an updatable column");
1498: System.out
1499: .println("The sql is SELECT c1 as col1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
1500: rs = stmt
1501: .executeQuery("SELECT c1 as col1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
1502: System.out
1503: .println("FAIL!!! executeQuery should have failed");
1504: } catch (SQLException e) {
1505: System.out.println("SQL State : " + e.getSQLState());
1506: System.out.println("Got expected exception "
1507: + e.getMessage());
1508: }
1509: System.out
1510: .println("attempt to get an updatable resultset using correlation name for an readonly column. It should work");
1511: System.out
1512: .println("The sql is SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
1513: rs = stmt
1514: .executeQuery("SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
1515: rs.next();
1516: rs.updateInt(1, 11);
1517: rs.updateRow();
1518: rs.close();
1519: System.out
1520: .println("Table t1 after updateRow has following rows");
1521: dumpRS(stmt.executeQuery("select * from t1"));
1522:
1523: System.out
1524: .println("Positive Test9c - try to updateXXX on a readonly column. Should get error");
1525: reloadData();
1526: rs = stmt
1527: .executeQuery("SELECT c1, c2 FROM t1 abcde FOR UPDATE of c1");
1528: rs.next();
1529: try {
1530: rs.updateString(2, "bbbb");
1531: System.out
1532: .println("FAIL!!! updateString on readonly column should have failed");
1533: } catch (SQLException e) {
1534: System.out.println("SQL State : " + e.getSQLState());
1535: System.out.println("Got expected exception "
1536: + e.getMessage());
1537: }
1538: System.out
1539: .println("attempt to get an updatable resultset using correlation name for an readonly column. It should work");
1540: System.out
1541: .println("The sql is SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
1542: rs = stmt
1543: .executeQuery("SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
1544: rs.next();
1545: rs.updateInt(1, 11);
1546: rs.updateRow();
1547: rs.close();
1548: System.out
1549: .println("Table t1 after updateRow has following rows");
1550: dumpRS(stmt.executeQuery("select * from t1"));
1551:
1552: System.out
1553: .println("Positive Test9d - try to updateXXX on a readonly column with correlation name. Should get error");
1554: reloadData();
1555: rs = stmt
1556: .executeQuery("SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
1557: rs.next();
1558: try {
1559: rs.updateString(2, "bbbb");
1560: System.out
1561: .println("FAIL!!! updateString on readonly column should have failed");
1562: } catch (SQLException e) {
1563: System.out.println("SQL State : " + e.getSQLState());
1564: System.out.println("Got expected exception "
1565: + e.getMessage());
1566: }
1567: rs.close();
1568: System.out.println("Table t1 has following rows");
1569: dumpRS(stmt.executeQuery("select * from t1"));
1570:
1571: System.out
1572: .println("Positive Test10 - 2 updatable resultsets going against the same table, will they conflict?");
1573: conn.setAutoCommit(false);
1574: reloadData();
1575: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1576: ResultSet.CONCUR_UPDATABLE);
1577: stmt1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1578: ResultSet.CONCUR_UPDATABLE);
1579: rs = stmt.executeQuery("SELECT * FROM t1");
1580: rs.next();
1581: rs1 = stmt1.executeQuery("SELECT * FROM t1 FOR UPDATE");
1582: rs1.next();
1583: System.out.println("delete using first resultset");
1584: rs.deleteRow();
1585: try {
1586: System.out
1587: .println("attempt to send deleteRow on the same row through a different resultset should throw an exception");
1588: rs1.deleteRow();
1589: System.out
1590: .println("FAIL!!! delete using second resultset succedded? ");
1591: } catch (SQLException e) {
1592: System.out.println("SQL State : " + e.getSQLState());
1593: System.out.println("Got expected exception "
1594: + e.getMessage());
1595: }
1596: System.out
1597: .println("Move to next row in the 2nd resultset and then delete using the second resultset");
1598: rs1.next();
1599: rs1.deleteRow();
1600: rs.close();
1601: rs1.close();
1602: conn.setAutoCommit(true);
1603:
1604: System.out
1605: .println("Positive Test11 - setting the fetch size to > 1 will be ignored by updatable resultset. Same as updatable cursors");
1606: reloadData();
1607: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1608: ResultSet.CONCUR_UPDATABLE);
1609: stmt
1610: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
1611: stmt.setFetchSize(200);
1612: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
1613: System.out
1614: .println("Notice the Fetch Size in run time statistics output.");
1615: showScanStatistics(rs, conn);
1616: System.out.println("statement's fetch size is "
1617: + stmt.getFetchSize());
1618: rs.close();
1619: stmt
1620: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
1621:
1622: System.out
1623: .println("Positive Test12a - make sure delete trigger gets fired when deleteRow is issued");
1624: reloadData();
1625: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1626: ResultSet.CONCUR_UPDATABLE);
1627: System.out
1628: .println("Verify that before delete trigger got fired, row count is 0 in deleteTriggerInsertIntoThisTable");
1629: dumpRS(stmt
1630: .executeQuery("select count(*) from deleteTriggerInsertIntoThisTable"));
1631: rs = stmt
1632: .executeQuery("SELECT * FROM table0WithTriggers FOR UPDATE");
1633: rs.next();
1634: System.out.println("column 1 on this row is "
1635: + rs.getInt(1));
1636: System.out
1637: .println("now try to delete row and make sure that trigger got fired");
1638: rs.deleteRow();
1639: rs.close();
1640: System.out
1641: .println("Verify that delete trigger got fired by verifying the row count to be 1 in deleteTriggerInsertIntoThisTable");
1642: dumpRS(stmt
1643: .executeQuery("select count(*) from deleteTriggerInsertIntoThisTable"));
1644: //have to close the resultset because by default, resultsets are held open over commit
1645: rs.close();
1646:
1647: System.out
1648: .println("Positive Test12b - make sure update trigger gets fired when updateRow is issued");
1649: reloadData();
1650: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1651: ResultSet.CONCUR_UPDATABLE);
1652: System.out
1653: .println("Verify that before update trigger got fired, row count is 0 in updateTriggerInsertIntoThisTable");
1654: dumpRS(stmt
1655: .executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
1656: rs = stmt.executeQuery("SELECT * FROM table0WithTriggers");
1657: rs.next();
1658: System.out.println("column 1 on this row is "
1659: + rs.getInt(1));
1660: System.out
1661: .println("now try to update row and make sure that trigger got fired");
1662: rs.updateLong(1, 123);
1663: rs.updateRow();
1664: rs.close();
1665: System.out
1666: .println("Verify that update trigger got fired by verifying the row count to be 1 in updateTriggerInsertIntoThisTable");
1667: dumpRS(stmt
1668: .executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
1669: //have to close the resultset because by default, resultsets are held open over commit
1670: rs.close();
1671:
1672: System.out
1673: .println("Positive Test13a - Another test case for delete trigger");
1674: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1675: ResultSet.CONCUR_UPDATABLE);
1676: rs = stmt
1677: .executeQuery("SELECT * FROM table1WithTriggers FOR UPDATE");
1678: rs.next();
1679: System.out.println("column 1 on this row is "
1680: + rs.getInt(1));
1681: System.out
1682: .println("this delete row will fire the delete trigger which will delete all the rows from the table and from the resultset");
1683: rs.deleteRow();
1684: rs.next();
1685: try {
1686: rs.deleteRow();
1687: System.out
1688: .println("FAIL!!! there should have be no more rows in the resultset at this point because delete trigger deleted all the rows");
1689: } catch (SQLException e) {
1690: System.out.println("SQL State : " + e.getSQLState());
1691: System.out.println("Got expected exception "
1692: + e.getMessage());
1693: }
1694: rs.close();
1695: System.out
1696: .println("Verify that delete trigger got fired by verifying the row count to be 0 in table1WithTriggers");
1697: dumpRS(stmt
1698: .executeQuery("select count(*) from table1WithTriggers"));
1699: //have to close the resultset because by default, resultsets are held open over commit
1700: rs.close();
1701:
1702: System.out
1703: .println("Positive Test13b - Another test case for update trigger");
1704: System.out
1705: .println("Look at the current contents of table2WithTriggers");
1706: dumpRS(stmt
1707: .executeQuery("select * from table2WithTriggers"));
1708: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1709: ResultSet.CONCUR_UPDATABLE);
1710: rs = stmt
1711: .executeQuery("SELECT * FROM table2WithTriggers where c1>1 FOR UPDATE");
1712: rs.next();
1713: System.out.println("column 1 on this row is "
1714: + rs.getInt(1));
1715: System.out
1716: .println("this update row will fire the update trigger which will update all the rows in the table to have c1=1 and hence no more rows will qualify for the resultset");
1717: rs.updateLong(2, 2);
1718: rs.updateRow();
1719: rs.next();
1720: try {
1721: rs.updateRow();
1722: System.out
1723: .println("FAIL!!! there should have be no more rows in the resultset at this point because update trigger made all the rows not qualify for the resultset");
1724: } catch (SQLException e) {
1725: System.out.println("SQL State : " + e.getSQLState());
1726: System.out.println("Got expected exception "
1727: + e.getMessage());
1728: }
1729: rs.close();
1730: System.out
1731: .println("Verify that update trigger got fired by verifying that all column c1s have value 1 in table2WithTriggers");
1732: dumpRS(stmt
1733: .executeQuery("select * from table2WithTriggers"));
1734: //have to close the resultset because by default, resultsets are held open over commit
1735: rs.close();
1736: conn.rollback();
1737:
1738: System.out
1739: .println("Positive Test14a - make sure self referential delete cascade works when deleteRow is issued");
1740: dumpRS(stmt.executeQuery("select * from selfReferencingT1"));
1741: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1742: ResultSet.CONCUR_UPDATABLE);
1743: rs = stmt.executeQuery("SELECT * FROM selfReferencingT1");
1744: rs.next();
1745: System.out.println("column 1 on this row is "
1746: + rs.getString(1));
1747: System.out
1748: .println("this delete row will cause the delete cascade constraint to delete all the rows from the table and from the resultset");
1749: rs.deleteRow();
1750: rs.next();
1751: try {
1752: rs.deleteRow();
1753: System.out
1754: .println("FAIL!!! there should have be no more rows in the resultset at this point because delete cascade deleted all the rows");
1755: } catch (SQLException e) {
1756: System.out.println("SQL State : " + e.getSQLState());
1757: System.out.println("Got expected exception "
1758: + e.getMessage());
1759: }
1760: rs.close();
1761: System.out
1762: .println("Verify that delete trigger got fired by verifying the row count to be 0 in selfReferencingT1");
1763: dumpRS(stmt
1764: .executeQuery("select count(*) from selfReferencingT1"));
1765: //have to close the resultset because by default, resultsets are held open over commit
1766: rs.close();
1767:
1768: System.out
1769: .println("Positive Test14b - make sure self referential update restrict works when updateRow is issued");
1770: dumpRS(stmt.executeQuery("select * from selfReferencingT2"));
1771: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1772: ResultSet.CONCUR_UPDATABLE);
1773: rs = stmt
1774: .executeQuery("SELECT * FROM selfReferencingT2 FOR UPDATE");
1775: rs.next();
1776: System.out.println("column 1 on this row is "
1777: + rs.getString(1));
1778: System.out
1779: .println("update row should fail because cascade constraint is update restrict");
1780: rs.updateString(1, "e2");
1781: try {
1782: rs.updateRow();
1783: System.out
1784: .println("FAIL!!! this update should have caused violation of foreign key constraint");
1785: } catch (SQLException e) {
1786: System.out.println("SQL State : " + e.getSQLState());
1787: System.out.println("Got expected exception "
1788: + e.getMessage());
1789: }
1790: //have to close the resultset because by default, resultsets are held open over commit
1791: rs.close();
1792:
1793: System.out
1794: .println("Positive Test15 - With autocommit off, attempt to drop a table when there is an open updatable resultset on it");
1795: reloadData();
1796: conn.setAutoCommit(false);
1797: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1798: ResultSet.CONCUR_UPDATABLE);
1799: rs = stmt.executeQuery("SELECT * FROM t1");
1800: rs.next();
1801: System.out
1802: .println("Opened an updatable resultset. Now trying to drop that table through another Statement");
1803: stmt1 = conn.createStatement();
1804: try {
1805: stmt1.executeUpdate("drop table t1");
1806: System.out
1807: .println("FAIL!!! drop table should have failed because the updatable resultset is still open");
1808: } catch (SQLException e) {
1809: System.out.println("SQL State : " + e.getSQLState());
1810: System.out.println("Got expected exception "
1811: + e.getMessage());
1812: }
1813: System.out
1814: .println("Since autocommit is off, the drop table exception will NOT result in a runtime rollback and hence updatable resultset object is still open");
1815: rs.deleteRow();
1816: rs.close();
1817: conn.setAutoCommit(true);
1818:
1819: System.out
1820: .println("Positive Test16a - Do deleteRow within a transaction and then rollback the transaction");
1821: reloadData();
1822: conn.setAutoCommit(false);
1823: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1824: ResultSet.CONCUR_UPDATABLE);
1825: System.out
1826: .println("Verify that before delete trigger got fired, row count is 0 in deleteTriggerInsertIntoThisTable");
1827: dumpRS(stmt
1828: .executeQuery("select count(*) from deleteTriggerInsertIntoThisTable"));
1829: System.out
1830: .println("Verify that before deleteRow, row count is 4 in table0WithTriggers");
1831: dumpRS(stmt
1832: .executeQuery("select count(*) from table0WithTriggers"));
1833: rs = stmt
1834: .executeQuery("SELECT * FROM table0WithTriggers FOR UPDATE");
1835: rs.next();
1836: System.out.println("column 1 on this row is "
1837: + rs.getInt(1));
1838: System.out
1839: .println("now try to delete row and make sure that trigger got fired");
1840: rs.deleteRow();
1841: rs.close();
1842: System.out
1843: .println("Verify that delete trigger got fired by verifying the row count to be 1 in deleteTriggerInsertIntoThisTable");
1844: dumpRS(stmt
1845: .executeQuery("select count(*) from deleteTriggerInsertIntoThisTable"));
1846: System.out
1847: .println("Verify that deleteRow in transaction, row count is 3 in table0WithTriggers");
1848: dumpRS(stmt
1849: .executeQuery("select count(*) from table0WithTriggers"));
1850: //have to close the resultset because by default, resultsets are held open over commit
1851: rs.close();
1852: conn.rollback();
1853: System.out
1854: .println("Verify that after rollback, row count is back to 0 in deleteTriggerInsertIntoThisTable");
1855: dumpRS(stmt
1856: .executeQuery("select count(*) from deleteTriggerInsertIntoThisTable"));
1857: System.out
1858: .println("Verify that after rollback, row count is back to 4 in table0WithTriggers");
1859: dumpRS(stmt
1860: .executeQuery("select count(*) from table0WithTriggers"));
1861: conn.setAutoCommit(true);
1862:
1863: System.out
1864: .println("Positive Test16b - Do updateRow within a transaction and then rollback the transaction");
1865: reloadData();
1866: conn.setAutoCommit(false);
1867: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1868: ResultSet.CONCUR_UPDATABLE);
1869: System.out
1870: .println("Verify that before update trigger got fired, row count is 0 in updateTriggerInsertIntoThisTable");
1871: dumpRS(stmt
1872: .executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
1873: System.out
1874: .println("Look at the data in table0WithTriggers before trigger gets fired");
1875: dumpRS(stmt
1876: .executeQuery("select * from table0WithTriggers"));
1877: rs = stmt.executeQuery("SELECT * FROM table0WithTriggers");
1878: rs.next();
1879: System.out.println("column 1 on this row is "
1880: + rs.getInt(1));
1881: System.out
1882: .println("now try to update row and make sure that trigger got fired");
1883: rs.updateLong(1, 123);
1884: rs.updateRow();
1885: rs.close();
1886: System.out
1887: .println("Verify that update trigger got fired by verifying the row count to be 1 in updateTriggerInsertIntoThisTable");
1888: dumpRS(stmt
1889: .executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
1890: System.out
1891: .println("Verify that new data in table0WithTriggers");
1892: dumpRS(stmt
1893: .executeQuery("select * from table0WithTriggers"));
1894: //have to close the resultset because by default, resultsets are held open over commit
1895: rs.close();
1896: conn.rollback();
1897: System.out
1898: .println("Verify that after rollback, row count is back to 0 in updateTriggerInsertIntoThisTable");
1899: dumpRS(stmt
1900: .executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
1901: System.out
1902: .println("Verify that after rollback, table0WithTriggers is back to its original contents");
1903: dumpRS(stmt
1904: .executeQuery("select * from table0WithTriggers"));
1905: conn.setAutoCommit(true);
1906:
1907: System.out
1908: .println("Positive Test17 - After deleteRow, resultset is positioned before the next row");
1909: reloadData();
1910: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1911: ResultSet.CONCUR_UPDATABLE);
1912: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
1913: rs.next();
1914: rs.deleteRow();
1915: System.out
1916: .println("getXXX right after deleteRow will fail because resultset is not positioned on a row, instead it is right before the next row");
1917: try {
1918: System.out
1919: .println("column 1 (which is not nullable) after deleteRow is "
1920: + rs.getString(1));
1921: } catch (SQLException e) {
1922: System.out.println("SQL State : " + e.getSQLState());
1923: System.out.println("Got expected exception "
1924: + e.getMessage());
1925: }
1926: rs.close();
1927:
1928: System.out
1929: .println("Positive Test18 - Test cancelRowUpdates method as the first updatable ResultSet api on a read-only resultset");
1930: stmt = conn.createStatement();
1931: rs = stmt
1932: .executeQuery("SELECT * FROM AllDataTypesForTestingTable");
1933: try {
1934: rs.cancelRowUpdates();
1935: System.out
1936: .println("Test failed - should not have reached here because cancelRowUpdates is being called on a read-only resultset");
1937: } catch (SQLException e) {
1938: System.out.println("SQL State : " + e.getSQLState());
1939: System.out.println("Got expected exception "
1940: + e.getMessage());
1941: }
1942: rs.close();
1943:
1944: System.out
1945: .println("Positive Test19 - Test updateRow method as the first updatable ResultSet api on a read-only resultset");
1946: stmt = conn.createStatement();
1947: rs = stmt
1948: .executeQuery("SELECT * FROM AllDataTypesForTestingTable");
1949: rs.next();
1950: try {
1951: rs.updateRow();
1952: System.out
1953: .println("Test failed - should not have reached here because updateRow is being called on a read-only resultset");
1954: return;
1955: } catch (Throwable e) {
1956: System.out.println(" Got expected exception : "
1957: + e.getMessage());
1958: }
1959: rs.close();
1960:
1961: System.out
1962: .println("Positive Test20 - Test updateXXX methods as the first updatable ResultSet api on a read-only resultset");
1963: conn.setAutoCommit(false);
1964: stmt = conn.createStatement();
1965: for (int updateXXXName = 1; updateXXXName <= allUpdateXXXNames.length; updateXXXName++) {
1966: System.out.println(" Test "
1967: + allUpdateXXXNames[updateXXXName - 1]
1968: + " on a readonly resultset");
1969: for (int indexOrName = 1; indexOrName <= 2; indexOrName++) {
1970: rs = stmt
1971: .executeQuery("SELECT * FROM AllDataTypesForTestingTable");
1972: rs.next();
1973: rs1 = stmt1
1974: .executeQuery("SELECT * FROM AllDataTypesNewValuesData");
1975: rs1.next();
1976: if (indexOrName == 1) //test by passing column position
1977: System.out
1978: .println(" Using column position as first parameter to "
1979: + allUpdateXXXNames[updateXXXName - 1]);
1980: else
1981: System.out
1982: .println(" Using column name as first parameter to "
1983: + allUpdateXXXNames[updateXXXName - 1]);
1984: try {
1985: if (updateXXXName == 1) {//update column with updateShort methods
1986: if (indexOrName == 1) //test by passing column position
1987: rs.updateShort(1, rs1
1988: .getShort(updateXXXName));
1989: else
1990: //test by passing column name
1991: rs.updateShort(ColumnNames[0], rs1
1992: .getShort(updateXXXName));
1993: } else if (updateXXXName == 2) { //update column with updateInt methods
1994: if (indexOrName == 1) //test by passing column position
1995: rs.updateInt(1, rs1
1996: .getInt(updateXXXName));
1997: else
1998: //test by passing column name
1999: rs.updateInt(ColumnNames[0], rs1
2000: .getInt(updateXXXName));
2001: } else if (updateXXXName == 3) { //update column with updateLong methods
2002: if (indexOrName == 1) //test by passing column position
2003: rs.updateLong(1, rs1
2004: .getLong(updateXXXName));
2005: else
2006: //test by passing column name
2007: rs.updateLong(ColumnNames[0], rs1
2008: .getLong(updateXXXName));
2009: } else if (updateXXXName == 4) { //update column with updateBigDecimal methods
2010: if (indexOrName == 1) //test by passing column position
2011: BigDecimalHandler
2012: .updateBigDecimalString(
2013: rs,
2014: 1,
2015: BigDecimalHandler
2016: .getBigDecimalString(
2017: rs1,
2018: updateXXXName));
2019: else
2020: //test by passing column name
2021: BigDecimalHandler
2022: .updateBigDecimalString(
2023: rs,
2024: ColumnNames[0],
2025: BigDecimalHandler
2026: .getBigDecimalString(
2027: rs1,
2028: updateXXXName));
2029: } else if (updateXXXName == 5) { //update column with updateFloat methods
2030: if (indexOrName == 1) //test by passing column position
2031: rs.updateFloat(1, rs1
2032: .getFloat(updateXXXName));
2033: else
2034: //test by passing column name
2035: rs.updateFloat(ColumnNames[0], rs1
2036: .getFloat(updateXXXName));
2037: } else if (updateXXXName == 6) { //update column with updateDouble methods
2038: if (indexOrName == 1) //test by passing column position
2039: rs.updateDouble(1, rs1
2040: .getDouble(updateXXXName));
2041: else
2042: //test by passing column name
2043: rs.updateDouble(ColumnNames[0], rs1
2044: .getDouble(updateXXXName));
2045: } else if (updateXXXName == 7) { //update column with updateString methods
2046: if (indexOrName == 1) //test by passing column position
2047: rs.updateString(1, rs1
2048: .getString(updateXXXName));
2049: else
2050: //test by passing column name
2051: rs.updateString(ColumnNames[0], rs1
2052: .getString(updateXXXName));
2053: } else if (updateXXXName == 8) { //update column with updateAsciiStream methods
2054: if (indexOrName == 1) //test by passing column position
2055: rs.updateAsciiStream(1, rs1
2056: .getAsciiStream(updateXXXName),
2057: 4);
2058: else
2059: //test by passing column name
2060: rs
2061: .updateAsciiStream(
2062: ColumnNames[0],
2063: rs1
2064: .getAsciiStream(updateXXXName),
2065: 4);
2066: } else if (updateXXXName == 9) { //update column with updateCharacterStream methods
2067: if (indexOrName == 1) //test by passing column position
2068: rs
2069: .updateCharacterStream(
2070: 1,
2071: rs1
2072: .getCharacterStream(updateXXXName),
2073: 4);
2074: else
2075: //test by passing column name
2076: rs
2077: .updateCharacterStream(
2078: ColumnNames[0],
2079: rs1
2080: .getCharacterStream(updateXXXName),
2081: 4);
2082: } else if (updateXXXName == 10) { //update column with updateByte methods
2083: if (indexOrName == 1) //test by passing column position
2084: rs.updateByte(1, rs1.getByte(1));
2085: else
2086: //test by passing column name
2087: rs.updateByte(ColumnNames[0], rs1
2088: .getByte(1));
2089: } else if (updateXXXName == 11) { //update column with updateBytes methods
2090: if (indexOrName == 1) //test by passing column position
2091: rs.updateBytes(1, rs1
2092: .getBytes(updateXXXName));
2093: else
2094: //test by passing column name
2095: rs.updateBytes(ColumnNames[0], rs1
2096: .getBytes(updateXXXName));
2097: } else if (updateXXXName == 12) { //update column with updateBinaryStream methods
2098: if (indexOrName == 1) //test by passing column position
2099: rs
2100: .updateBinaryStream(
2101: 1,
2102: rs1
2103: .getBinaryStream(updateXXXName),
2104: 2);
2105: else
2106: //test by passing column name
2107: rs
2108: .updateBinaryStream(
2109: ColumnNames[0],
2110: rs1
2111: .getBinaryStream(updateXXXName),
2112: 2);
2113: } else if (updateXXXName == 13) { //update column with updateClob methods
2114: if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
2115: continue;
2116: if (indexOrName == 1) //test by passing column position
2117: rs.updateClob(1, rs1
2118: .getClob(updateXXXName));
2119: else
2120: //test by passing column name
2121: rs.updateClob(ColumnNames[0], rs1
2122: .getClob(updateXXXName));
2123: } else if (updateXXXName == 14) { //update column with updateDate methods
2124: if (indexOrName == 1) //test by passing column position
2125: rs.updateDate(1, rs1
2126: .getDate(updateXXXName));
2127: else
2128: //test by passing column name
2129: rs.updateDate(ColumnNames[0], rs1
2130: .getDate(updateXXXName));
2131: } else if (updateXXXName == 15) { //update column with updateTime methods
2132: if (indexOrName == 1) //test by passing column position
2133: rs.updateTime(1, rs1
2134: .getTime(updateXXXName));
2135: else
2136: //test by passing column name
2137: rs.updateTime(ColumnNames[0], rs1
2138: .getTime(updateXXXName));
2139: } else if (updateXXXName == 16) { //update column with updateTimestamp methods
2140: if (indexOrName == 1) //test by passing column position
2141: rs.updateTimestamp(1, rs1
2142: .getTimestamp(updateXXXName));
2143: else
2144: //test by passing column name
2145: rs.updateTimestamp(ColumnNames[0], rs1
2146: .getTimestamp(updateXXXName));
2147: } else if (updateXXXName == 17) { //update column with updateBlob methods
2148: if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
2149: continue;
2150: if (indexOrName == 1) //test by passing column position
2151: rs.updateBlob(1, rs1
2152: .getBlob(updateXXXName));
2153: else
2154: //test by passing column name
2155: rs.updateBlob(ColumnNames[0], rs1
2156: .getBlob(updateXXXName));
2157: } else if (updateXXXName == 18) { //update column with getBoolean methods
2158: //use SHORT sql type column's value for testing boolean since Derby don't support boolean datatype
2159: //Since Derby does not support Boolean datatype, this method is going to fail with the syntax error
2160: if (indexOrName == 1) //test by passing column position
2161: rs.updateBoolean(1, rs1.getBoolean(1));
2162: else
2163: //test by passing column name
2164: rs.updateBoolean(ColumnNames[0], rs1
2165: .getBoolean(1));
2166: } else if (updateXXXName == 19) { //update column with updateNull methods
2167: if (indexOrName == 1) //test by passing column position
2168: rs.updateNull(1);
2169: else
2170: //test by passing column name
2171: rs.updateNull(ColumnNames[0]);
2172: } else if (updateXXXName == 20) { //update column with updateArray methods - should get not implemented exception
2173: if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
2174: continue;
2175: if (indexOrName == 1) //test by passing column position
2176: rs.updateArray(1, null);
2177: else
2178: //test by passing column name
2179: rs.updateArray(ColumnNames[0], null);
2180: } else if (updateXXXName == 21) { //update column with updateRef methods - should get not implemented exception
2181: if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
2182: continue;
2183: if (indexOrName == 1) //test by passing column position
2184: rs.updateRef(1, null);
2185: else
2186: //test by passing column name
2187: rs.updateRef(ColumnNames[0], null);
2188: }
2189: System.out
2190: .println("Test failed - should not have reached here because updateXXX is being called on a read-only resultset");
2191: return;
2192: } catch (Throwable e) {
2193: System.out
2194: .println(" Got expected exception : "
2195: + e.getMessage());
2196: }
2197: }
2198: }
2199: conn.rollback();
2200: conn.setAutoCommit(true);
2201:
2202: System.out
2203: .println("Positive Test21 - Test all updateXXX(excluding updateObject) methods on all the supported sql datatypes");
2204: conn.setAutoCommit(false);
2205: PreparedStatement pstmt = conn
2206: .prepareStatement(
2207: "SELECT * FROM AllDataTypesForTestingTable FOR UPDATE",
2208: ResultSet.TYPE_FORWARD_ONLY,
2209: ResultSet.CONCUR_UPDATABLE);
2210: PreparedStatement pstmt1 = conn
2211: .prepareStatement("SELECT * FROM AllDataTypesNewValuesData");
2212: for (int sqlType = 1, checkAgainstColumn = 1; sqlType <= allSQLTypes.length; sqlType++) {
2213: conn.rollback();
2214: System.out.println("Next datatype to test is "
2215: + allSQLTypes[sqlType - 1]);
2216: for (int updateXXXName = 1; updateXXXName <= allUpdateXXXNames.length; updateXXXName++) {
2217: checkAgainstColumn = updateXXXName;
2218: if (!HAVE_BIG_DECIMAL && (updateXXXName == 4))
2219: continue;
2220: System.out.println(" Testing "
2221: + allUpdateXXXNames[updateXXXName - 1]
2222: + " on SQL type "
2223: + allSQLTypes[sqlType - 1]);
2224: for (int indexOrName = 1; indexOrName <= 2; indexOrName++) {
2225: if (indexOrName == 1) //test by passing column position
2226: System.out
2227: .println(" Using column position as first parameter to "
2228: + allUpdateXXXNames[updateXXXName - 1]);
2229: else
2230: System.out
2231: .println(" Using column name as first parameter to "
2232: + allUpdateXXXNames[updateXXXName - 1]);
2233: rs = pstmt.executeQuery();
2234: rs.next();
2235: rs1 = pstmt1.executeQuery();
2236: rs1.next();
2237: try {
2238: if (updateXXXName == 1) {//update column with updateShort methods
2239: if (indexOrName == 1) //test by passing column position
2240: rs.updateShort(sqlType, rs1
2241: .getShort(updateXXXName));
2242: else
2243: //test by passing column name
2244: rs
2245: .updateShort(
2246: ColumnNames[sqlType - 1],
2247: rs1
2248: .getShort(updateXXXName));
2249: } else if (updateXXXName == 2) { //update column with updateInt methods
2250: if (indexOrName == 1) //test by passing column position
2251: rs.updateInt(sqlType, rs1
2252: .getInt(updateXXXName));
2253: else
2254: //test by passing column name
2255: rs.updateInt(
2256: ColumnNames[sqlType - 1],
2257: rs1.getInt(updateXXXName));
2258: } else if (updateXXXName == 3) { //update column with updateLong methods
2259: if (indexOrName == 1) //test by passing column position
2260: rs.updateLong(sqlType, rs1
2261: .getLong(updateXXXName));
2262: else
2263: //test by passing column name
2264: rs.updateLong(
2265: ColumnNames[sqlType - 1],
2266: rs1.getLong(updateXXXName));
2267: } else if (updateXXXName == 4) { //update column with updateBigDecimal methods
2268: if (HAVE_BIG_DECIMAL) {
2269: if (indexOrName == 1) //test by passing column position
2270: rs
2271: .updateBigDecimal(
2272: sqlType,
2273: rs1
2274: .getBigDecimal(updateXXXName));
2275: else
2276: //test by passing column name
2277: rs
2278: .updateBigDecimal(
2279: ColumnNames[sqlType - 1],
2280: rs1
2281: .getBigDecimal(updateXXXName));
2282: }
2283: } else if (updateXXXName == 5) { //update column with updateFloat methods
2284: if (indexOrName == 1) //test by passing column position
2285: rs.updateFloat(sqlType, rs1
2286: .getFloat(updateXXXName));
2287: else
2288: //test by passing column name
2289: rs
2290: .updateFloat(
2291: ColumnNames[sqlType - 1],
2292: rs1
2293: .getFloat(updateXXXName));
2294: } else if (updateXXXName == 6) { //update column with updateDouble methods
2295: if (indexOrName == 1) //test by passing column position
2296: rs.updateDouble(sqlType, rs1
2297: .getDouble(updateXXXName));
2298: else
2299: //test by passing column name
2300: rs
2301: .updateDouble(
2302: ColumnNames[sqlType - 1],
2303: rs1
2304: .getDouble(updateXXXName));
2305: } else if (updateXXXName == 7) { //update column with updateString methods
2306: if (indexOrName == 1) //test by passing column position
2307: rs.updateString(sqlType, rs1
2308: .getString(updateXXXName));
2309: else
2310: //test by passing column name
2311: rs
2312: .updateString(
2313: ColumnNames[sqlType - 1],
2314: rs1
2315: .getString(updateXXXName));
2316: } else if (updateXXXName == 8) { //update column with updateAsciiStream methods
2317: if (indexOrName == 1) //test by passing column position
2318: rs
2319: .updateAsciiStream(
2320: sqlType,
2321: rs1
2322: .getAsciiStream(updateXXXName),
2323: 4);
2324: else
2325: //test by passing column name
2326: rs
2327: .updateAsciiStream(
2328: ColumnNames[sqlType - 1],
2329: rs1
2330: .getAsciiStream(updateXXXName),
2331: 4);
2332: } else if (updateXXXName == 9) { //update column with updateCharacterStream methods
2333: if (indexOrName == 1) //test by passing column position
2334: rs
2335: .updateCharacterStream(
2336: sqlType,
2337: rs1
2338: .getCharacterStream(updateXXXName),
2339: 4);
2340: else
2341: //test by passing column name
2342: rs
2343: .updateCharacterStream(
2344: ColumnNames[sqlType - 1],
2345: rs1
2346: .getCharacterStream(updateXXXName),
2347: 4);
2348: } else if (updateXXXName == 10) { //update column with updateByte methods
2349: checkAgainstColumn = 1;
2350: if (indexOrName == 1) //test by passing column position
2351: rs
2352: .updateByte(
2353: sqlType,
2354: rs1
2355: .getByte(checkAgainstColumn));
2356: else
2357: //test by passing column name
2358: rs
2359: .updateByte(
2360: ColumnNames[sqlType - 1],
2361: rs1
2362: .getByte(checkAgainstColumn));
2363: } else if (updateXXXName == 11) { //update column with updateBytes methods
2364: if (indexOrName == 1) //test by passing column position
2365: rs.updateBytes(sqlType, rs1
2366: .getBytes(updateXXXName));
2367: else
2368: //test by passing column name
2369: rs
2370: .updateBytes(
2371: ColumnNames[sqlType - 1],
2372: rs1
2373: .getBytes(updateXXXName));
2374: } else if (updateXXXName == 12) { //update column with updateBinaryStream methods
2375: if (indexOrName == 1) //test by passing column position
2376: rs
2377: .updateBinaryStream(
2378: sqlType,
2379: rs1
2380: .getBinaryStream(updateXXXName),
2381: 2);
2382: else
2383: //test by passing column name
2384: rs
2385: .updateBinaryStream(
2386: ColumnNames[sqlType - 1],
2387: rs1
2388: .getBinaryStream(updateXXXName),
2389: 2);
2390: } else if (updateXXXName == 13) { //update column with updateClob methods
2391: if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
2392: continue;
2393: if (indexOrName == 1) //test by passing column position
2394: rs.updateClob(sqlType, rs1
2395: .getClob(updateXXXName));
2396: else
2397: //test by passing column name
2398: rs.updateClob(
2399: ColumnNames[sqlType - 1],
2400: rs1.getClob(updateXXXName));
2401: } else if (updateXXXName == 14) { //update column with updateDate methods
2402: if (indexOrName == 1) //test by passing column position
2403: rs.updateDate(sqlType, rs1
2404: .getDate(updateXXXName));
2405: else
2406: //test by passing column name
2407: rs.updateDate(
2408: ColumnNames[sqlType - 1],
2409: rs1.getDate(updateXXXName));
2410: } else if (updateXXXName == 15) { //update column with updateTime methods
2411: if (indexOrName == 1) //test by passing column position
2412: rs.updateTime(sqlType, rs1
2413: .getTime(updateXXXName));
2414: else
2415: //test by passing column name
2416: rs.updateTime(
2417: ColumnNames[sqlType - 1],
2418: rs1.getTime(updateXXXName));
2419: } else if (updateXXXName == 16) { //update column with updateTimestamp methods
2420: if (indexOrName == 1) //test by passing column position
2421: rs
2422: .updateTimestamp(
2423: sqlType,
2424: rs1
2425: .getTimestamp(updateXXXName));
2426: else
2427: //test by passing column name
2428: rs
2429: .updateTimestamp(
2430: ColumnNames[sqlType - 1],
2431: rs1
2432: .getTimestamp(updateXXXName));
2433: } else if (updateXXXName == 17) { //update column with updateBlob methods
2434: if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
2435: continue;
2436: if (indexOrName == 1) //test by passing column position
2437: rs.updateBlob(sqlType, rs1
2438: .getBlob(updateXXXName));
2439: else
2440: //test by passing column name
2441: rs.updateBlob(
2442: ColumnNames[sqlType - 1],
2443: rs1.getBlob(updateXXXName));
2444: } else if (updateXXXName == 18) { //update column with getBoolean methods
2445: //use SHORT sql type column's value for testing boolean since Derby don't support boolean datatype
2446: //Since Derby does not support Boolean datatype, this method is going to fail with the syntax error
2447: if (indexOrName == 1) //test by passing column position
2448: rs.updateBoolean(sqlType, rs1
2449: .getBoolean(1));
2450: else
2451: //test by passing column name
2452: rs.updateBoolean(
2453: ColumnNames[sqlType - 1],
2454: rs1.getBoolean(1));
2455: } else if (updateXXXName == 19) { //update column with updateNull methods
2456: if (indexOrName == 1) //test by passing column position
2457: rs.updateNull(sqlType);
2458: else
2459: //test by passing column name
2460: rs
2461: .updateNull(ColumnNames[sqlType - 1]);
2462: } else if (updateXXXName == 20) { //update column with updateArray methods - should get not implemented exception
2463: if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
2464: continue;
2465: if (indexOrName == 1) //test by passing column position
2466: rs.updateArray(sqlType, null);
2467: else
2468: //test by passing column name
2469: rs.updateArray(
2470: ColumnNames[sqlType - 1],
2471: null);
2472: } else if (updateXXXName == 21) { //update column with updateRef methods - should get not implemented exception
2473: if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
2474: continue;
2475: if (indexOrName == 1) //test by passing column position
2476: rs.updateRef(sqlType, null);
2477: else
2478: //test by passing column name
2479: rs.updateRef(
2480: ColumnNames[sqlType - 1],
2481: null);
2482: }
2483: rs.updateRow();
2484: if ((TestUtil.isNetFramework() && updateXXXRulesTableForNetworkServer[sqlType - 1][updateXXXName - 1]
2485: .equals("ERROR"))
2486: || (TestUtil.isEmbeddedFramework() && updateXXXRulesTableForEmbedded[sqlType - 1][updateXXXName - 1]
2487: .equals("ERROR"))) {
2488: System.out
2489: .println("FAILURE : We shouldn't reach here. The test should have failed earlier on updateXXX or updateRow call");
2490: return;
2491: }
2492: if (verifyData(sqlType, checkAgainstColumn,
2493: "AllDataTypesNewValuesData") == false) {
2494: System.out.println("Test failed");
2495: return;
2496: }
2497: resetData();
2498: } catch (Throwable e) {
2499: if ((TestUtil.isNetFramework() && updateXXXRulesTableForNetworkServer[sqlType - 1][updateXXXName - 1]
2500: .equals("ERROR"))
2501: || (TestUtil.isEmbeddedFramework() && updateXXXRulesTableForEmbedded[sqlType - 1][updateXXXName - 1]
2502: .equals("ERROR")))
2503: System.out
2504: .println(" Got expected exception : "
2505: + e.getMessage());
2506: else {
2507: if ((sqlType == 14 || sqlType == 15 || sqlType == 16)
2508: && //we are dealing with DATE/TIME/TIMESTAMP column types
2509: checkAgainstColumn == 7) //we are dealing with updateString. The failure is because string does not represent a valid datetime value
2510: System.out
2511: .println(" Got expected exception : "
2512: + e.getMessage());
2513: else {
2514: System.out
2515: .println(" Got UNexpected exception : "
2516: + e.getMessage());
2517: return;
2518: }
2519: }
2520: }
2521: }
2522: rs.close();
2523: rs1.close();
2524: }
2525: }
2526: conn.rollback();
2527: conn.setAutoCommit(true);
2528:
2529: System.out
2530: .println("Positive Test22 - Test updateObject method");
2531: conn.setAutoCommit(false);
2532: String displayString;
2533: for (int sqlType = 1; sqlType <= allSQLTypes.length; sqlType++) {
2534: conn.rollback();
2535: System.out.println("Next datatype to test is "
2536: + allSQLTypes[sqlType - 1]);
2537: for (int updateXXXName = 1; updateXXXName <= allUpdateXXXNames.length; updateXXXName++) {
2538: if (!HAVE_BIG_DECIMAL && (updateXXXName == 4))
2539: continue;
2540: for (int indexOrName = 1; indexOrName <= 2; indexOrName++) {
2541: if (indexOrName == 1) //test by passing column position
2542: displayString = " updateObject with column position &";
2543: else
2544: displayString = " updateObject with column name &";
2545: rs = pstmt.executeQuery();
2546: rs.next();
2547: rs1 = pstmt1.executeQuery();
2548: rs1.next();
2549: try {
2550: if (updateXXXName == 1) { //updateObject using Short object
2551: System.out
2552: .println(displayString
2553: + " Short object as parameters");
2554: if (indexOrName == 1) //test by passing column position
2555: rs
2556: .updateObject(
2557: sqlType,
2558: new Short(
2559: rs1
2560: .getShort(updateXXXName)));
2561: else
2562: //test by passing column name
2563: rs
2564: .updateObject(
2565: ColumnNames[sqlType - 1],
2566: new Short(
2567: rs1
2568: .getShort(updateXXXName)));
2569: } else if (updateXXXName == 2) { //updateObject using Integer object
2570: System.out
2571: .println(displayString
2572: + " Integer object as parameters");
2573: if (indexOrName == 1) //test by passing column position
2574: rs
2575: .updateObject(
2576: sqlType,
2577: new Integer(
2578: rs1
2579: .getInt(updateXXXName)));
2580: else
2581: //test by passing column name
2582: rs
2583: .updateObject(
2584: ColumnNames[sqlType - 1],
2585: new Integer(
2586: rs1
2587: .getInt(updateXXXName)));
2588: } else if (updateXXXName == 3) { //updateObject using Long object
2589: System.out.println(displayString
2590: + " Long object as parameters");
2591: if (indexOrName == 1) //test by passing column position
2592: rs
2593: .updateObject(
2594: sqlType,
2595: new Long(
2596: rs1
2597: .getLong(updateXXXName)));
2598: else
2599: //test by passing column name
2600: rs
2601: .updateObject(
2602: ColumnNames[sqlType - 1],
2603: new Long(
2604: rs1
2605: .getLong(updateXXXName)));
2606: } else if (updateXXXName == 4) { //updateObject using BigDecimal object
2607: if (HAVE_BIG_DECIMAL) {
2608: System.out
2609: .println(displayString
2610: + " BigDecimal object as parameters");
2611: if (indexOrName == 1) //test by passing column position
2612: rs
2613: .updateObject(
2614: sqlType,
2615: rs1
2616: .getBigDecimal(updateXXXName));
2617: else
2618: //test by passing column name
2619: rs
2620: .updateObject(
2621: ColumnNames[sqlType - 1],
2622: rs1
2623: .getBigDecimal(updateXXXName));
2624: }
2625: } else if (updateXXXName == 5) { //updateObject using Float object
2626: System.out
2627: .println(displayString
2628: + " Float object as parameters");
2629: if (indexOrName == 1) //test by passing column position
2630: rs
2631: .updateObject(
2632: sqlType,
2633: new Float(
2634: rs1
2635: .getFloat(updateXXXName)));
2636: else
2637: //test by passing column name
2638: rs
2639: .updateObject(
2640: ColumnNames[sqlType - 1],
2641: new Float(
2642: rs1
2643: .getFloat(updateXXXName)));
2644: } else if (updateXXXName == 6) { //updateObject using Double object
2645: System.out
2646: .println(displayString
2647: + " Double object as parameters");
2648: if (indexOrName == 1) //test by passing column position
2649: rs
2650: .updateObject(
2651: sqlType,
2652: new Double(
2653: rs1
2654: .getDouble(updateXXXName)));
2655: else
2656: //test by passing column name
2657: rs
2658: .updateObject(
2659: ColumnNames[sqlType - 1],
2660: new Double(
2661: rs1
2662: .getDouble(updateXXXName)));
2663: } else if (updateXXXName == 7) { //updateObject using String object
2664: System.out
2665: .println(displayString
2666: + " String object as parameters");
2667: if (indexOrName == 1) //test by passing column position
2668: rs.updateObject(sqlType, rs1
2669: .getString(updateXXXName));
2670: else
2671: //test by passing column name
2672: rs
2673: .updateObject(
2674: ColumnNames[sqlType - 1],
2675: rs1
2676: .getString(updateXXXName));
2677: } else if (updateXXXName == 8
2678: || updateXXXName == 12) //updateObject does not accept InputStream and hence this is a no-op
2679: continue;
2680: else if (updateXXXName == 9) //updateObject does not accept Reader and hence this is a no-op
2681: continue;
2682: else if (updateXXXName == 10) //update column with updateByte methods
2683: //non-Object parameter(which is byte in this cas) can't be passed to updateObject mthod
2684: continue;
2685: else if (updateXXXName == 11) { //update column with updateBytes methods
2686: System.out
2687: .println(displayString
2688: + " bytes[] array as parameters");
2689: if (indexOrName == 1) //test by passing column position
2690: rs.updateObject(sqlType, rs1
2691: .getBytes(updateXXXName));
2692: else
2693: //test by passing column name
2694: rs
2695: .updateObject(
2696: ColumnNames[sqlType - 1],
2697: rs1
2698: .getBytes(updateXXXName));
2699: } else if (updateXXXName == 13) { //update column with updateClob methods
2700: if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
2701: continue;
2702: System.out.println(displayString
2703: + " Clob object as parameters");
2704: if (indexOrName == 1) //test by passing column position
2705: rs.updateObject(sqlType, rs1
2706: .getClob(updateXXXName));
2707: else
2708: //test by passing column name
2709: rs.updateObject(
2710: ColumnNames[sqlType - 1],
2711: rs1.getClob(updateXXXName));
2712: } else if (updateXXXName == 14) { //update column with updateDate methods
2713: System.out.println(displayString
2714: + " Date object as parameters");
2715: if (indexOrName == 1) //test by passing column position
2716: rs.updateObject(sqlType, rs1
2717: .getDate(updateXXXName));
2718: else
2719: //test by passing column name
2720: rs.updateObject(
2721: ColumnNames[sqlType - 1],
2722: rs1.getDate(updateXXXName));
2723: } else if (updateXXXName == 15) { //update column with updateTime methods
2724: System.out.println(displayString
2725: + " Time object as parameters");
2726: if (indexOrName == 1) //test by passing column position
2727: rs.updateObject(sqlType, rs1
2728: .getTime(updateXXXName));
2729: else
2730: //test by passing column name
2731: rs.updateObject(
2732: ColumnNames[sqlType - 1],
2733: rs1.getTime(updateXXXName));
2734: } else if (updateXXXName == 16) { //update column with updateTimestamp methods
2735: System.out
2736: .println(displayString
2737: + " TimeStamp object as parameters");
2738: if (indexOrName == 1) //test by passing column position
2739: rs
2740: .updateObject(
2741: sqlType,
2742: rs1
2743: .getTimestamp(updateXXXName));
2744: else
2745: //test by passing column name
2746: rs
2747: .updateObject(
2748: ColumnNames[sqlType - 1],
2749: rs1
2750: .getTimestamp(updateXXXName));
2751: } else if (updateXXXName == 17) { //update column with updateBlob methods
2752: if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
2753: continue;
2754: System.out.println(displayString
2755: + " Blob object as parameters");
2756: if (indexOrName == 1) //test by passing column position
2757: rs.updateObject(sqlType, rs1
2758: .getBlob(updateXXXName));
2759: else
2760: //test by passing column name
2761: rs.updateObject(
2762: ColumnNames[sqlType - 1],
2763: rs1.getBlob(updateXXXName));
2764: } else if (updateXXXName == 18) {//update column with getBoolean methods
2765: System.out
2766: .println(displayString
2767: + " Boolean object as parameters");
2768: //use SHORT sql type column's value for testing boolean since Derby don't support boolean datatype
2769: if (indexOrName == 1) //test by passing column position
2770: rs.updateObject(sqlType,
2771: new Boolean(rs1
2772: .getBoolean(1)));
2773: else
2774: //test by passing column name
2775: rs.updateObject(
2776: ColumnNames[sqlType - 1],
2777: new Boolean(rs1
2778: .getBoolean(1)));
2779: } else if (updateXXXName == 19) { //update column with updateNull methods
2780: System.out.println(displayString
2781: + " null as parameters");
2782: try {
2783: if (indexOrName == 1) //test by passing column position
2784: rs.updateObject(sqlType, null);
2785: else
2786: //test by passing column name
2787: rs
2788: .updateObject(
2789: ColumnNames[sqlType - 1],
2790: null);
2791: } catch (Throwable e) {
2792: System.out
2793: .println(" Got UNexpected exception:"
2794: + e.getMessage());
2795: return;
2796: }
2797: } else if (updateXXXName == 20
2798: || updateXXXName == 21) //since Derby does not support Array, Ref datatype, this is a no-op
2799: continue;
2800:
2801: rs.updateRow();
2802: if (TestUtil.isNetFramework()
2803: && updateXXXName == 13
2804: && (sqlType == 7 || sqlType == 8
2805: || sqlType == 9 || sqlType == 13))
2806: //updateObject with clob allowed on char, varchar, longvarchar & clob
2807: System.out.print("");
2808: else if (TestUtil.isNetFramework()
2809: && updateXXXName == 17
2810: && (sqlType == 12 || sqlType == 17))
2811: //updateObject with blob allowed on longvarchar for bit & blob
2812: System.out.print("");
2813: else if ((TestUtil.isNetFramework() && updateXXXRulesTableForNetworkServer[sqlType - 1][updateXXXName - 1]
2814: .equals("ERROR"))
2815: || (TestUtil.isEmbeddedFramework() && updateXXXRulesTableForEmbedded[sqlType - 1][updateXXXName - 1]
2816: .equals("ERROR"))) {
2817: System.out
2818: .println("FAILURE : We shouldn't reach here. The test should have failed earlier on updateXXX or updateRow call");
2819: return;
2820: }
2821: if (!HAVE_BIG_DECIMAL
2822: && (updateXXXName == 4))
2823: continue;
2824: if (verifyData(sqlType, updateXXXName,
2825: "AllDataTypesNewValuesData") == false) {
2826: System.out.println("Test failed");
2827: return;
2828: }
2829: resetData();
2830: } catch (Throwable e) {
2831: if ((TestUtil.isNetFramework() && updateXXXRulesTableForNetworkServer[sqlType - 1][updateXXXName - 1]
2832: .equals("ERROR"))
2833: || (TestUtil.isEmbeddedFramework() && updateXXXRulesTableForEmbedded[sqlType - 1][updateXXXName - 1]
2834: .equals("ERROR")))
2835: System.out
2836: .println(" Got expected exception : "
2837: + e.getMessage());
2838: else {
2839: if ((sqlType == 14 || sqlType == 15 || sqlType == 16)
2840: && //we are dealing with DATE/TIME/TIMESTAMP column types
2841: updateXXXName == 7) //we are dealing with updateString. The failure is because string does not represent a valid datetime value
2842: System.out
2843: .println(" Got expected exception : "
2844: + e.getMessage());
2845: else {
2846: System.out
2847: .println(" Got UNexpected exception : "
2848: + e.getMessage());
2849: return;
2850: }
2851: }
2852: }
2853: rs.close();
2854: rs1.close();
2855: }
2856: }
2857: }
2858: conn.rollback();
2859: conn.setAutoCommit(true);
2860:
2861: System.out
2862: .println("Positive Test23 - Test cancelRowUpdates after updateXXX methods on all the supported sql datatypes");
2863: conn.setAutoCommit(false);
2864: reloadAllDataTypesForTestingTableData();
2865: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
2866: ResultSet.CONCUR_UPDATABLE);
2867: stmt1 = conn.createStatement();
2868: rs = stmt
2869: .executeQuery("SELECT * FROM AllDataTypesForTestingTable FOR UPDATE");
2870: rs.next();
2871: rs1 = stmt1
2872: .executeQuery("SELECT * FROM AllDataTypesNewValuesData");
2873: rs1.next();
2874:
2875: System.out
2876: .println(" updateShort and then cancelRowUpdates");
2877: short s = rs.getShort(1);
2878: rs.updateShort(1, rs1.getShort(1));
2879: if (rs.getShort(1) != rs1.getShort(1))
2880: return;
2881: rs.cancelRowUpdates();
2882: if (rs.getShort(1) != s)
2883: return;
2884:
2885: System.out.println(" updateInt and then cancelRowUpdates");
2886: int i = rs.getInt(2);
2887: rs.updateInt(2, rs1.getInt(2));
2888: if (rs.getInt(2) != rs1.getInt(2))
2889: return;
2890: rs.cancelRowUpdates();
2891: if (rs.getInt(2) != i)
2892: return;
2893:
2894: System.out
2895: .println(" updateLong and then cancelRowUpdates");
2896: long l = rs.getLong(3);
2897: rs.updateLong(3, rs1.getLong(3));
2898: if (rs.getLong(3) != rs1.getLong(3))
2899: return;
2900: rs.cancelRowUpdates();
2901: if (rs.getLong(3) != l)
2902: return;
2903:
2904: System.out
2905: .println(" updateBigDecimal and then cancelRowUpdates");
2906: String bdString = BigDecimalHandler.getBigDecimalString(rs,
2907: 4);
2908: BigDecimalHandler.updateBigDecimalString(rs, 4,
2909: BigDecimalHandler.getBigDecimalString(rs1, 4));
2910: if (!BigDecimalHandler.getBigDecimalString(rs, 4).equals(
2911: BigDecimalHandler.getBigDecimalString(rs1, 4)))
2912: return;
2913: rs.cancelRowUpdates();
2914: if (!BigDecimalHandler.getBigDecimalString(rs, 4).equals(
2915: bdString))
2916: return;
2917:
2918: System.out
2919: .println(" updateFloat and then cancelRowUpdates");
2920: float f = rs.getFloat(5);
2921: rs.updateFloat(5, rs1.getFloat(5));
2922: if (rs.getFloat(5) != rs1.getFloat(5))
2923: return;
2924: rs.cancelRowUpdates();
2925: if (rs.getFloat(5) != f)
2926: return;
2927:
2928: System.out
2929: .println(" updateDouble and then cancelRowUpdates");
2930: double db = rs.getDouble(6);
2931: rs.updateDouble(6, rs1.getDouble(6));
2932: if (rs.getDouble(6) != rs1.getDouble(6))
2933: return;
2934: rs.cancelRowUpdates();
2935: if (rs.getDouble(6) != db)
2936: return;
2937:
2938: System.out
2939: .println(" updateString and then cancelRowUpdates");
2940: String str = rs.getString(7);
2941: rs.updateString(7, rs1.getString(7));
2942: if (!rs.getString(7).equals(rs1.getString(7)))
2943: return;
2944: rs.cancelRowUpdates();
2945: if (!rs.getString(7).equals(str))
2946: return;
2947:
2948: System.out
2949: .println(" updateAsciiStream and then cancelRowUpdates");
2950: str = rs.getString(8);
2951: rs.updateAsciiStream(8, rs1.getAsciiStream(8), 4);
2952: if (!rs.getString(8).equals(rs1.getString(8)))
2953: return;
2954: rs.cancelRowUpdates();
2955: if (!rs.getString(8).equals(str))
2956: return;
2957:
2958: System.out
2959: .println(" updateCharacterStream and then cancelRowUpdates");
2960: str = rs.getString(9);
2961: rs.updateCharacterStream(9, rs1.getCharacterStream(9), 4);
2962: if (!rs.getString(9).equals(rs1.getString(9)))
2963: return;
2964: rs.cancelRowUpdates();
2965: if (!rs.getString(9).equals(str))
2966: return;
2967:
2968: System.out
2969: .println(" updateByte and then cancelRowUpdates");
2970: s = rs.getShort(1);
2971: rs.updateByte(1, rs1.getByte(1));
2972: if (rs.getShort(1) != rs1.getShort(1))
2973: return;
2974: rs.cancelRowUpdates();
2975: if (rs.getShort(1) != s)
2976: return;
2977:
2978: System.out
2979: .println(" updateBytes and then cancelRowUpdates");
2980: byte[] bts = rs.getBytes(11);
2981: rs.updateBytes(11, rs1.getBytes(11));
2982: if (!(java.util.Arrays.equals(rs.getBytes(11), rs1
2983: .getBytes(11))))
2984: return;
2985: rs.cancelRowUpdates();
2986: if (!(java.util.Arrays.equals(rs.getBytes(11), bts)))
2987: return;
2988:
2989: System.out
2990: .println(" updateBinaryStream and then cancelRowUpdates");
2991: bts = rs.getBytes(12);
2992: rs.updateBinaryStream(12, rs1.getBinaryStream(12), 2);
2993: if (!(java.util.Arrays.equals(rs.getBytes(12), rs1
2994: .getBytes(12))))
2995: return;
2996: rs.cancelRowUpdates();
2997: if (!(java.util.Arrays.equals(rs.getBytes(12), bts)))
2998: return;
2999:
3000: System.out
3001: .println(" updateDate and then cancelRowUpdates");
3002: Date date = rs.getDate(14);
3003: rs.updateDate(14, rs1.getDate(14));
3004: if (rs.getDate(14).compareTo(rs1.getDate(14)) != 0)
3005: return;
3006: rs.cancelRowUpdates();
3007: if (rs.getDate(14).compareTo(date) != 0)
3008: return;
3009:
3010: System.out
3011: .println(" updateTime and then cancelRowUpdates");
3012: Time time = rs.getTime(15);
3013: rs.updateTime(15, rs1.getTime(15));
3014: if (rs.getTime(15).compareTo(rs1.getTime(15)) != 0)
3015: return;
3016: rs.cancelRowUpdates();
3017: if (rs.getTime(15).compareTo(time) != 0)
3018: return;
3019:
3020: System.out
3021: .println(" updateTimestamp and then cancelRowUpdates");
3022: Timestamp timeStamp = rs.getTimestamp(16);
3023: rs.updateTimestamp(16, rs1.getTimestamp(16));
3024: if (!rs.getTimestamp(16).toString().equals(
3025: rs1.getTimestamp(16).toString()))
3026: return;
3027: rs.cancelRowUpdates();
3028: if (!rs.getTimestamp(16).toString().equals(
3029: timeStamp.toString()))
3030: return;
3031:
3032: //Don't test this when running JDK1.3/in Network Server because they both
3033: //do not support updateClob and updateBlob
3034: if (JVMInfo.JDK_ID != 2 && TestUtil.isEmbeddedFramework()) {
3035: System.out
3036: .println(" updateClob and then cancelRowUpdates");
3037: String clb1 = rs.getString(13);
3038: rs.updateClob(13, rs1.getClob(13));
3039: if (!rs.getString(13).equals(rs1.getString(13)))
3040: return;
3041: rs.cancelRowUpdates();
3042: if (!rs.getString(13).equals(clb1))
3043: return;
3044: System.out
3045: .println(" updateBlob and then cancelRowUpdates");
3046: bts = rs.getBytes(17);
3047: rs.updateBlob(17, rs1.getBlob(17));
3048: if (!(java.util.Arrays.equals(rs.getBytes(17), rs1
3049: .getBytes(17))))
3050: return;
3051: rs.cancelRowUpdates();
3052: if (!(java.util.Arrays.equals(rs.getBytes(17), bts)))
3053: return;
3054: }
3055:
3056: rs.close();
3057: rs1.close();
3058: conn.setAutoCommit(true);
3059:
3060: System.out
3061: .println("Positive Test24a - after updateXXX, try cancelRowUpdates and then deleteRow");
3062: reloadData();
3063: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3064: ResultSet.CONCUR_UPDATABLE);
3065: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
3066: rs.next();
3067: System.out
3068: .println("column 1 on this row before updateInt is "
3069: + rs.getInt(1));
3070: rs.updateInt(1, 234);
3071: System.out
3072: .println("column 1 on this row after updateInt is "
3073: + rs.getInt(1));
3074: System.out.println("now cancelRowUpdates on the row");
3075: rs.cancelRowUpdates();
3076: System.out
3077: .println("Since after cancelRowUpdates(), ResultSet is positioned on the same row, getXXX will pass");
3078: System.out
3079: .println("column 1 on this row after cancelRowUpdates is "
3080: + rs.getInt(1));
3081: System.out
3082: .println("Since after cancelRowUpdates(), ResultSet is positioned on the same row, a deleteRow at this point will pass");
3083: try {
3084: rs.deleteRow();
3085: System.out
3086: .println("PASS : deleteRow passed as expected");
3087: } catch (SQLException e) {
3088: dumpSQLExceptions(e);
3089: }
3090: System.out
3091: .println("calling updateRow after deleteRow w/o first positioning the ResultSet on the next row will fail");
3092: try {
3093: rs.updateRow();
3094: System.out
3095: .println("FAIL!!! updateRow should have failed because ResultSet is not positioned on a row");
3096: } catch (SQLException e) {
3097: System.out.println("SQL State : " + e.getSQLState());
3098: System.out.println("Got expected exception "
3099: + e.getMessage());
3100: }
3101: System.out.println("Position the ResultSet with next()");
3102: rs.next();
3103: System.out
3104: .println("Should be able to updateRow() on the current row now");
3105: rs.updateString(2, "234");
3106: rs.updateRow();
3107: //have to close the resultset because by default, resultsets are held open over commit
3108: rs.close();
3109:
3110: System.out
3111: .println("Positive Test25 - issue cancelRowUpdates without any updateXXX");
3112: reloadData();
3113: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3114: ResultSet.CONCUR_UPDATABLE);
3115: rs = stmt.executeQuery("SELECT * FROM t1");
3116: rs.next();
3117: rs.cancelRowUpdates();
3118: //have to close the resultset because by default, resultsets are held open over commit
3119: rs.close();
3120:
3121: System.out
3122: .println("Positive Test26 - issue updateRow without any updateXXX will not move the resultset position");
3123: reloadData();
3124: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3125: ResultSet.CONCUR_UPDATABLE);
3126: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
3127: rs.next();
3128: rs.updateRow(); //this will not move the resultset to right before the next row because there were no updateXXX issued before updateRow
3129: rs.updateRow();
3130: //have to close the resultset because by default, resultsets are held open over commit
3131: rs.close();
3132:
3133: System.out
3134: .println("Positive Test27 - issue updateXXX and then deleteRow");
3135: reloadData();
3136: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3137: ResultSet.CONCUR_UPDATABLE);
3138: rs = stmt.executeQuery("SELECT * FROM t1");
3139: rs.next();
3140: rs.updateInt(1, 1234);
3141: rs.updateString(2, "aaaaa");
3142: rs.deleteRow();
3143: try {
3144: rs.updateRow();
3145: System.out
3146: .println("FAIL!!! deleteRow should have moved the ResultSet to right before the next row");
3147: } catch (SQLException e) {
3148: System.out.println("SQL State : " + e.getSQLState());
3149: System.out.println("Got expected exception "
3150: + e.getMessage());
3151: }
3152: try {
3153: rs.updateInt(1, 2345);
3154: System.out
3155: .println("FAIL!!! deleteRow should have moved the ResultSet to right before the next row");
3156: } catch (SQLException e) {
3157: System.out.println("SQL State : " + e.getSQLState());
3158: System.out.println("Got expected exception "
3159: + e.getMessage());
3160: }
3161: try {
3162: rs.getInt(1);
3163: System.out
3164: .println("FAIL!!! deleteRow should have moved the ResultSet to right before the next row");
3165: } catch (SQLException e) {
3166: System.out.println("SQL State : " + e.getSQLState());
3167: System.out.println("Got expected exception "
3168: + e.getMessage());
3169: }
3170: //have to close the resultset because by default, resultsets are held open over commit
3171: rs.close();
3172:
3173: System.out
3174: .println("Positive Test28 - issue updateXXXs and then move off the row, the changes should be ignored");
3175: reloadData();
3176: dumpRS(stmt.executeQuery("select * from t1"));
3177: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3178: ResultSet.CONCUR_UPDATABLE);
3179: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
3180: rs.next();
3181: System.out
3182: .println(" column 1 on this row before updateInt is "
3183: + rs.getInt(1));
3184: System.out
3185: .println(" Issue updateInt to change the column's value to 2345");
3186: rs.updateInt(1, 2345);
3187: System.out
3188: .println(" Move to next row w/o issuing updateRow");
3189: rs.next(); //the changes made on the earlier row should have be ignored because we moved off that row without issuing updateRow
3190: //have to close the resultset because by default, resultsets are held open over commit
3191: rs.close();
3192: System.out
3193: .println(" Make sure that changes didn't make it to the database");
3194: dumpRS(stmt.executeQuery("select * from t1"));
3195:
3196: System.out
3197: .println("Positive Test29 - issue multiple updateXXXs and then a updateRow");
3198: reloadData();
3199: dumpRS(stmt.executeQuery("select * from t1"));
3200: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3201: ResultSet.CONCUR_UPDATABLE);
3202: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
3203: rs.next();
3204: System.out
3205: .println(" column 1 on this row before updateInt is "
3206: + rs.getInt(1));
3207: System.out
3208: .println(" Issue updateInt to change the column's value to 2345");
3209: rs.updateInt(1, 2345);
3210: System.out
3211: .println(" Issue another updateInt on the same row and column to change the column's value to 9999");
3212: rs.updateInt(1, 9999);
3213: System.out
3214: .println(" Issue updateString to change the column's value to 'xxxxxxx'");
3215: rs.updateString(2, "xxxxxxx");
3216: System.out.println(" Now issue updateRow");
3217: rs.updateRow();
3218: //have to close the resultset because by default, resultsets are held open over commit
3219: rs.close();
3220: System.out
3221: .println(" Make sure that changes made it to the database correctly");
3222: dumpRS(stmt.executeQuery("select * from t1"));
3223:
3224: System.out
3225: .println("Positive Test30 - call updateXXX methods on only columns that correspond to a column in the table");
3226: dumpRS(stmt.executeQuery("select * from t1"));
3227: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3228: ResultSet.CONCUR_UPDATABLE);
3229: rs = stmt.executeQuery("SELECT 1, 2, c1, c2 FROM t1");
3230: rs.next();
3231: rs.updateInt(3, 22);
3232: rs.updateRow();
3233: rs.close();
3234: System.out
3235: .println(" Make sure that changes made it to the database correctly");
3236: dumpRS(stmt.executeQuery("select * from t1"));
3237:
3238: System.out
3239: .println("Positive Test31a - case sensitive table and column names");
3240: stmt
3241: .executeUpdate("create table \"t1\" (\"c11\" int, c12 int)");
3242: stmt
3243: .executeUpdate("insert into \"t1\" values(1, 2), (2,3)");
3244: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3245: ResultSet.CONCUR_UPDATABLE);
3246: rs = stmt
3247: .executeQuery("SELECT \"c11\", \"C12\" FROM \"t1\" FOR UPDATE");
3248: rs.next();
3249: rs.updateInt(1, 11);
3250: rs.updateInt(2, 22);
3251: rs.updateRow();
3252: rs.next();
3253: rs.deleteRow();
3254: rs.close();
3255: System.out
3256: .println(" Make sure that changes made it to the database correctly");
3257: dumpRS(stmt.executeQuery("select * from \"t1\""));
3258:
3259: System.out
3260: .println("Positive Test31b - table and column names with spaces in middle and end");
3261: stmt
3262: .executeUpdate("create table \" t 11 \" (\" c 111 \" int, c112 int)");
3263: stmt
3264: .executeUpdate("insert into \" t 11 \" values(1, 2), (2,3)");
3265: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3266: ResultSet.CONCUR_UPDATABLE);
3267: rs = stmt
3268: .executeQuery("SELECT \" c 111 \", \"C112\" FROM \" t 11 \" ");
3269: rs.next();
3270: rs.updateInt(1, 11);
3271: rs.updateInt(2, 22);
3272: rs.updateRow();
3273: rs.next();
3274: rs.deleteRow();
3275: rs.close();
3276: System.out
3277: .println(" Make sure for table \" t 11 \" that changes made it to the database correctly");
3278: dumpRS(stmt.executeQuery("select * from \" t 11 \""));
3279:
3280: System.out
3281: .println("Positive Test32 - call updateXXX methods on column that is not in for update columns list");
3282: dumpRS(stmt.executeQuery("select * from t1"));
3283: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3284: ResultSet.CONCUR_UPDATABLE);
3285: rs = stmt
3286: .executeQuery("SELECT c1, c2 FROM t1 FOR UPDATE of c1");
3287: rs.next();
3288: try {
3289: rs.updateInt(2, 22);
3290: if (TestUtil.isEmbeddedFramework())
3291: System.out
3292: .println("PASS!!! Embedded throws exception for updateRow");
3293: else
3294: System.out
3295: .println("FAIL!!! Network Server should throw exception for updateXXX");
3296: } catch (SQLException e) {
3297: System.out.println("SQL State : " + e.getSQLState());
3298: System.out.println("Got expected exception "
3299: + e.getMessage());
3300: }
3301: try {
3302: rs.updateRow();
3303: System.out.println("updateRow passed");
3304: } catch (SQLException e) {
3305: if (TestUtil.isNetFramework())
3306: System.out
3307: .println("FAIL!!! updateRow w/o updateXXX is no-op in Network Server");
3308: else
3309: System.out.println("FAIL!!! exception is "
3310: + e.getMessage());
3311: }
3312: rs.close();
3313: System.out
3314: .println(" Make sure the contents of table are unchanged");
3315: dumpRS(stmt.executeQuery("select * from t1"));
3316:
3317: System.out
3318: .println("Positive Test33 - try to update a table from another schema");
3319: System.out
3320: .println(" contents of table t1 from current schema");
3321: dumpRS(stmt.executeQuery("select * from t1"));
3322: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3323: ResultSet.CONCUR_UPDATABLE);
3324: stmt.executeUpdate("create schema s2");
3325: stmt
3326: .executeUpdate("create table s2.t1 (c1s2t1 int, c2s2t1 smallint, c3s2t2 double)");
3327: stmt
3328: .executeUpdate("insert into s2.t1 values(1,2,2.2),(1,3,3.3)");
3329: System.out.println(" contents of table t1 from schema s2");
3330: dumpRS(stmt.executeQuery("select * from s2.t1"));
3331: System.out
3332: .println(" Try to change contents of 2nd column of s2.t1 using updateRow");
3333: rs = stmt.executeQuery("SELECT * FROM s2.t1 FOR UPDATE");
3334: rs.next();
3335: rs.updateInt(2, 1);
3336: rs.updateRow();
3337: rs.next();
3338: rs.updateInt(2, 1);
3339: rs.updateRow();
3340: rs.close();
3341: System.out
3342: .println(" Make sure that changes made to the right table t1");
3343: System.out
3344: .println(" contents of table t1 from current schema should have remained unchanged");
3345: dumpRS(stmt.executeQuery("select * from t1"));
3346: System.out
3347: .println(" contents of table t1 from schema s2 should have changed");
3348: dumpRS(stmt.executeQuery("select * from s2.t1"));
3349:
3350: System.out
3351: .println("Positive Test34 - in autocommit mode, check that updateRow and deleteRow does not commit");
3352: conn.setAutoCommit(true);
3353:
3354: // First try deleteRow and updateRow on *first* row of result set
3355: reloadData();
3356: System.out
3357: .println(" Contents before changes to first row in RS:");
3358: dumpRS(stmt.executeQuery("select * from t1"));
3359: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3360: ResultSet.CONCUR_UPDATABLE);
3361: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
3362: rs.next();
3363: rs.deleteRow();
3364: conn.rollback();
3365: rs.close();
3366: System.out
3367: .println(" Make sure the contents of table are unchanged:");
3368: dumpRS(stmt.executeQuery("select * from t1"));
3369: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3370: ResultSet.CONCUR_UPDATABLE);
3371: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
3372: rs.next();
3373: rs.updateInt(1, -rs.getInt(1));
3374: rs.updateRow();
3375: conn.rollback();
3376: rs.close();
3377: System.out
3378: .println(" Make sure the contents of table are unchanged:");
3379: dumpRS(stmt.executeQuery("select * from t1"));
3380:
3381: // Now try the same on the *last* row in the result set
3382: reloadData();
3383: stmt = conn.createStatement();
3384: rs = stmt.executeQuery("SELECT COUNT(*) FROM t1");
3385: rs.next();
3386: int count = rs.getInt(1);
3387: rs.close();
3388:
3389: System.out
3390: .println(" Contents before changes to last row in RS:");
3391: dumpRS(stmt.executeQuery("select * from t1"));
3392: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3393: ResultSet.CONCUR_UPDATABLE);
3394: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
3395: for (int j = 0; j < count; j++) {
3396: rs.next();
3397: }
3398: rs.deleteRow();
3399: conn.rollback();
3400: rs.close();
3401: System.out
3402: .println(" Make sure the contents of table are unchanged:");
3403: dumpRS(stmt.executeQuery("select * from t1"));
3404:
3405: stmt = conn.createStatement();
3406: rs = stmt.executeQuery("SELECT COUNT(*) FROM t1");
3407: rs.next();
3408: count = rs.getInt(1);
3409: rs.close();
3410:
3411: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3412: ResultSet.CONCUR_UPDATABLE);
3413: rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
3414: for (int j = 0; j < count; j++) {
3415: rs.next();
3416: }
3417: rs.updateInt(1, -rs.getInt(1));
3418: rs.updateRow();
3419: conn.rollback();
3420: rs.close();
3421: System.out
3422: .println(" Make sure the contents of table are unchanged:");
3423: dumpRS(stmt.executeQuery("select * from t1"));
3424:
3425: stmt.close();
3426: reloadData();
3427:
3428: // Tests for insert Row
3429: int c41, c42, c41old, c42old;
3430: conn.setAutoCommit(false);
3431: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3432: ResultSet.CONCUR_UPDATABLE);
3433:
3434: System.out
3435: .println("Positive Test 35 - moveToInsertRow, insertRow,"
3436: + "getXXX and moveToCurrentRow");
3437: rs = stmt.executeQuery("SELECT * FROM t4");
3438: rs.next();
3439: // Get values before insertRow for test36
3440: c41old = rs.getInt(1);
3441: c42old = rs.getInt(2);
3442: System.out.println("Positive Test 35.a - moveToInsertRow");
3443: rs.moveToInsertRow();
3444: rs.updateInt(1, 4);
3445: rs.updateInt(2, 4);
3446: System.out.println("Positive Test 35.b - insertRow");
3447: try {
3448: rs.insertRow();
3449: } catch (Throwable t) {
3450: System.out.println("Error " + t.getMessage());
3451: }
3452: System.out
3453: .println("Positive Test 35.c - check that getXXX gets the "
3454: + "correct values after insertRow");
3455: c41 = rs.getInt(1);
3456: c42 = rs.getInt(2);
3457: if ((c41 != 4) || (c42 != 4)) {
3458: System.out.println("getXXX failed after insertRow");
3459: }
3460: System.out.println("Positive Test 35.d - moveToCurrentRow");
3461: rs.moveToCurrentRow();
3462: System.out
3463: .println("Positive Test 35.e - check that getXXX gets the "
3464: + "correct values after moveToCurrentRow");
3465: if (c41old != rs.getInt(1) || c42old != rs.getInt(2)) {
3466: System.out
3467: .println("rs positioned on wrong row after moveToCurrentRow");
3468: }
3469:
3470: System.out
3471: .println("Positive test 36 - call moveToCurrentRow from current row");
3472: rs.moveToCurrentRow();
3473: if (c41old != rs.getInt(1) || c42old != rs.getInt(2)) {
3474: System.out
3475: .println("rs positioned on wrong row after second moveToCurrentRow");
3476: }
3477:
3478: System.out
3479: .println("Positive test 37 - several moveToInsertRow");
3480: System.out
3481: .println("Positive test 37.a - check that getXXX gets the "
3482: + "correct values after moveToInsertRow");
3483: rs.moveToInsertRow();
3484: rs.updateInt(1, 5);
3485: rs.updateInt(2, 4);
3486: c41 = rs.getInt(1);
3487: c42 = rs.getInt(2);
3488: if (c41 != 5 || c42 != 4) {
3489: System.out.println("Got wrong value for columns");
3490: }
3491: System.out
3492: .println("Positive test 37.b - moveToinsertRow from "
3493: + "insertRow");
3494: rs.moveToInsertRow();
3495: System.out
3496: .println("Positive test 37.c - check that getXXX gets "
3497: + "undefined values when updateXXX has not been called yet "
3498: + "on insertRow");
3499: c41 = rs.getInt(1);
3500: if (!rs.wasNull() || c41 != 0) {
3501: System.out
3502: .println("c41 should have been set to NULL after second "
3503: + "moveToInsertRow");
3504: }
3505: c42 = rs.getInt(2);
3506: if (!rs.wasNull() || c42 != 0) {
3507: System.out
3508: .println("c42 should have been set to NULL after second "
3509: + "moveToInsertRow");
3510: }
3511:
3512: System.out
3513: .println("Negative Test 38 - insertRow: do not set a value "
3514: + "to all not nullable columns");
3515: rs.moveToInsertRow();
3516: // Do not update column1
3517: rs.updateInt(2, 5);
3518: try {
3519: rs.insertRow();
3520: System.out.println("Should not have gotten here");
3521: } catch (SQLException se) {
3522: dumpExpectedSQLException(se);
3523: }
3524:
3525: System.out
3526: .println("Negative Test 39 - run updateRow and deleterow "
3527: + "when positioned at insertRow");
3528: rs.moveToInsertRow();
3529: rs.updateInt(1, 6);
3530: rs.updateInt(2, 6);
3531: try {
3532: System.out
3533: .println("Negative Test 39.a - run updateRow on "
3534: + "insertRow");
3535: rs.updateRow();
3536: System.out
3537: .println("Never get here, updateRow not allowed from insertRow");
3538: } catch (SQLException se) {
3539: dumpExpectedSQLException(se);
3540: }
3541: try {
3542: System.out
3543: .println("Negative Test 39.a - run deleteRow on "
3544: + "insertRow");
3545: rs.deleteRow();
3546: System.out
3547: .println("Never get here, deleteRow not allowed from insertRow");
3548: } catch (SQLException se) {
3549: dumpExpectedSQLException(se);
3550: }
3551:
3552: System.out
3553: .println("Negative test 40 - Try to insert row from currentRow");
3554: rs.moveToCurrentRow();
3555: try {
3556: rs.insertRow();
3557: System.out
3558: .println("Should not get here, insertRow should fail "
3559: + "when cursor is not positioned on InsertRow.");
3560: } catch (SQLException se) {
3561: dumpExpectedSQLException(se);
3562: }
3563:
3564: System.out
3565: .println("Positive test 41 - try to insertRow from all "
3566: + "posible positions");
3567: rs = stmt.executeQuery("SELECT * FROM t4 WHERE c41 <= 5");
3568: rs.moveToInsertRow();
3569: rs.updateInt(1, 1000);
3570: rs.updateInt(2, 1000);
3571: rs.insertRow();
3572: while (rs.next()) {
3573: c41 = rs.getInt(1);
3574: c42 = rs.getInt(2);
3575: rs.moveToInsertRow();
3576: rs.updateInt(1, c41 + 100);
3577: rs.updateInt(2, c42 + 100);
3578: rs.insertRow();
3579: }
3580: rs.moveToInsertRow();
3581: rs.updateInt(1, 2000);
3582: rs.updateInt(2, 2000);
3583: rs.insertRow();
3584:
3585: System.out
3586: .println("Positive test 42 - InsertRow leaving a nullable "
3587: + "columns = NULL");
3588: rs.moveToInsertRow();
3589: rs.updateInt(1, 7);
3590: rs.insertRow();
3591:
3592: rs.close();
3593: stmt.close();
3594:
3595: System.out
3596: .println("Positive and negative tests 43 - Commit while on insertRow");
3597: try {
3598: conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
3599: } catch (Throwable e) {
3600: if (JVMInfo.JDK_ID <= 2) {
3601: System.out
3602: .println("This exception is expected with jdk 1.3: "
3603: + "holdability not supported with jdk131 /ibm131 based jvms");
3604:
3605: } else {
3606: System.out.println("Got unexpected exception: "
3607: + e.getMessage());
3608: }
3609: }
3610: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3611: ResultSet.CONCUR_UPDATABLE);
3612: System.out
3613: .println("Positive test 43 - Commit while on insertRow "
3614: + "with holdable cursor");
3615: rs = stmt.executeQuery("SELECT * FROM t4");
3616: rs.next();
3617: rs.moveToInsertRow();
3618: rs.updateInt(1, 8);
3619: rs.updateInt(2, 8);
3620: conn.commit();
3621: try {
3622: rs.insertRow();
3623: } catch (SQLException se) {
3624: dumpSQLExceptions(se);
3625: }
3626: rs.close();
3627: stmt.close();
3628: try {
3629: conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
3630: } catch (Throwable e) {
3631: if (JVMInfo.JDK_ID <= 2) {
3632: System.out
3633: .println("This exception is expected with jdk 1.3: "
3634: + "holdability not supported with jdk131 /ibm131 based jvms");
3635: } else {
3636: System.out.println("Got unexpected exception: "
3637: + e.getMessage());
3638: }
3639: }
3640: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3641: ResultSet.CONCUR_UPDATABLE);
3642: System.out
3643: .println("Negative test 43 - Commit while on insertRow "
3644: + "with not holdable cursor");
3645: rs = stmt.executeQuery("SELECT * FROM t4");
3646: rs.next();
3647: rs.moveToInsertRow();
3648: rs.updateInt(1, 82);
3649: rs.updateInt(2, 82);
3650: conn.commit();
3651: try {
3652: rs.insertRow();
3653: } catch (SQLException se) {
3654: dumpExpectedSQLException(se);
3655: }
3656: rs.close();
3657:
3658: System.out.println("Negative test 44 - Closed RS");
3659: rs = stmt.executeQuery("SELECT * FROM t4");
3660: rs.next();
3661: rs.moveToInsertRow();
3662: rs.updateInt(1, 9);
3663: rs.updateInt(2, 9);
3664: rs.close();
3665: System.out
3666: .println("Negative test 44.a - try insertRow on closed RS");
3667: try {
3668: rs.insertRow();
3669: System.out
3670: .println("FAIL: insertRow can not be called on "
3671: + "closed RS");
3672: } catch (SQLException se) {
3673: dumpExpectedSQLException(se);
3674: }
3675: System.out
3676: .println("Negative test 44.b - try moveToCurrentRow on "
3677: + "closed RS");
3678: try {
3679: rs.moveToCurrentRow();
3680: System.out
3681: .println("FAIL: moveToCurrentRow can not be called on "
3682: + "closed RS");
3683: } catch (SQLException se) {
3684: dumpExpectedSQLException(se);
3685: }
3686: System.out
3687: .println("Negative test 44.c - try moveToInsertRow on "
3688: + "closed RS");
3689: try {
3690: rs.moveToInsertRow();
3691: System.out
3692: .println("FAIL: moveToInsertRow can not be called on "
3693: + "closed RS");
3694: } catch (SQLException se) {
3695: dumpExpectedSQLException(se);
3696: }
3697:
3698: System.out
3699: .println("Positive test 45 - try to insert without "
3700: + "updating all columns. All columns allow nulls or have a "
3701: + "default value");
3702: rs = stmt.executeQuery("SELECT * FROM t5");
3703: rs.next();
3704: rs.moveToInsertRow();
3705: try {
3706: // Should insert a row with NULLS and DEFAULT VALUES
3707: rs.insertRow();
3708: } catch (SQLException se) {
3709: dumpSQLExceptions(se);
3710: }
3711:
3712: rs.close();
3713: conn.commit();
3714:
3715: System.out
3716: .println("Positive test 46 - Rollback with AutoCommit on");
3717: conn.setAutoCommit(true);
3718: rs = stmt.executeQuery("SELECT * FROM t4");
3719: rs.next();
3720: rs.moveToInsertRow();
3721: rs.updateInt(1, 4000);
3722: rs.updateInt(2, 4000);
3723: rs.insertRow();
3724: conn.rollback();
3725: stmt.close();
3726:
3727: System.out
3728: .println("Negative test 47 - insertRow and read-only RS");
3729: stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
3730: ResultSet.CONCUR_READ_ONLY);
3731: rs = stmt.executeQuery("SELECT * FROM t4");
3732: System.out
3733: .println("Negative test 47.a - try moveToInsertRow on "
3734: + "read-only RS");
3735: try {
3736: rs.moveToInsertRow();
3737: System.out
3738: .println("FAIL: moveToInsertRow can not be called on "
3739: + "read-only RS");
3740: } catch (SQLException se) {
3741: dumpExpectedSQLException(se);
3742: }
3743: System.out.println("Negative test 47.b - try updateXXX on "
3744: + "read-only RS");
3745: try {
3746: rs.updateInt(1, 5000);
3747: System.out
3748: .println("FAIL: updateXXX not allowed on read-only RS");
3749: rs.updateInt(2, 5000);
3750: System.out
3751: .println("FAIL: updateXXX not allowed on read-only RS");
3752: } catch (SQLException se) {
3753: dumpExpectedSQLException(se);
3754: }
3755: System.out.println("Negative test 47.c - try insertRow on "
3756: + "read-only RS");
3757: try {
3758: rs.insertRow();
3759: System.out
3760: .println("FAIL: insertRow not allowed on read-only RS");
3761: } catch (SQLException se) {
3762: dumpExpectedSQLException(se);
3763: }
3764: System.out
3765: .println("Negative test 47.d - try moveToCurrentRow on "
3766: + "read-only RS");
3767: try {
3768: rs.moveToCurrentRow();
3769: System.out
3770: .println("FAIL: moveToCurrentRow can not be called on "
3771: + "read-only RS");
3772: } catch (SQLException se) {
3773: dumpExpectedSQLException(se);
3774: }
3775: rs.close();
3776: conn.commit();
3777: stmt.close();
3778:
3779: System.out
3780: .println("Positive test 48 - Test all updateXXX methods on "
3781: + "all the supported sql datatypes");
3782: conn.setAutoCommit(false);
3783: stmt = conn.createStatement();
3784: stmt
3785: .executeUpdate("DELETE FROM AllDataTypesForTestingTable");
3786: conn.commit();
3787: PreparedStatement pstmti = conn
3788: .prepareStatement(
3789: "SELECT * FROM AllDataTypesForTestingTable FOR UPDATE",
3790: ResultSet.TYPE_FORWARD_ONLY,
3791: ResultSet.CONCUR_UPDATABLE);
3792: PreparedStatement pstmt1i = conn
3793: .prepareStatement("SELECT * FROM AllDataTypesNewValuesData");
3794: for (int sqlType = 1, checkAgainstColumn = 1; sqlType <= allSQLTypes.length; sqlType++) {
3795: System.out.println("Next datatype to test is "
3796: + allSQLTypes[sqlType - 1]);
3797: for (int updateXXXName = 1; updateXXXName <= allUpdateXXXNames.length; updateXXXName++) {
3798: checkAgainstColumn = updateXXXName;
3799: System.out.println(" Testing "
3800: + allUpdateXXXNames[updateXXXName - 1]
3801: + " on SQL type "
3802: + allSQLTypes[sqlType - 1]);
3803: for (int indexOrName = 1; indexOrName <= 2; indexOrName++) {
3804: if (indexOrName == 1) //test by passing column position
3805: System.out
3806: .println(" Using column position as first parameter to "
3807: + allUpdateXXXNames[updateXXXName - 1]);
3808: else
3809: System.out
3810: .println(" Using column name as first parameter to "
3811: + allUpdateXXXNames[updateXXXName - 1]);
3812: rs = pstmti.executeQuery();
3813: rs.moveToInsertRow();
3814: rs1 = pstmt1i.executeQuery();
3815: rs1.next();
3816: try {
3817: if (updateXXXName == 1) {//update column with updateShort methods
3818: if (indexOrName == 1) //test by passing column position
3819: rs.updateShort(sqlType, rs1
3820: .getShort(updateXXXName));
3821: else
3822: //test by passing column name
3823: rs
3824: .updateShort(
3825: ColumnNames[sqlType - 1],
3826: rs1
3827: .getShort(updateXXXName));
3828: } else if (updateXXXName == 2) { //update column with updateInt methods
3829: if (indexOrName == 1) //test by passing column position
3830: rs.updateInt(sqlType, rs1
3831: .getInt(updateXXXName));
3832: else
3833: //test by passing column name
3834: rs.updateInt(
3835: ColumnNames[sqlType - 1],
3836: rs1.getInt(updateXXXName));
3837: } else if (updateXXXName == 3) { //update column with updateLong methods
3838: if (indexOrName == 1) //test by passing column position
3839: rs.updateLong(sqlType, rs1
3840: .getLong(updateXXXName));
3841: else
3842: //test by passing column name
3843: rs.updateLong(
3844: ColumnNames[sqlType - 1],
3845: rs1.getLong(updateXXXName));
3846: } else if (updateXXXName == 4) { //update column with updateBigDecimal methods
3847: if (indexOrName == 1) //test by passing column position
3848: rs
3849: .updateBigDecimal(
3850: sqlType,
3851: rs1
3852: .getBigDecimal(updateXXXName));
3853: else
3854: //test by passing column name
3855: rs
3856: .updateBigDecimal(
3857: ColumnNames[sqlType - 1],
3858: rs1
3859: .getBigDecimal(updateXXXName));
3860: } else if (updateXXXName == 5) { //update column with updateFloat methods
3861: if (indexOrName == 1) //test by passing column position
3862: rs.updateFloat(sqlType, rs1
3863: .getFloat(updateXXXName));
3864: else
3865: //test by passing column name
3866: rs
3867: .updateFloat(
3868: ColumnNames[sqlType - 1],
3869: rs1
3870: .getFloat(updateXXXName));
3871: } else if (updateXXXName == 6) { //update column with updateDouble methods
3872: if (indexOrName == 1) //test by passing column position
3873: rs.updateDouble(sqlType, rs1
3874: .getDouble(updateXXXName));
3875: else
3876: //test by passing column name
3877: rs
3878: .updateDouble(
3879: ColumnNames[sqlType - 1],
3880: rs1
3881: .getDouble(updateXXXName));
3882: } else if (updateXXXName == 7) { //update column with updateString methods
3883: if (indexOrName == 1) //test by passing column position
3884: rs.updateString(sqlType, rs1
3885: .getString(updateXXXName));
3886: else
3887: //test by passing column name
3888: rs
3889: .updateString(
3890: ColumnNames[sqlType - 1],
3891: rs1
3892: .getString(updateXXXName));
3893: } else if (updateXXXName == 8) { //update column with updateAsciiStream methods
3894: if (indexOrName == 1) //test by passing column position
3895: rs
3896: .updateAsciiStream(
3897: sqlType,
3898: rs1
3899: .getAsciiStream(updateXXXName),
3900: 4);
3901: else
3902: //test by passing column name
3903: rs
3904: .updateAsciiStream(
3905: ColumnNames[sqlType - 1],
3906: rs1
3907: .getAsciiStream(updateXXXName),
3908: 4);
3909: } else if (updateXXXName == 9) { //update column with updateCharacterStream methods
3910: if (indexOrName == 1) //test by passing column position
3911: rs
3912: .updateCharacterStream(
3913: sqlType,
3914: rs1
3915: .getCharacterStream(updateXXXName),
3916: 4);
3917: else
3918: //test by passing column name
3919: rs
3920: .updateCharacterStream(
3921: ColumnNames[sqlType - 1],
3922: rs1
3923: .getCharacterStream(updateXXXName),
3924: 4);
3925: } else if (updateXXXName == 10) { //update column with updateByte methods
3926: checkAgainstColumn = 1;
3927: if (indexOrName == 1) //test by passing column position
3928: rs
3929: .updateByte(
3930: sqlType,
3931: rs1
3932: .getByte(checkAgainstColumn));
3933: else
3934: //test by passing column name
3935: rs
3936: .updateByte(
3937: ColumnNames[sqlType - 1],
3938: rs1
3939: .getByte(checkAgainstColumn));
3940: } else if (updateXXXName == 11) { //update column with updateBytes methods
3941: if (indexOrName == 1) //test by passing column position
3942: rs.updateBytes(sqlType, rs1
3943: .getBytes(updateXXXName));
3944: else
3945: //test by passing column name
3946: rs
3947: .updateBytes(
3948: ColumnNames[sqlType - 1],
3949: rs1
3950: .getBytes(updateXXXName));
3951: } else if (updateXXXName == 12) { //update column with updateBinaryStream methods
3952: if (indexOrName == 1) //test by passing column position
3953: rs
3954: .updateBinaryStream(
3955: sqlType,
3956: rs1
3957: .getBinaryStream(updateXXXName),
3958: 2);
3959: else
3960: //test by passing column name
3961: rs
3962: .updateBinaryStream(
3963: ColumnNames[sqlType - 1],
3964: rs1
3965: .getBinaryStream(updateXXXName),
3966: 2);
3967: } else if (updateXXXName == 13) { //update column with updateClob methods
3968: if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
3969: continue;
3970: if (indexOrName == 1) //test by passing column position
3971: rs.updateClob(sqlType, rs1
3972: .getClob(updateXXXName));
3973: else
3974: //test by passing column name
3975: rs.updateClob(
3976: ColumnNames[sqlType - 1],
3977: rs1.getClob(updateXXXName));
3978: } else if (updateXXXName == 14) { //update column with updateDate methods
3979: if (indexOrName == 1) //test by passing column position
3980: rs.updateDate(sqlType, rs1
3981: .getDate(updateXXXName));
3982: else
3983: //test by passing column name
3984: rs.updateDate(
3985: ColumnNames[sqlType - 1],
3986: rs1.getDate(updateXXXName));
3987: } else if (updateXXXName == 15) { //update column with updateTime methods
3988: if (indexOrName == 1) //test by passing column position
3989: rs.updateTime(sqlType, rs1
3990: .getTime(updateXXXName));
3991: else
3992: //test by passing column name
3993: rs.updateTime(
3994: ColumnNames[sqlType - 1],
3995: rs1.getTime(updateXXXName));
3996: } else if (updateXXXName == 16) { //update column with updateTimestamp methods
3997: if (indexOrName == 1) //test by passing column position
3998: rs
3999: .updateTimestamp(
4000: sqlType,
4001: rs1
4002: .getTimestamp(updateXXXName));
4003: else
4004: //test by passing column name
4005: rs
4006: .updateTimestamp(
4007: ColumnNames[sqlType - 1],
4008: rs1
4009: .getTimestamp(updateXXXName));
4010: } else if (updateXXXName == 17) { //update column with updateBlob methods
4011: if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
4012: continue;
4013: if (indexOrName == 1) //test by passing column position
4014: rs.updateBlob(sqlType, rs1
4015: .getBlob(updateXXXName));
4016: else
4017: //test by passing column name
4018: rs.updateBlob(
4019: ColumnNames[sqlType - 1],
4020: rs1.getBlob(updateXXXName));
4021: } else if (updateXXXName == 18) { //update column with getBoolean methods
4022: //use SHORT sql type column's value for testing boolean since Derby don't support boolean datatype
4023: //Since Derby does not support Boolean datatype, this method is going to fail with the syntax error
4024: if (indexOrName == 1) //test by passing column position
4025: rs.updateBoolean(sqlType, rs1
4026: .getBoolean(1));
4027: else
4028: //test by passing column name
4029: rs.updateBoolean(
4030: ColumnNames[sqlType - 1],
4031: rs1.getBoolean(1));
4032: } else if (updateXXXName == 19) { //update column with updateNull methods
4033: if (indexOrName == 1) //test by passing column position
4034: rs.updateNull(sqlType);
4035: else
4036: //test by passing column name
4037: rs
4038: .updateNull(ColumnNames[sqlType - 1]);
4039: } else if (updateXXXName == 20) { //update column with updateArray methods - should get not implemented exception
4040: if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
4041: continue;
4042: if (indexOrName == 1) //test by passing column position
4043: rs.updateArray(sqlType, null);
4044: else
4045: //test by passing column name
4046: rs.updateArray(
4047: ColumnNames[sqlType - 1],
4048: null);
4049: } else if (updateXXXName == 21) { //update column with updateRef methods - should get not implemented exception
4050: if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
4051: continue;
4052: if (indexOrName == 1) //test by passing column position
4053: rs.updateRef(sqlType, null);
4054: else
4055: //test by passing column name
4056: rs.updateRef(
4057: ColumnNames[sqlType - 1],
4058: null);
4059: }
4060: rs.insertRow();
4061: if ((TestUtil.isNetFramework() && updateXXXRulesTableForNetworkServer[sqlType - 1][updateXXXName - 1]
4062: .equals("ERROR"))
4063: || (TestUtil.isEmbeddedFramework() && updateXXXRulesTableForEmbedded[sqlType - 1][updateXXXName - 1]
4064: .equals("ERROR"))) {
4065: System.out
4066: .println("FAILURE : We shouldn't reach here. The test should have failed earlier on updateXXX or updateRow call");
4067: return;
4068: }
4069: if (!verifyData(sqlType,
4070: checkAgainstColumn,
4071: "AllDataTypesNewValuesData")) {
4072: System.out
4073: .println("Verify data failed\nTest failed");
4074: return;
4075: }
4076: stmt
4077: .executeUpdate("DELETE FROM AllDataTypesForTestingTable");
4078: } catch (Throwable e) {
4079: if ((TestUtil.isNetFramework() && updateXXXRulesTableForNetworkServer[sqlType - 1][updateXXXName - 1]
4080: .equals("ERROR"))
4081: || (TestUtil.isEmbeddedFramework() && updateXXXRulesTableForEmbedded[sqlType - 1][updateXXXName - 1]
4082: .equals("ERROR")))
4083: System.out
4084: .println(" Got expected exception : "
4085: + e.getMessage());
4086: else {
4087: if ((sqlType == 14 || sqlType == 15 || sqlType == 16)
4088: && //we are dealing with DATE/TIME/TIMESTAMP column types
4089: checkAgainstColumn == 7) //we are dealing with updateString. The failure is because string does not represent a valid datetime value
4090: System.out
4091: .println(" Got expected exception : "
4092: + e.getMessage());
4093: else {
4094: System.out
4095: .println(" Got UNexpected exception : "
4096: + e.getMessage());
4097: return;
4098: }
4099: }
4100: }
4101: }
4102: rs.close();
4103: rs1.close();
4104: }
4105: }
4106: conn.rollback();
4107: conn.setAutoCommit(true);
4108:
4109: // Verify positive tests
4110: dumpRS(stmt.executeQuery("select * from t4"));
4111: dumpRS(stmt.executeQuery("select * from t5"));
4112:
4113: stmt.close();
4114:
4115: teardown();
4116:
4117: conn.close();
4118:
4119: } catch (Throwable e) {
4120: System.out.println("FAIL: exception thrown:");
4121: JDBCDisplayUtil.ShowException(System.out, e);
4122: }
4123:
4124: System.out.println("Finished testing updateable resultsets");
4125: }
4126:
4127: static boolean verifyData(int sqlType, int updateXXXName,
4128: String checkAgainstTheTable) throws SQLException {
4129: PreparedStatement pstmt1 = conn
4130: .prepareStatement("select * from "
4131: + checkAgainstTheTable);
4132: ResultSet rs1 = pstmt1.executeQuery();
4133: rs1.next();
4134: PreparedStatement pstmt = conn
4135: .prepareStatement("select * from AllDataTypesForTestingTable");
4136: ResultSet rs = pstmt.executeQuery();
4137: rs.next();
4138:
4139: if (updateXXXName == 18) { //verifying updateBoolean
4140: if (rs.getBoolean(sqlType) != rs1.getBoolean(1))
4141: return (false);
4142: else
4143: return (true);
4144: }
4145:
4146: if (updateXXXName == 19) { //verifying updateNull
4147: if (rs.getObject(sqlType) == null && rs.wasNull())
4148: return (true);
4149: else
4150: return (false);
4151: }
4152:
4153: if (sqlType == 1) {//verify update made to SMALLINT column with updateXXX methods
4154: if (rs.getShort(sqlType) != rs1.getShort(updateXXXName)) {
4155: return (false);
4156: }
4157: } else if (sqlType == 2) { //verify update made to INTEGER column with updateXXX methods
4158: if (rs.getInt(sqlType) != rs1.getInt(updateXXXName)) {
4159: return (false);
4160: }
4161: } else if (sqlType == 3) //verify update made to BIGINT column with updateXXX methods
4162: if (rs.getLong(sqlType) != rs1.getLong(updateXXXName)) {
4163: return (false);
4164: } else if (sqlType == 4) //verify update made to DECIMAL column with updateXXX methods
4165: if (BigDecimalHandler.getBigDecimalString(rs, sqlType) != BigDecimalHandler
4166: .getBigDecimalString(rs1, updateXXXName)) {
4167: return (false);
4168: } else if (sqlType == 5) //verify update made to REAL column with updateXXX methods
4169: if (rs.getFloat(sqlType) != rs1
4170: .getFloat(updateXXXName)) {
4171: return (false);
4172: } else if (sqlType == 6) //verify update made to DOUBLE column with updateXXX methods
4173: if (rs.getDouble(sqlType) != rs1
4174: .getDouble(updateXXXName)) {
4175: return (false);
4176: } else if (sqlType == 7 || sqlType == 8
4177: || sqlType == 9) //verify update made to CHAR/VARCHAR/LONG VARCHAR column with updateXXX methods
4178: if (!rs.getString(sqlType).equals(
4179: rs1.getString(updateXXXName))) {
4180: return (false);
4181: } else if (sqlType == 10 || sqlType == 11
4182: || sqlType == 12) //verify update made to CHAR/VARCHAR/LONG VARCHAR FOR BIT DATA column with updateXXX methods
4183: if (rs.getBytes(sqlType) != rs1
4184: .getBytes(updateXXXName)) {
4185: return (false);
4186: } else if (sqlType == 13
4187: && JVMInfo.JDK_ID != 2) //verify update made to CLOB column with updateXXX methods
4188: if (!rs
4189: .getClob(sqlType)
4190: .getSubString(1, 4)
4191: .equals(
4192: rs1
4193: .getClob(
4194: updateXXXName)
4195: .getSubString(
4196: 1,
4197: 4))) {
4198: return (false);
4199: } else if (sqlType == 14) //verify update made to DATE column with updateXXX methods
4200: if (rs.getDate(sqlType) != rs1
4201: .getDate(updateXXXName)) {
4202: return (false);
4203: } else if (sqlType == 15) { //verify update made to TIME column with updateXXX methods
4204: if (rs.getTime(sqlType) != rs1
4205: .getTime(updateXXXName)) {
4206: return (false);
4207: }
4208: } else if (sqlType == 16) { //verify update made to TIMESTAMP column with updateXXX methods
4209: // if(rs.getTimestamp(sqlType) != rs1.getTimestamp(updateXXXName)) {
4210: if (!rs
4211: .getTimestamp(
4212: sqlType)
4213: .equals(
4214: rs1
4215: .getTimestamp(updateXXXName))) {
4216: return (false);
4217: }
4218: } else if (sqlType == 17
4219: && JVMInfo.JDK_ID != 2) //verify update made to BLOB column with updateXXX methods
4220: if (rs.getBlob(sqlType)
4221: .getBytes(1, 4) != rs1
4222: .getBlob(
4223: updateXXXName)
4224: .getBytes(1, 4)) {
4225: return (false);
4226: }
4227:
4228: rs.close();
4229: rs1.close();
4230: pstmt.close();
4231: pstmt1.close();
4232: return (true);
4233: }
4234:
4235: static void resetData() throws SQLException {
4236: Statement stmt = conn.createStatement();
4237: stmt.executeUpdate("delete from AllDataTypesForTestingTable");
4238: StringBuffer insertSQL = new StringBuffer(
4239: "insert into AllDataTypesForTestingTable values(");
4240: for (int type = 0; type < allSQLTypes.length - 1; type++) {
4241: insertSQL.append(SQLData[type][0] + ",");
4242: }
4243: insertSQL.append("cast(" + SQLData[allSQLTypes.length - 1][0]
4244: + " as BLOB(1K)))");
4245: stmt.executeUpdate(insertSQL.toString());
4246: }
4247:
4248: // lifted from the autoGeneratedJdbc30 test
4249: public static void dumpRS(ResultSet s) throws SQLException {
4250: if (s == null) {
4251: System.out.println("<NULL>");
4252: return;
4253: }
4254:
4255: ResultSetMetaData rsmd = s.getMetaData();
4256:
4257: // Get the number of columns in the result set
4258: int numCols = rsmd.getColumnCount();
4259:
4260: if (numCols <= 0) {
4261: System.out.println("(no columns!)");
4262: return;
4263: }
4264:
4265: StringBuffer heading = new StringBuffer("\t ");
4266: StringBuffer underline = new StringBuffer("\t ");
4267:
4268: int len;
4269: // Display column headings
4270: for (int i = 1; i <= numCols; i++) {
4271: if (i > 1) {
4272: heading.append(",");
4273: underline.append(" ");
4274: }
4275: len = heading.length();
4276: heading.append(rsmd.getColumnLabel(i));
4277: len = heading.length() - len;
4278: for (int j = len; j > 0; j--) {
4279: underline.append("-");
4280: }
4281: }
4282: System.out.println(heading.toString());
4283: System.out.println(underline.toString());
4284:
4285: StringBuffer row = new StringBuffer();
4286: // Display data, fetching until end of the result set
4287: while (s.next()) {
4288: row.append("\t{");
4289: // Loop through each column, getting the
4290: // column data and displaying
4291: for (int i = 1; i <= numCols; i++) {
4292: if (i > 1)
4293: row.append(",");
4294: row.append(s.getString(i));
4295: }
4296: row.append("}\n");
4297: }
4298: System.out.println(row.toString());
4299: s.close();
4300: }
4301:
4302: static void reloadAllDataTypesForTestingTableData()
4303: throws SQLException {
4304: Statement stmt = conn.createStatement();
4305: stmt.executeUpdate("delete from t1");
4306: stmt.executeUpdate("delete from AllDataTypesForTestingTable");
4307: StringBuffer insertSQL = new StringBuffer(
4308: "insert into AllDataTypesForTestingTable values(");
4309: for (int type = 0; type < allSQLTypes.length - 1; type++)
4310: insertSQL.append(SQLData[type][0] + ",");
4311: insertSQL.append("cast(" + SQLData[allSQLTypes.length - 1][0]
4312: + " as BLOB(1K)))");
4313: stmt.executeUpdate(insertSQL.toString());
4314: }
4315:
4316: static void reloadData() throws SQLException {
4317: Statement stmt = conn.createStatement();
4318: stmt.executeUpdate("delete from t1");
4319: stmt
4320: .executeUpdate("insert into t1 values (1,'aa'), (2,'bb'), (3,'cc')");
4321: stmt.executeUpdate("delete from t3");
4322: stmt.executeUpdate("insert into t3 values (1,1), (2,2)");
4323: stmt.executeUpdate("delete from t4");
4324: stmt.executeUpdate("insert into t4 values (1,1), (2,2), (3,3)");
4325: stmt.executeUpdate("delete from t5");
4326: stmt.executeUpdate("insert into t5 values (1,1), (2,2), (3,3)");
4327: stmt.executeUpdate("delete from table0WithTriggers");
4328: stmt
4329: .executeUpdate("insert into table0WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
4330: stmt.executeUpdate("delete from table1WithTriggers");
4331: stmt
4332: .executeUpdate("insert into table1WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
4333: stmt.executeUpdate("delete from table2WithTriggers");
4334: stmt
4335: .executeUpdate("insert into table2WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
4336: stmt
4337: .executeUpdate("delete from deleteTriggerInsertIntoThisTable");
4338: stmt
4339: .executeUpdate("delete from updateTriggerInsertIntoThisTable");
4340: }
4341:
4342: static void setup(boolean first) throws SQLException {
4343: Statement stmt = conn.createStatement();
4344: stmt.executeUpdate("create table t1 (c1 int, c2 char(20))");
4345: stmt.executeUpdate("create view v1 as select * from t1");
4346: stmt.executeUpdate("create table t2 (c21 int, c22 int)");
4347: stmt
4348: .executeUpdate("create table t3 (c31 int not null primary key, c32 smallint)");
4349: stmt
4350: .executeUpdate("create table t4 (c41 int not null primary key, c42 int)");
4351: stmt
4352: .executeUpdate("create table t5 (c51 int not null default 0, c52 int)");
4353: stmt
4354: .executeUpdate("create table tableWithPrimaryKey (c1 int not null, c2 int not null, constraint pk primary key(c1,c2))");
4355: stmt
4356: .executeUpdate("create table tableWithConstraint (c1 int, c2 int, constraint fk foreign key(c1,c2) references tableWithPrimaryKey)");
4357: stmt
4358: .executeUpdate("create table table0WithTriggers (c1 int, c2 bigint)");
4359: stmt
4360: .executeUpdate("create table deleteTriggerInsertIntoThisTable (c1 int)");
4361: stmt
4362: .executeUpdate("create table updateTriggerInsertIntoThisTable (c1 int)");
4363: stmt
4364: .executeUpdate("create trigger tr1 after delete on table0WithTriggers for each statement mode db2sql insert into deleteTriggerInsertIntoThisTable values (1)");
4365: stmt
4366: .executeUpdate("create trigger tr2 after update on table0WithTriggers for each statement mode db2sql insert into updateTriggerInsertIntoThisTable values (1)");
4367: stmt
4368: .executeUpdate("create table table1WithTriggers (c1 int, c2 bigint)");
4369: stmt
4370: .executeUpdate("create trigger tr3 after delete on table1WithTriggers referencing old as old for each row mode db2sql delete from table1WithTriggers where c1=old.c1+1 or c1=old.c1-1");
4371: stmt
4372: .executeUpdate("create table table2WithTriggers (c1 int, c2 bigint)");
4373: stmt
4374: .executeUpdate("create trigger tr4 after update of c2 on table2WithTriggers for each statement mode db2sql update table2WithTriggers set c1=1");
4375: stmt
4376: .executeUpdate("create table selfReferencingT1 (c1 char(2) not null, c2 char(2), constraint selfReferencingT1 primary key(c1), constraint manages1 foreign key(c2) references selfReferencingT1(c1) on delete cascade)");
4377: stmt
4378: .executeUpdate("create table selfReferencingT2 (c1 char(2) not null, c2 char(2), constraint selfReferencingT2 primary key(c1), constraint manages2 foreign key(c2) references selfReferencingT2(c1) on update restrict)");
4379:
4380: stmt.executeUpdate("insert into t1 values (1,'aa')");
4381: stmt.executeUpdate("insert into t1 values (2,'bb')");
4382: stmt.executeUpdate("insert into t1 values (3,'cc')");
4383: stmt.executeUpdate("insert into t2 values (1,1)");
4384: stmt.executeUpdate("insert into t3 values (1,1)");
4385: stmt.executeUpdate("insert into t3 values (2,2)");
4386: stmt.executeUpdate("insert into t4 values (1,1), (2,2), (3,3)");
4387: stmt.executeUpdate("insert into t5 values (1,1), (2,2), (3,3)");
4388: stmt
4389: .executeUpdate("insert into tableWithPrimaryKey values (1, 1), (2, 2), (3, 3), (4, 4)");
4390: stmt
4391: .executeUpdate("insert into tableWithConstraint values (1, 1), (2, 2), (3, 3), (4, 4)");
4392: stmt
4393: .executeUpdate("insert into table0WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
4394: stmt
4395: .executeUpdate("insert into table1WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
4396: stmt
4397: .executeUpdate("insert into table2WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
4398: stmt
4399: .executeUpdate("insert into selfReferencingT1 values ('e1', null), ('e2', 'e1'), ('e3', 'e2'), ('e4', 'e3')");
4400: stmt
4401: .executeUpdate("insert into selfReferencingT2 values ('e1', null), ('e2', 'e1'), ('e3', 'e2'), ('e4', 'e3')");
4402:
4403: StringBuffer createSQL = new StringBuffer(
4404: "create table AllDataTypesForTestingTable (");
4405: StringBuffer createTestDataSQL = new StringBuffer(
4406: "create table AllDataTypesNewValuesData (");
4407: for (int type = 0; type < allSQLTypes.length - 1; type++) {
4408: createSQL.append(ColumnNames[type] + " "
4409: + allSQLTypes[type] + ",");
4410: createTestDataSQL.append(ColumnNames[type] + " "
4411: + allSQLTypes[type] + ",");
4412: }
4413: createSQL.append(ColumnNames[allSQLTypes.length - 1] + " "
4414: + allSQLTypes[allSQLTypes.length - 1] + ")");
4415: createTestDataSQL.append(ColumnNames[allSQLTypes.length - 1]
4416: + " " + allSQLTypes[allSQLTypes.length - 1] + ")");
4417: stmt.executeUpdate(createSQL.toString());
4418: stmt.executeUpdate(createTestDataSQL.toString());
4419:
4420: createSQL = new StringBuffer(
4421: "insert into AllDataTypesForTestingTable values(");
4422: createTestDataSQL = new StringBuffer(
4423: "insert into AllDataTypesNewValuesData values(");
4424: for (int type = 0; type < allSQLTypes.length - 1; type++) {
4425: createSQL.append(SQLData[type][0] + ",");
4426: createTestDataSQL.append(SQLData[type][1] + ",");
4427: }
4428: createSQL.append("cast(" + SQLData[allSQLTypes.length - 1][0]
4429: + " as BLOB(1K)))");
4430: createTestDataSQL
4431: .append("cast(" + SQLData[allSQLTypes.length - 1][1]
4432: + " as BLOB(1K)))");
4433: stmt.executeUpdate(createSQL.toString());
4434: stmt.executeUpdate(createTestDataSQL.toString());
4435:
4436: stmt.close();
4437: }
4438:
4439: static void teardown() throws SQLException {
4440: Statement stmt = conn.createStatement();
4441: String[] testObjects = { "table \" t 11 \"", "table \"t1\"",
4442: "trigger tr1", "trigger tr2", "trigger tr3",
4443: "trigger tr4", "view v1", "table s2.t1",
4444: "schema s2 restrict", "table t2", "table t1",
4445: "table t3", "table tableWithConstraint",
4446: "table tableWithPrimaryKey",
4447: "table deleteTriggerInsertIntoThisTable",
4448: "table updateTriggerInsertIntoThisTable",
4449: "table table0WithTriggers", "table table1WithTriggers",
4450: "table table2WithTriggers", "table selfReferencingT1",
4451: "table selfReferencingT2",
4452: "table AllDataTypesForTestingTable",
4453: "table AllDataTypesNewValuesData", "table t4",
4454: "table t5" };
4455: TestUtil.cleanUpTest(stmt, testObjects);
4456: conn.commit();
4457: stmt.close();
4458: }
4459:
4460: public static void showScanStatistics(ResultSet rs, Connection conn) {
4461: Statement s = null;
4462: ResultSet infors = null;
4463:
4464: try {
4465: rs.close(); // need to close to get statistics
4466: s = conn.createStatement();
4467: infors = s
4468: .executeQuery("values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
4469: JDBCDisplayUtil.setMaxDisplayWidth(2000);
4470: JDBCDisplayUtil.DisplayResults(System.out, infors, conn);
4471: infors.close();
4472: } catch (SQLException se) {
4473: System.out.print("FAIL:");
4474: JDBCDisplayUtil.ShowSQLException(System.out, se);
4475: }
4476: }
4477:
4478: static private void dumpSQLExceptions(SQLException se) {
4479: System.out.println("FAIL -- unexpected exception: "
4480: + se.toString());
4481: while (se != null) {
4482: System.out.print("SQLSTATE(" + se.getSQLState() + "):");
4483: se = se.getNextException();
4484: }
4485: }
4486:
4487: static private void dumpExpectedSQLException(SQLException se) {
4488: while (se != null) {
4489: System.out.println("SQL State: " + se.getSQLState());
4490: System.out.println("Got expected exception: "
4491: + se.getMessage());
4492: se = se.getNextException();
4493: }
4494: }
4495:
4496: }
|