0001: /*
0002:
0003: Loader - tool for transfering data from one JDBC source to another and
0004: doing transformations during copy.
0005:
0006: Copyright (C) 2002-2003 Together
0007:
0008: This library is free software; you can redistribute it and/or
0009: modify it under the terms of the GNU Lesser General Public
0010: License as published by the Free Software Foundation; either
0011: version 2.1 of the License, or (at your option) any later version.
0012:
0013: This library is distributed in the hope that it will be useful,
0014: but WITHOUT ANY WARRANTY; without even the implied warranty of
0015: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
0016: Lesser General Public License for more details.
0017:
0018: You should have received a copy of the GNU Lesser General Public
0019: License along with this library; if not, write to the Free Software
0020: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
0021:
0022: Loader.java
0023: Date: 03.03.2003.
0024: @version 2.1 alpha
0025: @author:
0026: Radoslav Dutina rale@prozone.co.yu
0027:
0028: */
0029: package org.webdocwf.util.loader;
0030:
0031: import java.sql.Connection;
0032: import java.sql.ResultSet;
0033: import java.sql.SQLException;
0034: import java.sql.Statement;
0035: import java.util.Calendar;
0036: import java.util.Date;
0037: import java.util.Hashtable;
0038:
0039: import org.webdocwf.util.loader.logging.Logger;
0040:
0041: /**
0042: *
0043: * DataCleaning class contain method for executing data cleaning process
0044: * @author Radoslav Dutina
0045: * @version 1.0
0046: */
0047: public class DataCleaning {
0048:
0049: private Logger logger;
0050: private Statement stmt;
0051: private ResultSet rsetTarget;
0052: private ResultSet rs;
0053: private Hashtable colNamesDataTypes = new Hashtable();
0054: private Hashtable colNamesDataLenght = new Hashtable();
0055: private String currentTableName = "";
0056:
0057: private Hashtable colNamesDataTypesUpdate = new Hashtable();
0058: private Hashtable colNamesDataLenghtUpdate = new Hashtable();
0059: private ResultSet rsUpdate;
0060:
0061: // Default values for log table
0062: private String logTableName = "LOGTABLENAME";
0063: private String logTable = "LOGTABLE";
0064: private String logColumnName = "LOGCOLUMNNAME";
0065: private String logRowNumber = "LOGROWNUMBER";
0066: private String logOriginalValue = "LOGORIGINALVALUE";
0067: private String logNewValue = "LOGNEWVALUE";
0068: private String logImportDefinitionName = "LOGIMPORTDEFINITIONNAME";
0069: private String logOperationName = "LOGOPERATIONNAME";
0070: private String logTypeName = "LOGTYPENAME";
0071: //ZK added this 2.9 2004, to support more informations in log table
0072: private String logTime = "LOGTIME";
0073: private String logFailedStatement = "LOGFAILEDSTATEMENT";
0074: //end
0075: private boolean logTableExists = false;
0076: private boolean checkIsDone = false;
0077:
0078: private ConfigReader configReader;
0079:
0080: /**
0081: * Constructor of DataCleaning class, without parameters.
0082: */
0083: public DataCleaning(ConfigReader configReader) {
0084: this .configReader = configReader;
0085: }
0086:
0087: /**
0088: * This method set the value of parameter logTableName
0089: * @param logTableName is value of parameter
0090: */
0091: public void setLogTableName(String logTableName) {
0092: this .logTableName = logTableName;
0093: }
0094:
0095: /**
0096: * This method set the value of parameter logTable
0097: * @param logTable is value of parameter
0098: */
0099: public void setLogTable(String logTable) {
0100: this .logTable = logTable;
0101: }
0102:
0103: /**
0104: * This method set the value of parameter logColumnName
0105: * @param logColumnName is value of parmeter
0106: */
0107: public void setLogColumnName(String logColumnName) {
0108: this .logColumnName = logColumnName;
0109: }
0110:
0111: /**
0112: * This method set the value of parameter logRowNumber
0113: * @param logRowNumber is value of parameter
0114: */
0115: public void setLogRowNumber(String logRowNumber) {
0116: this .logRowNumber = logRowNumber;
0117: }
0118:
0119: /**
0120: * This method set the value of parameter logOriginalValue
0121: * @param logOriginalValue is value of parameter
0122: */
0123: public void setLogOriginalValue(String logOriginalValue) {
0124: this .logOriginalValue = logOriginalValue;
0125: }
0126:
0127: /**
0128: * This method set the value of parameter logNewValue
0129: * @param logNewValue is value of parameter
0130: */
0131: public void setLogNewValue(String logNewValue) {
0132: this .logNewValue = logNewValue;
0133: }
0134:
0135: /**
0136: * This method set the value of parameter logImportDefinitionName
0137: * @param logImportDefinitionName is value of parameter
0138: */
0139: public void setLogImportDefinitionName(
0140: String logImportDefinitionName) {
0141: this .logImportDefinitionName = logImportDefinitionName;
0142: }
0143:
0144: /**
0145: * This method set the value of parameter logOperationName
0146: * @param logOperationName is value of parameter
0147: */
0148: public void setLogOperationName(String logOperationName) {
0149: this .logOperationName = logOperationName;
0150: }
0151:
0152: /**
0153: * This method set the value of parameter logTypeName
0154: * @param logTypeName is value of parameter
0155: */
0156: public void setLogTypeName(String logTypeName) {
0157: this .logTypeName = logTypeName;
0158: }
0159:
0160: /**
0161: * This method read value from parameter logTableName
0162: * @return value of parameter
0163: */
0164: public String getLogTableName() {
0165: return this .logTableName;
0166: }
0167:
0168: /**
0169: * This method read value from parameter logTable
0170: * @return value of parameter
0171: */
0172: public String getLogTable() {
0173: return this .logTable;
0174: }
0175:
0176: /**
0177: * This method read value from parameter logColumnName
0178: * @return value of parameter
0179: */
0180: public String getLogColumnName() {
0181: return this .logColumnName;
0182: }
0183:
0184: /**
0185: * This method read value from parameter logRowNumber
0186: * @return value of parameter
0187: */
0188: public String getLogRowNumber() {
0189: return this .logRowNumber;
0190: }
0191:
0192: /**
0193: * This method read value from parameter logOriginalValue
0194: * @return value of parameter
0195: */
0196: public String getLogOriginalValue() {
0197: return this .logOriginalValue;
0198: }
0199:
0200: /**
0201: * This method read value from parameter logNewValue
0202: * @return value of parameter
0203: */
0204: public String getLogNewValue() {
0205: return this .logNewValue;
0206: }
0207:
0208: /**
0209: * This method read value from parameter logImportDefinitionName
0210: * @return value of parameter
0211: */
0212: public String getLogImportDefinitionName() {
0213: return this .logImportDefinitionName;
0214: }
0215:
0216: /**
0217: * This method read value from parameter logImportDefinitionName
0218: * @return value of parameter
0219: */
0220: public String getLogOperationName() {
0221: return this .logOperationName;
0222: }
0223:
0224: /**
0225: * This method read value from parameter logTypeName
0226: * @return value of parameter
0227: */
0228: public String getLogTypeName() {
0229: return this .logTypeName;
0230: }
0231:
0232: /**
0233: * This method write message in to log table if insert/update fails
0234: * @param tableName is name of the table which is in the process
0235: * @param conn is connection to target database (table)
0236: * @param rowNumber is current row number
0237: * @param typeOfInsert is the parameter which may be 'insert' or 'update'
0238: * @param msg is error message
0239: * @param importDefinitionName is name of the import definition job
0240: */
0241: public void cleaningInsert(String tableName, Connection conn,
0242: int rowNumber, String typeOfInsert, String msg,
0243: String importDefinitionName, String logFailedStatement) {
0244: this .logger
0245: .write("full",
0246: "\tBecause DataCleaning features is turn on, some replacement were made:");
0247: this .logger.write("full", "\t " + typeOfInsert
0248: + ":FAILS: in table " + tableName + ", in row "
0249: + (rowNumber + 1) + " . " + msg);
0250: String newLogFailedStatement = logFailedStatement.replaceAll(
0251: "'", "");
0252: String logTime = "";
0253:
0254: try {
0255: logTime = getCurrentTime();
0256: String[] types = { "TABLE" };
0257: String catalogName = conn.getCatalog();
0258: stmt = conn.createStatement();
0259: msg = Utils.replaceAll(msg, "'", "''");
0260: ResultSet check = null;
0261: try {
0262: if (!checkIsDone) {
0263: this .logTableExists = true;
0264: Statement stmtt = conn.createStatement();
0265: try {
0266: stmtt.executeQuery("SELECT * FROM "
0267: + getLogTableName());
0268: } catch (Exception e) {
0269: this .logTableExists = false;
0270: }
0271: this .checkIsDone = true;
0272: stmtt.close();
0273: /*
0274: check = conn.getMetaData().getTables(catalogName, null, "LOGTABLENAME", types);
0275: this.checkIsDone = true;
0276: this.logTableExists = check.next();
0277: check.close();
0278: */
0279: }
0280: } catch (UnsupportedOperationException ex) {
0281: String message = "Error while trying to get meta data from target table."
0282: + "\n"
0283: + "\tMethod getMetaDatata().getTables() is not supported.";
0284: throw new SQLException(message);
0285: }
0286:
0287: if (this .logTableExists) {
0288:
0289: stmt.executeUpdate("INSERT INTO " + getLogTableName()
0290: + " (" + getLogImportDefinitionName() + ", "
0291: + getLogOperationName() + ", "
0292: + getLogTypeName() + ", " + getLogTable()
0293: + ", " + getLogColumnName() + ", "
0294: + getLogRowNumber() + ", "
0295: + getLogOriginalValue() + ", "
0296: + getLogNewValue() + ", "
0297: + getLogFailedStatement() + ", " + getLogTime()
0298: + ")" + " VALUES (" + "'"
0299: + importDefinitionName + "','" + typeOfInsert
0300: + "','ERROR','" + tableName + "','','"
0301: + (rowNumber + 1) + "','" + msg + "','"
0302: + typeOfInsert + " FAILS'" + "," + "'"
0303: + newLogFailedStatement + "'" + "," + "'"
0304: + logTime + "'" + ")");
0305:
0306: } else {
0307: this .logger.write("full", "\t " + getLogTableName()
0308: + " does not exists, or is invalid!");
0309: }
0310: // check.close();
0311: stmt.close();
0312: } catch (SQLException ex) {
0313:
0314: this .logger.write("full", "\t Error: " + ex.getMessage());
0315: }
0316:
0317: }
0318:
0319: /**
0320: * This method return current date and time
0321: * @return String time
0322: */
0323: private String getCurrentTime() {
0324: String logTime;
0325: Calendar time = Calendar.getInstance();
0326: Date dateAndTime = time.getTime();
0327: logTime = dateAndTime.toString();
0328: return logTime;
0329: }
0330:
0331: /**
0332: * This method write message in to log table if some data in sql statements has value
0333: * @param tableName is name of the table which is in the process
0334: * @param columnName is the name of column in current table
0335: * @param replacement is new value of data
0336: * @param rowNumber is current row number
0337: * @param conn is connection to target database (table)
0338: * @param typeOfInsert is the parameter which may be 'insert' or 'update'
0339: * @param importDefinitionName is name of the import definition job
0340: */
0341: public void cleaningColumnValues(String tableName,
0342: String columnName, String replacement, int rowNumber,
0343: Connection conn, String typeOfInsert,
0344: String importDefinitionName) {
0345: this .logger
0346: .write("full",
0347: "\tBecause DataCleaning features is turn on, some replacement were made:");
0348: this .logger.write("full", "\t " + typeOfInsert
0349: + ":REPLACED VALUES: In table " + tableName
0350: + ", column " + columnName
0351: + ", value 'null' were replaced");
0352: this .logger.write("full", "\t with value '" + replacement
0353: + "' (row number " + (rowNumber + 1) + ")");
0354: String logTime = "";
0355: String newLogFailedStatement = logFailedStatement.replaceAll(
0356: "'", "");
0357:
0358: try {
0359:
0360: logTime = getCurrentTime();
0361: String[] types = { "TABLE" };
0362: String catalogName = conn.getCatalog();
0363: stmt = conn.createStatement();
0364: ResultSet check = null;
0365: try {
0366: if (!checkIsDone) {
0367: this .logTableExists = true;
0368: Statement stmtt = conn.createStatement();
0369: try {
0370: stmtt.executeQuery("SELECT * FROM "
0371: + getLogTableName());
0372: } catch (Exception e) {
0373: this .logTableExists = false;
0374: }
0375: this .checkIsDone = true;
0376: stmtt.close();
0377: /*
0378: check = conn.getMetaData().getTables(catalogName, null, getLogTableName(), types);
0379: this.checkIsDone = true;
0380: this.logTableExists = check.next();
0381: check.close();
0382: */
0383: }
0384: } catch (UnsupportedOperationException ex) {
0385: String message = "Error while trying to get meta data from target table.";
0386: throw new SQLException(message);
0387: }
0388:
0389: if (this .logTableExists) {
0390: stmt.executeUpdate("INSERT INTO " + getLogTableName()
0391: + " (" + getLogImportDefinitionName() + ", "
0392: + getLogOperationName() + ", "
0393: + getLogTypeName() + ", " + getLogTable()
0394: + ", " + getLogColumnName() + ", "
0395: + getLogRowNumber() + ", "
0396: + getLogOriginalValue() + ", "
0397: + getLogNewValue() + ", "
0398: + getLogFailedStatement() + ", " + getLogTime()
0399: + ")" + " VALUES (" + "'"
0400: + importDefinitionName + "','" + typeOfInsert
0401: + "','REPLACE NULL VALUES','" + tableName
0402: + "','" + columnName + "','" + (rowNumber + 1)
0403: + "','" + "null" + "','" + replacement + "'"
0404: + "," + "'" + newLogFailedStatement + "'" + ","
0405: + "'" + logTime + "'" + ")");
0406: } else
0407: this .logger.write("full", "\t " + getLogTableName()
0408: + " does not exists, or is invalid!");
0409: // check.close();
0410: stmt.close();
0411: } catch (SQLException ex) {
0412: this .logger.write("full", "\t Error:" + ex.getMessage());
0413: }
0414: }
0415:
0416: /**
0417: * This method write message in to log table if relations between table faild to
0418: * insert/update
0419: * @param tableName is name of the table which is in the process
0420: * @param columnName is the name of column in current table
0421: * @param replacement is new value of data
0422: * @param dataType is type of relation column
0423: * @param rowNumber is current row number
0424: * @param conn is connection to target database (table)
0425: * @param typeOfInsert represents type of operation (insert or update)
0426: * @param currentVersion represents update version
0427: * @param oid define if the oid logic is present
0428: * @param importDefinitionName is name of the import definition job
0429: * @return value of parameter
0430: */
0431: public String cleaningRelationValues(String tableName,
0432: String columnName, String replacement, String dataType,
0433: int rowNumber, Connection conn, String typeOfInsert,
0434: int currentVersion, boolean oid,
0435: String importDefinitionName, String versionColumnName,
0436: String logFailedStatement) throws LoaderException {
0437:
0438: String[] types = { "TABLE" };
0439: String relValue = "";
0440: String addins = "";
0441: String prefix = "";
0442: String oidVersion = "";
0443: String newLogFailedStatement = logFailedStatement.replaceAll(
0444: "'", "");
0445:
0446: String logTime = "";
0447:
0448: if (oid) {
0449: // oidVersion=", version="+currentVersion;
0450: oidVersion = ", " + versionColumnName + "="
0451: + currentVersion;
0452: }
0453: if (typeOfInsert.equalsIgnoreCase("update")) {
0454: addins = oidVersion + " where ";
0455: prefix = " = ";
0456: } else {
0457: addins = "";
0458: }
0459: try {
0460: logTime = getCurrentTime();
0461: String catalogName = conn.getCatalog();
0462: stmt = conn.createStatement();
0463: ResultSet relations = null;
0464: ResultSet check = null;
0465:
0466: try {
0467: relations = conn.getMetaData().getColumns(catalogName,
0468: null, tableName, "%");
0469:
0470: if (!checkIsDone) {
0471: this .logTableExists = true;
0472: Statement stmtt = conn.createStatement();
0473: try {
0474: stmtt.executeQuery("SELECT * FROM "
0475: + getLogTableName());
0476: } catch (Exception e) {
0477: this .logTableExists = false;
0478: }
0479: this .checkIsDone = true;
0480: stmtt.close();
0481: /*
0482: check = conn.getMetaData().getTables(catalogName, null, getLogTableName(), types);
0483: this.checkIsDone = true;
0484: this.logTableExists = check.next();
0485: check.close();
0486: */
0487: }
0488: } catch (UnsupportedOperationException ex) {
0489: String message = "Error while trying to get meta data from target table.";
0490: throw new SQLException(message);
0491: }
0492:
0493: while (relations.next()) {
0494:
0495: if (columnName.equalsIgnoreCase(relations.getString(4))) {
0496:
0497: //ZK change from CheckType to targetConfigReader 7.5.2004
0498:
0499: try {
0500: if (configReader.isNumber(dataType)) {
0501: relValue = prefix + replacement + addins;
0502: } else {
0503: relValue = prefix + "'" + replacement + "'"
0504: + addins;
0505: }
0506: } catch (LoaderException e) {
0507:
0508: LoaderException le = new LoaderException(
0509: "Exception:", e);
0510: throw le;
0511: }
0512:
0513: if (this .logTableExists)
0514:
0515: stmt.executeUpdate("INSERT INTO "
0516: + getLogTableName() + " ("
0517: + getLogImportDefinitionName() + ", "
0518: + getLogOperationName() + ", "
0519: + getLogTypeName() + ", "
0520: + getLogTable() + ", "
0521: + getLogColumnName() + ", "
0522: + getLogRowNumber() + ", "
0523: + getLogOriginalValue() + ", "
0524: + getLogNewValue() + ", "
0525: + getLogFailedStatement() + ", "
0526: + getLogTime() + ")" + " VALUES ("
0527: + "'" + importDefinitionName + "','"
0528: + typeOfInsert
0529: + "','CLEANING RELATIONS','"
0530: + tableName + "','" + columnName
0531: + "','" + (rowNumber + 1) + "',"
0532: + "null,'" + replacement + "'" + ","
0533: + "'" + newLogFailedStatement + "'"
0534: + "," + "'" + logTime + "'" + ")");
0535:
0536: else {
0537:
0538: this .logger.write("full", "\t "
0539: + getLogTableName()
0540: + " does not exists, or is invalid!");
0541: }
0542: this .logger
0543: .write("full",
0544: "\tBecause DataCleaning features is turn on, some replacement were made:");
0545: this .logger.write("full", "\t " + typeOfInsert
0546: + ":RELATIONS: In table " + tableName
0547: + ", column " + columnName
0548: + ", value null were replaced");
0549: this .logger.write("full", "\t with value "
0550: + replacement + " (row number "
0551: + (rowNumber + 1) + ")");
0552:
0553: }
0554: }
0555: relations.close();
0556: stmt.close();
0557: } catch (SQLException ex) {
0558: this .logger.write("full", "\t Error:" + ex.getMessage());
0559: }
0560: return relValue;
0561: }
0562:
0563: /**
0564: * This method write message in to log table if some data must be truncated
0565: * @param tableName is name of the table which is in the process
0566: * @param conn is connection to target database (table)
0567: * @param strQuery is string representing sql statement
0568: * @param rowNumber is current row number
0569: * @param update is the parameter which may be 'insert' or 'update'
0570: * @param onErrorContinue is value of onErrorContinue attribute
0571: * @param importDefinitionName is name of the import definition job
0572: * @throws SQLException
0573: */
0574: public void cutingDataLenghtUpdate(String tableName,
0575: Connection conn, String strQuery, int rowNumber,
0576: String update, String onErrorContinue,
0577: String importDefinitionName, String logFailedStatement)
0578: throws SQLException, LoaderException {
0579:
0580: boolean end = false;
0581: boolean endTemp = false;
0582: SqlParser sqlParser = new SqlParser();
0583: String newLogFailedStatement = logFailedStatement.replaceAll(
0584: "'", "");
0585: String logTime = "";
0586: try {
0587: logTime = getCurrentTime();
0588: try {
0589: sqlParser.parse(update + strQuery);
0590: } catch (Exception e) {
0591: throw new SQLException(e.getMessage());
0592: }
0593: String[] columnNames = sqlParser.getColumnNames();
0594: String[] columnValues = sqlParser.getColumnValues();
0595:
0596: String[] types = { "TABLE" };
0597: String catalogName = conn.getCatalog();
0598: stmt = conn.createStatement();
0599:
0600: if (!this .currentTableName.equalsIgnoreCase(tableName)) {
0601: try {
0602: rsUpdate = conn.getMetaData().getColumns(
0603: catalogName, null, tableName, "%");
0604: } catch (UnsupportedOperationException ex) {
0605: String message = "Error while trying to get meta data from target table.";
0606: throw new SQLException(message);
0607: }
0608: colNamesDataTypesUpdate.clear();
0609: colNamesDataLenghtUpdate.clear();
0610: this .currentTableName = tableName;
0611: while (rsUpdate.next()) {
0612: String columnName = rsUpdate.getString(4)
0613: .toUpperCase();
0614: colNamesDataTypesUpdate.put(columnName, rsUpdate
0615: .getString(6));
0616: colNamesDataLenghtUpdate.put(columnName, rsUpdate
0617: .getString(7));
0618: }
0619: rsUpdate.close();
0620: }
0621:
0622: for (int i = 0; i < columnNames.length; i++) {
0623: String dataNameInQyery = columnNames[i];
0624: String dataValueInQyery = columnValues[i];
0625: String dataTypeName = (String) colNamesDataTypesUpdate
0626: .get(dataNameInQyery.toUpperCase());
0627: String dataTypeLenght = (String) colNamesDataLenghtUpdate
0628: .get(dataNameInQyery.toUpperCase());
0629: if (!dataValueInQyery.equalsIgnoreCase("null")) {
0630: // ZK change from CheckType to targetConfigReader 7.5.2004
0631: try {
0632:
0633: if (!configReader.isNumber(dataTypeName)) {
0634: if ((dataValueInQyery.length()) > Integer
0635: .parseInt(dataTypeLenght)) {
0636: String original = dataValueInQyery
0637: .substring(0, dataValueInQyery
0638: .length());
0639: String replacement = replaceQuote(
0640: original,
0641: Integer
0642: .parseInt(dataTypeLenght));
0643: update = replaceFirst(update,
0644: dataValueInQyery, replacement);
0645: ResultSet check = null;
0646: try {
0647: if (!checkIsDone) {
0648: this .logTableExists = true;
0649: Statement stmtt = conn
0650: .createStatement();
0651: try {
0652: stmtt
0653: .executeQuery("SELECT * FROM "
0654: + getLogTableName());
0655: } catch (Exception e) {
0656: this .logTableExists = false;
0657: }
0658: this .checkIsDone = true;
0659: stmtt.close();
0660: /*
0661: check = conn.getMetaData().getTables(catalogName, null, getLogTableName(), types);
0662: this.checkIsDone = true;
0663: this.logTableExists = check.next();
0664: check.close();
0665: */
0666: }
0667: } catch (UnsupportedOperationException ex) {
0668: String message = "Error while trying to get meta data from target table.";
0669: throw new SQLException(message);
0670: }
0671:
0672: if (this .logTableExists) {
0673: stmt
0674: .executeUpdate("INSERT INTO "
0675: + getLogTableName()
0676: + " ("
0677: + getLogImportDefinitionName()
0678: + ", "
0679: + getLogOperationName()
0680: + ", "
0681: + getLogTypeName()
0682: + ", "
0683: + getLogTable()
0684: + ", "
0685: + getLogColumnName()
0686: + ", "
0687: + getLogRowNumber()
0688: + ", "
0689: + getLogOriginalValue()
0690: + ", "
0691: + getLogNewValue()
0692: + ", "
0693: + getLogFailedStatement()
0694: + ", "
0695: + getLogTime()
0696: + ")"
0697: + " VALUES ("
0698: + "'"
0699: + importDefinitionName
0700: + "','UPDATE','CUT OFF DATA','"
0701: + tableName
0702: + "','"
0703: + dataNameInQyery
0704: + "','"
0705: + (rowNumber + 1)
0706: + "','"
0707: + original
0708: + "','"
0709: + replacement
0710: + "'"
0711: + ","
0712: + "'"
0713: + newLogFailedStatement
0714: + "'"
0715: + ","
0716: + "'"
0717: + logTime
0718: + "'"
0719: + ")");
0720:
0721: } else {
0722: this .logger
0723: .write(
0724: "full",
0725: "\t "
0726: + getLogTableName()
0727: + " does not exists, or is invalid");
0728: }
0729: this .logger
0730: .write("full",
0731: "\t Because dataCutOff attribute is true, some replacement were made:");
0732: this .logger.write("full",
0733: "\t UPDATE:CutOff: In table "
0734: + tableName
0735: + ", column "
0736: + dataNameInQyery
0737: + ", value "
0738: + dataValueInQyery
0739: + " were replaced");
0740: this .logger
0741: .write("full", "\t with value "
0742: + replacement
0743: + " (row number "
0744: + (rowNumber + 1) + ")");
0745:
0746: }
0747: }
0748: } catch (LoaderException e) {
0749: LoaderException le = new LoaderException(
0750: "Exception:", (Throwable) e);
0751: throw le;
0752:
0753: }
0754: }
0755: }
0756: stmt.executeUpdate(update + strQuery);
0757: stmt.close();
0758:
0759: } catch (SQLException ex) {
0760: if (onErrorContinue.equalsIgnoreCase("true"))
0761: cleaningInsert(tableName, conn, rowNumber, "UPDATE",
0762: "Update fails. Message: " + ex.getMessage(),
0763: importDefinitionName, logFailedStatement);
0764: else {
0765: LoaderException le = new LoaderException(
0766: "SQLException: ", (Throwable) ex);
0767: this .logger.write("full", "\tError in SQL statement: "
0768: + le.getCause());
0769: // this.logger.write("full", "\tError : Because OnErrorContinue Attribute is false, application is terminated");
0770: throw ex;
0771:
0772: }
0773: }
0774: }
0775:
0776: /**
0777: * This method write message in to log table if some data must be truncated
0778: * @param tableName is name of the table which is in the process
0779: * @param conn is connection to target database (table)
0780: * @param strQuery is string represented sql statement
0781: * @param rowNumber is current row number
0782: * @param onErrorContinue represents onErrorContinue tag
0783: * @param msg represents error message
0784: * @param importDefinitionName is name of the import definition job
0785: * @throws SQLException
0786: */
0787: public void cutingDataLenght(String tableName, Connection conn,
0788: String strQuery, int rowNumber, String onErrorContinue,
0789: String msg, String importDefinitionName,
0790: String logFailedStatement) throws SQLException {
0791: boolean end = false;
0792: boolean endTemp = false;
0793: SqlParser sqlParser = new SqlParser();
0794: String newLogFailedStatement = logFailedStatement.replaceAll(
0795: "'", "");
0796: String logTime = "";
0797: try {
0798: logTime = getCurrentTime();
0799: try {
0800: sqlParser.parse(strQuery);
0801: } catch (Exception e) {
0802: throw new SQLException(e.getMessage());
0803: }
0804: String[] columnNames = sqlParser.getColumnNames();
0805: String[] columnValues = sqlParser.getColumnValues();
0806:
0807: String[] types = { "TABLE" };
0808: String catalogName = conn.getCatalog();
0809: stmt = conn.createStatement();
0810:
0811: if (!this .currentTableName.equalsIgnoreCase(tableName)) {
0812: try {
0813: rs = conn.getMetaData().getColumns(catalogName,
0814: null, tableName, "%");
0815: } catch (UnsupportedOperationException ex) {
0816: String message = "Error while trying to get meta data from target table.";
0817: throw new SQLException(message);
0818: }
0819: colNamesDataTypes.clear();
0820: colNamesDataLenght.clear();
0821: this .currentTableName = tableName;
0822: while (rs.next()) {
0823: String columnName = rs.getString(4).toUpperCase();
0824: colNamesDataTypes.put(columnName, rs.getString(6));
0825: colNamesDataLenght.put(columnName, rs.getString(7));
0826: }
0827: }
0828:
0829: for (int i = 0; i < columnNames.length; i++) {
0830: String dataNameInQyery = columnNames[i];
0831: String dataValueInQyery = columnValues[i];
0832: String dataTypeName = (String) colNamesDataTypes
0833: .get(dataNameInQyery.toUpperCase());
0834: String dataTypeLenght = (String) colNamesDataLenght
0835: .get(dataNameInQyery.toUpperCase());
0836: if (!dataValueInQyery.equalsIgnoreCase("null")) {
0837: // ZK change from CheckType to targetConfigReader 7.5.2004
0838:
0839: try {
0840: if (!configReader.isNumber(dataTypeName)) {
0841: if ((dataValueInQyery.length()) > Integer
0842: .parseInt(dataTypeLenght)) {
0843: String original = dataValueInQyery
0844: .substring(0, dataValueInQyery
0845: .length());
0846: String replacement = replaceQuote(
0847: original,
0848: Integer
0849: .parseInt(dataTypeLenght));
0850: strQuery = replaceFirst(strQuery,
0851: dataValueInQyery, replacement);
0852: ResultSet check = null;
0853: try {
0854: if (!checkIsDone) {
0855: this .logTableExists = true;
0856: Statement stmtt = conn
0857: .createStatement();
0858: try {
0859: stmtt
0860: .executeQuery("SELECT * FROM "
0861: + getLogTableName());
0862: } catch (Exception e) {
0863: this .logTableExists = false;
0864: }
0865: this .checkIsDone = true;
0866: stmtt.close();
0867: /*
0868: check = conn.getMetaData().getTables(catalogName, null, getLogTableName(), types);
0869: this.checkIsDone = true;
0870: this.logTableExists = check.next();
0871: check.close();
0872: */
0873: }
0874: } catch (UnsupportedOperationException ex) {
0875: String message = "Error while trying to get meta data from target table.";
0876: throw new SQLException(message);
0877: }
0878:
0879: if (this .logTableExists) {
0880: stmt
0881: .executeUpdate("INSERT INTO "
0882: + getLogTableName()
0883: + " ("
0884: + getLogImportDefinitionName()
0885: + ", "
0886: + getLogOperationName()
0887: + ", "
0888: + getLogTypeName()
0889: + ", "
0890: + getLogTable()
0891: + ", "
0892: + getLogColumnName()
0893: + ", "
0894: + getLogRowNumber()
0895: + ", "
0896: + getLogOriginalValue()
0897: + ", "
0898: + getLogNewValue()
0899: + ", "
0900: + getLogFailedStatement()
0901: + ", "
0902: + getLogTime()
0903: + ")"
0904: + " VALUES ("
0905: + "'"
0906: + importDefinitionName
0907: + "','INSERT','CUT OFF DATA','"
0908: + tableName
0909: + "','"
0910: + dataNameInQyery
0911: + "','"
0912: + (rowNumber + 1)
0913: + "','"
0914: + original
0915: + "','"
0916: + replacement
0917: + "'"
0918: + ","
0919: + "'"
0920: + newLogFailedStatement
0921: + "'"
0922: + ","
0923: + "'"
0924: + logTime
0925: + "'"
0926: + ")");
0927:
0928: } else {
0929: this .logger
0930: .write(
0931: "full",
0932: "\t "
0933: + getLogTableName()
0934: + " does not exists, or is invalid");
0935:
0936: }
0937: // check.close();
0938: this .logger
0939: .write("full",
0940: "\t Because dataCutOff attribute is true, some replacement were made:");
0941: this .logger.write("full",
0942: "\t INSERT:CutOff: In table "
0943: + tableName
0944: + ", column "
0945: + dataNameInQyery
0946: + ", value "
0947: + dataValueInQyery
0948: + " were replaced");
0949: this .logger
0950: .write("full", "\t with value "
0951: + replacement
0952: + " (row number "
0953: + (rowNumber + 1) + ")");
0954: }
0955: }
0956:
0957: } catch (LoaderException e) {
0958: LoaderException le = new LoaderException(
0959: "Exception:", (Throwable) e);
0960:
0961: }
0962: }
0963: }
0964:
0965: stmt.executeUpdate(strQuery);
0966: stmt.close();
0967: } catch (SQLException ex) {
0968: if (onErrorContinue.equalsIgnoreCase("true"))
0969: cleaningInsert(tableName, conn, rowNumber, "INSERT",
0970: msg, importDefinitionName, logFailedStatement);
0971: else {
0972: LoaderException le = new LoaderException(
0973: "SQLException: ", (Throwable) ex);
0974: this .logger.write("full", "\tError in SQL statement: "
0975: + le.getCause());
0976: // this.logger.write("full", "\tError : Because OnErrorContinue Attribute is false, application is terminated");
0977: throw ex;
0978:
0979: }
0980: }
0981: }
0982:
0983: private String replaceQuote(String replacement, int length) {
0984: replacement = Utils.replaceAll(replacement, "''", "'");
0985: replacement = replacement.substring(0, length);
0986: int index = replacement.indexOf("'");
0987: if (index != -1) {
0988: replacement = Utils.replaceAll(replacement, "'", "''");
0989: }
0990: return replacement;
0991: }
0992:
0993: private String replaceFirst(String input, String forReplace,
0994: String replaceWith) {
0995: String retVal = input;
0996: int start = input.indexOf(forReplace);
0997: int end = start + forReplace.length();
0998: if (start != -1) {
0999: retVal = input.substring(0, start) + replaceWith
1000: + input.substring(end);
1001: }
1002: return retVal;
1003: }
1004:
1005: /**
1006: * Set Logger object
1007: * @param logger Logger object which is used for log file
1008: */
1009: public void setLogger(Logger logger) {
1010: this .logger = logger;
1011: }
1012:
1013: /**
1014: * This method set value of primary key for column which data wasn't succesufuly loaded
1015: * @param String primary key
1016: */
1017: public void setLogPrimaryKeyValue(String string) {
1018: this .logFailedStatement = string;
1019: }
1020:
1021: /**
1022: * This method set time when log was occured
1023: * @param String time
1024: */
1025: public void setLogTime(String string) {
1026: this .logTime = string;
1027: }
1028:
1029: /**
1030: * This method returns value of primary key for column which data wasn't succesufuly loaded
1031: * @return String value which is value of primary key for column
1032: */
1033: public String getLogFailedStatement() {
1034: return this .logFailedStatement;
1035: }
1036:
1037: /**
1038: * This method returns time when log was occured
1039: * @return String value which is time when log is inserted in table
1040: */
1041: public String getLogTime() {
1042: return this.logTime;
1043: }
1044:
1045: }
|