Here are the examples of the java api class java.sql.ResultSet taken from open source projects.
1. SURTest#testForUpdateWithColumnList()
View license/**
* DERBY-4198 "When using the FOR UPDATE OF clause with SUR
* (Scroll-insensive updatable result sets), the updateRow() method crashes"
*
* This bug revealed missing logic to handle the fact the the ExecRow
* passed down to ScrollInsensitiveResultSet.updateRow does not always
* contain all the rows of the basetable, cf. the logic of RowChangerImpl.
* When an explicit list of columns is given as in FOR UPDATE OF
* <column-list>, the ExecRow may contains a subset of the the base table
* columns and ScrollInsensitiveResultSet was not ready to handle that.
*
* Test some of the cases which went wrong before the fix.
*
*/
public void testForUpdateWithColumnList() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
// case a)
ResultSet rs = s.executeQuery("select c from t1 for update of c");
rs.next();
rs.updateString(1, "foobar");
rs.updateRow();
rs.next();
rs.previous();
assertEquals("foobar", rs.getString(1));
rs.close();
// case b)
rs = s.executeQuery("select id from t1 for update of id");
rs.next();
rs.updateInt(1, 20);
rs.updateRow();
rs.next();
rs.previous();
assertEquals(20, rs.getInt(1));
rs.close();
// case c)
rs = s.executeQuery("select * from t1 for update of id");
rs.next();
rs.updateInt(1, 20);
rs.updateRow();
rs.next();
rs.previous();
assertEquals(20, rs.getInt(1));
rs.close();
// case d)
rs = s.executeQuery("SELECT * from t1 for update of c");
rs.next();
int id = rs.getInt(1);
int a = rs.getInt(2);
int b = rs.getInt(3);
rs.updateString(4, "foobar");
rs.updateRow();
rs.next();
rs.previous();
assertEquals(id, rs.getInt(1));
assertEquals(a, rs.getInt(2));
assertEquals(b, rs.getInt(3));
assertEquals("foobar", rs.getString(4));
rs.close();
// case e)
rs = s.executeQuery("SELECT * from t1 for update of id,a,b,c");
rs.next();
rs.updateInt(1, -20);
rs.updateInt(2, 20);
rs.updateInt(3, 21);
rs.updateString(4, "foobar");
rs.updateRow();
rs.next();
rs.previous();
assertEquals(-20, rs.getInt(1));
assertEquals(20, rs.getInt(2));
assertEquals(21, rs.getInt(3));
assertEquals("foobar", rs.getString(4));
rs.close();
// case f)
rs = s.executeQuery("SELECT * from t1 for update of id, a,b,c");
rs.next();
rs.updateInt(1, 20);
rs.updateRow();
rs.next();
rs.previous();
assertEquals(20, rs.getInt(1));
rs.close();
// case h)
rs = s.executeQuery("SELECT id from t1 for update of id, c");
String cursorname = rs.getCursorName();
rs.next();
Statement s2 = createStatement();
s2.executeUpdate("update t1 set c='foobar' where current of " + cursorname);
s2.close();
rs.next();
rs.previous();
// gives error state 22018 before fix
rs.getInt(1);
rs.close();
// case i)
rs = s.executeQuery("SELECT id from t1 for update");
cursorname = rs.getCursorName();
rs.next();
s2 = createStatement();
s2.executeUpdate("update t1 set c='foobar' where current of " + cursorname);
s2.close();
rs.next();
rs.previous();
// ok before fix
rs.getInt(1);
rs.close();
// Odd cases: base row mentioned twice in rs, update 1st instance
rs = s.executeQuery("SELECT id,a,id from t1");
rs.next();
rs.updateInt(1, 20);
rs.updateRow();
rs.next();
rs.previous();
assertEquals(20, rs.getInt(1));
assertEquals(20, rs.getInt(3));
rs.close();
// Odd cases: base row mentioned twice in rs, update 2nd instance
// with explicit column list; fails, see DERBY-4226.
rs = s.executeQuery("SELECT id,a,id from t1 for update of id");
rs.next();
try {
rs.updateInt(3, 20);
fail("should fail");
} catch (SQLException e) {
String sqlState = usingEmbedded() ? "42X31" : "XJ124";
assertSQLState(sqlState, e);
}
rs.close();
// Odd cases: base row mentioned twice in rs, update 2nd instance
// without explicit column list; works
rs = s.executeQuery("SELECT id,a,id from t1 for update");
rs.next();
rs.updateInt(3, 20);
rs.updateRow();
assertEquals(20, rs.getInt(1));
assertEquals(20, rs.getInt(3));
rs.next();
rs.previous();
assertEquals(20, rs.getInt(1));
assertEquals(20, rs.getInt(3));
rs.close();
s.close();
}2. TestMetaData#testGeneral()
View licenseprivate void testGeneral() throws SQLException {
Connection conn = getConnection("metaData");
DatabaseMetaData meta = conn.getMetaData();
Statement stat = conn.createStatement();
stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
stat.execute("CREATE INDEX IDXNAME ON TEST(NAME)");
ResultSet rs;
rs = meta.getCatalogs();
rs.next();
assertEquals(CATALOG, rs.getString(1));
assertFalse(rs.next());
rs = meta.getSchemas();
rs.next();
assertEquals("INFORMATION_SCHEMA", rs.getString("TABLE_SCHEM"));
rs.next();
assertEquals("PUBLIC", rs.getString("TABLE_SCHEM"));
assertFalse(rs.next());
rs = meta.getSchemas(null, null);
rs.next();
assertEquals("INFORMATION_SCHEMA", rs.getString("TABLE_SCHEM"));
rs.next();
assertEquals("PUBLIC", rs.getString("TABLE_SCHEM"));
assertFalse(rs.next());
rs = meta.getSchemas(null, "PUBLIC");
rs.next();
assertEquals("PUBLIC", rs.getString("TABLE_SCHEM"));
assertFalse(rs.next());
rs = meta.getTableTypes();
rs.next();
assertEquals("SYSTEM TABLE", rs.getString("TABLE_TYPE"));
rs.next();
assertEquals("TABLE", rs.getString("TABLE_TYPE"));
rs.next();
assertEquals("TABLE LINK", rs.getString("TABLE_TYPE"));
rs.next();
assertEquals("VIEW", rs.getString("TABLE_TYPE"));
assertFalse(rs.next());
rs = meta.getTables(null, Constants.SCHEMA_MAIN, null, new String[] { "TABLE" });
assertTrue(rs.getStatement() == null);
rs.next();
assertEquals("TEST", rs.getString("TABLE_NAME"));
assertFalse(rs.next());
rs = meta.getTables(null, "INFORMATION_SCHEMA", null, new String[] { "TABLE", "SYSTEM TABLE" });
rs.next();
assertEquals("CATALOGS", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("COLLATIONS", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("COLUMNS", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("COLUMN_PRIVILEGES", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("CONSTANTS", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("CONSTRAINTS", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("CROSS_REFERENCES", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("DOMAINS", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("FUNCTION_ALIASES", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("FUNCTION_COLUMNS", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("HELP", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("INDEXES", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("IN_DOUBT", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("LOCKS", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("QUERY_STATISTICS", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("RIGHTS", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("ROLES", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("SCHEMATA", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("SEQUENCES", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("SESSIONS", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("SESSION_STATE", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("SETTINGS", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("TABLES", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("TABLE_PRIVILEGES", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("TABLE_TYPES", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("TRIGGERS", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("TYPE_INFO", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("USERS", rs.getString("TABLE_NAME"));
rs.next();
assertEquals("VIEWS", rs.getString("TABLE_NAME"));
assertFalse(rs.next());
rs = meta.getColumns(null, null, "TEST", null);
rs.next();
assertEquals("ID", rs.getString("COLUMN_NAME"));
rs.next();
assertEquals("NAME", rs.getString("COLUMN_NAME"));
assertFalse(rs.next());
rs = meta.getPrimaryKeys(null, null, "TEST");
rs.next();
assertEquals("ID", rs.getString("COLUMN_NAME"));
assertFalse(rs.next());
rs = meta.getBestRowIdentifier(null, null, "TEST", DatabaseMetaData.bestRowSession, false);
rs.next();
assertEquals("ID", rs.getString("COLUMN_NAME"));
assertFalse(rs.next());
rs = meta.getIndexInfo(null, null, "TEST", false, false);
rs.next();
String index = rs.getString("INDEX_NAME");
assertTrue(index.startsWith("PRIMARY_KEY"));
assertEquals("ID", rs.getString("COLUMN_NAME"));
rs.next();
assertEquals("IDXNAME", rs.getString("INDEX_NAME"));
assertEquals("NAME", rs.getString("COLUMN_NAME"));
assertFalse(rs.next());
rs = meta.getIndexInfo(null, null, "TEST", true, false);
rs.next();
index = rs.getString("INDEX_NAME");
assertTrue(index.startsWith("PRIMARY_KEY"));
assertEquals("ID", rs.getString("COLUMN_NAME"));
assertFalse(rs.next());
rs = meta.getVersionColumns(null, null, "TEST");
assertFalse(rs.next());
stat.execute("DROP TABLE TEST");
rs = stat.executeQuery("SELECT * FROM INFORMATION_SCHEMA.SETTINGS");
while (rs.next()) {
String name = rs.getString("NAME");
String value = rs.getString("VALUE");
trace(name + "=" + value);
}
testMore();
// meta.getTablePrivileges()
// meta.getAttributes()
// meta.getColumnPrivileges()
// meta.getSuperTables()
// meta.getSuperTypes()
// meta.getTypeInfo()
conn.close();
deleteDb("metaData");
}3. TestResultSet#testInsertRowWithUpdatableResultSetDefault()
View licenseprivate void testInsertRowWithUpdatableResultSetDefault() throws Exception {
stat.execute("create table test(id int primary key, " + "data varchar(255) default 'Hello')");
PreparedStatement prep = conn.prepareStatement("select * from test", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = prep.executeQuery();
rs.moveToInsertRow();
rs.updateInt(1, 1);
rs.insertRow();
rs.close();
rs = stat.executeQuery("select * from test");
assertTrue(rs.next());
assertEquals("Hello", rs.getString(2));
assertEquals("Hello", rs.getString("data"));
assertEquals("Hello", rs.getNString(2));
assertEquals("Hello", rs.getNString("data"));
assertEquals("Hello", IOUtils.readStringAndClose(rs.getNCharacterStream(2), -1));
assertEquals("Hello", IOUtils.readStringAndClose(rs.getNCharacterStream("data"), -1));
assertEquals("Hello", IOUtils.readStringAndClose(rs.getNClob(2).getCharacterStream(), -1));
assertEquals("Hello", IOUtils.readStringAndClose(rs.getNClob("data").getCharacterStream(), -1));
rs = prep.executeQuery();
rs.moveToInsertRow();
rs.updateInt(1, 2);
rs.updateNString(2, "Hello");
rs.insertRow();
rs.moveToInsertRow();
rs.updateInt(1, 3);
rs.updateNString("data", "Hello");
rs.insertRow();
Clob c;
Writer w;
rs.moveToInsertRow();
rs.updateInt(1, 4);
c = conn.createClob();
w = c.setCharacterStream(1);
w.write("Hello");
w.close();
rs.updateClob(2, c);
rs.insertRow();
rs.moveToInsertRow();
rs.updateInt(1, 5);
c = conn.createClob();
w = c.setCharacterStream(1);
w.write("Hello");
w.close();
rs.updateClob("data", c);
rs.insertRow();
InputStream in;
rs.moveToInsertRow();
rs.updateInt(1, 6);
in = new ByteArrayInputStream("Hello".getBytes("UTF-8"));
rs.updateAsciiStream(2, in);
rs.insertRow();
rs.moveToInsertRow();
rs.updateInt(1, 7);
in = new ByteArrayInputStream("Hello".getBytes("UTF-8"));
rs.updateAsciiStream("data", in);
rs.insertRow();
rs.moveToInsertRow();
rs.updateInt(1, 8);
in = new ByteArrayInputStream("Hello-".getBytes("UTF-8"));
rs.updateAsciiStream(2, in, 5);
rs.insertRow();
rs.moveToInsertRow();
rs.updateInt(1, 9);
in = new ByteArrayInputStream("Hello-".getBytes("UTF-8"));
rs.updateAsciiStream("data", in, 5);
rs.insertRow();
rs.moveToInsertRow();
rs.updateInt(1, 10);
in = new ByteArrayInputStream("Hello-".getBytes("UTF-8"));
rs.updateAsciiStream(2, in, 5L);
rs.insertRow();
rs.moveToInsertRow();
rs.updateInt(1, 11);
in = new ByteArrayInputStream("Hello-".getBytes("UTF-8"));
rs.updateAsciiStream("data", in, 5L);
rs.insertRow();
rs = stat.executeQuery("select * from test");
while (rs.next()) {
assertEquals("Hello", rs.getString(2));
}
stat.execute("drop table test");
}4. SURTest#testInsertRowWithScrollCursor()
View license/**
* Tests that it is possible to move using positioning methods after
* moveToInsertRow and that it is possible to delete a row after
* positioning back from insertRow. Also tests that it is possible to
* insert a row when positioned on insert row, that it is not possible
* to update or delete a row from insertRow and that it also is not possible
* to insert a row without being on insert row.
*/
public void testInsertRowWithScrollCursor() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
int currentPosition, lastRow;
s.setCursorName(getNextCursorName());
ResultSet rs = s.executeQuery("select * from t1");
rs.last();
lastRow = rs.getRow();
rs.beforeFirst();
rs.next();
// Test that it is possible to move to next row from insertRow
currentPosition = rs.getRow();
rs.moveToInsertRow();
rs.updateInt(1, currentPosition + 1000);
rs.next();
assertEquals("CurrentPosition should be " + (currentPosition + 1), rs.getRow(), currentPosition + 1);
// should be able to delete the row
rs.deleteRow();
// Test that it is possible to move using relative from insertRow
currentPosition = rs.getRow();
rs.moveToInsertRow();
rs.updateInt(1, currentPosition + 1000);
rs.relative(2);
assertEquals("CurrentPosition should be " + (currentPosition + 2), rs.getRow(), currentPosition + 2);
// should be able to delete the row
rs.deleteRow();
// Test that it is possible to move using absolute from insertRow
currentPosition = rs.getRow();
rs.moveToInsertRow();
rs.updateInt(1, currentPosition + 1000);
rs.absolute(6);
assertEquals("CurrentPosition should be 6", rs.getRow(), 6);
// should be able to delete the row
rs.deleteRow();
// Test that it is possible to move to previous row from insertRow
currentPosition = rs.getRow();
rs.moveToInsertRow();
rs.updateInt(1, currentPosition + 1000);
rs.previous();
assertEquals("CurrentPosition should be " + (currentPosition - 1), rs.getRow(), currentPosition - 1);
// should be able to delete the row
rs.deleteRow();
// Test that it is possible to move to first row from insertRow
currentPosition = rs.getRow();
rs.moveToInsertRow();
rs.updateInt(1, currentPosition + 1000);
rs.first();
assertEquals("CurrentPosition should be 1", rs.getRow(), 1);
assertTrue("isFirst() should return true", rs.isFirst());
// should be able to delete the row
rs.deleteRow();
// Test that it is possible to move to last row from insertRow
currentPosition = rs.getRow();
rs.moveToInsertRow();
rs.updateInt(1, currentPosition + 1000);
rs.last();
assertEquals("CurrentPosition should be " + lastRow, rs.getRow(), lastRow);
assertTrue("isLast() should return true", rs.isLast());
// should be able to delete the row
rs.deleteRow();
// Test that it is possible to move beforeFirst from insertRow
currentPosition = rs.getRow();
rs.moveToInsertRow();
rs.updateInt(1, currentPosition + 1000);
rs.beforeFirst();
assertTrue("isBeforeFirst() should return true", rs.isBeforeFirst());
rs.next();
assertEquals("CurrentPosition should be 1", rs.getRow(), 1);
assertTrue("isFirst() should return true", rs.isFirst());
// Test that it is possible to move afterLast from insertRow
currentPosition = rs.getRow();
rs.moveToInsertRow();
rs.updateInt(1, currentPosition + 1000);
rs.afterLast();
assertTrue("isAfterLast() should return true", rs.isAfterLast());
rs.previous();
assertEquals("CurrentPosition should be " + lastRow, rs.getRow(), lastRow);
assertTrue("isLast() should return true", rs.isLast());
// Test that it is possible to insert a row and move back to current row
rs.previous();
currentPosition = rs.getRow();
rs.moveToInsertRow();
rs.updateInt(1, currentPosition + 1000);
rs.insertRow();
rs.moveToCurrentRow();
assertEquals("CurrentPosition should be " + currentPosition, rs.getRow(), currentPosition);
try {
rs.moveToInsertRow();
rs.updateInt(1, currentPosition + 2000);
rs.updateRow();
} catch (SQLException se) {
assertEquals("Expected exception", se.getSQLState().substring(0, 5), INVALID_CURSOR_STATE_NO_CURRENT_ROW);
}
try {
rs.moveToInsertRow();
rs.updateInt(1, currentPosition + 2000);
rs.deleteRow();
} catch (SQLException se) {
assertEquals("Expected exception", se.getSQLState().substring(0, 5), INVALID_CURSOR_STATE_NO_CURRENT_ROW);
}
try {
rs.moveToCurrentRow();
rs.updateInt(1, currentPosition + 2000);
rs.insertRow();
} catch (SQLException se) {
assertEquals("Expected exception", se.getSQLState().substring(0, 5), CURSOR_NOT_POSITIONED_ON_INSERT_ROW);
}
rs.close();
s.close();
}5. SURTest#testDowngradeToScrollReadOnly()
View license/**
* DERBY-1481 - ResultSet.beforeFirst() gives protocol error on scrollable,
* updatable result sets that are downgraded to read-only
*
* Check that no exception is thrown when calling positioning methods on a
* result set that has been downgraded to read-only.
*
*/
public void testDowngradeToScrollReadOnly() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery("select * from t1 order by b");
// check that the ResultSet was downgraded
assertWarning(rs.getWarnings(), QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
// call positioning methods
rs.next();
rs.next();
rs.previous();
rs.relative(1);
rs.absolute(3);
rs.relative(-1);
rs.first();
rs.last();
rs.beforeFirst();
rs.afterLast();
// close result set and statement
rs.close();
s.close();
}6. LOBLocatorReleaseTest#scrollableTest()
View license/**
* Tests a sequence of operations on a scrollable result set.
*
* @param table the table to query
* @param rsConcurrency the result set concurrency
*/
private void scrollableTest(String table, int rsConcurrency) throws SQLException {
final String sql = "select dBlob, dClob from " + table;
getConnection().setAutoCommit(false);
Statement stmt = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, rsConcurrency);
ResultSet rs = stmt.executeQuery(sql);
// Just iterate through and close.
while (rs.next()) {
}
rs.close();
// Do some random navigation.
rs = stmt.executeQuery(sql);
rs.next();
rs.beforeFirst();
rs.first();
rs.relative(3);
rs.previous();
rs.last();
rs.absolute(5);
rs.afterLast();
rs.next();
}7. SURTest#testRowUpdatedAndRowDeleted()
View license/**
* Test that rowUpdated() and rowDeleted() methods both return true when
* the row has first been updated and then deleted using the updateRow()
* and deleteRow() methods.
*/
public void testRowUpdatedAndRowDeleted() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
s.setCursorName(getNextCursorName());
ResultSet rs = s.executeQuery("select a,b from t1");
rs.next();
rs.updateInt(1, rs.getInt(1) + 2 * recordCount);
rs.updateRow();
assertTrue("Expected rowUpdated() to return true", rs.rowUpdated());
rs.deleteRow();
rs.next();
rs.previous();
assertTrue("Expected rowUpdated() to return true", rs.rowUpdated());
assertTrue("Expected rowDeleted() to return true", rs.rowDeleted());
rs.next();
assertFalse("Expected rowUpdated() to return false", rs.rowUpdated());
assertFalse("Expected rowDeleted() to return false", rs.rowDeleted());
rs.previous();
assertTrue("Expected rowUpdated() to return true", rs.rowUpdated());
assertTrue("Expected rowDeleted() to return true", rs.rowDeleted());
rs.close();
s.close();
}8. TestPgServer#testPgClient()
View licenseprivate void testPgClient() throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5535/test", "sa", "sa");
Statement stat = conn.createStatement();
assertThrows(SQLException.class, stat).execute("select ***");
stat.execute("create user test password 'test'");
stat.execute("create table test(id int primary key, name varchar)");
stat.execute("create index idx_test_name on test(name, id)");
stat.execute("grant all on test to test");
stat.close();
conn.close();
conn = DriverManager.getConnection("jdbc:postgresql://localhost:5535/test", "test", "test");
stat = conn.createStatement();
ResultSet rs;
stat.execute("prepare test(int, int) as select ?1*?2");
rs = stat.executeQuery("execute test(3, 2)");
rs.next();
assertEquals(6, rs.getInt(1));
stat.execute("deallocate test");
PreparedStatement prep;
prep = conn.prepareStatement("select * from test where name = ?");
prep.setNull(1, Types.VARCHAR);
rs = prep.executeQuery();
assertFalse(rs.next());
prep = conn.prepareStatement("insert into test values(?, ?)");
ParameterMetaData meta = prep.getParameterMetaData();
assertEquals(2, meta.getParameterCount());
prep.setInt(1, 1);
prep.setString(2, "Hello");
prep.execute();
rs = stat.executeQuery("select * from test");
rs.next();
ResultSetMetaData rsMeta = rs.getMetaData();
assertEquals(Types.INTEGER, rsMeta.getColumnType(1));
assertEquals(Types.VARCHAR, rsMeta.getColumnType(2));
prep.close();
assertEquals(1, rs.getInt(1));
assertEquals("Hello", rs.getString(2));
assertFalse(rs.next());
prep = conn.prepareStatement("select * from test " + "where id = ? and name = ?");
prep.setInt(1, 1);
prep.setString(2, "Hello");
rs = prep.executeQuery();
rs.next();
assertEquals(1, rs.getInt(1));
assertEquals("Hello", rs.getString(2));
assertFalse(rs.next());
rs.close();
DatabaseMetaData dbMeta = conn.getMetaData();
rs = dbMeta.getTables(null, null, "TEST", null);
rs.next();
assertEquals("TEST", rs.getString("TABLE_NAME"));
assertFalse(rs.next());
rs = dbMeta.getColumns(null, null, "TEST", null);
rs.next();
assertEquals("ID", rs.getString("COLUMN_NAME"));
rs.next();
assertEquals("NAME", rs.getString("COLUMN_NAME"));
assertFalse(rs.next());
rs = dbMeta.getIndexInfo(null, null, "TEST", false, false);
// index info is currently disabled
// rs.next();
// assertEquals("TEST", rs.getString("TABLE_NAME"));
// rs.next();
// assertEquals("TEST", rs.getString("TABLE_NAME"));
assertFalse(rs.next());
rs = stat.executeQuery("select version(), pg_postmaster_start_time(), current_schema()");
rs.next();
String s = rs.getString(1);
assertTrue(s.contains("H2"));
assertTrue(s.contains("PostgreSQL"));
s = rs.getString(2);
s = rs.getString(3);
assertEquals(s, "PUBLIC");
assertFalse(rs.next());
conn.setAutoCommit(false);
stat.execute("delete from test");
conn.rollback();
stat.execute("update test set name = 'Hallo'");
conn.commit();
rs = stat.executeQuery("select * from test order by id");
rs.next();
assertEquals(1, rs.getInt(1));
assertEquals("Hallo", rs.getString(2));
assertFalse(rs.next());
rs = stat.executeQuery("select id, name, pg_get_userbyid(id) " + "from information_schema.users order by id");
rs.next();
assertEquals(rs.getString(2), rs.getString(3));
assertFalse(rs.next());
rs.close();
rs = stat.executeQuery("select currTid2('x', 1)");
rs.next();
assertEquals(1, rs.getInt(1));
rs = stat.executeQuery("select has_table_privilege('TEST', 'READ')");
rs.next();
assertTrue(rs.getBoolean(1));
rs = stat.executeQuery("select has_database_privilege(1, 'READ')");
rs.next();
assertTrue(rs.getBoolean(1));
rs = stat.executeQuery("select pg_get_userbyid(-1)");
rs.next();
assertEquals(null, rs.getString(1));
rs = stat.executeQuery("select pg_encoding_to_char(0)");
rs.next();
assertEquals("SQL_ASCII", rs.getString(1));
rs = stat.executeQuery("select pg_encoding_to_char(6)");
rs.next();
assertEquals("UTF8", rs.getString(1));
rs = stat.executeQuery("select pg_encoding_to_char(8)");
rs.next();
assertEquals("LATIN1", rs.getString(1));
rs = stat.executeQuery("select pg_encoding_to_char(20)");
rs.next();
assertEquals("UTF8", rs.getString(1));
rs = stat.executeQuery("select pg_encoding_to_char(40)");
rs.next();
assertEquals("", rs.getString(1));
rs = stat.executeQuery("select pg_get_oid('\"WRONG\"')");
rs.next();
assertEquals(0, rs.getInt(1));
rs = stat.executeQuery("select pg_get_oid('TEST')");
rs.next();
assertTrue(rs.getInt(1) > 0);
rs = stat.executeQuery("select pg_get_indexdef(0, 0, false)");
rs.next();
assertEquals("", rs.getString(1));
rs = stat.executeQuery("select id from information_schema.indexes " + "where index_name='IDX_TEST_NAME'");
rs.next();
int indexId = rs.getInt(1);
rs = stat.executeQuery("select pg_get_indexdef(" + indexId + ", 0, false)");
rs.next();
assertEquals("CREATE INDEX PUBLIC.IDX_TEST_NAME ON PUBLIC.TEST(NAME, ID)", rs.getString(1));
rs = stat.executeQuery("select pg_get_indexdef(" + indexId + ", null, false)");
rs.next();
assertEquals("CREATE INDEX PUBLIC.IDX_TEST_NAME ON PUBLIC.TEST(NAME, ID)", rs.getString(1));
rs = stat.executeQuery("select pg_get_indexdef(" + indexId + ", 1, false)");
rs.next();
assertEquals("NAME", rs.getString(1));
rs = stat.executeQuery("select pg_get_indexdef(" + indexId + ", 2, false)");
rs.next();
assertEquals("ID", rs.getString(1));
conn.close();
}9. UpdateableResultTest#testUpdateable()
View licensepublic void testUpdateable() throws SQLException {
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery("select * from updateable");
assertNotNull(rs);
rs.moveToInsertRow();
rs.updateInt(1, 1);
rs.updateString(2, "jake");
rs.updateString(3, "avalue");
rs.insertRow();
rs.first();
rs.updateInt("id", 2);
rs.updateString("name", "dave");
rs.updateRow();
assertEquals(2, rs.getInt("id"));
assertEquals("dave", rs.getString("name"));
assertEquals("avalue", rs.getString("notselected"));
rs.deleteRow();
rs.moveToInsertRow();
rs.updateInt("id", 3);
rs.updateString("name", "paul");
rs.insertRow();
try {
rs.refreshRow();
fail("Can't refresh when on the insert row.");
} catch (SQLException sqle) {
}
assertEquals(3, rs.getInt("id"));
assertEquals("paul", rs.getString("name"));
assertNull(rs.getString("notselected"));
rs.close();
rs = st.executeQuery("select id1, id, name, name1 from updateable, second");
try {
while (rs.next()) {
rs.updateInt("id", 2);
rs.updateString("name", "dave");
rs.updateRow();
}
fail("should not get here, update should fail");
} catch (SQLException ex) {
}
rs = st.executeQuery("select oid,* from updateable");
assertTrue(rs.first());
rs.updateInt("id", 3);
rs.updateString("name", "dave3");
rs.updateRow();
assertEquals(3, rs.getInt("id"));
assertEquals("dave3", rs.getString("name"));
rs.moveToInsertRow();
rs.updateInt("id", 4);
rs.updateString("name", "dave4");
rs.insertRow();
rs.updateInt("id", 5);
rs.updateString("name", "dave5");
rs.insertRow();
rs.moveToCurrentRow();
assertEquals(3, rs.getInt("id"));
assertEquals("dave3", rs.getString("name"));
assertTrue(rs.next());
assertEquals(4, rs.getInt("id"));
assertEquals("dave4", rs.getString("name"));
assertTrue(rs.next());
assertEquals(5, rs.getInt("id"));
assertEquals("dave5", rs.getString("name"));
rs.close();
st.close();
}10. UpdatableResultSetTest#testInsertRowOnQuotedTable()
View license/**
* Tests insertRow with table name containing quotes
*/
public void testInsertRowOnQuotedTable() throws SQLException {
ResultSet rs = null;
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" table\"");
rs.next();
rs.moveToInsertRow();
rs.updateInt(1, 4);
rs.insertRow();
rs.moveToCurrentRow();
rs.close();
rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" table\" " + "order by x");
for (int i = 1; i <= 4; i++) {
assertTrue("there is a row", rs.next());
assertEquals("row contains correct value", i, rs.getInt(1));
}
rs.close();
stmt.close();
}11. UpdatableResultSetTest#testInsertRowOnQuotedColumn()
View license/**
* Tests insertRow with column name containing quotes
*/
public void testInsertRowOnQuotedColumn() throws SQLException {
ResultSet rs = null;
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from \"my quoted columns\"");
rs.next();
rs.moveToInsertRow();
rs.updateInt(1, 4);
rs.insertRow();
rs.moveToCurrentRow();
rs.close();
rs = stmt.executeQuery("select * from \"my quoted columns\" " + "order by \"my \"\"quoted\"\" column\"");
for (int i = 1; i <= 4; i++) {
assertTrue("there is a row", rs.next());
assertEquals("row contains correct value", i, rs.getInt(1));
}
rs.close();
stmt.close();
}12. UpdatableResultSetTest#testInsertRowOnQuotedSchema()
View license/**
* Tests insertRow with schema name containing quotes
*/
public void testInsertRowOnQuotedSchema() throws SQLException {
ResultSet rs = null;
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" schema\"." + "\"my quoted schema\"");
rs.next();
rs.moveToInsertRow();
rs.updateInt(1, 4);
rs.insertRow();
rs.moveToCurrentRow();
rs.close();
rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" schema\"." + "\"my quoted schema\" order by x");
for (int i = 1; i <= 4; i++) {
assertTrue("there is a row", rs.next());
assertEquals("row contains correct value", i, rs.getInt(1));
}
rs.close();
stmt.close();
}13. UpdatableResultSetTest#testInsertRowOnQuotedCursor()
View license/**
* Tests insertRow with cursor name containing quotes
*/
public void testInsertRowOnQuotedCursor() throws SQLException {
ResultSet rs = null;
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
stmt.setCursorName("my \"\"\"\"quoted\"\"\"\" cursor\"\"");
rs = stmt.executeQuery("select * from \"my table\"");
rs.next();
rs.moveToInsertRow();
rs.updateInt(1, 4);
rs.insertRow();
rs.moveToCurrentRow();
rs.close();
rs = stmt.executeQuery("select * from \"my table\" order by x");
for (int i = 1; i <= 4; i++) {
assertTrue("there is a row", rs.next());
assertEquals("row contains correct value", i, rs.getInt(1));
}
rs.close();
stmt.close();
}14. HoldCursorTest#StatementsInProcedure()
View license/**
* test holdability of statements in procedures
* @param holdability
* @throws SQLException
*/
public void StatementsInProcedure(int holdability) throws SQLException {
setHoldability(holdability);
CallableStatement cs1 = prepareCall("call MYPROC()");
cs1.execute();
ResultSet rs2 = cs1.getResultSet();
rs2.next();
assertEquals(11, rs2.getInt(1));
rs2.next();
assertEquals(12, rs2.getInt(1));
CallableStatement cs2 = prepareCall("call MYPROC()");
cs2.execute();
commit();
ResultSet rs1 = cs2.getResultSet();
rs1.next();
assertEquals(11, rs1.getInt(1));
if (rs1.next())
assertEquals(12, rs1.getInt(1));
else
assertNull(rs1);
cs1.close();
cs2.close();
rs1.close();
rs2.close();
}15. UpdateableResultTest#testUpdateable()
View license@Test
@Ignore
public void testUpdateable() throws SQLException {
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.HOLD_CURSORS_OVER_COMMIT);
ResultSet rs = st.executeQuery("select * from updateable");
assertNotNull(rs);
rs.moveToInsertRow();
rs.updateInt(1, 1);
rs.updateString(2, "jake");
rs.updateString(3, "avalue");
rs.insertRow();
assertTrue(rs.first());
rs.updateInt("id", 2);
rs.updateString("name", "dave");
rs.updateRow();
assertEquals(2, rs.getInt("id"));
assertEquals("dave", rs.getString("name"));
assertEquals("avalue", rs.getString("notselected"));
rs.deleteRow();
rs.moveToInsertRow();
rs.updateInt("id", 3);
rs.updateString("name", "paul");
rs.insertRow();
try {
rs.refreshRow();
fail("Can't refresh when on the insert row.");
} catch (SQLException sqle) {
}
assertEquals(3, rs.getInt("id"));
assertEquals("paul", rs.getString("name"));
assertNull(rs.getString("notselected"));
rs.close();
rs = st.executeQuery("select id1, id, name, name1 from updateable, second");
try {
while (rs.next()) {
rs.updateInt("id", 2);
rs.updateString("name", "dave");
rs.updateRow();
}
fail("should not get here, update should fail");
} catch (SQLException ex) {
}
rs = st.executeQuery("select oid,* from updateable");
assertTrue(rs.first());
rs.updateInt("id", 3);
rs.updateString("name", "dave3");
rs.updateRow();
assertEquals(3, rs.getInt("id"));
assertEquals("dave3", rs.getString("name"));
rs.moveToInsertRow();
rs.updateInt("id", 4);
rs.updateString("name", "dave4");
rs.insertRow();
rs.updateInt("id", 5);
rs.updateString("name", "dave5");
rs.insertRow();
rs.moveToCurrentRow();
assertEquals(3, rs.getInt("id"));
assertEquals("dave3", rs.getString("name"));
assertTrue(rs.next());
assertEquals(4, rs.getInt("id"));
assertEquals("dave4", rs.getString("name"));
assertTrue(rs.next());
assertEquals(5, rs.getInt("id"));
assertEquals("dave5", rs.getString("name"));
rs.close();
st.close();
}16. UpdatableResultSetTest#testMultipleUpdateXXXAndUpdateRow()
View license/**
* Positive test - issue multiple updateXXXs and then a updateRow
*/
public void testMultipleUpdateXXXAndUpdateRow() throws SQLException {
createTableT1();
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
assertTrue("FAIL - row not found", rs.next());
assertEquals("FAIL - wrong value for column 1", 1, rs.getInt(1));
rs.updateInt(1, 2345);
// Issue another updateInt on the same row and column to change the
// column's value to 9999
rs.updateInt(1, 9999);
// Issue updateString to change the column's value to 'xxxxxxx'
rs.updateString(2, "xxxxxxx");
println(" Now issue updateRow");
rs.updateRow();
rs.close();
// Make sure that changes made it to the database correctly
String[][] expected = { { "9999", "xxxxxxx" }, { "2", "bb" }, { "3", "cc" } };
rs = stmt.executeQuery("SELECT * FROM t1");
JDBC.assertFullResultSet(rs, expected, true);
rs.close();
stmt.close();
}17. UpdatableResultSetTest#testCaseSensitiveTableAndColumnName()
View license/**
* Positive test - case sensitive table and column names
*/
public void testCaseSensitiveTableAndColumnName() throws SQLException {
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
stmt.executeUpdate("create table \"t1\" (\"c11\" int, c12 int)");
stmt.executeUpdate("insert into \"t1\" values(1, 2), (2,3)");
ResultSet rs = stmt.executeQuery("SELECT \"c11\", \"C12\" FROM \"t1\" FOR UPDATE");
assertTrue("FAIL - row not found", rs.next());
rs.updateInt(1, 11);
rs.updateInt(2, 22);
rs.updateRow();
assertTrue("FAIL - row not found", rs.next());
rs.deleteRow();
rs.close();
// Make sure that changes made it to the database correctly
rs = stmt.executeQuery("SELECT \"c11\", \"C12\" FROM \"t1\" FOR UPDATE");
String[][] expected = { { "11", "22" } };
JDBC.assertFullResultSet(rs, expected, true);
rs.close();
stmt.close();
}18. UpdatableResultSetTest#testTableAndColumnNameWithSpaces()
View license/**
* Positive test - table and column names with spaces in middle and end
*/
public void testTableAndColumnNameWithSpaces() throws SQLException {
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
stmt.executeUpdate("create table \" t 11 \" (\" c 111 \" int, c112 int)");
stmt.executeUpdate("insert into \" t 11 \" values(1, 2), (2,3)");
ResultSet rs = stmt.executeQuery("SELECT \" c 111 \", \"C112\" FROM \" t 11 \" ");
assertTrue("FAIL - row not found", rs.next());
rs.updateInt(1, 11);
rs.updateInt(2, 22);
rs.updateRow();
assertTrue("FAIL - row not found", rs.next());
rs.deleteRow();
rs.close();
// Make sure for table \" t 11 \" that changes made it to the database
// correctly
rs = stmt.executeQuery("SELECT \" c 111 \", \"C112\" FROM \" t 11 \" ");
String[][] expected = { { "11", "22" } };
JDBC.assertFullResultSet(rs, expected, true);
rs.close();
stmt.close();
}19. UpdatableResultSetTest#testRollbackWithAutoCommit()
View license/**
* Positive test - Rollback with AutoCommit on
*/
public void testRollbackWithAutoCommit() throws SQLException {
createTableT4();
getConnection().setAutoCommit(true);
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM t4");
assertTrue("FAIL - row not found", rs.next());
rs.moveToInsertRow();
rs.updateInt(1, 4000);
rs.updateInt(2, 4000);
rs.insertRow();
rollback();
rs.close();
String[][] expected = { { "1", "1" }, { "2", "2" }, { "3", "3" }, { "4", "4" } };
rs = stmt.executeQuery("SELECT * FROM t4");
JDBC.assertFullResultSet(rs, expected, true);
rs.close();
stmt.executeUpdate("DROP TABLE t4");
stmt.close();
commit();
}20. DeclareGlobalTempTableJavaTest#testDeleteWhereCurrentOfOnGTT()
View license/**
* Test for delete where current of on temporary tables
*
* @throws SQLException
*/
public void testDeleteWhereCurrentOfOnGTT() throws SQLException {
Statement s = createStatement();
assertUpdateCount(s, 0, "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
assertUpdateCount(s, 1, "insert into SESSION.t2 values(21, 1)");
assertUpdateCount(s, 1, "insert into SESSION.t2 values(22, 1)");
ResultSet rs1 = s.executeQuery("select count(*) from SESSION.t2");
JDBC.assertSingleValueResultSet(rs1, "2");
PreparedStatement pStmt1 = prepareStatement("select c21 from session.t2 for update");
ResultSet rs2 = pStmt1.executeQuery();
rs2.next();
PreparedStatement pStmt2 = prepareStatement("delete from session.t2 where current of " + rs2.getCursorName());
pStmt2.executeUpdate();
rs1 = s.executeQuery("select * from SESSION.t2");
rs1.next();
assertEquals(22, rs1.getInt(1));
assertEquals(1, rs1.getInt(2));
rs2.next();
pStmt2.executeUpdate();
rs1 = s.executeQuery("select count(*) from SESSION.t2");
rs1.next();
assertEquals(0, rs1.getInt(1));
rs2.close();
assertUpdateCount(s, 0, "DROP TABLE SESSION.t2");
}21. UpdatableResultSetTest#testInsertRowWithNullColumn()
View license/**
* Positive test - InsertRow leaving a nullable columns = NULL
*/
public void testInsertRowWithNullColumn() throws SQLException {
createTableT4();
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM t4");
assertTrue("FAIL - row not found", rs.next());
rs.moveToInsertRow();
rs.updateInt(1, 7);
rs.insertRow();
rs.close();
String[][] expected = { { "1", "1" }, { "2", "2" }, { "3", "3" }, { "4", "4" }, { "7", null } };
rs = stmt.executeQuery("SELECT * FROM t4");
JDBC.assertFullResultSet(rs, expected, true);
rs.close();
stmt.close();
}22. TestResultSet#testBeforeFirstAfterLast()
View licenseprivate void testBeforeFirstAfterLast() throws SQLException {
stat.executeUpdate("create table test(id int)");
stat.executeUpdate("insert into test values(1)");
// With a result
ResultSet rs = stat.executeQuery("select * from test");
assertTrue(rs.isBeforeFirst());
assertFalse(rs.isAfterLast());
rs.next();
assertFalse(rs.isBeforeFirst());
assertFalse(rs.isAfterLast());
rs.next();
assertFalse(rs.isBeforeFirst());
assertTrue(rs.isAfterLast());
rs.close();
// With no result
rs = stat.executeQuery("select * from test where 1 = 2");
assertFalse(rs.isBeforeFirst());
assertFalse(rs.isAfterLast());
rs.next();
assertFalse(rs.isBeforeFirst());
assertFalse(rs.isAfterLast());
rs.close();
stat.execute("drop table test");
}23. TestResultSet#testColumnLabelColumnName()
View licenseprivate void testColumnLabelColumnName() throws SQLException {
ResultSet rs = stat.executeQuery("select x as y from dual");
rs.next();
rs.getString("x");
rs.getString("y");
rs.close();
rs = conn.getMetaData().getColumns(null, null, null, null);
ResultSetMetaData meta = rs.getMetaData();
int columnCount = meta.getColumnCount();
String[] columnName = new String[columnCount];
for (int i = 1; i <= columnCount; i++) {
// columnName[i - 1] = meta.getColumnLabel(i);
columnName[i - 1] = meta.getColumnName(i);
}
while (rs.next()) {
for (int i = 0; i < columnCount; i++) {
rs.getObject(columnName[i]);
}
}
}24. TestResultSet#testUpdatePrimaryKey()
View licenseprivate void testUpdatePrimaryKey() throws SQLException {
stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
rs.next();
rs.updateInt(1, 2);
rs.updateRow();
rs.updateInt(1, 3);
rs.updateRow();
stat.execute("DROP TABLE TEST");
}25. BlobClob4BlobTest#testClobAfterCommitWithSecondClob()
View license/**
* Make sure we get an error attempting to access the
* lob after commit.
*/
public void testClobAfterCommitWithSecondClob() throws SQLException {
getConnection().setAutoCommit(false);
Statement s1 = createStatement();
ResultSet rs1 = s1.executeQuery("values cast('first' as clob)");
rs1.next();
Clob first = rs1.getClob(1);
rs1.close();
commit();
Statement s2 = createStatement();
ResultSet rs2 = s2.executeQuery("values cast('second' as clob)");
rs2.next();
Clob second = rs2.getClob(1);
try {
first.getSubString(1, 100);
fail("first.getSubString should have failed because after the commit");
} catch (SQLException se) {
assertSQLState(INVALID_LOB, se);
}
assertEquals("second", second.getSubString(1, 100));
rs2.close();
}26. UpdatableResultSetTest#testUpdateRowOnQuotedTable()
View license/**
* Tests updateRow with table name containing quotes
*/
public void testUpdateRowOnQuotedTable() throws SQLException {
ResultSet rs = null;
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" table\"");
rs.next();
rs.updateInt(1, 4);
rs.updateRow();
rs.close();
rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" table\" " + "order by x");
for (int i = 2; i <= 4; i++) {
assertTrue("there is a row", rs.next());
assertEquals("row contains correct value", i, rs.getInt(1));
}
rs.close();
stmt.close();
}27. UpdatableResultSetTest#testUpdateRowOnQuotedColumn()
View license/**
* Tests updateRow with column name containing quotes
*/
public void testUpdateRowOnQuotedColumn() throws SQLException {
ResultSet rs = null;
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from \"my quoted columns\"");
rs.next();
rs.updateInt(1, 4);
rs.updateRow();
rs.close();
rs = stmt.executeQuery("select * from \"my quoted columns\" " + "order by \"my \"\"quoted\"\" column\"");
for (int i = 2; i <= 4; i++) {
assertTrue("there is a row", rs.next());
assertEquals("row contains correct value", i, rs.getInt(1));
}
rs.close();
stmt.close();
}28. UpdatableResultSetTest#testUpdateRowOnQuotedSchema()
View license/**
* Tests updateRow with schema name containing quotes
*/
public void testUpdateRowOnQuotedSchema() throws SQLException {
ResultSet rs = null;
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" schema\"." + "\"my quoted schema\"");
rs.next();
rs.updateInt(1, 4);
rs.updateRow();
rs.close();
rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" schema\"." + "\"my quoted schema\" order by x");
for (int i = 2; i <= 4; i++) {
assertTrue("there is a row", rs.next());
assertEquals("row contains correct value", i, rs.getInt(1));
}
rs.close();
stmt.close();
}29. UpdatableResultSetTest#testUpdateRowOnQuotedCursor()
View license/**
* Tests updateRow with cursor name containing quotes
*/
public void testUpdateRowOnQuotedCursor() throws SQLException {
ResultSet rs = null;
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
stmt.setCursorName("\"\"my quoted cursor");
rs = stmt.executeQuery("select * from \"my table\"");
rs.next();
rs.updateInt(1, 4);
rs.updateRow();
rs.close();
rs = stmt.executeQuery("select * from \"my table\" order by x");
for (int i = 2; i <= 4; i++) {
assertTrue("there is a row", rs.next());
assertEquals("row contains correct value", i, rs.getInt(1));
}
rs.close();
stmt.close();
}30. ResultSetTest#testUpdateWithPGobject()
View licensepublic void testUpdateWithPGobject() throws SQLException {
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("select * from testpgobject where id = 1");
assertTrue(rs.next());
assertEquals("2010-11-03", rs.getDate("d").toString());
PGobject pgobj = new PGobject();
pgobj.setType("date");
pgobj.setValue("2014-12-23");
rs.updateObject("d", pgobj);
rs.updateRow();
rs.close();
ResultSet rs1 = stmt.executeQuery("select * from testpgobject where id = 1");
assertTrue(rs1.next());
assertEquals("2014-12-23", rs1.getDate("d").toString());
rs1.close();
stmt.close();
}31. TestJoinOnPartitionedTables#testPartitionTableJoinSmallTable()
View license@Test
public void testPartitionTableJoinSmallTable() throws Exception {
executeDDL("customer_ddl.sql", null);
ResultSet res = executeFile("insert_into_customer.sql");
res.close();
res = executeQuery();
assertResultSet(res);
res.close();
res = executeFile("selfJoinOfPartitionedTable.sql");
assertResultSet(res, "selfJoinOfPartitionedTable.result");
res.close();
res = executeFile("testNoProjectionJoinQual.sql");
assertResultSet(res, "testNoProjectionJoinQual.result");
res.close();
res = executeFile("testPartialFilterPushDown.sql");
assertResultSet(res, "testPartialFilterPushDown.result");
res.close();
}32. UpdatableResultSetTest#testUpdateRowWithoutUpdateXXX()
View license/**
* Positive test - issue updateRow without any updateXXX will not move
* the resultset position
*/
public void testUpdateRowWithoutUpdateXXX() throws SQLException {
createTableT1();
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
assertTrue("FAIL - row not found", rs.next());
// this will not move the resultset to right before the next row because
// there were no updateXXX issued before updateRow
rs.updateRow();
rs.updateRow();
rs.close();
// verify that the table is unchanged
String[][] original = { { "1", "aa" }, { "2", "bb" }, { "3", "cc" } };
rs = stmt.executeQuery("select * from t1");
JDBC.assertFullResultSet(rs, original, true);
rs.close();
stmt.close();
}33. UpdatableResultSetTest#testUpdateXXXAndMoveNext()
View license/**
* Positive test - issue updateXXXs and then move off the row, the changes
* should be ignored
*/
public void testUpdateXXXAndMoveNext() throws SQLException {
createTableT1();
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
assertTrue("FAIL - row not found", rs.next());
assertEquals("FAIL - wrong value for column 1", 1, rs.getInt(1));
println(" Issue updateInt to change the column's value to 2345");
rs.updateInt(1, 2345);
// Move to next row w/o issuing updateRow
// the changes made on the earlier row should have be ignored because
// we moved off that row without issuing updateRow
rs.next();
rs.close();
// Make sure that changes didn't make it to the database
String[][] original = { { "1", "aa" }, { "2", "bb" }, { "3", "cc" } };
rs = stmt.executeQuery("select * from t1");
JDBC.assertFullResultSet(rs, original, true);
rs.close();
stmt.close();
}34. UpdatableResultSetTest#testUpdateXXXOnTableColumn()
View license/**
* Positive test - call updateXXX methods on only columns that correspond
* to a column in the table
*/
public void testUpdateXXXOnTableColumn() throws SQLException {
createTableT1();
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT 1, 2, c1, c2 FROM t1");
assertTrue("FAIL - row not found", rs.next());
rs.updateInt(3, 22);
rs.updateRow();
rs.close();
// Make sure that changes made it to the database correctly
String[][] expected = { { "22", "aa" }, { "2", "bb" }, { "3", "cc" } };
rs = stmt.executeQuery("SELECT * FROM t1");
JDBC.assertFullResultSet(rs, expected, true);
rs.close();
stmt.close();
}35. UpdatableResultSetTest#testInsertRowWithDefaultValue()
View license/**
* Positive test - try to insert without updating all columns. All
* columns allow nulls or have a default value
*/
public void testInsertRowWithDefaultValue() throws SQLException {
createTableT5();
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM t5");
assertTrue("FAIL - row not found", rs.next());
rs.moveToInsertRow();
// Should insert a row with NULLS and DEFAULT VALUES
rs.insertRow();
rs.close();
String[][] expected = { { "1", "1" }, { "2", "2" }, { "3", "3" }, { "4", "4" }, { "0", null } };
rs = stmt.executeQuery("SELECT * FROM t5");
JDBC.assertFullResultSet(rs, expected, true);
rs.close();
stmt.close();
}36. IndexStatsUtil#getIdToNameMap()
View license/**
* Generates a map from ids to names for conglomerates in the database.
* <p>
* Convenience method, used for better reporting.
*
* @return Mappings from conglomerate id to conglomerate name.
* @throws SQLException if accessing the system tables fail
*/
private Map<String, String> getIdToNameMap() throws SQLException {
if (psGetIdToNameMapConglom == null) {
psGetIdToNameMapConglom = con.prepareStatement("select CONGLOMERATEID, CONGLOMERATENAME " + "from SYS.SYSCONGLOMERATES");
}
if (psGetIdToNameMapTable == null) {
psGetIdToNameMapTable = con.prepareStatement("select TABLEID, TABLENAME from SYS.SYSTABLES");
}
Map<String, String> map = new HashMap<String, String>();
ResultSet rs = psGetIdToNameMapConglom.executeQuery();
while (rs.next()) {
map.put(rs.getString(1), rs.getString(2));
}
rs.close();
rs = psGetIdToNameMapTable.executeQuery();
while (rs.next()) {
map.put(rs.getString(1), rs.getString(2));
}
rs.close();
return map;
}37. RenameIndexTest#testWithPreparedStatement()
View license/**
* Test RENAME INDEX With Prepared Statement.
*
* @exception SQLException
*/
//-- creating a prepared statement on a table
public void testWithPreparedStatement() throws SQLException {
Statement s = createStatement();
s.executeUpdate("create table t1(c11 int not null primary key, c12 int)");
//-- bug 5685
s.executeUpdate("create index i1 on t1(c11)");
PreparedStatement pstmt = prepareStatement("select * from t1 where c11 > ?");
pstmt.setInt(1, 1);
ResultSet rs = pstmt.executeQuery();
rs.next();
rs.close();
assertStatementError("42X65", s, "rename index i1 to i1r");
//-- statement passes
pstmt.setInt(1, 1);
rs = pstmt.executeQuery();
rs.next();
rs.close();
pstmt.close();
s.executeUpdate("drop table t1");
}38. MergeDatabase#loadUsers()
View licenseprivate void loadUsers() throws SQLException {
ResultSet rs = mergedConn.createStatement().executeQuery("SELECT * FROM users");
while (rs.next()) {
existingUsernames.put(rs.getString("name"), rs.getLong("id"));
}
rs.close();
rs = fromConn.createStatement().executeQuery("SELECT * FROM users");
while (rs.next()) {
long id = rs.getLong("id");
String name = rs.getString("name");
Long newId = id;
if (existingUsernames.containsKey(name)) {
newId = existingUsernames.get(name);
} else {
newId += ID_OFFSET;
existingUsernames.put(name, id);
}
usernameIdMap.put(id, newId);
}
rs.close();
}39. TableFunctionTest#derby_6151()
View license/**
* <p>
* Verify that warnings percolate back from table functions.
* </p>
*/
private void derby_6151() throws Exception {
goodStatement("create function warningVTI() returns table( a int, b varchar( 5 ) )\n" + "language java parameter style derby_jdbc_result_set no sql\n" + "external name '" + getClass().getName() + ".warningVTI'\n");
ResultSet rs = getConnection().prepareStatement("select * from table( warningVTI() ) t").executeQuery();
rs.next();
assertEquals("Warning for row 1", rs.getWarnings().getMessage());
rs.clearWarnings();
rs.next();
assertEquals("Warning for row 2", rs.getWarnings().getMessage());
rs.close();
goodStatement("drop function warningVTI");
}40. UpdatableResultSetTest#testUpdatableResultSetWithoutSelectingPrimaryKey()
View license/**
* Positive test - donot have to select primary key to get an updatable
* resultset
*/
public void testUpdatableResultSetWithoutSelectingPrimaryKey() throws SQLException {
createTableT3();
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT c32 FROM t3");
assertTrue("FAIL - statement should return a row", rs.next());
assertEquals("FAIL - wrong value for column 1", 1, rs.getInt(1));
// now try to delete row when primary key is not selected for that row
rs.deleteRow();
assertTrue("FAIL - statement should return a row", rs.next());
rs.updateLong(1, 123);
rs.updateRow();
rs.close();
// verify that the table was correctly update
String[][] expected = { { "2", "123" }, { "3", "3" }, { "4", "4" } };
JDBC.assertFullResultSet(stmt.executeQuery("select * from t3"), expected, true);
stmt.close();
}41. UpdateableResultTest#testUpdateablePreparedStatement()
View licensepublic void testUpdateablePreparedStatement() throws Exception {
PreparedStatement st = con.prepareStatement("select * from updateable", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery();
rs.moveToInsertRow();
rs.close();
st.close();
st = con.prepareStatement("select * from updateable where id = ?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
st.setInt(1, 1);
rs = st.executeQuery();
rs.moveToInsertRow();
rs.close();
st.close();
}42. UpdateableResultTest#testMultiColumnUpdate()
View licensepublic void testMultiColumnUpdate() throws Exception {
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
st.executeUpdate("INSERT INTO multicol (id1,id2,val) VALUES (1,2,'val')");
ResultSet rs = st.executeQuery("SELECT id1, id2, val FROM multicol");
assertTrue(rs.next());
assertEquals("val", rs.getString("val"));
rs.updateString("val", "newval");
rs.updateRow();
rs.close();
rs = st.executeQuery("SELECT id1, id2, val FROM multicol");
assertTrue(rs.next());
assertEquals("newval", rs.getString("val"));
rs.close();
st.close();
}43. TestResultSet#testReuseSimpleResult()
View licenseprivate void testReuseSimpleResult() throws SQLException {
ResultSet rs = stat.executeQuery("select table(x array=((1)))");
while (rs.next()) {
rs.getString(1);
}
rs.close();
rs = stat.executeQuery("select table(x array=((1)))");
while (rs.next()) {
rs.getString(1);
}
rs.close();
}44. TestResultSet#testOwnUpdates()
View licenseprivate void testOwnUpdates() throws SQLException {
DatabaseMetaData meta = conn.getMetaData();
for (int i = 0; i < 3; i++) {
int type = i == 0 ? ResultSet.TYPE_FORWARD_ONLY : i == 1 ? ResultSet.TYPE_SCROLL_INSENSITIVE : ResultSet.TYPE_SCROLL_SENSITIVE;
assertTrue(meta.ownUpdatesAreVisible(type));
assertFalse(meta.ownDeletesAreVisible(type));
assertFalse(meta.ownInsertsAreVisible(type));
}
stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
stat.execute("INSERT INTO TEST VALUES(2, 'World')");
ResultSet rs;
rs = stat.executeQuery("SELECT ID, NAME FROM TEST ORDER BY ID");
rs.next();
rs.next();
rs.updateString(2, "Hallo");
rs.updateRow();
assertEquals("Hallo", rs.getString(2));
stat.execute("DROP TABLE TEST");
}45. TestFunctionOverload#testOverload()
View licenseprivate void testOverload() throws SQLException {
Statement stat = conn.createStatement();
stat.execute("create alias overload1or2 for \"" + ME + ".overload1or2\"");
ResultSet rs = stat.executeQuery("select overload1or2(1) from dual");
rs.next();
assertEquals("1 arg", 1, rs.getInt(1));
assertFalse(rs.next());
rs = stat.executeQuery("select overload1or2(1, 2) from dual");
rs.next();
assertEquals("2 args", 3, rs.getInt(1));
assertFalse(rs.next());
rs = meta.getProcedures(null, null, "OVERLOAD1OR2");
rs.next();
assertEquals(1, rs.getInt("NUM_INPUT_PARAMS"));
rs.next();
assertEquals(2, rs.getInt("NUM_INPUT_PARAMS"));
assertFalse(rs.next());
}46. TestFunctionOverload#testOverloadWithConnection()
View licenseprivate void testOverloadWithConnection() throws SQLException {
Statement stat = conn.createStatement();
stat.execute("create alias overload1or2WithConn for \"" + ME + ".overload1or2WithConn\"");
ResultSet rs = stat.executeQuery("select overload1or2WithConn(1) from dual");
rs.next();
assertEquals("1 arg", 1, rs.getInt(1));
assertFalse(rs.next());
rs.close();
rs = stat.executeQuery("select overload1or2WithConn(1, 2) from dual");
rs.next();
assertEquals("2 args", 3, rs.getInt(1));
assertFalse(rs.next());
rs.close();
stat.close();
}47. UpdateableResultTest#testUpdateablePreparedStatement()
View license@Test
public void testUpdateablePreparedStatement() throws Exception {
PreparedStatement st = con.prepareStatement("select * from updateable", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery();
rs.moveToInsertRow();
rs.close();
st.close();
st = con.prepareStatement("select * from updateable where id = ?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
st.setInt(1, 1);
rs = st.executeQuery();
rs.moveToInsertRow();
rs.close();
st.close();
}48. TestOptimizations#testAnalyzeLob()
View licenseprivate void testAnalyzeLob() throws Exception {
Connection conn = getConnection("optimizations");
Statement stat = conn.createStatement();
stat.execute("create table test(v varchar, b binary, cl clob, bl blob) as " + "select ' ', '00', ' ', '00' from system_range(1, 100)");
stat.execute("analyze");
ResultSet rs = stat.executeQuery("select column_name, selectivity " + "from information_schema.columns where table_name='TEST'");
rs.next();
assertEquals("V", rs.getString(1));
assertEquals(1, rs.getInt(2));
rs.next();
assertEquals("B", rs.getString(1));
assertEquals(1, rs.getInt(2));
rs.next();
assertEquals("CL", rs.getString(1));
assertEquals(50, rs.getInt(2));
rs.next();
assertEquals("BL", rs.getString(1));
assertEquals(50, rs.getInt(2));
stat.execute("drop table test");
conn.close();
}49. SURTest#testIndexedScrollInsensitiveUpdateCursorWithForUpdate1()
View license/**
* Test that you can scroll forward and update indexed records
* in the scrollable ResultSet (using FOR UPDATE).
*/
public void testIndexedScrollInsensitiveUpdateCursorWithForUpdate1() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
s.setCursorName(getNextCursorName());
ResultSet rs = s.executeQuery("select * from t1 where a=1 or a=2 for update");
rs.next();
rs.next();
rs.previous();
verifyTuple(rs);
updateTuple(rs);
rs.close();
s.close();
}50. UpdatableResultSetTest#testDeleteRowOnQuotedTable()
View license/**
* Tests deleteRow with table name containing quotes
*/
public void testDeleteRowOnQuotedTable() throws SQLException {
ResultSet rs = null;
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" table\"");
rs.next();
rs.deleteRow();
rs.close();
rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" table\" " + "order by x");
for (int i = 2; i <= 3; i++) {
assertTrue("there is a row", rs.next());
assertEquals("row contains correct value", i, rs.getInt(1));
}
rs.close();
stmt.close();
}51. UpdatableResultSetTest#testDeleteRowOnQuotedColumn()
View license/**
* Tests deleteRow with column name containing quotes
*/
public void testDeleteRowOnQuotedColumn() throws SQLException {
ResultSet rs = null;
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from \"my quoted columns\"");
rs.next();
rs.deleteRow();
rs.close();
rs = stmt.executeQuery("select * from \"my quoted columns\" " + "order by \"my \"\"quoted\"\" column\"");
for (int i = 2; i <= 3; i++) {
assertTrue("there is a row", rs.next());
assertEquals("row contains correct value", i, rs.getInt(1));
}
rs.close();
stmt.close();
}52. UpdatableResultSetTest#testDeleteRowOnQuotedSchema()
View license/**
* Tests deleteRow with schema name containing quotes
*/
public void testDeleteRowOnQuotedSchema() throws SQLException {
ResultSet rs = null;
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" schema\"." + "\"my quoted schema\"");
rs.next();
rs.deleteRow();
rs.close();
rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" schema\"." + "\"my quoted schema\" order by x");
for (int i = 2; i <= 3; i++) {
assertTrue("there is a row", rs.next());
assertEquals("row contains correct value", i, rs.getInt(1));
}
rs.close();
stmt.close();
}53. UpdatableResultSetTest#testDeleteRowOnQuotedCursor()
View license/**
* Tests deleteRow with cursor name containing quotes
*/
public void testDeleteRowOnQuotedCursor() throws SQLException {
ResultSet rs = null;
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
stmt.setCursorName("\"\"my quoted cursor\"\"");
rs = stmt.executeQuery("select * from \"my table\"");
rs.next();
rs.deleteRow();
rs.close();
rs = stmt.executeQuery("select * from \"my table\" order by x");
for (int i = 2; i <= 3; i++) {
assertTrue("there is a row", rs.next());
assertEquals("row contains correct value", i, rs.getInt(1));
}
rs.close();
stmt.close();
}54. LOBLocatorReleaseTest#testScrollableAbsoluteRow()
View license/**
* Tests that absolute positioning can be called for the same row multiple
* times on a scrollable resultset.
*/
public void testScrollableAbsoluteRow() throws SQLException {
getConnection().setAutoCommit(false);
Statement stmt = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("select dBlob, dClob from LOBLOC_NO_NULLS");
rs.next();
rs.absolute(4);
rs.absolute(4);
rs.absolute(4);
}55. LobSortTest#fetchIterateGetLengthBlob()
View license/**
* Executes the specified query two times, materializes the Blob on the
* first run and gets the length through {@code Blob.length} on the second.
* <p>
* Note that the query must select a Blob column at index one and the length
* at index two.
*
* @param sql query to execute
* @throws SQLException if the test fails for some reason
*/
private void fetchIterateGetLengthBlob(String sql) throws SQLException {
Statement stmt = createStatement();
ResultSet rs = stmt.executeQuery(sql);
// Materialize the BLOB value.
while (rs.next()) {
assertEquals(rs.getInt(1), rs.getBytes(2).length);
}
rs.close();
rs = stmt.executeQuery(sql);
// Get the BLOB value length through Blob.length
while (rs.next()) {
assertEquals(rs.getInt(1), (int) rs.getBlob(2).length());
}
rs.close();
stmt.close();
}56. LobSortTest#fetchIterateGetLengthClob()
View license/**
* Executes the specified query two times, materializes the Clob on the
* first run and gets the length through {@code Clob.length} on the second.
* <p>
* Note that the query must select a Clob column at index one and the length
* at index two.
*
* @param sql query to execute
* @throws SQLException if the test fails for some reason
*/
private void fetchIterateGetLengthClob(String sql) throws SQLException {
Statement stmt = createStatement();
ResultSet rs = stmt.executeQuery(sql);
// Materialize the CLOB value.
while (rs.next()) {
assertEquals(rs.getInt(1), rs.getString(2).length());
}
rs.close();
rs = stmt.executeQuery(sql);
// Get the CLOB value length through Clob.length
while (rs.next()) {
assertEquals(rs.getInt(1), (int) rs.getClob(2).length());
}
rs.close();
stmt.close();
}57. TestBigResult#testLimitBufferedResult()
View licenseprivate void testLimitBufferedResult() throws SQLException {
deleteDb("bigResult");
Connection conn = getConnection("bigResult");
Statement stat = conn.createStatement();
stat.execute("DROP TABLE IF EXISTS TEST");
stat.execute("CREATE TABLE TEST(ID INT)");
for (int i = 0; i < 200; i++) {
stat.execute("INSERT INTO TEST(ID) VALUES(" + i + ")");
}
stat.execute("SET MAX_MEMORY_ROWS 100");
ResultSet rs;
rs = stat.executeQuery("select id from test order by id limit 10 offset 85");
for (int i = 85; rs.next(); i++) {
assertEquals(i, rs.getInt(1));
}
rs = stat.executeQuery("select id from test order by id limit 10 offset 95");
for (int i = 95; rs.next(); i++) {
assertEquals(i, rs.getInt(1));
}
rs = stat.executeQuery("select id from test order by id limit 10 offset 105");
for (int i = 105; rs.next(); i++) {
assertEquals(i, rs.getInt(1));
}
conn.close();
}58. HiveJDBCClientTest#testShowExtendedTable()
View license//@Test
public void testShowExtendedTable() throws SQLException {
Connection con = getHiveConnection();
String tableName = "testHiveDriverTable";
String sql = "show table extended like " + tableName + "";
ResultSet res = executeSQL(con, sql);
while (res.next()) {
System.out.println("--- " + res.getString("tab_name"));
}
sql = "describe extended " + tableName + "";
res = executeSQL(con, sql);
while (res.next()) {
System.out.println("---" + res.getString(1) + " | " + res.getString(2));
}
res.close();
con.close();
}59. UpdateableResultTest#testDeleteRows()
View licensepublic void testDeleteRows() throws SQLException {
Statement st = con.createStatement();
st.executeUpdate("INSERT INTO second values (2,'two')");
st.executeUpdate("INSERT INTO second values (3,'three')");
st.executeUpdate("INSERT INTO second values (4,'four')");
st.close();
st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery("select id1,name1 from second order by id1");
assertTrue(rs.next());
assertEquals(1, rs.getInt("id1"));
rs.deleteRow();
assertTrue(rs.isBeforeFirst());
assertTrue(rs.next());
assertTrue(rs.next());
assertEquals(3, rs.getInt("id1"));
rs.deleteRow();
assertEquals(2, rs.getInt("id1"));
rs.close();
st.close();
}60. RolesConferredPrivilegesTest#formatArgs()
View license/**
* Format the dbObject arguments used by the various assert* methods for
* printing.
*/
private static String formatArgs(Connection c, String schema, String dbObject) throws SQLException {
ResultSet rs;
Statement stm = c.createStatement();
rs = stm.executeQuery("values current_user");
rs.next();
String user = rs.getString(1);
rs = c.createStatement().executeQuery("values current_role");
rs.next();
String role = rs.getString(1);
rs.close();
stm.close();
return "User: " + user + (role == null ? "" : " Role: " + role) + " Object: " + schema + "." + dbObject;
}61. UpdateEncryptionMethod#doExecuteStatement()
View license@Override
protected void doExecuteStatement(Statement statement) throws SQLException {
ResultSet rs = statement.executeQuery("select apiKey, secret from App");
while (rs.next()) {
rs.updateString("apiKey", encrypt(decrypt(rs.getString("apiKey"))));
rs.updateString("secret", encrypt(decrypt(rs.getString("secret"))));
rs.updateRow();
}
rs = statement.executeQuery("select accessToken, secret from AppConnection");
while (rs.next()) {
rs.updateString("accessToken", encrypt(decrypt(rs.getString("accessToken"))));
rs.updateString("secret", encrypt(decrypt(rs.getString("secret"))));
rs.updateRow();
}
rs = statement.executeQuery("select member, provider, accessToken, secret from AccountConnection");
while (rs.next()) {
rs.updateString("accessToken", encrypt(decrypt(rs.getString("accessToken"))));
String secret = rs.getString("secret");
if (secret != null) {
rs.updateString("secret", encrypt(decrypt(secret)));
}
rs.updateRow();
}
}62. Changes10_3#testCompilationSchema()
View license/**
* Verify the compilation schema is nullable after upgrade to 10.3
* or later. (See DERBY-630)
* @throws SQLException
*/
public void testCompilationSchema() throws SQLException {
switch(getPhase()) {
case PH_CREATE:
case PH_POST_SOFT_UPGRADE:
// 10.0-10.2 inclusive had the system schema incorrect.
if (!oldAtLeast(10, 3))
return;
break;
}
DatabaseMetaData dmd = getConnection().getMetaData();
ResultSet rs = dmd.getColumns(null, "SYS", "SYSSTATEMENTS", "COMPILATIONSCHEMAID");
rs.next();
assertEquals("SYS.SYSSTATEMENTS.COMPILATIONSCHEMAID IS_NULLABLE", "YES", rs.getString("IS_NULLABLE"));
rs.close();
rs = dmd.getColumns(null, "SYS", "SYSVIEWS", "COMPILATIONSCHEMAID");
rs.next();
assertEquals("SYS.SYSVIEWS.COMPILATIONSCHEMAID IS_NULLABLE", "YES", rs.getString("IS_NULLABLE"));
}63. UpdatableResultSetTest#testUpdateRowSelfReferential()
View license/**
* Positive test - make sure self referential update restrict works when
* updateRow is issued
*/
public void testUpdateRowSelfReferential() throws SQLException {
createSelfReferencingTable();
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("select * from selfReferencingT2");
String[][] expected = { { "e1", null }, { "e2", "e1" }, { "e3", "e2" }, { "e4", "e3" } };
JDBC.assertFullResultSet(rs, expected, true);
rs.close();
rs = stmt.executeQuery("SELECT * FROM selfReferencingT2 FOR UPDATE");
assertTrue("FAIL - row not found", rs.next());
assertEquals("FAIL - wrong value for column1", "e1", rs.getString(1));
// update row should fail because cascade constraint is update restrict
rs.updateString(1, "e2");
try {
rs.updateRow();
fail("FAIL - this update should have caused violation of foreign " + "key constraint");
} catch (SQLException e) {
assertSQLState("23503", e);
}
rs.close();
stmt.close();
}64. UpdatableResultSetTest#testSetFetchSizeOnUpdatableResultSet()
View license/**
* Positive test - setting the fetch size to > 1 will be ignored by
* updatable resultset. Same as updatable cursors
*/
public void testSetFetchSizeOnUpdatableResultSet() throws SQLException {
createTableT1();
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
stmt.setFetchSize(200);
ResultSet rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
// Check the Fetch Size in run time statistics output
Statement stmt2 = createStatement();
ResultSet rs2 = stmt2.executeQuery("values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
while (rs2.next()) {
if (rs2.getString(1).startsWith("Fetch Size")) {
assertEquals("FAIL - wrong fetch size", "Fetch Size = 1", rs2.getString(1));
}
}
assertEquals("FAIL - wrong fetch size for updatable cursor", 200, stmt.getFetchSize());
rs.close();
stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
stmt.close();
}65. UpdatableResultSetTest#testUpdateRowWithCorrelationOnTableAndColumn()
View license/**
* Positive test - try to get an updatable resultset using correlation name
* for a readonly column
*/
public void testUpdateRowWithCorrelationOnTableAndColumn() throws SQLException {
createTableT1();
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
// attempt to get an updatable resultset using correlation name for a
// readonly column. It should work
ResultSet rs = stmt.executeQuery("SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
assertTrue("FAIL - row not found", rs.next());
rs.updateInt(1, 11);
rs.updateRow();
rs.close();
// verify that the table was correctly update
String[][] expected = { { "11", "aa" }, { "2", "bb" }, { "3", "cc" } };
JDBC.assertFullResultSet(stmt.executeQuery("SELECT * FROM t1"), expected, true);
stmt.close();
}66. UpdatableResultSetTest#testRowUpdated()
View license/**
* Positive test - For Forward Only resultsets, DatabaseMetaData will return
* false for ownUpdatesAreVisible and updatesAreDetected
* This is because, after updateRow, we position the ResultSet before the
* next row
*/
public void testRowUpdated() throws SQLException {
DatabaseMetaData dbmt = getConnection().getMetaData();
assertEquals("FAIL - wrong values for ownUpdatesAreVisible(" + "ResultSet.TYPE_FORWARD_ONLY)", false, dbmt.ownUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
assertEquals("FAIL - wrong values for othersUpdatesAreVisible(" + "ResultSet.TYPE_FORWARD_ONLY)", true, dbmt.othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
assertEquals("FAIL - wrong values for updatesAreDetected(" + "ResultSet.TYPE_FORWARD_ONLY)", false, dbmt.updatesAreDetected(ResultSet.TYPE_FORWARD_ONLY));
createTableT1();
Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
assertTrue("FAIL - statement should return a row", rs.next());
assertFalse("FAIL - rs.rowUpdated() should always return false for " + "this type of result set", rs.rowUpdated());
rs.updateLong(1, 123);
rs.updateRow();
rs.close();
stmt.close();
}67. SystemCatalogTest#testNewTableInSystemCatalogs()
View license/**
* Check that a newly created table and its columns appear in SYSTABLES and SYSCOLUMNS
* @throws SQLException
*/
public void testNewTableInSystemCatalogs() throws SQLException {
Statement s = createStatement();
s.execute("create table t (i int, s smallint)");
ResultSet rs = s.executeQuery("select TABLETYPE from sys.systables where tablename = 'T'");
JDBC.assertSingleValueResultSet(rs, "T");
rs.close();
rs = s.executeQuery("select TABLENAME, COLUMNNAME, COLUMNNUMBER, columndatatype from sys.systables t, sys.syscolumns c" + " where t.TABLEID=c.REFERENCEID and t.tablename = 'T' order by TABLENAME, COLUMNNAME");
String[][] expected = { { "T", "I", "1", "INTEGER" }, { "T", "S", "2", "SMALLINT" } };
JDBC.assertFullResultSet(rs, expected);
rs.close();
rs = s.executeQuery("select TABLENAME, ISINDEX from sys.systables t, sys.sysconglomerates c where t.TABLEID=c.TABLEID and t.TABLENAME = 'T' order by TABLENAME, ISINDEX");
expected = new String[][] { { "T", "false" } };
JDBC.assertFullResultSet(rs, expected);
rs.close();
s.execute("drop table t");
s.close();
}68. SysDiagVTIMappingTest#test_5391()
View license/**
* Test date formatting in the vtis which read the error log. This attempts
* to keep us from breaking these vtis if the format of logged timestamps
* changes. See DERBY-5391.
*/
public void test_5391() throws Exception {
Statement st = createStatement();
ResultSet rs1 = st.executeQuery("select * from table (syscs_diag.error_log_reader( )) as t1");
vetTimestamp(rs1);
rs1.close();
ResultSet rs2 = st.executeQuery("select * from table (syscs_diag.statement_duration()) as t1");
vetTimestamp(rs2);
rs2.close();
st.close();
}69. TestFunctions#testTransactionId()
View licenseprivate void testTransactionId() throws SQLException {
if (config.memory) {
return;
}
Connection conn = getConnection("functions");
Statement stat = conn.createStatement();
stat.execute("create table test(id int)");
ResultSet rs;
rs = stat.executeQuery("call transaction_id()");
rs.next();
assertTrue(rs.getString(1) == null && rs.wasNull());
stat.execute("insert into test values(1)");
rs = stat.executeQuery("call transaction_id()");
rs.next();
assertTrue(rs.getString(1) == null && rs.wasNull());
conn.setAutoCommit(false);
stat.execute("delete from test");
rs = stat.executeQuery("call transaction_id()");
rs.next();
assertTrue(rs.getString(1) != null);
stat.execute("drop table test");
conn.close();
}70. TestFunctions#testGreatest()
View licenseprivate void testGreatest() throws SQLException {
Connection conn = getConnection("functions");
Statement stat = conn.createStatement();
String createSQL = "CREATE TABLE testGreatest (id BIGINT);";
stat.execute(createSQL);
stat.execute("insert into testGreatest values (1)");
String query = "SELECT GREATEST(id, " + ((long) Integer.MAX_VALUE) + ") FROM testGreatest";
ResultSet rs = stat.executeQuery(query);
rs.next();
Object o = rs.getObject(1);
assertEquals(Long.class.getName(), o.getClass().getName());
String query2 = "SELECT GREATEST(id, " + ((long) Integer.MAX_VALUE + 1) + ") FROM testGreatest";
ResultSet rs2 = stat.executeQuery(query2);
rs2.next();
Object o2 = rs2.getObject(1);
assertEquals(Long.class.getName(), o2.getClass().getName());
conn.close();
}71. TestCases#testPersistentSettings()
View licenseprivate void testPersistentSettings() throws SQLException {
deleteDb("cases");
Connection conn = getConnection("cases");
Statement stat = conn.createStatement();
stat.execute("SET COLLATION de_DE");
stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, " + "NAME VARCHAR)");
stat.execute("CREATE INDEX IDXNAME ON TEST(NAME)");
stat.execute("INSERT INTO TEST VALUES(1, 'Böhlen'), " + "(2, 'Bach'), (3, 'Bucher')");
conn.close();
conn = getConnection("cases");
ResultSet rs = conn.createStatement().executeQuery("SELECT NAME FROM TEST ORDER BY NAME");
rs.next();
assertEquals("Bach", rs.getString(1));
rs.next();
assertEquals("Böhlen", rs.getString(1));
rs.next();
assertEquals("Bucher", rs.getString(1));
conn.close();
}72. TestCases#testExecuteTrace()
View licenseprivate void testExecuteTrace() throws SQLException {
deleteDb("cases");
Connection conn = getConnection("cases");
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("SELECT ? FROM DUAL {1: 'Hello'}");
rs.next();
assertEquals("Hello", rs.getString(1));
assertFalse(rs.next());
rs = stat.executeQuery("SELECT ? FROM DUAL UNION ALL " + "SELECT ? FROM DUAL {1: 'Hello', 2:'World' }");
rs.next();
assertEquals("Hello", rs.getString(1));
rs.next();
assertEquals("World", rs.getString(1));
assertFalse(rs.next());
conn.close();
}73. TestCases#testLobDecrypt()
View licenseprivate void testLobDecrypt() throws SQLException {
Connection conn = getConnection("cases");
String key = "key";
String value = "Hello World";
PreparedStatement prep = conn.prepareStatement("CALL ENCRYPT('AES', RAWTOHEX(?), STRINGTOUTF8(?))");
prep.setCharacterStream(1, new StringReader(key), -1);
prep.setCharacterStream(2, new StringReader(value), -1);
ResultSet rs = prep.executeQuery();
rs.next();
String encrypted = rs.getString(1);
PreparedStatement prep2 = conn.prepareStatement("CALL TRIM(CHAR(0) FROM " + "UTF8TOSTRING(DECRYPT('AES', RAWTOHEX(?), ?)))");
prep2.setCharacterStream(1, new StringReader(key), -1);
prep2.setCharacterStream(2, new StringReader(encrypted), -1);
ResultSet rs2 = prep2.executeQuery();
rs2.first();
String decrypted = rs2.getString(1);
prep2.close();
assertEquals(value, decrypted);
conn.close();
}74. PreparedStatementTest#testRowId()
View license@Test
public void testRowId() throws SQLException {
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO texttable (te) VALUES (?)", new String[] { "ctid" });
pstmt.setString(1, "some text");
pstmt.executeUpdate();
ResultSet keys = pstmt.getGeneratedKeys();
assertTrue(keys.next());
RowId rowId = keys.getRowId(1);
keys.close();
pstmt.close();
pstmt = conn.prepareStatement("SELECT te FROM texttable WHERE ctid = ?");
pstmt.setRowId(1, rowId);
ResultSet rs = pstmt.executeQuery();
assertTrue(rs.next());
assertEquals("some text", rs.getString(1));
rs.close();
pstmt.close();
}75. PreparedStatementTest#testFloat()
View license@Test
public void testFloat() throws SQLException {
PreparedStatement pstmt = conn.prepareStatement("CREATE TEMP TABLE float_tab (max_float real, min_float real, null_value real)");
pstmt.executeUpdate();
pstmt.close();
pstmt = conn.prepareStatement("insert into float_tab values (?,?,?)");
pstmt.setFloat(1, (float) 1.0E37);
pstmt.setFloat(2, (float) 1.0E-37);
pstmt.setNull(3, Types.FLOAT);
pstmt.executeUpdate();
pstmt.close();
pstmt = conn.prepareStatement("select * from float_tab");
ResultSet rs = pstmt.executeQuery();
assertTrue(rs.next());
rs.getFloat(1);
assertTrue("expected 1.0E37,received " + rs.getFloat(1), rs.getFloat(1) == (float) 1.0E37);
assertTrue("expected 1.0E-37,received " + rs.getFloat(2), rs.getFloat(2) == (float) 1.0E-37);
rs.getDouble(3);
assertTrue(rs.wasNull());
rs.close();
pstmt.close();
}76. PreparedStatementTest#testDouble()
View license@Test
public void testDouble() throws SQLException {
PreparedStatement pstmt = conn.prepareStatement("CREATE TEMP TABLE double_tab (max_double float, min_double float, null_value float)");
pstmt.executeUpdate();
pstmt.close();
pstmt = conn.prepareStatement("insert into double_tab values (?,?,?)");
pstmt.setDouble(1, 1.0E125);
pstmt.setDouble(2, 1.0E-130);
pstmt.setNull(3, Types.DOUBLE);
pstmt.executeUpdate();
pstmt.close();
pstmt = conn.prepareStatement("select * from double_tab");
ResultSet rs = pstmt.executeQuery();
assertTrue(rs.next());
rs.getDouble(1);
assertTrue(rs.getDouble(1) == 1.0E125);
assertTrue(rs.getDouble(2) == 1.0E-130);
rs.getDouble(3);
assertTrue(rs.wasNull());
rs.close();
pstmt.close();
}77. CursorFetchTest#testResultSetFetchSizeFour()
View license// test four:
// set fetchsize = 50
// run query (50 rows fetched)
// set fetchsize = 25
// process results:
// process 50 rows.
// do a FETCH FORWARD 25
// process 25 rows
// do a FETCH FORWARD 25
// process 25 rows. end of results.
@Test
public void testResultSetFetchSizeFour() throws Exception {
createRows(100);
PreparedStatement stmt = con.prepareStatement("select * from test_fetch order by value");
stmt.setFetchSize(50);
ResultSet rs = stmt.executeQuery();
rs.setFetchSize(25);
int count = 0;
while (rs.next()) {
assertEquals(count, rs.getInt(1));
++count;
}
assertEquals(100, count);
rs.close();
stmt.close();
}78. CursorFetchTest#testResultSetFetchSizeThree()
View license// test three:
// set fetchsize = 25
// run query (25 rows fetched)
// set fetchsize = 50
// process results:
// process 25 rows. should NOT hit end-of-results here.
// do a FETCH FORWARD 50
// process 50 rows
// do a FETCH FORWARD 50
// process 25 rows. end of results.
@Test
public void testResultSetFetchSizeThree() throws Exception {
createRows(100);
PreparedStatement stmt = con.prepareStatement("select * from test_fetch order by value");
stmt.setFetchSize(25);
ResultSet rs = stmt.executeQuery();
rs.setFetchSize(50);
int count = 0;
while (rs.next()) {
assertEquals(count, rs.getInt(1));
++count;
}
assertEquals(100, count);
rs.close();
stmt.close();
}79. CursorFetchTest#testResultSetFetchSizeTwo()
View license// test two:
// set fetchsize = 25
// run query (25 rows fetched)
// set fetchsize = 0
// process results:
// process 25 rows
// should do a FETCH ALL to get more data
// process 75 rows
@Test
public void testResultSetFetchSizeTwo() throws Exception {
createRows(100);
PreparedStatement stmt = con.prepareStatement("select * from test_fetch order by value");
stmt.setFetchSize(25);
ResultSet rs = stmt.executeQuery();
rs.setFetchSize(0);
int count = 0;
while (rs.next()) {
assertEquals(count, rs.getInt(1));
++count;
}
assertEquals(100, count);
rs.close();
stmt.close();
}80. CursorFetchTest#testResultSetFetchSizeOne()
View license//
// Tests for ResultSet.setFetchSize().
//
// test one:
// set fetchsize = 0
// run query (all rows should be fetched)
// set fetchsize = 50 (should have no effect)
// process results
@Test
public void testResultSetFetchSizeOne() throws Exception {
createRows(100);
PreparedStatement stmt = con.prepareStatement("select * from test_fetch order by value");
stmt.setFetchSize(0);
ResultSet rs = stmt.executeQuery();
// Should have no effect.
rs.setFetchSize(50);
int count = 0;
while (rs.next()) {
assertEquals(count, rs.getInt(1));
++count;
}
assertEquals(100, count);
rs.close();
stmt.close();
}81. StatementTest#getGeneratedKeys()
View license@Test
public void getGeneratedKeys() throws SQLException {
ResultSet rs;
stat.executeUpdate("create table t1 (c1 integer primary key, v);");
stat.executeUpdate("insert into t1 (v) values ('red');");
rs = stat.getGeneratedKeys();
assertTrue(rs.next());
assertEquals(rs.getInt(1), 1);
rs.close();
stat.executeUpdate("insert into t1 (v) values ('blue');");
rs = stat.getGeneratedKeys();
assertTrue(rs.next());
assertEquals(rs.getInt(1), 2);
rs.close();
// closing one statement shouldn't close shared db metadata object.
stat.close();
Statement stat2 = conn.createStatement();
rs = stat2.getGeneratedKeys();
assertNotNull(rs);
rs.close();
stat2.close();
}82. J2EEDataSourceTest#testDerby3799()
View license/**
* Regression test for a NullPointerException when trying to use the LOB
* stored procedures after closing and then getting a new logical
* connection. The problem was that the LOB stored procedure objects on the
* server side were closed and not reprepared.
* See Jira issue DERBY-3799.
*/
public void testDerby3799() throws SQLException {
ConnectionPoolDataSource cpDs = J2EEDataSource.getConnectionPoolDataSource();
PooledConnection pc = cpDs.getPooledConnection();
// Get first logical connection.
Connection con1 = pc.getConnection();
Statement stmt = con1.createStatement();
ResultSet rs = stmt.executeQuery("select dClob from derby3799");
assertTrue(rs.next());
rs.getString(1);
rs.close();
con1.close();
// Get second logical connection.
Connection con2 = pc.getConnection();
stmt = con2.createStatement();
rs = stmt.executeQuery("select dClob from derby3799");
assertTrue(rs.next());
// NPE happened here.
rs.getString(1);
con2.close();
}83. ScrollResultSetTest#testNextOnLastRowScrollable()
View license/**
* Test that moving to next row after positioned at the last row on a
* scrollable result set will not close the result set
*/
public void testNextOnLastRowScrollable() throws SQLException {
Connection con = getConnection();
con.setAutoCommit(true);
con.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
Statement roStmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = roStmt.executeQuery("SELECT c1 FROM tableWithPK");
// move to last position and then call next
rs.last();
rs.next();
// scrollable result set should still be open and not throw no
// exception will be thrown
assertFalse("Calling next while positioned after last returns " + "false", rs.next());
assertTrue("Moving to absolute(2) returns true", rs.absolute(2));
rs.close();
}84. SURTest#testIndexedScrollInsensitiveUpdateCursorWithoutForUpdate1()
View license/**
* Test that you can scroll forward and update indexed records
* in the scrollable ResultSet (not using FOR UPDATE).
*/
public void testIndexedScrollInsensitiveUpdateCursorWithoutForUpdate1() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
s.setCursorName(getNextCursorName());
ResultSet rs = s.executeQuery("select * from t1 where a=1 or a=2");
rs.next();
rs.next();
rs.previous();
verifyTuple(rs);
updateTuple(rs);
s.close();
}85. HoldCursorTest#testHoldCursorOnMultiTableQuery()
View license/**
* test cursor holdability after commit on multi table query
* @throws Exception
*/
public void testHoldCursorOnMultiTableQuery() throws Exception {
ResultSet rs;
Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
//open a cursor with multiple rows resultset
rs = s.executeQuery("select t1.c11, t2.c22 from t1, t2 where t1.c11=t2.c21");
rs.next();
assertEquals("1", rs.getString(2));
commit();
//because holdability is true, should be able to navigate the cursor after commit
rs.next();
assertEquals("2", rs.getString(2));
rs.close();
}86. BLOBTest#testUpdateBlobFromScrollableResultSetUsingPositionedUpdates()
View license/**
* Tests updating a Blob from a scollable resultset, using
* positioned updates.
* @exception SQLException causes test to fail with error
* @exception IOException causes test to fail with error
*/
public void testUpdateBlobFromScrollableResultSetUsingPositionedUpdates() throws SQLException, IOException {
final Statement stmt = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
final ResultSet rs = stmt.executeQuery("SELECT * from " + BLOBDataModelSetup.getBlobTableName());
println("Last");
rs.last();
final int newVal = rs.getInt(1) + 11;
final int newSize = rs.getInt(2) / 2;
testUpdateBlobWithPositionedUpdate(rs, newVal, newSize);
// Necessary after a positioned update
rs.relative(0);
println("Verify updated blob using result set");
verifyBlob(newVal, newSize, rs.getBlob(3));
rs.close();
stmt.close();
}87. BLOBTest#testUpdateBlobFromScrollableResultSetWithProjectUsingPositionedUpdates()
View license/**
* Tests updating a Blob from a scollable resultset, produced by
* a select query with projection. Updates are made using
* positioned updates
* @exception SQLException causes test to fail with error
* @exception IOException causes test to fail with error
*/
public void testUpdateBlobFromScrollableResultSetWithProjectUsingPositionedUpdates() throws SQLException, IOException {
final Statement stmt = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
final ResultSet rs = stmt.executeQuery("SELECT data from " + BLOBDataModelSetup.getBlobTableName() + " WHERE val= " + BLOBDataModelSetup.bigVal);
println("Last");
rs.last();
final int newVal = BLOBDataModelSetup.bigVal * 2;
final int newSize = BLOBDataModelSetup.bigSize / 2;
testUpdateBlobWithPositionedUpdate(rs, newVal, newSize);
// Necessary after a positioned update
rs.relative(0);
println("Verify updated blob using result set");
verifyBlob(newVal, newSize, rs.getBlob("DATA"));
rs.close();
stmt.close();
}88. UpdateXXXTest#testUpdateString()
View license/**
* Tests calling updateString on all columns of the row.
* @exception SQLException database access error. Causes test to
* fail with an error.
*/
public void testUpdateString() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery(SELECT_STMT);
rs.next();
for (int i = 1; i <= COLUMNS; i++) {
rs.updateString(i, "2");
assertEquals("Expected rs.getDouble(" + i + ") to match updated value", 2, (int) rs.getDouble(i));
}
rs.updateRow();
rs.close();
checkColumnsAreUpdated();
s.close();
}89. UpdateXXXTest#testUpdateInt()
View license/**
* Tests calling updateInt on all columns of the row.
* @exception SQLException database access error. Causes test to
* fail with an error.
*/
public void testUpdateInt() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery(SELECT_STMT);
rs.next();
for (int i = 1; i <= COLUMNS; i++) {
rs.updateInt(i, 2);
assertEquals("Expected rs.getInt(" + i + ") to match updated value", 2, rs.getInt(i));
}
rs.updateRow();
rs.close();
checkColumnsAreUpdated();
s.close();
}90. UpdateXXXTest#testUpdateLong()
View license/**
* Tests calling updateLong on all columns of the row.
* @exception SQLException database access error. Causes test to
* fail with an error.
*/
public void testUpdateLong() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery(SELECT_STMT);
rs.next();
for (int i = 1; i <= COLUMNS; i++) {
rs.updateLong(i, 2L);
assertEquals("Expected rs.getLong(" + i + ") to match updated value", 2L, rs.getLong(i));
}
rs.updateRow();
rs.close();
checkColumnsAreUpdated();
s.close();
}91. UpdateXXXTest#testUpdateShort()
View license/**
* Tests calling updateShort on all columns of the row.
* @exception SQLException database access error. Causes test to
* fail with an error.
*/
public void testUpdateShort() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery(SELECT_STMT);
rs.next();
for (int i = 1; i <= COLUMNS; i++) {
rs.updateShort(i, (short) 2);
assertEquals("Expected rs.getShort(" + i + ") to match updated value", 2, (int) rs.getShort(i));
}
rs.updateRow();
rs.close();
checkColumnsAreUpdated();
s.close();
}92. UpdateXXXTest#testUpdateFloat()
View license/**
* Tests calling updateFloat on all columns of the row.
* @exception SQLException database access error. Causes test to
* fail with an error.
*/
public void testUpdateFloat() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery(SELECT_STMT);
rs.next();
for (int i = 1; i <= COLUMNS; i++) {
rs.updateFloat(i, 2.0f);
assertEquals("Expected rs.getFloat(" + i + ") to match updated value", 2, (int) rs.getFloat(i));
}
rs.updateRow();
rs.close();
checkColumnsAreUpdated();
s.close();
}93. UpdateXXXTest#testUpdateDouble()
View license/**
* Tests calling updateDouble on all columns of the row.
* @exception SQLException database access error. Causes test to
* fail with an error.
*/
public void testUpdateDouble() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery(SELECT_STMT);
rs.next();
for (int i = 1; i <= COLUMNS; i++) {
rs.updateDouble(i, 2.0);
assertEquals("Expected rs.getDouble(" + i + ") to match updated value", 2, (int) rs.getDouble(i));
}
rs.updateRow();
rs.close();
checkColumnsAreUpdated();
s.close();
}94. TestMultiOrder#finalTest()
View license@Override
void finalTest() throws SQLException {
conn = base.getConnection();
ResultSet rs = conn.createStatement().executeQuery("select count(*) from customer");
rs.next();
base.assertEquals(customerCount, rs.getInt(1));
// System.out.println("customers: " + rs.getInt(1));
rs = conn.createStatement().executeQuery("select count(*) from orders");
rs.next();
base.assertEquals(orderCount, rs.getInt(1));
// System.out.println("orders: " + rs.getInt(1));
rs = conn.createStatement().executeQuery("select count(*) from orderLine");
rs.next();
base.assertEquals(orderLineCount, rs.getInt(1));
// System.out.println("orderLines: " + rs.getInt(1));
conn.close();
}95. TestOptimizations#testSortIndex()
View licenseprivate void testSortIndex() throws SQLException {
Connection conn = getConnection("optimizations");
Statement stat = conn.createStatement();
stat.execute("drop table test if exists");
stat.execute("create table test(id int)");
stat.execute("create index idx_id_desc on test(id desc)");
stat.execute("create index idx_id_asc on test(id)");
ResultSet rs;
rs = stat.executeQuery("explain select * from test " + "where id > 10 order by id");
rs.next();
assertTrue(rs.getString(1).contains("IDX_ID_ASC"));
rs = stat.executeQuery("explain select * from test " + "where id < 10 order by id desc");
rs.next();
assertTrue(rs.getString(1).contains("IDX_ID_DESC"));
rs.next();
stat.execute("drop table test");
conn.close();
}96. TestRunscript#testScriptExcludeSchema()
View licenseprivate void testScriptExcludeSchema() throws Exception {
deleteDb("runscript");
Connection conn;
ResultSet rs;
conn = getConnection("runscript");
Statement stat = conn.createStatement();
stat.execute("create schema include_schema1");
stat.execute("create schema exclude_schema1");
stat.execute("script schema include_schema1");
rs = stat.getResultSet();
while (rs.next()) {
assertTrue("The schema 'exclude_schema1' should not be present in the script", rs.getString(1).indexOf("exclude_schema1".toUpperCase()) == -1);
}
rs.close();
stat.execute("create schema include_schema2");
stat.execute("script nosettings schema include_schema1, include_schema2");
rs = stat.getResultSet();
// user and one row per schema = 3
assertResultRowCount(3, rs);
rs.close();
conn.close();
}97. TestMetaData#testSessionsUncommitted()
View licenseprivate void testSessionsUncommitted() throws SQLException {
if (config.mvcc || config.memory) {
return;
}
Connection conn = getConnection("metaData");
conn.setAutoCommit(false);
Statement stat = conn.createStatement();
stat.execute("create table test(id int)");
stat.execute("begin transaction");
for (int i = 0; i < 6; i++) {
stat.execute("insert into test values (1)");
}
ResultSet rs = stat.executeQuery("select contains_uncommitted " + "from INFORMATION_SCHEMA.SESSIONS");
rs.next();
assertEquals(true, rs.getBoolean(1));
rs.close();
stat.execute("commit");
rs = stat.executeQuery("select contains_uncommitted " + "from INFORMATION_SCHEMA.SESSIONS");
rs.next();
assertEquals(false, rs.getBoolean(1));
conn.close();
deleteDb("metaData");
}98. UpdateXXXTest#testUpdateObjectWithNull()
View license/**
* Tests calling updateObject with a null value on all columns.
* @exception SQLException database access error. Causes test to
* fail with an error.
*/
public void testUpdateObjectWithNull() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery(SELECT_STMT);
rs.next();
Object value = null;
for (int i = 1; i <= COLUMNS; i++) {
rs.updateObject(i, value);
assertNull("Expected rs.getObject(" + i + ") to be null", rs.getObject(i));
assertTrue("Expected rs.wasNull() to return true", rs.wasNull());
}
rs.updateRow();
rs.close();
checkColumnsAreNull();
s.close();
}99. ConnectionTest#setPragmasFromURI()
View license@Test
public void setPragmasFromURI() throws Exception {
File testDB = copyToTemp("sample.db");
assertTrue(testDB.exists());
Connection conn = DriverManager.getConnection(String.format("jdbc:sqlite:%s?journal_mode=WAL&synchronous=OFF&journal_size_limit=500", testDB));
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("pragma journal_mode");
assertEquals("wal", rs.getString(1));
rs.close();
rs = stat.executeQuery("pragma synchronous");
assertEquals(false, rs.getBoolean(1));
rs.close();
rs = stat.executeQuery("pragma journal_size_limit");
assertEquals(500, rs.getInt(1));
rs.close();
stat.close();
conn.close();
}100. UpdateXXXTest#jdbc2testUpdateBigDecimal()
View license/**
* Tests calling update on all columns of the row.
* @exception SQLException database access error. Causes test to
* fail with an error.
*/
public void jdbc2testUpdateBigDecimal() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery(SELECT_STMT);
rs.next();
for (int i = 1; i <= COLUMNS; i++) {
rs.updateBigDecimal(i, BigDecimal.valueOf(2L));
assertEquals("Expected rs.getBigDecimal(" + i + ") to match updated value", 2, rs.getBigDecimal(i).intValue());
}
rs.updateRow();
rs.close();
checkColumnsAreUpdated();
s.close();
}