$re = '/(\s*\}\s*catch\s*\(\s*\w+\s+(?<ErrName>\w+)\s*\)\s*\{\s*)m_log.error\s*\(\s*\g\'ErrName\'\s*\)\s*;\s+(throw\s+new\s+(?:\w+)\s*\(\s*"[^;]+(?=[)];))/s';
$str = 'package com.vips.fwa.db.mss;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import org.apache.log4j.Logger;
import com.vips.fwa.db.exception.DBMgrException;
import com.vips.fwa.db.exception.DbException;
import com.vips.fwa.db.exception.LoadWarningException;
import com.vips.fwa.db.factory.ColDDL;
import com.vips.fwa.db.factory.DbTable;
import com.vips.fwa.db.factory.DbTableImpl;
import com.vips.fwa.db.jdbc.DBMgr;
import com.vips.fwa.db.jdbc.Database;
import com.vips.fwa.db.jdbc.ResultSetWrapper;
import com.vips.fwa.db.metadata.ColInfo;
import com.vips.fwa.db.metadata.ColInfoList;
import com.vips.fwa.db.metadata.DbObjectMetaData;
import com.vips.fwa.db.metadata.IndexColInfo;
import com.vips.fwa.db.metadata.IndexInfo;
import com.vips.fwa.db.metadata.IndexType;
import com.vips.fwa.db.metadata.MetaObjectInfo;
import com.vips.fwa.db.util.UnixUtils;
import com.vips.fwa.log4j.FwaLogger;
import com.vips.fwa.util.config.DIConfig;
/**
* This class provides methods to implement SQL Server specific table management.
*
* @author B. Zhang 11/2009 Initial Code
*/
public class MssTable extends DbTableImpl implements DbTable {
private static final String CTL_FILE_EXT = ".xml";
private static final String ERR_FILE_EXT = ".err";
private static final String LOG_FILE_EXT = ".log";
private static final String CFG_DB_USER = "user";
private static final String CFG_DB_PASSWORD = "password";
private static final String CFG_DB_BASEURL = "baseUrl";
private static final String CFG_LOAD_DEBUG="mssql.load.debug";
private static final String BAD_FILE_EXT = ".bad";
private static final String UNLOAD_FIELD_DELIMITER="unload.field.delimiter";
private static final String UNLOAD_RECORD_DELIMITER="unload.record.delimiter";
/**
* Construction
* @param tblInfo - current table\'s meta info
* @param log - log file
* @throws DbException
*/
public MssTable(MetaObjectInfo tblInfo,Logger log) throws DbException {
super(tblInfo, log);
}
/**
* Create the object based on the meta data information about the object and it\'s columns.
* @throws DbException
*/
@Override
public void create() throws DbException {
StringBuilder ddl = new StringBuilder();
ddl.append("CREATE TABLE ");
ddl.append(getFullName() + " ");
ddl.append(generateDDLColumnSyntax());
createTable(ddl.toString());
}
/*
* Create table based on passed in DDL SQL.
* @param ddl SQL start with "CREATE TABLE".
*/
@Override
protected void createTable(String ddl) throws DbException {
if ( m_metaObjInfo.getColumns() == null || m_metaObjInfo.getColumns().size()==0 ) {
throw new DbException("Error in create(): m_columns is null.");
}
try {
m_dbUtils.runSql(ddl);
}catch (Exception e) {
m_log.error(e);
throw new DbException("Error creating table: " + getFullName());
}
super.grant();
}
/**
* Method to create a BCP Control format file to load an SQL Server table.
* File is created using BCP and the table information from the database,
* no column metadata is needed.
*
* @param _flatFileName name of data file to be loaded.
* @return name of control file
*/
@Override
public String createLoadControlFile(String _flatFileName) throws DbException,IOException {
String ctrlname = _flatFileName + CTL_FILE_EXT;
m_log.debug("Creating SQL Server load control file: "+ctrlname);
// Get reference to DI config file
DIConfig config = DIConfig.getConfig();
Properties dbProps = null;
try {
dbProps = DBMgr.getDBProps(super.m_metaObjInfo.getDbReference());
}catch (Exception e) {
m_log.error(e);
throw new DbException("Error getting dbprops values for " + super.m_metaObjInfo.getDbReference());
}
// Determine table loading tool (SQL*Loader, BCP, etc...)
String loader = dbProps.getProperty("loader");
//input database specific parameters
String userId = dbProps.getProperty(CFG_DB_USER);
String password = dbProps.getProperty(CFG_DB_PASSWORD);
String serverBase = dbProps.getProperty(CFG_DB_BASEURL);
String serverName = serverBase.substring(serverBase.indexOf("//")+2,serverBase.indexOf(";"));
String[] loadCommands = new String[14];
loadCommands[0] = loader;
StringBuilder tbName = new StringBuilder(m_metaObjInfo.getSchemaName());
tbName.append(".dbo.").append(m_metaObjInfo.getName());
loadCommands[1] = tbName.toString();
loadCommands[2] = "format";
loadCommands[3] = "nul";
loadCommands[4] = "-c";
loadCommands[5] = "-x";
loadCommands[6] = "-f" + ctrlname;
try {
loadCommands[7] = "-t" + config.get(DIConfig.LOAD_FIELD_DELIMITER);
loadCommands[8] = "-r" + config.get(DIConfig.LOAD_RECORD_DELIMITER);
loadCommands[9] = "-U";
loadCommands[10] = "*REM*"+userId;
loadCommands[11] = "-P";
loadCommands[12] = "*REM*"+password;
loadCommands[13] = "-S" + serverName;
}catch (Exception e) {
m_log.error(e);
throw new DbException("Missing required parameters in configration file for table "+getFullName());
}
int exitVal;
try {
exitVal = UnixUtils.runCommand(loadCommands, m_log);
} catch (InterruptedException e) {
m_log.error(e);
throw new DbException("Error creating control file");
}
// int exitVal = this.m_dbUtils.exec(loadCommands, null, m_log, null);
m_log.trace("Ending creating load control file - exitVal= " + exitVal);
return ctrlname;
}
/**
* Build user defined index.
* @param IndexInfo info that contains the meta data info.
* @return true if index built
* @throws DbException
*
*/
@Override
public boolean buildDBMSIndex(IndexInfo info) throws DbException
{
String sql = null;
if (info.getIndexType() == IndexType.PK)
{
m_log.info("Building primary key: "+info.getIndexName());
sql = getPrimaryKeySql(info);
}
else
{
m_log.info("Building index: "+info.getIndexName());
sql = getIndexSql(info);
}
try {
m_dbUtils.runSql(sql);
}catch (Exception e) {
m_log.error(e);
throw new DbException("Error creating index "+info.getIndexName()+" on table"+m_metaObjInfo.getName());
}
return true;
}
/**
* Generate SQL as \'CREATE <indexType> INDEX...\'
* @param _indexInfo
* @return SQL for creating index
*/
protected String getIndexSql(IndexInfo _indexInfo) throws DbException
{
String indexName = _indexInfo.getIndexName();
IndexType indexType = _indexInfo.getIndexType();
ArrayList<IndexColInfo> colInfoList=_indexInfo.getIndexColInfoList();
IndexColInfo colInfo = null;
StringBuilder sqlBld = new StringBuilder("CREATE ");
if (indexType==IndexType.UNIQUE||indexType==IndexType.BITMAP) {
sqlBld.append(indexType + " ");
}
sqlBld.append("INDEX ").append(indexName);
sqlBld.append(" ON ").append(getFullName()).append(" (");
for (int i=0;i<colInfoList.size();i++) {
if (i>0) {
sqlBld.append(", ");
}
colInfo=colInfoList.get(i);
sqlBld.append(colInfo.getColumnName());
}
sqlBld.append(")");
return sqlBld.toString();
}
/**
* This method is used to check if index already exists based on the index name.
* @param _indexName
* @return true if index exists with matching name
* @throws DbException
*
*/
@Override
protected boolean indexExistsByName(String _indexName) throws DbException
{
boolean exists = false;
List<String> indexNameList = getExistingIndexNames();
if ((indexNameList!=null) && (indexNameList.contains(_indexName))) {
return true;
}
return exists;
}
/**
* This method is used to check if index already exists based on the index columns.
*
* @param _info
* @return index name if matching index exists, otherwise null
* @throws DbException
*
*/
@Override
protected String indexColumnsExists(IndexInfo _info) throws DbException
{
List<IndexColInfo> infoColList = _info.getIndexColInfoList();
StringBuilder sql = new StringBuilder("SELECT I.NAME FROM SYS.INDEXES I ");
sql.append("WITH(NOLOCK) ");
sql.append("JOIN SYS.INDEX_COLUMNS IC WITH(NOLOCK) ON I.OBJECT_ID=IC.OBJECT_ID AND I.INDEX_ID=IC.INDEX_ID ");
sql.append("JOIN SYS.COLUMNS C WITH(NOLOCK) ON IC.OBJECT_ID=C.OBJECT_ID AND IC.COLUMN_ID=C.COLUMN_ID ");
sql.append("WHERE OBJECT_NAME(I.OBJECT_ID)=");
sql.append(DBMgr.fixSqlString(m_metaObjInfo.getName()));
sql.append(" AND C.NAME IN (");
for (IndexColInfo colInfo : infoColList){
sql.append("\'").append(colInfo.getColumnName()).append("\'").append(", ");
}
sql.delete(sql.length()-2, sql.length());
sql.append(") GROUP BY I.NAME ");
sql.append("HAVING COUNT(I.NAME) = ").append(infoColList.size());
String indexName = null;
try
{
indexName = m_dbUtils.runStringQuery(sql.toString(),true,true);
}
catch (Exception e)
{
m_log.error(e);
throw new DbException("Error checking if index exists with a different name");
}
return indexName;
}
/**
* Get a list containing all primary constraintNames for this table.
* @return list of constraint names
* @throws DbException
*/
@Override
protected List<String> getConstraintNames() throws DbException {
if (m_constraints != null) {
return m_constraints;
}
StringBuilder sql=new StringBuilder();
sql.append("SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE=\'PRIMARY KEY\'");
sql.append(" AND TABLE_NAME=\'" + this.getName() + "\'");
sql.append(" AND TABLE_CATALOG=\'" + m_metaObjInfo.getSchemaName().toUpperCase() + "\'");
try {
m_constraints = m_dbUtils.runStringListQuery(sql.toString());
if (m_constraints.size()==0) {
m_constraints = null;
}
}catch ( Exception e ) {
m_log.error(e);
throw new DbException("Error executing SQL: "+sql.toString());
}
return m_constraints;
}
/**
* Return number of indexes that exist
* There is a bug in SQL Server 2005, If no index found it returns index name null but the count will be 1
* So I couldn\'t tell if the index exists or not by counting index numbers
* @return
* @throws DbException
*/
@Override
protected int getExistingIndexCount() throws DbException
{
int count = 0;
String tbName=m_metaObjInfo.getName();
if (m_constraints == null) {
getConstraintNames();
}
StringBuilder constraint_name=new StringBuilder();
if (m_constraints!=null) {
String comma="";
for (int i=0;i<m_constraints.size();i++) {
constraint_name.append(comma+"\'"+m_constraints.get(i)+"\'");
comma=",";
}
}
//create sql for to retrieve the index count from the system table
StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM SYS.INDEXES ");
sql.append("WITH(NOLOCK) ");
sql.append("WHERE OBJECT_NAME(OBJECT_ID) = \'").append(m_metaObjInfo.getName()).append("\'");
if (m_constraints!=null) {
sql.append(" AND NAME NOT IN ("); // don\'t return system generated indexes...we can\'t do anything with them anyway.
sql.append(constraint_name+")");
}
try {
count = m_dbUtils.runIntQuery(sql.toString());
}catch (Exception e) {
m_log.error(e);
throw new DbException("Error getting existing index count for table: "+tbName);
}
return count;
}
/**
* This method will be implemented for each vendor to get the list of index
* names for this table. Each implementation of this method will get the
* list from the ASE system tables (not coll_indx_key/coll_indx_rule).
*
* @returns List An List of index names for this table with the
* format m_name.index_name
*
* @throws Exception
*
*/
protected List<String> getExistingIndexNames() throws DbException {
List<String> indexNames = null;
if (m_constraints == null) {
getConstraintNames();
}
StringBuilder constraint_name=new StringBuilder();
if (m_constraints!=null) {
String comma="";
for (int i=0;i<m_constraints.size();i++) {
constraint_name.append(comma+"\'"+m_constraints.get(i)+"\'");
comma=",";
}
}
// create sql for to retrieve the index names from the system table
StringBuilder sql = new StringBuilder("SELECT NAME FROM SYS.INDEXES ");
sql.append("WITH(NOLOCK) ");
sql.append("WHERE OBJECT_NAME(OBJECT_ID) = \'").append(m_metaObjInfo.getName()).append("\'");
if (m_constraints!=null) {
sql.append(" AND NAME NOT IN ("); // don\'t return system generated indexes...we can\'t do anything with them anyway.
sql.append(constraint_name+")");
}
try {
indexNames = m_dbUtils.runStringListQuery(sql.toString());
}catch (Exception e) {
m_log.error(e);
throw new DbException("Error getting existing index names for table: " + super.m_metaObjInfo.getName());
}
if ((indexNames!=null)&&(indexNames.size()>0)&&(indexNames.get(0)==null)) {
indexNames.remove(0);
}
return indexNames;
}
@Override
public void load( String _flatFileName ) throws DbException, LoadWarningException, DBMgrException {
this.loadTable(_flatFileName, false);
}
/**
* Load specified ASCII flat file into this table.
* Sets an indicator for the load command to enable parallel loading. This does not
* have the load actually perform the threading to do parallel loading.
*
* @param _flatFileName is the path and name of the file to be loaded
*
* @throws DbException
* @throws LoadWarningException
* @throws DBMgrException
*
*/
@Override
public void loadWithParallelOption( String _flatFileName )
throws DbException, LoadWarningException, DBMgrException
{
this.loadTable(_flatFileName,true);
}
private void loadTable(String _flatFileName, boolean _enableParallelOption)
throws DbException, LoadWarningException, DBMgrException
{
// Get reference to DI config file
DIConfig config = DIConfig.getConfig();
Properties dbProps = null;
try {
dbProps = DBMgr.getDBProps(super.m_metaObjInfo.getDbReference());
}catch (Exception e) {
m_log.error(e);
throw new DbException("Error getting dbprops values for " + super.m_metaObjInfo.getDbReference());
}
String controlFileName;
// create SQL Loader control file
try {
controlFileName = createLoadControlFile(_flatFileName);
}catch (Exception e) {
m_log.error(e);
throw new DbException("Error creating control file to load "
+ super.m_metaObjInfo.getDbReference());
}
// Determine table loading tool (SQL*Loader, BCP, etc...)
String loader = dbProps.getProperty("loader");
m_log.trace("LOADER: "+loader);
String errors = config.getProperty(DIConfig.LOAD_MAX_ERRORS,"0"); // defaults to 0
// adjust by number of CleanFile errors
int maxErrors = Integer.parseInt(errors);
//output information (logs, discard and invalid rows)
String badFileName = _flatFileName + ERR_FILE_EXT;
//input database specific parameters
String userId = dbProps.getProperty(CFG_DB_USER);
String password = dbProps.getProperty(CFG_DB_PASSWORD);
String serverBase = dbProps.getProperty(CFG_DB_BASEURL);
String serverName = serverBase.substring(serverBase.indexOf("//")+2,serverBase.indexOf(";"));
String[] loadCommands = new String[14];
loadCommands[0] = loader;
StringBuilder tbName = new StringBuilder(m_metaObjInfo.getSchemaName());
tbName.append(".dbo.").append(m_metaObjInfo.getName());
loadCommands[1] = tbName.toString();
loadCommands[2] = "in";
loadCommands[3] = _flatFileName;
loadCommands[4] = "-f" + controlFileName;
try {
String rows = config.getProperty(DIConfig.LOAD_ROWS,"10000");
loadCommands[5] = "-b" + rows;
loadCommands[6] = "-e" + badFileName;
loadCommands[7] = "-U";
loadCommands[8] = "*REM*"+userId;
loadCommands[9] = "-P";
loadCommands[10] = "*REM*"+password;
loadCommands[11] = "-S" + serverName;
loadCommands[12] = "-q";
loadCommands[13] = "-m" + String.valueOf(maxErrors+1);
} catch (Exception e) {
m_log.error(e);
throw new DbException("Missing required parameters in configration file for table "+getFullName());
}
// Determine if log file should be used by loader instead of DI_LOG table.
Logger loadLog;
String logToFile = config.getProperty(DIConfig.LOAD_LOG_TO_FILE,"");
if ( "Y".equals(logToFile.trim().toUpperCase()) ) {
//Determine log name.
String logFilename = config.getDirectory(DIConfig.DI_LOG_DIR)+"LOAD_"+m_metaObjInfo.getName()+".log";
m_log.debug("Create load log file: "+logFilename);
try {
loadLog = FwaLogger.getLogger(logFilename);
}catch (Exception e) {
m_log.error(e);
throw new DbException("Error creating load log file: " + logFilename);
}
}else {
// By default, set log to m_log
loadLog = m_log;
} // end if LOG_TO_FILE = Y
// erase any .bad file from previous runs
if (new File(badFileName).exists()) {
try {
new File(badFileName).delete();
}catch (SecurityException e) {
m_log.warn(e);
}
}
//Get Runtime Environment (Unix)
int exitVal = 0;
try {
exitVal = UnixUtils.runCommand(loadCommands, loadLog);
}catch ( Exception e ) {
m_log.error(e);
throw new DbException("Error utilizing Toolbox.exec to load: "
+ super.m_metaObjInfo.getName());
}
m_log.debug("Bulk Loader exitVal = " + exitVal);
boolean stopProcessing = false;
switch (exitVal) {
case 0: // BCP thinks it did its job... let\'s check the bad file for sure.
//returns a warning even if max errors allowed are reached. We want to
//treat this as a fatal error and stop processing so the job will complete with error
//status rather than warnings.
int errLineCount = 0;
BufferedReader in = null;
if (new File(badFileName).exists()) {
try {
m_log.trace("Get line count of error file: "+badFileName);
in = new BufferedReader(new FileReader(badFileName));
String line = in.readLine();
while (line != null) {
if ( !line.startsWith("#@")) {
errLineCount++;
}
line = in.readLine();
}
m_log.info("Line count on loading error file: "+badFileName +" = " + errLineCount);
in.close();
}
catch (Exception e) {
m_log.error(e);
throw new DbException("Error executing line count on file: " + badFileName);
}
finally {
if (in != null) {
try {
in.close();
}
catch (IOException e) {};
}
}
} // end if bad file exists
if (errLineCount > maxErrors) {
stopProcessing = true;
m_log.error("BCP IN execution encountered more than max number of allowed errors");
}else {
m_log.debug("BCP IN execution successful");
// Successful load! Delete the control file and log unless user specifies
// in config file that it is not to be deleted
// This is to avoid filling up disk space with numerous log files.
// The .bad and .dis (bad and discard files) will never be deleted
// automatically, but if all goes well we do not need the control file
// or the log file (successful record count will be in the DI log)
if (config.getProperty(CFG_LOAD_DEBUG, "N").equals("N")) {
try {
new File(controlFileName).delete();
}catch (SecurityException e) {
m_log.warn(e);
}
}
}
break;
default:
stopProcessing = true;
break;
}
if (stopProcessing) {
throw new DbException("BCP IN returned value: " + exitVal + "--Please Investigate");
}
}
/**
* Unload table contents to specified ASCII file.
*
* @param _flatFileName is the path and name of the file to be generated
* @throws DbException
*/
@Override
public void unload (String _flatFileName) throws DbException
{
// Get reference to DI config file
DIConfig config = DIConfig.getConfig();
Properties dbProps = null;
try {
dbProps = DBMgr.getDBProps(super.m_metaObjInfo.getDbReference());
}catch (Exception e) {
m_log.error(e);
throw new DbException("Error getting dbprops values for " + super.m_metaObjInfo.getDbReference());
}
// Determine table loading tool (SQL*Loader, BCP, etc...)
String loader = dbProps.getProperty("loader");
m_log.debug("LOADER: "+loader);
String errors = config.getProperty(DIConfig.LOAD_MAX_ERRORS,"0"); // defaults to 0
// adjust by number of CleanFile errors
int maxErrors = Integer.parseInt(errors);
//output information (logs, discard and invalid rows)
String log = _flatFileName + LOG_FILE_EXT;
//input database specific parameters
String userId = dbProps.getProperty(CFG_DB_USER);
String password = dbProps.getProperty(CFG_DB_PASSWORD);
String serverBase = dbProps.getProperty(CFG_DB_BASEURL);
String serverName = serverBase.substring(serverBase.indexOf("//")+2,serverBase.indexOf(";"));
//String fDelimiter = dbProps.getProperty("fieldDelimiter");
//String fDelimiter = dbProps.getProperty("fieldDelimiter");
String fDelimiter = config.getProperty(UNLOAD_FIELD_DELIMITER,"~");
String rDelimiter = config.getProperty(UNLOAD_RECORD_DELIMITER,"\\\\n");
String[] unloadCommands = new String[13];
unloadCommands[0] = loader;
//StringBuilder tbName = new StringBuilder(m_metaObjInfo.getSchemaName());
StringBuilder tbName = new StringBuilder(m_metaObjInfo.getDbReference());
tbName.append(".dbo.").append(m_metaObjInfo.getName());
unloadCommands[1] = tbName.toString();
unloadCommands[2] = "out";
unloadCommands[3] = _flatFileName;
unloadCommands[4] = "-c ";
unloadCommands[5] = "-t" + fDelimiter;
unloadCommands[6] = "-r" + rDelimiter;
//unloadCommands[6] = " -r\\\\n ";
//unloadCommands[4] = "-f " + controlFileName;
try {
//String rows = config.getProperty(DIConfig.LOAD_ROWS,"10000");
//unloadCommands[5] = "-b " + rows;
//unloadCommands[6] = "-e" + _flatFileName + ERR_FILE_EXT;
unloadCommands[7] = "-U";
unloadCommands[8] = userId;
unloadCommands[9] = "-P";
unloadCommands[10] = password;
unloadCommands[11] = "-S" + serverName;
//unloadCommands[12] = "-q";
unloadCommands[12] = "";
}catch (Exception e) {
m_log.error(e);
throw new DbException("Missing required parameters in configration file for table "+getFullName());
}
// Determine if log file should be used by loader instead of DI_LOG table.
Logger loadLog;
String logToFile = config.getProperty(DIConfig.LOAD_LOG_TO_FILE,"");
if ( "Y".equals(logToFile.trim().toUpperCase()) ) {
//Determine log name.
String logFilename = config.getDirectory(DIConfig.DI_LOG_DIR)+"LOAD_"+m_metaObjInfo.getName()+".log";
m_log.debug("Create unload log file: "+logFilename);
try {
loadLog = FwaLogger.getLogger(logFilename);
}catch (Exception e) {
m_log.error(e);
throw new DbException("Error creating unload log file: " + logFilename);
}
}else {
// By default, set log to m_log
loadLog = m_log;
} // end if LOG_TO_FILE = Y
//Get Runtime Environment (Unix)
int exitVal = 0;
try {
exitVal = UnixUtils.runCommand(unloadCommands, loadLog);
// exitVal = this.m_dbUtils.exec(unloadCommands, null, loaderDir,loadLog);
}catch ( Exception e ) {
m_log.error(e);
throw new DbException("Error utilizing Toolbox.exec to unload: "
+ super.m_metaObjInfo.getName());
}
m_log.debug("SQL-Loader exitVal= " + exitVal);
boolean stopProcessing = false;
switch (exitVal) {
case 0: m_log.debug("BCP OUT execution successful");
// Successful load! Delete the control file and log unless user specifies
// in config file that it is not to be deleted
// This is to avoid filling up disk space with numerous log files.
// The .bad and .dis (bad and discard files) will never be deleted
// automatically, but if all goes well we do not need the control file
// or the log file (successful record count will be in the DI log)
if (config.getProperty(CFG_LOAD_DEBUG, "N").equals("N")) {
try {
//new File(controlFileName).delete();
new File(log).delete();
}catch (SecurityException e) {
m_log.warn(e);
}
}
break;
case 1: m_log.warn("BCP OUT execution exited with EX_FAIL, see log "+log);
stopProcessing = true;
break;
case 2: String badFileName = _flatFileName + BAD_FILE_EXT;
//returns a warning even if max errors allowed are reached. We want to
//treat this as a fatal error and stop processing so the job will complete with error
//status rather than warnings.
int errLineCount = 0;
BufferedReader in = null;
if (new File(badFileName).exists()) {
try {
m_log.debug("Get line count of error file: "+badFileName);
in = new BufferedReader(new FileReader(badFileName));
String line = in.readLine();
while (line != null) {
line = in.readLine();
errLineCount++;
}
m_log.info("Line count on loading error file: "+badFileName +" = " +errLineCount);
in.close();
}
catch (Exception e) {
m_log.error(e);
throw new DbException("Error executing line count on file: " + badFileName);
}
finally {
if (in != null) {
try {
in.close();
}
catch (IOException e) {}
}
}
} // end if bad file exists
if (errLineCount > maxErrors) {
stopProcessing = true;
m_log.error("BCP OUT execution encountered more than max number of allowed errors, see log "+log);
}else {
m_log.warn("BCP OUT execution exited with EX_WARN, see log "+log);
}
break;
case 3: m_log.error("BCP OUT execution encountered a fatal error, see log "+log);
stopProcessing = true;
break;
default: m_log.error("BCP OUT unknown return code");
stopProcessing = true;
break;
}
if (stopProcessing) {
throw new DbException("BCP OUT returned value: " + exitVal + "--Please Investigate");
}
}
@Override
public void unload (String _flatFileName, String _whereClause) throws DbException
{
}
@Override
public ColInfoList getColumnsFromDatabase ()
{
ArrayList<ColInfo> copyList = new ArrayList<ColInfo>();
StringBuilder sql = new StringBuilder();
sql.append("SELECT COLUMN_NAME, UPPER(DATA_TYPE), ORDINAL_POSITION, ");
sql.append("CASE WHEN IS_NULLABLE = \'YES\' THEN \'Y\' ELSE \'N\' END AS NULLABLE, " );
sql.append(" COALESCE(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, DATETIME_PRECISION ) AS COL_PREC, " );
sql.append(" COALESCE(NUMERIC_SCALE, \'0\' ) AS COL_SCALE " );
sql.append(" FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = \'");
sql.append(m_metaObjInfo.getName()).append("\'");
ColInfoList allColumns = null;
try {
copyList = m_dbUtils.runColInfoArrayListQueryForColumns(sql.toString());
allColumns = new ColInfoList(copyList);
}catch (Exception e) {
m_log.error(e);
}
return allColumns;
}
@Override
public ArrayList<String> getPrimaryKeyFromDatabase()
{
StringBuilder sql = new StringBuilder();
sql.append("SELECT column_name FROM INFORMATION_SCHEMA.constraint_column_usage " );
sql.append("WHERE objectproperty(object_id(constraint_name),\'isprimarykey\') = 1 " );
sql.append("AND table_name = \'" + m_metaObjInfo.getName()).append("\'");
List<String> priKeyList = null;
try {
priKeyList = m_dbUtils.runStringListQuery(sql.toString());
}catch (Exception e) {
m_log.error(e);
}
return new ArrayList<String>( priKeyList );
}
/**
* Method to drop indexes. This uses the system tables to determine the
* index names.
*
* @returns number of indexes dropped
*
* @throws StarsTableException
*/
@Override
public int dropIndexes() throws DbException {
this.dropPrimaryKeyConstraint();
String strIndexName; // Index name to drop
int indxCnt = 0; // # of indexes to drop
// Get the ArrayList of index names. Method getIndexNames resides in the descendant object.
List<String> aIndexNames;
try {
aIndexNames = getExistingIndexNames();
}catch (Exception e) {
m_log.error(e);
throw new DbException("Error getting index names for table: " + super.m_metaObjInfo.getName());
}
int intSize = aIndexNames.size(); // Number of indexes
for (int i = 0; i < intSize; i++) {
// Drop each index retrieve from the ArrayList
StringBuilder sql = new StringBuilder(100);
strIndexName = aIndexNames.get(i);
sql.append("DROP INDEX ").append(m_metaObjInfo.getName());
sql.append(".");
sql.append(strIndexName);
try {
m_dbUtils.runSql(sql.toString());
}catch (Exception e) {
m_log.error(e);
String errMsg = "Error attempting to drop index on table: " + super.m_metaObjInfo.getName();
throw new DbException(errMsg);
}
indxCnt++;
}
return indxCnt;
}
/**
* Change meta data column type to MssColDDL.
* @return ColDDL[] - array of MssColDDL
* @throws DbException
*/
@Override
protected ColDDL[] createDDLColumns() throws DbException
{
ColInfoList cols = super.getColumns();
if (cols == null) {
throw new NullPointerException("Error in createDDLColumns(). m_starsColumns is null");
}
int scSize = cols.size();
ColDDL[] mssqlCols = null;
mssqlCols = new ColDDL[scSize];
for (int i = 0; i < scSize; i++)
{
ColInfo srcCol = cols.get(i);
MssColDDL col = new MssColDDL(srcCol);
mssqlCols[i] = col;
}
return mssqlCols;
}
/**
* SQL Server 2005 does not support a MERGE function. Create and execute the appropriate UPDATE and
* INSERT statements
*/
@Override
public void merge(String targetTbName, IndexInfo indexInfo) throws DbException {
StringBuilder updateStmt = new StringBuilder("UPDATE " + targetTbName + " SET ");
StringBuilder insertStmt = new StringBuilder("INSERT INTO " + targetTbName + " SELECT ");
ColInfoList colList = m_metaObjInfo.getColumns();
// SELECT / SET statements
for (int i=0; i < colList.size(); i++) {
String colName = colList.get(i).getColName();
if (i > 0 ) {
updateStmt.append(", ");
insertStmt.append(", ");
}
updateStmt.append(targetTbName + "." + colName + " = A." + colName);
insertStmt.append("A." + colName);
}
// FROM / JOIN clauses
updateStmt.append(" FROM " + getName() + " A WHERE ");
insertStmt.append(" FROM " + getName() + " A ");
insertStmt.append("LEFT OUTER JOIN " + targetTbName + " B ON ");
// JOIN / WHERE criteria
String nullColNm = null;
for (int i=0; i < indexInfo.getIndexColInfoList().size(); i++) {
String colName = indexInfo.getIndexColInfoList().get(i).getColumnName();
if (i > 0 ) {
updateStmt.append(" AND ");
insertStmt.append(" AND ");
}
else {
// Just pick one column to check for null for the outer join
nullColNm = colName;
}
updateStmt.append(targetTbName + "." + colName + " = A." + colName);
insertStmt.append("A." + colName + " = B." + colName);
}
insertStmt.append(" WHERE B." + nullColNm + " IS NULL");
try {
int rows = m_dbUtils.runSql(updateStmt.toString());
m_log.info(rows + " rows updated in " + targetTbName);
rows = m_dbUtils.runSql(insertStmt.toString());
m_log.info(rows + " rows inserted into " + targetTbName);
}catch (Exception e) {
m_log.error(e );
throw new DbException( "Error merging data into " + targetTbName);
}
}
/**
* Truncate this table.
* @throws DbException
*/
@Override
public void truncate() throws DbException
{
StringBuilder truncSql = new StringBuilder("TRUNCATE TABLE ");
truncSql.append(getFullName());
try {
m_dbUtils.runSql(truncSql.toString());
}catch (Exception e) {
m_log.error(e);
String errMsg = "Error truncating table " + getFullName();
throw new DbException(errMsg);
}
}
/**
* Gather statistics for all columns
* @throws DbException
*
*/
@Override
public void gatherStatistics() throws DbException {
//TODO - how to add params to update statistics?
StringBuilder sql = new StringBuilder();
sql.append("UPDATE STATISTICS ");
sql.append(m_metaObjInfo.getName());
try {
m_dbUtils.runSql( sql.toString() );
}catch(Exception e) {
m_log.error(e);
throw new DbException("Error in updateStatistics for table: "+getFullName());
}
}
/**
* INSERT INTO tablename SELECT...
* insert into the object based on the meta data information about the object and it\'s columns.
*
* @param _selectSql - SQL start with \'SELECT\'
* @return row count of inserted records
* @throws DbException
*/
@Override
public int insertSelect( String _selectSql ) throws DbException {
StringBuilder queryBuffer = new StringBuilder(100);
queryBuffer.append("INSERT INTO ");
queryBuffer.append(getFullName());
queryBuffer.append(" ");
queryBuffer.append(_selectSql);
int rowCount = 0;
try
{
rowCount = m_dbUtils.runSql( queryBuffer.toString() );
}
catch (Exception e)
{
m_log.error(e);
throw new DbException("Error inserting rows into table: "+getFullName() );
}
m_log.info("Number of rows inserted: "+rowCount);
return rowCount;
}
/**
* Renames the table to the value in _tableName passed to the function
* Example:
*
* @param _tableName new name for the table
* @throws DbException
*/
@Override
public void rename (String _tableName) throws DbException {
try {
StringBuilder renameSql;
for (int j=0;j<2;j++) {
List<String> nameList = null;
if (j==0) {
// Loop through and rename the indexes
nameList = getExistingIndexNames();
}else {
nameList = getConstraintNames();
}
for(int i = 0; i < nameList.size(); i++) {
String ixName = nameList.get(i);
renameSql = new StringBuilder("EXEC SP_RENAME \'");
renameSql.append(m_metaObjInfo.getName());
renameSql.append(".");
renameSql.append(ixName);
renameSql.append("\', \'");
renameSql.append(ixName.replace(m_metaObjInfo.getName(), _tableName));
renameSql.append("\', \'INDEX\'");
m_dbUtils.runSql(renameSql.toString());
}
}
renameSql = new StringBuilder("EXEC SP_RENAME ");
renameSql.append(this.getName());
renameSql.append(", ");
renameSql.append(_tableName);
m_dbUtils.runSql(renameSql.toString());
// Refresh information
DbObjectMetaData metaData=DbObjectMetaData.getMetaData(m_metaObjInfo.getDbReference(), m_log);
m_metaObjInfo=metaData.getObjectInfo(_tableName);
}catch(Exception e){
m_log.error(e);
throw new DbException("Error renaming table: "+getFullName()+" to "+_tableName+".");
}
}
/**
* Delete rows from this table.
* The SQL will be
* Delete <currentTableName> from <currentTableName> alias, <_fromWhereClause>
* @param _fromWhereClause
* @param _srcTableAlias
* @return row count of deleted records
* @throws DbException
*/
@Override
public int deleteRows(String _fromWhereClause, String _srcTableAlias ) throws DbException {
StringBuilder deleteSql = new StringBuilder("DELETE ");
deleteSql.append(m_metaObjInfo.getName());
deleteSql.append(" FROM ");
deleteSql.append(m_metaObjInfo.getName());
if (_srcTableAlias!=null)
{
deleteSql.append(" ").append(_srcTableAlias);
}
deleteSql.append(", ");
deleteSql.append(_fromWhereClause);
int numRows = 0;
try {
numRows = m_dbUtils.runSql( deleteSql.toString() );
}catch(Exception e) {
m_log.error(e);
throw new DbException("Error deleting rows from table: "+getFullName()+" based on join.");
}
return numRows;
}
/**
* Delete records based on joins to one more more tables
* @param _joinClause starting with "JOIN", "LEFT OUTER JOIN", etc.
* @param _whereClause Where syntax not including "WHERE" key word
* @return
* @throws DbException
*/
@Override
public int deleteJoinRows(String _joinClause, String _whereClause) throws DbException {
StringBuilder deleteSql = new StringBuilder("DELETE ");
deleteSql.append(m_metaObjInfo.getName());
deleteSql.append(" FROM ");
deleteSql.append(m_metaObjInfo.getName());
deleteSql.append(" ").append(_joinClause);
deleteSql.append(" ").append(_whereClause);
int numRows = 0;
try {
numRows = m_dbUtils.runSql( deleteSql.toString() );
}catch(Exception e) {
m_log.error(e);
throw new DbException("Error deleting rows from table: "+getFullName()+" based on join.");
}
return numRows;
}
/**
* Create Table based on passed in SQL
* @param _selectSql
* @param _ofCreateSql
* @throws DbException
*/
@Override
public void createFromSql(String _selectSql, String _ofCreateSql) throws DbException {
StringBuilder sql = new StringBuilder(_selectSql);
sql.append(" INTO ").append(getFullName());
sql.append(" ").append(_ofCreateSql);
try
{
m_dbUtils.runSql(sql.toString());
}
catch (Exception e)
{
m_log.error(e);
throw new DbException("Error creating table: " + getFullName());
}
super.grant();
}
/**
* Drop this table.
* @throws DbException
*/
@Override
public void drop() throws DbException {
drop(true);
}
/**
* Drop table.
* @param _dropTbSpace true only be used in Oracle.
* @throws DbException
*/
@Override
public void drop(boolean _dropTbSpace) throws DbException
{
try {
m_dbUtils.runSql("DROP TABLE " + getFullName());
}catch (Exception e) {
m_log.error(e);
throw new DbException("Error dropping table: " + getFullName());
}
}
/**
* Gather table and index statistics on indexed columns
* Only be used in Oracle
*/
@Override
public void gatherStatisticsOnIndexedColumns() throws DbException {
}
/**
* Remove all but one record for any NKs that exist more than once
* @param _ixInfo - index info
* @return number of indexes removed
* @throws DbException
*/
@Override
public int removeDuplicates(IndexInfo _ixInfo) throws DbException {
ArrayList<IndexColInfo> ixColInfo = _ixInfo.getIndexColInfoList();
StringBuilder keyColString = new StringBuilder();
for(int i=0; i < ixColInfo.size(); i++) {
if (i > 0) {keyColString.append(", ");}
keyColString.append(ixColInfo.get(i).getColumnName());
}
StringBuilder selectFrom = new StringBuilder();
selectFrom.append("SELECT COUNT(*) AS TOTAL,");
selectFrom.append(keyColString);
selectFrom.append(" FROM ");
selectFrom.append(getFullName());
selectFrom.append(" GROUP BY ");
selectFrom.append(keyColString);
selectFrom.append(" HAVING COUNT(*) >1");
int rowCount = 0;
int delCount = 0;
// Database Connection
Database db;
try {
db = DBMgr.connectToDatabase(m_metaObjInfo.getDbReference(), m_log);
}catch ( Exception e ) {
m_log.error(e );
throw new DbException( "Error getting Database object for " + m_metaObjInfo.getDbReference());
}
ResultSetWrapper rs = null;
try {
rs = db.query(selectFrom.toString());
while (rs.next()) {
StringBuilder whereClause = new StringBuilder();
for(int i=0; i < ixColInfo.size(); i++) {
if (i > 0) {whereClause.append(" AND ");}
whereClause.append(ixColInfo.get(i).getColumnName());
whereClause.append("=");
whereClause.append(DBMgr.fixSqlString(rs.getString(i+2)));
}
rowCount = rs.getInt("TOTAL")-1;
delCount = delCount + m_dbUtils.runLimitSql("DELETE FROM " + m_metaObjInfo.getName() +
" WHERE " + whereClause.toString(), rowCount);
} // end loop
}catch (Exception e) {
m_log.error(e );
throw new DbException( "Error removing duplicate for " + getFullName());
}finally {
// Close the ResultSet
if (rs != null) {
try {
rs.close();
}catch (Exception e) {
m_log.error(e );
db = null;
}
}
// Release the database connection
if (db != null) {
try {
db.close();
}catch (Exception e) {
m_log.error(e );
db = null;
}
}
}
return delCount;
}
/**
* Generate SQL as \'ALTER TABLE <tableName> ADD CONSTRINT <indexName> PRIMARY KEY (...\'
* @param IndexInfo info that contains the meta data info.
* @return SQL as \'ALTER TABLE <tableName> ADD CONSTRINT <indexName> PRIMARY KEY (...\'
* @throws DbException
*
*/
@Override
protected String getPrimaryKeySql(IndexInfo ixInfo) throws DbException {
StringBuffer sql = new StringBuffer();
if (!(ixInfo.getIndexType()==IndexType.PK))
{
throw new DbException(ixInfo.getIndexName() + " is not a Primary Key.");
}
ArrayList<IndexColInfo> ixCols=ixInfo.getIndexColInfoList();
sql.append("ALTER TABLE " + getFullName() + " ");
sql.append("ADD CONSTRAINT " + ixInfo.getIndexName() + " ");
sql.append("PRIMARY KEY (");
for (int i=0; i < ixCols.size(); i++)
{
if (i>0) {sql.append(",");}
sql.append(ixCols.get(i).getColumnName());
}
sql.append(")");
return sql.toString();
}
/**
* Generate String MAX_SID+ROWID
* @return MAX_SID+ROWID
* @throws DbException
*/
@Override
public String getNextSurrogateSql(String nkColName, int startNum) throws DbException {
return "ROW_NUMBER() OVER (ORDER BY " + nkColName + ") + " + startNum;
}
/**
* A flag to indentify if we need to create table/add partition in Default Table Space
* Not be used by SQL Server since there is no table space in SQL Server
*/
@Override
public void setInitFlag(boolean _flag) {
}
@Override
public int getMaxSurrogate() throws DbException {
if (m_metaObjInfo.getPrimaryKeyIsSurrogate()) {
String sql = "SELECT MAX(" + m_metaObjInfo.getPrimaryKeySurrogateColumnName() +
") AS MAX_SID FROM " + this.getName();
try {
return m_dbUtils.runIntQuery(sql);
} catch (SQLException e) {
throw new DbException (e.toString());
}
}
return 0;
}
/**
* Append is not utilized for SQL Server. Value is irrelevant. Call normal insertSelect
*/
@Override
public int insertSelect(String _selectSql, boolean _append) throws DbException {
return this.insertSelect(_selectSql);
}
/**
* Generates SQL like the following:
*
* SELECT
* CLM_HDR_SID,
* \'9\',
* \'|\' +
* (
* SELECT ICD9_DIAG_CD + \'|\'
* FROM D1_DAT_CLM_ICD9_DIAG_FAC A
* WHERE A.CLM_HDR_SID = SRC.CLM_HDR_SID
* AND A.DIAG_TYPE_CD IN (\'P\',\'S\')
* ORDER BY ICD9_DIAG_CD
* For XML PATH(\'\')
* )
* FROM D1_DAT_CLM_ICD9_DIAG_FAC SRC
* WHERE SRC.CLM_PRCSD_OR_PD_DT BETWEEN <Date1> AND <Date2>
* GROUP BY CLM_HDR_SID
*/
@Override
public int populateListAgg(String srcObjNm, String clmSkCol, String listCol, String diagVrsn, String whereClause)
throws DbException {
StringBuffer sql = new StringBuffer("SELECT ");
sql.append(clmSkCol + ", ") ;
sql.append(diagVrsn + ", ") ;
sql.append("\'|\' + (SELECT " + listCol + " + \'|\' ");
sql.append("FROM " + srcObjNm + " A ");
sql.append("WHERE A." + clmSkCol + " = SRC." + clmSkCol + " ");
sql.append("ORDER BY " + listCol + " ");
sql.append("FOR XML PATH(\'\')) ");
sql.append("FROM " + srcObjNm + " SRC ");
sql.append("WHERE " + whereClause + " ");
sql.append("GROUP BY " + clmSkCol);
return this.insertSelect(sql.toString());
}
}
';
$subst = "\1\3, \2";
$result = preg_replace($re, $subst, $str);
echo "The result of the substitution is ".$result;
Please keep in mind that these code samples are automatically generated and are not guaranteed to work. If you find any syntax errors, feel free to submit a bug report. For a full regex reference for PHP, please visit: http://php.net/manual/en/ref.pcre.php