ResultSets 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; iResultSet 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 sp_cursorfetch with fetch sizes
* greater than 1.
* * Assertions tested: *
ResultSet the requested number of rows is returned,
* starting with row 1 and the error code returned is non-zero (2).
* absolute(-1) works the same as last().
*/
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 absolute() 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 absolute() 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 read() works ok on the stream returned by
* ResultSet.getUnicodeStream() (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 Statement.setMaxRows() 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 Statement.setMaxRows() works on cursor
* ResultSets.
*/
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] setObject() causes SQLException.
*
* Conversion of float values to String 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 ResultSet.previous() works correctly on cursor
* ResultSets.
*/
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.
*
ResultSet.TYPE_SCROLL_SENSITIVE+1)
* 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);
}
}