edu.ku.brc.specify.conversion.BasicSQLUtils.java Source code

Java tutorial

Introduction

Here is the source code for edu.ku.brc.specify.conversion.BasicSQLUtils.java

Source

/* Copyright (C) 2015, University of Kansas Center for Research
 * 
 * Specify Software Project, specify@ku.edu, Biodiversity Institute,
 * 1345 Jayhawk Boulevard, Lawrence, Kansas, 66045, USA
 * 
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
*/
package edu.ku.brc.specify.conversion;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
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.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import net.sourceforge.jtds.jdbc.ClobImpl;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.mysql.jdbc.CommunicationsException;
import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;

import edu.ku.brc.af.core.db.DBFieldInfo;
import edu.ku.brc.af.core.db.DBTableIdMgr;
import edu.ku.brc.af.core.db.DBTableInfo;
import edu.ku.brc.dbsupport.DBConnection;
import edu.ku.brc.ui.ProgressFrame;
import edu.ku.brc.ui.UIHelper;

/**
 * A set of basic utilities that used almost exclusively for converting old Database schemas to the new schema
 *
 * @code_status Unknown (auto-generated)
 * 
 * @author rods
 *
 */
public class BasicSQLUtils {
    protected static final Logger log = Logger.getLogger(BasicSQLUtils.class);

    protected static TableWriter tblWriter = null;

    public static enum SERVERTYPE {
        MySQL, MS_SQLServer
    }

    public static SERVERTYPE myDestinationServerType = SERVERTYPE.MySQL;
    public static SERVERTYPE mySourceServerType = SERVERTYPE.MySQL;

    // These are the configuration Options for a View
    public static final int HIDE_ALL_ERRORS = 0; // Hhow no errors (Silent)
    public static final int SHOW_NAME_MAPPING_ERROR = 1; // Show Errors when mapping from Old Name to New Name
    public static final int SHOW_VAL_MAPPING_ERROR = 2; // Show Errors when mapping from Old Name to New Name
    public static final int SHOW_NULL_FK = 4; // Show Error When a Foreign Key is Null and shouldn't be
    public static final int SHOW_FK_LOOKUP = 8; // Show Error when Foreign Key is not null but couldn't be mapped to a new value
    public static final int SHOW_NULL_PM = 16; // Show Error When a Primary Key is Null and shouldn't be
    public static final int SHOW_PM_LOOKUP = 32; // Show Error when Primary Key is not null but couldn't be mapped to a new value
    public static final int SHOW_COPY_TABLE = 64; // Show Errors during copy table
    public static final int SHOW_ALL = SHOW_NAME_MAPPING_ERROR | SHOW_VAL_MAPPING_ERROR | SHOW_FK_LOOKUP
            | SHOW_NULL_FK | SHOW_NULL_PM | SHOW_PM_LOOKUP;

    protected static int showErrors = SHOW_ALL;

    protected static SimpleDateFormat dateTimeFormatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
    protected static SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd");
    protected static Calendar calendar = new GregorianCalendar();
    protected static Timestamp now = new Timestamp(System.currentTimeMillis());
    protected static String nowStr = dateTimeFormatter.format(now);
    protected static int currentYear;

    protected static Map<String, String> ignoreMappingFieldNames = null;
    protected static Map<String, String> ignoreMappingFieldIDs = null;
    protected static Map<String, String> oneToOneIDHash = null;

    // A map used to map a New Column name to an object that can either get or convert the value.
    protected static Hashtable<String, BasicSQLUtilsMapValueIFace> columnValueMapper = new Hashtable<String, BasicSQLUtilsMapValueIFace>();

    protected static Connection dbConn = null; // (it may be shared so don't close)
    protected static ProgressFrame frame = null;
    protected static boolean ignoreMySQLduplicates = true;
    protected static boolean skipTrackExceptions = false;

    protected static PartialDateConv datePair = new PartialDateConv();

    // Missing Mapping File
    protected static PrintWriter missingPW = null;

    static {
        /*try
        {
        missingPW = new PrintWriter("missing.txt");
            
        } catch (Exception ex)
        {
        ex.printStackTrace();
        }*/
        currentYear = calendar.get(Calendar.YEAR);
    }

    /**
     * Singleton
     */
    protected BasicSQLUtils() {

    }

    public static int getShowErrors() {
        return showErrors;
    }

    public static void setShowErrors(final int showErrors) {
        BasicSQLUtils.showErrors = showErrors;
    }

    /**
     * @param skipTrackExceptions the skipTrackExceptions to set
     */
    public static void setSkipTrackExceptions(boolean skipTrackExceptions) {
        BasicSQLUtils.skipTrackExceptions = skipTrackExceptions;
    }

    /**
     * @return the skipTrackExceptions
     */
    public static boolean isSkipTrackExceptions() {
        return skipTrackExceptions;
    }

    /**
     * Sets the SQL connection
     * @param connection the SQL Connection
     */
    public static void setDBConnection(final Connection connection) {
        dbConn = connection;
    }

    /**
     * Sets a UI feedback frame.
     * @param frame the frame
     */
    public static void setFrame(final ProgressFrame frame) {
        BasicSQLUtils.frame = frame;
    }

    /**
     * Sets min to max.
     * @param min min
     * @param max max
     */
    public static void setProcess(final int min, final int max) {
        if (frame != null) {
            frame.setProcess(min, max);
        }
    }

    /**
     * Sets the value.
     * @param value the value
     */
    public static void setProcess(final int value) {
        if (frame != null) {
            frame.setProcess(value);
        }
    }

    public static TableWriter getTblWriter() {
        return tblWriter;
    }

    public static void setTblWriter(TableWriter tblWriter) {
        BasicSQLUtils.tblWriter = tblWriter;
    }

    /**
     * @param oneToOneIDHash the oneToOneIDHash to set
     */
    public static void setOneToOneIDHash(Map<String, String> oneToOneIDHash) {
        BasicSQLUtils.oneToOneIDHash = oneToOneIDHash;
    }

    public static void clearValueMapper() {
        columnValueMapper.clear();
    }

    public static void addToValueMapper(final String newFieldName, final BasicSQLUtilsMapValueIFace mapper) {
        columnValueMapper.put(newFieldName, mapper);
    }

    /**
     * Creates or clears and fills a list
     * @param fieldNames the list of names, can be null then the list is cleared and nulled out
     * @param ignoreMap the map to be be crated or cleared and nulled
     * @return the same map or a newly created one
     */
    protected static Map<String, String> configureIgnoreMap(final String[] fieldNames,
            final Map<String, String> ignoreMap) {
        Map<String, String> ignoreMapLocal = ignoreMap;
        if (fieldNames == null) {
            if (ignoreMapLocal != null) {
                ignoreMapLocal.clear();
                ignoreMapLocal = null;
            }
        } else {
            if (ignoreMapLocal == null) {
                ignoreMapLocal = UIHelper.createMap();
            } else {
                ignoreMapLocal.clear();
            }
            //log.info("Ignore these Field Names when mapping:");
            for (String name : fieldNames) {
                ignoreMapLocal.put(name, "X");
                //log.info(name);
            }
        }
        return ignoreMapLocal;
    }

    /**
     * Sets a list of field names to ignore when mapping database tables from new names to old names
     * @param fieldNames the list of names to ignore
     */
    public static void setFieldsToIgnoreWhenMappingNames(final String[] fieldNames) {
        ignoreMappingFieldNames = configureIgnoreMap(fieldNames, ignoreMappingFieldNames);
    }

    /**
     * @return whether there are any ignore Fields
     */
    public static boolean hasIgnoreFields() {
        return ignoreMappingFieldNames != null;
    }

    /**
     * Sets a list of field names to ignore when mapping IDs
     * @param fieldNames the list of names to ignore
     */
    public static void setFieldsToIgnoreWhenMappingIDs(final String[] fieldNames) {
        ignoreMappingFieldIDs = configureIgnoreMap(fieldNames, ignoreMappingFieldIDs);
    }

    //    /**
    //     * Executes an SQL Update command
    //     * @param stmt Statement object to execute the SQL
    //     * @param cmdStr the SQL string to execute
    //     * @return the return code from the executeUpdate call
    //     */
    //    public static int exeUpdateCmd(Connection conn, String cmdStr)
    //    {
    //        try
    //        {   log.debug("---- exeUpdateCmd (PS)" + cmdStr);
    //            PreparedStatement pstmt = conn.prepareStatement(cmdStr);
    //            
    //            return pstmt.executeUpdate();
    //
    //        } 
    ////        catch (java.sql.SQLException ex)
    ////        {
    ////            //e.printStackTrace();
    ////            ex.getMessage()
    ////            log.error(ex.getStackTrace().toString());
    ////            log.error(cmdStr+"\n");
    ////            ex.printStackTrace();
    ////            throw new RuntimeException(ex);      
    ////        }
    //        catch (Exception ex)
    //        {
    //            //TODO: Problem encountered with the CUPaleo database when converting the AccessionAgent 
    //            //We (Rod?) need to go in an create a hashtable that
    //            if (ex instanceof MySQLIntegrityConstraintViolationException)
    //            {
    //                log.error("ignoring a record because it makes a MySQLIntegrityConstraintViolation: " + ex.getStackTrace().toString() );
    //                return 0;
    //            }
    //            log.error(ex.getStackTrace().toString());
    //            log.error(cmdStr+"\n");
    //            ex.printStackTrace();
    //            throw new RuntimeException(ex);
    //        }
    //        //return -1;
    //    }

    /**
     * Executes an SQL Update command
     * @param stmt Statement object to execute the SQL
     * @param cmdStr the SQL string to execute
     * @return the return code from the executeUpdate call
     */
    public static int exeUpdateCmd(Statement stmt, String cmdStr) {

        try {
            //log.debug("---- exeUpdateCmd" + cmdStr);
            stmt.setEscapeProcessing(true);
            return stmt.executeUpdate(cmdStr);
        } catch (Exception ex) {
            //TODO: Problem encountered with the CUPaleo database when converting the AccessionAgent 
            //We (Rod?) need to go in an create a hashtable that
            if ((ex instanceof MySQLIntegrityConstraintViolationException)
                    && (cmdStr.contains("INSERT INTO accessionagent"))) {
                log.error("ignoring a record because it makes a MySQLIntegrityConstraintViolation: "
                        + ex.getStackTrace().toString());
                log.error(cmdStr + "\n");
                ex.printStackTrace();
                return 0;
            } else if (cmdStr.contains("INSERT INTO accessionagent")) {
                log.error("ignoring a record because it makes a uncatchable SQL Exception: " + ex.getMessage());
                log.error("    " + cmdStr + "\n");
                //ex.printStackTrace();
                return 0;
            } else {
                //e.printStackTrace();
                log.error(ex.getMessage());
                log.error(cmdStr + "\n");
                ex.printStackTrace();
                //ex.getStackTrace().
                throw new RuntimeException(ex);
            }
        }
        //return -1;
    }

    /**
     * Returns the ID of the record that was just inserted.
     * @param stmt the insert statement
     * @return null on error, or the ID
     */
    public static Integer getInsertedId(final Statement stmt) {
        Integer count = null;
        ResultSet resultSet = null;
        try {
            resultSet = stmt.getGeneratedKeys();
            if (resultSet != null && resultSet.next()) {
                count = resultSet.getInt(1);
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (resultSet != null)
                    resultSet.close();
            } catch (SQLException ex) {
            }
        }

        return count;
    }

    /**
     * Executes an SQL Update command
     * @param stmt Statement object to execute the SQL
     * @param cmdStr the SQL string to execute
     * @return the return code from the executeUpdate call
     */
    //    public static int exeUpdateCmd(Statement stmt, String cmdStr)
    //    {
    //        log.debug("exeUpdateCmd" + cmdStr);
    //        if (cmdStr.equals("SET FOREIGN_KEY_CHECKS = 0")
    //                && (myDestinationServerType != myDestinationServerType.MS_SQLServer))
    //        {
    //            try
    //            {
    //                // log.info(cmdStr);
    //                return stmt.executeUpdate(cmdStr);
    //
    //            } catch (Exception ex)
    //            {
    //                // e.printStackTrace();
    //                log.error(ex.getStackTrace().toString());
    //                log.error(cmdStr + "\n");
    //                ex.printStackTrace();
    //                throw new RuntimeException(ex);
    //            }
    //        } else try
    //        {
    //            return removeForeignKeyConstraints(stmt.getConnection());
    //        } catch (SQLException e)
    //        {
    //            // TODO Auto-generated catch block
    //            e.printStackTrace();
    //        }
    //        return -1;
    //    }

    /**
     * Deletes all the records from a table
     * @param tableName the name of the table
     * @param currentServerType server type
     * @return the return value from the SQL update statment (or -1 on an exception)
     */
    public static int deleteAllRecordsFromTable(final String tableName, final SERVERTYPE currentServerType) {
        int count = 0;

        try {
            Connection connection = dbConn != null ? dbConn : DBConnection.getInstance().createConnection();

            count = deleteAllRecordsFromTable(connection, tableName, currentServerType);

            if (dbConn == null) {
                connection.close();
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return count;
    }

    /**
     * @param sql
     * @return
     */
    public static Integer getCount(final String sql) {
        return getCount(dbConn != null ? dbConn : DBConnection.getInstance().getConnection(), sql);
    }

    /**
     * @param sql
     * @return
     */
    public static int getCountAsInt(final String sql) {
        return getCountAsInt(dbConn != null ? dbConn : DBConnection.getInstance().getConnection(), sql);
    }

    /**
     * @param conn
     * @param sql
     * @return
     */
    public static int getCountAsInt(final Connection conn, final String sql) {
        Integer cnt = getCount(conn, sql);
        return cnt == null ? 0 : cnt;
    }

    /**
     * @param sql
     * @return
     */
    public static Integer getCount(final Connection connection, final String sql) {
        Integer count = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = connection.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                count = rs.getInt(1);
                return rs.wasNull() ? null : count;
            }

        } catch (Exception ex) {
            ex.printStackTrace();

        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();

            } catch (Exception ex) {
            }
        }

        return count;
    }

    /**
     * @param sql
     * @return
     */
    public static String getString(final String sql) {
        return getString(dbConn != null ? dbConn : DBConnection.getInstance().getConnection(), sql);
    }

    /**
     * @param sql
     * @return
     */
    public static String getString(final Connection connection, final String sql) {
        String str = null;
        Statement stmt = null;
        try {
            stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next()) {
                str = rs.getString(1);
            }
            rs.close();
        } catch (Exception ex) {
            ex.printStackTrace();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();

                } catch (Exception ex) {
                }
            }
        }

        return str;
    }

    /**
     * @param sql
     * @return
     */
    public static Object[] getRow(final String sql) {
        return getRow(dbConn != null ? dbConn : DBConnection.getInstance().getConnection(), sql);
    }

    /**
     * @param sql
     * @return
     */
    public static Object[] getRow(final Connection connection, final String sql) {
        Object[] row = null;
        Statement stmt = null;
        try {
            stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next()) {
                row = new Object[rs.getMetaData().getColumnCount()];

                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    row[i - 1] = rs.getObject(i);
                }
            }
            rs.close();

        } catch (Exception ex) {
            ex.printStackTrace();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();

                } catch (Exception ex) {
                }
            }
        }

        return row;
    }

    /**
     * @param sql
     * @return
     */
    public static Object[] queryForRow(final String sql) {
        return queryForRow(dbConn != null ? dbConn : DBConnection.getInstance().getConnection(), sql);
    }

    /**
     * @param sql
     * @return
     */
    public static Object[] queryForRow(final Connection connection, final String sql) {
        Object[] row = null;
        Statement stmt = null;
        try {
            stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next()) {
                row = new Object[rs.getMetaData().getColumnCount()];

                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    row[i - 1] = rs.getObject(i);
                }
            }
            rs.close();

        } catch (Exception ex) {
            ex.printStackTrace();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();

                } catch (Exception ex) {
                }
            }
        }

        return row;
    }

    /**
     * @param sql
     * @return
     */
    public static Vector<Object[]> query(final String sql) {
        return query(null, sql, false);
    }

    /**
     * @param sql
     * @return
     */
    public static Vector<Object[]> query(final Connection conn, final String sql) {
        return query(conn, sql, false);
    }

    /**
     * @param conn
     * @param sql
     * @param includeHeaderRow
     * @return
     */
    public static Vector<Object[]> query(final Connection conn, final String sql, final boolean includeHeaderRow) {
        Vector<Object[]> list = new Vector<Object[]>();
        Statement stmt = null;

        Connection connection = null;
        boolean doCloseConn = false;
        boolean doSkipConnSet = false;
        boolean isStale = true;
        int tries = 0;

        while (isStale && tries < 3) {
            try {
                if (!doSkipConnSet) {
                    if (conn != null) {
                        connection = conn;

                    } else if (dbConn != null) {
                        connection = dbConn;
                    } else {
                        connection = DBConnection.getInstance().createConnection();
                        doCloseConn = true;
                    }
                }

                if (connection == null) {
                    return list;
                }

                tries++;
                stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery(sql);
                ResultSetMetaData metaData = rs.getMetaData();
                int numCols = metaData.getColumnCount();
                if (includeHeaderRow) {
                    Object[] colData = new Object[numCols];
                    list.add(colData);
                    for (int i = 0; i < numCols; i++) {
                        colData[i] = metaData.getColumnName(i + 1);
                    }
                }
                while (rs.next()) {
                    Object[] colData = new Object[numCols];
                    list.add(colData);
                    for (int i = 0; i < numCols; i++) {
                        colData[i] = rs.getObject(i + 1);
                    }
                }
                rs.close();

                isStale = false;

            } catch (CommunicationsException ex) {
                connection = DBConnection.getInstance().createConnection();
                doCloseConn = true;
                doSkipConnSet = true;

            } catch (com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException e) {
                e.printStackTrace();
                if (!skipTrackExceptions) {
                    edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
                    edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, e);
                }

            } catch (SQLException ex) {
                ex.printStackTrace();

                if (!skipTrackExceptions) {
                    edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
                    edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
                }

            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (Exception ex) {
                    }
                }
            }

            if (!isStale && connection != null && doCloseConn) {
                try {
                    connection.close();
                } catch (Exception ex) {
                }
            }
        }

        return list;
    }

    /**
     * @param sql
     * @return
     */
    public static Vector<Object> querySingleCol(final String sql) {
        return querySingleCol(null, sql);
    }

    /**
     * @param conn 
     * @param sql
     * @return
     */
    public static Vector<Object> querySingleCol(final Connection conn, final String sql) {
        Vector<Object> list = new Vector<Object>();
        Statement stmt = null;

        Connection connection = null;
        boolean doCloseConn = false;
        boolean doSkipConnSet = false;
        boolean isStale = true;
        int tries = 0;

        while (isStale && tries < 3) {
            try {
                if (!doSkipConnSet) {
                    if (conn != null) {
                        connection = conn;

                    } else if (dbConn != null) {
                        connection = dbConn;
                    } else {
                        connection = DBConnection.getInstance().createConnection();
                        doCloseConn = true;
                    }
                }

                tries++;
                if (connection != null) {
                    stmt = connection.createStatement();
                    ResultSet rs = stmt.executeQuery(sql);
                    ResultSetMetaData metaData = rs.getMetaData();
                    int numCols = metaData.getColumnCount();

                    if (numCols > 1) {
                        log.warn("Query has " + numCols + " columns and should only have one.");
                    }

                    while (rs.next()) {
                        list.add(rs.getObject(1));
                    }
                    rs.close();

                    isStale = false;
                } else {
                    isStale = true;
                }

            } catch (CommunicationsException ex) {
                connection = DBConnection.getInstance().createConnection();
                doCloseConn = true;
                doSkipConnSet = true;

            } catch (SQLException ex) {
                ex.printStackTrace();

                if (!skipTrackExceptions) {
                    edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
                    edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
                }

            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (Exception ex) {
                    }
                }
            }

            if (!isStale && connection != null && doCloseConn) {
                try {
                    connection.close();
                } catch (Exception ex) {
                }
            }
        }

        return list;
    }

    /**
     * @param sql
     * @return
     */
    public static <T> T querySingleObj(final String sql) {
        return querySingleObj(null, sql);
    }

    /**
     * @param conn 
     * @param sql
     * @return
     */
    @SuppressWarnings("unchecked")
    public static <T> T querySingleObj(final Connection conn, final String sql) {
        Vector<Object> list = querySingleCol(conn, sql);
        if (list.size() > 1) {
            log.warn("The query [" + sql + "] returned more than one object.");
        }
        return list.size() > 0 ? (T) list.get(0) : null;
    }

    /**
     * @param sql
     * @return
     */
    public static Vector<Integer> queryForInts(final String sql) {
        return queryForInts(null, sql);
    }

    /**
     * @param conn 
     * @param sql
     * @return
     */
    public static Vector<Integer> queryForInts(final Connection conn, final String sql) {
        Vector<Integer> list = new Vector<Integer>();
        Statement stmt = null;

        Connection connection = null;
        boolean doCloseConn = false;
        boolean doSkipConnSet = false;
        boolean isStale = true;
        int tries = 0;

        while (isStale && tries < 3) {
            try {
                if (!doSkipConnSet) {
                    if (conn != null) {
                        connection = conn;

                    } else if (dbConn != null) {
                        connection = dbConn;
                    } else {
                        connection = DBConnection.getInstance().createConnection();
                        doCloseConn = true;
                    }
                }

                tries++;
                stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery(sql);
                ResultSetMetaData metaData = rs.getMetaData();
                int numCols = metaData.getColumnCount();

                if (numCols > 1) {
                    log.warn("Query has " + numCols + " columns and should only have one.");
                }

                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
                rs.close();

                isStale = false;

            } catch (CommunicationsException ex) {
                connection = DBConnection.getInstance().createConnection();
                doCloseConn = true;
                doSkipConnSet = true;

            } catch (SQLException ex) {
                ex.printStackTrace();

                if (!skipTrackExceptions) {
                    edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
                    edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
                }

            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (Exception ex) {
                    }
                }
            }

            if (!isStale && connection != null && doCloseConn) {
                try {
                    connection.close();
                } catch (Exception ex) {
                }
            }
        }

        return list;
    }

    /**
     * @param sql
     * @return
     */
    public static int update(final String sql) {
        return update(null, sql);
    }

    /**
     * @param conn
     * @param sql
     * @return
     */
    public static int update(final Connection conn, final String sql) {
        Statement stmt = null;
        try {
            Connection connection = conn != null ? conn
                    : (dbConn != null ? dbConn : DBConnection.getInstance().getConnection());

            stmt = connection.createStatement();
            return stmt.executeUpdate(sql);

        } catch (SQLException ex) {

            if (!skipTrackExceptions) {
                ex.printStackTrace();
                edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
                edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
            }

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception ex) {
                }
            }
        }

        return -1;
    }

    /**
     * Deletes all the records from a table
     * @param connection connection to the DB
     * @param tableName the name of the table
     * @return the return value from the SQL update statement (or -1 on an exception)
     */
    public static int deleteAllRecordsFromTable(final Connection connection, final String tableName,
            final SERVERTYPE currentServerType) {
        try {
            if (doesTableExist(connection, tableName)) {
                Integer count = getCount(connection, "SELECT COUNT(*) FROM " + tableName);
                if (count == null || count == 0) {
                    return 0;
                }

                Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);
                if (currentServerType != SERVERTYPE.MS_SQLServer) {
                    removeForeignKeyConstraints(stmt.getConnection(), currentServerType);
                }
                int retVal = exeUpdateCmd(stmt, "delete from " + tableName);
                stmt.clearBatch();
                stmt.close();

                log.info("Deleted " + count + " records from " + tableName);

                return retVal;

            }
        } catch (SQLException ex) {
            edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
            log.error(ex);
            ex.printStackTrace();

        }
        return 0;
    }

    /**
     * @param connection
     * @param tableName
     * @return
     */
    public static boolean doesTableExist(final Connection connection, final String tableName) {
        try {
            DatabaseMetaData mdm = connection.getMetaData();
            ResultSet rs = mdm.getColumns(connection.getCatalog(), connection.getCatalog(), tableName, null);
            if (rs.next()) {
                rs.close();
                return true;
            }
            rs.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }

    /**
     * Removes all the records from all the tables from the current DBConnection
     */
    public static void cleanAllTables(SERVERTYPE currentServerType) {
        try {
            Connection connection = dbConn != null ? dbConn : DBConnection.getInstance().createConnection();

            cleanAllTables(connection, currentServerType);

            if (dbConn == null) {
                connection.close();
            }

        } catch (SQLException ex) {
            edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
            //e.printStackTrace();
            log.error(ex);
        }
    }

    public static String getDatabaseName(final Connection connection) {
        // List<String> names = new Vector<String>();
        String databaseName = null;
        try {
            //Statement  stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

            //ResultSet rs1 = stmt.executeQuery("select name from sysfiles");

            //if (rs1.first())
            //{
            databaseName = connection.getCatalog();
            //log.debug("GETTING db NAME: " + databaseName);
            return databaseName;
            //}

        } catch (SQLException ex) {
            edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
            log.error(ex);
            ex.printStackTrace();
        }
        return databaseName;
    }

    /**
     * Removes all the records from all the tables
     */
    public static void cleanAllTables(final Connection connection, SERVERTYPE currentServerType) {
        try {
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            ResultSet rs = stmt.executeQuery("show tables");
            if (rs.first()) {
                do {
                    String tableName = rs.getString(1);
                    //System.out.println("Deleting Records from "+tableName);
                    deleteAllRecordsFromTable(connection, tableName, currentServerType);
                } while (rs.next());
            }
            rs.close();

            stmt.clearBatch();
            stmt.close();

        } catch (SQLException ex) {
            edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
            log.error(ex);
            ex.printStackTrace();
        }
    }

    /**
     * Returns a valid String value for an Object, meaning it will put quotes around Strings and ate etc.
     * @param obj the object to convert
     * @return the string representation
     */
    public static String getStrValue(final Object obj) {
        return getStrValue(obj, null);
    }

    /**
     * Returns a valid String value for an Boolean, meaning convert true to 1 and false to 0
     * @param Boolean value to convert
     * @return the string representation
     */
    public static String getStrValue(final Boolean val) {
        return Integer.toString(val == false ? 0 : 1);
        //return getStrValue(obj, null);
    }

    /**
     * Escaped and delimited string for use in SQL, 
     * using appropriate delimiter for DestinationServerType
     * @param str
     * @return escaped and delimited string for use in SQL, 
     * using appropriate delimiter for DestinationServerType
     */
    public static String getEscapedSQLStrExpr(final String str) {
        if (str != null) {
            String delimiter = "'";
            /*if (myDestinationServerType == SERVERTYPE.MS_SQLServer
             || myDestinationServerType == SERVERTYPE.MySQL)
            {
               //possibly for other dbms some other encloser would be required
               log.info("setting string delimiter to \"'\" for ServerType " + myDestinationServerType);
            }*/
            return delimiter + escapeStringLiterals(str, delimiter) + delimiter;
        }
        return null;
    }

    /**
     * Escapes the single quote so it can be part of the data without causing an exception.
     * @param str the string to be escaped
     * @return escaped version of str 
     * 
     * Delimiter is assumed to be "'"
     */
    public static String escapeStringLiterals(String str) {
        //" can't be used to enclose strings in where clauses for SQLServer and postgres
        //' works for MySQL, SQLServer, and postgres
        return escapeStringLiterals(str, "'");
    }

    // MEG NEEDS TO FIX THIS!!!!!!! IT IS NOT CORRECT, BUT I WANTED TO MOVE ON
    /**
     * @param str string to escape
     * @param enclosingChar character used to delimit the string
     * @return string with bad characters escaped 
     */
    public static String escapeStringLiterals(String str, String enclosingChar) {
        //        if (s.indexOf("\r\n")>= 0)
        //                {
        //            log.error("slash r slash n: newline encountered");
        //                }
        //
        //        for(int i = 0; i < s.length(); i++)
        //        {
        //            char c = s.charAt(i);
        //            //Character c1 = (Character)c;
        //            if (Character.isWhitespace(c))
        //            {
        //                log.error("Character is whitespace");
        //            }
        //            
        //            log.error("Char: " + c );
        //            log.error("Unicode: " + Character.getNumericValue(c));
        //        }
        //        if (s.indexOf("\r")>= 0)
        //        {
        //            log.error("return encountered");
        //        }
        //                
        // log.debug("escaping string literal:" + s);
        if (str != null) {
            String s = str;
            if (s.indexOf("\\") >= 0) {
                // s = s.replaceAll("\\","\\\\\\");
                // s = s.replaceAll("\\", "\\\\");
                if (myDestinationServerType != SERVERTYPE.MS_SQLServer) {
                    s = s.replaceAll("\\\\", "\\\\\\\\");
                    //log.debug("escaping !M$ backslash:" + s);
                }
                // s = StringEscapeUtils.escapeJava(s);
                // log.debug("backslash:" + s);
            }
            if (enclosingChar.equals("\"") && s.indexOf("\"") >= 0) {
                s = s.replaceAll("\"", "\\\"\"");
                // s = s.replaceAll("\"","\\\"");
                //log.debug("escaped double quotes:" + s);
            }
            if (enclosingChar.equals("\'") && s.indexOf("\'") >= 0) {
                //if (myDestinationServerType == SERVERTYPE.MS_SQLServer)
                {
                    s = s.replaceAll("'", "''");
                }
                // s = s.replaceAll("\'","\\\'\'");
                // s = s.replaceAll("\'","\\\'");
                // log.debug("single quotes:" + s);
            }
            return s;
        }
        return null;
    }

    /**
     * @param obj
     * @return
     */
    public static java.sql.Date getDateObj(final Object obj) {
        if (obj == null) {
            return null;
        }

        if (obj instanceof Integer) {
            getPartialDate(obj, datePair);

            if (!datePair.isNull()) {
                try {
                    Date d = dateFormatter.parse(datePair.getDateStr());
                    if (d != null) {
                        return new java.sql.Date(d.getTime());
                    }
                    return null;

                } catch (ParseException e) {
                    e.printStackTrace();
                }
            }
            return null;

        } else if (obj instanceof Date) {
            return new java.sql.Date(((Date) obj).getTime());
        }
        return null;
    }

    /**
     * Returns a valid String value for an Object, meaning it will put quotes around Strings and ate
     * etc.
     * @param obj the object to convert
     * @return the string representation
     */
    public static String getStrValue(final Object obj, final String newFieldType) {
        if (obj == null) {
            return "NULL";

        } else if (obj instanceof net.sourceforge.jtds.jdbc.ClobImpl) {
            //log.debug("instance of Clob");
            String str = "";
            ClobImpl clob = (ClobImpl) obj;
            //log.debug("tyring to get clob");
            try {
                str = clob.getSubString(1, (int) clob.length());
                //                str = escapeStringLiterals(str);
                //                return '"'+str+'"';
                return getEscapedSQLStrExpr(str);
            } catch (SQLException ex) {
                edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
                edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
                log.error("error occurred trying to get string from clob for SQL Server driver");
                // TODO Auto-generated catch block
                ex.printStackTrace();
                System.exit(0);
            }
            //return getStrValue(obj, null);    
            return obj.toString();
        } else if (obj instanceof String) {
            String str = (String) obj;
            //            if (str.indexOf('"') > -1 || str.indexOf('\\') > -1 || str.indexOf('\'') > -1)
            //            {
            //                str = escapeStringLiterals(str);
            //            }
            //            return '"'+str+'"';
            return getEscapedSQLStrExpr(str);
            //            String str = (String)obj;
            //            if (str.indexOf('"') > -1 || str.indexOf('\\') > -1)
            //            {
            //                log.debug("ran into char worth escaping" + str);
            //                str = StringEscapeUtils.escapeSql(str);
            //                log.debug("ran into char worth escaping (escaped for SQL)" + str);
            //                //str = StringEscapeUtils.escapeJava(str);
            //                //log.debug("ran into char worth escaping (escaped for java)" + str);
            //            }
            //            return '"'+str+'"';

        } else if (obj instanceof Integer) {
            if (newFieldType != null) {
                if (newFieldType.toLowerCase().indexOf("date") == 0) {
                    getPartialDate(obj, datePair);
                    return datePair.getDateStr();

                }
                //Meg dropped the (1) from the newFieldType check, field metadata didn't include the (1) values
                else if (newFieldType.equalsIgnoreCase("bit") || newFieldType.equalsIgnoreCase("tinyint")
                        || newFieldType.equalsIgnoreCase("boolean"))
                //else if (newFieldType.equalsIgnoreCase("bit(1)") || newFieldType.equalsIgnoreCase("tinyint(1)"))
                {
                    int val = ((Integer) obj).intValue();
                    return Integer.toString(val == 0 ? 0 : 1);
                }
                ////Meg dropped the (1) from the newFieldType check, field metadata didn't include the (1) values
                //else if (newFieldType.equalsIgnoreCase("bit") || newFieldType.equalsIgnoreCase("tinyint"))
                else if (newFieldType.equalsIgnoreCase("bit(1)") || newFieldType.equalsIgnoreCase("tinyint(1)")) {
                    int val = ((Integer) obj).intValue();
                    return Integer.toString(val == 0 ? 0 : 1);
                }
                return ((Integer) obj).toString();
            }
            return ((Integer) obj).toString();

        } else if (obj instanceof Date) {
            return '"' + dateTimeFormatter.format((Date) obj) + '"';

        } else if (obj instanceof Float) {
            return ((Float) obj).toString();

        } else if (obj instanceof Double) {
            return ((Double) obj).toString();

        } else if (obj instanceof Character) {
            return '"' + ((Character) obj).toString() + '"';
        } else {
            //log.debug("not sure the type");
            return obj.toString();
        }
    }
    //    /**
    //     * Returns a valid String value for an Object, meaning it will put quotes around Strings and ate etc.
    //     * @param obj the object to convert
    //     * @return the string representation
    //     */
    //    public static String getStrValue2(final Object obj, final String newFieldType)
    //    {
    //        if (obj == null)
    //        {
    //            return "NULL";
    //
    //        } else if (obj instanceof String)
    //        {
    //            String str = (String)obj;
    //            if (str.indexOf('"') > -1 || str.indexOf('\\') > -1 || str.indexOf('\'') > -1 )
    //            {
    //                str = StringEscapeUtils.escapeJava(str);
    //            }
    //            return '\''+str+'\'';
    //
    //        } else if (obj instanceof Integer)
    //        {
    //            if (newFieldType != null)
    //            {
    //                if (newFieldType.indexOf("date") ==  0)
    //                {
    //                    Date dateObj = UIHelper.convertIntToDate((Integer)obj);                   
    //                    return dateObj == null ? "NULL" : '\''+dateFormatter.format(dateObj) + '\'';
    //
    //                }
    //                else if (newFieldType.equalsIgnoreCase("bit(1)") || newFieldType.equalsIgnoreCase("tinyint(1)"))
    //                {
    //                    int val = ((Integer)obj).intValue();
    //                    return Integer.toString(val == 0? 0 : 1);
    //                }
    //                return ((Integer)obj).toString();
    //            }
    //            return ((Integer)obj).toString();
    //
    //        } else if (obj instanceof Date)
    //        {
    //            return '\''+dateTimeFormatter.format((Date)obj) + '\'';
    //
    //        } else if (obj instanceof Float)
    //        {
    //            return ((Float)obj).toString();
    //
    //        } else if (obj instanceof Double)
    //        {
    //            return ((Double)obj).toString();
    //
    //        } else if (obj instanceof Character)
    //        {
    //            return '\''+((Character)obj).toString()+'\'';
    //        } else
    //        {
    //            return obj.toString();
    //        }
    //    }

    public static List<String> getFieldNamesFromSchema(final Connection connection, final String tableName) {
        try {
            ArrayList<String> fields = new ArrayList<String>();

            DatabaseMetaData mdm = connection.getMetaData();
            ResultSet rs = mdm.getColumns(connection.getCatalog(), connection.getCatalog(), tableName, null);
            while (rs.next()) {
                fields.add(rs.getString("COLUMN_NAME"));
            }
            rs.close();
            return fields;

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public static List<String> getFieldNamesFromSchema(final Connection connection, final String tableName,
            final List<String> fields) {
        try {
            DatabaseMetaData mdm = connection.getMetaData();
            ResultSet rs = mdm.getColumns(connection.getCatalog(), connection.getCatalog(), tableName, null);
            while (rs.next()) {
                fields.add(rs.getString("COLUMN_NAME"));
            }
            rs.close();
            return fields;

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    /**
     * @param connection
     * @return
     */
    public static List<String> getTableNames(final Connection connection) {
        try {
            ArrayList<String> fields = new ArrayList<String>();

            DatabaseMetaData mdm = connection.getMetaData();
            ResultSet rs = mdm.getTables(connection.getCatalog(), connection.getCatalog(), null,
                    new String[] { "TABLE" });
            while (rs.next()) {
                /*System.out.println("-------- " + rs.getString("TABLE_NAME")+" ----------");
                for (int i=1;i<=rs.getMetaData().getColumnCount();i++)
                {
                System.out.println(rs.getMetaData().getColumnName(i)+"="+rs.getObject(i));
                    
                }*/
                fields.add(rs.getString("TABLE_NAME"));
            }
            rs.close();
            return fields;

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    /**
     * @param connection
     * @param tableName
     * @return
     */
    public static List<FieldMetaData> getFieldMetaDataFromSchema(final Connection connection,
            final String tableName) {
        try {
            ArrayList<FieldMetaData> fields = new ArrayList<FieldMetaData>();

            DatabaseMetaData mdm = connection.getMetaData();
            ResultSet rs = mdm.getColumns(connection.getCatalog(), connection.getCatalog(), tableName, null);
            while (rs.next()) {
                /*System.out.println("-------- " + rs.getString("COLUMN_NAME")+" ----------");
                for (int i=1;i<=rs.getMetaData().getColumnCount();i++)
                {
                System.out.println(rs.getMetaData().getColumnName(i)+"="+rs.getObject(i));
                    
                }*/

                String typeStr = rs.getString("TYPE_NAME");
                FieldMetaData fmd = new FieldMetaData(rs.getString("COLUMN_NAME"), typeStr,
                        typeStr.startsWith("DATE"), false, StringUtils.contains(typeStr.toLowerCase(), "varchar"));
                fmd.setSqlType(rs.getInt("DATA_TYPE"));
                fields.add(fmd);
            }
            rs.close();
            return fields;

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    /**
     * @param pStmt
     * @param type
     * @param data
     * @throws SQLException 
     */
    public static void setData(final PreparedStatement pStmt, final int type, final int colInx, final Object data)
            throws SQLException {
        if (data == null) {
            pStmt.setObject(colInx, null);
            return;
        }

        boolean isStr = data instanceof String;
        switch (type) {
        case java.sql.Types.TINYINT:
        case java.sql.Types.SMALLINT:
        case java.sql.Types.INTEGER:
            if (isStr) {
                pStmt.setString(colInx, (String) data);
            } else {
                pStmt.setInt(colInx, (Integer) data);
            }
            break;

        case java.sql.Types.FLOAT:
            if (isStr) {
                pStmt.setString(colInx, (String) data);
            } else {
                pStmt.setFloat(colInx, (Float) data);
            }
            break;

        case java.sql.Types.VARCHAR:
        case java.sql.Types.CHAR:
        case java.sql.Types.LONGVARCHAR:
        case java.sql.Types.LONGNVARCHAR:
        case java.sql.Types.NCHAR:
            if (isStr) {
                pStmt.setString(colInx, (String) data);
            } else {
                pStmt.setString(colInx, (String) data);
            }
            break;

        case java.sql.Types.REAL:
        case java.sql.Types.DOUBLE:
            if (isStr) {
                pStmt.setString(colInx, (String) data);
            } else {
                pStmt.setDouble(colInx, (Double) data);
            }
            break;

        case java.sql.Types.DATE:
            if (isStr) {
                pStmt.setString(colInx, (String) data);
            } else {
                pStmt.setDate(colInx, (java.sql.Date) data);
            }
            break;

        case java.sql.Types.TIMESTAMP:
            if (isStr) {
                pStmt.setString(colInx, (String) data);
            } else {
                pStmt.setTimestamp(colInx, (Timestamp) data);
            }
            break;

        case java.sql.Types.BOOLEAN:
            if (isStr) {
                String val = (String) data;
                pStmt.setBoolean(colInx, !val.equalsIgnoreCase("true"));
            } else {
                pStmt.setBoolean(colInx, (Boolean) data);
            }
            break;

        case java.sql.Types.BIT:
            if (data instanceof Boolean) {
                pStmt.setBoolean(colInx, (Boolean) data);
            } else {
                pStmt.setBoolean(colInx, !(((Integer) data) == 0));
            }
            break;

        default:
            throw new RuntimeException(String.format("Missing case for SQL Type %d for Column: %d Data[%s]", type,
                    colInx, data.getClass().getSimpleName()));
        }
    }

    /**
     * @param connection
     * @param tableName
     * @return
     */
    public static Map<String, FieldMetaData> getFieldMetaDataFromSchemaHash(final Connection connection,
            final String tableName) {
        Map<String, FieldMetaData> fieldMetaDataMap = new Hashtable<String, FieldMetaData>();
        for (FieldMetaData fmd : getFieldMetaDataFromSchema(connection, tableName)) {
            fieldMetaDataMap.put(fmd.getName(), fmd);
        }
        return fieldMetaDataMap;
    }

    /**
     * Converts an integer time in the form of YYYYMMDD to the proper Date
     * @param iDate the int to be converted
     * @return the date object
     * @return the verbatimDate strin that holds old invalid verbatim dates
     */
    public static Date convertIntToDate(final int iDate, final StringBuilder verbatimDate) {
        calendar.clear();

        int year = iDate / 20000;
        if (year > 1600) {
            int tmp = (iDate - (year * 20000));
            int month = tmp / 100;
            int day = (tmp - (month * 100));

            if (month == 0 || day == 0) {
                verbatimDate.setLength(0);
                verbatimDate.append(Integer.toString(iDate));
                if (month == 0) {
                    month = 7;
                }
                if (day == 0) {
                    day = 1;
                }
            }

            calendar.set(year, month - 1, day);
        } else {
            calendar.setTimeInMillis(0);
        }

        return calendar.getTime();
    }

    /**
     * @param msg
     */
    protected static void writeErrLog(final String msg) {
        if (tblWriter != null) {
            tblWriter.logError(msg);
            tblWriter.flush();

        } else if (missingPW != null) {
            missingPW.println(msg);
            missingPW.flush();
        }
    }

    /**
     * Copies a table from one DB to another
     */
    /**
     * Copies a a table with the same name from one DB to another
     * @param fromConn the "from" DB
     * @param toConn the "to" DB
     * @param tableName the table name to be copied
     * @param colNewToOldMap a map of new file names toold file names
     * @param verbatimDateMapper a map from the new Vertbatim Date Field to the new date column name it is associated with
     * @return true if successful
     */
    public static boolean copyTable(final Connection fromConn, final Connection toConn, final String tableName,
            final Map<String, String> colNewToOldMap, final Map<String, String> verbatimDateMapper,
            final SERVERTYPE sourceServerType, final SERVERTYPE destServerType) {
        return copyTable(fromConn, toConn, "select * from " + tableName, tableName, tableName, colNewToOldMap,
                verbatimDateMapper, sourceServerType, destServerType);
    }

    /**
     * Copies a table to a new table of a different name (same schema) within the same DB Connection
     * @param conn a connection to copy from one table to another in the same database
     * @param fromTableName the table name its coming from
     * @param toTableName the table name it is going to
     * @param colNewToOldMap a map of new file names toold file names
     * @param verbatimDateMapper a map from the new Vertbatim Date Field to the new date column name it is associated with
     * @return true if successful
     */
    public static boolean copyTable(final Connection fromConn, final Connection toConn, final String fromTableName,
            final String toTableName, final Map<String, String> colNewToOldMap,
            final Map<String, String> verbatimDateMapper, final SERVERTYPE sourceServerType,
            final SERVERTYPE destServerType) {
        return copyTable(fromConn, toConn, "select * from " + fromTableName, fromTableName, toTableName,
                colNewToOldMap, verbatimDateMapper, sourceServerType, destServerType);
    }

    /**
     * Copies a table to a new table of a different name (same schema) within the same DB Connection
     * @param conn a connection to copy from one table to another in the same database
     * @param fromTableName the table name its coming from
     * @param toTableName the table name it is going to
     * @param colNewToOldMap a map of new file names toold file names
     * @param verbatimDateMapper a map from the new Vertbatim Date Field to the new date column name it is associated with
     * @return true if successful
     */
    public static boolean copyTable(final Connection conn, final String fromTableName, final String toTableName,
            final Map<String, String> colNewToOldMap, final Map<String, String> verbatimDateMapper,
            final SERVERTYPE sourceServerType, final SERVERTYPE destServerType) {
        return copyTable(conn, conn, "select * from " + fromTableName, fromTableName, toTableName, colNewToOldMap,
                verbatimDateMapper, sourceServerType, destServerType);
    }

    /**
     * Copies from one connection/table to another connection/table. Sets the order by clause to be the first field in the
     * "from" field list.
     *
     * @param fromConn DB Connection that the data is coming from
     * @param toConn Connection that the data is going to
     * @param sql the SQL to be executed
     * @param fromTableName the table name its coming from
     * @param toTableName the table name it is going to
     * @param colNewToOldMap a map of new file names to old file names
     * @param verbatimDateMapper a map from the new Vertbatim Date Field to the new date column name it is associated with
     * @return true if successful
     */
    public static boolean copyTable(final Connection fromConn, final Connection toConn, final String sql,
            final String fromTableName, final String toTableName, final Map<String, String> colNewToOldMap,
            final Map<String, String> verbatimDateMapper, final SERVERTYPE sourceServerType,
            final SERVERTYPE destServerType) {
        String sqlStr = sql == null ? "SELECT * FROM " + fromTableName : sql;

        return copyTable(fromConn, toConn, sqlStr, fromTableName, toTableName, colNewToOldMap, verbatimDateMapper,
                null, sourceServerType, destServerType);
    }

    /**
     * @param fromConn
     * @param toConn
     * @param sql
     * @param fromTableName
     * @param toTableName
     * @param colNewToOldMap
     * @param verbatimDateMapper
     * @param newColDefValues
     * @param sourceServerType
     * @param destServerType
     * @return
     */
    public static boolean copyTable(final Connection fromConn, final Connection toConn, final String sql,
            final String fromTableName, final String toTableName, final Map<String, String> colNewToOldMap,
            final Map<String, String> verbatimDateMapper, final Map<String, String> newColDefValues,
            final SERVERTYPE sourceServerType, final SERVERTYPE destServerType) {
        return copyTable(fromConn, toConn, sql, null, fromTableName, toTableName, colNewToOldMap,
                verbatimDateMapper, newColDefValues, sourceServerType, destServerType);
    }

    /**
     * @param fromConn
     * @param toConn
     * @param sql
     * @param fromTableName
     * @param toTableName
     * @param colNewToOldMap
     * @param verbatimDateMapper
     * @param newColDefValues
     * @param sourceServerType
     * @param destServerType
     * @return
     */
    public static boolean copyTable(final Connection fromConn, final Connection toConn, final String sql,
            final String countSQL, final String fromTableName, final String toTableName,
            final Map<String, String> colNewToOldMap, final Map<String, String> verbatimDateMapper,
            final Map<String, String> newColDefValues, final SERVERTYPE sourceServerType,
            final SERVERTYPE destServerType) {
        //Timestamp now = new Timestamp(System.currentTimeMillis());

        IdMapperMgr idMapperMgr = IdMapperMgr.getInstance();

        if (frame != null) {
            frame.setDesc("Copying Table " + fromTableName);
        }
        log.info("Copying Table " + fromTableName);

        List<String> fromFieldNameList = getFieldNamesFromSchema(fromConn, fromTableName);

        String sqlStr = sql + " ORDER BY " + fromTableName + "." + fromFieldNameList.get(0);
        log.debug(sqlStr);

        int numRecs;
        if (countSQL == null) {
            numRecs = getNumRecords(fromConn, fromTableName);
        } else {
            numRecs = getCountAsInt(fromConn, countSQL);
        }
        setProcess(0, numRecs);

        DBTableInfo tblInfo = DBTableIdMgr.getInstance().getInfoByTableName(toTableName);
        Statement updateStatement = null;
        String id = "";
        try {

            updateStatement = toConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) {
                BasicSQLUtils.removeForeignKeyConstraints(toConn, BasicSQLUtils.myDestinationServerType);

            }

            //HashMap<String, Integer> newDBFieldHash   = new HashMap<String, Integer>();
            List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(toConn, toTableName);
            //int inx = 1;
            //for (FieldMetaData fmd : newFieldMetaData)
            //{
            //    newDBFieldHash.put(fmd.getName(), inx++);
            //}

            Statement stmt = fromConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            //System.out.println(sqlStr);
            ResultSet rs = stmt.executeQuery(sqlStr);
            ResultSetMetaData rsmd = rs.getMetaData();

            Vector<Integer> dateColumns = new Vector<Integer>();

            //System.out.println(toTableName);
            Hashtable<String, Integer> fromHash = new Hashtable<String, Integer>();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                String colName = rsmd.getColumnName(i);

                fromHash.put(colName, i);
                //System.out.println(rsmd.getColumnName(i)+" -> "+i);

                if (rsmd.getColumnType(i) == java.sql.Types.DATE || colName.toLowerCase().endsWith("date")
                        || colName.toLowerCase().startsWith("date")) {
                    //System.out.println("Date: "+rsmd.getColumnName(i)+" -> "+i);
                    dateColumns.add(i);
                }
            }

            Hashtable<String, String> oldNameToNewNameHash = new Hashtable<String, String>();
            if (colNewToOldMap != null) {
                for (String newName : colNewToOldMap.keySet()) {
                    String oldName = colNewToOldMap.get(newName);
                    System.out
                            .println("Mapping oldName[" + (oldName == null ? newName : oldName) + " -> " + newName);

                    oldNameToNewNameHash.put(oldName == null ? newName : oldName, newName);
                }
            }

            // System.out.println("Num Cols: "+rsmd.getColumnCount());

            Map<String, PartialDateConv> dateMap = new Hashtable<String, PartialDateConv>();

            String insertSQL = null;

            // Get the columns that have dates in case we get a TimestampCreated date that is null
            // and then we can go looking for an older date to try to figure it out
            Integer timestampModifiedInx = fromHash.get("TimestampModified");
            Integer timestampCreatedInx = fromHash.get("TimestampCreated");
            boolean isAccessionTable = fromTableName.equals("accession");
            boolean hasInstIdCol = fromTableName.equals("permit") || fromTableName.equals("journal")
                    || fromTableName.equals("referencework");

            StringBuffer str = new StringBuffer(1024);
            int count = 0;
            while (rs.next()) {
                boolean skipRecord = false;

                dateMap.clear();

                // Start by going through the resultset and converting all dates from Integers
                // to real dates and keep the verbatium date information if it is a partial date
                for (int i : dateColumns) {
                    String oldColName = rsmd.getColumnName(i);
                    Integer oldColIndex = fromHash.get(oldColName);

                    if (oldColIndex == null) {
                        log.error("Couldn't find new column for old column for date for Table[" + fromTableName
                                + "] Col Name[" + newFieldMetaData.get(i).getName() + "]");
                        continue;
                    }

                    if (oldColIndex > newFieldMetaData.size()) {
                        continue;
                    }

                    String newColName = colNewToOldMap != null ? oldNameToNewNameHash.get(oldColName) : null;
                    if (newColName == null) {
                        newColName = oldColName;
                    }

                    Object dataObj = rs.getObject(i);

                    if (dataObj instanceof Integer) {
                        PartialDateConv datep = new PartialDateConv();
                        getPartialDate((Integer) dataObj, datep); // fills in Verbatim also

                        dateMap.put(newColName, datep);
                    }
                }

                // OK here we make sure that both the created dated ad modified date are not null
                // and we copy the date if one has a value and the other does not.
                Date timestampCreatedCached = now;
                Date timestampModifiedCached = now;

                if (timestampModifiedInx != null && timestampCreatedInx != null) {
                    timestampModifiedCached = rs.getDate(timestampModifiedInx);
                    timestampCreatedCached = rs.getDate(timestampCreatedInx);
                    if (timestampModifiedCached == null && timestampCreatedCached == null) {
                        timestampCreatedCached = Calendar.getInstance().getTime();
                        timestampModifiedCached = Calendar.getInstance().getTime();

                    } else if (timestampModifiedCached == null && timestampCreatedCached != null) {
                        timestampModifiedCached = new Date(timestampCreatedCached.getTime());
                    } else {
                        timestampCreatedCached = timestampModifiedCached != null
                                ? new Date(timestampModifiedCached.getTime())
                                : new Date();
                    }
                } else {

                    if (timestampModifiedInx != null) {
                        timestampModifiedCached = rs.getDate(timestampModifiedInx);
                        if (timestampModifiedCached == null) {
                            timestampModifiedCached = now;
                        }
                    }

                    if (timestampCreatedInx != null) {
                        timestampCreatedCached = rs.getDate(timestampCreatedInx);
                        if (timestampCreatedCached == null) {
                            timestampCreatedCached = now;
                        }
                    }
                }

                str.setLength(0);
                if (insertSQL == null) {
                    StringBuffer fieldList = new StringBuffer();
                    fieldList.append("( ");
                    for (int i = 0; i < newFieldMetaData.size(); i++) {
                        if ((i > 0) && (i < newFieldMetaData.size())) {
                            fieldList.append(", ");
                        }
                        String newFieldName = newFieldMetaData.get(i).getName();
                        fieldList.append(newFieldName + " ");
                    }
                    fieldList.append(")");

                    str.append("INSERT INTO " + toTableName + " " + fieldList + " VALUES (");

                    insertSQL = str.toString();

                    log.debug(str);
                } else {
                    str.append(insertSQL);
                }

                id = rs.getString(1);

                // For each column in the new DB table...
                for (int i = 0; i < newFieldMetaData.size(); i++) {
                    FieldMetaData newFldMetaData = newFieldMetaData.get(i);
                    String newColName = newFldMetaData.getName();
                    String oldMappedColName = null;

                    //System.out.println("["+newColName+"]");

                    // Get the Old Column Index from the New Name
                    // String  oldName     = colNewToOldMap != null ? colNewToOldMap.get(newColName) : newColName;
                    Integer columnIndex = fromHash.get(newColName);

                    if (columnIndex == null && colNewToOldMap != null) {
                        oldMappedColName = colNewToOldMap.get(newColName);
                        if (oldMappedColName != null) {
                            columnIndex = fromHash.get(oldMappedColName);

                        } else if (isOptionOn(SHOW_NAME_MAPPING_ERROR) && (ignoreMappingFieldNames == null
                                || ignoreMappingFieldNames.get(newColName) == null)) {
                            String msg = "No Map for table [" + fromTableName + "] from New Name[" + newColName
                                    + "] to Old Name[" + oldMappedColName + "]";
                            log.error(msg);

                            writeErrLog(msg);

                        }
                    } else {
                        oldMappedColName = newColName;
                    }

                    String verbatimDateFieldName = null;
                    if (verbatimDateMapper != null) {
                        verbatimDateFieldName = verbatimDateMapper.get(newColName);
                    }

                    //System.out.println("new["+newColName+"]  old["+oldMappedColName+"]");

                    if (columnIndex != null) {
                        if (i > 0)
                            str.append(", ");
                        Object dataObj = rs.getObject(columnIndex);

                        if (idMapperMgr != null && oldMappedColName != null && oldMappedColName.endsWith("ID")) {
                            IdMapperIFace idMapper = idMapperMgr.get(fromTableName, oldMappedColName);
                            if (idMapper != null) {
                                int showNullOption = SHOW_NULL_FK;
                                int showFkLookUpOption = SHOW_FK_LOOKUP;

                                int oldPrimaryKeyId = rs.getInt(columnIndex);
                                if (oldMappedColName.equalsIgnoreCase(fromTableName + "id")) {
                                    showNullOption = SHOW_NULL_PM;
                                    showFkLookUpOption = SHOW_PM_LOOKUP;
                                }

                                // if the value was null, getInt() returns 0
                                // use wasNull() to distinguish real 0 from a null return
                                if (rs.wasNull()) {
                                    dataObj = null;

                                    if (isOptionOn(showNullOption)) {

                                        String msg = "Unable to Map "
                                                + (showNullOption == SHOW_NULL_FK ? "Foreign" : "Primary")
                                                + " Key Id[NULL] old Name[" + oldMappedColName + "]   colInx["
                                                + columnIndex + "]   newColName[" + newColName + "]";
                                        log.error(msg);
                                        writeErrLog(msg);
                                        skipRecord = true;
                                    }
                                } else {
                                    dataObj = idMapper.get(oldPrimaryKeyId);

                                    if (dataObj == null && isOptionOn(showFkLookUpOption)) {
                                        String msg = "Unable to Map Primary Id[" + oldPrimaryKeyId + "] old Name["
                                                + oldMappedColName + "] table[" + fromTableName + "]";
                                        log.error(msg);
                                        writeErrLog(msg);
                                        skipRecord = true;
                                    }
                                }
                            } else {
                                if (isOptionOn(SHOW_NAME_MAPPING_ERROR) && (ignoreMappingFieldIDs == null
                                        || ignoreMappingFieldIDs.get(oldMappedColName) == null)) {
                                    // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                                    // XXX Temporary fix so it doesn't hide other errors
                                    // Josh has promised his first born if he doesn't fix this!
                                    // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                                    if (!oldMappedColName.equals("RankID")) {
                                        //idMapperMgr.dumpKeys();
                                        String msg = "No ID Map for [" + fromTableName + "] Old Column Name["
                                                + oldMappedColName + "]";
                                        log.error(msg);
                                        writeErrLog(msg);
                                        skipRecord = true;
                                    }
                                }
                            }
                        }

                        // First check to see if it is null
                        if (dataObj == null) {
                            if (newFldMetaData.getName().equals("TimestampCreated")) {
                                if (timestampCreatedInx != null) {
                                    if (isAccessionTable) {
                                        Date date = UIHelper
                                                .convertIntToDate(rs.getInt(fromHash.get("DateAccessioned")));
                                        str.append(date != null ? getStrValue(date)
                                                : getStrValue(timestampCreatedCached, newFldMetaData.getType()));
                                    } else {
                                        str.append(getStrValue(timestampCreatedCached, newFldMetaData.getType()));
                                    }

                                } else {
                                    str.append(getStrValue(timestampCreatedCached, newFldMetaData.getType()));
                                }

                            } else if (newFldMetaData.getName().equals("TimestampModified")) {
                                if (timestampModifiedInx != null) {
                                    if (isAccessionTable) {
                                        Date date = UIHelper
                                                .convertIntToDate(rs.getInt(fromHash.get("DateAccessioned")));
                                        str.append(date != null ? getStrValue(date)
                                                : getStrValue(timestampCreatedCached, newFldMetaData.getType()));

                                    } else {
                                        str.append(getStrValue(timestampModifiedCached, newFldMetaData.getType()));
                                    }
                                } else {
                                    str.append(getStrValue(timestampModifiedCached, newFldMetaData.getType()));
                                }
                            } else {
                                str.append("NULL");
                            }

                        } else if (dataObj instanceof Integer && (newFldMetaData.getSqlType() == java.sql.Types.DATE
                                || newColName.toLowerCase().endsWith("date")
                                || newColName.toLowerCase().startsWith("date"))) {
                            PartialDateConv datePr = dateMap.get(newColName);
                            if (datePr != null) {
                                str.append(datePr.getDateStr());
                            } else {
                                str.append("NULL");
                            }

                        } else if (verbatimDateFieldName != null) {
                            PartialDateConv datePr = dateMap.get(newColName);
                            str.append(datePr != null ? datePr.getVerbatim() : "NULL");

                        } else if (dataObj instanceof Number) {
                            DBFieldInfo fi = tblInfo.getFieldByColumnName(newColName);
                            String type = newFldMetaData.getType().toLowerCase().startsWith("tiny") ? fi.getType()
                                    : newFldMetaData.getType();
                            str.append(getStrValue(dataObj, type));

                        } else {
                            if (columnValueMapper != null) {
                                BasicSQLUtilsMapValueIFace valueMapper = columnValueMapper.get(newColName);
                                if (valueMapper != null) {
                                    dataObj = valueMapper.mapValue(dataObj);
                                }
                            }

                            if (dataObj instanceof String && newFldMetaData.isString()) {
                                DBFieldInfo fi = tblInfo.getFieldByColumnName(newColName);
                                String s = (String) dataObj;
                                if (s.length() > fi.getLength()) {
                                    String msg = String.format(
                                            "Truncating Table '%s' Field '%s' with Length %d, db len %d Value[%s]",
                                            toTableName, newColName, s.length(), fi.getLength(), s);
                                    tblWriter.logError(msg);
                                    log.error(msg);
                                    dataObj = s.substring(0, fi.getLength());
                                }
                            }
                            str.append(getStrValue(dataObj, newFldMetaData.getType()));
                        }

                    } else if (hasInstIdCol && newFldMetaData.getName().equals("InstitutionID")) {
                        if (i > 0)
                            str.append(", ");
                        str.append("1");

                    } else if (newColName.endsWith("Version")) {
                        if (i > 0)
                            str.append(", ");
                        str.append("0");

                    } else if (newColName.endsWith("DatePrecision")) {
                        if (i > 0)
                            str.append(", ");

                        String cName = newColName.substring(0, newColName.length() - 9);
                        PartialDateConv datePr = dateMap.get(cName);
                        if (datePr != null) {
                            str.append(datePr.getPartial());
                        } else {
                            str.append("NULL");
                        }

                    } else if (idMapperMgr != null && newColName.endsWith("ID") && oneToOneIDHash != null
                            && oneToOneIDHash.get(newColName) != null) {

                        IdMapperIFace idMapper = idMapperMgr.get(toTableName, newColName);
                        if (idMapper != null) {
                            idMapper.setShowLogErrors(false);
                            Integer newPrimaryId = idMapper.get(Integer.parseInt(id));
                            if (newPrimaryId != null) {
                                if (i > 0)
                                    str.append(", ");
                                str.append(newPrimaryId);
                            } else {
                                if (i > 0)
                                    str.append(", ");
                                str.append("NULL");

                                if (isOptionOn(SHOW_VAL_MAPPING_ERROR)) {
                                    String msg = "For Table[" + fromTableName + "] mapping new Column Name["
                                            + newColName + "] ID[" + id + "] was not mapped";
                                    log.error(msg);
                                    writeErrLog(msg);
                                    skipRecord = true;
                                }
                            }
                        }

                    } else // there was no old column that maps to this new column
                    {
                        String newColValue = null;
                        if (newColDefValues != null) {
                            newColValue = newColDefValues.get(newColName);
                        }

                        if (newColValue == null) {
                            newColValue = "NULL";
                            //System.out.println("ignoreMappingFieldNames" + ignoreMappingFieldNames);
                            //System.out.println("ignoreMappingFieldNames.get(colName)" + ignoreMappingFieldNames.get(colName));
                            if (isOptionOn(SHOW_NAME_MAPPING_ERROR) && (ignoreMappingFieldNames == null
                                    || ignoreMappingFieldNames.get(newColName) == null)) {
                                String msg = "For Table[" + fromTableName + "] mapping new Column Name["
                                        + newColName + "] was not mapped";
                                log.error(msg);
                                writeErrLog(msg);
                                skipRecord = true;
                            }
                        }
                        if (i > 0)
                            str.append(", ");

                        BasicSQLUtilsMapValueIFace valueMapper = columnValueMapper.get(newFldMetaData.getName());
                        if (valueMapper != null) {
                            newColValue = valueMapper.mapValue(newColValue);
                        }

                        str.append(newColValue);
                    }

                }

                str.append(")");
                if (frame != null) {
                    if (count % 500 == 0) {
                        frame.setProcess(count);
                    }

                } else {
                    if (count % 2000 == 0) {
                        log.info(toTableName + " processed: " + count);
                    }
                }

                //setQuotedIdentifierOFFForSQLServer(toConn, BasicSQLUtils.myDestinationServerType);
                //exeUpdateCmd(updateStatement, "SET FOREIGN_KEY_CHECKS = 0");
                //if (str.toString().toLowerCase().contains("insert into locality"))
                //{
                //log.debug(str.toString());
                //}

                //String str2 = "SET QUOTED_IDENTIFIER ON";
                //log.debug("executing: " + str);
                //updateStatement.execute(str2);
                // updateStatement.close();
                if (!skipRecord) {
                    if (isOptionOn(SHOW_COPY_TABLE)) {
                        log.debug("executing: " + str);
                    }
                    int retVal = exeUpdateCmd(updateStatement, str.toString());
                    if (retVal == -1) {
                        rs.close();
                        stmt.clearBatch();
                        stmt.close();
                        return false;
                    }
                }
                count++;
                // if (count == 1) break;
            }

            if (frame != null) {
                frame.setProcess(count);

            } else {
                log.info(fromTableName + " processed " + count + " records.");
            }

            rs.close();
            stmt.clearBatch();
            stmt.close();

        } catch (SQLException ex) {
            ex.printStackTrace();

            edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
            //e.printStackTrace();
            log.error(sqlStr);
            log.error(ex);
            log.error("ID: " + id);
        } finally {
            try {
                updateStatement.clearBatch();
                updateStatement.close();

            } catch (SQLException ex) {

            }
        }
        BasicSQLUtils.setFieldsToIgnoreWhenMappingNames(null);//meg added
        return true;
    }

    /**
     * @param newFieldName
     * @param type
     * @param data
     * @param sb
     */
    public static void fixTimestamps(final String newFieldName, final String type, final Object data,
            final StringBuilder sb) {
        if (newFieldName.equals("TimestampModified") || newFieldName.equals("TimestampModified")) {
            if (getStrValue(data, type).toString().toLowerCase().equals("null")) {
                sb.append("'" + nowStr + "'");
            } else {
                sb.append(getStrValue(data, type));
            }
        } else {
            sb.append(getStrValue(data, type));
        }
    }

    /**
     * @param data
     * @param newFieldType
     * @param datePair
     */
    public static void getPartialDate(final Object data, final PartialDateConv partialDateConv) {
        getPartialDate(data, partialDateConv, true);
    }

    /**
     * @param data
     * @param newFieldType
     * @param datePair
     */
    public static void getPartialDate(final Object data, final PartialDateConv partialDateConv,
            final boolean includeQuotes) {
        partialDateConv.setAllNullStrs();

        if (data != null) {
            if (((Integer) data) > 0) {
                // 012345678     012345678
                // 20051314      19800307
                Date dateObj = null;
                String dateStr = ((Integer) data).toString();
                String partial = "1";
                String verbatim = "NULL";

                if (dateStr.length() == 8) {
                    String yearStr = dateStr.substring(0, 4);
                    if (yearStr.equals("0000") || yearStr.equals("9999") || yearStr.equals("1111")) {
                        verbatim = dateStr;
                        dateStr = "NULL";
                        partial = "NULL";

                    } else {
                        Integer yr = Integer.parseInt(yearStr);
                        if (yr < 1700 || yr > currentYear) {
                            dateStr = "NULL";
                            partial = "NULL";
                            verbatim = dateStr;

                        } else {
                            //System.out.println("["+dateStr+"]["+data+"]");//["+(dateStr.length() >)+"]");
                            int fndInx = dateStr.substring(4, 8).indexOf("00");
                            if (fndInx > -1) {
                                if (fndInx == 0) {
                                    dateStr = dateStr.substring(0, 4) + "0101";
                                    dateObj = UIHelper.convertIntToDate(Integer.parseInt(dateStr));
                                    partial = "3";

                                } else if (fndInx == 2) {
                                    dateStr = dateStr.substring(0, 6) + "01";
                                    dateObj = UIHelper.convertIntToDate(Integer.parseInt(dateStr));
                                    partial = "2";

                                } else {
                                    dateObj = UIHelper.convertIntToDate((Integer) data);
                                    partial = "1";
                                }

                            } else {
                                dateObj = UIHelper.convertIntToDate((Integer) data);
                                partial = "1";
                            }
                            dateStr = (dateObj == null) ? "NULL"
                                    : (includeQuotes ? "\"" : "") + dateFormatter.format(dateObj)
                                            + (includeQuotes ? "\"" : "");
                        }
                    }
                    partialDateConv.set(dateStr, partial, verbatim);
                } else {
                    log.error("Partial Date was't 8 digits! [" + dateStr + "]");
                }
            }
        }
    }

    /**
     * Takes a list of names and creates a string with the names comma separated
     * @param list the list of names (or field names)
     * @return the string of comma separated names
     */
    public static String buildSelectFieldList(final List<String> list) {
        return buildSelectFieldList(list, null);
    }

    /**
     * Takes a list of names and creates a string with the names comma separated
     * @param list the list of names (or field names)
     * @param tableName table prefix
     * @return the string of comma separated names
     */
    public static String buildSelectFieldList(final List<String> list, final String tableName) {
        StringBuffer str = new StringBuffer();
        for (int i = 0; i < list.size(); i++) {
            if (i > 0)
                str.append(", ");

            if (tableName != null) {
                str.append(tableName);
                str.append('.');
            }

            str.append(list.get(i));
        }
        return str.toString();
    }

    /**
     * Takes a list of names and creates a string with the names comma separated
     * @param list the list of names (or field names)
     * @return the string of comma separated names
     */
    public static String buildSelectFieldMetaDataList(final List<FieldMetaData> list, final String tableName) {
        StringBuffer str = new StringBuffer();
        for (int i = 0; i < list.size(); i++) {
            if (i > 0)
                str.append(", ");

            if (tableName != null) {
                str.append(tableName);
                str.append('.');
            }

            str.append(list.get(i).getName());
        }
        return str.toString();
    }

    /**
     * Creates a mapping of the new name to the old name
     * @param pairs array of pairs of names
     * @return the map object
     */
    public static Map<String, String> createFieldNameMap(String[] pairs) {
        Map<String, String> map = new Hashtable<String, String>();

        for (int i = 0; i < pairs.length; i++) {
            map.put(pairs[i], pairs[i + 1]);
            i++;
        }
        return map;
    }

    /**
     * Returns the number of records in a table
     * @param connection db connection
     * @param tableName the name of the table
     * @return the number of records in a table
     */
    public static int getNumRecords(final Connection connection, final String tableName) {
        try {
            Integer count = 0;
            Statement cntStmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = cntStmt.executeQuery("select count(*) from " + tableName);
            if (rs.first()) {
                count = rs.getInt(1);
                if (count == null) {
                    return -1;
                }
            }
            rs.close();
            cntStmt.close();

            return count;

        } catch (SQLException ex) {
            log.error(ex);
            ex.printStackTrace();
            edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
        }
        return -1;
    }

    /**
     * Returns a count of the records query by SQL passed in.
     * @param sql the SQL with a 'count(?)' 
     * @return the number of records or zero
     */
    public static int getNumRecords(final String sql) {
        log.debug(sql);

        Connection conn = DBConnection.getInstance().createConnection();
        try {
            int count = getNumRecords(sql, conn);
            conn.close();
            return count;

        } catch (SQLException ex) {
            edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
            ex.printStackTrace();
        }
        return -1;
    }

    /**
     * Returns a count of the records query by SQL passed in.
     * @param sql the SQL with a 'count(?)' 
     * @return the number of records or zero
     */
    public static int getNumRecords(final String sql, final Connection conn) {
        Statement cntStmt = null;
        try {
            int count = 0;

            if (conn != null) {
                cntStmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                if (cntStmt != null) {
                    ResultSet rs = cntStmt.executeQuery(sql);
                    if (rs.first()) {
                        count = rs.getInt(1);
                    }
                    rs.close();
                }
                cntStmt.close();
            }
            return count;

        } catch (SQLException ex) {
            edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
            log.error(ex);

        } finally {
            try {
                if (cntStmt != null) {
                    cntStmt.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return 0;
    }

    /**
     * Returns the last ID that was inserted into the database
     * @param connection db connection
     * @param tableName the name of the table
     * @param idColName primary key column name
     * @return the last ID that was inserted into the database
     */
    public static int getHighestId(final Connection connection, final String idColName, final String tableName) {
        try {
            Statement cntStmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = cntStmt
                    .executeQuery("select " + idColName + " from " + tableName + " order by " + idColName + " asc");
            int id = 0;
            if (rs.last()) {
                id = rs.getInt(1);
            } else {
                id = 1;
            }
            rs.close();
            cntStmt.close();

            return id;

        } catch (SQLException ex) {
            edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
            log.error(ex);
        }
        return -1;
    }

    /**
     * @param opt
     * @return
     */
    public static boolean isOptionOn(final int opt) {
        return (showErrors & opt) == opt;
    }

    /**
     * @param mySQLFormatedStr
     * @param currentServerType
     * @return
     */
    public static String getServerTypeSpecificSQL(final String mySQLFormatedStr,
            final SERVERTYPE currentServerType) {
        String mySQLFormatedString = mySQLFormatedStr;
        if (currentServerType == SERVERTYPE.MS_SQLServer) {
            mySQLFormatedString = stripSingleQuotes(mySQLFormatedString);
            mySQLFormatedString = stripEngineCharSet(mySQLFormatedString);
            mySQLFormatedString = stripIntSize(mySQLFormatedString);
        }
        return mySQLFormatedString;
    }

    /**
     * @param str
     * @return
     */
    private static String stripSingleQuotes(final String str) {
        return str.replace("`", "");
    }

    /**
     * @param strArg
     * @return
     */
    private static String stripEngineCharSet(final String strArg) {
        String str = strArg;
        str = str.replaceAll("ENGINE=InnoDB", "");
        str = str.replaceAll("DEFAULT CHARSET=latin1", "");
        return str;
    }

    /**
     * @param str
     * @return
     */
    private static String stripIntSize(final String str) {
        return str.replaceAll("\\(11\\)", "");
    }

    /**
     * @param name
     * @param currentServerType
     * @return
     */
    public static String createIndexFieldStatment(final String name, final SERVERTYPE currentServerType) {
        if (currentServerType == SERVERTYPE.MS_SQLServer) {
            return "create INDEX INX_" + name + " ON " + name + " (NewID)";
        } else if (currentServerType == SERVERTYPE.MySQL) {
            return "alter table " + name + " add index INX_" + name + " (NewID)";
        }
        return "alter table " + name + " add index INX_" + name + " (NewID)";
    }

    /**
     * @param connection
     * @param tableName
     * @param currentServerType
     */
    public static void setIdentityInsertONCommandForSQLServer(final Connection connection, final String tableName,
            final SERVERTYPE currentServerType) {
        setIdentityInserCommandForSQLServer(connection, tableName, "ON", currentServerType);
    }

    /**
     * @param connection
     * @param tableName
     * @param currentServerType
     */
    public static void setIdentityInsertOFFCommandForSQLServer(final Connection connection, final String tableName,
            final SERVERTYPE currentServerType) {
        setIdentityInserCommandForSQLServer(connection, tableName, "OFF", currentServerType);
    }

    /**
     * @param connection
     * @param tableName
     * @param mySwitch
     * @param currentServerType
     */
    public static void setIdentityInserCommandForSQLServer(final Connection connection, final String tableName,
            final String mySwitch, final SERVERTYPE currentServerType) {
        //REQUIRED FOR SQL SERVER IN ORDER TO PROGRAMMATICALLY SET DEFAULT VALUES
        if (currentServerType == SERVERTYPE.MS_SQLServer) {
            try {
                Statement cntStmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);
                String str = "SET IDENTITY_INSERT " + tableName + " " + mySwitch;
                cntStmt.execute(str);
                str = "SET QUOTED_IDENTIFIER OFF";
                cntStmt.execute(str);
                cntStmt.close();

            } catch (SQLException ex) {
                edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
                edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
                log.error(ex);
                ex.printStackTrace();
                System.exit(0);
            }
        }
    }

    //    public static void setQuotedIdentifierOFFForSQLServer(Connection connection,
    //                                                           SERVERTYPE currentServerType)
    //    {
    //        //      REQUIRED FOR SQL SERVER IN ORDER TO PROGRAMMATICALLY SET DEFAULT VALUES
    //        if (currentServerType == SERVERTYPE.MS_SQLServer)
    //        {
    //            try
    //            {
    //                Statement cntStmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
    //
    //                String str = "SET QUOTED_IDENTIFIER OFF";
    //                log.debug("executing: " + str);
    //                cntStmt.execute(str);
    //                cntStmt.close();
    //
    //            } catch (SQLException ex)
    //            {
    //                log.error(ex);
    //                ex.printStackTrace();
    //                System.exit(0);
    //            }
    //        }
    //    }
    public static void removeForeignKeyConstraints(final Connection connection, final String tableName,
            final SERVERTYPE currentServerType) {
        try {
            if (currentServerType == SERVERTYPE.MS_SQLServer) {
                Statement cntStmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);
                String str = "ALTER TABLE " + tableName + " NOCHECK CONSTRAINT ALL";
                cntStmt.execute(str);
                cntStmt.close();

            } else {
                Statement cntStmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);
                String str = "SET FOREIGN_KEY_CHECKS = 0";
                cntStmt.execute(str);
                cntStmt.close();

            }
        } catch (SQLException ex) {
            edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
            log.error("Error encountered trying to turn off foreign key constraints on database");
            log.error(ex);
        }
    }

    /**
     * @param connection
     * @param currentServerType
     */
    public static void removeForeignKeyConstraints(final Connection connection,
            final SERVERTYPE currentServerType) {
        try {
            if (currentServerType == SERVERTYPE.MS_SQLServer) {
                List<String> myTables = getTableNames(connection);
                for (Iterator<String> i = myTables.iterator(); i.hasNext();) {
                    String s = i.next();

                    Statement cntStmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                            ResultSet.CONCUR_READ_ONLY);
                    String str = "ALTER TABLE " + s + " NOCHECK CONSTRAINT ALL";
                    cntStmt.execute(str);
                    cntStmt.close();
                }
            } else {
                Statement cntStmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);
                String str = "SET FOREIGN_KEY_CHECKS = 0";
                cntStmt.execute(str);
                cntStmt.close();
            }
        } catch (SQLException ex) {
            edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
            log.error(ex);
        }
    }
}