0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.resultset
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.jdbcapi;
0023:
0024: import java.sql.CallableStatement;
0025: import java.sql.Connection;
0026: import java.sql.Date;
0027: import java.sql.DriverManager;
0028: import java.sql.ResultSetMetaData;
0029: import java.sql.ResultSet;
0030: import java.sql.PreparedStatement;
0031: import java.sql.Statement;
0032: import java.sql.SQLException;
0033: import java.sql.Time;
0034: import java.sql.Timestamp;
0035: import java.sql.Types;
0036:
0037: import java.lang.reflect.*;
0038:
0039: import org.apache.derby.tools.ij;
0040: import org.apache.derbyTesting.functionTests.util.SecurityCheck;
0041: import org.apache.derbyTesting.functionTests.util.TestUtil;
0042: import org.apache.derbyTesting.functionTests.util.JDBCTestDisplayUtil;
0043: import org.apache.derby.iapi.reference.JDBC30Translation;
0044: import org.apache.derby.iapi.reference.SQLState;
0045: import org.apache.derbyTesting.functionTests.util.BigDecimalHandler;
0046:
0047: /**
0048: * Test of JDBC result set and result set meta-data.
0049: * This program simply calls each of the result set and result set meta-data
0050: * methods, one by one, and prints the results. The test passes if the printed
0051: * results match a previously stored "master". Thus this test cannot actually
0052: * discern whether it passes or not.
0053: *
0054: * Test is only touching on known result set hot-spots at present.
0055:
0056: * Performs SecurityCheck analysis on the JDBC ResultSet and
0057: * ResultSetMetaData objects returned.
0058: *
0059: * @see org.apache.derbyTesting.functionTests.util.SecurityCheck
0060: * @author ames
0061: */
0062:
0063: public class resultset {
0064:
0065: private static Class[] CONN_PARAM = { Integer.TYPE };
0066: private static Object[] CONN_ARG = { new Integer(
0067: JDBC30Translation.CLOSE_CURSORS_AT_COMMIT) };
0068:
0069: static private boolean isDerbyNet = false;
0070:
0071: private static String VALID_DATE_STRING = "'2000-01-01'";
0072: private static String VALID_TIME_STRING = "'15:30:20'";
0073: private static String VALID_TIMESTAMP_STRING = "'2000-01-01 15:30:20'";
0074: private static String NULL_VALUE = "NULL";
0075:
0076: private static String[] SQLTypes = { "SMALLINT", "INTEGER",
0077: "BIGINT", "DECIMAL(10,5)", "REAL", "DOUBLE", "CHAR(60)",
0078: "VARCHAR(60)", "LONG VARCHAR", "CHAR(60) FOR BIT DATA",
0079: "VARCHAR(60) FOR BIT DATA", "LONG VARCHAR FOR BIT DATA",
0080: "CLOB(1k)", "DATE", "TIME", "TIMESTAMP", "BLOB(1k)",
0081:
0082: };
0083:
0084: private static String[] ColumnNames = { "SMALLINTCOL",
0085: "INTEGERCOL", "BIGINTCOL", "DECIMALCOL", "REALCOL",
0086: "DOUBLECOL", "CHARCOL", "VARCHARCOL", "LONGVARCHARCOL",
0087: "CHARFORBITCOL", "VARCHARFORBITCOL", "LVARCHARFORBITCOL",
0088: "CLOBCOL", "DATECOL", "TIMECOL", "TIMESTAMPCOL", "BLOBCOL",
0089:
0090: };
0091:
0092: private static String[][] SQLData = {
0093: { NULL_VALUE, "0", "1", "2" }, // SMALLINT
0094: { NULL_VALUE, "0", "1", "21" }, // INTEGER
0095: { NULL_VALUE, "0", "1", "22" }, // BIGINT
0096: { NULL_VALUE, "0.0", "1.0", "23.0" }, // DECIMAL(10,5)
0097: { NULL_VALUE, "0.0", "1.0", "24.0" }, // REAL,
0098: { NULL_VALUE, "0.0", "1.0", "25.0" }, // DOUBLE
0099: { NULL_VALUE, "'0'", "'aa'", "'2.0'" }, // CHAR(60)
0100: { NULL_VALUE, "'0'", "'aa'", VALID_TIME_STRING }, //VARCHAR(60)",
0101: { NULL_VALUE, "'0'", "'aa'", VALID_TIMESTAMP_STRING }, // LONG VARCHAR
0102: { NULL_VALUE, "X'10aa'", NULL_VALUE, "X'10aaaa'" }, // CHAR(60) FOR BIT DATA
0103: { NULL_VALUE, "X'10aa'", NULL_VALUE, "X'10aaba'" }, // VARCHAR(60) FOR BIT DATA
0104: { NULL_VALUE, "X'10aa'", NULL_VALUE, "X'10aaca'" }, //LONG VARCHAR FOR BIT DATA
0105: { NULL_VALUE, "'13'", "'14'", NULL_VALUE }, //CLOB(1k)
0106: { NULL_VALUE, VALID_DATE_STRING, VALID_DATE_STRING,
0107: NULL_VALUE }, // DATE
0108: { NULL_VALUE, VALID_TIME_STRING, VALID_TIME_STRING,
0109: VALID_TIME_STRING }, // TIME
0110: { NULL_VALUE, VALID_TIMESTAMP_STRING,
0111: VALID_TIMESTAMP_STRING, VALID_TIMESTAMP_STRING }, // TIMESTAMP
0112: { NULL_VALUE, NULL_VALUE, NULL_VALUE, NULL_VALUE } // BLOB
0113: };
0114:
0115: /**
0116: * Hang onto the SecurityCheck class while running the
0117: * tests so that it is not garbage collected during the
0118: * test and lose the information it has collected.
0119: */
0120: private final Object nogc = SecurityCheck.class;
0121:
0122: public static void main(String[] args) throws Throwable {
0123:
0124: isDerbyNet = TestUtil.isNetFramework();
0125:
0126: Connection con;
0127: ResultSetMetaData met;
0128: ResultSet rs;
0129: Statement stmt;
0130: String[] columnNames = { "i", "s", "r", "d", "dt", "t", "ts",
0131: "c", "v", "dc", "bi", "cbd", "vbd", "lvbd", "cl", "bl" };
0132:
0133: System.out.println("Test resultset starting");
0134:
0135: try {
0136: // use the ij utility to read the property file and
0137: // make the initial connection.
0138: ij.getPropertyArg(args);
0139: con = ij.startJBMS();
0140: // Test setCatalog/getCatalog for beetle 5504
0141: con.setCatalog("mycatalog");
0142: String cat = con.getCatalog();
0143: if (cat != null)
0144: System.out
0145: .println("ERROR: getCatalog did not return null");
0146: //Use reflection to set the holdability to false so that the test can run in jdk14 and lower jdks as well
0147: try {
0148: Method sh = con.getClass().getMethod("setHoldability",
0149: CONN_PARAM);
0150: sh.invoke(con, CONN_ARG);
0151: } catch (Exception e) {
0152: System.out.println("shouldn't get that error "
0153: + e.getMessage());
0154: }//for jdks prior to jdk14
0155:
0156: stmt = con.createStatement(
0157: ResultSet.TYPE_SCROLL_INSENSITIVE,
0158: ResultSet.CONCUR_UPDATABLE);
0159:
0160: try {
0161: stmt.execute("drop table t");
0162: } catch (SQLException se) {
0163: } // ignore, assume it is because table does not exist
0164:
0165: stmt
0166: .execute("create table t (i int, s smallint, r real, "
0167: + "d double precision, dt date, t time, ts timestamp, "
0168: + "c char(10), v varchar(40) not null, dc dec(10,2),"
0169: + "bi bigint, cbd char(10) for bit data,"
0170: + "vbd varchar(10) for bit data,lvbd long varchar for bit data,"
0171: + "cl clob(2G), bl blob(1G) )");
0172: stmt
0173: .execute("insert into t values(1,2,3.3,4.4,date('1990-05-05'),"
0174: + "time('12:06:06'),timestamp('1990-07-07 07:07:07.000007'),"
0175: + "'eight','nine', 10.1, 11,"
0176: + TestUtil.stringToHexLiteral("twelv")
0177: + ","
0178: + TestUtil.stringToHexLiteral("3teen")
0179: + ","
0180: + TestUtil.stringToHexLiteral("4teen")
0181: + ", null, null)");
0182:
0183: rs = stmt
0184: .executeQuery("select i, s, r, d, dt, t, ts, c, v, dc, bi, cbd, vbd, lvbd, cl, bl from t");
0185: met = rs.getMetaData();
0186:
0187: int colCount;
0188: System.out.println("getColumnCount(): "
0189: + (colCount = met.getColumnCount()));
0190:
0191: // JDBC columns use 1-based counting
0192: for (int i = 1; i <= colCount; i++) {
0193: System.out.println("isAutoIncrement(" + i + "): "
0194: + met.isAutoIncrement(i));
0195: System.out.println("isCaseSensitive(" + i + "): "
0196: + met.isCaseSensitive(i));
0197: System.out.println("isSearchable(" + i + "): "
0198: + met.isSearchable(i));
0199: System.out.println("isCurrency(" + i + "): "
0200: + met.isCurrency(i));
0201: System.out.println("isNullable(" + i + "): "
0202: + met.isNullable(i));
0203: System.out.println("isSigned(" + i + "): "
0204: + met.isSigned(i));
0205: System.out.println("getColumnDisplaySize(" + i + "): "
0206: + met.getColumnDisplaySize(i));
0207: System.out.println("getColumnLabel(" + i + "): "
0208: + met.getColumnLabel(i));
0209: System.out.println("getColumnName(" + i + "): "
0210: + met.getColumnName(i));
0211: // beetle 5323
0212: System.out.println("getTableName(" + i + "): "
0213: + met.getTableName(i));
0214: System.out.println("getSchemaName(" + i + "): "
0215: + met.getSchemaName(i));
0216: System.out.println("getCatalogName(" + i + "): "
0217: + met.getCatalogName(i));
0218: System.out.println("getColumnType(" + i + "): "
0219: + met.getColumnType(i));
0220: System.out.println("getPrecision(" + i + "): "
0221: + met.getPrecision(i));
0222: System.out.println("getScale(" + i + "): "
0223: + met.getScale(i));
0224: System.out.println("getColumnTypeName(" + i + "): "
0225: + met.getColumnTypeName(i));
0226: System.out.println("isReadOnly(" + i + "): "
0227: + met.isReadOnly(i));
0228: boolean writable = met.isWritable(i);
0229: // JCC & Embedded driver support updatable resultsets so isWritable is true
0230: if (writable == true)
0231: System.out.println("isWritable(" + i
0232: + "): Expected isWritable value");
0233: System.out.println("isDefinitelyWritable(" + i + "): "
0234: + met.isDefinitelyWritable(i));
0235: }
0236:
0237: /* Try the various get methods on each column */
0238: while (rs.next()) {
0239: // JDBC columns use 1-based counting
0240: for (int i = 1; i <= colCount; i++) {
0241: try {
0242: System.out.println("getBigDecimal("
0243: + i
0244: + ",1): "
0245: + BigDecimalHandler
0246: .getBigDecimalString(rs, i));
0247: } catch (Throwable e) {
0248: System.out.println("getBigDecimal(" + i
0249: + ",1) got exception ");
0250: if (e instanceof SQLException)
0251: JDBCTestDisplayUtil.ShowCommonSQLException(
0252: System.out, (SQLException) e);
0253: }
0254:
0255: try {
0256: if (isDerbyNet)
0257: System.out
0258: .println("beetle 5328 - JCC returns incorrect scale for getBigDecimal(String,int)");
0259: System.out.println("getBigDecimal("
0260: + columnNames[i - 1]
0261: + ",1): "
0262: + BigDecimalHandler
0263: .getBigDecimalString(rs,
0264: columnNames[i - 1], i));
0265: } catch (Throwable e) {
0266: System.out.println("getBigDecimal("
0267: + columnNames[i - 1]
0268: + ",1) got exception ");
0269: if (e instanceof SQLException)
0270: JDBCTestDisplayUtil.ShowCommonSQLException(
0271: System.out, (SQLException) e);
0272: }
0273:
0274: try {
0275: System.out.println("getBoolean(" + i + "): "
0276: + rs.getBoolean(i));
0277: } catch (Throwable e) {
0278: System.out.println("getBoolean(" + i
0279: + ") got exception ");
0280: if (e instanceof SQLException)
0281: JDBCTestDisplayUtil.ShowCommonSQLException(
0282: System.out, (SQLException) e);
0283: }
0284:
0285: try {
0286: System.out.println("getBoolean("
0287: + columnNames[i - 1] + "): "
0288: + rs.getBoolean(columnNames[i - 1]));
0289: } catch (Throwable e) {
0290: System.out.println("getBoolean("
0291: + columnNames[i - 1]
0292: + ") got exception ");
0293: if (e instanceof SQLException)
0294: JDBCTestDisplayUtil.ShowCommonSQLException(
0295: System.out, (SQLException) e);
0296: }
0297:
0298: try {
0299: System.out.println("getByte(" + i + "): "
0300: + rs.getByte(i));
0301: } catch (Throwable e) {
0302: System.out.println("getByte(" + i
0303: + ") got exception ");
0304: if (e instanceof SQLException)
0305: JDBCTestDisplayUtil.ShowCommonSQLException(
0306: System.out, (SQLException) e);
0307: }
0308:
0309: try {
0310: System.out.println("getByte("
0311: + columnNames[i - 1] + "): "
0312: + rs.getByte(columnNames[i - 1]));
0313: } catch (Throwable e) {
0314: System.out.println("getByte("
0315: + columnNames[i - 1]
0316: + ") got exception ");
0317: if (e instanceof SQLException)
0318: JDBCTestDisplayUtil.ShowCommonSQLException(
0319: System.out, (SQLException) e);
0320: }
0321:
0322: try {
0323: System.out.println("getBytes(" + i + "): "
0324: + showBytes(rs.getBytes(i)));
0325: } catch (SQLException e) {
0326: System.out.println("getBytes(" + i
0327: + ") got exception ");
0328: JDBCTestDisplayUtil.ShowCommonSQLException(
0329: System.out, (SQLException) e);
0330: }
0331:
0332: try {
0333: System.out.println("getBytes("
0334: + columnNames[i - 1]
0335: + "): "
0336: + showBytes(rs
0337: .getBytes(columnNames[i - 1])));
0338: } catch (SQLException e) {
0339: System.out.println("getBytes("
0340: + columnNames[i - 1]
0341: + ") got exception ");
0342: JDBCTestDisplayUtil.ShowCommonSQLException(
0343: System.out, (SQLException) e);
0344: }
0345:
0346: try {
0347: System.out.println("getDate(" + i + "): "
0348: + rs.getDate(i));
0349: } catch (SQLException e) {
0350: System.out.println("getDate(" + i
0351: + ") got exception ");
0352: JDBCTestDisplayUtil.ShowCommonSQLException(
0353: System.out, (SQLException) e);
0354: }
0355:
0356: try {
0357: System.out.println("getDate("
0358: + columnNames[i - 1] + "): "
0359: + rs.getDate(columnNames[i - 1]));
0360: } catch (SQLException e) {
0361: System.out.println("getDate("
0362: + columnNames[i - 1]
0363: + ") got exception ");
0364: JDBCTestDisplayUtil.ShowCommonSQLException(
0365: System.out, (SQLException) e);
0366: }
0367:
0368: try {
0369: System.out.println("getDouble(" + i + "): "
0370: + rs.getDouble(i));
0371: } catch (Throwable e) {
0372: System.out.println("getDouble(" + i
0373: + ") got exception ");
0374: if (e instanceof SQLException)
0375: JDBCTestDisplayUtil.ShowCommonSQLException(
0376: System.out, (SQLException) e);
0377: }
0378:
0379: try {
0380: System.out.println("getDouble("
0381: + columnNames[i - 1] + "): "
0382: + rs.getDouble(columnNames[i - 1]));
0383: } catch (Throwable e) {
0384: System.out.println("getDouble("
0385: + columnNames[i - 1]
0386: + ") got exception ");
0387: if (e instanceof SQLException)
0388: JDBCTestDisplayUtil.ShowCommonSQLException(
0389: System.out, (SQLException) e);
0390: }
0391:
0392: try {
0393: System.out.println("getFloat(" + i + "): "
0394: + rs.getFloat(i));
0395: } catch (Throwable e) {
0396: System.out.println("getFloat(" + i
0397: + ") got exception ");
0398: if (e instanceof SQLException)
0399: JDBCTestDisplayUtil.ShowCommonSQLException(
0400: System.out, (SQLException) e);
0401: }
0402:
0403: try {
0404: System.out.println("getFloat("
0405: + columnNames[i - 1] + "): "
0406: + rs.getFloat(columnNames[i - 1]));
0407: } catch (Throwable e) {
0408: System.out.println("getFloat("
0409: + columnNames[i - 1]
0410: + ") got exception ");
0411: if (e instanceof SQLException)
0412: JDBCTestDisplayUtil.ShowCommonSQLException(
0413: System.out, (SQLException) e);
0414: }
0415:
0416: try {
0417: System.out.println("getInt(" + i + "): "
0418: + rs.getInt(i));
0419: } catch (Throwable e) {
0420: System.out.println("getInt(" + i
0421: + ") got exception ");
0422: if (e instanceof SQLException)
0423: JDBCTestDisplayUtil.ShowCommonSQLException(
0424: System.out, (SQLException) e);
0425: }
0426:
0427: try {
0428: System.out.println("getInt("
0429: + columnNames[i - 1] + "): "
0430: + rs.getInt(columnNames[i - 1]));
0431: } catch (Throwable e) {
0432: System.out.println("getInt("
0433: + columnNames[i - 1]
0434: + ") got exception ");
0435: if (e instanceof SQLException)
0436: JDBCTestDisplayUtil.ShowCommonSQLException(
0437: System.out, (SQLException) e);
0438: }
0439:
0440: try {
0441: System.out.println("getLong(" + i + "): "
0442: + rs.getLong(i));
0443: } catch (Throwable e) {
0444: System.out.println("getLong(" + i
0445: + ") got exception ");
0446: if (e instanceof SQLException)
0447: JDBCTestDisplayUtil.ShowCommonSQLException(
0448: System.out, (SQLException) e);
0449: }
0450:
0451: try {
0452: System.out.println("getLong("
0453: + columnNames[i - 1] + "): "
0454: + rs.getLong(columnNames[i - 1]));
0455: } catch (Throwable e) {
0456: System.out.println("getLong("
0457: + columnNames[i - 1]
0458: + ") got exception ");
0459: if (e instanceof SQLException)
0460: JDBCTestDisplayUtil.ShowCommonSQLException(
0461: System.out, (SQLException) e);
0462: }
0463:
0464: try {
0465: // with the bit datatypes the string output is not the same for every run,
0466: // so we need to mask that to prevent false test failures
0467: // this does not test the values returned, just whether it gives an exception.
0468: if (i > 11) {
0469: BigDecimalHandler.getObjectString(rs, i);
0470: System.out.println("getObject(" + i
0471: + ") is ok");
0472: } else
0473: System.out.println("getObject("
0474: + i
0475: + "): "
0476: + BigDecimalHandler
0477: .getObjectString(rs, i));
0478: } catch (SQLException e) {
0479: System.out.println("getObject(" + i
0480: + ") got exception ");
0481: JDBCTestDisplayUtil.ShowCommonSQLException(
0482: System.out, (SQLException) e);
0483: }
0484:
0485: try {
0486: // with the bit datatypes the string output is not the same for every run,
0487: // so we need to mask that to prevent false test failures
0488: // this does not test the values returned, just whether it gives an exception.
0489: if (i > 11) {
0490: BigDecimalHandler.getObjectString(rs,
0491: columnNames[i - 1], i);
0492: System.out.println("getObject("
0493: + columnNames[i - 1] + ") is ok ");
0494: } else
0495: System.out.println("getObject("
0496: + columnNames[i - 1]
0497: + "): "
0498: + BigDecimalHandler
0499: .getObjectString(rs,
0500: columnNames[i - 1],
0501: i));
0502: } catch (SQLException e) {
0503: System.out.println("getObject("
0504: + columnNames[i - 1]
0505: + ") got exception ");
0506: JDBCTestDisplayUtil.ShowCommonSQLException(
0507: System.out, (SQLException) e);
0508: }
0509:
0510: try {
0511: System.out.println("getShort(" + i + "): "
0512: + rs.getShort(i));
0513: } catch (Throwable e) {
0514: System.out.println("getShort(" + i
0515: + ") got exception ");
0516: if (e instanceof SQLException)
0517: JDBCTestDisplayUtil.ShowCommonSQLException(
0518: System.out, (SQLException) e);
0519: }
0520:
0521: try {
0522: System.out.println("getShort("
0523: + columnNames[i - 1] + "): "
0524: + rs.getShort(columnNames[i - 1]));
0525: } catch (Throwable e) {
0526: System.out.println("getShort("
0527: + columnNames[i - 1]
0528: + ") got exception ");
0529: if (e instanceof SQLException)
0530: JDBCTestDisplayUtil.ShowCommonSQLException(
0531: System.out, (SQLException) e);
0532: }
0533:
0534: try {
0535: System.out.println("getString(" + i + "): "
0536: + rs.getString(i));
0537: } catch (SQLException e) {
0538: System.out.println("getString(" + i
0539: + ") got exception ");
0540: JDBCTestDisplayUtil.ShowCommonSQLException(
0541: System.out, (SQLException) e);
0542: }
0543:
0544: try {
0545: System.out.println("getString("
0546: + columnNames[i - 1] + "): "
0547: + rs.getString(columnNames[i - 1]));
0548: } catch (SQLException e) {
0549: System.out.println("getString("
0550: + columnNames[i - 1]
0551: + ") got exception ");
0552: JDBCTestDisplayUtil.ShowCommonSQLException(
0553: System.out, (SQLException) e);
0554: }
0555:
0556: try {
0557: System.out.println("getTime(" + i + "): "
0558: + rs.getTime(i));
0559: } catch (SQLException e) {
0560: System.out.println("getTime(" + i
0561: + ") got exception ");
0562: JDBCTestDisplayUtil.ShowCommonSQLException(
0563: System.out, (SQLException) e);
0564: }
0565:
0566: try {
0567: System.out.println("getTime("
0568: + columnNames[i - 1] + "): "
0569: + rs.getTime(columnNames[i - 1]));
0570: } catch (SQLException e) {
0571: System.out.println("getTime("
0572: + columnNames[i - 1]
0573: + ") got exception ");
0574: JDBCTestDisplayUtil.ShowCommonSQLException(
0575: System.out, (SQLException) e);
0576: }
0577:
0578: try {
0579: System.out.println("getTimestamp(" + i + "): "
0580: + rs.getTimestamp(i));
0581: } catch (SQLException e) {
0582: System.out.println("getTimestamp(" + i
0583: + ") got exception ");
0584: JDBCTestDisplayUtil.ShowCommonSQLException(
0585: System.out, (SQLException) e);
0586: }
0587:
0588: try {
0589: System.out.println("getTimestamp("
0590: + columnNames[i - 1] + "): "
0591: + rs.getTimestamp(columnNames[i - 1]));
0592: } catch (SQLException e) {
0593: System.out.println("getTimestamp("
0594: + columnNames[i - 1]
0595: + ") got exception ");
0596: JDBCTestDisplayUtil.ShowCommonSQLException(
0597: System.out, (SQLException) e);
0598: }
0599: }
0600: }
0601:
0602: rs.close();
0603:
0604: // Try getting a row from the closed result set
0605: try {
0606: rs.next();
0607: System.out
0608: .println("FAIL - rs.next() allowed on closed result set.");
0609: } catch (SQLException e) {
0610: System.out
0611: .println("rs.next() on closed result set got exception ");
0612: JDBCTestDisplayUtil.ShowCommonSQLException(System.out,
0613: e);
0614: } catch (Throwable e) {
0615: System.out
0616: .println("rs.next() didn't fail with SQLException as "
0617: + "expected on closed result set. Got Throwable instead: "
0618: + e);
0619: }
0620:
0621: // Ensure commit or rollback in auto commit actually does something
0622: stmt.executeUpdate("create table bug4810(i int, b int)");
0623: stmt
0624: .executeUpdate("insert into bug4810 values (1,1), (1,2), (1,3), (1,4)");
0625: stmt
0626: .executeUpdate("insert into bug4810 values (1,1), (1,2), (1,3), (1,4)");
0627: con.commit();
0628: con.setAutoCommit(true);
0629: con
0630: .setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
0631: System.out.println("just auto commit");
0632: showLocksForAutoCommitSelect(con, stmt, 0);
0633: System.out.println("commit with auto commit");
0634: showLocksForAutoCommitSelect(con, stmt, 1);
0635: System.out.println("rollback with auto commit");
0636: showLocksForAutoCommitSelect(con, stmt, 2);
0637:
0638: stmt.execute("drop table bug4810");
0639: con.commit();
0640: stmt.close();
0641:
0642: testMutableValues(con);
0643: testCorrelationNamesAndMetaDataCalls(con);
0644: testNullIfAndMetaDataCalls(con);
0645: testFloatMAX_VALUE(con);
0646: //We know that JCC behavior does not match
0647: //DerbyNetClient or embedded
0648: if (!TestUtil.isJCCFramework()) {
0649: runAutoCommitTests(con);
0650: }
0651: // final clean up
0652: cleanUp(con);
0653: con.close();
0654:
0655: } catch (SQLException e) {
0656: System.out.println("FAIL -- unexpected exception: "
0657: + e.toString());
0658: dumpSQLExceptions(e);
0659: e.printStackTrace();
0660: } catch (Throwable e) {
0661: System.out.println("FAIL -- unexpected exception: " + e);
0662: e.printStackTrace();
0663: }
0664:
0665: // Print a report on System.out of the issues
0666: // found with the security checks.
0667: SecurityCheck.report();
0668: System.out.println("Test resultset finished");
0669: }
0670:
0671: //test NULLIF(L,R) with and without parameter and check the return value's data type
0672: static private void testNullIfAndMetaDataCalls(Connection conn)
0673: throws Throwable {
0674: System.out
0675: .println("Tests to check metadata information of nullif column");
0676: tablesForTestingAllDatatypesCombinations(conn);
0677: testAllDatatypesCombinations(conn);
0678: testParameterForFirstOperandToNullIf(conn);
0679: }
0680:
0681: public static void testParameterForFirstOperandToNullIf(
0682: Connection conn) throws Throwable {
0683: System.out
0684: .println("Start testing first operand as parameter to nullif");
0685: PreparedStatement ps;
0686: for (int secondColumnType = 0; secondColumnType < SQLTypes.length; secondColumnType++) {
0687: System.out.println("Testing nullif(?,"
0688: + SQLTypes[secondColumnType] + ")");
0689: String nullIfString = new String("SELECT NULLIF(?,"
0690: + ColumnNames[secondColumnType]
0691: + ") from AllDataTypesTable");
0692: try {
0693: ps = conn.prepareStatement(nullIfString);
0694: switch (secondColumnType) {
0695: case 0:
0696: case 1:
0697: case 2:
0698: case 3:
0699: case 4:
0700: case 5:
0701: case 6:
0702: case 7:
0703: System.out.println("Testing nullif(?,"
0704: + SQLTypes[secondColumnType]
0705: + ") with setBoolean");
0706: ps.setBoolean(1, true);
0707: break;
0708: case 8: //'LONG VARCHAR'
0709: case 11: //'LONG VARCHAR FOR BIT DATA'
0710: case 12: //'CLOB'
0711: case 16: //'BLOB'
0712: //Take specific case of LONG VARCHAR. Prepare of nullif(?,long varchar)
0713: //fails early on because at bind time, Derby tries to set ? to
0714: //long varchar. But comparison between 2 long varchars is not
0715: //supported and hence bind code in BinaryComparisonOperatorNode fails
0716: //Similar thing happens for CLOB, BLOB and LONG VARCHAR FOR BIT DATA
0717: case 9:
0718: case 10:
0719: System.out.println("Testing nullif(?,"
0720: + SQLTypes[secondColumnType]
0721: + ") with setBinaryStream");
0722: ps
0723: .setBinaryStream(1,
0724: (java.io.InputStream) null, 1);
0725: break;
0726: case 13://DATE
0727: System.out.println("Testing nullif(?,"
0728: + SQLTypes[secondColumnType]
0729: + ") with setDate");
0730: ps.setDate(1, Date.valueOf("2000-01-01"));
0731: break;
0732: case 14://TIME
0733: System.out.println("Testing nullif(?,"
0734: + SQLTypes[secondColumnType]
0735: + ") with setTime");
0736: ps.setTime(1, Time.valueOf("15:30:20"));
0737: break;
0738: case 15://TIMESTAMP
0739: System.out.println("Testing nullif(?,"
0740: + SQLTypes[secondColumnType]
0741: + ") with setTimestamp");
0742: ps.setTimestamp(1, Timestamp
0743: .valueOf("2000-01-01 15:30:20"));
0744: break;
0745: default:
0746: break;
0747: }
0748: dumpRS(ps.executeQuery());
0749: } catch (SQLException e) {
0750: dumpSQLExceptions(e);
0751: }
0752: }
0753: }
0754:
0755: public static void testAllDatatypesCombinations(Connection conn)
0756: throws Throwable {
0757: System.out
0758: .println("Start testing all datatypes combinations in NULLIF function");
0759: Statement s = conn.createStatement();
0760: for (int firstColumnType = 0; firstColumnType < SQLTypes.length; firstColumnType++) {
0761: StringBuffer nullIfString = new StringBuffer(
0762: "SELECT NULLIF(" + ColumnNames[firstColumnType]);
0763: for (int secondColumnType = 0; secondColumnType < SQLTypes.length; secondColumnType++) {
0764: try {
0765: StringBuffer completeNullIfString = new StringBuffer(
0766: nullIfString.toString() + ","
0767: + ColumnNames[secondColumnType]);
0768: System.out.println(completeNullIfString
0769: + ") from AllDataTypesTable");
0770: dumpRS(s.executeQuery(completeNullIfString
0771: + ") from AllDataTypesTable"));
0772: } catch (SQLException e) {
0773: dumpSQLExceptions(e);
0774: }
0775: }
0776: }
0777: }
0778:
0779: public static void tablesForTestingAllDatatypesCombinations(
0780: Connection conn) throws Throwable {
0781: System.out
0782: .println("Set up by creating table for testing all datatypes combinations");
0783:
0784: Statement s = conn.createStatement();
0785:
0786: try {
0787: s.executeUpdate("DROP TABLE AllDataTypesTable");
0788: } catch (SQLException se) {
0789: }
0790:
0791: StringBuffer createSQL = new StringBuffer(
0792: "create table AllDataTypesTable (");
0793: for (int type = 0; type < SQLTypes.length - 1; type++) {
0794: createSQL.append(ColumnNames[type] + " " + SQLTypes[type]
0795: + ",");
0796: }
0797: createSQL.append(ColumnNames[SQLTypes.length - 1] + " "
0798: + SQLTypes[SQLTypes.length - 1] + ")");
0799: System.out.println(createSQL);
0800: s.executeUpdate(createSQL.toString());
0801:
0802: for (int row = 0; row < SQLData[0].length; row++) {
0803: createSQL = new StringBuffer(
0804: "insert into AllDataTypesTable values(");
0805: for (int type = 0; type < SQLTypes.length - 1; type++) {
0806: createSQL.append(SQLData[type][row] + ",");
0807: }
0808: createSQL.append(SQLData[SQLTypes.length - 1][row] + ")");
0809: System.out.println(createSQL);
0810: s.executeUpdate(createSQL.toString());
0811: }
0812:
0813: s.close();
0814: conn.commit();
0815: }
0816:
0817: public static void dumpRS(ResultSet s) throws SQLException {
0818: SecurityCheck.inspect(s, "java.sql.ResultSet");
0819:
0820: if (s == null) {
0821: System.out.println("<NULL>");
0822: return;
0823: }
0824:
0825: // This might look strange, but we want to know that the first
0826: // call to next() is successful before we get meta data and
0827: // display column headings. If a driver is pre-fetching data,
0828: // an exception might be thrown in Statement.executeQuery(),
0829: // but a driver without pre-fetching will not get the
0830: // exception until ResultSet.next() is called (hence column
0831: // headings are printed). With this early call to next(), we
0832: // get the same output regardless of pre-fetching.
0833: boolean hasData = s.next();
0834:
0835: ResultSetMetaData rsmd = s.getMetaData();
0836: SecurityCheck.inspect(rsmd, "java.sql.ResultSetMetaData");
0837:
0838: // Get the number of columns in the result set
0839: int numCols = rsmd.getColumnCount();
0840:
0841: if (numCols <= 0) {
0842: System.out.println("(no columns!)");
0843: return;
0844: }
0845:
0846: StringBuffer heading = new StringBuffer("\t ");
0847: StringBuffer underline = new StringBuffer("\t ");
0848:
0849: int len;
0850: // Display column headings
0851: for (int i = 1; i <= numCols; i++) {
0852: if (i > 1) {
0853: heading.append(",");
0854: underline.append(" ");
0855: }
0856: len = heading.length();
0857: heading.append("COL" + i);
0858: heading.append("(datatype : " + rsmd.getColumnTypeName(i));
0859: heading.append(", precision : " + rsmd.getPrecision(i));
0860: heading.append(", scale : " + rsmd.getScale(i) + ")");
0861: len = heading.length() - len;
0862: for (int j = len; j > 0; j--) {
0863: underline.append("-");
0864: }
0865: }
0866: System.out.println(heading.toString());
0867: System.out.println(underline.toString());
0868:
0869: StringBuffer row = new StringBuffer();
0870: // Display data, fetching until end of the result set
0871: while (hasData) {
0872: row.append("\t{");
0873: // Loop through each column, getting the
0874: // column data and displaying
0875: for (int i = 1; i <= numCols; i++) {
0876: if (i > 1)
0877: row.append(",");
0878: try {
0879: row.append(s.getString(i));
0880: } catch (SQLException ex) {
0881: if (ex.getSQLState().equals("22005"))
0882: row.append("Invalid Conversion Error\n");
0883: else
0884: throw ex;
0885: }
0886: }
0887: row.append("}\n");
0888: hasData = s.next();
0889: }
0890: System.out.println(row.toString());
0891: s.close();
0892: }
0893:
0894: static private void testCorrelationNamesAndMetaDataCalls(
0895: Connection conn) throws Exception {
0896: Statement stmt = conn.createStatement();
0897: stmt
0898: .executeUpdate("create table s (a int, b int, c int, d int, e int, f int)");
0899: stmt.executeUpdate("insert into s values (0,1,2,3,4,5)");
0900: stmt.executeUpdate("insert into s values (10,11,12,13,14,15)");
0901: System.out
0902: .println("Run select * from s ss (f, e, d, c, b, a) where f = 0 and then try getTableName and getSchemaName on columns");
0903: ResultSet rs = stmt
0904: .executeQuery("select * from s ss (f, e, d, c, b, a) where f = 0");
0905: rs.next();
0906: ResultSetMetaData met = rs.getMetaData();
0907: System.out.println("getTableName(1): " + met.getTableName(1));
0908: System.out.println("getSchemaName(1): " + met.getSchemaName(1));
0909:
0910: System.out
0911: .println("Run select * from (select * from s) a and then try getTableName and getSchemaName on columns");
0912: rs = stmt.executeQuery("select * from (select * from s) a");
0913: rs.next();
0914: met = rs.getMetaData();
0915: System.out.println("getTableName(1): " + met.getTableName(1));
0916: System.out.println("getSchemaName(1): " + met.getSchemaName(1));
0917:
0918: stmt.executeUpdate("create schema s1");
0919: stmt.executeUpdate("create table s1.t1 (c11 int, c12 int)");
0920: stmt
0921: .executeUpdate("insert into s1.t1 values (11, 12), (21, 22)");
0922: System.out
0923: .println("Run select * from s1.t1 as abc and then try getTableName and getSchemaName on columns");
0924: rs = stmt.executeQuery("select * from s1.t1 as abc");
0925: met = rs.getMetaData();
0926: System.out.println("Table name of first column is "
0927: + met.getTableName(1));
0928: System.out.println("Schema name of first column is "
0929: + met.getSchemaName(1));
0930: System.out.println("Table name of second column is "
0931: + met.getTableName(2));
0932: System.out.println("Schema name of second column is "
0933: + met.getSchemaName(2));
0934: System.out
0935: .println("Run select abc.c11 from s1.t1 as abc and then try getTableName and getSchemaName on columns");
0936: rs = stmt.executeQuery("select abc.c11 from s1.t1 as abc");
0937: met = rs.getMetaData();
0938: System.out.println("Table name of first column is "
0939: + met.getTableName(1));
0940: System.out.println("Schema name of first column is "
0941: + met.getSchemaName(1));
0942: System.out
0943: .println("Run select bcd.a, abc.c11 from s1.t1 as abc, s as bcd and then try getTableName and getSchemaName on columns");
0944: rs = stmt
0945: .executeQuery("select bcd.a, abc.c11 from s1.t1 as abc, s as bcd");
0946: met = rs.getMetaData();
0947: System.out.println("Table name of first column is "
0948: + met.getTableName(1));
0949: System.out.println("Schema name of first column is "
0950: + met.getSchemaName(1));
0951: System.out.println("Table name of second column is "
0952: + met.getTableName(2));
0953: System.out.println("Schema name of second column is "
0954: + met.getSchemaName(2));
0955:
0956: stmt.executeUpdate("create schema app1");
0957: stmt.executeUpdate("create table app1.t1 (c11 int, c12 int)");
0958: stmt
0959: .executeUpdate("insert into app1.t1 values (11, 12), (21, 22)");
0960: stmt.executeUpdate("create schema app2");
0961: stmt.executeUpdate("create table app2.t1 (c11 int, c12 int)");
0962: stmt
0963: .executeUpdate("insert into app2.t1 values (11, 12), (21, 22)");
0964: System.out
0965: .println("Run select app1.t1.c11, app2.t1.c11 from app1.t1, app2.t1 and then try getTableName and getSchemaName on columns");
0966: rs = stmt
0967: .executeQuery("select app1.t1.c11, app2.t1.c11 from app1.t1, app2.t1");
0968: met = rs.getMetaData();
0969: System.out.println("Table name of first column is "
0970: + met.getTableName(1));
0971: System.out.println("Schema name of first column is "
0972: + met.getSchemaName(1));
0973: System.out.println("Table name of second column is "
0974: + met.getTableName(2));
0975: System.out.println("Schema name of second column is "
0976: + met.getSchemaName(2));
0977: stmt.execute("drop table s");
0978: stmt.execute("drop table s1.t1");
0979: stmt.execute("drop schema s1 restrict");
0980: stmt.execute("drop table app1.t1");
0981: stmt.execute("drop table app2.t1");
0982: stmt.execute("drop schema app2 restrict");
0983: stmt.execute("drop schema app1 restrict");
0984: }
0985:
0986: static private void doTheTests() throws Exception {
0987:
0988: }
0989:
0990: static private void showLocksForAutoCommitSelect(Connection conn,
0991: Statement stmt, int action) throws Exception {
0992:
0993: ResultSet rs = stmt.executeQuery("select i,b from bug4810");
0994: rs.next();
0995: System.out.println(" bug4810 " + rs.getInt(1) + ", "
0996: + rs.getInt(2));
0997: rs.next();
0998: System.out.println(" bug4810 " + rs.getInt(1) + ", "
0999: + rs.getInt(2));
1000:
1001: if (action == 1) {
1002: System.out.println("commit");
1003: conn.commit();
1004: } else if (action == 2) {
1005: System.out.println("rollback");
1006: conn.rollback();
1007: }
1008:
1009: showLocks();
1010:
1011: try {
1012:
1013: rs.next();
1014: System.out.println(" bug4810 " + rs.getInt(1) + ", "
1015: + rs.getInt(2));
1016: } catch (SQLException sqle) {
1017: JDBCTestDisplayUtil
1018: .ShowCommonSQLException(System.out, sqle);
1019: }
1020: showLocks();
1021: rs.close();
1022:
1023: showLocks();
1024:
1025: }
1026:
1027: private static void showLocks() throws Exception {
1028: System.out.println(" LOCK TABLE");
1029: Connection con2 = ij.startJBMS();
1030: PreparedStatement ps2 = con2
1031: .prepareStatement("select XID, count(*) from SYSCS_DIAG.LOCK_TABLE as L group by XID");
1032: ResultSet rs2 = ps2.executeQuery();
1033:
1034: while (rs2.next()) {
1035: if (rs2.getInt(2) > 0) {
1036: System.out.println("Locks are held");
1037: } else if (rs2.getInt(2) == 0) {
1038: System.out.println("No locks to hold");
1039: }
1040: }
1041:
1042: rs2.close();
1043: ps2.close();
1044: con2.close();
1045: }
1046:
1047: static private void dumpSQLExceptions(SQLException se) {
1048: while (se != null) {
1049: JDBCTestDisplayUtil.ShowCommonSQLException(System.out, se);
1050: se = se.getNextException();
1051: }
1052: }
1053:
1054: static private String showBytes(byte[] bytes) {
1055: if (bytes == null)
1056: return "null";
1057:
1058: StringBuffer s = new StringBuffer("0x");
1059: s.ensureCapacity(2 + 2 * bytes.length);
1060: for (int i = 0; i < bytes.length; i++) {
1061: int hi = (bytes[i] & 0xf0) >>> 4;
1062: int lo = (bytes[i] & 0x0f);
1063: s.append(representation[hi]);
1064: s.append(representation[lo]);
1065: }
1066: return s.toString();
1067: }
1068:
1069: static final char[] representation = { '0', '1', '2', '3', '4',
1070: '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' };
1071:
1072: /**
1073: Test that for mutable types returned from a ResultSet we do not
1074: re-use the type, thus conusing any application that holds onto
1075: the returned value. Possible mutable types are
1076:
1077: byte[]
1078: java.sql.Date
1079: java.sql.Timestamp
1080: java.sql.Time
1081:
1082: The stream types are mutable but they are closed once the appliction
1083: moves to the next column or row.
1084: */
1085: private static void testMutableValues(Connection conn)
1086: throws SQLException {
1087: System.out.println("START testMutableValues");
1088:
1089: Statement s = conn.createStatement();
1090:
1091: s
1092: .execute("CREATE TABLE MUTABLE.T1(C CHAR(10) FOR BIT DATA, V VARCHAR(10) FOR BIT DATA, L LONG VARCHAR FOR BIT DATA, D DATE, T TIME, TS TIMESTAMP)");
1093: s
1094: .execute("INSERT INTO MUTABLE.T1 VALUES (X'34', X'4de5', X'5e3a67', '1992-01-01', '17.05.00', '2003-3-1-17.05.43.123456')");
1095: s
1096: .execute("INSERT INTO MUTABLE.T1 VALUES (X'93', X'4825', X'6e3a64', '1992-01-03', '17.06.00', '2007-3-1-17.05.43.123456')");
1097: s
1098: .execute("INSERT INTO MUTABLE.T1 VALUES (X'34', X'4de5', X'5e3a67', '1992-01-01', '17.05.00', '2003-3-1-17.05.43.123456')");
1099:
1100: {
1101: ResultSet rs = s
1102: .executeQuery("SELECT C,V,L,D,T,TS FROM MUTABLE.T1");
1103: java.util.ArrayList[] values = new java.util.ArrayList[6];
1104: for (int i = 0; i < values.length; i++) {
1105: values[i] = new java.util.ArrayList();
1106: }
1107: System.out.println("CHECKING on getXXX()");
1108: int rc = 0;
1109: while (rs.next()) {
1110: rc++;
1111: System.out.println("ROW " + rc);
1112:
1113: checkMutableValue(values[0], 1, rs.getBytes(1));
1114: checkMutableValue(values[1], 2, rs.getBytes(2));
1115: checkMutableValue(values[2], 3, rs.getBytes(3));
1116:
1117: checkMutableValue(values[3], 4, rs.getDate(4));
1118: checkMutableValue(values[4], 5, rs.getTime(5));
1119: checkMutableValue(values[5], 6, rs.getTimestamp(6));
1120:
1121: }
1122: rs.close();
1123: }
1124: {
1125: ResultSet rs = s
1126: .executeQuery("SELECT C,V,L,D,T,TS FROM MUTABLE.T1");
1127: java.util.ArrayList[] values = new java.util.ArrayList[6];
1128: for (int i = 0; i < values.length; i++) {
1129: values[i] = new java.util.ArrayList();
1130: }
1131: System.out.println("CHECKING on getObject()");
1132: int rc = 0;
1133: while (rs.next()) {
1134: rc++;
1135: System.out.println("ROW " + rc);
1136:
1137: for (int i = 0; i < 6; i++)
1138: checkMutableValue(values[i], i + 1, rs
1139: .getObject(i + 1));
1140: }
1141: rs.close();
1142: }
1143:
1144: s.execute("DROP TABLE MUTABLE.T1");
1145:
1146: System.out.println("COMPLETE testMutableValues");
1147: }
1148:
1149: private static void checkMutableValue(java.util.ArrayList list,
1150: int col, Object value) {
1151:
1152: int same = -1;
1153: int equals = -1;
1154: for (int i = 0; i < list.size(); i++) {
1155: Object previous = list.get(i);
1156: if (previous == value)
1157: same = i + 1;
1158: if (previous.equals(value))
1159: equals = i + 1;
1160: }
1161:
1162: if (same != -1)
1163: System.out.println("FAIL SAME OBJECT RETURNED column "
1164: + col + " existing " + same);
1165: if (equals != -1)
1166: System.out.println("OK EQUALITY OBJECT RETURNED column "
1167: + col + " existing " + equals);
1168:
1169: list.add(value);
1170: }
1171:
1172: // JIRA-1136: LossOfPrecisionConversionException fetching Float.MAX_VALUE.
1173: // This test proves that we can successfully fetch that value from the DB
1174: // Note that we still fail to fetch that value in the JCC driver, so we
1175: // don't try this test under that framework, since it would fail, and we
1176: // share masters for these tests.
1177: private static void testFloatMAX_VALUE(Connection conn)
1178: throws SQLException {
1179: Statement stmt = conn.createStatement();
1180: try {
1181: stmt.execute("drop table jira1136");
1182: } catch (Throwable t) {
1183: }
1184: stmt.execute("create table jira1136 (f float)");
1185: stmt.execute("insert into jira1136 values (3.4028235E38)");
1186: PreparedStatement ps = conn
1187: .prepareStatement("select * from jira1136");
1188: ResultSet rs = ps.executeQuery();
1189: while (rs.next()) {
1190: if (!TestUtil.isJCCFramework())
1191: System.out.println("DERBY-1136 fetched: "
1192: + rs.getFloat(1));
1193: else
1194: // lie about the result so we can share the master files.
1195: System.out.println("DERBY-1136 fetched: 3.4028235E38");
1196: }
1197: }
1198:
1199: /**
1200: * Helper method to set up and run the auto-commit tests.
1201: *
1202: * @param conn The Connection
1203: * @throws SQLException
1204: */
1205: private static void runAutoCommitTests(Connection conn)
1206: throws SQLException {
1207: Statement s = conn.createStatement();
1208: ResultSet rs = s
1209: .executeQuery("select tablename from sys.systables "
1210: + "where tablename = 'AUTOCOMMITTABLE'");
1211: if (rs.next()) {
1212: rs.close();
1213: s.executeUpdate("delete from AutoCommitTable");
1214: } else {
1215: rs.close();
1216: s.executeUpdate("create table AutoCommitTable (num int)");
1217: }
1218: s.executeUpdate("insert into AutoCommitTable values (1)");
1219: s.executeUpdate("insert into AutoCommitTable values (2)");
1220: int isolation = conn.getTransactionIsolation();
1221: conn
1222: .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
1223: testSingleRSAutoCommit(conn);
1224: testSingleRSCloseCursorsAtCommit(conn);
1225: conn.setTransactionIsolation(isolation);
1226: s.executeUpdate("drop table AutoCommitTable");
1227: s.close();
1228: }
1229:
1230: /**
1231: * Tests for two things:
1232: *
1233: * 1) The ResultSet does not close implicitly when the ResultSet completes
1234: * and holdability == HOLD_CURSORS_OVER_COMMIT
1235: *
1236: * 2) The ResultSet auto-commits when it completes and auto-commit is on.
1237: *
1238: * @param conn The Connection
1239: * @param tableName
1240: * @throws SQLException
1241: */
1242: private static void testSingleRSAutoCommit(Connection conn)
1243: throws SQLException {
1244: setHoldability(conn, JDBC30Translation.HOLD_CURSORS_OVER_COMMIT);
1245: System.out.print("Single RS auto-commit test: ");
1246: Statement s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1247: ResultSet.CONCUR_READ_ONLY);
1248: ResultSet rs = s.executeQuery("select * from AutoCommitTable");
1249: while (rs.next())
1250: ;
1251: if (!checkLocks()) {
1252: System.out.println("FAIL. Auto-commit unsuccessful.");
1253: rs.close();
1254: return;
1255: }
1256: try {
1257: if (!rs.next()) {
1258: System.out.println("PASS.");
1259: } else {
1260: System.out
1261: .println("FAIL. Final call of the ResultSet should return false");
1262: }
1263: rs.close();
1264: } catch (SQLException e) {
1265: System.out
1266: .println("FAIL. Final call to ResultSet.next() threw an Exception: ");
1267: e.printStackTrace();
1268: }
1269: }
1270:
1271: /**
1272: * Check to see that ResultSet closes implicitly when holdability is set to
1273: * CLOSE_CURORS_AT_COMMIT.
1274: *
1275: * @param conn The Connection
1276: * @throws SQLException
1277: */
1278: private static void testSingleRSCloseCursorsAtCommit(Connection conn)
1279: throws SQLException {
1280: setHoldability(conn, JDBC30Translation.CLOSE_CURSORS_AT_COMMIT);
1281: conn
1282: .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
1283: System.out.print("SingleRSCloseCursorsAtCommit: ");
1284: Statement s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
1285: ResultSet.CONCUR_READ_ONLY);
1286: ResultSet rs = s.executeQuery("select * from AutoCommitTable");
1287: while (rs.next())
1288: ;
1289: if (!checkLocks()) {
1290: System.out.println("FAIL. Auto-commit unsuccessful.");
1291: rs.close();
1292: return;
1293: }
1294: try {
1295: rs.next();
1296: System.out.println("FAIL. ResultSet not closed implicitly");
1297: rs.close();
1298: } catch (SQLException e) {
1299: System.out.println("PASS.");
1300: }
1301: }
1302:
1303: /**
1304: * Checks to see if there is a lock on a table by attempting to modify the
1305: * same table. If the first connection was serializable then it will
1306: * continue to hold a lock and the second Connection will time out.
1307: *
1308: * @return false if the a lock could not be established, true if a lock
1309: * can be established.
1310: * @throws SQLException
1311: */
1312: private static boolean checkLocks() throws SQLException {
1313: Connection conn = null;
1314: try {
1315: conn = ij.startJBMS();
1316: } catch (Exception e) {
1317: System.out
1318: .println("FAIL. Unable to establish connection in checkLocks");
1319: return false;
1320: }
1321: Statement stmt = conn.createStatement();
1322: try {
1323: stmt.executeUpdate("update AutoCommitTable "
1324: + "set num = 3 where num = 2");
1325: stmt.executeUpdate("update AutoCommitTable "
1326: + "set num = 2 where num = 3");
1327: } catch (SQLException e) {
1328: if (e.getSQLState().equals(SQLState.LOCK_TIMEOUT)) {
1329: return false;
1330: } else {
1331: throw e;
1332: }
1333: }
1334: stmt.close();
1335: conn.close();
1336: return true;
1337: }
1338:
1339: /**
1340: * Sets the holdability of a Connection using reflection so it is
1341: * JDBC2.0 compatible.
1342: *
1343: * @param conn The Connection
1344: * @param hold The new holdability.
1345: * @throws SQLException
1346: */
1347: public static void setHoldability(Connection conn, int hold)
1348: throws SQLException {
1349: try {
1350: Object[] holdArray = { new Integer(hold) };
1351: Method sh = conn.getClass().getMethod("setHoldability",
1352: CONN_PARAM);
1353: sh.invoke(conn, holdArray);
1354: } catch (Exception e) {
1355: System.out.println("shouldn't get that error "
1356: + e.getMessage());
1357: }//for jdks prior to jdk14
1358: }
1359:
1360: private static void cleanUp(Connection conn) throws SQLException {
1361: Statement s = conn.createStatement();
1362: String[] testObjects = { "TABLE APP.T" };
1363: try {
1364: TestUtil.cleanUpTest(s, testObjects);
1365: } catch (SQLException se) {
1366: }
1367: }
1368:
1369: }
|