/**
* 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;
}
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;
}
}
/**
* @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();
}
}
@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();
}
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 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;
}
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);
}
}