KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > webdocwf > util > loader > DataCleaning


1 /*
2
3   Loader - tool for transfering data from one JDBC source to another and
4   doing transformations during copy.
5
6     Copyright (C) 2002-2003 Together
7
8     This library is free software; you can redistribute it and/or
9     modify it under the terms of the GNU Lesser General Public
10     License as published by the Free Software Foundation; either
11     version 2.1 of the License, or (at your option) any later version.
12
13     This library is distributed in the hope that it will be useful,
14     but WITHOUT ANY WARRANTY; without even the implied warranty of
15     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16     Lesser General Public License for more details.
17
18     You should have received a copy of the GNU Lesser General Public
19     License along with this library; if not, write to the Free Software
20     Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
21
22  Loader.java
23  Date: 03.03.2003.
24  @version 2.1 alpha
25  @author:
26  Radoslav Dutina rale@prozone.co.yu
27
28 */

29 package org.webdocwf.util.loader;
30
31 import java.sql.Connection JavaDoc;
32 import java.sql.ResultSet JavaDoc;
33 import java.sql.SQLException JavaDoc;
34 import java.sql.Statement JavaDoc;
35 import java.util.Calendar JavaDoc;
36 import java.util.Date JavaDoc;
37 import java.util.Hashtable JavaDoc;
38
39 import org.webdocwf.util.loader.logging.Logger;
40
41 /**
42  *
43  * DataCleaning class contain method for executing data cleaning process
44  * @author Radoslav Dutina
45  * @version 1.0
46  */

47 public class DataCleaning {
48
49     private Logger logger;
50     private Statement JavaDoc stmt;
51     private ResultSet JavaDoc rsetTarget;
52     private ResultSet JavaDoc rs;
53     private Hashtable JavaDoc colNamesDataTypes = new Hashtable JavaDoc();
54     private Hashtable JavaDoc colNamesDataLenght = new Hashtable JavaDoc();
55     private String JavaDoc currentTableName = "";
56
57     private Hashtable JavaDoc colNamesDataTypesUpdate = new Hashtable JavaDoc();
58     private Hashtable JavaDoc colNamesDataLenghtUpdate = new Hashtable JavaDoc();
59     private ResultSet JavaDoc rsUpdate;
60
61     // Default values for log table
62
private String JavaDoc logTableName = "LOGTABLENAME";
63     private String JavaDoc logTable = "LOGTABLE";
64     private String JavaDoc logColumnName = "LOGCOLUMNNAME";
65     private String JavaDoc logRowNumber = "LOGROWNUMBER";
66     private String JavaDoc logOriginalValue = "LOGORIGINALVALUE";
67     private String JavaDoc logNewValue = "LOGNEWVALUE";
68     private String JavaDoc logImportDefinitionName = "LOGIMPORTDEFINITIONNAME";
69     private String JavaDoc logOperationName = "LOGOPERATIONNAME";
70     private String JavaDoc logTypeName = "LOGTYPENAME";
71     //ZK added this 2.9 2004, to support more informations in log table
72
private String JavaDoc logTime = "LOGTIME";
73     private String JavaDoc logFailedStatement = "LOGFAILEDSTATEMENT";
74         //end
75
private boolean logTableExists = false;
76     private boolean checkIsDone = false;
77
78     private ConfigReader configReader;
79
80     /**
81      * Constructor of DataCleaning class, without parameters.
82      */

83     public DataCleaning(ConfigReader configReader) {
84         this.configReader = configReader;
85     }
86
87     /**
88      * This method set the value of parameter logTableName
89      * @param logTableName is value of parameter
90      */

91     public void setLogTableName(String JavaDoc logTableName) {
92         this.logTableName = logTableName;
93     }
94
95     /**
96      * This method set the value of parameter logTable
97      * @param logTable is value of parameter
98      */

99     public void setLogTable(String JavaDoc logTable) {
100         this.logTable = logTable;
101     }
102
103     /**
104      * This method set the value of parameter logColumnName
105      * @param logColumnName is value of parmeter
106      */

107     public void setLogColumnName(String JavaDoc logColumnName) {
108         this.logColumnName = logColumnName;
109     }
110
111     /**
112      * This method set the value of parameter logRowNumber
113      * @param logRowNumber is value of parameter
114      */

115     public void setLogRowNumber(String JavaDoc logRowNumber) {
116         this.logRowNumber = logRowNumber;
117     }
118
119     /**
120      * This method set the value of parameter logOriginalValue
121      * @param logOriginalValue is value of parameter
122      */

123     public void setLogOriginalValue(String JavaDoc logOriginalValue) {
124         this.logOriginalValue = logOriginalValue;
125     }
126
127     /**
128      * This method set the value of parameter logNewValue
129      * @param logNewValue is value of parameter
130      */

131     public void setLogNewValue(String JavaDoc logNewValue) {
132         this.logNewValue = logNewValue;
133     }
134
135     /**
136      * This method set the value of parameter logImportDefinitionName
137      * @param logImportDefinitionName is value of parameter
138      */

139     public void setLogImportDefinitionName(String JavaDoc logImportDefinitionName) {
140         this.logImportDefinitionName = logImportDefinitionName;
141     }
142
143     /**
144      * This method set the value of parameter logOperationName
145      * @param logOperationName is value of parameter
146      */

147     public void setLogOperationName(String JavaDoc logOperationName) {
148         this.logOperationName = logOperationName;
149     }
150
151     /**
152      * This method set the value of parameter logTypeName
153      * @param logTypeName is value of parameter
154      */

155     public void setLogTypeName(String JavaDoc logTypeName) {
156         this.logTypeName = logTypeName;
157     }
158
159     /**
160      * This method read value from parameter logTableName
161      * @return value of parameter
162      */

163     public String JavaDoc getLogTableName() {
164         return this.logTableName;
165     }
166
167     /**
168      * This method read value from parameter logTable
169      * @return value of parameter
170      */

171     public String JavaDoc getLogTable() {
172         return this.logTable;
173     }
174
175     /**
176      * This method read value from parameter logColumnName
177      * @return value of parameter
178      */

179     public String JavaDoc getLogColumnName() {
180         return this.logColumnName;
181     }
182
183     /**
184      * This method read value from parameter logRowNumber
185      * @return value of parameter
186      */

187     public String JavaDoc getLogRowNumber() {
188         return this.logRowNumber;
189     }
190
191     /**
192      * This method read value from parameter logOriginalValue
193      * @return value of parameter
194      */

195     public String JavaDoc getLogOriginalValue() {
196         return this.logOriginalValue;
197     }
198
199     /**
200      * This method read value from parameter logNewValue
201      * @return value of parameter
202      */

203     public String JavaDoc getLogNewValue() {
204         return this.logNewValue;
205     }
206
207     /**
208      * This method read value from parameter logImportDefinitionName
209      * @return value of parameter
210      */

211     public String JavaDoc getLogImportDefinitionName() {
212         return this.logImportDefinitionName;
213     }
214
215     /**
216      * This method read value from parameter logImportDefinitionName
217      * @return value of parameter
218      */

219     public String JavaDoc getLogOperationName() {
220         return this.logOperationName;
221     }
222
223     /**
224      * This method read value from parameter logTypeName
225      * @return value of parameter
226      */

227     public String JavaDoc getLogTypeName() {
228         return this.logTypeName;
229     }
230
231     /**
232      * This method write message in to log table if insert/update fails
233      * @param tableName is name of the table which is in the process
234      * @param conn is connection to target database (table)
235      * @param rowNumber is current row number
236      * @param typeOfInsert is the parameter which may be 'insert' or 'update'
237      * @param msg is error message
238      * @param importDefinitionName is name of the import definition job
239      */

240     public void cleaningInsert(String JavaDoc tableName, Connection JavaDoc conn, int rowNumber, String JavaDoc typeOfInsert, String JavaDoc msg, String JavaDoc importDefinitionName, String JavaDoc logFailedStatement) {
241         this.logger.write("full", "\tBecause DataCleaning features is turn on, some replacement were made:");
242         this.logger.write("full", "\t " + typeOfInsert + ":FAILS: in table " + tableName + ", in row " + (rowNumber + 1) + " . " + msg);
243                 String JavaDoc newLogFailedStatement =logFailedStatement.replaceAll("'","");
244                 String JavaDoc logTime = "";
245              
246         try {
247                 logTime = getCurrentTime();
248             String JavaDoc[] types = { "TABLE" };
249             String JavaDoc catalogName = conn.getCatalog();
250             stmt = conn.createStatement();
251             msg = Utils.replaceAll(msg, "'", "''");
252             ResultSet JavaDoc check = null;
253             try {
254                 if (!checkIsDone) {
255                               this.logTableExists = true;
256                                         Statement JavaDoc stmtt = conn.createStatement();
257                       try {
258                         stmtt.executeQuery("SELECT * FROM "+getLogTableName());
259                     } catch (Exception JavaDoc e) {
260                         this.logTableExists = false;
261                     }
262                                         this.checkIsDone = true;
263                                         stmtt.close();
264                       /*
265                     check = conn.getMetaData().getTables(catalogName, null, "LOGTABLENAME", types);
266                     this.checkIsDone = true;
267                     this.logTableExists = check.next();
268                     check.close();
269                     */

270                 }
271             } catch (UnsupportedOperationException JavaDoc ex) {
272                 String JavaDoc message = "Error while trying to get meta data from target table." + "\n" + "\tMethod getMetaDatata().getTables() is not supported.";
273                 throw new SQLException JavaDoc(message);
274             }
275             
276             if (this.logTableExists) {
277                
278                      stmt.executeUpdate("INSERT INTO " + getLogTableName() + " ("
279                         + getLogImportDefinitionName() + ", " + getLogOperationName()
280                         + ", " + getLogTypeName() + ", " + getLogTable() + ", "
281                         + getLogColumnName() + ", " + getLogRowNumber() + ", "
282                         + getLogOriginalValue() + ", " + getLogNewValue() + ", "
283                         + getLogFailedStatement() +", " +getLogTime()+ ")"
284                         + " VALUES (" + "'" + importDefinitionName + "','" + typeOfInsert
285                         + "','ERROR','" + tableName + "','','" + (rowNumber + 1)
286                         + "','" + msg + "','" + typeOfInsert + " FAILS'" + ","+"'"+newLogFailedStatement+"'"+"," +"'" +logTime+"'" +")");
287                 
288             } else {
289                 this.logger.write("full", "\t " + getLogTableName() + " does not exists, or is invalid!");
290             }
291             // check.close();
292
stmt.close();
293         } catch (SQLException JavaDoc ex) {
294     
295             this.logger.write("full", "\t Error: " + ex.getMessage());
296         }
297
298     }
299         /**
300          * This method return current date and time
301          * @return String time
302          */

303             private String JavaDoc getCurrentTime() {
304                 String JavaDoc logTime;
305                 Calendar JavaDoc time = Calendar.getInstance();
306                 Date JavaDoc dateAndTime = time.getTime();
307                 logTime = dateAndTime.toString();
308                 return logTime;
309         }
310
311     /**
312      * This method write message in to log table if some data in sql statements has value
313      * @param tableName is name of the table which is in the process
314      * @param columnName is the name of column in current table
315      * @param replacement is new value of data
316      * @param rowNumber is current row number
317      * @param conn is connection to target database (table)
318      * @param typeOfInsert is the parameter which may be 'insert' or 'update'
319      * @param importDefinitionName is name of the import definition job
320      */

321     public void cleaningColumnValues(String JavaDoc tableName, String JavaDoc columnName, String JavaDoc replacement, int rowNumber, Connection JavaDoc conn, String JavaDoc typeOfInsert, String JavaDoc importDefinitionName) {
322         this.logger.write("full", "\tBecause DataCleaning features is turn on, some replacement were made:");
323         this.logger.write("full", "\t " + typeOfInsert + ":REPLACED VALUES: In table " + tableName + ", column " + columnName + ", value 'null' were replaced");
324         this.logger.write("full", "\t with value '" + replacement + "' (row number " + (rowNumber + 1) + ")");
325                 String JavaDoc logTime = "";
326                 String JavaDoc newLogFailedStatement =logFailedStatement.replaceAll("'","");
327                  
328         try {
329             
330                 logTime = getCurrentTime();
331             String JavaDoc[] types = { "TABLE" };
332             String JavaDoc catalogName = conn.getCatalog();
333             stmt = conn.createStatement();
334             ResultSet JavaDoc check = null;
335             try {
336                 if (!checkIsDone) {
337                                             this.logTableExists = true;
338                                             Statement JavaDoc stmtt = conn.createStatement();
339                                           try {
340                                                 stmtt.executeQuery("SELECT * FROM "+getLogTableName());
341                                             } catch (Exception JavaDoc e) {
342                                                 this.logTableExists = false;
343                                             }
344                                             this.checkIsDone = true;
345                                             stmtt.close();
346                                         /*
347                     check = conn.getMetaData().getTables(catalogName, null, getLogTableName(), types);
348                     this.checkIsDone = true;
349                     this.logTableExists = check.next();
350                     check.close();
351                     */

352                 }
353             } catch (UnsupportedOperationException JavaDoc ex) {
354                 String JavaDoc message = "Error while trying to get meta data from target table.";
355                 throw new SQLException JavaDoc(message);
356             }
357     
358             if (this.logTableExists) {
359                 stmt.executeUpdate("INSERT INTO " + getLogTableName() + " ("
360                 + getLogImportDefinitionName() + ", " + getLogOperationName()
361                 + ", " + getLogTypeName() + ", " + getLogTable() + ", "
362                 + getLogColumnName() + ", " + getLogRowNumber() + ", "
363                 + getLogOriginalValue() + ", " + getLogNewValue() + ", "
364                                 + getLogFailedStatement() +", " +getLogTime()+ ")"
365                 + " VALUES (" + "'" + importDefinitionName + "','" + typeOfInsert
366                 + "','REPLACE NULL VALUES','" + tableName + "','"
367                 + columnName + "','" + (rowNumber + 1) + "','" + "null"
368                 + "','" + replacement + "'" + ","+"'"+newLogFailedStatement+"'"+","+"'"+logTime+"'"+")");
369             } else
370                 this.logger.write("full", "\t " + getLogTableName() + " does not exists, or is invalid!");
371             // check.close();
372
stmt.close();
373         } catch (SQLException JavaDoc ex) {
374             this.logger.write("full", "\t Error:" + ex.getMessage());
375         }
376     }
377
378     /**
379      * This method write message in to log table if relations between table faild to
380      * insert/update
381      * @param tableName is name of the table which is in the process
382      * @param columnName is the name of column in current table
383      * @param replacement is new value of data
384      * @param dataType is type of relation column
385      * @param rowNumber is current row number
386      * @param conn is connection to target database (table)
387      * @param typeOfInsert represents type of operation (insert or update)
388      * @param currentVersion represents update version
389      * @param oid define if the oid logic is present
390      * @param importDefinitionName is name of the import definition job
391      * @return value of parameter
392      */

393     public String JavaDoc cleaningRelationValues(String JavaDoc tableName, String JavaDoc columnName, String JavaDoc replacement, String JavaDoc dataType, int rowNumber, Connection JavaDoc conn, String JavaDoc typeOfInsert, int currentVersion, boolean oid, String JavaDoc importDefinitionName, String JavaDoc versionColumnName, String JavaDoc logFailedStatement) throws LoaderException {
394
395         String JavaDoc[] types = { "TABLE" };
396         String JavaDoc relValue = "";
397         String JavaDoc addins = "";
398         String JavaDoc prefix = "";
399         String JavaDoc oidVersion = "";
400                 String JavaDoc newLogFailedStatement =logFailedStatement.replaceAll("'","");
401                 
402                 String JavaDoc logTime = "";
403             
404         if (oid) {
405             // oidVersion=", version="+currentVersion;
406
oidVersion = ", " + versionColumnName + "=" + currentVersion;
407         }
408         if (typeOfInsert.equalsIgnoreCase("update")) {
409             addins = oidVersion + " where ";
410             prefix = " = ";
411         } else {
412             addins = "";
413         }
414         try {
415                 logTime = getCurrentTime();
416             String JavaDoc catalogName = conn.getCatalog();
417             stmt = conn.createStatement();
418             ResultSet JavaDoc relations = null;
419             ResultSet JavaDoc check = null;
420             
421             try {
422                 relations = conn.getMetaData().getColumns(catalogName, null, tableName, "%");
423
424                 if (!checkIsDone) {
425                                             this.logTableExists = true;
426                                             Statement JavaDoc stmtt = conn.createStatement();
427                                           try {
428                                             stmtt.executeQuery("SELECT * FROM "+getLogTableName());
429                                         } catch (Exception JavaDoc e) {
430                                             this.logTableExists = false;
431                                         }
432                                         this.checkIsDone = true;
433                                         stmtt.close();
434                     /*
435                     check = conn.getMetaData().getTables(catalogName, null, getLogTableName(), types);
436                     this.checkIsDone = true;
437                     this.logTableExists = check.next();
438                     check.close();
439                     */

440                 }
441             } catch (UnsupportedOperationException JavaDoc ex) {
442                 String JavaDoc message = "Error while trying to get meta data from target table.";
443                 throw new SQLException JavaDoc(message);
444             }
445
446             while (relations.next()) {
447                             
448                 if (columnName.equalsIgnoreCase(relations.getString(4))) {
449                         
450                             
451                     //ZK change from CheckType to targetConfigReader 7.5.2004
452

453                     try {
454                         if (configReader.isNumber(dataType)) {
455                             relValue = prefix + replacement + addins;
456                         } else {
457                             relValue = prefix + "'" + replacement + "'" + addins;
458                         }
459                     } catch (LoaderException e) {
460
461                         LoaderException le = new LoaderException("Exception:", e);
462                         throw le;
463                     }
464                     
465                     if (this.logTableExists)
466                      
467                       stmt.executeUpdate("INSERT INTO " + getLogTableName() + " ("
468                         + getLogImportDefinitionName() + ", " + getLogOperationName()
469                         + ", " + getLogTypeName() + ", " + getLogTable() + ", "
470                         + getLogColumnName() + ", " + getLogRowNumber() + ", "
471                         + getLogOriginalValue() + ", " + getLogNewValue() + ", "
472                                 + getLogFailedStatement() +", " +getLogTime()+ ")"
473                         + " VALUES (" + "'" + importDefinitionName + "','" + typeOfInsert
474                         + "','CLEANING RELATIONS','" + tableName + "','" + columnName
475                         + "','" + (rowNumber + 1) + "'," + "null,'" + replacement + "'" + ","+"'"+newLogFailedStatement+"'"+","+"'"+logTime+"'"+")");
476
477                     else {
478                                         
479                         this.logger.write("full", "\t " + getLogTableName() + " does not exists, or is invalid!");
480                     }
481                     this.logger.write("full", "\tBecause DataCleaning features is turn on, some replacement were made:");
482                     this.logger.write("full", "\t " + typeOfInsert + ":RELATIONS: In table " + tableName + ", column " + columnName + ", value null were replaced");
483                     this.logger.write("full", "\t with value " + replacement + " (row number " + (rowNumber + 1) + ")");
484
485                 }
486             }
487             relations.close();
488             stmt.close();
489         } catch (SQLException JavaDoc ex) {
490             this.logger.write("full", "\t Error:" + ex.getMessage());
491         }
492         return relValue;
493     }
494
495     /**
496      * This method write message in to log table if some data must be truncated
497      * @param tableName is name of the table which is in the process
498      * @param conn is connection to target database (table)
499      * @param strQuery is string representing sql statement
500      * @param rowNumber is current row number
501      * @param update is the parameter which may be 'insert' or 'update'
502      * @param onErrorContinue is value of onErrorContinue attribute
503      * @param importDefinitionName is name of the import definition job
504      * @throws SQLException
505      */

506     public void cutingDataLenghtUpdate(String JavaDoc tableName, Connection JavaDoc conn, String JavaDoc strQuery, int rowNumber, String JavaDoc update, String JavaDoc onErrorContinue, String JavaDoc importDefinitionName, String JavaDoc logFailedStatement) throws SQLException JavaDoc, LoaderException {
507
508         boolean end = false;
509         boolean endTemp = false;
510         SqlParser sqlParser = new SqlParser();
511                 String JavaDoc newLogFailedStatement =logFailedStatement.replaceAll("'","");
512                 String JavaDoc logTime = "";
513         try {
514                 logTime = getCurrentTime();
515             try {
516                 sqlParser.parse(update + strQuery);
517             } catch (Exception JavaDoc e) {
518                 throw new SQLException JavaDoc(e.getMessage());
519             }
520             String JavaDoc[] columnNames = sqlParser.getColumnNames();
521             String JavaDoc[] columnValues = sqlParser.getColumnValues();
522
523             String JavaDoc[] types = { "TABLE" };
524             String JavaDoc catalogName = conn.getCatalog();
525             stmt = conn.createStatement();
526
527             if (!this.currentTableName.equalsIgnoreCase(tableName)) {
528                 try {
529                     rsUpdate = conn.getMetaData().getColumns(catalogName, null, tableName, "%");
530                 } catch (UnsupportedOperationException JavaDoc ex) {
531                     String JavaDoc message = "Error while trying to get meta data from target table.";
532                     throw new SQLException JavaDoc(message);
533                 }
534                 colNamesDataTypesUpdate.clear();
535                 colNamesDataLenghtUpdate.clear();
536                 this.currentTableName = tableName;
537                 while (rsUpdate.next()) {
538                     String JavaDoc columnName = rsUpdate.getString(4).toUpperCase();
539                     colNamesDataTypesUpdate.put(columnName, rsUpdate.getString(6));
540                     colNamesDataLenghtUpdate.put(columnName, rsUpdate.getString(7));
541                 }
542                 rsUpdate.close();
543             }
544
545             for (int i = 0; i < columnNames.length; i++) {
546                 String JavaDoc dataNameInQyery = columnNames[i];
547                 String JavaDoc dataValueInQyery = columnValues[i];
548                 String JavaDoc dataTypeName = (String JavaDoc) colNamesDataTypesUpdate.get(dataNameInQyery.toUpperCase());
549                 String JavaDoc dataTypeLenght = (String JavaDoc) colNamesDataLenghtUpdate.get(dataNameInQyery.toUpperCase());
550                 if (!dataValueInQyery.equalsIgnoreCase("null")) {
551                     // ZK change from CheckType to targetConfigReader 7.5.2004
552
try {
553
554                         if (!configReader.isNumber(dataTypeName)) {
555                             if ((dataValueInQyery.length()) > Integer.parseInt(dataTypeLenght)) {
556                                 String JavaDoc original = dataValueInQyery.substring(0, dataValueInQyery.length());
557                                 String JavaDoc replacement = replaceQuote(original, Integer.parseInt(dataTypeLenght));
558                                 update = replaceFirst(update, dataValueInQyery, replacement);
559                                 ResultSet JavaDoc check = null;
560                                 try {
561                                     if (!checkIsDone) {
562                                                                                 this.logTableExists = true;
563                                                                                 Statement JavaDoc stmtt = conn.createStatement();
564                                                                               try {
565                                                                                 stmtt.executeQuery("SELECT * FROM "+getLogTableName());
566                                                                             } catch (Exception JavaDoc e) {
567                                                                                 this.logTableExists = false;
568                                                                             }
569                                                                             this.checkIsDone = true;
570                                                                             stmtt.close();
571                                                                             /*
572                                                                                 check = conn.getMetaData().getTables(catalogName, null, getLogTableName(), types);
573                                         this.checkIsDone = true;
574                                         this.logTableExists = check.next();
575                                                                                 check.close();
576                                                                             */

577                                     }
578                                 } catch (UnsupportedOperationException JavaDoc ex) {
579                                     String JavaDoc message = "Error while trying to get meta data from target table.";
580                                     throw new SQLException JavaDoc(message);
581                                 }
582                             
583                                 if (this.logTableExists) {
584                                     stmt.executeUpdate("INSERT INTO " + getLogTableName()
585                                     + " (" + getLogImportDefinitionName() + ", "
586                                     + getLogOperationName() + ", " + getLogTypeName()
587                                     + ", " + getLogTable() + ", " + getLogColumnName()
588                                     + ", " + getLogRowNumber() + ", " + getLogOriginalValue()
589                                     + ", " + getLogNewValue() + ", " + getLogFailedStatement()
590                                     +", " +getLogTime()+ ")"
591                                     + " VALUES (" + "'"
592                                     + importDefinitionName + "','UPDATE','CUT OFF DATA','"
593                                     + tableName + "','" + dataNameInQyery + "','"
594                                     + (rowNumber + 1) + "','" + original + "','"
595                                     + replacement + "'" + ","+"'"+newLogFailedStatement+"'"+","+"'"+logTime+"'"+")");
596
597                                 } else {
598                                     this.logger.write("full", "\t " + getLogTableName() + " does not exists, or is invalid");
599                                 }
600                                 this.logger.write("full", "\t Because dataCutOff attribute is true, some replacement were made:");
601                                 this.logger.write("full", "\t UPDATE:CutOff: In table " + tableName + ", column " + dataNameInQyery + ", value " + dataValueInQyery + " were replaced");
602                                 this.logger.write("full", "\t with value " + replacement + " (row number " + (rowNumber + 1) + ")");
603
604                             }
605                         }
606                     } catch (LoaderException e) {
607                         LoaderException le = new LoaderException("Exception:", (Throwable JavaDoc) e);
608                         throw le;
609
610                     }
611                 }
612             }
613             stmt.executeUpdate(update + strQuery);
614             stmt.close();
615
616         } catch (SQLException JavaDoc ex) {
617             if (onErrorContinue.equalsIgnoreCase("true"))
618                 cleaningInsert(tableName, conn, rowNumber, "UPDATE", "Update fails. Message: " + ex.getMessage(), importDefinitionName,logFailedStatement);
619             else {
620                 LoaderException le = new LoaderException("SQLException: ", (Throwable JavaDoc) ex);
621                 this.logger.write("full", "\tError in SQL statement: " + le.getCause());
622                 // this.logger.write("full", "\tError : Because OnErrorContinue Attribute is false, application is terminated");
623
throw ex;
624
625             }
626         }
627     }
628
629     /**
630      * This method write message in to log table if some data must be truncated
631      * @param tableName is name of the table which is in the process
632      * @param conn is connection to target database (table)
633      * @param strQuery is string represented sql statement
634      * @param rowNumber is current row number
635      * @param onErrorContinue represents onErrorContinue tag
636      * @param msg represents error message
637      * @param importDefinitionName is name of the import definition job
638      * @throws SQLException
639      */

640     public void cutingDataLenght(String JavaDoc tableName, Connection JavaDoc conn, String JavaDoc strQuery, int rowNumber, String JavaDoc onErrorContinue, String JavaDoc msg, String JavaDoc importDefinitionName, String JavaDoc logFailedStatement) throws SQLException JavaDoc {
641         boolean end = false;
642         boolean endTemp = false;
643         SqlParser sqlParser = new SqlParser();
644                 String JavaDoc newLogFailedStatement =logFailedStatement.replaceAll("'","");
645                 String JavaDoc logTime = "";
646         try {
647                         logTime = getCurrentTime();
648             try {
649                 sqlParser.parse(strQuery);
650             } catch (Exception JavaDoc e) {
651                 throw new SQLException JavaDoc(e.getMessage());
652             }
653             String JavaDoc[] columnNames = sqlParser.getColumnNames();
654             String JavaDoc[] columnValues = sqlParser.getColumnValues();
655
656             String JavaDoc[] types = { "TABLE" };
657             String JavaDoc catalogName = conn.getCatalog();
658             stmt = conn.createStatement();
659
660             if (!this.currentTableName.equalsIgnoreCase(tableName)) {
661                 try {
662                     rs = conn.getMetaData().getColumns(catalogName, null, tableName, "%");
663                 } catch (UnsupportedOperationException JavaDoc ex) {
664                     String JavaDoc message = "Error while trying to get meta data from target table.";
665                     throw new SQLException JavaDoc(message);
666                 }
667                 colNamesDataTypes.clear();
668                 colNamesDataLenght.clear();
669                 this.currentTableName = tableName;
670                 while (rs.next()) {
671                     String JavaDoc columnName = rs.getString(4).toUpperCase();
672                     colNamesDataTypes.put(columnName, rs.getString(6));
673                     colNamesDataLenght.put(columnName, rs.getString(7));
674                 }
675             }
676
677             for (int i = 0; i < columnNames.length; i++) {
678                 String JavaDoc dataNameInQyery = columnNames[i];
679                 String JavaDoc dataValueInQyery = columnValues[i];
680                 String JavaDoc dataTypeName = (String JavaDoc) colNamesDataTypes.get(dataNameInQyery.toUpperCase());
681                 String JavaDoc dataTypeLenght = (String JavaDoc) colNamesDataLenght.get(dataNameInQyery.toUpperCase());
682                 if (!dataValueInQyery.equalsIgnoreCase("null")) {
683                     // ZK change from CheckType to targetConfigReader 7.5.2004
684

685                     try {
686                         if (!configReader.isNumber(dataTypeName)) {
687                             if ((dataValueInQyery.length()) > Integer.parseInt(dataTypeLenght)) {
688                                 String JavaDoc original = dataValueInQyery.substring(0, dataValueInQyery.length());
689                                 String JavaDoc replacement = replaceQuote(original, Integer.parseInt(dataTypeLenght));
690                                 strQuery = replaceFirst(strQuery, dataValueInQyery, replacement);
691                                 ResultSet JavaDoc check = null;
692                                 try {
693                                     if (!checkIsDone) {
694                                                                                 this.logTableExists = true;
695                                                                                 Statement JavaDoc stmtt = conn.createStatement();
696                                                                               try {
697                                                                                 stmtt.executeQuery("SELECT * FROM "+getLogTableName());
698                                                                             } catch (Exception JavaDoc e) {
699                                                                                 this.logTableExists = false;
700                                                                             }
701                                                                             this.checkIsDone = true;
702                                                                             stmtt.close();
703                                         /*
704                                             check = conn.getMetaData().getTables(catalogName, null, getLogTableName(), types);
705                                         this.checkIsDone = true;
706                                         this.logTableExists = check.next();
707                                         check.close();
708                                                                             */

709                                     }
710                                 } catch (UnsupportedOperationException JavaDoc ex) {
711                                     String JavaDoc message = "Error while trying to get meta data from target table.";
712                                     throw new SQLException JavaDoc(message);
713                                 }
714                             
715                                 if (this.logTableExists) {
716                                     stmt.executeUpdate("INSERT INTO " + getLogTableName()
717                                     + " (" + getLogImportDefinitionName() + ", "
718                                     + getLogOperationName() + ", " + getLogTypeName()
719                                     + ", " + getLogTable() + ", " + getLogColumnName()
720                                     + ", " + getLogRowNumber() + ", " + getLogOriginalValue()
721                                     + ", " + getLogNewValue() + ", " + getLogFailedStatement()
722                                     +", " +getLogTime()
723                                                         + ")" + " VALUES (" + "'"
724                                     + importDefinitionName + "','INSERT','CUT OFF DATA','"
725                                     + tableName + "','" + dataNameInQyery + "','" + (rowNumber + 1)
726                                     + "','" + original + "','" + replacement + "'" + ","+"'"+newLogFailedStatement+"'"+","+"'"+logTime+"'"+")");
727
728                                 } else {
729                                     this.logger.write("full", "\t " + getLogTableName() + " does not exists, or is invalid");
730
731                                 }
732                                 // check.close();
733
this.logger.write("full", "\t Because dataCutOff attribute is true, some replacement were made:");
734                                 this.logger.write("full", "\t INSERT:CutOff: In table " + tableName + ", column " + dataNameInQyery + ", value " + dataValueInQyery + " were replaced");
735                                 this.logger.write("full", "\t with value " + replacement + " (row number " + (rowNumber + 1) + ")");
736                             }
737                         }
738
739                     } catch (LoaderException e) {
740                         LoaderException le = new LoaderException("Exception:", (Throwable JavaDoc) e);
741
742                     }
743                 }
744             }
745
746             stmt.executeUpdate(strQuery);
747             stmt.close();
748         } catch (SQLException JavaDoc ex) {
749             if (onErrorContinue.equalsIgnoreCase("true"))
750                 cleaningInsert(tableName, conn, rowNumber, "INSERT", msg, importDefinitionName,logFailedStatement);
751             else {
752                 LoaderException le = new LoaderException("SQLException: ", (Throwable JavaDoc) ex);
753                 this.logger.write("full", "\tError in SQL statement: " + le.getCause());
754                 // this.logger.write("full", "\tError : Because OnErrorContinue Attribute is false, application is terminated");
755
throw ex;
756
757             }
758         }
759     }
760
761     private String JavaDoc replaceQuote(String JavaDoc replacement, int length) {
762         replacement = Utils.replaceAll(replacement, "''", "'");
763         replacement = replacement.substring(0, length);
764         int index = replacement.indexOf("'");
765         if (index != -1) {
766             replacement = Utils.replaceAll(replacement, "'", "''");
767         }
768         return replacement;
769     }
770
771     private String JavaDoc replaceFirst(String JavaDoc input, String JavaDoc forReplace, String JavaDoc replaceWith) {
772         String JavaDoc retVal = input;
773         int start = input.indexOf(forReplace);
774         int end = start + forReplace.length();
775         if (start != -1) {
776             retVal = input.substring(0, start) + replaceWith + input.substring(end);
777         }
778         return retVal;
779     }
780
781     /**
782      * Set Logger object
783      * @param logger Logger object which is used for log file
784      */

785     public void setLogger(Logger logger) {
786         this.logger = logger;
787     }
788     /**
789      * This method set value of primary key for column which data wasn't succesufuly loaded
790      * @param String primary key
791      */

792     public void setLogPrimaryKeyValue(String JavaDoc string) {
793         this.logFailedStatement = string;
794     }
795
796     /**
797      * This method set time when log was occured
798      * @param String time
799      */

800     public void setLogTime(String JavaDoc string) {
801         this.logTime = string;
802     }
803
804     /**
805      * This method returns value of primary key for column which data wasn't succesufuly loaded
806      * @return String value which is value of primary key for column
807      */

808     public String JavaDoc getLogFailedStatement() {
809         return this.logFailedStatement;
810     }
811
812     /**
813      * This method returns time when log was occured
814      * @return String value which is time when log is inserted in table
815      */

816     public String JavaDoc getLogTime() {
817         return this.logTime;
818     }
819
820 }
Popular Tags