Java > Open Source Codes > com > knowgate > hipergate > datamodel > ImportExport


1 /*
2   Copyright (C) 2005 Know Gate S.L. All rights reserved.
3                       C/Oña, 107 1º2 28050 Madrid (Spain)
4
5   Redistribution and use in source and binary forms, with or without
6   modification, are permitted provided that the following conditions
7   are met:
8
9   1. Redistributions of source code must retain the above copyright
10      notice, this list of conditions and the following disclaimer.
11
12   2. The end-user documentation included with the redistribution,
13      if any, must include the following acknowledgment:
14      "This product includes software parts from hipergate
15      (http://www.hipergate.org/)."
16      Alternately, this acknowledgment may appear in the software itself,
17      if and wherever such third-party acknowledgments normally appear.
18
19   3. The name hipergate must not be used to endorse or promote products
20      derived from this software without prior written permission.
21      Products derived from this software may not be called hipergate,
22      nor may hipergate appear in their name, without prior written
23      permission.
24
25   This library is distributed in the hope that it will be useful,
26   but WITHOUT ANY WARRANTY; without even the implied warranty of
27   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
28
29   You should have received a copy of hipergate License with this code;
30   if not, visit http://www.hipergate.org or mail to info@hipergate.org
31 */
32
33 package com.knowgate.hipergate.datamodel;
34
35 /**
36  * @author Sergio Montoro Ten
37  * @version 1.0
38  */
39
40 import java.sql.DriverManager ;
41 import java.sql.Connection ;
42 import java.sql.SQLException ;
43 import java.sql.PreparedStatement ;
44 import java.sql.ResultSet ;
45 import java.sql.Timestamp ;
46 import java.sql.Types ;
47
48 import java.io.File ;
49 import java.io.FileOutputStream ;
50 import java.io.FileInputStream ;
51 import java.io.FileWriter ;
52 import java.io.BufferedInputStream ;
53 import java.io.BufferedOutputStream ;
54 import java.io.InputStreamReader ;
55 import java.io.FileNotFoundException ;
56 import java.io.IOException ;
57 import java.io.OutputStream ;
58
59 import java.math.BigDecimal ;
60 import java.text.ParseException ;
61
62 import java.util.Date ;
63 import java.util.Properties ;
64 import java.util.ArrayList ;
65 import java.util.HashMap ;
66 import java.util.Iterator ;
67 import java.text.SimpleDateFormat ;
68
69 import java.math.BigDecimal ;
70
71 import com.knowgate.debug.DebugFile;
72 import com.knowgate.misc.Gadgets;
73 import com.knowgate.acl.UserLoader;
74 import com.knowgate.crm.ContactLoader;
75 import com.knowgate.hipergate.ProductLoader;
76 import com.knowgate.dataobjs.DBSubset;
77 import com.knowgate.dataobjs.DBColumn;
78
79 public class ImportExport {
80
81   private static String [] ReservedWords = { "ALLCAPS", "APPEND", "UPDATE", "APPENDUPDATE",
82   "CONNECT", "TO", "IDENTIFIED", "BY", "SCHEMA", "INFILE", "INPUTFILE", "BADFILE",
83   "DISCARDFILE", "CHARSET", "CHARACTERSET", "ROWDELIM", "COLDELIM", "RECOVERABLE",
84   "UNRECOVERABLE", "PRESERVESPACE", "WORKAREA", "MAXERRORS", "INSERTLOOKUPS", "SKIP",
85   "CATEGORY", "USERS", "CONTACTS", "COMPANIES", "PRODUCTS" };
86
87   // ---------------------------------------------------------------------------
88
89   public ImportExport() { }
90
91   // ---------------------------------------------------------------------------
92
93   private boolean isReservedWord(String sWord) {
94     for (int r=ReservedWords.length; r>=0; r--)
95       if (ReservedWords[r].equalsIgnoreCase(sWord))
96         return true;
97     return false;
98   }
99
100   // ---------------------------------------------------------------------------
101
102   /**
103    *
104    * @param sControlCmdLine String [APPEND|UPDATE|APPENDUPDATE] [CONTACTS|COMPANIES|PRODUCTS|USERS|<I>table_name</I>] CONNECT user TO connection_string IDENTIFED BY password INPUTFILE "/tmp/filename.txt" CHARSET ISO8859_1<BR>
105    * EXPORT <I>table_name</I> CONNECT user TO connection_string IDENTIFED BY password OUTPUTFILE "/tmp/filename.txt"
106    * @return Count of errors found or zero if operation was successfully completed
107    * @throws ImportExportException
108    */
109   public int perform (String sControlCmdLine)
110     throws ImportExportException {
111
112     if (DebugFile.trace) {
113       DebugFile.writeln("Begin ImportExport.perform("+sControlCmdLine+")");
114     }
115
116     int iErrorCount = 0;
117     int iMaxErrors = 2147483647;
118     int i=-1, r=0, c=0;
119     int iLine = 1;
120     int iSkip = 0;
121     int iRowDelimLen = -1;
122     String sColDelim=";", sRowDelim="\n";
123     String sCmd=null, sEntity=null, sCharSet="ISO8859_1", sInFile=null,
124            sBadFile=null, sDiscardFile=null,sOutFile=null,
125            sConnectStr=null, sUser=null, sPwd=null, sSchema=null,
126            sWorkArea=null,sCategory=null,sWhere=null;
127     int iFlags = 0;
128     boolean bRecoverable=true, bPreserveSpace=false, bAllCaps=false;
129     String sToken,sLine;
130     String [] aLine;
131     int iParenthesisNesting = 0;
132     File oInFile = null;
133     ColumnList oColumns = new ColumnList();
134     DBColumn oCurrentColumn = null;
135     ImportLoader oImplLoad=null;
136
137     if (null==sControlCmdLine) return 0;
138     if (sControlCmdLine.length()==0) return 0;
139
140     // *********************
141 // Tokenize command line
142
143     String [] aCmdLine = Gadgets.tokenizeCmdLine(sControlCmdLine);
144     final int nCmmds = aCmdLine.length;
145
146     if (DebugFile.trace) DebugFile.writeln(" parsing command line...");
147
148     // ******************************************
149 // Check that the main command is a valid one
150 if (aCmdLine[0].equalsIgnoreCase("APPEND") ||
151         aCmdLine[0].equalsIgnoreCase("UPDATE") ||
152         aCmdLine[0].equalsIgnoreCase("APPENDUPDATE")) {
153       sCmd = aCmdLine[0];
154       sEntity = aCmdLine[1];
155       if (sEntity.equalsIgnoreCase("CONTACTS")) {
156         oImplLoad = new ContactLoader();
157         iFlags |= ContactLoader.WRITE_CONTACTS|ContactLoader.WRITE_COMPANIES|ContactLoader.WRITE_ADDRESSES;
158       } else if (sEntity.equalsIgnoreCase("COMPANIES")) {
159         oImplLoad = new ContactLoader();
160         iFlags |= ContactLoader.WRITE_COMPANIES|ContactLoader.WRITE_ADDRESSES;
161       } else if (sEntity.equalsIgnoreCase("USERS")) {
162         oImplLoad = new UserLoader();
163       } else if (sEntity.equalsIgnoreCase("PRODUCTS")) {
164         oImplLoad = new ProductLoader();
165       }
166       else
167         oImplLoad = new TableLoader(sEntity);
168     } else if (aCmdLine[0].equalsIgnoreCase("EXPORT")) {
169       sCmd = aCmdLine[0];
170       sEntity = aCmdLine[1];
171     } else {
172       throw new ImportExportException("Cannot recognize command " + aCmdLine[0]);
173     }
174
175     if (sCmd.equalsIgnoreCase("APPEND")) {
176       iFlags |= ImportLoader.MODE_APPEND;
177     } else if (sCmd.equalsIgnoreCase("UPDATE")) {
178       iFlags |= ImportLoader.MODE_UPDATE;
179     } else if (sCmd.equalsIgnoreCase("APPENDUPDATE")) {
180       iFlags |= ImportLoader.MODE_APPENDUPDATE;
181     }
182
183     // ************************************************
184 // Iterate throught tokens and set status variables
185
186     for (int t=0; t<nCmmds; t++) {
187       sToken = aCmdLine[t];
188
189       // *******************************************
190 // Process tokens before a parenthesis is found
191
192       if (0==iParenthesisNesting) {
193         if (sToken.equalsIgnoreCase("CHARACTERSET") || sToken.equalsIgnoreCase("CHARSET")) {
194             if (t==nCmmds-1)
195               throw new ImportExportException("CHARACTERSET attribute lacks of value specification");
196             else
197               sCharSet=aCmdLine[++t];
198         if (DebugFile.trace) DebugFile.writeln(" CHARACTERSET="+sCharSet);
199         } // fi
200 if (sToken.equalsIgnoreCase("CONNECT")) {
201             if (t>nCmmds-6)
202               throw new ImportExportException("CONNECT attribute lacks of value specification");
203             else {
204               if (!aCmdLine[t+2].equalsIgnoreCase("TO"))
205                 throw new ImportExportException("TO keyword expected but found " + aCmdLine[t+2]);
206               if (!aCmdLine[t+4].equalsIgnoreCase("IDENTIFIED"))
207                 throw new ImportExportException("IDENTIFIED keyword expected but found " + aCmdLine[t+4]);
208               if (!aCmdLine[t+5].equalsIgnoreCase("BY"))
209                 throw new ImportExportException("BY keyword expected but found " + aCmdLine[t+5]);
210
211               sUser=aCmdLine[++t]; // t+1
212 t++; // skip TO
213 sConnectStr=aCmdLine[++t]; // t+3
214 t+=2; // skip IDENTIFIED BY
215 sPwd=aCmdLine[++t]; // t+6
216 }
217         if (DebugFile.trace) DebugFile.writeln(" CONNECT="+sConnectStr);
218         } // fi
219 if (sToken.equalsIgnoreCase("SCHEMA")) {
220           if (t==nCmmds-1)
221             throw new ImportExportException("SCHEMA attribute lacks of value specification");
222           else
223             sSchema=aCmdLine[++t];
224         if (DebugFile.trace) DebugFile.writeln(" SCHEMA="+sSchema);
225         } // fi
226 if (sToken.equalsIgnoreCase("SKIP")) {
227           if (t==nCmmds-1)
228             throw new ImportExportException("SKIP attribute lacks of value specification");
229           else {
230             try {
231               iSkip=Integer.parseInt(aCmdLine[++t]);
232             } catch (NumberFormatException nfe) {
233               throw new ImportExportException("SKIP attribute must be a positive integer value");
234             }
235             if (iSkip<0)
236               throw new ImportExportException("SKIP attribute must be a positive integer value");
237           }
238         if (DebugFile.trace) DebugFile.writeln(" SKIP="+String.valueOf(iSkip));
239         } // fi
240 else if (sToken.equalsIgnoreCase("WORKAREA")) {
241           if (t==nCmmds-1)
242             throw new ImportExportException("WORKAREA attribute lacks of value specification");
243           else
244             sWorkArea=aCmdLine[++t];
245         if (DebugFile.trace) DebugFile.writeln(" WORKAREA="+sWorkArea);
246         }
247         else if (sToken.equalsIgnoreCase("CATEGORY")) {
248           if (!sEntity.equalsIgnoreCase("PRODUCTS"))
249             throw new ImportExportException("CATEGORY attribute is only allowed for loading PRODUCTS, not "+sEntity);
250           if (t==nCmmds-1)
251             throw new ImportExportException("CATEGORY attribute lacks of value specification");
252           else
253             sCategory=aCmdLine[++t];
254         if (DebugFile.trace) DebugFile.writeln(" CATEGORY="+sCategory);
255         }
256         else if (sToken.equalsIgnoreCase("WHERE")) {
257           if (sCmd.equalsIgnoreCase("APPEND") || sCmd.equalsIgnoreCase("UPDATE") || sCmd.equalsIgnoreCase("APPENDUPDATE"))
258             throw new ImportExportException(sToken + "parameter cannot de used with " + sCmd + " command");
259           if (t==nCmmds-1)
260             throw new ImportExportException(sToken+" attribute lacks of value specification");
261           sWhere=aCmdLine[++t];
262         }
263         else if (sToken.equalsIgnoreCase("OUTPUTFILE") || sToken.equalsIgnoreCase("OUTFILE")) {
264           if (sCmd.equalsIgnoreCase("APPEND") || sCmd.equalsIgnoreCase("UPDATE") || sCmd.equalsIgnoreCase("APPENDUPDATE"))
265             throw new ImportExportException(sToken + "parameter cannot de used with " + sCmd + " command");
266           if (t==nCmmds-1)
267             throw new ImportExportException(sToken+" attribute lacks of value specification");
268           sOutFile=aCmdLine[++t];
269         }
270         else if (sToken.equalsIgnoreCase("INPUTFILE") || sToken.equalsIgnoreCase("INFILE")) {
271           if (t==nCmmds-1)
272             throw new ImportExportException(sToken+" attribute lacks of value specification");
273           else {
274             sInFile=aCmdLine[++t];
275             oInFile=new File (sInFile);
276             if (!oInFile.exists()) throw new ImportExportException("File not found " + sInFile);
277           }
278           if (DebugFile.trace) DebugFile.writeln(" INPUTFILE="+sInFile);
279         } // fi
280 else if (sToken.equalsIgnoreCase("BADFILE")) {
281           if (sCmd.equalsIgnoreCase("EXPORT"))
282             throw new ImportExportException(sToken + "parameter cannot de used with " + sCmd + " command");
283           if (t==nCmmds-1)
284               throw new ImportExportException("BADFILE attribute lacks of value specification");
285           else {
286               sBadFile=aCmdLine[++t];
287           }
288           if (DebugFile.trace) DebugFile.writeln(" BADFILE="+sBadFile);
289         } // fi
290 else if (sToken.equalsIgnoreCase("DISCARDFILE")) {
291           if (sCmd.equalsIgnoreCase("EXPORT"))
292             throw new ImportExportException(sToken + "parameter cannot de used with " + sCmd + " command");
293           if (t==nCmmds-1)
294             throw new ImportExportException("DISCARDFILE attribute lacks of value specification");
295           else {
296             sDiscardFile=aCmdLine[++t];
297           }
298           if (DebugFile.trace) DebugFile.writeln(" DISCARDFILE="+sDiscardFile);
299         } // fi
300 else if (sToken.equalsIgnoreCase("INSERTLOOKUPS")) {
301           if (sCmd.equalsIgnoreCase("EXPORT"))
302             throw new ImportExportException(sToken + "parameter cannot de used with " + sCmd + " command");
303           iFlags |= ImportLoader.WRITE_LOOKUPS;
304           if (DebugFile.trace) DebugFile.writeln(" INSERTLOOKUPS set to true");
305         } // fi
306 else if (sToken.equalsIgnoreCase("MAXERRORS")) {
307           if (t==nCmmds-1)
308             throw new ImportExportException("MAXERRORS attribute lacks of value specification");
309           else {
310             try {
311               iMaxErrors=Integer.parseInt(aCmdLine[++t]);
312             } catch (NumberFormatException nfe) {
313               throw new ImportExportException("MAXERRORS attribute must be a positive integer value");
314             }
315             if (iMaxErrors<0)
316               throw new ImportExportException("MAXERRORS attribute must be a positive integer value");
317           }
318           if (DebugFile.trace) DebugFile.writeln(" MAXERRORS="+String.valueOf(iMaxErrors));
319         } // fi
320 else if (sToken.equalsIgnoreCase("RECOVERABLE")) {
321           if (sCmd.equalsIgnoreCase("EXPORT"))
322             throw new ImportExportException(sToken + "parameter cannot de used with " + sCmd + " command");
323           bRecoverable=true;
324           if (DebugFile.trace) DebugFile.writeln(" RECOVERABLE set to true");
325         } // fi
326 else if (sToken.equalsIgnoreCase("UNRECOVERABLE")) {
327           if (sCmd.equalsIgnoreCase("EXPORT"))
328             throw new ImportExportException(sToken + "parameter cannot de used with " + sCmd + " command");
329           bRecoverable=false;
330           if (DebugFile.trace) DebugFile.writeln(" RECOVERABLE set to false");
331         } // fi
332 else if (sToken.equalsIgnoreCase("ROWDELIM") || sToken.equalsIgnoreCase("ROWDELIMITER")) {
333           if (t==nCmmds-1)
334             throw new ImportExportException("ROWDELIM attribute lacks of value specification");
335           else {
336             sRowDelim=aCmdLine[++t];
337             if (sRowDelim.equalsIgnoreCase("LF")) sRowDelim="\n";
338             if (sRowDelim.equalsIgnoreCase("CR")) sRowDelim="\r";
339             if (sRowDelim.equalsIgnoreCase("CRLF")) sRowDelim="\r\n";
340             iRowDelimLen = sRowDelim.length();
341           }
342           if (DebugFile.trace) DebugFile.writeln(" ROWDELIM="+sRowDelim);
343         } // fi
344 else if (sToken.equalsIgnoreCase("COLDELIM") || sToken.equalsIgnoreCase("COLDELIMITER") ||
345                  sToken.equalsIgnoreCase("COLUMNDELIM") || sToken.equalsIgnoreCase("COLUMNDELIMITER")) {
346           if (t==nCmmds-1)
347             throw new ImportExportException("COLDELIM attribute lacks of value specification");
348           else {
349             sColDelim=aCmdLine[++t];
350             if (sColDelim.equalsIgnoreCase("TAB")) sColDelim="\t";
351           }
352           if (DebugFile.trace) DebugFile.writeln(" COLDELIM="+sColDelim);
353         } // fi
354 else if (sToken.equalsIgnoreCase("PRESERVESPACE")) {
355           bPreserveSpace=true;
356           if (DebugFile.trace) DebugFile.writeln(" PRESERVESPACE set to true");
357         } // fi
358 else if (sToken.equalsIgnoreCase("ALLCAPS")) {
359           bAllCaps=true;
360           if (DebugFile.trace) DebugFile.writeln(" ALLCAPS set to true");
361         } // fi
362 else if (sToken.equalsIgnoreCase("(")) {
363           iParenthesisNesting++;
364           if (DebugFile.trace) DebugFile.writeln("open parenthesis, count is "+String.valueOf(iParenthesisNesting));
365           oCurrentColumn = new DBColumn();
366           oColumns.add(oCurrentColumn);
367         }
368         else if (sToken.equalsIgnoreCase(")")) {
369           iParenthesisNesting--;
370           if (DebugFile.trace) DebugFile.writeln("open parenthesis, count is "+String.valueOf(iParenthesisNesting));
371         }
372       } else {
373         // *******************************************
374 // Process tokens after a parenthesis is found
375 // These are the column names and types definition
376
377         if (sToken.equalsIgnoreCase("(")) {
378           iParenthesisNesting++;
379           if (DebugFile.trace) DebugFile.writeln(" open parenthesis, count is "+String.valueOf(iParenthesisNesting));
380         }
381         else if (sToken.equalsIgnoreCase(")")) {
382           iParenthesisNesting--;
383           if (DebugFile.trace) DebugFile.writeln(" close parenthesis, count is "+String.valueOf(iParenthesisNesting));
384         }
385         else if (sToken.equalsIgnoreCase(",")) {
386           // after each comma, add a new column
387 if (DebugFile.trace) DebugFile.writeln(" added column "+oCurrentColumn.getName()+" "+oCurrentColumn.getSqlTypeName());
388           oCurrentColumn = new DBColumn();
389           oColumns.add(oCurrentColumn);
390         }
391         else {
392           int iSQType = DBColumn.getSQLType(sToken);
393           if (iSQType!=Types.NULL) {
394             if (iSQType==Types.DATE) {
395               if (t==nCmmds-1)
396                 throw new ImportExportException("DATE format is required");
397               else {
398                 oCurrentColumn.setSqlType(Types.DATE);
399                 try {
400                   oCurrentColumn.setDateFormat(aCmdLine[++t]);
401                 } catch (IllegalArgumentException iae) {
402                   throw new ImportExportException("Invalid date format "+sToken, iae);
403                 }
404               }
405             } else {
406               oCurrentColumn.setSqlType(iSQType);
407             }
408           } else {
409               oCurrentColumn.setName(sToken);
410               oCurrentColumn.setSqlType(Types.NULL);
411           } // fi (iSQType!=Types.NULL)
412 }
413       } // fi (1==iParenthesisNesting)
414 } // next
415
416     final int iColFmtsCount = oColumns.size();
417     DBColumn oColFmt;
418
419     if (DebugFile.trace) {
420       StringBuffer oColumnsFmts = new StringBuffer ();
421       for (c=0; c<iColFmtsCount; c++) {
422         oColFmt = oColumns.getColumn(c);
423         oColumnsFmts.append(oColFmt.getName()+" "+oColFmt.getSqlTypeName());
424         if (oColFmt.getDateFormat()!=null) oColumnsFmts.append(" "+oColFmt.getDateFormat().toPattern());
425         oColumnsFmts.append(c<iColFmtsCount-1 ? "," : "}");
426       } // next
427 DebugFile.writeln(" Column definitions {"+oColumnsFmts.toString());
428     } // fi (DebugFile.trace)
429
430     // *************************************************************
431 // Perform some basic verifications on command line completeness
432
433     if (0!=iParenthesisNesting)
434       throw new ImportExportException("Unterminated parenthesis (");
435     if ((sCmd.equalsIgnoreCase("APPEND") || sCmd.equalsIgnoreCase("UPDATE") || sCmd.equalsIgnoreCase("APPENDUPDATE")) && null==sInFile)
436       throw new ImportExportException("INFILE is required");
437     if (sCmd.equalsIgnoreCase("EXPORT") && null==sOutFile)
438       throw new ImportExportException("OUTFILE is required");
439     if (sCmd.equalsIgnoreCase("EXPORT") && null==sWhere)
440       throw new ImportExportException("WHERE clause is required");
441     if (null==sConnectStr)
442       throw new ImportExportException("ConnectionString is required");
443
444     // *********************************************
445 // Load JDBC drivers for all supported databases
446
447     Class jdbcdriver;
448     try {
449       if (DebugFile.trace) DebugFile.writeln(" Class.forName(com.microsoft.jdbc.sqlserver.SQLServerDriver)");
450       jdbcdriver = Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
451     } catch (ClassNotFoundException ignore) { if (DebugFile.trace) DebugFile.writeln("Class not found com.microsoft.jdbc.sqlserver.SQLServerDriver"); }
452     try {
453       if (DebugFile.trace) DebugFile.writeln(" Class.forName(org.postgresql.Driver)");
454       jdbcdriver = Class.forName("org.postgresql.Driver");
455     } catch (ClassNotFoundException ignore) { if (DebugFile.trace) DebugFile.writeln("Class not found org.postgresql.Driver"); }
456     try {
457       if (DebugFile.trace) DebugFile.writeln(" Class.forName(oracle.jdbc.driver.OracleDriver)");
458       jdbcdriver = Class.forName("oracle.jdbc.driver.OracleDriver");
459     } catch (ClassNotFoundException ignore) { if (DebugFile.trace) DebugFile.writeln("Class not found oracle.jdbc.driver.OracleDriver"); }
460     try {
461       if (DebugFile.trace) DebugFile.writeln(" Class.forName(com.ibm.db2.jcc.DB2Driver)");
462       jdbcdriver = Class.forName("com.ibm.db2.jcc.DB2Driver");
463     } catch (ClassNotFoundException ignore) { if (DebugFile.trace) DebugFile.writeln("Class not found com.ibm.db2.jcc.DB2Driver"); }
464     try {
465       if (DebugFile.trace) DebugFile.writeln(" Class.forName(com.mysql.jdbc.Driver)");
466       jdbcdriver = Class.forName("com.mysql.jdbc.Driver");
467     } catch (ClassNotFoundException ignore) { if (DebugFile.trace) DebugFile.writeln("Class not found com.mysql.jdbc.Driver"); }
468
469     // ***********************
470 // Connect to the database
471 Connection oConn = null;
472     try {
473       if (DebugFile.trace) DebugFile.writeln(" DriverManager.getConnection("+sConnectStr+","+sUser+")");
474       oConn = DriverManager.getConnection(sConnectStr, sUser, sPwd);
475     } catch (SQLException sqle) {
476       throw new ImportExportException(sqle.getMessage(), sqle);
477     }
478
479     // **************************
480 // Check that WorkArea exists
481
482     if (null!=sWorkArea) {
483       boolean bSignalWorkAreaException = false;
484       try {
485         if (DebugFile.trace) DebugFile.writeln(" Connection.prepareStatement(SELECT gu_workarea FROM k_workareas WHERE gu_workarea='"+Gadgets.left(sWorkArea, 32)+"' OR nm_workarea='"+Gadgets.left(sWorkArea, 50)+"'");
486         PreparedStatement oStmt = oConn.prepareStatement("SELECT gu_workarea FROM k_workareas WHERE gu_workarea=? OR nm_workarea=?",ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
487         oStmt.setString(1, Gadgets.left(sWorkArea, 32));
488         oStmt.setString(2, Gadgets.left(sWorkArea, 50));
489         ResultSet oRSet = oStmt.executeQuery();
490         if (oRSet.next())
491           sWorkArea = oRSet.getString(1);
492         else
493           bSignalWorkAreaException = true;
494         oRSet.close();
495         oStmt.close();
496         if (bSignalWorkAreaException) {
497           oConn.close();
498           throw new ImportExportException("WorkArea " + sWorkArea + " not found");
499         }
500       } catch (SQLException sqle) {
501         try {oConn.close();} catch (SQLException ignore) {}
502         throw new ImportExportException("SQLException " + sqle.getMessage());
503       }
504     } // fi
505
506     // **************************
507 // Check that Category exists
508
509     if (null!=sCategory) {
510       boolean bSignalCategoryException = false;
511       try {
512         if (DebugFile.trace) DebugFile.writeln(" Connection.prepareStatement(SELECT gu_workarea FROM k_categories WHERE gu_category='"+Gadgets.left(sCategory, 32)+"' OR nm_category='"+Gadgets.left(sCategory, 100)+"'");
513         PreparedStatement oStmt = oConn.prepareStatement("SELECT gu_category FROM k_categories WHERE gu_category=? OR nm_category=?",ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
514         oStmt.setString(1, Gadgets.left(sCategory, 32));
515         oStmt.setString(2, Gadgets.left(sCategory, 100));
516         ResultSet oRSet = oStmt.executeQuery();
517         if (oRSet.next())
518           sCategory = oRSet.getString(1);
519         else
520           bSignalCategoryException = true;
521         oRSet.close();
522         oStmt.close();
523         if (bSignalCategoryException) {
524           oConn.close();
525           throw new ImportExportException("Category " + sCategory + " not found");
526         }
527       } catch (SQLException sqle) {
528         try {oConn.close();} catch (SQLException ignore) {}
529         throw new ImportExportException("SQLException " + sqle.getMessage());
530       }
531     } // fi
532
533     // ***************
534 // File handlers
535
536     // Data input file
537 FileOutputStream oOutStrm = null;
538     FileInputStream oInStrm = null;
539     BufferedInputStream oInBuff;
540     BufferedOutputStream oOutBuff;
541     InputStreamReader oInRdr;
542     // File with a description of all errors
543 FileWriter oBadWrtr = null;
544     // File with just the rows that failed to be inserted
545 FileWriter oDiscardWrtr = null;
546     // Intermediate buffer for SQL composition
547 StringBuffer oSQL = new StringBuffer ();
548     StringBuffer oRow = new StringBuffer ();
549
550       if (DebugFile.trace) DebugFile.writeln(" Begin inserting data...");
551
552       // ********************
553 // Open data input file
554
555     if (sCmd.equalsIgnoreCase("APPEND") || sCmd.equalsIgnoreCase("UPDATE") || sCmd.equalsIgnoreCase("APPENDUPDATE")) {
556       try {
557         if (DebugFile.trace) DebugFile.writeln(" new FileInputStream("+sInFile+")");
558         oInStrm = new FileInputStream (oInFile);
559       } catch (FileNotFoundException fnfe) {
560         try { oConn.close(); } catch (Exception ignore) {}
561         throw new ImportExportException("Input file not found", fnfe);
562       }
563       // Java direct I/O performance sucks so use an intermediate buffer
564 oInBuff = new BufferedInputStream (oInStrm);
565       try {
566         // Use an stream reader for decoding bytes into the proper charset
567 oInRdr = new InputStreamReader (oInBuff, sCharSet);
568       } catch (java.io.UnsupportedEncodingException uee) {
569         try { oInBuff.close(); } catch (Exception ignore) {}
570         try { oInStrm.close(); } catch (Exception ignore) {}
571         try { oConn.close(); } catch (Exception ignore) {}
572         throw new ImportExportException("Unsupported Encoding "+sCharSet, uee);
573       }
574
575       // **********************************
576 // Open bad and discard file writters
577
578       if (sBadFile!=null) {
579         try {
580           if (DebugFile.trace) DebugFile.writeln(" new FileWriter("+sBadFile+")");
581           oBadWrtr = new FileWriter (sBadFile, false);
582         }
583         catch (FileNotFoundException neverthrown) {}
584         catch (IOException ioe) {
585           try { oInBuff.close(); } catch (Exception ignore) {}
586           try { oInStrm.close(); } catch (Exception ignore) {}
587           try { oConn.close(); } catch (Exception ignore) {}
588           throw new ImportExportException("IOException "+ioe.getMessage(), ioe);
589         }
590       }
591       if (sDiscardFile!=null) {
592         try {
593           if (DebugFile.trace) DebugFile.writeln(" new FileWriter("+sDiscardFile+")");
594           oDiscardWrtr = new FileWriter (sDiscardFile, false);
595
596         }
597         catch (FileNotFoundException neverthrown) {}
598         catch (IOException ioe) {
599           try { oBadWrtr.close(); } catch (Exception ignore) {}
600           try { oInBuff.close(); } catch (Exception ignore) {}
601           try { oInStrm.close(); } catch (Exception ignore) {}
602           try { oConn.close(); } catch (Exception ignore) {}
603           throw new ImportExportException("IOException "+ioe.getMessage(), ioe);
604         }
605       }
606
607       // *****************************************************************
608 // If RECOVERABLE mode is enabled then set AutoCommit OFF else switch
609 // AutoCommit ON and commit each row just after insertion
610
611       try {
612         oImplLoad.prepare(oConn, oColumns);
613         if (bRecoverable)
614           oConn.setAutoCommit(false);
615         else
616           oConn.setAutoCommit(true);
617       } catch (SQLException sqle) {
618         try { oBadWrtr.close(); } catch (Exception ignore) {}
619         try { oInBuff.close(); } catch (Exception ignore) {}
620         try { oInStrm.close(); } catch (Exception ignore) {}
621         try { oImplLoad.close(); } catch (Exception ignore) {}
622         try { oConn.close(); } catch (Exception ignore) {}
623         throw new ImportExportException("SQLException "+sqle.getMessage(), sqle);
624       }
625
626       // *******************************************************
627 // Check that all column names on input file are valid
628 // and resolve column names to positions for faster access
629
630       for (c=0; c<iColFmtsCount; c++) {
631         DBColumn oClmn = oColumns.getColumn(c);
632         if (oClmn.getSqlType()!=Types.NULL) {
633           int cIndex = oImplLoad.getColumnIndex(oClmn.getName());
634           if (-1==cIndex) {
635             throw new ImportExportException("SQLException column "+oClmn.getName()+" not found at base table");
636           } else {
637             oClmn.setPosition(cIndex);
638           }
639         }
640       } // next (c)
641
642       // ******************************************
643 // Read data input file and insert row by row
644
645       try {
646         while (((i=oInRdr.read())!=-1) && (iErrorCount<=iMaxErrors)) {
647           // Skip row delimiter,
648 r=0;
649           while (i==sRowDelim.charAt(r) && i!=-1) {
650             r++;
651             i=oInRdr.read();
652             if (r==iRowDelimLen) break;
653           } // wend
654 // If r>0 then the row delimiter has been reached Or
655 // If i==-1 then it is the last line, so insert the row
656 if (r==iRowDelimLen || i==-1) {
657             if (iLine>iSkip) {
658               if (DebugFile.trace) DebugFile.writeln(" Processing line "+String.valueOf(iLine));
659               if (bPreserveSpace)
660                 sLine = oRow.toString();
661               else
662                 sLine = oRow.toString().trim();
663               if (sLine.length()>0) {
664                 if (sColDelim.length()==1)
665                   aLine = Gadgets.split(sLine, sColDelim.charAt(0));
666                 else
667                   aLine = Gadgets.split(sLine, sColDelim);
668                 // If current line does have the same number of columns as in the
669 // file definition then report an error or raise an exception
670 if (aLine.length!=iColFmtsCount) {
671                   iErrorCount++;
672                   if (DebugFile.trace) DebugFile.writeln(" Error: at line "+String.valueOf(iLine)+" has "+String.valueOf(aLine.length)+" columns but should have "+String.valueOf(iColFmtsCount));
673                   if (bRecoverable) {
674                     if (DebugFile.trace) DebugFile.writeln(" Connection.rollback()");
675                     try { oConn.rollback(); } catch (SQLException ignore) {}
676                   }
677                   if (oBadWrtr!=null) {
678                     oBadWrtr.write("Error: at line "+String.valueOf(iLine)+" has "+String.valueOf(aLine.length)+" columns but should have "+String.valueOf(iColFmtsCount)+"\r\n");
679                     oBadWrtr.write(sLine+"\r\n");
680                   }
681                   if (oDiscardWrtr!=null) {
682                     oDiscardWrtr.write(sLine+sRowDelim);
683                   }
684                 } else {
685                   // Up to here a single line as been readed and is kept in aLines array
686 try {
687                     oImplLoad.setAllColumnsToNull();
688                     if (bAllCaps) {
689                       for (c=0; c<iColFmtsCount; c++) {
690                         oColFmt = oColumns.getColumn(c);
691                         if (oColFmt.getSqlType()!=Types.NULL) {
692                           if (DebugFile.trace) DebugFile.writeln(" ImportLoader.put("+oColFmt.getName()+"("+String.valueOf(oColFmt.getPosition())+"),"+aLine[c]+")");
693                           oImplLoad.put(oColFmt.getPosition(), oColFmt.convert(aLine[c].toUpperCase()));
694                         } // fi (getSqlType()!=NULL)
695 } // next c)
696 if (null!=sCategory) oImplLoad.put("gu_category", sCategory);
697                     } else {
698                       for (c=0; c<iColFmtsCount; c++) {
699                         oColFmt = oColumns.getColumn(c);
700                         if (oColFmt.getSqlType()!=Types.NULL) {
701                           if (DebugFile.trace) DebugFile.writeln(" ImportLoader.put("+oColFmt.getName()+"("+String.valueOf(oColFmt.getPosition())+"),"+aLine[c]+")");
702                           oImplLoad.put(oColFmt.getPosition(), oColFmt.convert(aLine[c]));
703                         } // fi (getSqlType()!=NULL)
704 } // next c)
705 if (null!=sCategory) oImplLoad.put("gu_category", sCategory);
706                     } // fi (ALLCAPS)
707 oImplLoad.store(oConn, sWorkArea, iFlags);
708                   } catch (Exception xcpt) {
709                     iErrorCount++;
710                     if (DebugFile.trace) DebugFile.writeln(" "+xcpt.getClass().getName()+": at line "+String.valueOf(iLine)+" "+xcpt.getMessage());
711                     if (bRecoverable) {
712                       if (DebugFile.trace) DebugFile.writeln(" Connection.rollback()");
713                       try { oConn.rollback(); } catch (SQLException ignore) {}
714                     } // fi (bRecoverable)
715 if (oBadWrtr!=null) {
716                       oBadWrtr.write(xcpt.getClass().getName()+": at line "+String.valueOf(iLine)+" "+xcpt.getMessage()+"\r\n");
717                       oBadWrtr.write(sLine+"\r\n");
718                     }
719                     if (oDiscardWrtr!=null) {
720                       oDiscardWrtr.write(sLine+sRowDelim);
721                     }
722                   } // catch
723 } // fi (aLine.length!=iColFmtsCount)
724 } // fi (sLine!="")
725 } // fi (iLine>iSkip)
726 oRow.setLength(0);
727             iLine++;
728             if (-1==i) break;
729           }
730           else {
731             oRow.append((char)i);
732           } // fi (r>0)
733 } // wend
734 if (oInBuff!=null) { oInBuff.close(); }
735         oInBuff=null;
736         if (oInStrm!=null) { oInStrm.close(); }
737         oInStrm=null;
738         if (oBadWrtr!=null) { oBadWrtr.close(); oBadWrtr=null; }
739         if (oDiscardWrtr!=null) { oDiscardWrtr.close(); oDiscardWrtr=null; }
740         oImplLoad.close();
741         if (bRecoverable) {
742           if (0==iErrorCount)
743             oConn.commit();
744           else
745             oConn.rollback();
746         }
747         oConn.close();
748       } catch (Exception xcpt) {
749         if (DebugFile.trace) DebugFile.writeln(" "+xcpt.getClass().getName()+": at row "+String.valueOf(r)+" "+xcpt.getMessage());
750         try { if (null!=oDiscardWrtr) oDiscardWrtr.close(); } catch (Exception ignore) {}
751         try { if (null!=oBadWrtr) oBadWrtr.close(); } catch (Exception ignore) {}
752         try { if (null!=oInBuff) oInBuff.close(); } catch (Exception ignore) {}
753         try { if (null!=oInStrm) oInStrm.close(); } catch (Exception ignore) {}
754         try { oImplLoad.close(); } catch (Exception ignore) {}
755         try { if (bRecoverable) oConn.rollback(); } catch (Exception ignore) {}
756         try { oConn.close(); } catch (Exception ignore) {}
757         throw new ImportExportException(xcpt.getClass().getName() + " " + xcpt.getMessage() + " at row " + String.valueOf(r));
758       }
759     } // fi (sCmd==APPEND || sCmd==UPDATE || sCmd==APPENDUPDATE)
760
761     if (sCmd.equalsIgnoreCase("EXPORT")) {
762       try {
763         if (DebugFile.trace) DebugFile.writeln(" new FileOutputStream("+sOutFile+")");
764         oOutStrm = new FileOutputStream (sOutFile);
765         oOutBuff = new BufferedOutputStream (oOutStrm);
766         // Use an stream reader for decoding bytes into the proper charset
767 } catch (IOException ioe) {
768         try { oConn.close(); } catch (Exception ignore) {}
769         throw new ImportExportException("IOException "+ioe.getMessage(), ioe);
770       } catch (SecurityException jse) {
771         try { oConn.close(); } catch (Exception ignore) {}
772         throw new ImportExportException("SecurityException "+jse.getMessage(), jse);
773       }
774
775       try {
776         sToken = "";
777         for (c = 0; c < iColFmtsCount; c++) {
778           oColFmt = oColumns.getColumn(c);
779           sToken = (sToken.length() == 0 ? "" : ",") + oColFmt.getName();
780         } // next
781 DBSubset oDbs = new DBSubset(sEntity, sToken, sWhere, 100);
782         oDbs.setColumnDelimiter(sColDelim);
783         oDbs.setRowDelimiter(sRowDelim);
784         oDbs.print(oConn, oOutBuff);
785         oOutBuff.close();
786         oOutStrm.close();
787       } catch (Exception xcpt) {
788         try { oOutBuff.close(); } catch (Exception ignore) {}
789         try { oOutStrm.close(); } catch (Exception ignore) {}
790         try { oConn.close(); } catch (Exception ignore) {}
791         throw new ImportExportException(xcpt.getClass().getName()+xcpt.getMessage(), xcpt);
792       }
793     } // fi (sCmd==EXPORT)
794
795     if (DebugFile.trace) {
796       DebugFile.writeln("End ImportExport.perform() : "+String.valueOf(iErrorCount));
797     }
798     return iErrorCount;
799   } // perform
800 }
801

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags