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