0001: // jTDS JDBC Driver for Microsoft SQL Server and Sybase
0002: // Copyright (C) 2004 The jTDS Project
0003: //
0004: // This library is free software; you can redistribute it and/or
0005: // modify it under the terms of the GNU Lesser General Public
0006: // License as published by the Free Software Foundation; either
0007: // version 2.1 of the License, or (at your option) any later version.
0008: //
0009: // This library is distributed in the hope that it will be useful,
0010: // but WITHOUT ANY WARRANTY; without even the implied warranty of
0011: // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
0012: // Lesser General Public License for more details.
0013: //
0014: // You should have received a copy of the GNU Lesser General Public
0015: // License along with this library; if not, write to the Free Software
0016: // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
0017: //
0018: package net.sourceforge.jtds.test;
0019:
0020: import java.sql.*;
0021: import java.math.BigDecimal;
0022: import java.io.InputStream;
0023: import java.util.ArrayList;
0024:
0025: /**
0026: * @version 1.0
0027: */
0028: public class ResultSetTest extends DatabaseTestCase {
0029: public ResultSetTest(String name) {
0030: super (name);
0031: }
0032:
0033: /**
0034: * Test BIT data type.
0035: */
0036: public void testGetObject1() throws Exception {
0037: boolean data = true;
0038:
0039: Statement stmt = con.createStatement();
0040: stmt
0041: .execute("CREATE TABLE #getObject1 (data BIT, minval BIT, maxval BIT)");
0042: stmt.close();
0043:
0044: PreparedStatement pstmt = con
0045: .prepareStatement("INSERT INTO #getObject1 (data, minval, maxval) VALUES (?, ?, ?)");
0046:
0047: pstmt.setBoolean(1, data);
0048: pstmt.setBoolean(2, false);
0049: pstmt.setBoolean(3, true);
0050: assertEquals(1, pstmt.executeUpdate());
0051:
0052: pstmt.close();
0053:
0054: Statement stmt2 = con.createStatement();
0055: ResultSet rs = stmt2
0056: .executeQuery("SELECT data, minval, maxval FROM #getObject1");
0057:
0058: assertTrue(rs.next());
0059:
0060: assertTrue(rs.getBoolean(1));
0061: assertTrue(rs.getByte(1) == 1);
0062: assertTrue(rs.getShort(1) == 1);
0063: assertTrue(rs.getInt(1) == 1);
0064: assertTrue(rs.getLong(1) == 1);
0065: assertTrue(rs.getFloat(1) == 1);
0066: assertTrue(rs.getDouble(1) == 1);
0067: assertTrue(rs.getBigDecimal(1).byteValue() == 1);
0068: assertEquals("1", rs.getString(1));
0069:
0070: Object tmpData = rs.getObject(1);
0071:
0072: assertTrue(tmpData instanceof Boolean);
0073: assertEquals(true, ((Boolean) tmpData).booleanValue());
0074:
0075: ResultSetMetaData resultSetMetaData = rs.getMetaData();
0076:
0077: assertNotNull(resultSetMetaData);
0078: assertEquals(Types.BIT, resultSetMetaData.getColumnType(1));
0079:
0080: assertFalse(rs.getBoolean(2));
0081: assertTrue(rs.getBoolean(3));
0082:
0083: assertFalse(rs.next());
0084: stmt2.close();
0085: rs.close();
0086: }
0087:
0088: /**
0089: * Test TINYINT data type.
0090: */
0091: public void testGetObject2() throws Exception {
0092: byte data = 1;
0093:
0094: Statement stmt = con.createStatement();
0095: stmt
0096: .execute("CREATE TABLE #getObject2 (data TINYINT, minval TINYINT, maxval TINYINT)");
0097: stmt.close();
0098:
0099: PreparedStatement pstmt = con
0100: .prepareStatement("INSERT INTO #getObject2 (data, minval, maxval) VALUES (?, ?, ?)");
0101:
0102: pstmt.setByte(1, data);
0103: pstmt.setByte(2, Byte.MIN_VALUE);
0104: pstmt.setByte(3, Byte.MAX_VALUE);
0105: assertEquals(1, pstmt.executeUpdate());
0106:
0107: pstmt.close();
0108:
0109: Statement stmt2 = con.createStatement();
0110: ResultSet rs = stmt2
0111: .executeQuery("SELECT data, minval, maxval FROM #getObject2");
0112:
0113: assertTrue(rs.next());
0114:
0115: assertTrue(rs.getBoolean(1));
0116: assertTrue(rs.getByte(1) == 1);
0117: assertTrue(rs.getShort(1) == 1);
0118: assertTrue(rs.getInt(1) == 1);
0119: assertTrue(rs.getLong(1) == 1);
0120: assertTrue(rs.getFloat(1) == 1);
0121: assertTrue(rs.getDouble(1) == 1);
0122: assertTrue(rs.getBigDecimal(1).byteValue() == 1);
0123: assertEquals("1", rs.getString(1));
0124:
0125: Object tmpData = rs.getObject(1);
0126:
0127: assertTrue(tmpData instanceof Integer);
0128: assertEquals(data, ((Integer) tmpData).byteValue());
0129:
0130: ResultSetMetaData resultSetMetaData = rs.getMetaData();
0131:
0132: assertNotNull(resultSetMetaData);
0133: assertEquals(Types.TINYINT, resultSetMetaData.getColumnType(1));
0134:
0135: assertEquals(rs.getByte(2), Byte.MIN_VALUE);
0136: assertEquals(rs.getByte(3), Byte.MAX_VALUE);
0137:
0138: assertFalse(rs.next());
0139: stmt2.close();
0140: rs.close();
0141: }
0142:
0143: /**
0144: * Test SMALLINT data type.
0145: */
0146: public void testGetObject3() throws Exception {
0147: short data = 1;
0148:
0149: Statement stmt = con.createStatement();
0150: stmt
0151: .execute("CREATE TABLE #getObject3 (data SMALLINT, minval SMALLINT, maxval SMALLINT)");
0152: stmt.close();
0153:
0154: PreparedStatement pstmt = con
0155: .prepareStatement("INSERT INTO #getObject3 (data, minval, maxval) VALUES (?, ?, ?)");
0156:
0157: pstmt.setShort(1, data);
0158: pstmt.setShort(2, Short.MIN_VALUE);
0159: pstmt.setShort(3, Short.MAX_VALUE);
0160: assertEquals(1, pstmt.executeUpdate());
0161:
0162: pstmt.close();
0163:
0164: Statement stmt2 = con.createStatement();
0165: ResultSet rs = stmt2
0166: .executeQuery("SELECT data, minval, maxval FROM #getObject3");
0167:
0168: assertTrue(rs.next());
0169:
0170: assertTrue(rs.getBoolean(1));
0171: assertTrue(rs.getByte(1) == 1);
0172: assertTrue(rs.getShort(1) == 1);
0173: assertTrue(rs.getInt(1) == 1);
0174: assertTrue(rs.getLong(1) == 1);
0175: assertTrue(rs.getFloat(1) == 1);
0176: assertTrue(rs.getDouble(1) == 1);
0177: assertTrue(rs.getBigDecimal(1).shortValue() == 1);
0178: assertEquals("1", rs.getString(1));
0179:
0180: Object tmpData = rs.getObject(1);
0181:
0182: assertTrue(tmpData instanceof Integer);
0183: assertEquals(data, ((Integer) tmpData).shortValue());
0184:
0185: ResultSetMetaData resultSetMetaData = rs.getMetaData();
0186:
0187: assertNotNull(resultSetMetaData);
0188: assertEquals(Types.SMALLINT, resultSetMetaData.getColumnType(1));
0189:
0190: assertEquals(rs.getShort(2), Short.MIN_VALUE);
0191: assertEquals(rs.getShort(3), Short.MAX_VALUE);
0192:
0193: assertFalse(rs.next());
0194: stmt2.close();
0195: rs.close();
0196: }
0197:
0198: /**
0199: * Test INT data type.
0200: */
0201: public void testGetObject4() throws Exception {
0202: int data = 1;
0203:
0204: Statement stmt = con.createStatement();
0205: stmt
0206: .execute("CREATE TABLE #getObject4 (data INT, minval INT, maxval INT)");
0207: stmt.close();
0208:
0209: PreparedStatement pstmt = con
0210: .prepareStatement("INSERT INTO #getObject4 (data, minval, maxval) VALUES (?, ?, ?)");
0211:
0212: pstmt.setInt(1, data);
0213: pstmt.setInt(2, Integer.MIN_VALUE);
0214: pstmt.setInt(3, Integer.MAX_VALUE);
0215: assertEquals(1, pstmt.executeUpdate());
0216:
0217: pstmt.close();
0218:
0219: Statement stmt2 = con.createStatement();
0220: ResultSet rs = stmt2
0221: .executeQuery("SELECT data, minval, maxval FROM #getObject4");
0222:
0223: assertTrue(rs.next());
0224:
0225: assertTrue(rs.getBoolean(1));
0226: assertTrue(rs.getByte(1) == 1);
0227: assertTrue(rs.getShort(1) == 1);
0228: assertTrue(rs.getInt(1) == 1);
0229: assertTrue(rs.getLong(1) == 1);
0230: assertTrue(rs.getFloat(1) == 1);
0231: assertTrue(rs.getDouble(1) == 1);
0232: assertTrue(rs.getBigDecimal(1).intValue() == 1);
0233: assertEquals("1", rs.getString(1));
0234:
0235: Object tmpData = rs.getObject(1);
0236:
0237: assertTrue(tmpData instanceof Integer);
0238: assertEquals(data, ((Integer) tmpData).intValue());
0239:
0240: ResultSetMetaData resultSetMetaData = rs.getMetaData();
0241:
0242: assertNotNull(resultSetMetaData);
0243: assertEquals(Types.INTEGER, resultSetMetaData.getColumnType(1));
0244:
0245: assertEquals(rs.getInt(2), Integer.MIN_VALUE);
0246: assertEquals(rs.getInt(3), Integer.MAX_VALUE);
0247:
0248: assertFalse(rs.next());
0249: stmt2.close();
0250: rs.close();
0251: }
0252:
0253: /**
0254: * Test BIGINT data type.
0255: */
0256: public void testGetObject5() throws Exception {
0257: long data = 1;
0258:
0259: Statement stmt = con.createStatement();
0260: stmt
0261: .execute("CREATE TABLE #getObject5 (data DECIMAL(28, 0), minval DECIMAL(28, 0), maxval DECIMAL(28, 0))");
0262: stmt.close();
0263:
0264: PreparedStatement pstmt = con
0265: .prepareStatement("INSERT INTO #getObject5 (data, minval, maxval) VALUES (?, ?, ?)");
0266:
0267: pstmt.setLong(1, data);
0268: pstmt.setLong(2, Long.MIN_VALUE);
0269: pstmt.setLong(3, Long.MAX_VALUE);
0270: assertEquals(1, pstmt.executeUpdate());
0271:
0272: pstmt.close();
0273:
0274: Statement stmt2 = con.createStatement();
0275: ResultSet rs = stmt2
0276: .executeQuery("SELECT data, minval, maxval FROM #getObject5");
0277:
0278: assertTrue(rs.next());
0279:
0280: assertTrue(rs.getBoolean(1));
0281: assertTrue(rs.getByte(1) == 1);
0282: assertTrue(rs.getShort(1) == 1);
0283: assertTrue(rs.getInt(1) == 1);
0284: assertTrue(rs.getLong(1) == 1);
0285: assertTrue(rs.getFloat(1) == 1);
0286: assertTrue(rs.getDouble(1) == 1);
0287: assertTrue(rs.getBigDecimal(1).longValue() == 1);
0288: assertEquals("1", rs.getString(1));
0289:
0290: Object tmpData = rs.getObject(1);
0291:
0292: assertTrue(tmpData instanceof BigDecimal);
0293: assertEquals(data, ((BigDecimal) tmpData).longValue());
0294:
0295: ResultSetMetaData resultSetMetaData = rs.getMetaData();
0296:
0297: assertNotNull(resultSetMetaData);
0298: assertEquals(Types.DECIMAL, resultSetMetaData.getColumnType(1));
0299:
0300: assertEquals(rs.getLong(2), Long.MIN_VALUE);
0301: assertEquals(rs.getLong(3), Long.MAX_VALUE);
0302:
0303: assertFalse(rs.next());
0304: stmt2.close();
0305: rs.close();
0306: }
0307:
0308: /**
0309: * Test for bug [961594] ResultSet.
0310: */
0311: public void testResultSetScroll1() throws Exception {
0312: int count = 125;
0313:
0314: Statement stmt = con.createStatement();
0315: stmt.execute("CREATE TABLE #resultSetScroll1 (data INT)");
0316: stmt.close();
0317:
0318: PreparedStatement pstmt = con
0319: .prepareStatement("INSERT INTO #resultSetScroll1 (data) VALUES (?)");
0320:
0321: for (int i = 1; i <= count; i++) {
0322: pstmt.setInt(1, i);
0323: assertEquals(1, pstmt.executeUpdate());
0324: }
0325:
0326: pstmt.close();
0327:
0328: Statement stmt2 = con.createStatement(
0329: ResultSet.TYPE_SCROLL_INSENSITIVE,
0330: ResultSet.CONCUR_READ_ONLY);
0331: ResultSet rs = stmt2
0332: .executeQuery("SELECT data FROM #resultSetScroll1");
0333:
0334: assertTrue(rs.last());
0335: assertEquals(count, rs.getRow());
0336:
0337: stmt2.close();
0338: rs.close();
0339: }
0340:
0341: /**
0342: * Test for bug [945462] getResultSet() return null if you use scrollable/updatable.
0343: */
0344: public void testResultSetScroll2() throws Exception {
0345: Statement stmt = con.createStatement();
0346: stmt.execute("CREATE TABLE #resultSetScroll2 (data INT)");
0347: stmt.close();
0348:
0349: PreparedStatement pstmt = con
0350: .prepareStatement("INSERT INTO #resultSetScroll2 (data) VALUES (?)");
0351:
0352: pstmt.setInt(1, 1);
0353: assertEquals(1, pstmt.executeUpdate());
0354:
0355: pstmt.close();
0356:
0357: Statement stmt2 = con.createStatement(
0358: ResultSet.TYPE_SCROLL_INSENSITIVE,
0359: ResultSet.CONCUR_UPDATABLE);
0360: stmt2.executeQuery("SELECT data FROM #resultSetScroll2");
0361:
0362: ResultSet rs = stmt2.getResultSet();
0363:
0364: assertNotNull(rs);
0365: assertTrue(rs.next());
0366: assertEquals(1, rs.getInt(1));
0367: assertFalse(rs.next());
0368:
0369: stmt2.close();
0370: rs.close();
0371: }
0372:
0373: /**
0374: * Test for bug [1028881] statement.execute() causes wrong ResultSet type.
0375: */
0376: public void testResultSetScroll3() throws Exception {
0377: Statement stmt = con.createStatement();
0378: stmt.execute("CREATE TABLE #resultSetScroll3 (data INT)");
0379: stmt
0380: .execute("CREATE PROCEDURE #procResultSetScroll3 AS SELECT data FROM #resultSetScroll3");
0381: stmt.close();
0382:
0383: PreparedStatement pstmt = con
0384: .prepareStatement("INSERT INTO #resultSetScroll3 (data) VALUES (?)");
0385: pstmt.setInt(1, 1);
0386: assertEquals(1, pstmt.executeUpdate());
0387: pstmt.close();
0388:
0389: // Test plain Statement
0390: Statement stmt2 = con.createStatement(
0391: ResultSet.TYPE_SCROLL_INSENSITIVE,
0392: ResultSet.CONCUR_READ_ONLY);
0393: assertTrue("Was expecting a ResultSet", stmt2
0394: .execute("SELECT data FROM #resultSetScroll3"));
0395:
0396: ResultSet rs = stmt2.getResultSet();
0397: assertEquals("ResultSet not scrollable",
0398: ResultSet.TYPE_SCROLL_INSENSITIVE, rs.getType());
0399:
0400: rs.close();
0401: stmt2.close();
0402:
0403: // Test PreparedStatement
0404: pstmt = con.prepareStatement(
0405: "SELECT data FROM #resultSetScroll3",
0406: ResultSet.TYPE_SCROLL_INSENSITIVE,
0407: ResultSet.CONCUR_READ_ONLY);
0408: assertTrue("Was expecting a ResultSet", pstmt.execute());
0409:
0410: rs = pstmt.getResultSet();
0411: assertEquals("ResultSet not scrollable",
0412: ResultSet.TYPE_SCROLL_INSENSITIVE, rs.getType());
0413:
0414: rs.close();
0415: pstmt.close();
0416:
0417: // Test CallableStatement
0418: CallableStatement cstmt = con.prepareCall(
0419: "{call #procResultSetScroll3}",
0420: ResultSet.TYPE_SCROLL_INSENSITIVE,
0421: ResultSet.CONCUR_READ_ONLY);
0422: assertTrue("Was expecting a ResultSet", cstmt.execute());
0423:
0424: rs = cstmt.getResultSet();
0425: assertEquals("ResultSet not scrollable",
0426: ResultSet.TYPE_SCROLL_INSENSITIVE, rs.getType());
0427:
0428: rs.close();
0429: cstmt.close();
0430: }
0431:
0432: /**
0433: * Test for bug [1008208] 0.9-rc1 updateNull doesn't work.
0434: */
0435: public void testResultSetUpdate1() throws Exception {
0436: Statement stmt = con.createStatement();
0437: stmt
0438: .execute("CREATE TABLE #resultSetUpdate1 (id INT PRIMARY KEY, dsi SMALLINT NULL, di INT NULL)");
0439: stmt.close();
0440:
0441: PreparedStatement pstmt = con
0442: .prepareStatement("INSERT INTO #resultSetUpdate1 (id, dsi, di) VALUES (?, ?, ?)");
0443:
0444: pstmt.setInt(1, 1);
0445: pstmt.setShort(2, (short) 1);
0446: pstmt.setInt(3, 1);
0447: assertEquals(1, pstmt.executeUpdate());
0448:
0449: pstmt.close();
0450:
0451: stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0452: ResultSet.CONCUR_UPDATABLE);
0453: stmt.executeQuery("SELECT id, dsi, di FROM #resultSetUpdate1");
0454:
0455: ResultSet rs = stmt.getResultSet();
0456:
0457: assertNotNull(rs);
0458: assertTrue(rs.next());
0459: rs.updateNull("dsi");
0460: rs.updateNull("di");
0461: rs.updateRow();
0462: rs.moveToInsertRow();
0463: rs.updateInt(1, 2);
0464: rs.updateNull("dsi");
0465: rs.updateNull("di");
0466: rs.insertRow();
0467:
0468: stmt.close();
0469: rs.close();
0470:
0471: stmt = con.createStatement();
0472: stmt
0473: .executeQuery("SELECT id, dsi, di FROM #resultSetUpdate1 ORDER BY id");
0474:
0475: rs = stmt.getResultSet();
0476:
0477: assertNotNull(rs);
0478: assertTrue(rs.next());
0479: assertEquals(1, rs.getInt(1));
0480: rs.getShort(2);
0481: assertTrue(rs.wasNull());
0482: rs.getInt(3);
0483: assertTrue(rs.wasNull());
0484: assertTrue(rs.next());
0485: assertEquals(2, rs.getInt(1));
0486: rs.getShort(2);
0487: assertTrue(rs.wasNull());
0488: rs.getInt(3);
0489: assertTrue(rs.wasNull());
0490: assertFalse(rs.next());
0491:
0492: stmt.close();
0493: rs.close();
0494: }
0495:
0496: /**
0497: * Test for bug [1009233] ResultSet getColumnName, getColumnLabel return wrong values
0498: */
0499: public void testResultSetColumnName1() throws Exception {
0500: Statement stmt = con.createStatement();
0501: stmt.execute("CREATE TABLE #resultSetCN1 (data INT)");
0502: stmt.close();
0503:
0504: PreparedStatement pstmt = con
0505: .prepareStatement("INSERT INTO #resultSetCN1 (data) VALUES (?)");
0506:
0507: pstmt.setInt(1, 1);
0508: assertEquals(1, pstmt.executeUpdate());
0509:
0510: pstmt.close();
0511:
0512: Statement stmt2 = con.createStatement();
0513: stmt2.executeQuery("SELECT data as test FROM #resultSetCN1");
0514:
0515: ResultSet rs = stmt2.getResultSet();
0516:
0517: assertNotNull(rs);
0518: assertTrue(rs.next());
0519: assertEquals(1, rs.getInt("test"));
0520: assertFalse(rs.next());
0521:
0522: stmt2.close();
0523: rs.close();
0524: }
0525:
0526: /**
0527: * Test for fixed bugs in ResultSetMetaData:
0528: * <ol>
0529: * <li>isNullable() always returns columnNoNulls.
0530: * <li>isSigned returns true in error for TINYINT columns.
0531: * <li>Type names for numeric / decimal have (prec,scale) appended in error.
0532: * <li>Type names for auto increment columns do not have "identity" appended.
0533: * </ol>
0534: * NB: This test assumes getColumnName has been fixed to work as per the suggestion
0535: * in bug report [1009233].
0536: *
0537: * @throws Exception
0538: */
0539: public void testResultSetMetaData() throws Exception {
0540: Statement stmt = con
0541: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
0542: ResultSet.CONCUR_UPDATABLE);
0543: stmt
0544: .execute("CREATE TABLE #TRSMD (id INT IDENTITY NOT NULL, byte TINYINT NOT NULL, num DECIMAL(28,10) NULL)");
0545: ResultSetMetaData rsmd = stmt.executeQuery(
0546: "SELECT id as idx, byte, num FROM #TRSMD")
0547: .getMetaData();
0548: assertNotNull(rsmd);
0549: // Check id
0550: assertEquals("idx", rsmd.getColumnName(1)); // no longer returns base name
0551: assertEquals("idx", rsmd.getColumnLabel(1));
0552: assertTrue(rsmd.isAutoIncrement(1));
0553: assertTrue(rsmd.isSigned(1));
0554: assertEquals(ResultSetMetaData.columnNoNulls, rsmd
0555: .isNullable(1));
0556: assertEquals("int identity", rsmd.getColumnTypeName(1));
0557: assertEquals(Types.INTEGER, rsmd.getColumnType(1));
0558: // Check byte
0559: assertFalse(rsmd.isAutoIncrement(2));
0560: assertFalse(rsmd.isSigned(2));
0561: assertEquals(ResultSetMetaData.columnNoNulls, rsmd
0562: .isNullable(2));
0563: assertEquals("tinyint", rsmd.getColumnTypeName(2));
0564: assertEquals(Types.TINYINT, rsmd.getColumnType(2));
0565: // Check num
0566: assertFalse(rsmd.isAutoIncrement(3));
0567: assertTrue(rsmd.isSigned(3));
0568: assertEquals(ResultSetMetaData.columnNullable, rsmd
0569: .isNullable(3));
0570: assertEquals("decimal", rsmd.getColumnTypeName(3));
0571: assertEquals(Types.DECIMAL, rsmd.getColumnType(3));
0572: stmt.close();
0573: }
0574:
0575: /**
0576: * Test for bug [1022445] Cursor downgrade warning not raised.
0577: */
0578: public void testCursorWarning() throws Exception {
0579: Statement stmt = con.createStatement();
0580: stmt
0581: .execute("CREATE TABLE #TESTCW (id INT PRIMARY KEY, DATA VARCHAR(255))");
0582: stmt
0583: .execute("CREATE PROC #SPTESTCW @P0 INT OUTPUT AS SELECT * FROM #TESTCW");
0584: stmt.close();
0585: CallableStatement cstmt = con.prepareCall(
0586: "{call #SPTESTCW(?)}",
0587: ResultSet.TYPE_SCROLL_INSENSITIVE,
0588: ResultSet.CONCUR_UPDATABLE);
0589: cstmt.registerOutParameter(1, Types.INTEGER);
0590: ResultSet rs = cstmt.executeQuery();
0591: // This should generate a ResultSet type/concurrency downgraded error.
0592: assertNotNull(rs.getWarnings());
0593: cstmt.close();
0594: }
0595:
0596: /**
0597: * Test that the cursor fallback logic correctly discriminates between
0598: * "real" sql errors and cursor open failures.
0599: * <p/>
0600: * This illustrates the logic added to fix:
0601: * <ol>
0602: * <li>[1323363] Deadlock Exception not reported (SQL Server)</li>
0603: * <li>[1283472] Unable to cancel statement with cursor resultset</li>
0604: * </ol>
0605: */
0606: public void testCursorFallback() throws Exception {
0607: Statement stmt = con.createStatement(
0608: ResultSet.TYPE_SCROLL_INSENSITIVE,
0609: ResultSet.CONCUR_READ_ONLY);
0610: //
0611: // This test should fail on the cursor open but fall back to normal
0612: // execution returning two result sets
0613: //
0614: stmt
0615: .execute("CREATE PROC #testcursor as SELECT 'data' select 'data2'");
0616: stmt.execute("exec #testcursor");
0617: assertNotNull(stmt.getWarnings());
0618: ResultSet rs = stmt.getResultSet();
0619: assertNotNull(rs); // First result set OK
0620: assertTrue(stmt.getMoreResults());
0621: rs = stmt.getResultSet();
0622: assertNotNull(rs); // Second result set OK
0623: //
0624: // This test should fail on the cursor open (because of the for browse)
0625: // but fall back to normal execution returning a single result set
0626: //
0627: rs = stmt
0628: .executeQuery("SELECT description FROM master..sysmessages FOR BROWSE");
0629: assertNotNull(rs);
0630: assertNotNull(rs.getWarnings());
0631: rs.close();
0632: //
0633: // Enable logging to see that this test should just fail without
0634: // attempting to fall back on normal execution.
0635: //
0636: // DriverManager.setLogStream(System.out);
0637: try {
0638: stmt.executeQuery("select bad from syntax");
0639: fail("Expected SQLException");
0640: } catch (SQLException e) {
0641: assertEquals("S0002", e.getSQLState());
0642: }
0643: // DriverManager.setLogStream(null);
0644: stmt.close();
0645: }
0646:
0647: /**
0648: * Test for bug [1246270] Closing a statement after canceling it throws an
0649: * exception.
0650: */
0651: public void testCancelResultSet() throws Exception {
0652: Statement stmt = con.createStatement();
0653: stmt
0654: .execute("CREATE TABLE #TEST (id int primary key, data varchar(255))");
0655: for (int i = 1; i < 1000; i++) {
0656: stmt
0657: .executeUpdate("INSERT INTO #TEST VALUES ("
0658: + i
0659: + ", 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
0660: + "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')");
0661: }
0662: ResultSet rs = stmt.executeQuery("SELECT * FROM #TEST");
0663: assertNotNull(rs);
0664: assertTrue(rs.next());
0665: stmt.cancel();
0666: stmt.close();
0667: }
0668:
0669: /**
0670: * Test whether retrieval by name returns the first occurence (that's what
0671: * the spec requires).
0672: */
0673: public void testGetByName() throws Exception {
0674: Statement stmt = con.createStatement();
0675: ResultSet rs = stmt
0676: .executeQuery("SELECT 1 myCol, 2 myCol, 3 myCol");
0677: assertTrue(rs.next());
0678: assertEquals(1, rs.getInt("myCol"));
0679: assertFalse(rs.next());
0680: stmt.close();
0681: }
0682:
0683: /**
0684: * Test if COL_INFO packets are processed correctly for
0685: * <code>ResultSet</code>s with over 255 columns.
0686: */
0687: public void testMoreThan255Columns() throws Exception {
0688: Statement stmt = con
0689: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
0690: ResultSet.CONCUR_UPDATABLE);
0691:
0692: // create the table
0693: int cols = 260;
0694: StringBuffer create = new StringBuffer(
0695: "create table #manycolumns (");
0696: for (int i = 0; i < cols; ++i) {
0697: create.append("col" + i + " char(10), ");
0698: }
0699: create.append(")");
0700: stmt.executeUpdate(create.toString());
0701:
0702: String query = "select * from #manycolumns";
0703: ResultSet rs = stmt.executeQuery(query);
0704: rs.close();
0705: stmt.close();
0706: }
0707:
0708: /**
0709: * Test that <code>insertRow()</code> works with no values set.
0710: */
0711: public void testEmptyInsertRow() throws Exception {
0712: int rows = 10;
0713: Statement stmt = con
0714: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
0715: ResultSet.CONCUR_UPDATABLE);
0716:
0717: stmt
0718: .executeUpdate("create table #emptyInsertRow (id int identity, val int default 10)");
0719: ResultSet rs = stmt
0720: .executeQuery("select * from #emptyInsertRow");
0721:
0722: for (int i = 0; i < rows; i++) {
0723: rs.moveToInsertRow();
0724: rs.insertRow();
0725: }
0726: rs.close();
0727:
0728: rs = stmt.executeQuery("select count(*) from #emptyInsertRow");
0729: assertTrue(rs.next());
0730: assertEquals(rows, rs.getInt(1));
0731: rs.close();
0732:
0733: rs = stmt
0734: .executeQuery("select * from #emptyInsertRow order by id");
0735: assertTrue(rs.next());
0736: assertEquals(1, rs.getInt(1));
0737: assertEquals(10, rs.getInt(2));
0738: rs.close();
0739: stmt.close();
0740: }
0741:
0742: /**
0743: * Test that inserted rows are visible in a scroll sensitive
0744: * <code>ResultSet</code> and that they show up at the end.
0745: */
0746: public void testInsertRowVisible() throws Exception {
0747: int rows = 10;
0748: Statement stmt = con.createStatement(
0749: ResultSet.TYPE_SCROLL_SENSITIVE,
0750: ResultSet.CONCUR_UPDATABLE);
0751:
0752: stmt
0753: .executeUpdate("create table #insertRowNotVisible (val int primary key)");
0754: ResultSet rs = stmt
0755: .executeQuery("select * from #insertRowNotVisible");
0756:
0757: for (int i = 1; i <= rows; i++) {
0758: rs.moveToInsertRow();
0759: rs.updateInt(1, i);
0760: rs.insertRow();
0761: rs.moveToCurrentRow();
0762: rs.last();
0763: assertEquals(i, rs.getRow());
0764: }
0765:
0766: rs.close();
0767: stmt.close();
0768: }
0769:
0770: /**
0771: * Test that updated rows are marked as deleted and the new values inserted
0772: * at the end of the <code>ResultSet</code> if the primary key is updated.
0773: */
0774: public void testUpdateRowDuplicatesRow() throws Exception {
0775: Statement stmt = con.createStatement(
0776: ResultSet.TYPE_SCROLL_SENSITIVE,
0777: ResultSet.CONCUR_UPDATABLE);
0778:
0779: stmt
0780: .executeUpdate("create table #updateRowDuplicatesRow (val int primary key)");
0781: stmt
0782: .executeUpdate("insert into #updateRowDuplicatesRow (val) values (1)");
0783: stmt
0784: .executeUpdate("insert into #updateRowDuplicatesRow (val) values (2)");
0785: stmt
0786: .executeUpdate("insert into #updateRowDuplicatesRow (val) values (3)");
0787:
0788: ResultSet rs = stmt
0789: .executeQuery("select val from #updateRowDuplicatesRow order by val");
0790:
0791: for (int i = 0; i < 3; i++) {
0792: assertTrue(rs.next());
0793: assertFalse(rs.rowUpdated());
0794: assertFalse(rs.rowInserted());
0795: assertFalse(rs.rowDeleted());
0796: rs.updateInt(1, rs.getInt(1) + 10);
0797: rs.updateRow();
0798: assertFalse(rs.rowUpdated());
0799: assertFalse(rs.rowInserted());
0800: assertTrue(rs.rowDeleted());
0801: }
0802:
0803: for (int i = 11; i <= 13; i++) {
0804: assertTrue(rs.next());
0805: assertFalse(rs.rowUpdated());
0806: assertFalse(rs.rowInserted());
0807: assertFalse(rs.rowDeleted());
0808: assertEquals(i, rs.getInt(1));
0809: }
0810:
0811: rs.close();
0812: stmt.close();
0813: }
0814:
0815: /**
0816: * Test that updated rows are modified in place if the primary key is not
0817: * updated.
0818: */
0819: public void testUpdateRowUpdatesRow() throws Exception {
0820: Statement stmt = con.createStatement(
0821: ResultSet.TYPE_SCROLL_SENSITIVE,
0822: ResultSet.CONCUR_UPDATABLE);
0823:
0824: stmt
0825: .executeUpdate("create table #updateRowUpdatesRow (id int primary key, val int)");
0826: stmt
0827: .executeUpdate("insert into #updateRowUpdatesRow (id, val) values (1, 1)");
0828: stmt
0829: .executeUpdate("insert into #updateRowUpdatesRow (id, val) values (2, 2)");
0830: stmt
0831: .executeUpdate("insert into #updateRowUpdatesRow (id, val) values (3, 3)");
0832:
0833: ResultSet rs = stmt
0834: .executeQuery("select id, val from #updateRowUpdatesRow order by id");
0835:
0836: for (int i = 0; i < 3; i++) {
0837: assertTrue(rs.next());
0838: assertFalse(rs.rowUpdated());
0839: assertFalse(rs.rowInserted());
0840: assertFalse(rs.rowDeleted());
0841: rs.updateInt(2, rs.getInt(2) + 10);
0842: rs.updateRow();
0843: assertFalse(rs.rowUpdated());
0844: assertFalse(rs.rowInserted());
0845: assertFalse(rs.rowDeleted());
0846: assertEquals(rs.getInt(1) + 10, rs.getInt(2));
0847: }
0848:
0849: assertFalse(rs.next());
0850:
0851: rs.close();
0852: stmt.close();
0853: }
0854:
0855: /**
0856: * Test that deleted rows are not removed but rather marked as deleted.
0857: */
0858: public void testDeleteRowMarksDeleted() throws Exception {
0859: Statement stmt = con.createStatement(
0860: ResultSet.TYPE_SCROLL_SENSITIVE,
0861: ResultSet.CONCUR_UPDATABLE);
0862:
0863: stmt
0864: .executeUpdate("create table #deleteRowMarksDeleted (val int primary key)");
0865: stmt
0866: .executeUpdate("insert into #deleteRowMarksDeleted (val) values (1)");
0867: stmt
0868: .executeUpdate("insert into #deleteRowMarksDeleted (val) values (2)");
0869: stmt
0870: .executeUpdate("insert into #deleteRowMarksDeleted (val) values (3)");
0871:
0872: ResultSet rs = stmt
0873: .executeQuery("select val from #deleteRowMarksDeleted order by val");
0874:
0875: for (int i = 0; i < 3; i++) {
0876: assertTrue(rs.next());
0877: assertFalse(rs.rowUpdated());
0878: assertFalse(rs.rowInserted());
0879: assertFalse(rs.rowDeleted());
0880: rs.deleteRow();
0881: assertFalse(rs.rowUpdated());
0882: assertFalse(rs.rowInserted());
0883: assertTrue(rs.rowDeleted());
0884: }
0885:
0886: assertFalse(rs.next());
0887: rs.close();
0888: stmt.close();
0889: }
0890:
0891: /**
0892: * Test for bug [1170777] resultSet.updateRow() fails if no row has been
0893: * changed.
0894: */
0895: public void testUpdateRowNoChanges() throws Exception {
0896: Statement stmt = con.createStatement(
0897: ResultSet.TYPE_SCROLL_SENSITIVE,
0898: ResultSet.CONCUR_UPDATABLE);
0899:
0900: stmt
0901: .executeUpdate("create table #deleteRowMarksDeleted (val int primary key)");
0902: stmt
0903: .executeUpdate("insert into #deleteRowMarksDeleted (val) values (1)");
0904:
0905: ResultSet rs = stmt
0906: .executeQuery("select val from #deleteRowMarksDeleted order by val");
0907: assertTrue(rs.next());
0908: // This should not crash; it should be a no-op
0909: rs.updateRow();
0910: rs.refreshRow();
0911: assertEquals(1, rs.getInt(1));
0912: assertFalse(rs.next());
0913:
0914: rs.close();
0915: stmt.close();
0916: }
0917:
0918: /**
0919: * Test the behavior of <code>sp_cursorfetch</code> with fetch sizes
0920: * greater than 1.
0921: * <p>
0922: * <b>Assertions tested:</b>
0923: * <ul>
0924: * <li>The <i>current row</i> is always the first row returned by the
0925: * last fetch, regardless of what fetch type was used.
0926: * <li>Row number parameter is ignored by fetch types other than absolute
0927: * and relative.
0928: * <li>Refresh fetch type simply reruns the previous request (it ignores
0929: * both row number and number of rows) and will not affect the
0930: * <i>current row</i>.
0931: * <li>Fetch next returns the packet of rows right after the last row
0932: * returned by the last fetch (regardless of what type of fetch that
0933: * was).
0934: * <li>Fetch previous returns the packet of rows right before the first
0935: * row returned by the last fetch (regardless of what type of fetch
0936: * that was).
0937: * <li>If a fetch previous tries to read before the start of the
0938: * <code>ResultSet</code> the requested number of rows is returned,
0939: * starting with row 1 and the error code returned is non-zero (2).
0940: * </ul>
0941: */
0942: public void testCursorFetch() throws Exception {
0943: int rows = 10;
0944: Statement stmt = con.createStatement();
0945: stmt
0946: .executeUpdate("create table #testCursorFetch (id int primary key, val int)");
0947: stmt.close();
0948:
0949: PreparedStatement pstmt = con
0950: .prepareStatement("insert into #testCursorFetch (id, val) values (?, ?)");
0951: for (int i = 1; i <= rows; i++) {
0952: pstmt.setInt(1, i);
0953: pstmt.setInt(2, i);
0954: pstmt.executeUpdate();
0955: }
0956: pstmt.close();
0957:
0958: //
0959: // Open cursor
0960: //
0961: CallableStatement cstmt = con
0962: .prepareCall("{?=call sp_cursoropen(?, ?, ?, ?, ?)}");
0963: // Return value (OUT)
0964: cstmt.registerOutParameter(1, Types.INTEGER);
0965: // Cursor handle (OUT)
0966: cstmt.registerOutParameter(2, Types.INTEGER);
0967: // Statement (IN)
0968: cstmt
0969: .setString(3,
0970: "select * from #testCursorFetch order by id");
0971: // Scroll options (INOUT)
0972: cstmt.setInt(4, 1); // Keyset driven
0973: cstmt.registerOutParameter(4, Types.INTEGER);
0974: // Concurrency options (INOUT)
0975: cstmt.setInt(5, 2); // Scroll locks
0976: cstmt.registerOutParameter(5, Types.INTEGER);
0977: // Row count (OUT)
0978: cstmt.registerOutParameter(6, Types.INTEGER);
0979:
0980: ResultSet rs = cstmt.executeQuery();
0981: assertEquals(2, rs.getMetaData().getColumnCount());
0982: assertFalse(rs.next());
0983:
0984: assertEquals(0, cstmt.getInt(1));
0985: int cursor = cstmt.getInt(2);
0986: assertEquals(1, cstmt.getInt(4));
0987: assertEquals(2, cstmt.getInt(5));
0988: assertEquals(rows, cstmt.getInt(6));
0989:
0990: cstmt.close();
0991:
0992: //
0993: // Play around with fetch
0994: //
0995: cstmt = con.prepareCall("{?=call sp_cursorfetch(?, ?, ?, ?)}");
0996: // Return value (OUT)
0997: cstmt.registerOutParameter(1, Types.INTEGER);
0998: // Cursor handle (IN)
0999: cstmt.setInt(2, cursor);
1000: // Fetch type (IN)
1001: cstmt.setInt(3, 2); // Next row
1002: // Row number (INOUT)
1003: cstmt.setInt(4, 1); // Only matters for absolute and relative fetching
1004: // Number of rows (INOUT)
1005: cstmt.setInt(5, 2); // Read 2 rows
1006:
1007: // Fetch rows 1-2 (current row is 1)
1008: rs = cstmt.executeQuery();
1009: assertTrue(rs.next());
1010: assertTrue(rs.next());
1011: assertFalse(rs.next());
1012: rs.close();
1013: assertEquals(0, cstmt.getInt(1));
1014:
1015: // Fetch rows 3-4 (current row is 3)
1016: rs = cstmt.executeQuery();
1017: assertTrue(rs.next());
1018: assertTrue(rs.next());
1019: assertEquals(4, rs.getInt(1));
1020: assertFalse(rs.next());
1021: rs.close();
1022: assertEquals(0, cstmt.getInt(1));
1023:
1024: // Refresh rows 3-4 (current row is 3)
1025: cstmt.setInt(3, 0x80); // Refresh
1026: cstmt.setInt(4, 2); // Try to refresh only 2nd row (will be ignored)
1027: cstmt.setInt(5, 1); // Try to refresh only 1 row (will be ignored)
1028: rs = cstmt.executeQuery();
1029: assertTrue(rs.next());
1030: assertTrue(rs.next());
1031: assertEquals(4, rs.getInt(1));
1032: assertFalse(rs.next());
1033: rs.close();
1034: assertEquals(0, cstmt.getInt(1));
1035:
1036: // Fetch rows 5-6 (current row is 5)
1037: cstmt.setInt(3, 2); // Next
1038: cstmt.setInt(4, 1); // Row number 1
1039: cstmt.setInt(5, 2); // Get 2 rows
1040: rs = cstmt.executeQuery();
1041: assertTrue(rs.next());
1042: assertTrue(rs.next());
1043: assertEquals(6, rs.getInt(1));
1044: assertFalse(rs.next());
1045: rs.close();
1046: assertEquals(0, cstmt.getInt(1));
1047:
1048: // Fetch previous rows (3-4) (current row is 3)
1049: cstmt.setInt(3, 4); // Previous
1050: rs = cstmt.executeQuery();
1051: assertTrue(rs.next());
1052: assertEquals(3, rs.getInt(1));
1053: assertTrue(rs.next());
1054: assertEquals(4, rs.getInt(1));
1055: assertFalse(rs.next());
1056: rs.close();
1057: assertEquals(0, cstmt.getInt(1));
1058:
1059: // Refresh rows 3-4 (current row is 3)
1060: cstmt.setInt(3, 0x80); // Refresh
1061: rs = cstmt.executeQuery();
1062: assertTrue(rs.next());
1063: assertEquals(3, rs.getInt(1));
1064: assertTrue(rs.next());
1065: assertEquals(4, rs.getInt(1));
1066: assertFalse(rs.next());
1067: rs.close();
1068: assertEquals(0, cstmt.getInt(1));
1069:
1070: // Fetch previous rows (1-2) (current row is 1)
1071: cstmt.setInt(3, 4); // Previous
1072: rs = cstmt.executeQuery();
1073: assertTrue(rs.next());
1074: assertEquals(1, rs.getInt(1));
1075: assertTrue(rs.next());
1076: assertEquals(2, rs.getInt(1));
1077: assertFalse(rs.next());
1078: rs.close();
1079: assertEquals(0, cstmt.getInt(1));
1080:
1081: // Fetch next rows (3-4) (current row is 3)
1082: cstmt.setInt(3, 2); // Next
1083: rs = cstmt.executeQuery();
1084: assertTrue(rs.next());
1085: assertEquals(3, rs.getInt(1));
1086: assertTrue(rs.next());
1087: assertEquals(4, rs.getInt(1));
1088: assertFalse(rs.next());
1089: rs.close();
1090: assertEquals(0, cstmt.getInt(1));
1091:
1092: // Fetch first rows (1-2) (current row is 1)
1093: cstmt.setInt(3, 1); // First
1094: rs = cstmt.executeQuery();
1095: assertTrue(rs.next());
1096: assertEquals(1, rs.getInt(1));
1097: assertTrue(rs.next());
1098: assertEquals(2, rs.getInt(1));
1099: assertFalse(rs.next());
1100: rs.close();
1101: assertEquals(0, cstmt.getInt(1));
1102:
1103: // Fetch last rows (9-10) (current row is 9)
1104: cstmt.setInt(3, 8); // Last
1105: rs = cstmt.executeQuery();
1106: assertTrue(rs.next());
1107: assertEquals(9, rs.getInt(1));
1108: assertTrue(rs.next());
1109: assertEquals(10, rs.getInt(1));
1110: assertFalse(rs.next());
1111: rs.close();
1112: assertEquals(0, cstmt.getInt(1));
1113:
1114: // Fetch next rows; should not fail (current position is after last)
1115: cstmt.setInt(3, 2); // Next
1116: rs = cstmt.executeQuery();
1117: assertFalse(rs.next());
1118: rs.close();
1119: assertEquals(0, cstmt.getInt(1));
1120:
1121: // Fetch absolute starting with 6 (6-7) (current row is 6)
1122: cstmt.setInt(3, 0x10); // Absolute
1123: cstmt.setInt(4, 6);
1124: rs = cstmt.executeQuery();
1125: assertTrue(rs.next());
1126: assertEquals(6, rs.getInt(1));
1127: assertTrue(rs.next());
1128: assertEquals(7, rs.getInt(1));
1129: assertFalse(rs.next());
1130: rs.close();
1131: assertEquals(0, cstmt.getInt(1));
1132:
1133: // Fetch relative -4 (2-3) (current row is 2)
1134: cstmt.setInt(3, 0x20); // Relative
1135: cstmt.setInt(4, -4);
1136: rs = cstmt.executeQuery();
1137: assertTrue(rs.next());
1138: assertEquals(2, rs.getInt(1));
1139: assertTrue(rs.next());
1140: assertEquals(3, rs.getInt(1));
1141: assertFalse(rs.next());
1142: rs.close();
1143: assertEquals(0, cstmt.getInt(1));
1144:
1145: // Fetch previous 2 rows; should fail (current row is 1)
1146: cstmt.setInt(3, 4); // Previous
1147: rs = cstmt.executeQuery();
1148: assertTrue(rs.next());
1149: assertEquals(1, rs.getInt(1));
1150: assertTrue(rs.next());
1151: assertEquals(2, rs.getInt(1));
1152: assertFalse(rs.next());
1153: rs.close();
1154: // Returns 2 on error
1155: assertEquals(2, cstmt.getInt(1));
1156:
1157: // Fetch next rows (3-4) (current row is 3)
1158: cstmt.setInt(3, 2); // Next
1159: rs = cstmt.executeQuery();
1160: assertTrue(rs.next());
1161: assertEquals(3, rs.getInt(1));
1162: assertTrue(rs.next());
1163: assertEquals(4, rs.getInt(1));
1164: assertFalse(rs.next());
1165: rs.close();
1166: assertEquals(0, cstmt.getInt(1));
1167:
1168: cstmt.close();
1169:
1170: //
1171: // Close cursor
1172: //
1173: cstmt = con.prepareCall("{?=call sp_cursorclose(?)}");
1174: // Return value (OUT)
1175: cstmt.registerOutParameter(1, Types.INTEGER);
1176: // Cursor handle (IN)
1177: cstmt.setInt(2, cursor);
1178: assertFalse(cstmt.execute());
1179: assertEquals(0, cstmt.getInt(1));
1180: cstmt.close();
1181: }
1182:
1183: /**
1184: * Test that <code>absolute(-1)</code> works the same as <code>last()</code>.
1185: */
1186: public void testAbsoluteMinusOne() throws Exception {
1187: Statement stmt = con.createStatement(
1188: ResultSet.TYPE_SCROLL_INSENSITIVE,
1189: ResultSet.CONCUR_READ_ONLY);
1190:
1191: stmt
1192: .executeUpdate("create table #absoluteMinusOne (val int primary key)");
1193: stmt
1194: .executeUpdate("insert into #absoluteMinusOne (val) values (1)");
1195: stmt
1196: .executeUpdate("insert into #absoluteMinusOne (val) values (2)");
1197: stmt
1198: .executeUpdate("insert into #absoluteMinusOne (val) values (3)");
1199:
1200: ResultSet rs = stmt
1201: .executeQuery("select val from #absoluteMinusOne order by val");
1202:
1203: rs.absolute(-1);
1204: assertTrue(rs.isLast());
1205: assertEquals(3, rs.getInt(1));
1206: assertFalse(rs.next());
1207:
1208: rs.last();
1209: assertTrue(rs.isLast());
1210: assertEquals(3, rs.getInt(1));
1211: assertFalse(rs.next());
1212:
1213: rs.close();
1214: stmt.close();
1215: }
1216:
1217: /**
1218: * Test that calling <code>absolute()</code> with very large positive
1219: * values positions the cursor after the last row and with very large
1220: * negative values positions the cursor before the first row.
1221: */
1222: public void testAbsoluteLargeValue() throws SQLException {
1223: Statement stmt = con.createStatement(
1224: ResultSet.TYPE_SCROLL_INSENSITIVE,
1225: ResultSet.CONCUR_READ_ONLY);
1226:
1227: stmt
1228: .executeUpdate("create table #absoluteLargeValue (val int primary key)");
1229: stmt
1230: .executeUpdate("insert into #absoluteLargeValue (val) values (1)");
1231: stmt
1232: .executeUpdate("insert into #absoluteLargeValue (val) values (2)");
1233: stmt
1234: .executeUpdate("insert into #absoluteLargeValue (val) values (3)");
1235:
1236: ResultSet rs = stmt
1237: .executeQuery("select val from #absoluteLargeValue order by val");
1238:
1239: assertFalse(rs.absolute(10));
1240: assertEquals(0, rs.getRow());
1241: assertTrue(rs.isAfterLast());
1242: assertFalse(rs.next());
1243: assertEquals(0, rs.getRow());
1244: assertTrue(rs.isAfterLast());
1245:
1246: assertFalse(rs.absolute(-10));
1247: assertEquals(0, rs.getRow());
1248: assertTrue(rs.isBeforeFirst());
1249: assertFalse(rs.previous());
1250: assertEquals(0, rs.getRow());
1251: assertTrue(rs.isBeforeFirst());
1252:
1253: rs.close();
1254: stmt.close();
1255: }
1256:
1257: /**
1258: * Test that calling <code>absolute()</code> with very large positive
1259: * values positions the cursor after the last row and with very large
1260: * negative values positions the cursor before the first row.
1261: */
1262: public void testRelativeLargeValue() throws SQLException {
1263: Statement stmt = con.createStatement(
1264: ResultSet.TYPE_SCROLL_INSENSITIVE,
1265: ResultSet.CONCUR_READ_ONLY);
1266:
1267: stmt
1268: .executeUpdate("create table #relativeLargeValue (val int primary key)");
1269: stmt
1270: .executeUpdate("insert into #relativeLargeValue (val) values (1)");
1271: stmt
1272: .executeUpdate("insert into #relativeLargeValue (val) values (2)");
1273: stmt
1274: .executeUpdate("insert into #relativeLargeValue (val) values (3)");
1275:
1276: ResultSet rs = stmt
1277: .executeQuery("select val from #relativeLargeValue order by val");
1278:
1279: assertFalse(rs.relative(10));
1280: assertEquals(0, rs.getRow());
1281: assertTrue(rs.isAfterLast());
1282: assertFalse(rs.next());
1283: assertEquals(0, rs.getRow());
1284: assertTrue(rs.isAfterLast());
1285:
1286: assertFalse(rs.relative(-10));
1287: assertEquals(0, rs.getRow());
1288: assertTrue(rs.isBeforeFirst());
1289: assertFalse(rs.previous());
1290: assertEquals(0, rs.getRow());
1291: assertTrue(rs.isBeforeFirst());
1292:
1293: rs.close();
1294: stmt.close();
1295: }
1296:
1297: /**
1298: * Test that <code>read()</code> works ok on the stream returned by
1299: * <code>ResultSet.getUnicodeStream()</code> (i.e. it doesn't always fill
1300: * the buffer, regardless of whether there's available data or not).
1301: */
1302: public void testUnicodeStream() throws Exception {
1303: Statement stmt = con.createStatement();
1304: stmt
1305: .executeUpdate("create table #unicodeStream (val varchar(255))");
1306: stmt
1307: .executeUpdate("insert into #unicodeStream (val) values ('test')");
1308: ResultSet rs = stmt
1309: .executeQuery("select val from #unicodeStream");
1310:
1311: if (rs.next()) {
1312: byte[] buf = new byte[8000];
1313: InputStream is = rs.getUnicodeStream(1);
1314: int length = is.read(buf);
1315: assertEquals(4 * 2, length);
1316: }
1317:
1318: rs.close();
1319: stmt.close();
1320: }
1321:
1322: /**
1323: * Test that <code>Statement.setMaxRows()</code> works on cursor
1324: * <code>ResultSet</code>s.
1325: */
1326: public void testCursorMaxRows() throws Exception {
1327: Statement stmt = con.createStatement();
1328: stmt.executeUpdate("create table #cursorMaxRows (val int)");
1329: stmt.close();
1330:
1331: // Insert 10 rows
1332: PreparedStatement pstmt = con
1333: .prepareStatement("insert into #cursorMaxRows (val) values (?)");
1334: for (int i = 0; i < 10; i++) {
1335: pstmt.setInt(1, i);
1336: assertEquals(1, pstmt.executeUpdate());
1337: }
1338: pstmt.close();
1339:
1340: // Create a cursor ResultSet
1341: stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
1342: ResultSet.CONCUR_READ_ONLY);
1343: // Set maxRows to 5
1344: stmt.setMaxRows(5);
1345:
1346: // Select all (should only return 5 rows)
1347: ResultSet rs = stmt
1348: .executeQuery("select * from #cursorMaxRows");
1349: rs.last();
1350: assertEquals(5, rs.getRow());
1351: rs.beforeFirst();
1352:
1353: int cnt = 0;
1354: while (rs.next()) {
1355: cnt++;
1356: }
1357: assertEquals(5, cnt);
1358:
1359: rs.close();
1360: stmt.close();
1361: }
1362:
1363: /**
1364: * Test for bug [1075977] <code>setObject()</code> causes SQLException.
1365: * <p>
1366: * Conversion of <code>float</code> values to <code>String</code> adds
1367: * grouping to the value, which cannot then be parsed.
1368: */
1369: public void testSetObjectScale() throws Exception {
1370: Statement stmt = con.createStatement();
1371: stmt.execute("create table #testsetobj (i int)");
1372: PreparedStatement pstmt = con
1373: .prepareStatement("insert into #testsetobj values(?)");
1374: // next line causes sqlexception
1375: pstmt.setObject(1, new Float(1234.5667), Types.INTEGER, 0);
1376: assertEquals(1, pstmt.executeUpdate());
1377: ResultSet rs = stmt.executeQuery("select * from #testsetobj");
1378: assertTrue(rs.next());
1379: assertEquals("1234", rs.getString(1));
1380: }
1381:
1382: /**
1383: * Test that <code>ResultSet.previous()</code> works correctly on cursor
1384: * <code>ResultSet</code>s.
1385: */
1386: public void testCursorPrevious() throws Exception {
1387: Statement stmt = con.createStatement();
1388: stmt.executeUpdate("create table #cursorPrevious (val int)");
1389: stmt.close();
1390:
1391: // Insert 10 rows
1392: PreparedStatement pstmt = con
1393: .prepareStatement("insert into #cursorPrevious (val) values (?)");
1394: for (int i = 0; i < 10; i++) {
1395: pstmt.setInt(1, i);
1396: assertEquals(1, pstmt.executeUpdate());
1397: }
1398: pstmt.close();
1399:
1400: // Create a cursor ResultSet
1401: stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
1402: ResultSet.CONCUR_READ_ONLY);
1403: // Set fetch size to 2
1404: stmt.setFetchSize(2);
1405:
1406: // Select all
1407: ResultSet rs = stmt
1408: .executeQuery("select * from #cursorPrevious");
1409: rs.last();
1410: int i = 10;
1411: do {
1412: assertEquals(i, rs.getRow());
1413: assertEquals(--i, rs.getInt(1));
1414: } while (rs.previous());
1415: assertTrue(rs.isBeforeFirst());
1416: assertEquals(0, i);
1417:
1418: rs.close();
1419: stmt.close();
1420: }
1421:
1422: /**
1423: * Test the behavior of the ResultSet/Statement/Connection when the JVM
1424: * runs out of memory (hopefully) in the middle of a packet.
1425: * <p/>
1426: * Previously jTDS was not able to close a ResultSet/Statement/Connection
1427: * after an OutOfMemoryError because the input stream pointer usually
1428: * remained inside a packet and further attempts to dump the rest of the
1429: * response failed because of "protocol confusions".
1430: */
1431: public void testOutOfMemory() throws SQLException {
1432: Statement stmt = con.createStatement();
1433: stmt
1434: .executeUpdate("create table #testOutOfMemory (val binary(8000))");
1435:
1436: // Insert a 8KB value
1437: byte[] val = new byte[8000];
1438: PreparedStatement pstmt = con
1439: .prepareStatement("insert into #testOutOfMemory (val) values (?)");
1440: pstmt.setBytes(1, val);
1441: assertEquals(1, pstmt.executeUpdate());
1442: pstmt.close();
1443:
1444: // Create a list and keep adding rows to it until we run out of memory
1445: // Most probably this will happen in the middle of a row packet, when
1446: // jTDS tries to allocate the array, after reading the data length
1447: ArrayList results = new ArrayList();
1448: ResultSet rs = null;
1449: try {
1450: while (true) {
1451: rs = stmt
1452: .executeQuery("select val from #testOutOfMemory");
1453: assertTrue(rs.next());
1454: results.add(rs.getBytes(1));
1455: assertFalse(rs.next());
1456: rs.close();
1457: rs = null;
1458: }
1459: } catch (OutOfMemoryError err) {
1460: // Do not remove this. Although not really used, it will free
1461: // memory, avoiding another OutOfMemoryError
1462: results = null;
1463: if (rs != null) {
1464: // This used to fail, because the parser got confused
1465: rs.close();
1466: }
1467: }
1468:
1469: // Make sure the Statement still works
1470: rs = stmt.executeQuery("select 1");
1471: assertTrue(rs.next());
1472: assertFalse(rs.next());
1473: rs.close();
1474: stmt.close();
1475: }
1476:
1477: /**
1478: * Test for bug [1182066] regression bug resultset: relative() not working
1479: * as expected.
1480: */
1481: public void testRelative() throws Exception {
1482: final int ROW_COUNT = 99;
1483:
1484: Statement stmt = con.createStatement();
1485: stmt
1486: .executeUpdate("create table #test2 (i int primary key, v varchar(100))");
1487: for (int i = 1; i <= ROW_COUNT; i++) {
1488: stmt.executeUpdate("insert into #test2 (i, v) values (" + i
1489: + ", 'This is a test')");
1490: }
1491: stmt.close();
1492:
1493: String sql = "select * from #test2";
1494:
1495: PreparedStatement pstmt = con.prepareStatement(sql,
1496: ResultSet.TYPE_SCROLL_INSENSITIVE,
1497: ResultSet.CONCUR_READ_ONLY);
1498: pstmt.setFetchSize(10);
1499:
1500: ResultSet rs = pstmt.executeQuery();
1501:
1502: int resCnt = 0;
1503:
1504: if (rs.next()) {
1505: do {
1506: assertEquals(++resCnt, rs.getInt(1));
1507: } while (rs.relative(1));
1508: }
1509: assertEquals(ROW_COUNT, resCnt);
1510:
1511: if (rs.previous()) {
1512: do {
1513: assertEquals(resCnt--, rs.getInt(1));
1514: } while (rs.relative(-1));
1515: }
1516:
1517: pstmt.close();
1518: assertEquals(0, resCnt);
1519: }
1520:
1521: /**
1522: * Test that after updateRow() the cursor is positioned correctly.
1523: */
1524: public void testUpdateRowPosition() throws Exception {
1525: final int ROW_COUNT = 99;
1526: final int TEST_ROW = 33;
1527:
1528: Statement stmt = con.createStatement();
1529: stmt
1530: .executeUpdate("create table #testPos (i int primary key, v varchar(100))");
1531: for (int i = 1; i <= ROW_COUNT; i++) {
1532: stmt.executeUpdate("insert into #testPos (i, v) values ("
1533: + i + ", 'This is a test')");
1534: }
1535: stmt.close();
1536:
1537: String sql = "select * from #testPos order by i";
1538:
1539: PreparedStatement pstmt = con.prepareStatement(sql,
1540: ResultSet.TYPE_SCROLL_SENSITIVE,
1541: ResultSet.CONCUR_UPDATABLE);
1542: pstmt.setFetchSize(10);
1543:
1544: ResultSet rs = pstmt.executeQuery();
1545:
1546: for (int i = 1; i <= TEST_ROW; i++) {
1547: assertTrue(rs.next());
1548: assertEquals(i, rs.getInt(1));
1549: }
1550:
1551: // We're on TEST_ROW now
1552: assertEquals(TEST_ROW, rs.getRow());
1553: rs.updateString(2, "This is another test");
1554: rs.updateRow();
1555: assertEquals(TEST_ROW, rs.getRow());
1556: assertEquals(TEST_ROW, rs.getInt(1));
1557: rs.refreshRow();
1558: assertEquals(TEST_ROW, rs.getRow());
1559: assertEquals(TEST_ROW, rs.getInt(1));
1560:
1561: for (int i = TEST_ROW + 1; i <= ROW_COUNT; i++) {
1562: assertTrue(rs.next());
1563: assertEquals(i, rs.getInt(1));
1564: }
1565:
1566: pstmt.close();
1567: }
1568:
1569: /**
1570: * Test for bug [1197603] Cursor downgrade error in CachedResultSet --
1571: * updateable result sets were incorrectly downgraded to read only forward
1572: * only ones when client side cursors were used.
1573: */
1574: public void testUpdateableClientCursor() throws Exception {
1575: Statement stmt = con.createStatement();
1576: stmt.executeUpdate("create table #testUpdateableClientCursor "
1577: + "(i int primary key, v varchar(100))");
1578: stmt.executeUpdate("insert into #testUpdateableClientCursor "
1579: + "(i, v) values (1, 'This is a test')");
1580: stmt.close();
1581:
1582: // Use a statement that the server won't be able to create a cursor on
1583: String sql = "select * from #testUpdateableClientCursor where i = ?";
1584:
1585: PreparedStatement pstmt = con.prepareStatement(sql,
1586: ResultSet.TYPE_SCROLL_SENSITIVE,
1587: ResultSet.CONCUR_UPDATABLE);
1588: pstmt.setInt(1, 1);
1589: ResultSet rs = pstmt.executeQuery();
1590: assertTrue(rs.next());
1591: assertEquals(1, rs.getInt(1));
1592:
1593: assertNull(pstmt.getWarnings());
1594: rs.updateString(2, "This is another test");
1595: rs.updateRow();
1596: rs.close();
1597: pstmt.close();
1598:
1599: stmt = con.createStatement();
1600: rs = stmt
1601: .executeQuery("select * from #testUpdateableClientCursor");
1602: assertTrue(rs.next());
1603: assertEquals(1, rs.getInt(1));
1604: assertEquals("This is another test", rs.getString(2));
1605: assertFalse(rs.next());
1606: rs.close();
1607: stmt.close();
1608: }
1609:
1610: /**
1611: * Test bug with Sybase where readonly scrollable result set based on a
1612: * SELECT DISTINCT returns duplicate rows.
1613: */
1614: public void testDistinctBug() throws Exception {
1615: Statement stmt = con.createStatement(
1616: ResultSet.TYPE_SCROLL_INSENSITIVE,
1617: ResultSet.CONCUR_READ_ONLY);
1618: stmt
1619: .execute("CREATE TABLE #testdistinct (id int primary key, c varchar(255))");
1620: stmt.addBatch("INSERT INTO #testdistinct VALUES(1, 'AAAA')");
1621: stmt.addBatch("INSERT INTO #testdistinct VALUES(2, 'AAAA')");
1622: stmt.addBatch("INSERT INTO #testdistinct VALUES(3, 'BBBB')");
1623: stmt.addBatch("INSERT INTO #testdistinct VALUES(4, 'BBBB')");
1624: stmt.addBatch("INSERT INTO #testdistinct VALUES(5, 'CCCC')");
1625: int counts[] = stmt.executeBatch();
1626: assertEquals(5, counts.length);
1627:
1628: ResultSet rs = stmt
1629: .executeQuery("SELECT DISTINCT c FROM #testdistinct");
1630: assertNotNull(rs);
1631: int rowCount = 0;
1632: while (rs.next()) {
1633: rowCount++;
1634: }
1635: assertEquals(3, rowCount);
1636: stmt.close();
1637: }
1638:
1639: /**
1640: * Test pessimistic concurrency for SQL Server (for Sybase optimistic
1641: * concurrency will always be used).
1642: */
1643: public void testPessimisticConcurrency() throws Exception {
1644: dropTable("pessimisticConcurrency");
1645: Connection con2 = getConnection();
1646: Statement stmt = null;
1647: ResultSet rs = null;
1648: try {
1649: // Create statement using pessimistic locking.
1650: stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
1651: ResultSet.CONCUR_UPDATABLE + 1);
1652: stmt
1653: .execute("CREATE TABLE pessimisticConcurrency (id int primary key, data varchar(255))");
1654: for (int i = 0; i < 4; i++) {
1655: stmt
1656: .executeUpdate("INSERT INTO pessimisticConcurrency VALUES("
1657: + i + ", 'Table A line " + i + "')");
1658: }
1659:
1660: // Fetch one row at a time, making sure we know exactly which row is locked
1661: stmt.setFetchSize(1);
1662: // Open cursor
1663: rs = stmt
1664: .executeQuery("SELECT id, data FROM pessimisticConcurrency ORDER BY id");
1665: assertNull(rs.getWarnings());
1666: assertEquals(ResultSet.TYPE_SCROLL_SENSITIVE, rs.getType());
1667: assertEquals(ResultSet.CONCUR_UPDATABLE + 1, rs
1668: .getConcurrency());
1669: // If not a MSCursorResultSet, give up as no locking will happen
1670: if (rs.getClass().getName().indexOf("MSCursorResultSet") == -1) {
1671: rs.close();
1672: stmt.close();
1673: return;
1674: }
1675: // Scroll to and lock row 3
1676: for (int i = 0; i < 3; ++i) {
1677: rs.next();
1678: }
1679:
1680: // Create a second statement
1681: final Statement stmt2 = con2.createStatement(
1682: ResultSet.TYPE_SCROLL_SENSITIVE,
1683: ResultSet.CONCUR_UPDATABLE + 1);
1684:
1685: // No better idea to store exceptions
1686: final ArrayList container = new ArrayList();
1687: // Launch a thread that will cancel the second statement if it hangs.
1688: new Thread() {
1689: public void run() {
1690: try {
1691: sleep(1000);
1692: stmt2.cancel();
1693: } catch (Exception ex) {
1694: container.add(ex);
1695: }
1696: }
1697: }.start();
1698:
1699: // Open second cursor
1700: ResultSet rs2 = stmt2
1701: .executeQuery("SELECT id, data FROM pessimisticConcurrency WHERE id = 2");
1702: assertNull(rs2.getWarnings());
1703: assertEquals(ResultSet.TYPE_SCROLL_SENSITIVE, rs2.getType());
1704: assertEquals(ResultSet.CONCUR_UPDATABLE + 1, rs2
1705: .getConcurrency());
1706: try {
1707: System.out.println(rs2.next());
1708: } catch (SQLException ex) {
1709: ex.printStackTrace();
1710: System.out.println(ex.getNextException());
1711: if ("HY010".equals(ex.getSQLState())) {
1712: stmt2.getMoreResults();
1713: }
1714: if (!"HY008".equals(ex.getSQLState())
1715: && !"HY010".equals(ex.getSQLState())) {
1716: fail("Expecting cancel exception.");
1717: }
1718: }
1719: rs.close();
1720: stmt.close();
1721: rs2.close();
1722: stmt2.close();
1723:
1724: // Check for exceptions thrown in the cancel thread
1725: if (container.size() != 0) {
1726: throw (SQLException) container.get(0);
1727: }
1728: } finally {
1729: dropTable("pessimisticConcurrency");
1730: if (con2 != null) {
1731: con2.close();
1732: }
1733: }
1734: }
1735:
1736: /**
1737: * Test if dynamic cursors (<code>ResultSet.TYPE_SCROLL_SENSITIVE+1</code>)
1738: * see others' updates. SQL Server only.
1739: */
1740: public void testDynamicCursors() throws Exception {
1741: final int ROWS = 4;
1742: dropTable("dynamicCursors");
1743: Connection con2 = getConnection();
1744: try {
1745: Statement stmt = con.createStatement(
1746: ResultSet.TYPE_SCROLL_SENSITIVE + 1,
1747: ResultSet.CONCUR_READ_ONLY);
1748: stmt
1749: .execute("CREATE TABLE dynamicCursors (id int primary key, data varchar(255))");
1750: for (int i = 0; i < ROWS; i++) {
1751: stmt.executeUpdate("INSERT INTO dynamicCursors VALUES("
1752: + i + ", 'Table A line " + i + "')");
1753: }
1754:
1755: // Open cursor
1756: ResultSet rs = stmt
1757: .executeQuery("SELECT id, data FROM dynamicCursors");
1758: // If not a MSCursorResultSet, give up as it will not see inserts
1759: if (rs.getClass().getName().indexOf("MSCursorResultSet") == -1) {
1760: rs.close();
1761: stmt.close();
1762: return;
1763: }
1764:
1765: // Insert new row from other connection
1766: Statement stmt2 = con2.createStatement();
1767: assertEquals(1, stmt2
1768: .executeUpdate("INSERT INTO dynamicCursors VALUES("
1769: + ROWS + ", 'Table A line " + ROWS + "')"));
1770: stmt2.close();
1771:
1772: // Count rows and make sure the newly inserted row is visible
1773: int cnt;
1774: for (cnt = 0; rs.next(); cnt++)
1775: ;
1776: assertEquals(ROWS + 1, cnt);
1777:
1778: rs.close();
1779: stmt.close();
1780: } finally {
1781: dropTable("dynamicCursors");
1782: if (con2 != null) {
1783: con2.close();
1784: }
1785: }
1786: }
1787:
1788: /**
1789: * Test for bug [1232733] setFetchSize(0) causes exception.
1790: */
1791: public void testZeroFetchSize() throws Exception {
1792: Statement stmt = con.createStatement(
1793: ResultSet.TYPE_SCROLL_INSENSITIVE,
1794: ResultSet.CONCUR_READ_ONLY);
1795: stmt.setFetchSize(0);
1796:
1797: ResultSet rs = stmt.executeQuery("SELECT 1 UNION SELECT 2");
1798: assertTrue(rs.next());
1799:
1800: rs.setFetchSize(0);
1801: assertTrue(rs.next());
1802: assertFalse(rs.next());
1803:
1804: rs.close();
1805: stmt.close();
1806: }
1807:
1808: /**
1809: * Test for bug [1329765] Pseudo column ROWSTAT is back with SQL 2005
1810: * (September CTP).
1811: */
1812: public void testRowstat() throws Exception {
1813: PreparedStatement stmt = con.prepareStatement(
1814: "SELECT 'STRING' str",
1815: ResultSet.TYPE_SCROLL_INSENSITIVE,
1816: ResultSet.CONCUR_READ_ONLY);
1817: ResultSet rs = stmt.executeQuery();
1818:
1819: assertEquals(1, rs.getMetaData().getColumnCount());
1820: assertTrue(rs.next());
1821: assertFalse(rs.next());
1822:
1823: rs.close();
1824: stmt.close();
1825: }
1826:
1827: public static void main(String[] args) {
1828: junit.textui.TestRunner.run(ResultSetTest.class);
1829: }
1830: }
|