/**********************************************************************************
* $URL: https://source.sakaiproject.org/svn/kernel/trunk/kernel-impl/src/main/java/org/sakaiproject/db/impl/BasicSqlService.java $
* $Id: BasicSqlService.java 105077 2012-02-24 22:54:29Z ottenhoff@longsight.com $
***********************************************************************************
*
* Copyright (c) 2003, 2004, 2005, 2006, 2007, 2008 Sakai Foundation
*
* Licensed under the Educational Community License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.opensource.org/licenses/ECL-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
**********************************************************************************/
package org.sakaiproject.db.impl;
import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Arrays;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;
import java.util.Vector;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.sakaiproject.db.api.SqlReader;
import org.sakaiproject.db.api.SqlReaderFinishedException;
import org.sakaiproject.db.api.SqlService;
import org.sakaiproject.db.api.SqlServiceDeadlockException;
import org.sakaiproject.db.api.SqlServiceUniqueViolationException;
import org.sakaiproject.event.api.UsageSessionService;
import org.sakaiproject.exception.ServerOverloadException;
import org.sakaiproject.thread_local.api.ThreadLocalManager;
import org.sakaiproject.time.api.Time;
/**
* <p>
* BasicSqlService implements the SqlService.
* </p>
*/
public abstract class BasicSqlService implements SqlService
{
private static final Log LOG = LogFactory.getLog(BasicSqlService.class);
private static final Log SWC_LOG = LogFactory.getLog(StreamWithConnection.class);
/** Key name in thread local to find the current transaction connection. */
protected static final String TRANSACTION_CONNECTION = "sqlService:transaction_connection";
/** The "shared", "common" database connection pool */
protected DataSource defaultDataSource;
/** The "slow" connection pool for file uploads/downloads */
protected DataSource longDataSource;
/** Should we do a commit after a single statement read? */
protected boolean m_commitAfterRead = false;
/*************************************************************************************************************************************************
* Dependencies
************************************************************************************************************************************************/
/**
* @return the UsageSessionService collaborator.
*/
protected abstract UsageSessionService usageSessionService();
/**
* @return the ThreadLocalManager collaborator.
*/
protected abstract ThreadLocalManager threadLocalManager();
/*************************************************************************************************************************************************
* Configuration
************************************************************************************************************************************************/
/**
* Configuration: should we do a commit after each single SQL read?
*
* @param value
* the setting (true of false) string.
*/
public void setCommitAfterRead(String value)
{
if (LOG.isDebugEnabled())
{
LOG.debug("setCommitAfterRead(String " + value + ")");
}
m_commitAfterRead = Boolean.valueOf(value).booleanValue();
}
/** Database vendor used; possible values are oracle, mysql, hsqldb (default). */
protected String m_vendor = "hsqldb";
/**
* Configuration: Database vendor used; possible values are oracle, mysql, hsqldb.
*
* @param value
* the Database vendor used.
*/
public void setVendor(String value)
{
if (LOG.isDebugEnabled())
{
LOG.debug("setVendor(String " + value + ")");
}
m_vendor = (value != null) ? value.toLowerCase().trim() : null;
}
/**
* @inheritDoc
*/
public String getVendor()
{
return m_vendor;
}
/** if true, debug each sql command with timing. */
protected boolean m_showSql = false;
/**
* Configuration: to show each sql command in the logs or not.
*
* @param value
* the showSql setting.
*/
public void setShowSql(String value)
{
if (LOG.isDebugEnabled())
{
LOG.debug("setShowSql(String " + value + ")");
}
m_showSql = Boolean.valueOf(value).booleanValue();
}
/** Configuration: number of on-deadlock retries for save. */
protected int m_deadlockRetries = 5;
/**
* Configuration: number of on-deadlock retries for save.
*
* @param value
* the number of on-deadlock retries for save.
*/
public void setDeadlockRetries(String value)
{
m_deadlockRetries = Integer.parseInt(value);
}
/** Configuration: to run the ddl on init or not. */
protected boolean m_autoDdl = false;
/**
* Configuration: to run the ddl on init or not.
*
* @param value
* the auto ddl value.
*/
public void setAutoDdl(String value)
{
if (LOG.isDebugEnabled())
{
LOG.debug("setAutoDdl(String " + value + ")");
}
m_autoDdl = Boolean.valueOf(value).booleanValue();
}
/** contains a map of the database dependent handlers. */
protected Map<String, SqlServiceSql> databaseBeans;
/** The db handler we are using. */
protected SqlServiceSql sqlServiceSql;
/**
* sets which bean containing database dependent code should be used depending on the database vendor.
*/
public void setDatabaseBeans(Map databaseBeans)
{
this.databaseBeans = databaseBeans;
}
/**
* sets which bean containing database dependent code should be used depending on the database vendor.
*/
public void setSqlServiceSql(String vendor)
{
this.sqlServiceSql = (databaseBeans.containsKey(vendor) ? databaseBeans.get(vendor) : databaseBeans.get("default"));
}
/*************************************************************************************************************************************************
* Init and Destroy
************************************************************************************************************************************************/
/**
* Final initialization, once all dependencies are set.
*/
public void init()
{
setSqlServiceSql(getVendor());
// if we are auto-creating our schema, check and create
if (m_autoDdl)
{
ddl(getClass().getClassLoader(), "sakai_locks");
}
LOG.info("init(): vendor: " + m_vendor + " autoDDL: " + m_autoDdl + " deadlockRetries: " + m_deadlockRetries);
}
/**
* Final cleanup.
*/
public void destroy()
{
LOG.info("destroy()");
}
/*************************************************************************************************************************************************
* Work interface methods: org.sakaiproject.sql.SqlService
************************************************************************************************************************************************/
/**
* {@inheritDoc}
*/
public Connection borrowConnection() throws SQLException
{
LOG.debug("borrowConnection()");
if (defaultDataSource != null)
{
return defaultDataSource.getConnection();
}
else
{
throw new SQLException("no default pool.");
}
}
/**
* {@inheritDoc}
*/
public void returnConnection(Connection conn)
{
if (LOG.isDebugEnabled())
{
LOG.debug("returnConnection(Connection " + conn + ")");
}
if (conn != null)
{
try
{
conn.close();
}
catch (SQLException e)
{
throw new AssertionError(e);
}
}
}
/**
* {@inheritDoc}
*/
public boolean transact(Runnable callback, String tag)
{
// if we are already in a transaction, stay in it (don't start a new one), and just run the callback (no retries, let the outside transaction
// code handle that)
if (threadLocalManager().get(TRANSACTION_CONNECTION) != null)
{
callback.run();
return true;
}
// in case of deadlock we might retry
for (int i = 0; i <= m_deadlockRetries; i++)
{
if (i > 0)
{
// make a little fuss
LOG.warn("transact: deadlock: retrying (" + i + " / " + m_deadlockRetries + "): " + tag);
// do a little wait, longer for each retry
// TODO: randomize?
try
{
Thread.sleep(i * 100L);
}
catch (Exception ignore)
{
}
}
Connection connection = null;
boolean wasCommit = true;
try
{
connection = borrowConnection();
wasCommit = connection.getAutoCommit();
connection.setAutoCommit(false);
// store the connection in the thread
threadLocalManager().set(TRANSACTION_CONNECTION, connection);
callback.run();
connection.commit();
return true;
}
catch (SqlServiceDeadlockException e)
{
// rollback
if (connection != null)
{
try
{
connection.rollback();
LOG.warn("transact: deadlock: rolling back: " + tag);
}
catch (Exception ee)
{
LOG.warn("transact: (deadlock: rollback): " + tag + " : " + ee);
}
}
// if this was the last attempt, throw to abort
if (i == m_deadlockRetries)
{
LOG.warn("transact: deadlock: retry failure: " + tag);
throw e;
}
}
catch (RuntimeException e)
{
// rollback
if (connection != null)
{
try
{
connection.rollback();
LOG.warn("transact: rolling back: " + tag);
}
catch (Exception ee)
{
LOG.warn("transact: (rollback): " + tag + " : " + ee);
}
}
LOG.warn("transact: failure: " + e);
throw e;
}
catch (SQLException e)
{
// rollback
if (connection != null)
{
try
{
connection.rollback();
LOG.warn("transact: rolling back: " + tag);
}
catch (Exception ee)
{
LOG.warn("transact: (rollback): " + tag + " : " + ee);
}
}
LOG.warn("transact: failure: " + e);
throw new RuntimeException("SqlService.transact failure", e);
}
finally
{
if (connection != null)
{
// clear the connection from the thread
threadLocalManager().set(TRANSACTION_CONNECTION, null);
try
{
connection.setAutoCommit(wasCommit);
}
catch (Exception e)
{
LOG.warn("transact: (setAutoCommit): " + tag + " : " + e);
}
returnConnection(connection);
}
}
}
return false;
}
/** Used to work with dates in GMT in the db. */
protected final GregorianCalendar m_cal = new GregorianCalendar(TimeZone.getTimeZone("GMT"));
/**
* {@inheritDoc}
*/
public GregorianCalendar getCal()
{
//KNL-637 Calendar is not thread safe so we can't use the shared object
return (GregorianCalendar)m_cal.clone();
}
/**
* Read a single field from the db, from multiple records, returned as List<String>, one per record.
*
* @param sql
* The sql statement.
* @return The List of Strings of single fields of the record found, or empty if none found.
*/
public List<String> dbRead(String sql)
{
if (LOG.isDebugEnabled())
{
LOG.debug("dbRead(String " + sql + ")");
}
return dbRead(sql, null, null);
}
/**
* Process a query, filling in with fields, and return the results as a List, one per record read. If a reader is provided, it will be called for
* each record to prepare the Object placed into the List. Otherwise, the first field of each record, as a String, will be placed in the list.
*
* @param sql
* The sql statement.
* @param fields
* The array of fields for parameters.
* @param reader
* The reader object to read each record.
* @return The List of things read, one per record.
*/
public List dbRead(String sql, Object[] fields, SqlReader reader)
{
if (LOG.isDebugEnabled())
{
LOG.debug("dbRead(String " + sql + ", Object[] " + Arrays.toString(fields) + ", SqlReader " + reader + ")");
}
return dbRead(null, sql, fields, reader);
}
/**
* Process a query, filling in with fields, and return the results as a List, one per record read. If a reader is provided, it will be called for
* each record to prepare the Object placed into the List. Otherwise, the first field of each record, as a String, will be placed in the list.
*
* @param callerConn
* The db connection object to use (if not null).
* @param sql
* The sql statement.
* @param fields
* The array of fields for parameters.
* @param reader
* The reader object to read each record.
* @return The List of things read, one per record.
*/
public List dbRead(Connection callerConn, String sql, Object[] fields, SqlReader reader)
{
// check for a transaction conncetion
if (callerConn == null)
{
callerConn = (Connection) threadLocalManager().get(TRANSACTION_CONNECTION);
}
if (LOG.isDebugEnabled())
{
LOG.debug("dbRead(Connection " + callerConn + ", String " + sql + ", Object[] " + Arrays.toString(fields) + ", SqlReader " + reader + ")");
}
// for DEBUG
long start = 0;
long connectionTime = 0;
int lenRead = 0;
long stmtTime = 0;
long resultsTime = 0;
int count = 0;
if (LOG.isDebugEnabled())
{
String userId = usageSessionService().getSessionId();
StringBuilder buf = new StringBuilder();
if (fields != null)
{
buf.append(fields[0]);
for (int i = 1; i < fields.length; i++)
{
buf.append(", ");
buf.append(fields[i]);
}
}
LOG.debug("Sql.dbRead: " + userId + "\n" + sql + "\n" + buf);
}
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet result = null;
ResultSetMetaData meta = null;
List rv = new Vector();
try {
if (m_showSql) {
start = System.currentTimeMillis();
}
// borrow a new connection if we are not provided with one to use
if (callerConn != null) {
conn = callerConn;
} else {
conn = borrowConnection();
}
if (m_showSql) {
connectionTime = System.currentTimeMillis() - start;
}
if (m_showSql) {
start = System.currentTimeMillis();
}
pstmt = conn.prepareStatement(sql);
// put in all the fields
prepareStatement(pstmt, fields);
result = pstmt.executeQuery();
if (m_showSql) {
stmtTime = System.currentTimeMillis() - start;
}
if (m_showSql) {
start = System.currentTimeMillis();
}
while (result.next()) {
if (m_showSql) {
count++;
}
// without a reader, we read the first String from each record
if (reader == null) {
String s = result.getString(1);
if (s != null) {
rv.add(s);
}
} else {
try {
Object obj = reader.readSqlResultRecord(result);
if (obj != null) {
rv.add(obj);
}
} catch (SqlReaderFinishedException e) {
break;
}
}
}
} catch (SQLException e) {
LOG.warn("Sql.dbRead: sql: " + sql + debugFields(fields), e);
} catch (UnsupportedEncodingException e) {
LOG.warn("Sql.dbRead: sql: " + sql + debugFields(fields), e);
} finally {
if (m_showSql) {
resultsTime = System.currentTimeMillis() - start;
}
if (null != result) {
try {
result.close();
} catch (SQLException e) {
LOG.warn("Sql.dbRead: sql: " + sql + debugFields(fields), e);
}
}
if (null != pstmt) {
try {
pstmt.close();
} catch (SQLException e) {
LOG.warn("Sql.dbRead: sql: " + sql + debugFields(fields), e);
}
}
// return the connection only if we have borrowed a new one for this call
if (callerConn == null) {
if (null != conn) {
// if we commit on read
if (m_commitAfterRead) {
try {
conn.commit();
} catch (SQLException e) {
LOG.warn("Sql.dbRead: sql: " + sql + debugFields(fields), e);
}
}
returnConnection(conn);
}
}
}
if (m_showSql) debug("Sql.dbRead: time: " + connectionTime + " / " + stmtTime + " / " + resultsTime + " #: " + count, sql, fields);
return rv;
}
/**
* Read a single field from the db, from multiple record - concatenating the binary values into value.
*
* @param sql
* The sql statement.
* @param fields
* The array of fields for parameters.
* @param value
* The array of bytes to fill with the value read from the db.
*/
public void dbReadBinary(String sql, Object[] fields, byte[] value)
{
if (LOG.isDebugEnabled())
{
LOG.debug("dbReadBinary(String " + sql + ", Object[] " + Arrays.toString(fields) + ")");
}
dbReadBinary(null, sql, fields, value);
}
/**
* Read a single field from the db, from multiple record - concatenating the binary values into value.
*
* @param callerConn
* The optional db connection object to use.
* @param sql
* The sql statement.
* @param fields
* The array of fields for parameters.
* @param value
* The array of bytes to fill with the value read from the db.
*/
public void dbReadBinary(Connection callerConn, String sql, Object[] fields, byte[] value)
{
// check for a transaction conncetion
if (callerConn == null)
{
callerConn = (Connection) threadLocalManager().get(TRANSACTION_CONNECTION);
}
if (LOG.isDebugEnabled())
{
LOG.debug("dbReadBinary(Connection " + callerConn + ", String " + sql + ", Object[] " + Arrays.toString(fields) + ")");
}
// for DEBUG
long start = 0;
long connectionTime = 0;
int lenRead = 0;
if (LOG.isDebugEnabled())
{
String userId = usageSessionService().getSessionId();
LOG.debug("Sql.dbReadBinary(): " + userId + "\n" + sql);
}
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet result = null;
ResultSetMetaData meta = null;
try
{
if (m_showSql) start = System.currentTimeMillis();
if (callerConn != null)
{
conn = callerConn;
}
else
{
conn = borrowConnection();
}
if (m_showSql) connectionTime = System.currentTimeMillis() - start;
if (m_showSql) start = System.currentTimeMillis();
pstmt = conn.prepareStatement(sql);
// put in all the fields
prepareStatement(pstmt, fields);
result = pstmt.executeQuery();
int index = 0;
while (result.next() && (index < value.length))
{
InputStream stream = result.getBinaryStream(1);
int len = stream.read(value, index, value.length - index);
stream.close();
index += len;
if (m_showSql) lenRead += len;
}
} catch (Exception e) {
LOG.warn("Sql.dbReadBinary(): " + e);
} finally {
if (null != result) {
try {
result.close();
} catch (SQLException e) {
LOG.warn("Sql.dbReadBinary(): result close fail: " + e);
}
}
if (null != pstmt) {
try {
pstmt.close();
} catch (SQLException e) {
LOG.warn("Sql.dbReadBinary(): pstmt close fail: " + e);
}
}
// return the connection only if we have borrowed a new one for this call
if (callerConn == null) {
if (null != conn) {
// if we commit on read
if (m_commitAfterRead) {
try {
conn.commit();
} catch (SQLException e) {
LOG.warn("Sql.dbReadBinary(): conn commit fail: " + e);
}
}
returnConnection(conn);
}
}
}
if (m_showSql) {
debug("sql read binary: len: " + lenRead + " time: " + connectionTime + " / " + (System.currentTimeMillis() - start), sql, fields);
}
}
/**
* Read a single field / record from the db, returning a stream on the result record / field. The stream holds the conection open - so it must be
* closed or finalized quickly!
*
* @param sql
* The sql statement.
* @param fields
* The array of fields for parameters.
* @param big
* If true, the read is expected to be potentially large.
* @throws ServerOverloadException
* if the read cannot complete due to lack of a free connection (if wait is false)
*/
public InputStream dbReadBinary(String sql, Object[] fields, boolean big) throws ServerOverloadException
{
// Note: does not support TRANSACTION_CONNECTION -ggolden
if (LOG.isDebugEnabled())
{
LOG.debug("dbReadBinary(String " + sql + ", Object[] " + Arrays.toString(fields) + ", boolean " + big + ")");
}
InputStream rv = null;
// for DEBUG
long start = 0;
long connectionTime = 0;
int lenRead = 0;
if (LOG.isDebugEnabled()) {
String userId = usageSessionService().getSessionId();
LOG.debug("Sql.dbReadBinary(): " + userId + "\n" + sql);
}
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet result = null;
try {
if (m_showSql) {
start = System.currentTimeMillis();
}
if (!big) {
conn = borrowConnection();
} else {
// get a connection if it's available, else throw
conn = borrowConnection();
if (conn == null) {
throw new ServerOverloadException(null);
}
}
if (m_showSql) {
connectionTime = System.currentTimeMillis() - start;
}
if (m_showSql) {
start = System.currentTimeMillis();
}
pstmt = conn.prepareStatement(sql);
// put in all the fields
prepareStatement(pstmt, fields);
result = pstmt.executeQuery();
if (result.next()) {
InputStream stream = result.getBinaryStream(1);
rv = new StreamWithConnection(stream, result, pstmt, conn);
}
} catch (ServerOverloadException e) {
throw e;
} catch (SQLException e) {
LOG.warn("Sql.dbReadBinary(): " + e);
} catch (UnsupportedEncodingException e) {
LOG.warn("Sql.dbReadBinary(): " + e);
} finally {
// ONLY if we didn't make the rv - else let the rv hold these OPEN!
if (rv == null) {
if (null != result) {
try {
result.close();
} catch (SQLException e) {
LOG.warn("Sql.dbReadBinary(): " + e);
}
}
if (null != pstmt) {
try {
pstmt.close();
} catch (SQLException e) {
LOG.warn("Sql.dbReadBinary(): " + e);
}
}
if (null != conn) {
// if we commit on read
if (m_commitAfterRead) {
try {
conn.commit();
} catch (SQLException e) {
LOG.warn("Sql.dbReadBinary(): " + e);
}
}
returnConnection(conn);
}
}
// LOG.warn("Sql.dbReadBinary(): " + e);
}
if (m_showSql) {
debug("sql read binary: len: " + lenRead + " time: " + connectionTime + " / " + (System.currentTimeMillis() - start), sql, fields);
}
return rv;
}
/**
* Execute the "write" sql - no response.
*
* @param sql
* The sql statement.
* @return true if successful, false if not.
*/
public boolean dbWrite(String sql)
{
if (LOG.isDebugEnabled())
{
LOG.debug("dbWrite(String " + sql + ")");
}
return dbWrite(sql, null, null, null, false);
} // dbWrite
/**
* Execute the "write" sql - no response. a long field is set to "?" - fill it in with var
*
* @param sql
* The sql statement.
* @param var
* The value to bind to the first parameter in the sql statement.
* @return true if successful, false if not.
*/
public boolean dbWrite(String sql, String var)
{
if (LOG.isDebugEnabled())
{
LOG.debug("dbWrite(String " + sql + ", String " + var + ")");
}
return dbWrite(sql, null, var, null, false);
}
/**
* Execute the "write" sql - no response. a long binary field is set to "?" - fill it in with var
*
* @param sql
* The sql statement.
* @param fields
* The array of fields for parameters.
* @param var
* The value to bind to the last parameter in the sql statement.
* @param offset
* The start within the var to write
* @param len
* The number of bytes of var, starting with index, to write
* @return true if successful, false if not.
*/
public boolean dbWriteBinary(String sql, Object[] fields, byte[] var, int offset, int len)
{
// Note: does not support TRANSACTION_CONNECTION -ggolden
if (LOG.isDebugEnabled())
{
LOG.debug("dbWriteBinary(String " + sql + ", Object[] " + Arrays.toString(fields) + ", byte[] " + Arrays.toString(var) + ", int " + offset + ", int " + len + ")");
}
// for DEBUG
long start = 0;
long connectionTime = 0;
if (LOG.isDebugEnabled())
{
String userId = usageSessionService().getSessionId();
LOG.debug("Sql.dbWriteBinary(): " + userId + "\n" + sql + " size:" + var.length);
}
Connection conn = null;
PreparedStatement pstmt = null;
boolean autoCommit = false;
boolean resetAutoCommit = false;
// stream from the var
InputStream varStream = new ByteArrayInputStream(var, offset, len);
boolean success = false;
try
{
if (m_showSql) start = System.currentTimeMillis();
conn = borrowConnection();
if (m_showSql) connectionTime = System.currentTimeMillis() - start;
// make sure we do not have auto commit - will change and reset if needed
autoCommit = conn.getAutoCommit();
if (autoCommit)
{
conn.setAutoCommit(false);
resetAutoCommit = true;
}
if (m_showSql) start = System.currentTimeMillis();
pstmt = conn.prepareStatement(sql);
// put in all the fields
int pos = prepareStatement(pstmt, fields);
// last, put in the binary
pstmt.setBinaryStream(pos, varStream, len);
//int result =
pstmt.executeUpdate();
// commit and indicate success
conn.commit();
success = true;
}
catch (SQLException e)
{
// this is likely due to a key constraint problem...
return false;
}
catch (Exception e)
{
LOG.warn("Sql.dbWriteBinary(): " + e);
return false;
}
finally
{
//try
//{
if (null != pstmt)
{
try {
pstmt.close();
} catch (SQLException e) {
LOG.warn("Sql.dbWriteBinary(): " + e);
}
}
if (null != varStream)
{
try {
varStream.close();
} catch (IOException e) {
LOG.warn("Sql.dbWriteBinary(): " + e);
}
}
if (null != conn)
{
// rollback on failure
if (!success)
{
try {
conn.rollback();
} catch (SQLException e) {
LOG.warn("Sql.dbWriteBinary(): " + e);
}
}
// if we changed the auto commit, reset here
if (resetAutoCommit)
{
try {
conn.setAutoCommit(autoCommit);
} catch (SQLException e) {
LOG.warn("Sql.dbWriteBinary(): " + e);
}
}
returnConnection(conn);
}
}
if (m_showSql)
debug("sql write binary: len: " + len + " time: " + connectionTime + " / " + (System.currentTimeMillis() - start), sql, fields);
return true;
}
/**
* Execute the "write" sql - no response, using a set of fields from an array plus one more as params.
*
* @param sql
* The sql statement.
* @param fields
* The array of fields for parameters.
* @return true if successful, false if not.
*/
public boolean dbWrite(String sql, Object[] fields)
{
if (LOG.isDebugEnabled())
{
LOG.debug("dbWrite(String " + sql + ", Object[] " + Arrays.toString(fields) + ")");
}
return dbWrite(sql, fields, null, null, false);
}
/**
* Execute the "write" sql - no response, using a set of fields from an array and a given connection.
*
* @param connection
* The connection to use.
* @param sql
* The sql statement.
* @param fields
* The array of fields for parameters.
* @return true if successful, false if not.
*/
public boolean dbWrite(Connection connection, String sql, Object[] fields)
{
if (LOG.isDebugEnabled())
{
LOG.debug("dbWrite(Connection " + connection + ", String " + sql + ", Object[] " + Arrays.toString(fields) + ")");
}
return dbWrite(sql, fields, null, connection, false);
}
/**
* Execute the "write" sql - no response, using a set of fields from an array and a given connection logging no errors on failure.
*
* @param connection
* The connection to use.
* @param sql
* The sql statement.
* @param fields
* The array of fields for parameters.
* @return true if successful, false if not.
*/
public boolean dbWriteFailQuiet(Connection connection, String sql, Object[] fields)
{
if (LOG.isDebugEnabled())
{
LOG.debug("dbWriteFailQuiet(Connection " + connection + ", String " + sql + ", Object[] " + Arrays.toString(fields) + ")");
}
return dbWrite(sql, fields, null, connection, true);
}
/**
* Execute the "write" sql - no response, using a set of fields from an array plus one more as params.
*
* @param sql
* The sql statement.
* @param fields
* The array of fields for parameters.
* @param lastField
* The value to bind to the last parameter in the sql statement.
* @return true if successful, false if not.
*/
public boolean dbWrite(String sql, Object[] fields, String lastField)
{
if (LOG.isDebugEnabled())
{
LOG.debug("dbWrite(String " + sql + ", Object[] " + Arrays.toString(fields) + ", String " + lastField + ")");
}
return dbWrite(sql, fields, lastField, null, false);
}
/**
* Execute the "write" sql - no response, using a set of fields from an array plus one more as params and connection.
*
* @param sql
* The sql statement.
* @param fields
* The array of fields for parameters.
* @param lastField
* The value to bind to the last parameter in the sql statement.
* @param callerConnection
* The connection to use.
* @param failQuiet
* If true, don't log errors from statement failure
* @return true if successful, false if not due to unique constraint violation or duplicate key (i.e. the record already exists) OR we are
* instructed to fail quiet.
*/
protected boolean dbWrite(String sql, Object[] fields, String lastField, Connection callerConnection, boolean failQuiet)
{
return ( dbWriteCount(sql, fields, lastField, callerConnection, failQuiet) >= 0 ) ;
}
/**
* Execute the "write/update" sql - no response, using a set of fields from an array plus one more as params and connection.
*
* @param sql
* The sql statement.
* @param fields
* The array of fields for parameters.
* @param lastField
* The value to bind to the last parameter in the sql statement.
* @param callerConnection
* The connection to use.
* @param failQuiet
* If true, don't log errors from statement failure
* @return the number of records affected or -1 if something goes wrong if not due to unique constraint
* violation or duplicate key (i.e. the record already exists) OR we are instructed to fail quiet.
*/
public int dbWriteCount(String sql, Object[] fields, String lastField, Connection callerConnection, boolean failQuiet)
{
int retval = -1;
// check for a transaction connection
if (callerConnection == null)
{
callerConnection = (Connection) threadLocalManager().get(TRANSACTION_CONNECTION);
}
if (LOG.isDebugEnabled())
{
LOG.debug("dbWrite(String " + sql + ", Object[] " + Arrays.toString(fields) + ", String " + lastField + ", Connection " + callerConnection + ", boolean "
+ failQuiet + ")");
}
// for DEBUG
long start = 0;
long connectionTime = 0;
if (LOG.isDebugEnabled())
{
String userId = usageSessionService().getSessionId();
StringBuilder buf = new StringBuilder();
if (fields != null)
{
buf.append(fields[0]);
for (int i = 1; i < fields.length; i++)
{
buf.append(", ");
buf.append(fields[i]);
}
if (lastField != null)
{
buf.append(", ");
buf.append(lastField);
}
}
else if (lastField != null)
{
buf.append(lastField);
}
LOG.debug("Sql.dbWrite(): " + userId + "\n" + sql + "\n" + buf);
}
Connection conn = null;
PreparedStatement pstmt = null;
boolean autoCommit = false;
boolean resetAutoCommit = false;
boolean success = false;
try
{
if (callerConnection != null)
{
conn = callerConnection;
}
else
{
if (m_showSql) start = System.currentTimeMillis();
conn = borrowConnection();
if (m_showSql) connectionTime = System.currentTimeMillis() - start;
// make sure we have do not have auto commit - will change and reset if needed
autoCommit = conn.getAutoCommit();
if (autoCommit)
{
conn.setAutoCommit(false);
resetAutoCommit = true;
}
}
if (m_showSql) start = System.currentTimeMillis();
pstmt = conn.prepareStatement(sql);
// put in all the fields
int pos = prepareStatement(pstmt, fields);
// last, put in the string value
if (lastField != null)
{
sqlServiceSql.setBytes(pstmt, lastField, pos);
pos++;
}
retval = pstmt.executeUpdate();
// commit unless we are in a transaction (provided with a connection)
if (callerConnection == null)
{
conn.commit();
}
// indicate success
success = true;
}
catch (SQLException e)
{
// is this due to a key constraint problem?... check each vendor's error codes
boolean recordAlreadyExists = sqlServiceSql.getRecordAlreadyExists(e);
if (m_showSql)
{
LOG.warn("Sql.dbWrite(): error code: " + e.getErrorCode() + " sql: " + sql + " binds: " + debugFields(fields) + " " + e);
}
// if asked to fail quietly, just return -1 if we find this error.
if (recordAlreadyExists || failQuiet) return -1;
// perhaps due to a mysql deadlock?
if (sqlServiceSql.isDeadLockError(e.getErrorCode()))
{
// just a little fuss
LOG.warn("Sql.dbWrite(): deadlock: error code: " + e.getErrorCode() + " sql: " + sql + " binds: " + debugFields(fields) + " " + e.toString());
throw new SqlServiceDeadlockException(e);
}
else if (recordAlreadyExists)
{
// just a little fuss
LOG.warn("Sql.dbWrite(): unique violation: error code: " + e.getErrorCode() + " sql: " + sql + " binds: " + debugFields(fields) + " " + e.toString());
throw new SqlServiceUniqueViolationException(e);
}
else
{
// something ELSE went wrong, so lest make a fuss
LOG.warn("Sql.dbWrite(): error code: " + e.getErrorCode() + " sql: " + sql + " binds: " + debugFields(fields) + " ", e);
throw new RuntimeException("SqlService.dbWrite failure", e);
}
}
catch (Exception e)
{
LOG.warn("Sql.dbWrite(): " + e);
throw new RuntimeException("SqlService.dbWrite failure", e);
}
finally
{
try
{
if (null != pstmt) pstmt.close();
if ((null != conn) && (callerConnection == null))
{
// rollback on failure
if (!success)
{
conn.rollback();
}
// if we changed the auto commit, reset here
if (resetAutoCommit)
{
conn.setAutoCommit(autoCommit);
}
returnConnection(conn);
}
}
catch (Exception e)
{
LOG.warn("Sql.dbWrite(): " + e);
throw new RuntimeException("SqlService.dbWrite failure", e);
}
}
if (m_showSql)
debug("Sql.dbWrite(): len: " + ((lastField != null) ? "" + lastField.length() : "null") + " time: " + connectionTime + " / "
+ (System.currentTimeMillis() - start), sql, fields);
return retval;
}
/**
* Execute the "insert" sql, returning a possible auto-update field Long value
*
* @param sql
* The sql statement.
* @param fields
* The array of fields for parameters.
* @param callerConnection
* The connection to use.
* @param autoColumn
* The name of the db column that will have auto-update - we will return the value used (leave null to disable this feature).
* @return The auto-update value, or null
*/
public Long dbInsert(Connection callerConnection, String sql, Object[] fields, String autoColumn)
{
return dbInsert(callerConnection, sql, fields, autoColumn, null, 0);
}
/**
* Execute the "insert" sql, returning a possible auto-update field Long value
*
* @param sql
* The sql statement.
* @param fields
* The array of fields for parameters.
* @param callerConnection
* The connection to use.
* @param autoColumn
* The name of the db column that will have auto-update - we will return the value used (leave null to disable this feature).
* @param last
* A stream to set as the last field.
* @return The auto-update value, or null
*/
public Long dbInsert(Connection callerConnection, String sql, Object[] fields, String autoColumn, InputStream last, int lastLength)
{
boolean connFromThreadLocal = false;
// check for a transaction conncetion
if (callerConnection == null)
{
callerConnection = (Connection) threadLocalManager().get(TRANSACTION_CONNECTION);
if(callerConnection != null)
{
// KNL-492 We set this so we can avoid returning a connection that is being managed elsewhere
connFromThreadLocal = true;
}
}
if (LOG.isDebugEnabled())
{
LOG.debug("dbInsert(String " + sql + ", Object[] " + Arrays.toString(fields) + ", Connection " + callerConnection + ")");
}
// for DEBUG
long start = 0;
long connectionTime = 0;
if (LOG.isDebugEnabled())
{
String userId = usageSessionService().getSessionId();
StringBuilder buf = new StringBuilder();
if (fields != null)
{
buf.append(fields[0]);
for (int i = 1; i < fields.length; i++)
{
buf.append(", ");
buf.append(fields[i]);
}
}
LOG.debug("Sql.dbInsert(): " + userId + "\n" + sql + "\n" + buf);
}
Connection conn = null;
PreparedStatement pstmt = null;
boolean autoCommit = false;
boolean resetAutoCommit = false;
boolean success = false;
Long rv = null;
try
{
if (callerConnection != null)
{
conn = callerConnection;
}
else
{
if (m_showSql) start = System.currentTimeMillis();
conn = borrowConnection();
if (m_showSql) connectionTime = System.currentTimeMillis() - start;
// make sure we have do not have auto commit - will change and reset if needed
autoCommit = conn.getAutoCommit();
if (autoCommit)
{
conn.setAutoCommit(false);
resetAutoCommit = true;
}
}
if (m_showSql) start = System.currentTimeMillis();
pstmt = sqlServiceSql.prepareAutoColumn(conn, sql, autoColumn);
// put in all the fields
int pos = prepareStatement(pstmt, fields);
// and the last one
if (last != null)
{
pstmt.setBinaryStream(pos, last, lastLength);
}
int result = pstmt.executeUpdate();
rv = sqlServiceSql.getGeneratedKey(pstmt, sql);
// commit unless we are in a transaction (provided with a connection)
if (callerConnection == null)
{
conn.commit();
}
// indicate success
success = true;
}
catch (SQLException e)
{
// is this due to a key constraint problem... check each vendor's error codes
boolean recordAlreadyExists = sqlServiceSql.getRecordAlreadyExists(e);
if (m_showSql)
{
LOG.warn("Sql.dbInsert(): error code: " + e.getErrorCode() + " sql: " + sql + " binds: " + debugFields(fields) + " " + e);
}
if (recordAlreadyExists) return null;
// perhaps due to a mysql deadlock?
if (("mysql".equals(m_vendor)) && (e.getErrorCode() == 1213))
{
// just a little fuss
LOG.warn("Sql.dbInsert(): deadlock: error code: " + e.getErrorCode() + " sql: " + sql + " binds: " + debugFields(fields) + " "
+ e.toString());
throw new SqlServiceDeadlockException(e);
}
else if (recordAlreadyExists)
{
// just a little fuss
LOG.warn("Sql.dbInsert(): unique violation: error code: " + e.getErrorCode() + " sql: " + sql + " binds: " + debugFields(fields)
+ " " + e.toString());
throw new SqlServiceUniqueViolationException(e);
}
else
{
// something ELSE went wrong, so lest make a fuss
LOG.warn("Sql.dbInsert(): error code: " + e.getErrorCode() + " sql: " + sql + " binds: " + debugFields(fields) + " ", e);
throw new RuntimeException("SqlService.dbInsert failure", e);
}
}
catch (Exception e)
{
LOG.warn("Sql.dbInsert(): " + e);
throw new RuntimeException("SqlService.dbInsert failure", e);
}
finally
{
try
{
if (null != pstmt) pstmt.close();
if ((null != conn) && (callerConnection == null))
{
// rollback on failure
if (!success)
{
conn.rollback();
}
// if we changed the auto commit, reset here
if (resetAutoCommit)
{
conn.setAutoCommit(autoCommit);
}
}
}
catch (Exception e)
{
LOG.warn("Sql.dbInsert(): " + e);
throw new RuntimeException("SqlService.dbInsert failure", e);
}
//make sure we return the connection even if the rollback etc above
// KNL-492 connFromThreadLocal is tested so we can avoid returning a
// connection that is being managed elsewhere
if (conn != null && !connFromThreadLocal)
{
returnConnection(conn);
}
}
if (m_showSql) debug("Sql.dbWrite(): len: " + " time: " + connectionTime + " / " + (System.currentTimeMillis() - start), sql, fields);
return rv;
}
/**
* Read a single field BLOB from the db from one record, and update it's bytes with content.
*
* @param sql
* The sql statement to select the BLOB.
* @param content
* The new bytes for the BLOB.
*/
public void dbReadBlobAndUpdate(String sql, byte[] content)
{
// Note: does not support TRANSACTION_CONNECTION -ggolden
if (LOG.isDebugEnabled())
{
LOG.debug("dbReadBlobAndUpdate(String " + sql + ", byte[] " + Arrays.toString(content) + ")");
}
if (!sqlServiceSql.canReadAndUpdateBlob())
{
throw new UnsupportedOperationException("BasicSqlService.dbReadBlobAndUpdate() is not supported by the " + getVendor() + " database.");
}
// for DEBUG
long start = 0;
long connectionTime = 0;
int lenRead = 0;
if (LOG.isDebugEnabled())
{
String userId = usageSessionService().getSessionId();
LOG.debug("Sql.dbReadBlobAndUpdate(): " + userId + "\n" + sql);
}
Connection conn = null;
Statement stmt = null;
ResultSet result = null;
ResultSetMetaData meta = null;
Object blob = null;
OutputStream os = null;
try
{
if (m_showSql) start = System.currentTimeMillis();
conn = borrowConnection();
if (m_showSql) connectionTime = System.currentTimeMillis() - start;
if (m_showSql) start = System.currentTimeMillis();
stmt = conn.createStatement();
result = stmt.executeQuery(sql);
if (result.next())
{
blob = result.getBlob(1);
}
if (blob != null)
{
// %%% not supported? b.truncate(0);
// int len = b.setBytes(0, content);
try
{
// Use reflection to remove compile time dependency on oracle driver
Class[] paramsClasses = new Class[0];
Method getBinaryOutputStreamMethod = blob.getClass().getMethod("getBinaryOutputStream", paramsClasses);
Object[] params = new Object[0];
os = (OutputStream) getBinaryOutputStreamMethod.invoke(blob, params);
os.write(content);
}
catch (NoSuchMethodException ex)
{
LOG.warn("Oracle driver error: " + ex);
}
catch (IllegalAccessException ex)
{
LOG.warn("Oracle driver error: " + ex);
}
catch (InvocationTargetException ex)
{
LOG.warn("Oracle driver error: " + ex);
} catch (IOException e) {
LOG.warn("Oracle driver error: " + e);
}
}
}
catch (SQLException e)
{
LOG.warn("Sql.dbReadBlobAndUpdate(): " + e);
}
finally
{
if (null != os) {
try {
os.close();
} catch (IOException e) {
LOG.warn("Sql.dbRead(): " + e);
}
}
if (null != result)
{
try {
result.close();
} catch (SQLException e) {
LOG.warn("Sql.dbRead(): " + e);
}
}
if (null != stmt)
{
try {
stmt.close();
} catch (SQLException e) {
LOG.warn("Sql.dbRead(): " + e);
}
}
if (null != conn)
{
// if we commit on read
if (m_commitAfterRead)
{
try {
conn.commit();
} catch (SQLException e) {
LOG.warn("Sql.dbRead(): " + e);
}
}
returnConnection(conn);
}
}
if (m_showSql)
debug("sql dbReadBlobAndUpdate: len: " + lenRead + " time: " + connectionTime + " / " + (System.currentTimeMillis() - start), sql, null);
}
/**
* Read a single field from the db, from a single record, return the value found, and lock for update.
*
* @param sql
* The sql statement.
* @param field
* A StringBuilder that will be filled with the field.
* @return The Connection holding the lock.
*/
public Connection dbReadLock(String sql, StringBuilder field)
{
// Note: does not support TRANSACTION_CONNECTION -ggolden
if (LOG.isDebugEnabled())
{
LOG.debug("dbReadLock(String " + sql + ", StringBuilder " + field + ")");
}
Connection conn = null;
Statement stmt = null;
ResultSet result = null;
boolean autoCommit = false;
boolean resetAutoCommit = false;
boolean closeConn = false;
try
{
// get a new connection
conn = borrowConnection();
// adjust to turn off auto commit - we need a transaction
autoCommit = conn.getAutoCommit();
if (autoCommit)
{
conn.setAutoCommit(false);
resetAutoCommit = true;
}
if (LOG.isDebugEnabled()) LOG.debug("Sql.dbReadLock():\n" + sql);
// create a statement and execute
stmt = conn.createStatement();
result = stmt.executeQuery(sql);
// if we have a result record
if (result.next())
{
// get the result and pack into the return buffer
String rv = result.getString(1);
if ((field != null) && (rv != null)) field.append(rv);
}
// otherwise we fail
else
{
closeConn = true;
}
}
// this is likely the error when the record is otherwise locked - we fail
catch (SQLException e)
{
// Note: ORA-00054 gives an e.getErrorCode() of 54, if anyone cares...
LOG.warn("Sql.dbUpdateLock(): " + e.getErrorCode() + " - " + e);
closeConn = true;
}
finally
{
// close the result and statement
if (result != null) {
try {
result.close();
} catch (SQLException e) {
LOG.warn("Sql.dbReadBinary(): " + e);
}
}
if (null != stmt) {
try {
stmt.close();
} catch (SQLException e) {
LOG.warn("Sql.dbReadBinary(): " + e);
}
}
// if we are failing, restore and release the connection
if ((closeConn) && (conn != null))
{
// just in case we got a lock
try {
conn.rollback();
} catch (SQLException e) {
LOG.warn("Sql.dbReadBinary(): " + e);
}
if (resetAutoCommit)
try {
conn.setAutoCommit(autoCommit);
} catch (SQLException e) {
LOG.warn("Sql.dbReadBinary(): " + e);
}
}
if (conn != null)
{
returnConnection(conn);
}
}
return conn;
}
/**
* Read a single field from the db, from a single record, return the value found, and lock for update.
*
* @param sql
* The sql statement.
* @param reader
* A SqlReader that buils the result.
* @return The Connection holding the lock.
*/
public Connection dbReadLock(String sql, SqlReader reader)
{
// Note: does not support TRANSACTION_CONNECTION -ggolden
if (LOG.isDebugEnabled())
{
LOG.debug("dbReadLock(String " + sql + ")");
}
Connection conn = null;
Statement stmt = null;
ResultSet result = null;
boolean autoCommit = false;
boolean resetAutoCommit = false;
boolean closeConn = false;
try
{
// get a new conncetion
conn = borrowConnection();
// adjust to turn off auto commit - we need a transaction
autoCommit = conn.getAutoCommit();
if (autoCommit)
{
conn.setAutoCommit(false);
resetAutoCommit = true;
}
if (LOG.isDebugEnabled()) LOG.debug("Sql.dbReadLock():\n" + sql);
// create a statement and execute
stmt = conn.createStatement();
result = stmt.executeQuery(sql);
// if we have a result record
if (result.next())
{
reader.readSqlResultRecord(result);
}
// otherwise we fail
else
{
closeConn = true;
}
}
// this is likely the error when the record is otherwise locked - we fail
catch (SQLException e)
{
// Note: ORA-00054 gives an e.getErrorCode() of 54, if anyone cares...
// LOG.warn("Sql.dbUpdateLock(): " + e.getErrorCode() + " - " + e);
closeConn = true;
}
catch (SqlReaderFinishedException e) {
LOG.warn("Sql.dbReadLock(): " + e);
closeConn = true;
}
finally
{
// close the result and statement
if (null != result) {
try {
result.close();
} catch (SQLException e) {
LOG.warn("Sql.dbReadBinary(): " + e);
}
}
if (null != stmt) {
try {
stmt.close();
} catch (SQLException e) {
LOG.warn("Sql.dbReadBinary(): " + e);
}
}
// if we are failing, restore and release the connectoin
if ((closeConn) && (conn != null))
{
// just in case we got a lock
try {
conn.rollback();
} catch (SQLException e) {
LOG.warn("Sql.dbReadBinary(): " + e);
}
if (resetAutoCommit)
try {
conn.setAutoCommit(autoCommit);
} catch (SQLException e) {
LOG.warn("Sql.dbReadBinary(): " + e);
}
}
// LOG.warn("Sql.dbReadLock(): " + e);
if (conn != null)
{
returnConnection(conn);
}
}
return conn;
}
/**
* Commit the update that was locked on this connection.
*
* @param sql
* The sql statement.
* @param fields
* The array of fields for parameters.
* @param var
* The value to bind to the last parameter in the sql statement.
* @param conn
* The database connection on which the lock was gained.
*/
public void dbUpdateCommit(String sql, Object[] fields, String var, Connection conn)
{
// Note: does not support TRANSACTION_CONNECTION -ggolden
if (LOG.isDebugEnabled())
{
LOG.debug("dbUpdateCommit(String " + sql + ", Object[] " + Arrays.toString(fields) + ", String " + var + ", Connection " + conn + ")");
}
PreparedStatement pstmt = null;
try
{
if (LOG.isDebugEnabled()) LOG.debug("Sql.dbUpdateCommit():\n" + sql);
pstmt = conn.prepareStatement(sql);
// put in all the fields
int pos = prepareStatement(pstmt, fields);
// prepare the update statement and fill with the last variable (if any)
if (var != null)
{
sqlServiceSql.setBytes(pstmt, var, pos);
pos++;
}
// run the SQL statement
pstmt.executeUpdate();
pstmt.close();
pstmt = null;
// commit
conn.commit();
}
catch (SQLException e)
{
LOG.warn("Sql.dbUpdateCommit(): " + e);
} catch (UnsupportedEncodingException e) {
LOG.warn("Sql.dbUpdateCommit(): " + e);
}
finally
{
try
{
// close the statemenet and restore / release the connection
if (null != pstmt) pstmt.close();
if (null != conn)
{
// we don't really know what this should be, but we assume the default is not
conn.setAutoCommit(false);
returnConnection(conn);
}
}
catch (Exception e)
{
LOG.warn("Sql.dbUpdateCommit(): " + e);
}
}
}
/**
* Cancel the update that was locked on this connection.
*
* @param conn
* The database connection on which the lock was gained.
*/
public void dbCancel(Connection conn)
{
// Note: does not support TRANSACTION_CONNECTION -ggolden
if (LOG.isDebugEnabled())
{
LOG.debug("dbCancel(Connection " + conn + ")");
}
try
{
// cancel any changes, release any locks
conn.rollback();
// we don't really know what this should be, but we assume the default is not
conn.setAutoCommit(false);
returnConnection(conn);
}
catch (Exception e)
{
LOG.warn("Sql.dbCancel(): " + e);
}
}
/**
* {@inheritDoc}
*/
public void ddl(ClassLoader loader, String resource)
{
if (LOG.isDebugEnabled())
{
LOG.debug("ddl(ClassLoader " + loader + ", String " + resource + ")");
}
// add the vender string path, and extension
resource = m_vendor + '/' + resource + ".sql";
// find the resource from the loader
InputStream in = loader.getResourceAsStream(resource);
if (in == null)
{
LOG.warn("Sql.ddl: missing resource: " + resource);
return;
}
try
{
BufferedReader r = new BufferedReader(new InputStreamReader(in));
try
{
// read the first line, skipping any '--' comment lines
boolean firstLine = true;
StringBuilder buf = new StringBuilder();
for (String line = r.readLine(); line != null; line = r.readLine())
{
line = line.trim();
if (line.startsWith("--")) continue;
if (line.length() == 0) continue;
// add the line to the buffer
buf.append(' ');
buf.append(line);
// process if the line ends with a ';'
boolean process = line.endsWith(";");
if (!process) continue;
// remove trailing ';'
buf.setLength(buf.length() - 1);
// run the first line as the test - if it fails, we are done
if (firstLine)
{
firstLine = false;
if (!dbWriteFailQuiet(null, buf.toString(), null))
{
return;
}
}
// run other lines, until done - any one can fail (we will report it)
else
{
dbWrite(null, buf.toString(), null);
}
// clear the buffer for next
buf.setLength(0);
}
}
catch (IOException any)
{
LOG.warn("Sql.ddl: resource: " + resource + " : " + any);
}
finally
{
try
{
r.close();
}
catch (IOException any)
{
LOG.warn("Sql.ddl: resource: " + resource + " : " + any);
}
}
}
finally
{
try
{
in.close();
}
catch (IOException any)
{
LOG.warn("Sql.ddl: resource: " + resource + " : " + any);
}
}
}
/**
* Prepare a prepared statement with fields.
*
* @param pstmt
* The prepared statement to fill in.
* @param fields
* The Object array of values to fill in.
* @return the next pos that was not filled in.
* @throws UnsupportedEncodingException
*/
protected int prepareStatement(PreparedStatement pstmt, Object[] fields) throws SQLException, UnsupportedEncodingException
{
if (LOG.isDebugEnabled())
{
LOG.debug("prepareStatement(PreparedStatement " + pstmt + ", Object[] " + Arrays.toString(fields) + ")");
}
// put in all the fields
int pos = 1;
if ((fields != null) && (fields.length > 0))
{
for (int i = 0; i < fields.length; i++)
{
if (fields[i] == null || (fields[i] instanceof String && ((String) fields[i]).length() == 0))
{
// treat a Java null as an SQL null,
// and ALSO treat a zero-length Java string as an SQL null
// This makes sure that Oracle vs MySQL use the same value
// for null.
sqlServiceSql.setNull(pstmt, pos);
pos++;
}
else if (fields[i] instanceof Time)
{
Time t = (Time) fields[i];
sqlServiceSql.setTimestamp(pstmt, new Timestamp(t.getTime()), m_cal, pos);
pos++;
}
//KNL-558 an obvious one
else if (fields[i] instanceof java.util.Date)
{
java.util.Date d = (java.util.Date) fields[i];
sqlServiceSql.setTimestamp(pstmt, new Timestamp(d.getTime()), m_cal, pos);
pos++;
}
else if (fields[i] instanceof Long)
{
long l = ((Long) fields[i]).longValue();
pstmt.setLong(pos, l);
pos++;
}
else if (fields[i] instanceof Integer)
{
int n = ((Integer) fields[i]).intValue();
pstmt.setInt(pos, n);
pos++;
}
else if (fields[i] instanceof Float)
{
float f = ((Float) fields[i]).floatValue();
pstmt.setFloat(pos, f);
pos++;
}
else if (fields[i] instanceof Boolean)
{
pstmt.setBoolean(pos, ((Boolean) fields[i]).booleanValue());
pos++;
}
else if ( fields[i] instanceof byte[] )
{
sqlServiceSql.setBytes(pstmt, (byte[])fields[i], pos);
pos++;
}
// %%% support any other types specially?
else
{
String value = fields[i].toString();
sqlServiceSql.setBytes(pstmt, value, pos);
pos++;
}
}
}
return pos;
}
/**
* Append a message about this SQL statement to the DEBUG string in progress, if any
*
* @param str
* The SQL statement.
* @param fields
* The bind fields.
*/
protected void debug(String str, String sql, Object[] fields)
{
// no error will mess us up!
try
{
// StringBuilder buf = (StringBuilder) CurrentService.getInThread("DEBUG");
// if (buf == null) return;
StringBuilder buf = new StringBuilder(2048);
// skip some chatter
// if (str.indexOf("SAKAI_CLUSTER") != -1) return;
// if (str.indexOf("dual") != -1) return;
// buf.append("\n\t");
buf.append(str);
buf.append(" binds: ");
buf.append(debugFields(fields));
buf.append(" sql: ");
buf.append(sql);
LOG.info(buf.toString());
}
catch (Exception ignore)
{
if (LOG.isDebugEnabled())
{
LOG.debug("Ignored Exception: " + ignore.getMessage(), ignore);
}
}
}
protected String debugFields(Object[] fields)
{
StringBuilder buf = new StringBuilder();
if (fields != null)
{
for (int i = 0; i < fields.length; i++)
{
if (fields[i] != null)
{
buf.append(" ");
buf.append(fields[i].toString());
}
else
{
buf.append(" null");
}
}
}
return buf.toString();
}
/**
* <p>
* StreamWithConnection is a cover over a stream that comes from a statement result in a connection, holding all these until closed.
* </p>
*/
public class StreamWithConnection extends InputStream
{
protected Connection m_conn = null;
protected PreparedStatement m_pstmt = null;
protected ResultSet m_result = null;
protected InputStream m_stream;
public StreamWithConnection(InputStream stream, ResultSet result, PreparedStatement pstmt,
Connection conn) {
if (SWC_LOG.isDebugEnabled()) {
SWC_LOG.debug("new StreamWithConnection(InputStream " + stream + ", ResultSet "
+ result + ", PreparedStatement " + pstmt + ", Connection " + conn + ")");
}
m_conn = conn;
m_result = result;
m_pstmt = pstmt;
m_stream = stream;
}
/* (non-Javadoc)
* @see java.io.InputStream#close()
*/
public void close() throws IOException {
if (SWC_LOG.isDebugEnabled()) {
SWC_LOG.debug("close()");
}
try {
if (m_stream != null) {
m_stream.close();
}
m_stream = null;
} catch (Exception e) {
}
try {
if (null != m_result) {
m_result.close();
}
m_result = null;
} catch (Exception e) {
}
try {
if (null != m_pstmt) {
m_pstmt.close();
}
m_pstmt = null;
} catch (Exception e) {
}
if (null != m_conn) {
returnConnection(m_conn);
m_conn = null;
}
}
/* (non-Javadoc)
* @see java.lang.Object#finalize()
*/
protected void finalize() {
if (SWC_LOG.isDebugEnabled()) {
SWC_LOG.debug("finalize()");
}
try {
close();
} catch (IOException any) {
LOG.error(any.getMessage(), any);
}
}
/* (non-Javadoc)
* @see java.io.InputStream#read()
*/
public int read() throws IOException
{
if (SWC_LOG.isDebugEnabled()) {
SWC_LOG.debug("read()");
}
return m_stream.read();
}
/* (non-Javadoc)
* @see java.io.InputStream#read(byte[])
*/
public int read(byte b[]) throws IOException
{
if (SWC_LOG.isDebugEnabled()) {
SWC_LOG.debug("read(byte " + Arrays.toString(b) + ")");
}
return m_stream.read(b);
}
/* (non-Javadoc)
* @see java.io.InputStream#read(byte[], int, int)
*/
public int read(byte b[], int off, int len) throws IOException
{
if (SWC_LOG.isDebugEnabled()) {
SWC_LOG.debug("read(byte " + Arrays.toString(b) + ", int " + off + ", int " + len + ")");
}
return m_stream.read(b, off, len);
}
/* (non-Javadoc)
* @see java.io.InputStream#skip(long)
*/
public long skip(long n) throws IOException
{
if (SWC_LOG.isDebugEnabled()) {
SWC_LOG.debug("skip(long " + n + ")");
}
return m_stream.skip(n);
}
/* (non-Javadoc)
* @see java.io.InputStream#available()
*/
public int available() throws IOException
{
if (SWC_LOG.isDebugEnabled()) {
SWC_LOG.debug("available()");
}
return m_stream.available();
}
/* (non-Javadoc)
* @see java.io.InputStream#mark(int)
*/
public synchronized void mark(int readlimit)
{
if (SWC_LOG.isDebugEnabled()) {
SWC_LOG.debug("mark(int " + readlimit + ")");
}
m_stream.mark(readlimit);
}
/* (non-Javadoc)
* @see java.io.InputStream#reset()
*/
public synchronized void reset() throws IOException
{
if (SWC_LOG.isDebugEnabled()) {
SWC_LOG.debug("reset()");
}
m_stream.reset();
}
/* (non-Javadoc)
* @see java.io.InputStream#markSupported()
*/
public boolean markSupported()
{
if (SWC_LOG.isDebugEnabled()) {
SWC_LOG.debug("markSupported()");
}
return m_stream.markSupported();
}
}
/**
* @param defaultDataSource
* The defaultDataSource to set.
*/
public void setDefaultDataSource(DataSource defaultDataSource)
{
if (LOG.isDebugEnabled())
{
LOG.debug("setDefaultDataSource(DataSource " + defaultDataSource + ")");
}
this.defaultDataSource = defaultDataSource;
}
/**
* @param slowDataSource
* The slowDataSource to set.
*/
public void setLongDataSource(DataSource slowDataSource)
{
if (LOG.isDebugEnabled())
{
LOG.debug("setLongDataSource(DataSource " + slowDataSource + ")");
}
this.longDataSource = slowDataSource;
}
/**
* {@inheritDoc}
*/
public Long getNextSequence(String tableName, Connection conn)
{
String sql = sqlServiceSql.getNextSequenceSql(tableName);
return (sql == null ? null : new Long((String) (dbRead(conn, sql, null, null).get(0))));
}
/**
* {@inheritDoc}
*/
public String getBooleanConstant(boolean value)
{
return sqlServiceSql.getBooleanConstant(value);
}
}