public void testQueryForObjectWithIntegerAndNull() throws Exception {
String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3";
mockResultSetMetaData.getColumnCount();
ctrlResultSetMetaData.setReturnValue(1);
mockResultSet.getMetaData();
ctrlResultSet.setReturnValue(mockResultSetMetaData);
mockResultSet.next();
ctrlResultSet.setReturnValue(true);
mockResultSet.getInt(1);
ctrlResultSet.setReturnValue(0);
mockResultSet.wasNull();
ctrlResultSet.setReturnValue(true);
mockResultSet.next();
ctrlResultSet.setReturnValue(false);
mockResultSet.close();
ctrlResultSet.setVoidCallable();
mockStatement.executeQuery(sql);
ctrlStatement.setReturnValue(mockResultSet);
mockStatement.getWarnings();
ctrlStatement.setReturnValue(null);
mockStatement.close();
ctrlStatement.setVoidCallable();
mockConnection.createStatement();
ctrlConnection.setReturnValue(mockStatement);
replay();
JdbcTemplate template = new JdbcTemplate(mockDataSource);
assertNull(template.queryForObject(sql, Integer.class));
}
public static ResultSet executeSql(final Statement statement, final String sql) {
ResultSet results = null;
if (statement == null) {
return results;
}
if (isBlank(sql)) {
LOGGER.log(Level.FINE, "No SQL provided", new RuntimeException());
return results;
}
try {
statement.clearWarnings();
final boolean hasResults = statement.execute(sql);
if (hasResults) {
results = statement.getResultSet();
} else {
final int updateCount = statement.getUpdateCount();
LOGGER.log(
Level.FINE,
String.format("No results. Update count of %d for query: %s", updateCount, sql));
}
SQLWarning sqlWarning = statement.getWarnings();
while (sqlWarning != null) {
LOGGER.log(Level.INFO, sqlWarning.getMessage(), sqlWarning);
sqlWarning = sqlWarning.getNextWarning();
}
return results;
} catch (final SQLException e) {
LOGGER.log(Level.WARNING, "Error executing: " + sql, e);
return null;
}
}
private void execute(
boolean script, boolean export, Writer fileOutput, Statement statement, final String sql)
throws IOException, SQLException {
final SqlExceptionHelper sqlExceptionHelper = new SqlExceptionHelper();
String formatted = formatter.format(sql);
if (delimiter != null) formatted += delimiter;
if (script) System.out.println(formatted);
LOG.debug(formatted);
if (outputFile != null) {
fileOutput.write(formatted + "\n");
}
if (export) {
statement.executeUpdate(sql);
try {
SQLWarning warnings = statement.getWarnings();
if (warnings != null) {
sqlExceptionHelper.logAndClearWarnings(connectionHelper.getConnection());
}
} catch (SQLException sqle) {
LOG.unableToLogSqlWarnings(sqle);
}
}
}
public void testQueryForLong() throws Exception {
String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3";
mockResultSetMetaData.getColumnCount();
ctrlResultSetMetaData.setReturnValue(1);
mockResultSet.getMetaData();
ctrlResultSet.setReturnValue(mockResultSetMetaData);
mockResultSet.next();
ctrlResultSet.setReturnValue(true);
mockResultSet.getLong(1);
ctrlResultSet.setReturnValue(87);
mockResultSet.wasNull();
ctrlResultSet.setReturnValue(false);
mockResultSet.next();
ctrlResultSet.setReturnValue(false);
mockResultSet.close();
ctrlResultSet.setVoidCallable();
mockStatement.executeQuery(sql);
ctrlStatement.setReturnValue(mockResultSet);
mockStatement.getWarnings();
ctrlStatement.setReturnValue(null);
mockStatement.close();
ctrlStatement.setVoidCallable();
mockConnection.createStatement();
ctrlConnection.setReturnValue(mockStatement);
replay();
JdbcTemplate template = new JdbcTemplate(mockDataSource);
long l = template.queryForLong(sql);
assertEquals("Return of a long", 87, l);
}
public void testDecimal() {
String ddl = "create table test (cash decimal default 23.587);";
String dml = "insert into test values (123.45678911111);";
String query = "select * from test;";
Connection dbconn;
try {
Class.forName("org.hsqldb_voltpatches.jdbcDriver");
dbconn = DriverManager.getConnection("jdbc:hsqldb:mem:x1", "sa", "");
dbconn.setAutoCommit(true);
dbconn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
Statement stmt = dbconn.createStatement();
stmt.execute(ddl);
SQLWarning warn = stmt.getWarnings();
if (warn != null) System.out.println("warn: " + warn.getMessage());
assertTrue(warn == null);
long ucount = stmt.executeUpdate(dml);
assertTrue(ucount == 1);
ResultSet rs = stmt.executeQuery(query);
assertTrue(rs != null);
ResultSetMetaData rsmd = rs.getMetaData();
assertTrue(rsmd != null);
assertTrue(rsmd.getColumnCount() == 1);
/*System.out.printf("Typename %s, Type %d, Precision %s, Scale %d, Classname %s\n",
rsmd.getColumnTypeName(1),
rsmd.getColumnType(1),
rsmd.getPrecision(1),
rsmd.getScale(1),
rsmd.getColumnClassName(1));*/
boolean success = rs.next();
assertTrue(success);
BigDecimal x = rs.getBigDecimal(1);
assertNotNull(x);
// System.out.printf("Value: %.10f\n", x.doubleValue());
BigDecimal expected = new BigDecimal(123.4567);
assertTrue(x.subtract(expected).abs().doubleValue() < .01);
try {
stmt.execute("SHUTDOWN;");
} catch (Exception e) {
}
;
dbconn.close();
System.gc();
} catch (Exception e) {
e.printStackTrace();
assertTrue(false);
}
}
/**
* Utility method for executing DML
*
* @param conn The DML Connection object that will execute the SQL
* @param qry The DML statements to be executed
*/
private static Statement executeQueryWithResults(Connection conn, String qry)
throws SQLException {
Statement stmnt = conn.createStatement();
stmnt.executeQuery(qry);
SQLWarning warn = stmnt.getWarnings();
if (warn != null) {
LOGGER.warn(warn);
}
return stmnt;
}
/**
* Utility method for executing DML
*
* @param conn The DML Connection object that will execute the SQL
* @param qry The DML statements to be executed
*/
private static void executeQuery(Connection conn, String qry) throws SQLException {
try (Statement stmnt = conn.createStatement()) {
stmnt.execute(qry);
SQLWarning warn = stmnt.getWarnings();
if (warn != null) {
LOGGER.warn(warn);
}
stmnt.close();
}
}
@Override
public void emptyCache() throws Exception {
// DY: performs "CHECKPOINT" then "DBCC DROPCLEANBUFFERS" to clean buffers.
++numberOfCacheEmptying;
Timer t = new Timer();
Statement stmt = queryConnection.createStatement();
String createCheckpoint = "CHECKPOINT";
String dropCleanBuffers = "DBCC DROPCLEANBUFFERS";
String dropPlanCache = "DBCC FREEPROCCACHE";
stmt.execute(createCheckpoint);
SQLWarning warning = stmt.getWarnings();
while (warning != null) {
// System.out.println(warning.getMessage());
warning = warning.getNextWarning();
}
String msg = "";
stmt.execute(dropPlanCache);
warning = stmt.getWarnings();
while (warning != null) {
msg += warning.getMessage();
warning = warning.getNextWarning();
}
if (!msg.contains("DBCC execution completed.")) {
log.error("Failed to clear db cache.");
}
msg = "";
stmt.execute(dropCleanBuffers);
warning = stmt.getWarnings();
while (warning != null) {
msg += warning.getMessage();
warning = warning.getNextWarning();
}
if (!msg.contains("DBCC execution completed.")) {
log.error("Failed to clear db cache.");
}
// for now, we just assume the commands ran successfully. (retry will be added after unit
// testing is done)
log.status(LogLevel.DEBUG, "Successfully emptied the cache.");
secondsSpentEmptyingCache += t.lapSeconds();
}
protected boolean isClosed(Statement statement) {
try {
statement.getWarnings();
return false;
} catch (SQLException e) {
// getWarnings throws an exception if the statement is
// closed, but could throw an exception for other reasons
// in this case it is good enought to assume the statement
// is closed
return true;
}
}
public void runDDL(String ddl) {
try {
// LOG.info("Executing " + ddl);
Statement stmt = dbconn.createStatement();
/*boolean success =*/ stmt.execute(ddl);
SQLWarning warn = stmt.getWarnings();
if (warn != null) sqlLog.warn(warn.getMessage());
// LOG.info("SQL DDL execute result: " + (success ? "true" : "false"));
} catch (SQLException e) {
hostLog.l7dlog(Level.ERROR, LogKeys.host_Backend_RunDDLFailed.name(), new Object[] {ddl}, e);
}
}
@Override
public void export(String string) throws Exception {
statement.executeUpdate(string);
try {
SQLWarning warnings = statement.getWarnings();
if (warnings != null) {
sqlExceptionHelper.logAndClearWarnings(connection);
}
} catch (SQLException e) {
LOG.unableToLogSqlWarnings(e);
}
}
public void testVarbinary() {
String ddl = "create table testvb (cash integer default 23, b varbinary(1024) default NULL);";
String dml = "insert into testvb values (123, 'AAAA');";
String query = "select * from testvb;";
Connection dbconn;
try {
Class.forName("org.hsqldb_voltpatches.jdbcDriver");
dbconn = DriverManager.getConnection("jdbc:hsqldb:mem:x1", "sa", "");
dbconn.setAutoCommit(true);
dbconn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
Statement stmt = dbconn.createStatement();
stmt.execute(ddl);
SQLWarning warn = stmt.getWarnings();
if (warn != null) System.out.println("warn: " + warn.getMessage());
assertTrue(warn == null);
long ucount = stmt.executeUpdate(dml);
assertTrue(ucount == 1);
ResultSet rs = stmt.executeQuery(query);
assertTrue(rs != null);
ResultSetMetaData rsmd = rs.getMetaData();
assertTrue(rsmd != null);
assertTrue(rsmd.getColumnCount() == 2);
boolean success = rs.next();
assertTrue(success);
int x = rs.getInt(1);
assertTrue(x == 123);
try {
stmt.execute("SHUTDOWN;");
} catch (Exception e) {
}
;
dbconn.close();
System.gc();
} catch (Exception e) {
e.printStackTrace();
assertTrue(false);
}
}
public void testValid() throws Exception {
String sql = "SELECT NAME AS NAME, PROPERTY AS PROPERTY, VALUE AS VALUE FROM T";
MockControl ctrlResultSet = MockControl.createControl(ResultSet.class);
ResultSet mockResultSet = (ResultSet) ctrlResultSet.getMock();
ctrlResultSet.expectAndReturn(mockResultSet.next(), true, 2);
ctrlResultSet.expectAndReturn(mockResultSet.next(), false);
// first row
ctrlResultSet.expectAndReturn(mockResultSet.getString(1), "one");
ctrlResultSet.expectAndReturn(mockResultSet.getString(2), "(class)");
ctrlResultSet.expectAndReturn(mockResultSet.getString(3), "org.springframework.beans.TestBean");
// second row
ctrlResultSet.expectAndReturn(mockResultSet.getString(1), "one");
ctrlResultSet.expectAndReturn(mockResultSet.getString(2), "age");
ctrlResultSet.expectAndReturn(mockResultSet.getString(3), "53");
mockResultSet.close();
ctrlResultSet.setVoidCallable();
MockControl ctrlStatement = MockControl.createControl(Statement.class);
Statement mockStatement = (Statement) ctrlStatement.getMock();
ctrlStatement.expectAndReturn(mockStatement.executeQuery(sql), mockResultSet);
if (debugEnabled) {
ctrlStatement.expectAndReturn(mockStatement.getWarnings(), null);
}
mockStatement.close();
ctrlStatement.setVoidCallable();
mockConnection.createStatement();
ctrlConnection.setReturnValue(mockStatement);
ctrlResultSet.replay();
ctrlStatement.replay();
replay();
DefaultListableBeanFactory bf = new DefaultListableBeanFactory();
JdbcBeanDefinitionReader reader = new JdbcBeanDefinitionReader(bf);
reader.setDataSource(mockDataSource);
reader.loadBeanDefinitions(sql);
assertEquals("Incorrect number of bean definitions", 1, bf.getBeanDefinitionCount());
TestBean tb = (TestBean) bf.getBean("one");
assertEquals("Age in TestBean was wrong.", 53, tb.getAge());
ctrlResultSet.verify();
ctrlStatement.verify();
}
public static void ShowWarnings(PrintStream out, Statement s) {
try {
// GET STATEMENT WARNINGS
SQLWarning warning = null;
if (s != null) {
ShowWarnings(out, s.getWarnings());
}
if (s != null) {
s.clearWarnings();
}
} catch (SQLException e) {
ShowSQLException(out, e);
}
} // ShowStatementWarnings
public String checkWarning(Statement st, int updateCount) {
String result;
result = null;
try {
SQLWarning warning = st.getWarnings();
if (warning != null) {
result = warning.getMessage();
}
} catch (Exception e) {
result = processSQLError(e);
}
if (updateCount != 1) {
result = "Tuple not inserted correctly.";
}
return result;
}
public void testQueryForObjectThrowsIncorrectResultSizeForMoreThanOneRow() throws Exception {
String sql = "select pass from t_account where first_name='Alef'";
mockResultSetMetaData.getColumnCount();
ctrlResultSetMetaData.setReturnValue(1);
mockResultSet.getMetaData();
ctrlResultSet.setReturnValue(mockResultSetMetaData);
mockResultSet.next();
ctrlResultSet.setReturnValue(true);
mockResultSet.getString(1);
ctrlResultSet.setReturnValue("pass");
mockResultSet.next();
ctrlResultSet.setReturnValue(true);
mockResultSet.getMetaData();
ctrlResultSet.setReturnValue(mockResultSetMetaData);
mockResultSetMetaData.getColumnCount();
ctrlResultSetMetaData.setReturnValue(1);
mockResultSet.getString(1);
ctrlResultSet.setReturnValue("pass");
mockResultSet.next();
ctrlResultSet.setReturnValue(false);
mockResultSet.close();
ctrlResultSet.setVoidCallable();
mockStatement.executeQuery(sql);
ctrlStatement.setReturnValue(mockResultSet);
mockStatement.getWarnings();
ctrlStatement.setReturnValue(null);
mockStatement.close();
ctrlStatement.setVoidCallable();
mockConnection.createStatement();
ctrlConnection.setReturnValue(mockStatement);
replay();
JdbcTemplate template = new JdbcTemplate(mockDataSource);
try {
template.queryForObject(sql, String.class);
fail("Should have thrown IncorrectResultSizeDataAccessException");
} catch (IncorrectResultSizeDataAccessException ex) {
// expected
}
}
public void testQueryForList() throws Exception {
String sql = "SELECT AGE FROM CUSTMR WHERE ID < 3";
mockResultSetMetaData.getColumnCount();
ctrlResultSetMetaData.setReturnValue(1, 2);
mockResultSetMetaData.getColumnLabel(1);
ctrlResultSetMetaData.setReturnValue("age", 2);
mockResultSet.getMetaData();
ctrlResultSet.setReturnValue(mockResultSetMetaData, 2);
mockResultSet.next();
ctrlResultSet.setReturnValue(true);
mockResultSet.getObject(1);
ctrlResultSet.setReturnValue(new Integer(11));
mockResultSet.next();
ctrlResultSet.setReturnValue(true);
mockResultSet.getObject(1);
ctrlResultSet.setReturnValue(new Integer(12));
mockResultSet.next();
ctrlResultSet.setReturnValue(false);
mockResultSet.close();
ctrlResultSet.setVoidCallable();
mockStatement.executeQuery(sql);
ctrlStatement.setReturnValue(mockResultSet);
mockStatement.getWarnings();
ctrlStatement.setReturnValue(null);
mockStatement.close();
ctrlStatement.setVoidCallable();
mockConnection.createStatement();
ctrlConnection.setReturnValue(mockStatement);
replay();
JdbcTemplate template = new JdbcTemplate(mockDataSource);
List li = template.queryForList(sql);
assertEquals("All rows returned", 2, li.size());
assertEquals("First row is Integer", 11, ((Integer) ((Map) li.get(0)).get("age")).intValue());
assertEquals("Second row is Integer", 12, ((Integer) ((Map) li.get(1)).get("age")).intValue());
}
public void testWarning() throws Exception {
Connection con = TestUtil.openDB();
Statement stmt = con.createStatement();
stmt.execute("CREATE TEMP TABLE t(a int primary key)");
SQLWarning warning = stmt.getWarnings();
// We should get a warning about primary key index creation
// it's possible we won't depending on the server's
// client_min_messages setting.
while (warning != null) {
// Verify that the SQLWarning is serializable.
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ObjectOutputStream oos = new ObjectOutputStream(baos);
oos.writeObject(warning);
oos.close();
warning = warning.getNextWarning();
}
stmt.close();
con.close();
}
public void testQueryForListWithIntegerElement() throws Exception {
String sql = "SELECT AGE FROM CUSTMR WHERE ID < 3";
mockResultSetMetaData.getColumnCount();
ctrlResultSetMetaData.setReturnValue(1);
mockResultSet.getMetaData();
ctrlResultSet.setReturnValue(mockResultSetMetaData);
mockResultSet.next();
ctrlResultSet.setReturnValue(true);
mockResultSet.getInt(1);
ctrlResultSet.setReturnValue(11);
mockResultSet.wasNull();
ctrlResultSet.setReturnValue(false);
mockResultSet.next();
ctrlResultSet.setReturnValue(false);
mockResultSet.close();
ctrlResultSet.setVoidCallable();
mockStatement.executeQuery(sql);
ctrlStatement.setReturnValue(mockResultSet);
mockStatement.getWarnings();
ctrlStatement.setReturnValue(null);
mockStatement.close();
ctrlStatement.setVoidCallable();
mockConnection.createStatement();
ctrlConnection.setReturnValue(mockStatement);
replay();
JdbcTemplate template = new JdbcTemplate(mockDataSource);
List li = template.queryForList(sql, Integer.class);
assertEquals("All rows returned", 1, li.size());
assertEquals("Element is Integer", 11, ((Integer) li.get(0)).intValue());
}
public void testQueryForObjectWithRowMapper() throws Exception {
String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3";
mockResultSet.next();
ctrlResultSet.setReturnValue(true);
mockResultSet.getInt(1);
ctrlResultSet.setReturnValue(22);
mockResultSet.next();
ctrlResultSet.setReturnValue(false);
mockResultSet.close();
ctrlResultSet.setVoidCallable();
mockStatement.executeQuery(sql);
ctrlStatement.setReturnValue(mockResultSet);
mockStatement.getWarnings();
ctrlStatement.setReturnValue(null);
mockStatement.close();
ctrlStatement.setVoidCallable();
mockConnection.createStatement();
ctrlConnection.setReturnValue(mockStatement);
replay();
JdbcTemplate template = new JdbcTemplate(mockDataSource);
Object o =
template.queryForObject(
sql,
new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
return new Integer(rs.getInt(1));
}
});
assertTrue("Correct result type", o instanceof Integer);
}
public void testQueryForMapWithSingleRowAndColumn() throws Exception {
String sql = "SELECT AGE FROM CUSTMR WHERE ID < 3";
mockResultSetMetaData.getColumnCount();
ctrlResultSetMetaData.setReturnValue(1);
mockResultSetMetaData.getColumnLabel(1);
ctrlResultSetMetaData.setReturnValue("age", 1);
mockResultSet.getMetaData();
ctrlResultSet.setReturnValue(mockResultSetMetaData);
mockResultSet.next();
ctrlResultSet.setReturnValue(true);
mockResultSet.getObject(1);
ctrlResultSet.setReturnValue(new Integer(11));
mockResultSet.next();
ctrlResultSet.setReturnValue(false);
mockResultSet.close();
ctrlResultSet.setVoidCallable();
mockStatement.executeQuery(sql);
ctrlStatement.setReturnValue(mockResultSet);
mockStatement.getWarnings();
ctrlStatement.setReturnValue(null);
mockStatement.close();
ctrlStatement.setVoidCallable();
mockConnection.createStatement();
ctrlConnection.setReturnValue(mockStatement);
replay();
JdbcTemplate template = new JdbcTemplate(mockDataSource);
Map map = template.queryForMap(sql);
assertEquals("Wow is Integer", 11, ((Integer) map.get("age")).intValue());
}
public void testQueryForListWithEmptyResult() throws Exception {
String sql = "SELECT AGE FROM CUSTMR WHERE ID < 3";
mockResultSet.next();
ctrlResultSet.setReturnValue(false);
mockResultSet.close();
ctrlResultSet.setVoidCallable();
mockStatement.executeQuery(sql);
ctrlStatement.setReturnValue(mockResultSet);
mockStatement.getWarnings();
ctrlStatement.setReturnValue(null);
mockStatement.close();
ctrlStatement.setVoidCallable();
mockConnection.createStatement();
ctrlConnection.setReturnValue(mockStatement);
replay();
JdbcTemplate template = new JdbcTemplate(mockDataSource);
List li = template.queryForList(sql);
assertEquals("All rows returned", 0, li.size());
}
protected SQLWarning getWarnings(Statement statement) throws SQLException {
return statement.getWarnings();
}
protected void setUp() throws SQLException {
conControl = MockControl.createControl(Connection.class);
con = (Connection) conControl.getMock();
con.isClosed();
conControl.setDefaultReturnValue(false);
rsmdControl = MockControl.createControl(ResultSetMetaData.class);
rsmd = (ResultSetMetaData) rsmdControl.getMock();
rsmd.getColumnCount();
rsmdControl.setReturnValue(4, 1);
rsmd.getColumnLabel(1);
rsmdControl.setReturnValue("name", 1);
rsmd.getColumnType(1);
rsmdControl.setReturnValue(Types.VARCHAR, 1);
rsmd.getColumnLabel(2);
rsmdControl.setReturnValue("age", 1);
rsmd.getColumnType(2);
rsmdControl.setReturnValue(Types.NUMERIC, 1);
rsmd.getColumnLabel(3);
rsmdControl.setReturnValue("birth_date", 1);
rsmd.getColumnType(3);
rsmdControl.setReturnValue(Types.TIMESTAMP, 1);
rsmd.getColumnLabel(4);
rsmdControl.setReturnValue("balance", 1);
rsmd.getColumnType(4);
rsmdControl.setReturnValue(Types.DECIMAL, 1);
rsmdControl.replay();
rsControl = MockControl.createControl(ResultSet.class);
rs = (ResultSet) rsControl.getMock();
rs.getMetaData();
rsControl.setReturnValue(rsmd, 1);
rs.next();
rsControl.setReturnValue(true, 1);
rs.getString("name");
rsControl.setReturnValue("Bubba", 1);
rs.getLong("age");
rsControl.setReturnValue(22, 1);
rs.findColumn("birth_date");
rsControl.setReturnValue(3, 1);
rs.getObject(3);
rsControl.setReturnValue(new java.sql.Timestamp(1221222L), 1);
rs.getBigDecimal("balance");
rsControl.setReturnValue(new BigDecimal("1234.56"), 1);
rs.next();
rsControl.setReturnValue(false, 1);
rs.close();
rsControl.setVoidCallable(1);
rsControl.replay();
stmtControl = MockControl.createControl(Statement.class);
stmt = (Statement) stmtControl.getMock();
con.createStatement();
conControl.setReturnValue(stmt, 1);
stmt.executeQuery("select name, age, birth_date, balance from people");
stmtControl.setReturnValue(rs, 1);
stmt.getWarnings();
stmtControl.setReturnValue(null, 1);
stmt.close();
stmtControl.setVoidCallable(1);
conControl.replay();
stmtControl.replay();
jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(new SingleConnectionDataSource(con, false));
jdbcTemplate.setExceptionTranslator(new SQLStateSQLExceptionTranslator());
jdbcTemplate.afterPropertiesSet();
}
@Override
public SQLWarning getWarnings() throws SQLException {
return rawStatement.getWarnings();
}
/**
* General test of scrollable cursor functionality.
*
* <p>When running on SQL Server this test will exercise MSCursorResultSet. When running on Sybase
* this test will exercise CachedResultSet.
*/
public void testCachedCursor() throws Exception {
try {
dropTable("jTDS_CachedCursorTest");
Statement stmt = con.createStatement();
stmt.execute(
"CREATE TABLE jTDS_CachedCursorTest "
+ "(key1 int NOT NULL, key2 char(4) NOT NULL,"
+ "data varchar(255))\r\n"
+ "ALTER TABLE jTDS_CachedCursorTest "
+ "ADD CONSTRAINT PK_jTDS_CachedCursorTest PRIMARY KEY CLUSTERED"
+ "( key1, key2)");
for (int i = 1; i <= 16; i++) {
assertEquals(
1,
stmt.executeUpdate(
"INSERT INTO jTDS_CachedCursorTest VALUES(" + i + ", 'XXXX','LINE " + i + "')"));
}
stmt.close();
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM jTDS_CachedCursorTest ORDER BY key1");
assertNotNull(rs);
assertEquals(null, stmt.getWarnings());
assertTrue(rs.isBeforeFirst());
assertTrue(rs.first());
assertEquals(1, rs.getInt(1));
assertTrue(rs.isFirst());
assertTrue(rs.last());
assertEquals(16, rs.getInt(1));
assertTrue(rs.isLast());
assertFalse(rs.next());
assertTrue(rs.isAfterLast());
rs.beforeFirst();
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
rs.afterLast();
assertTrue(rs.previous());
assertEquals(16, rs.getInt(1));
assertTrue(rs.absolute(8));
assertEquals(8, rs.getInt(1));
assertTrue(rs.relative(-1));
assertEquals(7, rs.getInt(1));
rs.updateString(3, "New line 7");
rs.updateRow();
// assertTrue(rs.rowUpdated()); // MS API cursors appear not to support this
rs.moveToInsertRow();
rs.updateInt(1, 17);
rs.updateString(2, "XXXX");
rs.updateString(3, "LINE 17");
rs.insertRow();
rs.moveToCurrentRow();
rs.last();
// assertTrue(rs.rowInserted()); // MS API cursors appear not to support this
Statement stmt2 =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs2 = stmt2.executeQuery("SELECT * FROM jTDS_CachedCursorTest ORDER BY key1");
rs.updateString(3, "NEW LINE 17");
rs.updateRow();
assertTrue(rs2.last());
assertEquals(17, rs2.getInt(1));
assertEquals("NEW LINE 17", rs2.getString(3));
rs.deleteRow();
rs2.refreshRow();
assertTrue(rs2.rowDeleted());
rs2.close();
stmt2.close();
rs.close();
stmt.close();
} finally {
dropTable("jTDS_CachedCursorTest");
}
}
/**
* This method assumes that there the SQL will either return no ResultSet or one ResultSet (not
* multiple ResultSets).
*
* @see org.teiid.designer.jdbc.Request#performInvocation(java.sql.Connection,
* org.teiid.designer.jdbc.Response)
*/
@Override
protected IStatus performInvocation(final Response results) {
final Connection conn = (Connection) getTarget();
final List statuses = new ArrayList();
Statement statement = null;
ResultSet resultSet = null;
try {
statement = conn.createStatement();
final boolean isResultSet = statement.execute(this.sql);
// If the statement resulted in a ResultSet ...
if (isResultSet) {
resultSet = statement.getResultSet();
Response.addResults(results, resultSet, this.isMetadataRequested());
} else {
// Otherwise, just add in the update count
try {
final int updateCount = statement.getUpdateCount();
final Object value = new Integer(updateCount);
Response.addResults(results, value, true);
} catch (SQLException e) {
statuses.add(JdbcUtil.createIStatus(e));
}
}
// Add any errors or warnings ...
SQLWarning warning = statement.getWarnings();
// Unchain the warnings ...
while (warning != null) {
statuses.add(JdbcUtil.createIStatus(warning));
warning = warning.getNextWarning();
}
} catch (SQLException e) {
statuses.add(JdbcUtil.createIStatus(e));
} catch (Throwable e) {
statuses.add(JdbcUtil.createIStatus(e));
} finally {
if (resultSet != null) {
// attempt to close the result set ...
try {
resultSet.close();
} catch (SQLException e) {
statuses.add(JdbcUtil.createIStatus(e));
} finally {
resultSet = null;
}
}
if (statement != null) {
// attempt to close the statement ...
try {
statement.close();
} catch (SQLException e) {
statuses.add(JdbcUtil.createIStatus(e));
} finally {
statement = null;
}
}
}
// Process the status(es) that may have been created due to problems/warnings
if (statuses.size() == 1) {
return (IStatus) statuses.get(0);
}
if (statuses.size() > 1) {
final String text =
JdbcPlugin.Util.getString(
"QueryRequest.Error_while_processing_sql_against_connection",
sql,
conn); //$NON-NLS-1$
return JdbcUtil.createIStatus(statuses, text);
}
// If there are no errors, return null
return null;
}
public SQLWarning getWarnings() throws SQLException {
return pst.getWarnings();
};
@Override
public SQLWarning getWarnings() throws SQLException {
return stat.getWarnings();
}