// jTDS JDBC Driver for Microsoft SQL Server and Sybase // Copyright (C) 2004 The jTDS Project // // This library is free software; you can redistribute it and/or // modify it under the terms of the GNU Lesser General Public // License as published by the Free Software Foundation; either // version 2.1 of the License, or (at your option) any later version. // // This library is distributed in the hope that it will be useful, // but WITHOUT ANY WARRANTY; without even the implied warranty of // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU // Lesser General Public License for more details. // // You should have received a copy of the GNU Lesser General Public // License along with this library; if not, write to the Free Software // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA // package net.sourceforge.jtds.jdbc; import java.sql.*; import java.math.BigDecimal; import java.io.InputStream; import java.util.ArrayList; import java.util.List; /** * @version 1.0 */ public class ResultSetTest extends DatabaseTestCase { public ResultSetTest(String name) { super(name); } /** * Test for bug #644, updateNull not working with MSSQL DECIMAL field. */ public void testBug644() throws Exception { /* * The test used to fail on SQL server 6.5 and 7 only, because the * precision of numeric and decimal data types is 38 in SQL 2000 and above * but in previous versions of SQL Server, the default maximum was 28. */ Statement sta = con.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE ); sta.executeUpdate( "create table #Bug644( A int not null, B decimal(10,2) null)" ); ResultSet res = sta.executeQuery( "select * from #Bug644" ); res.moveToInsertRow(); res.updateInt( "A", 1 ); res.updateNull( "B" ); res.insertRow(); res = sta.executeQuery( "select * from #Bug644" ); assertTrue( res.next() ); assertEquals( 1, res.getInt( 1 ) ); assertFalse( res.wasNull() ); assertEquals( 0, res.getInt( 2 ) ); assertTrue( res.wasNull() ); assertFalse( res.next() ); res.close(); sta.close(); } /** * Test for bug #690, IS_AUTOINCREMENT invalid for identity columns. */ public void testAutoIncrement() throws Exception { // cannot retrieve meta data for temporary tables dropTable( "Bug690" ); Statement sta = con.createStatement(); sta.executeUpdate( "create table Bug690( A int identity not null, B int)" ); DatabaseMetaData dbm = con.getMetaData(); // get and check database meta data ResultSet res = dbm.getColumns( null, null, "Bug690", null ); assertTrue( res.next() ); assertEquals( res.getString( "COLUMN_NAME" ), "A" ); assertEquals( res.getString( "IS_AUTOINCREMENT" ), "YES" ); assertTrue( res.next() ); assertEquals( res.getString( "COLUMN_NAME" ), "B" ); assertEquals( res.getString( "IS_AUTOINCREMENT" ), "NO" ); assertFalse( res.next() ); res.close(); // insert test data for( int i = 1; i <= 10; i ++ ) { assertEquals( 1, sta.executeUpdate( "insert into Bug690(B) values (" + i + ")" ) ); } res = sta.executeQuery( "select * from Bug690 order by B asc " ); ResultSetMetaData rsmd = res.getMetaData(); // ensure resultset meta data reports column as autoIncrement assertTrue( rsmd.isAutoIncrement( 1 ) ); // check data for( int i = 1; i <= 10; i ++ ) { assertTrue( res.next() ); assertEquals( i, res.getInt( 1 ) ); assertEquals( i, res.getInt( 2 ) ); } assertFalse( res.next() ); res.close(); } /** * Test BIT data type. */ public void testGetObject1() throws Exception { boolean data = true; Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #getObject1 (data BIT, minval BIT, maxval BIT)"); stmt.close(); PreparedStatement pstmt = con.prepareStatement("INSERT INTO #getObject1 (data, minval, maxval) VALUES (?, ?, ?)"); pstmt.setBoolean(1, data); pstmt.setBoolean(2, false); pstmt.setBoolean(3, true); assertEquals(1, pstmt.executeUpdate()); pstmt.close(); Statement stmt2 = con.createStatement(); ResultSet rs = stmt2.executeQuery("SELECT data, minval, maxval FROM #getObject1"); assertTrue(rs.next()); assertTrue(rs.getBoolean(1)); assertTrue(rs.getByte(1) == 1); assertTrue(rs.getShort(1) == 1); assertTrue(rs.getInt(1) == 1); assertTrue(rs.getLong(1) == 1); assertTrue(rs.getFloat(1) == 1); assertTrue(rs.getDouble(1) == 1); assertTrue(rs.getBigDecimal(1).byteValue() == 1); assertEquals("1", rs.getString(1)); Object tmpData = rs.getObject(1); assertTrue(tmpData instanceof Boolean); assertEquals(true, ((Boolean) tmpData).booleanValue()); ResultSetMetaData resultSetMetaData = rs.getMetaData(); assertNotNull(resultSetMetaData); assertEquals(Types.BIT, resultSetMetaData.getColumnType(1)); assertFalse(rs.getBoolean(2)); assertTrue(rs.getBoolean(3)); assertFalse(rs.next()); stmt2.close(); rs.close(); } /** * Test TINYINT data type. */ public void testGetObject2() throws Exception { byte data = 1; Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #getObject2 (data TINYINT, minval TINYINT, maxval TINYINT)"); stmt.close(); PreparedStatement pstmt = con.prepareStatement("INSERT INTO #getObject2 (data, minval, maxval) VALUES (?, ?, ?)"); pstmt.setByte(1, data); pstmt.setByte(2, (byte)0); pstmt.setShort(3, (short)255); assertEquals(1, pstmt.executeUpdate()); pstmt.close(); Statement stmt2 = con.createStatement(); ResultSet rs = stmt2.executeQuery("SELECT data, minval, maxval FROM #getObject2"); assertTrue(rs.next()); assertTrue(rs.getBoolean(1)); assertTrue(rs.getByte(1) == 1); assertTrue(rs.getShort(1) == 1); assertTrue(rs.getInt(1) == 1); assertTrue(rs.getLong(1) == 1); assertTrue(rs.getFloat(1) == 1); assertTrue(rs.getDouble(1) == 1); assertTrue(rs.getBigDecimal(1).byteValue() == 1); assertEquals("1", rs.getString(1)); Object tmpData = rs.getObject(1); assertTrue(tmpData instanceof Integer); assertEquals(data, ((Integer) tmpData).byteValue()); ResultSetMetaData resultSetMetaData = rs.getMetaData(); assertNotNull(resultSetMetaData); assertEquals(Types.TINYINT, resultSetMetaData.getColumnType(1)); assertEquals(rs.getByte(2), 0); assertEquals(rs.getShort(3), 255); assertFalse(rs.next()); stmt2.close(); rs.close(); } /** * Test SMALLINT data type. */ public void testGetObject3() throws Exception { short data = 1; Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #getObject3 (data SMALLINT, minval SMALLINT, maxval SMALLINT)"); stmt.close(); PreparedStatement pstmt = con.prepareStatement("INSERT INTO #getObject3 (data, minval, maxval) VALUES (?, ?, ?)"); pstmt.setShort(1, data); pstmt.setShort(2, Short.MIN_VALUE); pstmt.setShort(3, Short.MAX_VALUE); assertEquals(1, pstmt.executeUpdate()); pstmt.close(); Statement stmt2 = con.createStatement(); ResultSet rs = stmt2.executeQuery("SELECT data, minval, maxval FROM #getObject3"); assertTrue(rs.next()); assertTrue(rs.getBoolean(1)); assertTrue(rs.getByte(1) == 1); assertTrue(rs.getShort(1) == 1); assertTrue(rs.getInt(1) == 1); assertTrue(rs.getLong(1) == 1); assertTrue(rs.getFloat(1) == 1); assertTrue(rs.getDouble(1) == 1); assertTrue(rs.getBigDecimal(1).shortValue() == 1); assertEquals("1", rs.getString(1)); Object tmpData = rs.getObject(1); assertTrue(tmpData instanceof Integer); assertEquals(data, ((Integer) tmpData).shortValue()); ResultSetMetaData resultSetMetaData = rs.getMetaData(); assertNotNull(resultSetMetaData); assertEquals(Types.SMALLINT, resultSetMetaData.getColumnType(1)); assertEquals(rs.getShort(2), Short.MIN_VALUE); assertEquals(rs.getShort(3), Short.MAX_VALUE); assertFalse(rs.next()); stmt2.close(); rs.close(); } /** * Test INT data type. */ public void testGetObject4() throws Exception { int data = 1; Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #getObject4 (data INT, minval INT, maxval INT)"); stmt.close(); PreparedStatement pstmt = con.prepareStatement("INSERT INTO #getObject4 (data, minval, maxval) VALUES (?, ?, ?)"); pstmt.setInt(1, data); pstmt.setInt(2, Integer.MIN_VALUE); pstmt.setInt(3, Integer.MAX_VALUE); assertEquals(1, pstmt.executeUpdate()); pstmt.close(); Statement stmt2 = con.createStatement(); ResultSet rs = stmt2.executeQuery("SELECT data, minval, maxval FROM #getObject4"); assertTrue(rs.next()); assertTrue(rs.getBoolean(1)); assertTrue(rs.getByte(1) == 1); assertTrue(rs.getShort(1) == 1); assertTrue(rs.getInt(1) == 1); assertTrue(rs.getLong(1) == 1); assertTrue(rs.getFloat(1) == 1); assertTrue(rs.getDouble(1) == 1); assertTrue(rs.getBigDecimal(1).intValue() == 1); assertEquals("1", rs.getString(1)); Object tmpData = rs.getObject(1); assertTrue(tmpData instanceof Integer); assertEquals(data, ((Integer) tmpData).intValue()); ResultSetMetaData resultSetMetaData = rs.getMetaData(); assertNotNull(resultSetMetaData); assertEquals(Types.INTEGER, resultSetMetaData.getColumnType(1)); assertEquals(rs.getInt(2), Integer.MIN_VALUE); assertEquals(rs.getInt(3), Integer.MAX_VALUE); assertFalse(rs.next()); stmt2.close(); rs.close(); } /** * Test BIGINT data type. */ public void testGetObject5() throws Exception { long data = 1; Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #getObject5 (data DECIMAL(28, 0), minval DECIMAL(28, 0), maxval DECIMAL(28, 0))"); stmt.close(); PreparedStatement pstmt = con.prepareStatement("INSERT INTO #getObject5 (data, minval, maxval) VALUES (?, ?, ?)"); pstmt.setLong(1, data); pstmt.setLong(2, Long.MIN_VALUE); pstmt.setLong(3, Long.MAX_VALUE); assertEquals(1, pstmt.executeUpdate()); pstmt.close(); Statement stmt2 = con.createStatement(); ResultSet rs = stmt2.executeQuery("SELECT data, minval, maxval FROM #getObject5"); assertTrue(rs.next()); assertTrue(rs.getBoolean(1)); assertTrue(rs.getByte(1) == 1); assertTrue(rs.getShort(1) == 1); assertTrue(rs.getInt(1) == 1); assertTrue(rs.getLong(1) == 1); assertTrue(rs.getFloat(1) == 1); assertTrue(rs.getDouble(1) == 1); assertTrue(rs.getBigDecimal(1).longValue() == 1); assertEquals("1", rs.getString(1)); Object tmpData = rs.getObject(1); assertTrue(tmpData instanceof BigDecimal); assertEquals(data, ((BigDecimal) tmpData).longValue()); ResultSetMetaData resultSetMetaData = rs.getMetaData(); assertNotNull(resultSetMetaData); assertEquals(Types.DECIMAL, resultSetMetaData.getColumnType(1)); assertEquals(rs.getLong(2), Long.MIN_VALUE); assertEquals(rs.getLong(3), Long.MAX_VALUE); assertFalse(rs.next()); stmt2.close(); rs.close(); } /** * Test for the problem reported in feature request #78, columns of type DATE * are returned as String. */ public void testDateType() throws Exception { Statement stm = con.createStatement(); boolean dateSupported = false; try { stm.executeUpdate( "create table #FeatureRequest78 ( A date )" ); dateSupported = true; } catch( SQLException e ) { // date type not supported, skip test } if( dateSupported ) { stm.executeUpdate( "insert into #FeatureRequest78 values ( '2009-09-03' )" ); ResultSet rs = stm.executeQuery( "select * from #FeatureRequest78" ); assertTrue( rs.next() ); ResultSetMetaData rsmd = rs.getMetaData(); assertEquals( Types.DATE, rsmd.getColumnType( 1 ) ); assertTrue( rs.getObject( 1 ).getClass() + " cannot be assigned to "+ Date.class, Date.class.isAssignableFrom( rs.getObject( 1 ).getClass() ) ); rs.close(); } stm.close(); } /** * Test for bug [961594] ResultSet. */ public void testResultSetScroll1() throws Exception { int count = 125; Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #resultSetScroll1 (data INT)"); stmt.close(); PreparedStatement pstmt = con.prepareStatement("INSERT INTO #resultSetScroll1 (data) VALUES (?)"); for (int i = 1; i <= count; i++) { pstmt.setInt(1, i); assertEquals(1, pstmt.executeUpdate()); } pstmt.close(); Statement stmt2 = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = stmt2.executeQuery("SELECT data FROM #resultSetScroll1"); assertTrue(rs.last()); assertEquals(count, rs.getRow()); stmt2.close(); rs.close(); } /** * Test for bug [945462] getResultSet() return null if you use scrollable/updatable. */ public void testResultSetScroll2() throws Exception { Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #resultSetScroll2 (data INT)"); stmt.close(); PreparedStatement pstmt = con.prepareStatement("INSERT INTO #resultSetScroll2 (data) VALUES (?)"); pstmt.setInt(1, 1); assertEquals(1, pstmt.executeUpdate()); pstmt.close(); Statement stmt2 = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt2.executeQuery("SELECT data FROM #resultSetScroll2"); ResultSet rs = stmt2.getResultSet(); assertNotNull(rs); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); assertFalse(rs.next()); stmt2.close(); rs.close(); } /** * Test for bug [1028881] statement.execute() causes wrong ResultSet type. */ public void testResultSetScroll3() throws Exception { dropProcedure( "procResultSetScroll3" ); dropTable( "resultSetScroll3" ); Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE resultSetScroll3 (data INT)"); stmt.execute("CREATE PROCEDURE procResultSetScroll3 AS SELECT data FROM resultSetScroll3"); stmt.close(); PreparedStatement pstmt = con.prepareStatement("INSERT INTO resultSetScroll3 (data) VALUES (?)"); pstmt.setInt(1, 1); assertEquals(1, pstmt.executeUpdate()); pstmt.close(); // Test plain Statement Statement stmt2 = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); assertTrue("Was expecting a ResultSet", stmt2.execute("SELECT data FROM resultSetScroll3")); ResultSet rs = stmt2.getResultSet(); assertEquals("ResultSet not scrollable", ResultSet.TYPE_SCROLL_INSENSITIVE, rs.getType()); rs.close(); stmt2.close(); // Test PreparedStatement pstmt = con.prepareStatement("SELECT data FROM resultSetScroll3", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); assertTrue("Was expecting a ResultSet", pstmt.execute()); rs = pstmt.getResultSet(); assertEquals("ResultSet not scrollable", ResultSet.TYPE_SCROLL_INSENSITIVE, rs.getType()); rs.close(); pstmt.close(); // Test CallableStatement CallableStatement cstmt = con.prepareCall("{call procResultSetScroll3}", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); assertTrue("Was expecting a ResultSet", cstmt.execute()); rs = cstmt.getResultSet(); assertEquals("ResultSet not scrollable", ResultSet.TYPE_SCROLL_INSENSITIVE, rs.getType()); rs.close(); cstmt.close(); } /** * Test for bug [1008208] 0.9-rc1 updateNull doesn't work. */ public void testResultSetUpdate1() throws Exception { Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #resultSetUpdate1 (id INT PRIMARY KEY, dsi SMALLINT NULL, di INT NULL)"); stmt.close(); PreparedStatement pstmt = con.prepareStatement("INSERT INTO #resultSetUpdate1 (id, dsi, di) VALUES (?, ?, ?)"); pstmt.setInt(1, 1); pstmt.setShort(2, (short) 1); pstmt.setInt(3, 1); assertEquals(1, pstmt.executeUpdate()); pstmt.close(); stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); stmt.executeQuery("SELECT id, dsi, di FROM #resultSetUpdate1"); ResultSet rs = stmt.getResultSet(); assertNotNull(rs); assertTrue(rs.next()); rs.updateNull("dsi"); rs.updateNull("di"); rs.updateRow(); rs.moveToInsertRow(); rs.updateInt(1, 2); rs.updateNull("dsi"); rs.updateNull("di"); rs.insertRow(); stmt.close(); rs.close(); stmt = con.createStatement(); stmt.executeQuery("SELECT id, dsi, di FROM #resultSetUpdate1 ORDER BY id"); rs = stmt.getResultSet(); assertNotNull(rs); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); rs.getShort(2); assertTrue(rs.wasNull()); rs.getInt(3); assertTrue(rs.wasNull()); assertTrue(rs.next()); assertEquals(2, rs.getInt(1)); rs.getShort(2); assertTrue(rs.wasNull()); rs.getInt(3); assertTrue(rs.wasNull()); assertFalse(rs.next()); stmt.close(); rs.close(); } /** * Test for bug [1009233] ResultSet getColumnName, getColumnLabel return wrong values */ public void testResultSetColumnName1() throws Exception { Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #resultSetCN1 (data INT)"); stmt.close(); PreparedStatement pstmt = con.prepareStatement("INSERT INTO #resultSetCN1 (data) VALUES (?)"); pstmt.setInt(1, 1); assertEquals(1, pstmt.executeUpdate()); pstmt.close(); Statement stmt2 = con.createStatement(); stmt2.executeQuery("SELECT data as test FROM #resultSetCN1"); ResultSet rs = stmt2.getResultSet(); assertNotNull(rs); assertTrue(rs.next()); assertEquals(1, rs.getInt("test")); assertFalse(rs.next()); stmt2.close(); rs.close(); } /** * Test for fixed bugs in ResultSetMetaData: * <ol> * <li>isNullable() always returns columnNoNulls. * <li>isSigned returns true in error for TINYINT columns. * <li>Type names for numeric / decimal have (prec,scale) appended in error. * <li>Type names for auto increment columns do not have "identity" appended. * <li>table names are empty when not using cursors, bug [1833720]</li> * </ol> * NB: This test assumes getColumnName has been fixed to work as per the suggestion * in bug report [1009233]. * * @throws Exception */ public void testResultSetMetaData() throws Exception { // Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #TRSMD (id INT IDENTITY NOT NULL, byte TINYINT NOT NULL, num DECIMAL(28,10) NULL)"); ResultSetMetaData rsmd = stmt.executeQuery("SELECT id as idx, byte, num FROM #TRSMD").getMetaData(); assertNotNull(rsmd); // Check id assertEquals("idx", rsmd.getColumnName(1)); // no longer returns base name assertEquals("idx", rsmd.getColumnLabel(1)); assertTrue(rsmd.isAutoIncrement(1)); assertTrue(rsmd.isSigned(1)); assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(1)); assertEquals("int identity", rsmd.getColumnTypeName(1)); assertEquals(Types.INTEGER, rsmd.getColumnType(1)); // Check byte assertFalse(rsmd.isAutoIncrement(2)); assertFalse(rsmd.isSigned(2)); assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(2)); assertEquals("tinyint", rsmd.getColumnTypeName(2)); assertEquals(Types.TINYINT, rsmd.getColumnType(2)); // Check num assertFalse(rsmd.isAutoIncrement(3)); assertTrue(rsmd.isSigned(3)); assertEquals(ResultSetMetaData.columnNullable, rsmd.isNullable(3)); assertEquals("decimal", rsmd.getColumnTypeName(3)); assertEquals(Types.DECIMAL, rsmd.getColumnType(3)); assertEquals("#TRSMD", rsmd.getTableName(1)); stmt.close(); } /** * Test for bug [1022445] Cursor downgrade warning not raised. */ public void testCursorWarning() throws Exception { dropProcedure( "SPTESTCW" ); dropTable( "TESTCW" ); Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE TESTCW (id INT PRIMARY KEY, DATA VARCHAR(255))"); stmt.execute("CREATE PROC SPTESTCW @P0 INT OUTPUT AS SELECT * FROM TESTCW"); stmt.close(); CallableStatement cstmt = con.prepareCall("{call SPTESTCW(?)}", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); cstmt.registerOutParameter(1, Types.INTEGER); ResultSet rs = cstmt.executeQuery(); // This should generate a ResultSet type/concurrency downgraded error. assertNotNull(rs.getWarnings()); cstmt.close(); } /** * Test that the cursor fallback logic correctly discriminates between * "real" sql errors and cursor open failures. * <p/> * This illustrates the logic added to fix: * <ol> * <li>[1323363] Deadlock Exception not reported (SQL Server)</li> * <li>[1283472] Unable to cancel statement with cursor resultset</li> * </ol> */ public void testCursorFallback() throws Exception { dropProcedure( "testcursor" ); Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); // // This test should fail on the cursor open but fall back to normal // execution returning two result sets // stmt.execute("CREATE PROC testcursor as SELECT 'data' select 'data2'"); stmt.execute("exec testcursor"); assertNotNull(stmt.getWarnings()); ResultSet rs = stmt.getResultSet(); assertNotNull(rs); // First result set OK assertTrue(stmt.getMoreResults()); rs = stmt.getResultSet(); assertNotNull(rs); // Second result set OK // // This test should fail on the cursor open (because of the for browse) // but fall back to normal execution returning a single result set // rs = stmt.executeQuery("SELECT description FROM master..sysmessages FOR BROWSE"); assertNotNull(rs); assertNotNull(rs.getWarnings()); rs.close(); // // Enable logging to see that this test should just fail without // attempting to fall back on normal execution. // // DriverManager.setLogStream(System.out); try { stmt.executeQuery("select bad from syntax"); fail("Expected SQLException"); } catch (SQLException e) { assertEquals("S0002", e.getSQLState()); } // DriverManager.setLogStream(null); stmt.close(); } /** * Test for bug [1246270] Closing a statement after canceling it throws an * exception. */ public void testCancelResultSet() throws Exception { Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #TEST (id int primary key, data varchar(255))"); for (int i = 1; i < 1000; i++) { stmt.executeUpdate("INSERT INTO #TEST VALUES (" + i + ", 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" + "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')"); } ResultSet rs = stmt.executeQuery("SELECT * FROM #TEST"); assertNotNull(rs); assertTrue(rs.next()); stmt.cancel(); stmt.close(); } /** * Test whether retrieval by name returns the first occurrence (that's what * the spec requires). */ public void testGetByName() throws Exception { // Sybase ASE doesn't support duplicate column names if( isMSSQL() ) { Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT 1 myCol, 2 myCol, 3 myCol" ); assertTrue( rs.next() ); assertEquals( 1, rs.getInt( "myCol" ) ); assertFalse( rs.next() ); stmt.close(); } } /** * Test if COL_INFO packets are processed correctly for * <code>ResultSet</code>s with over 255 columns. */ public void testMoreThan255Columns() throws Exception { Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); // create the table int cols = 260; StringBuilder create = new StringBuilder("create table #manycolumns ("); for (int i=0; i<cols; ++i) { create.append("col" + i + " char(10), ") ; } create.append(")"); stmt.executeUpdate(create.toString()); String query = "select * from #manycolumns"; ResultSet rs = stmt.executeQuery(query); rs.close(); stmt.close(); } /** * Test that <code>insertRow()</code> works with no values set. */ public void testEmptyInsertRow() throws Exception { int rows = 10; Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); stmt.executeUpdate( "create table #emptyInsertRow (id int identity, val int default 10)"); ResultSet rs = stmt.executeQuery("select * from #emptyInsertRow"); for (int i=0; i<rows; i++) { rs.moveToInsertRow(); rs.insertRow(); } rs.close(); rs = stmt.executeQuery("select count(*) from #emptyInsertRow"); assertTrue(rs.next()); assertEquals(rows, rs.getInt(1)); rs.close(); rs = stmt.executeQuery("select * from #emptyInsertRow order by id"); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); assertEquals(10, rs.getInt(2)); rs.close(); stmt.close(); } /** * Test that inserted rows are visible in a scroll sensitive * <code>ResultSet</code> and that they show up at the end. */ public void testInsertRowVisible() throws Exception { int rows = 10; Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt.executeUpdate( "create table #insertRowNotVisible (val int primary key)"); ResultSet rs = stmt.executeQuery("select * from #insertRowNotVisible"); for (int i = 1; i <= rows; i++) { rs.moveToInsertRow(); rs.updateInt(1, i); rs.insertRow(); rs.moveToCurrentRow(); rs.last(); assertEquals(i, rs.getRow()); } rs.close(); stmt.close(); } /** * Test that updated rows are marked as deleted and the new values inserted * at the end of the <code>ResultSet</code> if the primary key is updated. */ public void testUpdateRowDuplicatesRow() throws Exception { Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt.executeUpdate( "create table #updateRowDuplicatesRow (val int primary key)"); stmt.executeUpdate( "insert into #updateRowDuplicatesRow (val) values (1)"); stmt.executeUpdate( "insert into #updateRowDuplicatesRow (val) values (2)"); stmt.executeUpdate( "insert into #updateRowDuplicatesRow (val) values (3)"); ResultSet rs = stmt.executeQuery( "select val from #updateRowDuplicatesRow order by val"); for (int i = 0; i < 3; i++) { assertTrue(rs.next()); assertFalse(rs.rowUpdated()); assertFalse(rs.rowInserted()); assertFalse(rs.rowDeleted()); rs.updateInt(1, rs.getInt(1) + 10); rs.updateRow(); assertFalse(rs.rowUpdated()); assertFalse(rs.rowInserted()); assertTrue(rs.rowDeleted()); } for (int i = 11; i <= 13; i++) { assertTrue(rs.next()); assertFalse(rs.rowUpdated()); assertFalse(rs.rowInserted()); assertFalse(rs.rowDeleted()); assertEquals(i, rs.getInt(1)); } rs.close(); stmt.close(); } /** * Test that updated rows are modified in place if the primary key is not * updated. */ public void testUpdateRowUpdatesRow() throws Exception { Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt.executeUpdate( "create table #updateRowUpdatesRow (id int primary key, val int)"); stmt.executeUpdate( "insert into #updateRowUpdatesRow (id, val) values (1, 1)"); stmt.executeUpdate( "insert into #updateRowUpdatesRow (id, val) values (2, 2)"); stmt.executeUpdate( "insert into #updateRowUpdatesRow (id, val) values (3, 3)"); ResultSet rs = stmt.executeQuery( "select id, val from #updateRowUpdatesRow order by id"); for (int i = 0; i < 3; i++) { assertTrue(rs.next()); assertFalse(rs.rowUpdated()); assertFalse(rs.rowInserted()); assertFalse(rs.rowDeleted()); rs.updateInt(2, rs.getInt(2) + 10); rs.updateRow(); assertFalse(rs.rowUpdated()); assertFalse(rs.rowInserted()); assertFalse(rs.rowDeleted()); assertEquals(rs.getInt(1) + 10, rs.getInt(2)); } assertFalse(rs.next()); rs.close(); stmt.close(); } /** * Test that deleted rows are not removed but rather marked as deleted. */ public void testDeleteRowMarksDeleted() throws Exception { Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt.executeUpdate( "create table #deleteRowMarksDeleted (val int primary key)"); stmt.executeUpdate( "insert into #deleteRowMarksDeleted (val) values (1)"); stmt.executeUpdate( "insert into #deleteRowMarksDeleted (val) values (2)"); stmt.executeUpdate( "insert into #deleteRowMarksDeleted (val) values (3)"); ResultSet rs = stmt.executeQuery( "select val from #deleteRowMarksDeleted order by val"); for (int i = 0; i < 3; i++) { assertTrue(rs.next()); assertFalse(rs.rowUpdated()); assertFalse(rs.rowInserted()); assertFalse(rs.rowDeleted()); rs.deleteRow(); assertFalse(rs.rowUpdated()); assertFalse(rs.rowInserted()); assertTrue(rs.rowDeleted()); } assertFalse(rs.next()); rs.close(); stmt.close(); } /** * Test for bug [1170777] resultSet.updateRow() fails if no row has been * changed. */ public void testUpdateRowNoChanges() throws Exception { Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt.executeUpdate( "create table #deleteRowMarksDeleted (val int primary key)"); stmt.executeUpdate( "insert into #deleteRowMarksDeleted (val) values (1)"); ResultSet rs = stmt.executeQuery( "select val from #deleteRowMarksDeleted order by val"); assertTrue(rs.next()); // This should not crash; it should be a no-op rs.updateRow(); rs.refreshRow(); assertEquals(1, rs.getInt(1)); assertFalse(rs.next()); rs.close(); stmt.close(); } /** * Test the behavior of <code>sp_cursorfetch</code> with fetch sizes * greater than 1. * <p> * <b>Assertions tested:</b> * <ul> * <li>The <i>current row</i> is always the first row returned by the * last fetch, regardless of what fetch type was used. * <li>Row number parameter is ignored by fetch types other than absolute * and relative. * <li>Refresh fetch type simply reruns the previous request (it ignores * both row number and number of rows) and will not affect the * <i>current row</i>. * <li>Fetch next returns the packet of rows right after the last row * returned by the last fetch (regardless of what type of fetch that * was). * <li>Fetch previous returns the packet of rows right before the first * row returned by the last fetch (regardless of what type of fetch * that was). * <li>If a fetch previous tries to read before the start of the * <code>ResultSet</code> the requested number of rows is returned, * starting with row 1 and the error code returned is non-zero (2). * </ul> */ public void testCursorFetch() throws Exception { int rows = 10; Statement stmt = con.createStatement(); stmt.executeUpdate( "create table #testCursorFetch (id int primary key, val int)"); stmt.close(); PreparedStatement pstmt = con.prepareStatement( "insert into #testCursorFetch (id, val) values (?, ?)"); for (int i = 1; i <= rows; i++) { pstmt.setInt(1, i); pstmt.setInt(2, i); pstmt.executeUpdate(); } pstmt.close(); // // Open cursor // CallableStatement cstmt = con.prepareCall( "{?=call sp_cursoropen(?, ?, ?, ?, ?)}"); // Return value (OUT) cstmt.registerOutParameter(1, Types.INTEGER); // Cursor handle (OUT) cstmt.registerOutParameter(2, Types.INTEGER); // Statement (IN) cstmt.setString(3, "select * from #testCursorFetch order by id"); // Scroll options (INOUT) cstmt.setInt(4, 1); // Keyset driven cstmt.registerOutParameter(4, Types.INTEGER); // Concurrency options (INOUT) cstmt.setInt(5, 2); // Scroll locks cstmt.registerOutParameter(5, Types.INTEGER); // Row count (OUT) cstmt.registerOutParameter(6, Types.INTEGER); ResultSet rs = cstmt.executeQuery(); assertEquals(2, rs.getMetaData().getColumnCount()); assertFalse(rs.next()); assertEquals(0, cstmt.getInt(1)); int cursor = cstmt.getInt(2); assertEquals(1, cstmt.getInt(4)); assertEquals(2, cstmt.getInt(5)); assertEquals(rows, cstmt.getInt(6)); cstmt.close(); // // Play around with fetch // cstmt = con.prepareCall("{?=call sp_cursorfetch(?, ?, ?, ?)}"); // Return value (OUT) cstmt.registerOutParameter(1, Types.INTEGER); // Cursor handle (IN) cstmt.setInt(2, cursor); // Fetch type (IN) cstmt.setInt(3, 2); // Next row // Row number (INOUT) cstmt.setInt(4, 1); // Only matters for absolute and relative fetching // Number of rows (INOUT) cstmt.setInt(5, 2); // Read 2 rows // Fetch rows 1-2 (current row is 1) rs = cstmt.executeQuery(); assertTrue(rs.next()); assertTrue(rs.next()); assertFalse(rs.next()); rs.close(); assertEquals(0, cstmt.getInt(1)); // Fetch rows 3-4 (current row is 3) rs = cstmt.executeQuery(); assertTrue(rs.next()); assertTrue(rs.next()); assertEquals(4, rs.getInt(1)); assertFalse(rs.next()); rs.close(); assertEquals(0, cstmt.getInt(1)); // Refresh rows 3-4 (current row is 3) cstmt.setInt(3, 0x80); // Refresh cstmt.setInt(4, 2); // Try to refresh only 2nd row (will be ignored) cstmt.setInt(5, 1); // Try to refresh only 1 row (will be ignored) rs = cstmt.executeQuery(); assertTrue(rs.next()); assertTrue(rs.next()); assertEquals(4, rs.getInt(1)); assertFalse(rs.next()); rs.close(); assertEquals(0, cstmt.getInt(1)); // Fetch rows 5-6 (current row is 5) cstmt.setInt(3, 2); // Next cstmt.setInt(4, 1); // Row number 1 cstmt.setInt(5, 2); // Get 2 rows rs = cstmt.executeQuery(); assertTrue(rs.next()); assertTrue(rs.next()); assertEquals(6, rs.getInt(1)); assertFalse(rs.next()); rs.close(); assertEquals(0, cstmt.getInt(1)); // Fetch previous rows (3-4) (current row is 3) cstmt.setInt(3, 4); // Previous rs = cstmt.executeQuery(); assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); assertTrue(rs.next()); assertEquals(4, rs.getInt(1)); assertFalse(rs.next()); rs.close(); assertEquals(0, cstmt.getInt(1)); // Refresh rows 3-4 (current row is 3) cstmt.setInt(3, 0x80); // Refresh rs = cstmt.executeQuery(); assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); assertTrue(rs.next()); assertEquals(4, rs.getInt(1)); assertFalse(rs.next()); rs.close(); assertEquals(0, cstmt.getInt(1)); // Fetch previous rows (1-2) (current row is 1) cstmt.setInt(3, 4); // Previous rs = cstmt.executeQuery(); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); assertTrue(rs.next()); assertEquals(2, rs.getInt(1)); assertFalse(rs.next()); rs.close(); assertEquals(0, cstmt.getInt(1)); // Fetch next rows (3-4) (current row is 3) cstmt.setInt(3, 2); // Next rs = cstmt.executeQuery(); assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); assertTrue(rs.next()); assertEquals(4, rs.getInt(1)); assertFalse(rs.next()); rs.close(); assertEquals(0, cstmt.getInt(1)); // Fetch first rows (1-2) (current row is 1) cstmt.setInt(3, 1); // First rs = cstmt.executeQuery(); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); assertTrue(rs.next()); assertEquals(2, rs.getInt(1)); assertFalse(rs.next()); rs.close(); assertEquals(0, cstmt.getInt(1)); // Fetch last rows (9-10) (current row is 9) cstmt.setInt(3, 8); // Last rs = cstmt.executeQuery(); assertTrue(rs.next()); assertEquals(9, rs.getInt(1)); assertTrue(rs.next()); assertEquals(10, rs.getInt(1)); assertFalse(rs.next()); rs.close(); assertEquals(0, cstmt.getInt(1)); // Fetch next rows; should not fail (current position is after last) cstmt.setInt(3, 2); // Next rs = cstmt.executeQuery(); assertFalse(rs.next()); rs.close(); assertEquals(0, cstmt.getInt(1)); // Fetch absolute starting with 6 (6-7) (current row is 6) cstmt.setInt(3, 0x10); // Absolute cstmt.setInt(4, 6); rs = cstmt.executeQuery(); assertTrue(rs.next()); assertEquals(6, rs.getInt(1)); assertTrue(rs.next()); assertEquals(7, rs.getInt(1)); assertFalse(rs.next()); rs.close(); assertEquals(0, cstmt.getInt(1)); // Fetch relative -4 (2-3) (current row is 2) cstmt.setInt(3, 0x20); // Relative cstmt.setInt(4, -4); rs = cstmt.executeQuery(); assertTrue(rs.next()); assertEquals(2, rs.getInt(1)); assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); assertFalse(rs.next()); rs.close(); assertEquals(0, cstmt.getInt(1)); // Fetch previous 2 rows; should fail (current row is 1) cstmt.setInt(3, 4); // Previous rs = cstmt.executeQuery(); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); assertTrue(rs.next()); assertEquals(2, rs.getInt(1)); assertFalse(rs.next()); rs.close(); // Returns 2 on error assertEquals(2, cstmt.getInt(1)); // Fetch next rows (3-4) (current row is 3) cstmt.setInt(3, 2); // Next rs = cstmt.executeQuery(); assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); assertTrue(rs.next()); assertEquals(4, rs.getInt(1)); assertFalse(rs.next()); rs.close(); assertEquals(0, cstmt.getInt(1)); cstmt.close(); // // Close cursor // cstmt = con.prepareCall("{?=call sp_cursorclose(?)}"); // Return value (OUT) cstmt.registerOutParameter(1, Types.INTEGER); // Cursor handle (IN) cstmt.setInt(2, cursor); assertFalse(cstmt.execute()); assertEquals(0, cstmt.getInt(1)); cstmt.close(); } /** * Test that <code>absolute(-1)</code> works the same as <code>last()</code>. */ public void testAbsoluteMinusOne() throws Exception { Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.executeUpdate( "create table #absoluteMinusOne (val int primary key)"); stmt.executeUpdate( "insert into #absoluteMinusOne (val) values (1)"); stmt.executeUpdate( "insert into #absoluteMinusOne (val) values (2)"); stmt.executeUpdate( "insert into #absoluteMinusOne (val) values (3)"); ResultSet rs = stmt.executeQuery( "select val from #absoluteMinusOne order by val"); rs.absolute(-1); assertTrue(rs.isLast()); assertEquals(3, rs.getInt(1)); assertFalse(rs.next()); rs.last(); assertTrue(rs.isLast()); assertEquals(3, rs.getInt(1)); assertFalse(rs.next()); rs.close(); stmt.close(); } /** * Test that calling <code>absolute()</code> with very large positive * values positions the cursor after the last row and with very large * negative values positions the cursor before the first row. */ public void testAbsoluteLargeValue() throws SQLException { Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.executeUpdate( "create table #absoluteLargeValue (val int primary key)"); stmt.executeUpdate( "insert into #absoluteLargeValue (val) values (1)"); stmt.executeUpdate( "insert into #absoluteLargeValue (val) values (2)"); stmt.executeUpdate( "insert into #absoluteLargeValue (val) values (3)"); ResultSet rs = stmt.executeQuery( "select val from #absoluteLargeValue order by val"); assertFalse(rs.absolute(10)); assertEquals(0, rs.getRow()); assertTrue(rs.isAfterLast()); assertFalse(rs.next()); assertEquals(0, rs.getRow()); assertTrue(rs.isAfterLast()); assertFalse(rs.absolute(-10)); assertEquals(0, rs.getRow()); assertTrue(rs.isBeforeFirst()); assertFalse(rs.previous()); assertEquals(0, rs.getRow()); assertTrue(rs.isBeforeFirst()); rs.close(); stmt.close(); } /** * Test that calling <code>absolute()</code> with very large positive * values positions the cursor after the last row and with very large * negative values positions the cursor before the first row. */ public void testRelativeLargeValue() throws SQLException { Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.executeUpdate( "create table #relativeLargeValue (val int primary key)"); stmt.executeUpdate( "insert into #relativeLargeValue (val) values (1)"); stmt.executeUpdate( "insert into #relativeLargeValue (val) values (2)"); stmt.executeUpdate( "insert into #relativeLargeValue (val) values (3)"); ResultSet rs = stmt.executeQuery( "select val from #relativeLargeValue order by val"); assertFalse(rs.relative(10)); assertEquals(0, rs.getRow()); assertTrue(rs.isAfterLast()); assertFalse(rs.next()); assertEquals(0, rs.getRow()); assertTrue(rs.isAfterLast()); assertFalse(rs.relative(-10)); assertEquals(0, rs.getRow()); assertTrue(rs.isBeforeFirst()); assertFalse(rs.previous()); assertEquals(0, rs.getRow()); assertTrue(rs.isBeforeFirst()); rs.close(); stmt.close(); } /** * Test that <code>read()</code> works ok on the stream returned by * <code>ResultSet.getUnicodeStream()</code> (i.e. it doesn't always fill * the buffer, regardless of whether there's available data or not). */ public void testUnicodeStream() throws Exception { Statement stmt = con.createStatement(); stmt.executeUpdate("create table #unicodeStream (val varchar(255))"); stmt.executeUpdate("insert into #unicodeStream (val) values ('test')"); ResultSet rs = stmt.executeQuery("select val from #unicodeStream"); if (rs.next()) { byte[] buf = new byte[8000]; InputStream is = rs.getUnicodeStream(1); int length = is.read(buf); assertEquals(4 * 2, length); } rs.close(); stmt.close(); } /** * Check whether <code>Statement.setMaxRows()</code> works okay, bug * [1812686]. */ public void testMaxRows() throws Exception { Statement stmt = con.createStatement(); stmt.executeUpdate("create table #statementMaxRows (val int)"); stmt.close(); // insert 1000 rows PreparedStatement pstmt = con.prepareStatement("insert into #statementMaxRows values (?)"); for (int i = 0; i < 1000; i++) { pstmt.setInt(1, i); assertEquals(1, pstmt.executeUpdate()); } pstmt.close(); stmt = con.createStatement(); // set maxRows to 100 stmt.setMaxRows(100); // select all rows (should only return 100 rows) ResultSet rs = stmt.executeQuery("select * from #statementMaxRows"); int rows = 0; while (rs.next()) { rows++; } assertEquals(100, rows); rs.close(); stmt.close(); } /** * Test that <code>Statement.setMaxRows()</code> works on cursor * <code>ResultSet</code>s. */ public void testCursorMaxRows() throws Exception { Statement stmt = con.createStatement(); stmt.executeUpdate("create table #cursorMaxRows (val int)"); stmt.close(); // Insert 10 rows PreparedStatement pstmt = con.prepareStatement( "insert into #cursorMaxRows (val) values (?)"); for (int i = 0; i < 10; i++) { pstmt.setInt(1, i); assertEquals(1, pstmt.executeUpdate()); } pstmt.close(); // Create a cursor ResultSet stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); // Set maxRows to 5 stmt.setMaxRows(5); // Select all (should only return 5 rows) ResultSet rs = stmt.executeQuery("select * from #cursorMaxRows"); rs.last(); assertEquals(5, rs.getRow()); rs.beforeFirst(); int cnt = 0; while (rs.next()) { cnt++; } assertEquals(5, cnt); rs.close(); stmt.close(); } /** * Test for bug [1075977] <code>setObject()</code> causes SQLException. * <p> * Conversion of <code>float</code> values to <code>String</code> adds * grouping to the value, which cannot then be parsed. */ public void testSetObjectScale() throws Exception { Statement stmt = con.createStatement(); stmt.execute("create table #testsetobj (i int)"); PreparedStatement pstmt = con.prepareStatement("insert into #testsetobj values(?)"); // next line causes sqlexception pstmt.setObject(1, new Float(1234.5667), Types.INTEGER, 0); assertEquals(1, pstmt.executeUpdate()); ResultSet rs = stmt.executeQuery("select * from #testsetobj"); assertTrue(rs.next()); assertEquals("1234", rs.getString(1)); } /** * Test that <code>ResultSet.previous()</code> works correctly on cursor * <code>ResultSet</code>s. */ public void testCursorPrevious() throws Exception { Statement stmt = con.createStatement(); stmt.executeUpdate("create table #cursorPrevious (val int)"); stmt.close(); // Insert 10 rows PreparedStatement pstmt = con.prepareStatement( "insert into #cursorPrevious (val) values (?)"); for (int i = 0; i < 10; i++) { pstmt.setInt(1, i); assertEquals(1, pstmt.executeUpdate()); } pstmt.close(); // Create a cursor ResultSet stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); // Set fetch size to 2 stmt.setFetchSize(2); // Select all ResultSet rs = stmt.executeQuery("select * from #cursorPrevious"); rs.last(); int i = 10; do { assertEquals(i, rs.getRow()); assertEquals(--i, rs.getInt(1)); } while (rs.previous()); assertTrue(rs.isBeforeFirst()); assertEquals(0, i); rs.close(); stmt.close(); } /** * Test the behavior of the ResultSet/Statement/Connection when the JVM * runs out of memory (hopefully) in the middle of a packet. * <p/> * Previously jTDS was not able to close a ResultSet/Statement/Connection * after an OutOfMemoryError because the input stream pointer usually * remained inside a packet and further attempts to dump the rest of the * response failed because of "protocol confusions". */ public void testOutOfMemory() throws SQLException { Statement stmt = con.createStatement(); stmt.executeUpdate( "create table #testOutOfMemory (val binary(8000))" ); // Insert 64kB values byte[] val = new byte[64000]; PreparedStatement pstmt = con.prepareStatement( "insert into #testOutOfMemory (val) values (?)" ); pstmt.setBytes( 1, val ); assertEquals( 1, pstmt.executeUpdate() ); pstmt.close(); // Create a list and keep adding rows to it until we run out of memory // Most probably this will happen in the middle of a row packet, when // jTDS tries to allocate the array, after reading the data length ArrayList results = new ArrayList(); ResultSet rs = null; try { while( true ) { rs = stmt.executeQuery( "select val from #testOutOfMemory" ); assertTrue( rs.next() ); results.add( rs.getBytes( 1 ) ); assertFalse( rs.next() ); rs.close(); rs = null; } } catch( OutOfMemoryError err ) { // Do not remove this. Although not really used, it will free // memory, avoiding another OutOfMemoryError val = null; results = null; if( rs != null ) { // This used to fail, because the parser got confused rs.close(); } } // Make sure the Statement still works rs = stmt.executeQuery( "select 1" ); assertTrue( rs.next() ); assertFalse( rs.next() ); rs.close(); stmt.close(); } /** * Test for bug [1182066] regression bug resultset: relative() not working * as expected. */ public void testRelative() throws Exception { final int ROW_COUNT = 99; Statement stmt = con.createStatement(); stmt.executeUpdate("create table #test2 (i int primary key, v varchar(100))"); for (int i = 1; i <= ROW_COUNT; i++) { stmt.executeUpdate("insert into #test2 (i, v) values (" + i + ", 'This is a test')"); } stmt.close(); String sql = "select * from #test2"; PreparedStatement pstmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); pstmt.setFetchSize(10); ResultSet rs = pstmt.executeQuery(); int resCnt = 0; if (rs.next()) { do { assertEquals(++resCnt, rs.getInt(1)); } while (rs.relative(1)); } assertEquals(ROW_COUNT, resCnt); if (rs.previous()) { do { assertEquals(resCnt--, rs.getInt(1)); } while (rs.relative(-1)); } pstmt.close(); assertEquals(0, resCnt); } /** * Test that after updateRow() the cursor is positioned correctly. */ public void testUpdateRowPosition() throws Exception { final int ROW_COUNT = 99; final int TEST_ROW = 33; Statement stmt = con.createStatement(); stmt.executeUpdate("create table #testPos (i int primary key, v varchar(100))"); for (int i = 1; i <= ROW_COUNT; i++) { stmt.executeUpdate("insert into #testPos (i, v) values (" + i + ", 'This is a test')"); } stmt.close(); String sql = "select * from #testPos order by i"; PreparedStatement pstmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); pstmt.setFetchSize(10); ResultSet rs = pstmt.executeQuery(); for (int i = 1; i <= TEST_ROW; i++) { assertTrue(rs.next()); assertEquals(i, rs.getInt(1)); } // We're on TEST_ROW now assertEquals(TEST_ROW, rs.getRow()); rs.updateString(2, "This is another test"); rs.updateRow(); assertEquals(TEST_ROW, rs.getRow()); assertEquals(TEST_ROW, rs.getInt(1)); rs.refreshRow(); assertEquals(TEST_ROW, rs.getRow()); assertEquals(TEST_ROW, rs.getInt(1)); for (int i = TEST_ROW + 1; i <= ROW_COUNT; i++) { assertTrue(rs.next()); assertEquals(i, rs.getInt(1)); } pstmt.close(); } /** * Test for bug [1197603] Cursor downgrade error in CachedResultSet -- * updateable result sets were incorrectly downgraded to read only forward * only ones when client side cursors were used. */ public void testUpdateableClientCursor() throws Exception { Statement stmt = con.createStatement(); stmt.executeUpdate("create table #testUpdateableClientCursor " + "(i int primary key, v varchar(100))"); stmt.executeUpdate("insert into #testUpdateableClientCursor " + "(i, v) values (1, 'This is a test')"); stmt.close(); // Use a statement that the server won't be able to create a cursor on String sql = "select * from #testUpdateableClientCursor where i = ?"; PreparedStatement pstmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); pstmt.setInt(1, 1); ResultSet rs = pstmt.executeQuery(); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); assertNull(pstmt.getWarnings()); rs.updateString(2, "This is another test"); rs.updateRow(); rs.close(); pstmt.close(); stmt = con.createStatement(); rs = stmt.executeQuery( "select * from #testUpdateableClientCursor"); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); assertEquals("This is another test", rs.getString(2)); assertFalse(rs.next()); rs.close(); stmt.close(); } /** * Test bug with Sybase where readonly scrollable result set based on a * SELECT DISTINCT returns duplicate rows. */ public void testDistinctBug() throws Exception { Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.execute( "CREATE TABLE #testdistinct (id int primary key, c varchar(255))"); stmt.addBatch("INSERT INTO #testdistinct VALUES(1, 'AAAA')"); stmt.addBatch("INSERT INTO #testdistinct VALUES(2, 'AAAA')"); stmt.addBatch("INSERT INTO #testdistinct VALUES(3, 'BBBB')"); stmt.addBatch("INSERT INTO #testdistinct VALUES(4, 'BBBB')"); stmt.addBatch("INSERT INTO #testdistinct VALUES(5, 'CCCC')"); int counts[] = stmt.executeBatch(); assertEquals(5, counts.length); ResultSet rs = stmt.executeQuery( "SELECT DISTINCT c FROM #testdistinct"); assertNotNull(rs); int rowCount = 0; while (rs.next()) { rowCount++; } assertEquals(3, rowCount); stmt.close(); } /** * Test pessimistic concurrency for SQL Server (for Sybase optimistic * concurrency will always be used). */ public void testPessimisticConcurrency() throws Exception { dropTable("pessimisticConcurrency"); Connection con2 = getConnection(); Statement stmt = null; ResultSet rs = null; try { // Create statement using pessimistic locking. stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE + 1); stmt.execute("CREATE TABLE pessimisticConcurrency (id int primary key, data varchar(255))"); for (int i = 0; i < 4; i++) { stmt.executeUpdate("INSERT INTO pessimisticConcurrency VALUES("+i+", 'Table A line "+i+"')"); } // Fetch one row at a time, making sure we know exactly which row is locked stmt.setFetchSize(1); // Open cursor rs = stmt.executeQuery("SELECT id, data FROM pessimisticConcurrency ORDER BY id"); assertNull(rs.getWarnings()); assertEquals(ResultSet.TYPE_SCROLL_SENSITIVE, rs.getType()); assertEquals(ResultSet.CONCUR_UPDATABLE + 1, rs.getConcurrency()); // If not a MSCursorResultSet, give up as no locking will happen if (rs.getClass().getName().indexOf("MSCursorResultSet") == -1) { rs.close(); stmt.close(); return; } // Scroll to and lock row 3 for (int i = 0; i < 3; ++i) { rs.next(); } // Create a second statement final Statement stmt2 = con2.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE + 1); // No better idea to store exceptions final ArrayList container = new ArrayList(); // Launch a thread that will cancel the second statement if it hangs. new Thread() { public void run() { try { sleep(1000); stmt2.cancel(); } catch (Exception ex) { container.add(ex); } } }.start(); // Open second cursor ResultSet rs2 = stmt2.executeQuery("SELECT id, data FROM pessimisticConcurrency WHERE id = 2"); assertNull(rs2.getWarnings()); assertEquals(ResultSet.TYPE_SCROLL_SENSITIVE, rs2.getType()); assertEquals(ResultSet.CONCUR_UPDATABLE + 1, rs2.getConcurrency()); try { System.out.println(rs2.next()); } catch (SQLException ex) { if ("HY010".equals(ex.getSQLState())) { stmt2.getMoreResults(); } if (!"HY008".equals(ex.getSQLState()) && !"HY010".equals(ex.getSQLState())) { fail("Expecting cancel exception."); } } rs.close(); stmt.close(); rs2.close(); stmt2.close(); // Check for exceptions thrown in the cancel thread if (container.size() != 0) { throw (SQLException) container.get(0); } } finally { dropTable("pessimisticConcurrency"); if (con2 != null) { con2.close(); } } } /** * Test if dynamic cursors (<code>ResultSet.TYPE_SCROLL_SENSITIVE+1</code>) * see others' updates. SQL Server only. */ public void testDynamicCursors() throws Exception { final int ROWS = 4; dropTable("dynamicCursors"); Connection con2 = getConnection(); try { Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE + 1, ResultSet.CONCUR_READ_ONLY); stmt.execute("CREATE TABLE dynamicCursors (id int primary key, data varchar(255))"); for (int i = 0; i < ROWS; i++) { stmt.executeUpdate("INSERT INTO dynamicCursors VALUES(" + i + ", 'Table A line " + i + "')"); } // Open cursor ResultSet rs = stmt.executeQuery("SELECT id, data FROM dynamicCursors"); // If not a MSCursorResultSet, give up as it will not see inserts if (rs.getClass().getName().indexOf("MSCursorResultSet") == -1) { rs.close(); stmt.close(); return; } // Insert new row from other connection Statement stmt2 = con2.createStatement(); assertEquals(1, stmt2.executeUpdate( "INSERT INTO dynamicCursors VALUES(" + ROWS + ", 'Table A line " + ROWS + "')")); stmt2.close(); // Count rows and make sure the newly inserted row is visible int cnt; for (cnt = 0; rs.next(); cnt++); assertEquals(ROWS + 1, cnt); rs.close(); stmt.close(); } finally { dropTable("dynamicCursors"); if (con2 != null) { con2.close(); } } } /** * Test for bug [1232733] setFetchSize(0) causes exception. */ public void testZeroFetchSize() throws Exception { Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(0); ResultSet rs = stmt.executeQuery("SELECT 1 UNION SELECT 2"); assertTrue(rs.next()); rs.setFetchSize(0); assertTrue(rs.next()); assertFalse(rs.next()); rs.close(); stmt.close(); } /** * Test for bug [1329765] Pseudo column ROWSTAT is back with SQL 2005 * (September CTP). */ public void testRowstat() throws Exception { PreparedStatement stmt = con.prepareStatement("SELECT 'STRING' str", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = stmt.executeQuery(); assertEquals(1, rs.getMetaData().getColumnCount()); assertTrue(rs.next()); assertFalse(rs.next()); rs.close(); stmt.close(); } /** * Test for bug [2051585], TDS Protocol error when 2 ResultSets on the * same connection are being iterated at the same time. */ public void testConcurrentResultSets() throws Exception { con.setAutoCommit(false); final int rows = 100; final int threads = 100; // prepare test data Statement stmt = con.createStatement(); stmt.execute("create table #conrs (id int,data varchar(20))"); for (int r=0; r < rows; r++) { assertEquals(1, stmt.executeUpdate("insert into #conrs values(" + r + ",'test" + r + "')")); } stmt.close(); final Thread[] workers = new Thread[threads]; final List errors = new ArrayList(); for (int i=0; i < threads; i++) { workers[i] = new Thread("thread " + i) { public void run() { int i=0; try { Statement st = con.createStatement(); ResultSet rs = st.executeQuery("select * from #conrs order by id asc"); for ( ; i < rows; i++) { assertTrue("premature end of result, only " + i + "of " + rows + " rows present", rs.next()); assertEquals("resultset contains wrong row:", i, rs.getInt(1)); assertEquals("resultset contains wrong column value:", "test" + i, rs.getString(2)); // random delays should ensure that threads are not executed one after another if (Math.random() < 0.01) { Thread.sleep(1); } } rs.close(); st.close(); } catch (Throwable t) { synchronized (errors) { errors.add(new Exception(getName() + " at row " + i + ": " + t.getMessage())); } } } }; } // start all threads for (int i=0; i < threads; i++) { workers[i].start(); } // wait for the threads to finish for (int i=0; i < threads; i++) { workers[i].join(); } assertEquals("[]", array2String(errors.toArray())); con.setAutoCommit(true); } private static String array2String(Object[] a) { if (a == null) return "null"; int iMax = a.length - 1; if (iMax == -1) return "[]"; StringBuilder b = new StringBuilder(); b.append('['); for (int i = 0; ; i++) { b.append(String.valueOf(a[i])); if (i == iMax) return b.append(']').toString(); b.append(", "); } } /** * Test for bug [1855125], numeric overflow not reported by jTDS. */ public void testNumericOverflow() throws SQLException { Statement st = con.createStatement(); st.execute("create table #test(data numeric(30,10))"); assertEquals(1, st.executeUpdate("insert into #test values (10000000000000000000.0000)")); ResultSet rs = st.executeQuery("select * from #test"); assertTrue(rs.next()); try { byte b = rs.getByte(1); assertTrue("expected numeric overflow error, got " + b, false); } catch (SQLException e) { assertEquals(e.getSQLState(), "22003"); } try { short s = rs.getShort(1); assertTrue("expected numeric overflow error, got " + s, false); } catch (SQLException e) { assertEquals(e.getSQLState(), "22003"); } try { int i = rs.getInt(1); assertTrue("expected numeric overflow error, got " + i, false); } catch (SQLException e) { assertEquals(e.getSQLState(), "22003"); } try { long l = rs.getLong(1); assertTrue("expected numeric overflow error, got " + l, false); } catch (SQLException e) { assertEquals(e.getSQLState(), "22003"); } rs.close(); st.close(); } /** * Test for bug [2860742], getByte() causes overflow error for negative * values. */ public void testNegativeOverflow() throws SQLException { Statement st = con.createStatement(); st.execute("create table #testNegativeOverflow(data int)"); int [] values = new int [] { -1, -128, -129, 127, 128}; boolean[] overflow = new boolean[] {false, false, true, false, true}; for (int i = 0; i < values.length; i++) { assertEquals(1, st.executeUpdate("insert into #testNegativeOverflow values (" + values[i] + ")")); } ResultSet rs = st.executeQuery("select * from #testNegativeOverflow"); for (int i = 0; i < values.length; i++) { assertTrue(rs.next()); try { byte b = rs.getByte(1); assertFalse("expected numeric overflow error for value " + values[i] + ", got " + b, overflow[i]); } catch (SQLException e) { assertTrue("unexpected numeric overflow for value " + values[i], overflow[i]); } } rs.close(); st.close(); } /** * Test for bug #548, Select statement very slow with date parameter. */ public void testDatePerformance() throws SQLException { Statement st = con.createStatement(); st.execute("create table #test(data datetime)"); st.close(); PreparedStatement ps = con.prepareStatement("insert into #test values(?)"); final int iterations = 10000; final String dateString = "2009-09-03"; // test date value Date date = Date.valueOf(dateString); System.gc(); long start = System.currentTimeMillis(); // insert test data using prepared statement for (int i = 0; i < iterations; i ++) { ps.setDate(1, date); ps.executeUpdate(); } long prep = System.currentTimeMillis() - start; System.out.println("prepared: " + prep + " ms"); ps.close(); // delete test data st = con.createStatement(); assertEquals(iterations, st.executeUpdate("delete from #test")); st.close(); st = con.createStatement(); System.gc(); start = System.currentTimeMillis(); // insert test data using prepared statement for (int i = 0; i < iterations; i ++) { st.executeUpdate("insert into #test values('" + dateString + "')"); } long unprep = System.currentTimeMillis() - start; System.out.println("inlined : " + unprep + " ms"); st.close(); // prepared statement should be faster // ("faster" means prep taking not longer than 110% of unprep; // the 10% are there, because in my tests prep and unprep are almost // identical and jitter leads to the test case sometimes failing and // sometimes not. The "10%" are not really solving the issue, but // if the interpretation could be "prep should not be unexpectedly slower", // those 10% are good enough.) assertEquals( unprep, prep, unprep < prep ? unprep / 10 : unprep ); } public static void main(String[] args) { junit.textui.TestRunner.run(ResultSetTest.class); } }