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;
}
}
@Test
public void testWarnings() throws Exception {
ConnectionImpl conn = Mockito.mock(ConnectionImpl.class);
DQP dqp = Mockito.mock(DQP.class);
ResultsFuture<ResultsMessage> results = new ResultsFuture<ResultsMessage>();
Mockito.stub(dqp.executeRequest(Mockito.anyLong(), (RequestMessage) Mockito.anyObject()))
.toReturn(results);
ResultsMessage rm = new ResultsMessage();
rm.setResults(new List<?>[] {Arrays.asList(1)});
rm.setWarnings(Arrays.asList(new Throwable()));
rm.setColumnNames(new String[] {"expr1"});
rm.setDataTypes(new String[] {"string"});
results.getResultsReceiver().receiveResults(rm);
Mockito.stub(conn.getDQP()).toReturn(dqp);
StatementImpl statement =
new StatementImpl(conn, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY) {
@Override
protected java.util.TimeZone getServerTimeZone() throws java.sql.SQLException {
return null;
}
};
statement.execute("select 'a'");
assertNotNull(statement.getResultSet());
SQLWarning warning = statement.getWarnings();
assertNotNull(warning);
assertNull(warning.getNextWarning());
}
/**
* Creates a new database and keeps track of it to delete it when the clean up is invoked.
*
* <p>If the database already exists, a connection to the existing database is returned.
*
* @param dbName the database name
* @param dbAttributes database attributes (i.e. encryption)
* @param user user name
* @param password user password
* @return A connection to the database.
* @throws SQLException if creating or connecting to the database fails
*/
public Connection createDatabase(String dbName, String dbAttributes, String user, String password)
throws SQLException {
String userAttr = "";
if (user != null) {
userAttr = ";user="******";password="******";" + dbAttributes;
}
if (!userAttr.equals("")) {
url += userAttr;
}
if (url.indexOf(ATTR_CREATE) == -1) {
url += ATTR_CREATE;
}
Connection con = getConnection(url);
if (con.getWarnings() != null) {
// See if there are more than one warning.
SQLWarning w = con.getWarnings();
String warnings = w.getMessage();
while ((w = w.getNextWarning()) != null) {
warnings += " || " + w.getMessage();
}
BaseJDBCTestCase.fail("Warning(s) when creating database: " + warnings);
}
// Keep track of the database we just created, so that we can
// delete it.
DATABASES.add(dbName + userAttr);
return con;
}
/**
* @param out the place to write to
* @param warning the SQLWarning
*/
public static void ShowWarnings(PrintWriter out, SQLWarning warning) {
while (warning != null) {
String p1 = mapNull(warning.getSQLState(), LocalizedResource.getMessage("UT_NoSqlst_7"));
String p2 = mapNull(warning.getMessage(), LocalizedResource.getMessage("UT_NoMessa_8"));
out.println(LocalizedResource.getMessage("UT_Warni01", p1, p2));
warning = warning.getNextWarning();
}
}
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);
}
}
public static void ShowWarnings(PrintStream out, SQLWarning warning) {
while (warning != null) {
out.println(
"WARNING "
+ mapNull(warning.getSQLState(), "(no SQLState)")
+ ": "
+ mapNull(warning.getMessage(), "(no message)"));
warning = warning.getNextWarning();
}
}
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);
}
}
public static final void clearWarnings(java.sql.Connection db, DBCommandHandler msghandler) {
try {
java.sql.SQLWarning warn = db.getWarnings();
if (warn != null) {
msghandler.showSQLException(warn);
while ((warn = warn.getNextWarning()) != null) {
msghandler.showSQLException(warn);
}
}
db.clearWarnings();
} catch (java.sql.SQLException sqle) {
msghandler.showSQLException(sqle);
}
}
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);
}
}
// Format and print any warnings from the connection
private static void checkForWarning(SQLWarning warn) throws SQLException {
// If a SQLWarning object was given, display the
// warning messages. Note that there could be
// multiple warnings chained together
if (warn != null) {
System.out.println("*** Warning ***\n");
while (warn != null) {
System.out.println("SQLState: " + warn.getSQLState());
System.out.println("Message: " + warn.getMessage());
System.out.println("Vendor: " + warn.getErrorCode());
System.out.println("");
warn = warn.getNextWarning();
}
}
}
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;
}
/**
* Helper method that derived classes may use to set warnings
*
* @param warning Warning
*/
protected void setWarning(SQLWarning warning) {
LOGGER.warn("SQL Warning was issued", warning);
if (this.warnings == null) {
this.warnings = warning;
} else {
// Chain with existing warnings
warning.setNextWarning(this.warnings);
this.warnings = warning;
}
}
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 connect() {
try {
// Load the database driver
Class.forName("net.sourceforge.jtds.jdbc.Driver");
// Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
// Get a connection to the database
String url = "jdbc:jtds:sqlserver://147.87.98.131:55783";
conn = DriverManager.getConnection(url, "se2012_red", "Thanuc57ch");
// Get a statement from the connection
stmt = conn.createStatement();
// Print all warnings
for (SQLWarning warn = conn.getWarnings(); warn != null; warn = warn.getNextWarning()) {
System.out.println("SQL Warning:");
System.out.println("State : " + warn.getSQLState());
System.out.println("Message: " + warn.getMessage());
System.out.println("Error : " + warn.getErrorCode());
}
} catch (SQLException se) {
System.out.println("SQL Exception:");
// Loop through the SQL Exceptions
while (se != null) {
System.out.println("State : " + se.getSQLState());
System.out.println("Message: " + se.getMessage());
System.out.println("Error : " + se.getErrorCode());
se = se.getNextException();
}
} catch (Exception e) {
System.out.println(e);
}
}
@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 void setWarning(SQLWarning warn) {
if (warnings == null) warnings = warn;
else warnings.setNextWarning(warn);
return;
}
/**
* 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;
}
/**
* Create a new scrollable result set in memory or a named server cursor.
*
* @param sql The SQL SELECT statement.
* @param procName Optional procedure name for cursors based on a stored procedure.
* @param parameters Optional stored procedure parameters.
* @exception java.sql.SQLException
*/
private void cursorCreate(String sql, String procName, ParamInfo[] parameters)
throws SQLException {
//
boolean isSelect = false;
SQLWarning warning = null;
//
// Validate the SQL statement to ensure we have a select.
//
if (resultSetType != ResultSet.TYPE_FORWARD_ONLY
|| concurrency == ResultSet.CONCUR_UPDATABLE
|| cursorName != null) {
//
// We are going to need access to a SELECT statement for
// this to work. Reparse the SQL now and check.
//
ArrayList params = new ArrayList();
String tmp[] =
new SQLParser(sql, params, (ConnectionJDBC2) statement.getConnection()).parse(true);
if (tmp[2].equals("select") && tmp[3] != null && tmp[3].length() > 0) {
// OK We have a select with at least one table.
tableName = tmp[3];
isSelect = true;
} else {
// No good we can't update and we can't declare a cursor
cursorName = null;
if (concurrency == ResultSet.CONCUR_UPDATABLE) {
concurrency = ResultSet.CONCUR_READ_ONLY;
warning =
new SQLWarning(Messages.get("warning.cursordowngraded", "CONCUR_READ_ONLY"), "01000");
}
if (resultSetType == ResultSet.TYPE_SCROLL_SENSITIVE) {
resultSetType = ResultSet.TYPE_SCROLL_INSENSITIVE;
SQLWarning warning2 =
new SQLWarning(
Messages.get("warning.cursordowngraded", "TYPE_SCROLL_INSENSITIVE"), "01000");
if (warning != null) {
warning.setNextWarning(warning2);
} else {
warning = warning2;
}
}
}
}
//
// If a cursor name is specified we try and declare a conventional cursor
//
if (cursorName != null) {
//
// We need to substitute any parameters now as the prepended DECLARE CURSOR
// will throw the parameter positions off.
//
if (parameters != null && parameters.length > 0) {
sql = Support.substituteParameters(sql, parameters, statement.getTds().getTdsVersion());
}
StringBuffer cursorSQL = new StringBuffer(sql.length() + cursorName.length() + 128);
cursorSQL.append("DECLARE ").append(cursorName).append(" CURSOR FOR ").append(sql);
cursorTds.executeSQL(
cursorSQL.toString(),
procName,
parameters,
false,
statement.getQueryTimeout(),
statement.getMaxRows(),
statement.getMaxFieldSize(),
true);
cursorTds.clearResponseQueue();
cursorSQL.setLength(0);
cursorSQL.append("\r\nOPEN ").append(cursorName);
if (fetchSize > 1 && isSybase) {
cursorSQL.append("\r\nSET CURSOR ROWS ").append(fetchSize);
cursorSQL.append(" FOR ").append(cursorName);
}
cursorSQL.append("\r\nFETCH ").append(cursorName);
//
// OK Declare cursor, open it and fetch first (fetchSize) rows.
//
cursorTds.executeSQL(
cursorSQL.toString(),
null,
null,
false,
statement.getQueryTimeout(),
statement.getMaxRows(),
statement.getMaxFieldSize(),
true);
while (!cursorTds.getMoreResults() && !cursorTds.isEndOfResponse()) ;
if (!cursorTds.isResultSet()) {
throw new SQLException(Messages.get("error.statement.noresult"), "24000");
}
columns = cursorTds.getColumns();
columnCount = getColumnCount(columns);
} else {
//
// Open a memory cached scrollable or forward only possibly updateable cursor
//
if (isSelect
&& (concurrency == ResultSet.CONCUR_UPDATABLE
|| resultSetType != ResultSet.TYPE_FORWARD_ONLY)) {
// Need to execute SELECT .. FOR BROWSE to get
// the MetaData we require for updates etc
// OK Should have an SQL select statement
// append " FOR BROWSE" to obtain table names
// NB. We can't use any jTDS temporary stored proc
cursorTds.executeSQL(
sql + " FOR BROWSE",
null,
parameters,
false,
statement.getQueryTimeout(),
statement.getMaxRows(),
statement.getMaxFieldSize(),
true);
while (!cursorTds.getMoreResults() && !cursorTds.isEndOfResponse()) ;
if (!cursorTds.isResultSet()) {
throw new SQLException(Messages.get("error.statement.noresult"), "24000");
}
columns = cursorTds.getColumns();
columnCount = getColumnCount(columns);
rowData = new ArrayList(INITIAL_ROW_COUNT);
//
// Load result set into buffer
//
while (super.next()) {
rowData.add(copyRow(currentRow));
}
rowsInResult = rowData.size();
initialRowCnt = rowsInResult;
pos = POS_BEFORE_FIRST;
//
// If cursor is built over one table and the table has
// key columns then the result set is updateable and / or
// can be used as a scroll sensitive result set.
//
if (!isCursorUpdateable()) {
// No so downgrade
if (concurrency == ResultSet.CONCUR_UPDATABLE) {
concurrency = ResultSet.CONCUR_READ_ONLY;
statement.addWarning(
new SQLWarning(
Messages.get("warning.cursordowngraded", "CONCUR_READ_ONLY"), "01000"));
}
if (resultSetType == ResultSet.TYPE_SCROLL_SENSITIVE) {
resultSetType = ResultSet.TYPE_SCROLL_INSENSITIVE;
statement.addWarning(
new SQLWarning(
Messages.get("warning.cursordowngraded", "TYPE_SCROLL_INSENSITIVE"), "01000"));
}
}
return;
}
//
// Create a read only cursor using direct SQL
//
cursorTds.executeSQL(
sql,
procName,
parameters,
false,
statement.getQueryTimeout(),
statement.getMaxRows(),
statement.getMaxFieldSize(),
true);
while (!cursorTds.getMoreResults() && !cursorTds.isEndOfResponse()) ;
if (!cursorTds.isResultSet()) {
throw new SQLException(Messages.get("error.statement.noresult"), "24000");
}
columns = cursorTds.getColumns();
columnCount = getColumnCount(columns);
rowData = new ArrayList(INITIAL_ROW_COUNT);
//
// Load result set into buffer
//
while (super.next()) {
rowData.add(copyRow(currentRow));
}
rowsInResult = rowData.size();
initialRowCnt = rowsInResult;
pos = POS_BEFORE_FIRST;
if (warning != null) {
statement.addWarning(warning);
}
}
}
/** Adds an SQLWarning to the warning chain. */
synchronized void addWarning(SQLWarning warn) {
if (warnings == null) warnings = warn;
else warnings.setNextWarning(warn);
}