1: /*
2: * Convert.java -- Converter class - Filemaker to SQL Converter Copyright (C)
3: * 2003 Robert Gordesch (rogo@mpiwg-berlin.mpg.de) This program is free
4: * software; you can redistribute it and/or modify it under the terms of the GNU
5: * General Public License as published by the Free Software Foundation; either
6: * version 2 of the License, or (at your option) any later version. Please read
7: * license.txt for the full details. A copy of the GPL may be found at
8: * http://www.gnu.org/copyleft/lgpl.html You should have received a copy of the
9: * GNU General Public License along with this program; if not, write to the Free
10: * Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
11: * USA Created on 15.09.2003 by rogo
12: */
13:
14: import java.awt.Cursor;
15: import java.io.BufferedReader;
16: import java.io.BufferedWriter;
17: import java.io.File;
18: import java.io.FileInputStream;
19: import java.io.FileNotFoundException;
20: import java.io.FileOutputStream;
21: import java.io.IOException;
22: import java.io.InputStream;
23: import java.io.InputStreamReader;
24: import java.io.OutputStreamWriter;
25: import java.io.PrintStream;
26: import java.net.URL;
27: import java.sql.PreparedStatement;
28: import java.sql.ResultSet;
29: import java.sql.SQLException;
30: import java.sql.Statement;
31: import java.sql.Types;
32: import java.text.ParseException;
33: import java.util.ArrayList;
34: import java.util.Hashtable;
35: import java.util.Iterator;
36: import java.util.List;
37: import java.util.StringTokenizer;
38: import java.util.TreeSet;
39: import java.util.Vector;
40:
41: import javax.swing.JDialog;
42: import javax.swing.JLabel;
43: import javax.swing.JPanel;
44:
45: import com.exploringxml.xml.Node;
46: import com.exploringxml.xml.Xparse;
47:
48: class Convert
49: {
50: /**
51: * Helper class for index creation
52: *
53: * @author rogo
54: *
55: */
56: public static class IndexList extends Vector
57: {
58: public String toString()
59: {
60: StringBuffer buff = new StringBuffer(1000);
61: int count = 0;
62: for (Iterator iter = this.iterator(); iter.hasNext();)
63: {
64: String element = (String) iter.next();
65: if (count < elementCount - 1)
66: {
67: buff.append(element).append(", ");
68: count++;
69: } else
70: buff.append(element);
71:
72: }
73:
74: return buff.toString();
75: }
76: }
77:
78: /**
79: * Helper class for pre/or post execution of SQLCommands
80: * @author rogo
81: *
82: */
83: public static class SQLCommand
84: {
85: private DBBean bean;
86: private String command;
87:
88: public SQLCommand(DBBean bean, String command)
89: {
90: this.bean = bean;
91: this.command = command;
92: }
93:
94: public void executeCommand() throws SQLException, Exception
95: {
96: System.out.println("Executing command: \n");
97: System.out.println(command);
98:
99: java.sql.Connection con = bean.getConnection();
100: Statement stm = con.createStatement();
101: stm.execute(command);
102: stm.close();
103: }
104: }
105:
106: static DBBean bean = new DBBean();
107:
108: static DBBean beanDest = new DBBean();
109:
110: static String user = "", passwd = "e1nste1n";
111:
112: static String userDest = "postgres", passwdDest = "rogo";
113:
114: static boolean batchRun = false;
115:
116: static Vector databases = new Vector();
117:
118: final static int numHits = 5000;
119:
120: final static int numIntervalls = 4;
121:
122: static boolean debug = !false;
123:
124: static boolean isGUI = true;
125:
126: /**
127: * Vector for all SQLCommands to executed before any conversion action is performed
128: */
129: static Vector preSQLCommands = new Vector();
130: /**
131: * Vector for all SQLCommands to executed after any conversion action has been performed
132: */
133:
134: static Vector postSQLCommands = new Vector();
135:
136: static final String versionID = new String("FM2SQL Version 0.9.1b\n");
137:
138: private static boolean noError = false;
139:
140: public static void main(String args[]) throws IOException
141: {
142: /*
143: * try { //byte[] b = "�".getBytes("UTF-8"); //
144: * System.out.println("QueryString " +b[0]+" "+b[1]+(new
145: * String(b).getBytes()[0])+" "+new String(b).getBytes()[1]);
146: * //System.out.println(new String(b,"UTF-8")); } catch
147: * (UnsupportedEncodingException e) { e.printStackTrace(); }
148: */
149: File tmpPath = new File(System.getProperty("java.io.tmpdir"));
150: isGUI = false;
151: FileOutputStream file = null;
152: if (args.length != 1)
153: {
154: System.out.println(versionID);
155: System.out.println("Usage: java Convert <xml config file>");
156: System.exit(-1);
157: }
158: File temp = null;
159: try
160: {
161: temp = new File(tmpPath, "fm2sql.txt");
162: int count = 1;
163: while (temp.exists())
164: {
165: temp = new File(tmpPath, "fm2sql" + generateSuffix(count++) + ".txt");
166: }
167: file = new FileOutputStream(temp);
168: } catch (FileNotFoundException e1)
169: {
170: e1.printStackTrace();
171: }
172: PrintStream stream = new PrintStream(file, true);
173: // write info for user to stdout
174: System.out.println(versionID);
175: System.out.println("Loading " + args[0] + "....");
176: System.out.println("Log will be written to " + temp.getCanonicalPath());
177:
178: if (!debug)
179: {
180: System.setOut(stream);
181: System.setErr(stream);
182: }
183: System.out.println(versionID);
184: System.out.println("Using config file : " + args[0] + "....");
185:
186: StringBuffer sb = readXMLFile(args[0]);
187: parseXMLConfig(sb);
188: if (!(new File(args[0]).exists()))
189: {
190:
191: System.exit(0);
192: }
193: System.out.println("Finished!");
194: // convert("jdbc:fmpro:http://141.14.237.74:8050","jdbc:postgresql://erebos/test",null,null);
195: }
196:
197: public static void convertBatch(DBBean source, DBBean destination, Vector names, Vector layouts,
198: Vector selects, Vector creates, Vector ids, int mode, String delimiter) throws Exception
199: {
200: bean = source;
201: beanDest = destination;
202: convert(null, null, names, layouts, selects, creates, ids, mode, delimiter);
203: }
204:
205: public static String formatFileMakerArray(List list, String delimiter)
206: {
207: StringBuffer formattedString = new StringBuffer();
208: for (int i = 0; i < list.size(); ++i)
209: {
210: formattedString.append(list.get(i).toString());
211: if (i < list.size() - 1)
212: formattedString.append(delimiter);
213: }
214: return formattedString.toString();
215: }
216:
217: /**
218: * Method for SQL UPDATE
219: *
220: * @param source
221: * @param destination
222: * @param names
223: * @param layouts
224: * @param selects
225: * @param creates
226: * @param ids
227: * @param mode
228: * @throws Exception
229: */
230: public static void update(String source, String destination, Vector names, Vector layouts,
231: Vector selects, Vector creates, Vector ids, int mode) throws Exception
232: {
233:
234: FM2SQL.ProgressDialog dialog = null;
235: if (isGUI)
236: {
237: dialog = initDialog();
238: }
239: // setting user and passwd
240: bean.setUserAndPasswd(user, passwd);
241: // setting user and passwd
242: beanDest.setUserAndPasswd(userDest, passwdDest);
243:
244: StringBuffer command = null;
245: String query = null;
246: try
247: {
248:
249: bean.setConnection(source);
250:
251: if (names == null)
252: names = bean.getTableNames();
253: // Collections.sort(names);
254: int tbIndex = 1;
255:
256: System.out.println("Start at table " + names.firstElement());
257: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
258: {
259: Vector[] result = null;
260: String destTableName = "";
261: try
262: {
263: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
264: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
265: query = (selects != null) ? selects.get(tbIndex).toString() : query;
266: // if vectors[1].get(i) != null)
267: if (!layout.equals(""))
268: {
269:
270: query = addLayoutToQuery(names, query, tbIndex, layout);
271:
272: }
273: if (dialog != null)
274: {
275: prepareDialogforUse(names, dialog, tbIndex);
276: }
277: bean.getConnection();
278: bean.makeQuery(query, 0);
279:
280: } catch (Exception e)
281: {
282: System.out.println("Warning exception occured \n " + e);
283:
284: continue;
285: }
286: // determine destTableName from createStatement or from source
287: // table name
288: if (!creates.get(tbIndex).equals(""))
289: {
290: String create = creates.get(tbIndex).toString().toLowerCase();
291: int fromIndex = create.indexOf("table") + 5;
292: int toIndex = create.indexOf("(");
293: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "")
294: .trim();
295: System.out.println("destTable " + destTableName);
296:
297: } else
298: destTableName = convertText(names.get(tbIndex).toString());
299:
300: beanDest.setConnection(destination);
301:
302: Statement stm = beanDest.getConnection().createStatement();
303:
304: Vector tables = beanDest.getTableNames();
305:
306: System.out.println(names.get(tbIndex) + " "
307: + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
308: tables = beanDest.getTableNames();
309: stm = beanDest.getConnection().createStatement();
310:
311: if (dialog != null)
312: dialog.title.setText("Updating table data ...");
313: else
314: System.out.println("Updating table data ...");
315: int j = -1;
316:
317: Vector row = null;
318: command = new StringBuffer();
319:
320: command.append("UPDATE ");
321: command.append(beanDest.getQC());
322: command.append(destTableName);
323: // command.append(convertText((String) names.get(tbIndex)));
324: command.append(beanDest.getQC());
325: command.append(" SET ");
326:
327: int size = bean.getColumnNames().size();
328: for (int i = 0; i < size - 1; ++i)
329: command.append(beanDest.getQC() + convertText((String) bean.getColumnNames().get(i))
330: + beanDest.getQC() + " = ? ,");
331: command.append(convertText((String) bean.getColumnNames().get(size - 1)) + " = ? ");
332: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
333: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
334: System.out.println(command + " " + tbIndex);
335: int rowCount = bean.getRowCount(query);
336: int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
337: while ((row = bean.getNextRow()) != null)
338: {
339: j++;
340: // print rows
341: Object obj = null;
342: for (int k = 0; k < row.size(); ++k)
343: {
344: obj = row.get(k);
345: if (obj instanceof ArrayList)
346: obj = ((List) obj).get(0);
347: String str = (obj == null) ? "NULL" : obj.toString();
348: if (!str.equals("NULL"))
349: pstm.setString(k + 1, str);
350: else
351: pstm.setNull(k + 1, Types.NULL);
352: }
353: pstm.setString(row.size() + 1, row.get(idIndex).toString());
354: pstm.execute();
355: if (dialog != null)
356: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
357: command = null;
358: } // to for loop
359:
360: }
361: } catch (Exception e)
362: {
363: System.out.println("Error while connecting to database " + e);
364: if (isGUI)
365: {
366: showExceptionDialog(dialog, command, e);
367: } else
368: {
369: e.printStackTrace();
370:
371: }
372: } finally
373: {
374: if (isGUI)
375: {
376: resetGUI(dialog);
377: }
378: }
379: }
380:
381: /**
382: * @param dialog
383: */
384: private static void resetGUI(FM2SQL.ProgressDialog dialog)
385: {
386: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
387: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
388: dialog.setVisible(false);
389: }
390:
391: /**
392: * @param dialog
393: * @param command
394: * @param e
395: */
396: private static void showExceptionDialog(FM2SQL.ProgressDialog dialog, StringBuffer command,
397: Exception e)
398: {
399: dialog.setVisible(false);
400: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
401: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
402: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
403: java.io.PrintStream stream = new java.io.PrintStream(b);
404: stream.print(command + "\n\n");
405: e.printStackTrace(stream);
406: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
407: }
408:
409: /**
410: * @return
411: */
412: private static FM2SQL.ProgressDialog initDialog()
413: {
414: FM2SQL.ProgressDialog dialog;
415: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
416: dialog.setTitle("Conversion running ...");
417: dialog.title.setText("Getting table data ...");
418: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x
419: + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y
420: + (FM2SQL.fmInstance.getHeight() - 250) / 2);
421: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
422: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
423: dialog.thread = Thread.currentThread();
424: dialog.setSize(420, 250);
425: return dialog;
426: }
427:
428: /**
429: * transfers the specified array of tables to the destination database and
430: * creates the table if it does not exist if it exists and mode is not append
431: * the table is dropped
432: *
433: * @param source
434: * @param destination
435: * @param names
436: * @param layouts
437: * @param selects
438: * @param creates
439: * @param ids
440: * @param mode
441: * @throws Exception
442: */
443:
444: public static void convert(String source, String destination, Vector names, Vector layouts,
445: Vector selects, Vector creates, Vector ids, int mode, String delimiter) throws Exception
446: {
447: // TODO implement convert_temp mode!!!
448: FM2SQL.ProgressDialog dialog = null;
449:
450: if (isGUI)
451: {
452: dialog = initDialog();
453: }
454:
455: System.out.println("connection established " + source + " " + bean.url);
456:
457: java.util.TreeSet myIds = new TreeSet();
458: int deltaID = 1;
459: String idField = "";
460: String destTableName = "";
461: String[] fieldNames = null;
462:
463: if (source != null && destination != null)
464: {
465: // setting user and passwd
466: bean.setUserAndPasswd(user, passwd);
467: // setting user and passwd
468: beanDest.setUserAndPasswd(userDest, passwdDest);
469: }
470:
471: StringBuffer command = null;
472: String query = null;
473: try
474: {
475:
476: if (source != null)
477: bean.setConnection(source);
478: else
479: bean.setConnection(bean.url);
480: if (names == null)
481: names = bean.getTableNames();
482: int tbIndex = 1;
483:
484: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
485: {
486: Vector[] result = null;
487: try
488: {
489: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
490: query = (selects != null) ? selects.get(tbIndex).toString() : "select * from "
491: + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
492: if (layout.intern() != "")
493: {
494: query = addLayoutToQuery(names, query, tbIndex, layout);
495:
496: }
497: if (dialog != null)
498: {
499: prepareDialogforUse(names, dialog, tbIndex);
500: }
501:
502: bean.getConnection();
503: bean.makeQuery(query, 50);
504: idField = ids.get(tbIndex).toString();
505:
506: } catch (Exception e)
507: {
508: System.out.println("Warning exception occured \n " + e);
509:
510: continue;
511: }
512: if (destination != null)
513: beanDest.setConnection(destination);
514: else
515: beanDest.setConnection(beanDest.url);
516: Statement stm = beanDest.getConnection().createStatement();
517:
518: Vector tables = beanDest.getTableNames();
519: // Collections.sort(tables);
520: System.out.println(names.get(tbIndex) + " "
521: + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
522: tables = beanDest.getTableNames();
523: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
524: stm = beanDest.getConnection().createStatement();
525: // System.exit(0);
526:
527: // determine destTableName from createStatement or from source
528: // table name
529: ConversionProperties prop = getFieldNamesAndDestTableName(creates.get(tbIndex).toString(),
530: query, names.get(tbIndex).toString());
531: destTableName = prop.destTableName;
532: if (mode == Convert.DataBase.CONVERT_TEMP_MODE)
533: {
534: destTableName += "_temp";
535: }
536: fieldNames = prop.fieldNames;
537: if (mode == Convert.DataBase.CONVERT_MODE || mode == Convert.DataBase.CONVERT_TEMP_MODE)
538: {
539:
540: if (tables.indexOf(destTableName) >= 0)
541: {
542: stm.executeUpdate("drop table " + beanDest.getQC() + destTableName + beanDest.getQC());
543: tables.remove(destTableName);
544: System.out.println("dropped table" + destTableName);
545: }
546: if ((tables.indexOf(destTableName) < 0))
547: {
548:
549: if (creates.get(tbIndex).equals("")
550: || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
551: {
552: System.out
553: .println("Warning empty or invalid create statement - creating one for you\n");
554:
555: command = new StringBuffer(50);
556: command.append("CREATE TABLE ");
557: command.append(beanDest.getQC());
558: command.append(destTableName);//convertText((String) names.get(tbIndex)));
559: command.append(beanDest.getQC());
560: command.append("(");
561: String type = null;
562: Vector columnNames = bean.getColumnNames();
563: for (int i = 0; i < columnNames.size() - 1; ++i)
564: {
565: type = bean.metaData.getColumnTypeName(i + 1);
566: // System.out.println(i+" "+result[1].get(i)+"
567: // "+type);
568: type = (type.equals("NUMBER")) ? "INT4" : type;
569: type = (type.equals("CONTAINER")) ? "TEXT" : type;
570:
571: command.append(beanDest.getQC() + convertText((String) columnNames.get(i))
572: + beanDest.getQC() + " " + type + ", ");
573: }
574: type = bean.metaData.getColumnTypeName(columnNames.size());
575: type = (type.equals("NUMBER")) ? "INT4" : type;
576: type = (type.equals("CONTAINER")) ? "TEXT" : type;
577: command.append(beanDest.getQC()
578: + convertText((String) columnNames.get(columnNames.size() - 1))
579: + beanDest.getQC() + " " + type);
580: command.append(" )");
581:
582: } else
583: command = new StringBuffer().append(creates.get(tbIndex).toString().toLowerCase());
584: stm.executeUpdate(command.toString());
585:
586: }
587: }
588: if (dialog != null)
589: dialog.title.setText("Writing table data ...");
590:
591: // prepare the insert statement
592: int j = -1;
593: Vector row = null;
594: command = new StringBuffer();
595:
596: command.append("INSERT INTO ");
597: command.append(beanDest.getQC());
598: command.append(destTableName);
599: command.append(beanDest.getQC());
600: command.append(" (");
601: for (int i = 0; i < fieldNames.length; i++)
602: {
603: command.append(fieldNames[i]);
604: if (i < fieldNames.length - 1)
605: command.append(",");
606: }
607: command.append(") ");
608:
609: command.append(" values ( ");
610: // add a question marks for every field
611: for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
612: command.append("?,");
613: command.append("?)");
614: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
615: System.out.println(command);
616: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
617: Vector vec = new Vector(myIds);
618: int endIndex = -1;
619: String tempQuery = query;
620: String tempID = bean.getQC() + idField + bean.getQC();
621: // if id_field not do incremental conversion else do it all at
622: // once
623: if (!idField.equals(""))
624: {
625: long startTime = System.currentTimeMillis();
626: int counter = -1;
627: while (true)
628: {
629: ++counter;
630: if (counter == 0 && dialog != null)
631: dialog.title.setText("Check if data is available");
632: else if (dialog != null)
633: dialog.title.setText("Check if more data is available");
634: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex),
635: tempQuery, numHits);
636: if (myIds.isEmpty())
637: break;
638: vec = new Vector(myIds);
639: rowCount = vec.size();
640: System.out.println("ID LIST SIZE "
641: + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIds.size());
642: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
643: if (vec.size() <= numIntervalls)
644: {
645: endIndex = 0;
646: deltaID = vec.size();
647: }
648: for (int k = 0; k < vec.size() - deltaID; k = k + deltaID)
649: {
650: System.out.println(vec.get(k) + " " + vec.get(k + deltaID) + " " + vec.lastElement());
651: if (query.indexOf("where") > 0)
652: tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID
653: + "<='" + vec.get(k + deltaID) + "'";
654: else
655: tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID
656: + "<='" + vec.get(k + deltaID) + "'";
657: System.out.println(tempQuery);
658: if (dialog != null)
659: dialog.title.setText("Reading table data ...");
660:
661: bean.makeQuery(tempQuery, deltaID);
662: if (dialog != null)
663: dialog.title.setText("Writing table data ...");
664:
665: command = writeDatainDestTable(dialog, command, k, pstm, rowCount, delimiter);
666: endIndex = k + deltaID;
667: }
668: System.out.println(endIndex);
669: // all data written ? if not write last chunk of data
670: if (endIndex == vec.size() - 1)
671: System.out.println("fits");
672: else
673: {
674: System.out.println(" last intervall from " + vec.get(endIndex) + " "
675: + vec.lastElement());
676:
677: if (query.indexOf("where") > 0)
678: tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and "
679: + tempID + "<='" + vec.lastElement() + "'";
680: else
681: tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and "
682: + tempID + "<='" + vec.lastElement() + "'";
683: System.out.println(tempQuery);
684: if (dialog != null)
685: dialog.title.setText("Reading table data ...");
686: bean.makeQuery(tempQuery, 0);
687: if (dialog != null)
688: dialog.title.setText("Writing table data ...");
689: command = writeDatainDestTable(dialog, command, endIndex, pstm, rowCount, delimiter);
690: }
691: // prepare new query for next chunk
692: if (query.indexOf("where") > 0)
693: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
694: else
695: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
696:
697: }
698: long endTime = System.currentTimeMillis();
699: System.out.println("Time for incremental convert elapsed " + (endTime - startTime));
700: } else
701: {
702: // read and write all in one big chunk
703: long startTime = System.currentTimeMillis();
704:
705: bean.makeQuery(query, 0);
706: System.err.println("query for whole table done");
707: command = writeDatainDestTable(dialog, command, j, pstm, rowCount, delimiter);
708: long endTime = System.currentTimeMillis();
709: System.out.println("Time for old convert elapsed " + (endTime - startTime));
710:
711: }
712: if (isGUI)
713: resetGUI(dialog);
714: noError = true;
715: }
716: } catch (Exception e)
717: {
718: System.out.println("Error while connecting to database " + e.getMessage());
719: noError = false;
720: if (isGUI)
721: {
722: showExceptionDialog(dialog, command, e);
723: resetGUI(dialog);
724: } else
725: {
726: e.printStackTrace();
727:
728: }
729: } catch (Error e)
730: {
731: System.out.println(e);
732: e.printStackTrace();
733: }
734:
735: }
736:
737: /**
738: * @param names
739: * @param dialog
740: * @param tbIndex
741: */
742: private static void prepareDialogforUse(Vector names, FM2SQL.ProgressDialog dialog, int tbIndex)
743: {
744: dialog.title.setText("Reading table data ...");
745: dialog.table.setText(names.get(tbIndex).toString());
746: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
747: dialog.setVisible(true);
748: }
749:
750: /**
751: * @param names
752: * @param query
753: * @param tbIndex
754: * @param layout
755: * @return
756: */
757: private static String addLayoutToQuery(Vector names, String query, int tbIndex, String layout)
758: {
759: layout = " layout " + bean.getQC() + layout + bean.getQC();
760: String name = names.get(tbIndex).toString();
761: StringBuffer queryLayout = new StringBuffer(query);
762: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
763: query = queryLayout.toString();
764: System.out.println("added layout " + query);
765: return query;
766: }
767:
768: /**
769: * Writes data to the destination table
770: *
771: * @param dialog
772: * progress dialog
773: * @param command
774: * @param j
775: * data index for progress bar
776: * @param pstm
777: * prepared statement
778: * @param rowCount
779: * number of datasets
780: * @return command
781: * @throws Exception
782: * @throws SQLException
783: */
784: private static StringBuffer writeDatainDestTable(FM2SQL.ProgressDialog dialog,
785: StringBuffer command, int j, PreparedStatement pstm, int rowCount, String delimiter)
786: throws Exception, SQLException
787: {
788: Vector row;
789: while ((row = bean.getNextRow()) != null)
790: {
791: j++;
792: Object obj = null;
793: for (int k = 0; k < row.size(); ++k)
794: {
795: obj = row.get(k);
796:
797: if (obj instanceof ArrayList)
798: obj = formatFileMakerArray((List) obj, delimiter);
799:
800: String str = (obj == null) ? "NULL" : obj.toString();
801: if (obj instanceof Double)
802: {
803: pstm.setDouble(k + 1, ((Double) obj).doubleValue());
804: } else if (!str.equals("NULL"))
805: pstm.setString(k + 1, str);
806: else
807: pstm.setNull(k + 1, Types.NULL);
808: }
809: pstm.execute();
810: if (isGUI)
811: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
812: command = null;
813: } // to while loop
814: return command;
815: }
816:
817: /**
818: * removes special characters from the input string as well as .fp5
819: *
820: * @param newName
821: * String to change
822: * @return
823: */
824: public static String convertText(String newName)
825: {
826: StringBuffer alterMe = new StringBuffer(newName.trim().toLowerCase());
827: int length = alterMe.length();
828: int j = 0;
829: int index = alterMe.indexOf(".fp5");
830: if (index >= 0)
831: {
832: alterMe.delete(index, index + 4);
833: length = length - 4;
834: }
835:
836: while (j < length)
837: {
838: if (alterMe.charAt(j) == ' ')
839: {
840: alterMe.setCharAt(j, '_');
841: // if(j<length-1) j=j+1;
842: } else if (alterMe.charAt(j) == '_')
843: {
844:
845: if (alterMe.charAt(j + 1) == '_')
846: alterMe.deleteCharAt(j);
847: length = length - 1;
848: // if(j<length-1) j=j+1;
849: } else if (alterMe.charAt(j) == 'ä')
850: {
851: alterMe.setCharAt(j, 'a');
852: alterMe.insert(j + 1, "e");
853: length = length + 1;
854: if (j < length - 1)
855: j = j + 1;
856: } else if (alterMe.charAt(j) == 'ö')
857: {
858: alterMe.setCharAt(j, 'o');
859: alterMe.insert(j + 1, "e");
860: length = length + 1;
861: if (j < length - 1)
862: j = j + 1;
863: } else if (alterMe.charAt(j) == 'ü')
864: {
865: alterMe.setCharAt(j, 'u');
866: alterMe.insert(j + 1, "e");
867: length = length + 1;
868: if (j < length - 1)
869: j = j + 1;
870: } else if (alterMe.charAt(j) == 'ß')
871: {
872: alterMe.setCharAt(j, 's');
873: alterMe.insert(j + 1, "s");
874: length = length + 1;
875: if (j < length - 1)
876: j = j + 1;
877: } else if (alterMe.charAt(j) == ':')
878: {
879: if (j < length - 1)
880: {
881: if (alterMe.charAt(j + 1) == ':')
882: {
883: alterMe.setCharAt(j, '_');
884: alterMe.delete(j + 1, j + 2);
885: length = length - 1;
886:
887: }
888:
889: if (j < length - 1)
890: j = j + 1;
891: }
892: } else if (alterMe.charAt(j) == '-')
893: {
894: alterMe.setCharAt(j, '_');
895:
896: } else if (alterMe.charAt(j) == '?')
897: {
898: // changed ? to _ because of update statement
899: alterMe.setCharAt(j, '_');
900: // length = length + 1;
901: // j=j+1;
902: System.out.println(alterMe);
903: } else if (alterMe.charAt(j) == '.')
904: {
905: if (j == length - 1)
906: {
907: alterMe.delete(j, j);
908: length--;
909: } else
910: alterMe.setCharAt(j, '_');
911: }
912:
913: ++j;
914: }
915: return alterMe.toString();
916: }
917:
918: /**
919: * Converts > and < in an entity (> or <)
920: *
921: * @param newName
922: * @return
923: */
924: public static String convertToEntities(String newName)
925: {
926: StringBuffer alterMe = new StringBuffer(newName.trim());
927: int length = alterMe.length();
928: int j = 0;
929:
930: while (j < length)
931: {
932:
933: if (alterMe.charAt(j) == '>')
934: {
935: alterMe.setCharAt(j, '&');
936: alterMe.insert(j + 1, "gt;");
937: length = length + 2;
938: if (j < length - 1)
939: j = j + 1;
940:
941: } else if (alterMe.charAt(j) == '<')
942: {
943: alterMe.setCharAt(j, '&');
944: alterMe.insert(j + 1, "lt;");
945: length = length + 2;
946: if (j < length - 1)
947: j = j + 1;
948:
949: }
950: ++j;
951: }
952: return alterMe.toString();
953: }
954:
955: /**
956: * Masks the single quote character '-->\'
957: *
958: * @param newName
959: * @return
960: */
961: public static String convertUml(String newName)
962: {
963: StringBuffer alterMe = new StringBuffer(newName.trim());
964: int length = alterMe.length();
965: int j = 0;
966:
967: while (j < length)
968: {
969:
970: if (alterMe.charAt(j) == '\'')
971: {
972: alterMe.setCharAt(j, '\\');
973: alterMe.insert(j + 1, "'");
974: length = length + 1;
975: if (j < length - 1)
976: j = j + 1;
977: }
978: /*
979: * else if (alterMe.charAt(j) == '"') { alterMe.setCharAt(j, '\\');
980: * alterMe.insert(j + 1, "\""); length = length + 1; if(j <length-1)
981: * j=j+1; } else if (alterMe.charAt(j) == '>') { alterMe.setCharAt(j,
982: * '\\'); alterMe.insert(j + 1, ">"); length = length + 1; if(j <length-1)
983: * j=j+1; } else if (alterMe.charAt(j) == ' <') { alterMe.setCharAt(j,
984: * '\\'); alterMe.insert(j + 1, " <"); length = length + 1; if(j
985: * <length-1) j=j+1; } else if (alterMe.charAt(j) == '?') {
986: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "?"); length = length +
987: * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == '&') {
988: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "&"); length = length +
989: * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == '=') {
990: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "="); length = length +
991: * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == ',') {
992: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, ","); length = length +
993: * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == '.') {
994: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "."); length = length +
995: * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == '[') {
996: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "."); length = length +
997: * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == ']') {
998: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "."); length = length +
999: * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == '%') {
1000: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "%"); length = length +
1001: * 1; if(j <length-1) j=j+1; }
1002: */
1003: ++j;
1004: }
1005: return alterMe.toString();
1006: }
1007:
1008: /**
1009: * parses the input xml file for batch conversion called from readXMLFile *
1010: *
1011: * @param sb
1012: */
1013: public static void parseXMLConfig(StringBuffer sb)
1014: {
1015: // boolean finished = false;
1016: Vector databases = new Vector();
1017: try
1018: {
1019: databases = getXMLConfig(sb);
1020:
1021: // destination DataBase object
1022: DataBase dbDest = ((DataBase) databases.lastElement());
1023:
1024: DBBean database = ((DataBase) databases.lastElement()).bean;
1025: databases.remove(databases.size() - 1);
1026:
1027: for (Iterator iterator = dbDest.preCommands.iterator(); iterator.hasNext();)
1028: {
1029: SQLCommand sqlCommand = (SQLCommand) iterator.next();
1030: sqlCommand.executeCommand();
1031: }
1032:
1033: // databases.add(database);
1034: for (Iterator iter = databases.iterator(); iter.hasNext();)
1035: {
1036: DataBase db = (DataBase) iter.next();
1037: for (Iterator iterator = db.preCommands.iterator(); iterator.hasNext();)
1038: {
1039: SQLCommand sqlCommand = (SQLCommand) iterator.next();
1040: sqlCommand.executeCommand();
1041: }
1042: int mode = db.mode;
1043: if (mode == DataBase.CONVERT_TEMP_MODE)
1044: {
1045: convertBatch(db.bean, database, db.tables, db.layouts, db.selects, db.creates, db.ids,
1046: mode, db.delimiter);
1047: if (noError)
1048: {
1049: System.out.println("no Error occured ");
1050: // db.bean.setURL(database.url);
1051: // db.bean.setUserAndPasswd(database.user,database.passwd);
1052: //
1053: // Convert.user = db.bean.user;
1054: // Convert.passwd = db.bean.passwd;
1055: // userDest = database.user;
1056: // passwdDest = database.passwd;
1057: // synchronize(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates,
1058: // db.ids, mode, db.delimiter, new Vector(db.htIndex.values()));
1059: }
1060: }
1061: if (mode == DataBase.CONVERT_MODE || mode == DataBase.APPEND_MODE)
1062: convertBatch(db.bean, database, db.tables, db.layouts, db.selects, db.creates, db.ids,
1063: mode, db.delimiter);
1064: else if (mode == DataBase.UPDATE_MODE)
1065: {
1066:
1067: Convert.user = db.bean.user;
1068: Convert.passwd = db.bean.passwd;
1069: userDest = database.user;
1070: passwdDest = database.passwd;
1071:
1072: update(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids,
1073: mode);
1074: } else if (mode == DataBase.SYNCHRONIZE_MODE)
1075: {
1076: Convert.user = db.bean.user;
1077: Convert.passwd = db.bean.passwd;
1078: userDest = database.user;
1079: passwdDest = database.passwd;
1080:
1081: synchronize(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates,
1082: db.ids, mode, db.delimiter, new Vector(db.htIndex.values()));
1083: }
1084: for (Iterator iterator = db.postCommands.iterator(); iterator.hasNext();)
1085: {
1086: SQLCommand sqlCommand = (SQLCommand) iterator.next();
1087: sqlCommand.executeCommand();
1088: }
1089:
1090: }
1091: for (Iterator iterator = dbDest.postCommands.iterator(); iterator.hasNext();)
1092: {
1093: SQLCommand sqlCommand = (SQLCommand) iterator.next();
1094: sqlCommand.executeCommand();
1095: }
1096:
1097: } catch (Exception e)
1098: {
1099:
1100: e.printStackTrace();
1101:
1102: } finally
1103: {
1104: bean.closeAllConnections();
1105: beanDest.closeAllConnections();
1106: }
1107: }
1108:
1109: public static Vector getXMLConfig(StringBuffer sb)
1110: {
1111:
1112: boolean finished = false;
1113: // parse string and build document tree
1114: Xparse parser = new Xparse();
1115: parser.changeEntities = true;
1116: Node root = parser.parse(sb.toString());
1117: // printContents(root);
1118: Vector databases = new Vector();
1119: Vector tables = new Vector();
1120: Vector layouts = new Vector();
1121: Vector selects = new Vector();
1122: Vector creates = new Vector();
1123: Vector ids = new Vector();
1124: Vector preSQLCommands = new Vector();
1125: Vector postSQLCommands = new Vector();
1126:
1127: String delimiter = "|";
1128: int mode = -1;
1129: try
1130: {
1131: Node tempNode = root.find("convert/source", new int[]
1132: { 1, 1 });
1133: if (tempNode == null)
1134: throw new Error("parse error source tag missing");
1135: System.out.println(tempNode.name);
1136: int length = countNodes(tempNode);
1137: for (int i = 1; i <= length; i++)
1138: {
1139:
1140: DBBean database = new DBBean();
1141: tables = new Vector();
1142: layouts = new Vector();
1143: selects = new Vector();
1144: creates = new Vector();
1145: ids = new Vector();
1146: preSQLCommands = new Vector();
1147: postSQLCommands = new Vector();
1148:
1149: // parse dataBase
1150: Node node = root.find("convert/source/database/url", new int[]
1151: { 1, 1, i, 1 });
1152: Node node1 = root.find("convert/source/database/user", new int[]
1153: { 1, 1, i, 1, 1 });
1154: Node node2 = root.find("convert/source/database/password", new int[]
1155: { 1, 1, i, 1, 1 });
1156: Node node3 = root.find("convert/source/database", new int[]
1157: { 1, 1, i });
1158: Node nodeMode = root.find("convert/source/database/mode", new int[]
1159: { 1, 1, i, 1, 1 });
1160: Node delimiterNode = root.find("convert/source/database/delimiter", new int[]
1161: { 1, 1, i, 1, 1 });
1162:
1163: Node commandNodes = root.find("convert/source/database/sqlcommands", new int[]
1164: { 1, 1, i, 1, 1 });
1165: if (commandNodes != null)
1166: {
1167: parseCommandNode(commandNodes, database, preSQLCommands, postSQLCommands);
1168: }
1169: Node useNormanToUnicodeMapper = root.find(
1170: "convert/source/database/usenormantounicodemapper", new int[]
1171: { 1, 1, i, 1, 1 });
1172:
1173: if (delimiterNode != null)
1174: delimiter = delimiterNode.getCharacters();
1175: if (useNormanToUnicodeMapper != null)
1176: {
1177: database.setUseNormanToUnicodeMapper(Boolean.valueOf(
1178: useNormanToUnicodeMapper.getCharacters()).booleanValue());
1179: System.out.println("useMapper "
1180: + Boolean.valueOf(useNormanToUnicodeMapper.getCharacters().trim()).booleanValue());
1181: }
1182:
1183: if (node3 == null)
1184: throw new Error("parse error database tag missing");
1185: if (node == null)
1186: throw new Error("parse error url tag missing");
1187: if (node1 == null)
1188: throw new Error("parse error user tag missing");
1189: if (node2 == null)
1190: throw new Error("parse error password tag missing");
1191: String url = node.getCharacters();
1192: String user = node1.getCharacters();
1193: String password = node2.getCharacters();
1194: database.setURL(url.trim());
1195: database.setUserAndPasswd(user.trim(), password.trim());
1196: System.out.println(node.name + " " + node.getCharacters());
1197: System.out.println(node1.name + " " + node1.getCharacters());
1198: System.out.println(node2.name + " " + node2.getCharacters());
1199: String modeString = "";
1200: if (nodeMode == null)
1201: modeString = "convert";
1202: else
1203: modeString = nodeMode.getCharacters();
1204: if (modeString.equals("convert"))
1205: mode = DataBase.CONVERT_MODE;
1206: else if (modeString.equals("append"))
1207: mode = DataBase.APPEND_MODE;
1208: else if (modeString.equals("update"))
1209: mode = DataBase.UPDATE_MODE;
1210: else if (modeString.equals("delete"))
1211: mode = DataBase.DELETE_MODE;
1212:
1213: else if (modeString.equals("synchronize"))
1214: mode = DataBase.SYNCHRONIZE_MODE;
1215: else if (modeString.equals("convert_temp"))
1216: mode = DataBase.CONVERT_TEMP_MODE;
1217:
1218: // if(node3!=null)
1219: // System.out.println(node3.name);
1220:
1221: int length2 = countNodes(node3);
1222:
1223: System.out.println("number of tables " + length2);
1224:
1225: for (int j = 1; j <= length2; ++j)
1226: {
1227: Node node4 = root.find("convert/source/database/table", new int[]
1228: { 1, 1, i, j });
1229: Node node5 = root.find("convert/source/database/table/select", new int[]
1230: { 1, 1, i, j, 1 });
1231: Node node6 = root.find("convert/source/database/table/create", new int[]
1232: { 1, 1, i, j, 1 });
1233:
1234: if (node4 != null)
1235: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1236: if (node5 != null)
1237: System.out.println(node5.name + " " + node5.getCharacters());
1238: if (node6 != null)
1239: System.out.println(node6.name + " " + node6.getCharacters());
1240: if (node4 == null)
1241: throw new Error("parse error table tag missing");
1242: // if(node5==null) throw new Error("parse error select tag
1243: // missing");
1244: // if(node6==null) throw new Error("parse error create tag
1245: // missing");
1246: String name = (String) node4.attributes.get("name");
1247: String layout = (String) node4.attributes.get("layout");
1248: String id = (String) node4.attributes.get("id");
1249: System.out.println("id was " + id);
1250:
1251: if (name == null)
1252: throw new Error("parse error required table tag attribute name missing");
1253: if (layout == null)
1254: layout = "";
1255: if (id == null)
1256: id = "";
1257: if (name.equals(""))
1258: throw new Error("parse error table tag attribute must not be empty");
1259: tables.add(name.intern());
1260: layouts.add(layout.intern());
1261: ids.add(id.intern());
1262: String query = (node5 == null) ? "" : node5.getCharacters();
1263: if (query.equals(""))
1264: System.err.println("Warning empty select tag or select tag missing !!");
1265: query = (query.equals("")) ? "select * from " + database.getQC() + name
1266: + database.getQC() : query;
1267: selects.add(query);
1268: if (node6 != null)
1269: creates.add(node6.getCharacters().trim());
1270: else
1271: creates.add("");
1272:
1273: }
1274: DataBase dataBase = new DataBase(database, tables, layouts, selects, creates, ids, mode);
1275: dataBase.delimiter = delimiter;
1276: dataBase.preCommands = new Vector(preSQLCommands);
1277: dataBase.postCommands = new Vector(postSQLCommands);
1278: databases.add(dataBase);
1279: }
1280: DBBean database = new DBBean();
1281:
1282: preSQLCommands.clear();
1283: postSQLCommands.clear();
1284:
1285: // parse dataBase
1286: Node node = root.find("convert/destination/database/url", new int[]
1287: { 1, 1, 1, 1 });
1288: Node node1 = root.find("convert/destination/database/user", new int[]
1289: { 1, 1, 1, 1, 1 });
1290: Node node2 = root.find("convert/destination/database/password", new int[]
1291: { 1, 1, 1, 1, 1 });
1292: Node commandNodes = root.find("convert/destination/database/sqlcommands", new int[]
1293: { 1, 1, 1, 1, 1 });
1294: if (commandNodes != null)
1295: {
1296: parseCommandNode(commandNodes, database, preSQLCommands, postSQLCommands);
1297: }
1298:
1299: String url = node.getCharacters();
1300: String user = node1.getCharacters();
1301: String password = node2.getCharacters();
1302: System.out.println(url);
1303: database.setURL(url.trim());
1304: database.setUserAndPasswd(user.trim(), password.trim());
1305: DataBase db = new DataBase(database, new Vector(), null, null, null, null, 0);
1306: databases.add(db);
1307: db.preCommands = new Vector(preSQLCommands);
1308: db.postCommands = new Vector(postSQLCommands);
1309:
1310: } catch (Exception e)
1311: {
1312: e.printStackTrace();
1313: }
1314: return databases;
1315: }
1316:
1317: /**
1318: *
1319: * @param commandNode
1320: * @param database
1321: * @param preSQLCommands
1322: * @param postSQLCommand
1323: */
1324: private static void parseCommandNode(Node commandNode, DBBean database,
1325: java.util.Vector preSQLCommands, java.util.Vector postSQLCommands)
1326: {
1327: // System.out.println(commandNode.name + " " + countNodes(commandNode));
1328: int numCommands = commandNode.contents.length();
1329: for (int j = 0; j < numCommands; ++j)
1330: {
1331: Node node = (Node) commandNode.contents.v.elementAt(j);
1332: if (node.type.equals("element"))
1333: {
1334: // System.out.println(node.name + " " + node.getCharacters() + database);
1335: String execute = node.attributes.get("execute").toString();
1336: if (execute.equals("before"))
1337: {
1338: preSQLCommands.add(new SQLCommand(database, node.getCharacters()));
1339: }
1340: if (execute.equals("after"))
1341: {
1342: postSQLCommands.add(new SQLCommand(database, node.getCharacters()));
1343: }
1344:
1345: }
1346:
1347: }
1348: }
1349:
1350: private static int countNodes(Node tempNode)
1351: {
1352: int length = 0;
1353: for (int i = 0; i < tempNode.contents.v.size(); ++i)
1354: {
1355: Node node = (Node) tempNode.contents.v.elementAt(i);
1356: if (node.type.equals("element"))
1357: {
1358: if (node.name.equals("database"))
1359: length++;
1360: if (node.name.equals("table"))
1361: length++;
1362: if (node.name.equals("sqlcommand"))
1363: length++;
1364:
1365: }
1366:
1367: // System.out.println(((Node)tempNode.contents.v.elementAt(i)).attributes+"
1368: // "+i);
1369: }
1370: return length;
1371: }
1372:
1373: private static void printContents(Node root)
1374: {
1375:
1376: Vector contents = (root.index == null) ? root.contents.v : root.index.v;
1377: for (int i = 0; i < contents.size(); ++i)
1378: {
1379: Node n = (Node) contents.elementAt(i);
1380: if (n.type.equals("element"))
1381: {
1382: System.out.println("tag " + n.name);
1383: System.out.println(n.getCharacters());
1384: // contents=n.contents.v i=0;
1385: }
1386: // System.out.println(n.type);
1387: }
1388: }
1389:
1390: /**
1391: * reads the specified xml file
1392: *
1393: * @param xmlFile
1394: */
1395: public static StringBuffer readXMLFile(String xmlFile)
1396: {
1397: InputStream stream = null;
1398: StringBuffer sb = new StringBuffer();
1399:
1400: try
1401: {
1402:
1403: if (xmlFile.indexOf("file://") >= 0 || xmlFile.indexOf("http://") >= 0)
1404: {
1405: URL url = new URL(xmlFile);
1406: stream = url.openStream();
1407: } else
1408: // read XML Metadata from a file
1409: stream = new FileInputStream(xmlFile);
1410: InputStreamReader isr = new InputStreamReader(stream, "UTF-8");
1411: BufferedReader buffr = new BufferedReader(isr);
1412: int c = 0;
1413: while ((c = buffr.read()) != -1)
1414: {
1415: char ch = (char) c;
1416: sb.append(ch);
1417: // System.out.print((char)c);
1418: }
1419: } catch (Exception e)
1420: {
1421: e.printStackTrace();
1422: } finally
1423: {
1424:
1425: try
1426: {
1427: stream.close();
1428: } catch (IOException e1)
1429: {
1430: // TODO Auto-generated catch block
1431: e1.printStackTrace();
1432: }
1433: }
1434: return sb;
1435: }
1436:
1437: /**
1438: * Helper class for Conversion etc. Contains data needed for the conversion
1439: *
1440: * @author rogo
1441: *
1442: */
1443:
1444: public static class ConversionProperties
1445: {
1446: String destTableName;
1447:
1448: String[] fieldNames;
1449:
1450: public ConversionProperties()
1451: {
1452: }
1453:
1454: public ConversionProperties(String destTableName, String[] fieldNames)
1455: {
1456: this.destTableName = destTableName;
1457: this.fieldNames = fieldNames;
1458:
1459: }
1460:
1461: }
1462:
1463: /**
1464: * Helper class for XML-File parsing Holds the parsed data
1465: *
1466: * @author rogo
1467: *
1468: */
1469: public static class DataBase
1470: {
1471: DBBean bean;
1472:
1473: Vector creates;
1474:
1475: Vector selects;
1476:
1477: Vector layouts;
1478:
1479: Vector tables = new Vector();
1480:
1481: Vector ids;
1482:
1483: String delimiter = "//";
1484:
1485: Vector preCommands;
1486: Vector postCommands;
1487:
1488: /**
1489: * maps table name to index fields
1490: */
1491: Hashtable htIndex = new Hashtable();
1492:
1493: boolean useNormanToUnicodeMapper = false;
1494:
1495: final static int CONVERT_MODE = 1;
1496:
1497: final static int APPEND_MODE = 2;
1498:
1499: final static int UPDATE_MODE = 3;
1500:
1501: final static int DELETE_MODE = 4;
1502:
1503: final static int SYNCHRONIZE_MODE = 5;
1504:
1505: final static int CONVERT_TEMP_MODE = 6;
1506:
1507: int mode = -1;
1508:
1509: public DataBase(DBBean bean, Vector tables, Vector layouts, Vector selects, Vector creates,
1510: Vector ids, int mode)
1511: {
1512: this.bean = bean;
1513: this.tables = tables;
1514: this.layouts = layouts;
1515: this.selects = selects;
1516: this.creates = creates;
1517: this.ids = ids;
1518: this.mode = mode;
1519: this.bean.setIDVector(ids);
1520: }
1521:
1522: /**
1523: * @param indexListVec
1524: */
1525: public void buildIndexTable(Vector indexListVec)
1526: {
1527: for (int i = 0; i < tables.size(); i++)
1528: {
1529: fillIndexList((String) tables.get(i), (String) indexListVec.get(i));
1530: }
1531: }
1532:
1533: /**
1534: * writes the data contained in this object to the buffered writer *
1535: *
1536: * @param buffr
1537: * @throws Exception
1538: */
1539: public void exportToXML(BufferedWriter buffr) throws Exception
1540: {
1541: // ids=bean.getIDVector();
1542: buffr.write(" <database>\n");
1543: buffr.write(" <url>" + bean.url + "</url>\n");
1544: buffr.write(" <user>" + bean.user + "</user>\n");
1545: buffr.write(" <password>" + bean.passwd + "</password>\n");
1546: buffr.write(" <delimiter>" + delimiter + "</delimiter>\n");
1547: String modeString = "";
1548: if (mode == CONVERT_MODE)
1549: modeString = "convert";
1550: else if (mode == APPEND_MODE)
1551: modeString = "append";
1552: else if (mode == UPDATE_MODE)
1553: modeString = "update";
1554: else if (mode == DELETE_MODE)
1555: modeString = "delete";
1556: else if (mode == SYNCHRONIZE_MODE)
1557: modeString = "synchronize";
1558:
1559: buffr.write(" <mode>" + modeString + "</mode>\n");
1560: buffr.write(" <usenormantounicodemapper>" + useNormanToUnicodeMapper
1561: + "</usenormantounicodemapper>\n");
1562: if (preCommands != null || postCommands != null)
1563: {
1564: int count = 0;
1565:
1566: buffr.write(" <sqlcommands> \n");
1567:
1568: if (preCommands != null)
1569: {
1570: while (count < preCommands.size())
1571: {
1572: SQLCommand sqlcommand = (SQLCommand) preCommands.get(count);
1573: buffr.write(" <sqlcommand execute=\"before\">" + sqlcommand.command
1574: + "</sqlcommand>\n");
1575: count++;
1576: }
1577: }
1578: if (postCommands != null)
1579: {
1580: count = 0;
1581: while (count < postCommands.size())
1582: {
1583: SQLCommand sqlcommand = (SQLCommand) postCommands.get(count);
1584:
1585: buffr.write(" <sqlcommand execute=\"after\">" + sqlcommand.command
1586: + "</sqlcommand>\n");
1587: count++;
1588: }
1589: }
1590: buffr.write(" </sqlcommands> \n");
1591:
1592: }
1593: int index = 0;
1594: while (index < tables.size())
1595: {
1596: String table = (String) tables.get(index);
1597: String layout = (String) layouts.get(index);
1598: String select = (String) selects.get(index);
1599: String create = (String) creates.get(index);
1600: String id = (String) ids.get(index);
1601: IndexList indexList = (IndexList) htIndex.get(table);
1602: if (indexList == null)
1603: indexList = new IndexList();
1604: buffr.write(" <table name = \"" + table + "\" layout = \"" + layout + "\" id = \""
1605: + id + "\" indexList =\"" + indexList + "\">\n");
1606: buffr.write(" <select>" + convertToEntities(select) + "</select>\n");
1607: if (!create.equals(""))
1608: buffr.write(" <create>" + create + " </create>\n");
1609: buffr.write(" </table>\n");
1610: index++;
1611: }
1612: buffr.write(" </database>\n");
1613: }
1614:
1615: public void fillIndexList(String table, String list)
1616: {
1617: IndexList indexList = new IndexList();
1618: StringTokenizer tokenizer = new StringTokenizer(list, ",");
1619: while (tokenizer.hasMoreTokens())
1620: {
1621: indexList.add(tokenizer.nextToken());
1622: }
1623: System.out.println(indexList);
1624:
1625: htIndex.put(table, indexList);
1626: }
1627:
1628: public String toString()
1629: {
1630: return bean.url + " " + tables;
1631: }
1632:
1633: }
1634:
1635: public static void writeConfig(String file, DataBase source, DataBase destination)
1636: throws Exception
1637: {
1638: if (!file.toLowerCase().endsWith(".xml"))
1639: file += ".xml";
1640: File f = new File(file);
1641:
1642: FileOutputStream fout = new FileOutputStream(f);
1643: OutputStreamWriter outsw = new OutputStreamWriter(fout, "UTF-8");
1644: BufferedWriter buffw = new BufferedWriter(outsw);
1645: buffw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
1646: buffw.newLine();
1647: buffw.write("<convert>\n");
1648: buffw.write(" <source>\n");
1649: source.exportToXML(buffw);
1650: buffw.write(" </source>\n");
1651: buffw.write("\n <destination>\n");
1652: destination.exportToXML(buffw);
1653: buffw.write(" </destination>\n");
1654: buffw.write("</convert>\n");
1655: buffw.close();
1656: }
1657:
1658: public static void delete(String source, String destination, Vector names, Vector layouts,
1659: Vector selects, Vector creates, Vector ids, int mode) throws Exception
1660: {
1661: FM2SQL.ProgressDialog dialog = null;
1662: if (isGUI)
1663: {
1664: dialog = initDialog();
1665: }
1666: // setting user and passwd
1667: bean.setUserAndPasswd(user, passwd);
1668: // setting user and passwd
1669: beanDest.setUserAndPasswd(userDest, passwdDest);
1670: StringBuffer command = null;
1671: String query = null;
1672: try
1673: {
1674: // bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
1675: // bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
1676: bean.setConnection(source);
1677: if (names == null)
1678: names = bean.getTableNames();
1679: // Collections.sort(names);
1680: int tbIndex = 1;
1681:
1682: // System.out.println("Start at
1683: // "+names.indexOf("archimedes_facsimiles"));
1684: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1685: {
1686: Vector[] result = null;
1687: java.util.TreeSet myIds = new TreeSet();
1688: java.util.TreeSet myIdsDest = new TreeSet();
1689: int deltaID = 1;
1690: String idField = "";
1691: String destTableName = "";
1692:
1693: try
1694: {
1695: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
1696: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
1697: query = (selects != null) ? selects.get(tbIndex).toString() : query;
1698: // if vectors[1].get(i) != null)
1699: if (layout != "")
1700: {
1701: layout = " layout " + bean.getQC() + layout + bean.getQC();
1702: String name = names.get(tbIndex).toString();
1703: StringBuffer queryLayout = new StringBuffer(query);
1704: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
1705: query = queryLayout.toString();
1706: System.out.println("added layout " + query);
1707:
1708: }
1709: dialog.title.setText("Getting table data ...");
1710: dialog.table.setText(names.get(tbIndex).toString());
1711: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
1712: dialog.setVisible(true);
1713: bean.getConnection();
1714: bean.makeQuery(query, 50);
1715: idField = ids.get(tbIndex).toString();
1716:
1717: } catch (Exception e)
1718: {
1719: continue;
1720: }
1721: // determine destTableName from createStatement or from source
1722: // table name
1723: if (!creates.get(tbIndex).equals(""))
1724: {
1725: String create = creates.get(tbIndex).toString().toLowerCase();
1726: int fromIndex = create.indexOf("table") + 5;
1727: int toIndex = create.indexOf("(");
1728: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "")
1729: .trim();
1730: System.out.println("destTable " + destTableName);
1731:
1732: } else
1733: destTableName = convertText(names.get(tbIndex).toString());
1734:
1735: // for id kram
1736: Vector vec = null;
1737: Vector vecDest = null;
1738: // tempo
1739: beanDest.setConnection(destination);
1740: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
1741: String tempID = bean.getQC() + idField + bean.getQC();
1742: String tempIDdest = beanDest.getQC() + convertText(idField) + beanDest.getQC();
1743:
1744: int endIndex = -1;
1745: String tempQuery = query;
1746: String destQuery = query.replaceAll(names.get(tbIndex).toString(), destTableName);
1747: String tempQueryDest = destQuery;
1748: // remove extra query parts
1749: // destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
1750: System.out.println("new Query " + tempQueryDest);
1751: if (!idField.equals(""))
1752: {
1753: long startTime = System.currentTimeMillis();
1754: int counter = -1;
1755: while (true)
1756: {
1757: ++counter;
1758: if (counter == 0 && dialog != null)
1759: dialog.title.setText("Check if data is available");
1760: else if (dialog != null)
1761: dialog.title.setText("Check if more data is available");
1762: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex),
1763: tempQuery, numHits);
1764: myIdsDest = beanDest.getIDVector(convertText(idField), destTableName, tempQueryDest,
1765: numHits);
1766: if (myIds.isEmpty())
1767: break;
1768: vec = new Vector(myIds);
1769: vecDest = new Vector(myIdsDest);
1770: rowCount = vec.size();
1771: // Deletion will work this way
1772: Vector deleted = new Vector(vec);
1773: Vector linesToDelete = new Vector(vecDest);
1774: // remove all lines that should not be deleted
1775: linesToDelete.removeAll(deleted);
1776: // System.out.println("ID LIST SIZE " +
1777: // Math.round((double) myIds.size() / (double)
1778: // numIntervalls) + " " + myIdsDest.size());
1779: // / @TODO complete delete task remove query show lines
1780: // to be deleted let user choose if he wants that
1781: System.out.println("number of lines to be deleted " + linesToDelete.size());
1782: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
1783: beanDest.setConnection(destination);
1784:
1785: Statement stm = beanDest.getConnection().createStatement();
1786:
1787: Vector tables = beanDest.getTableNames();
1788: // Collections.sort(tables);
1789: System.out.println(names.get(tbIndex) + " "
1790: + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
1791: tables = beanDest.getTableNames();
1792: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
1793: stm = beanDest.getConnection().createStatement();
1794:
1795: if (dialog != null)
1796: dialog.title.setText(" Deleting table data ...");
1797:
1798: int j = -1;
1799:
1800: Vector row = null;
1801: command = new StringBuffer();
1802:
1803: command.append("DELETE FROM");
1804: command.append(beanDest.getQC());
1805: command.append(destTableName);
1806: command.append(beanDest.getQC());
1807: int size = bean.getColumnNames().size();
1808: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
1809: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
1810: System.out.println(command + " " + tbIndex);
1811: while (true)
1812: {
1813:
1814: ++j;
1815: if (j == linesToDelete.size())
1816: break;
1817: // print rows
1818: pstm.setString(1, linesToDelete.get(j).toString());
1819: System.out.println(pstm.toString());
1820: pstm.execute();
1821: if (isGUI)
1822: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
1823: command = null;
1824: }
1825: // prepare new query for next chunk
1826: if (query.indexOf("where") > 0)
1827: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
1828: else
1829: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
1830:
1831: } // to outer while
1832: } // to idfield if
1833: } // table loop
1834:
1835: } catch (Exception e)
1836: {
1837: System.out.println("Error while connecting to database " + e);
1838: if (isGUI)
1839: {
1840: showExceptionDialog(dialog, command, e);
1841: resetGUI(dialog);
1842: } else
1843: {
1844: e.printStackTrace();
1845: }
1846: }
1847: } // to method
1848:
1849: /**
1850: * synchronize method based on delete method code
1851: *
1852: * @param source
1853: * @param destination
1854: * @param names
1855: * @param layouts
1856: * @param selects
1857: * @param creates
1858: * @param ids
1859: * @param mode
1860: * @throws Exception
1861: */
1862: // TODO implement append,update and delete in one method
1863: // TODO using id based algorithm
1864: public static void synchronize(String source, String destination, Vector names, Vector layouts,
1865: Vector selects, Vector creates, Vector ids, int mode, String delimiter, Vector indexList)
1866: throws Exception
1867: {
1868: System.out.println(" bin in synchronize!!!");
1869: FM2SQL.ProgressDialog dialog = null;
1870: if (isGUI)
1871: {
1872: dialog = initDialog();
1873: dialog.setTitle("Synchronize running ...");
1874:
1875: }
1876: // setting user and passwd
1877: bean.setUserAndPasswd(user, passwd);
1878: // setting user and passwd
1879: beanDest.setUserAndPasswd(userDest, passwdDest);
1880: StringBuffer command = null;
1881: String query = null;
1882: try
1883: {
1884: bean.setConnection(source);
1885: if (names == null)
1886: names = bean.getTableNames();
1887: int tbIndex = 1;
1888:
1889: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1890: {
1891: Vector[] result = null;
1892: java.util.TreeSet myIds = new TreeSet();
1893: java.util.TreeSet myIdsDest = new TreeSet();
1894: int deltaID = 1;
1895: String idField = "";
1896: String destTableName = "";
1897:
1898: try
1899: {
1900: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
1901: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
1902: query = (selects != null) ? selects.get(tbIndex).toString() : query;
1903: // if vectors[1].get(i) != null)
1904: if (!layout.equals(""))
1905: {
1906: query = addLayoutToQuery(names, query, tbIndex, layout);
1907:
1908: }
1909: if (dialog != null)
1910: {
1911: prepareDialogforUse(names, dialog, tbIndex);
1912: }
1913: bean.getConnection();
1914: bean.makeQuery(query, 50);
1915: idField = ids.get(tbIndex).toString();
1916:
1917: } catch (Exception e)
1918: {
1919: System.out.println("Warning exception occured \n " + e);
1920:
1921: continue;
1922: }
1923: // determine destTableName from createStatement or from source
1924: // table name
1925: if (!creates.get(tbIndex).equals(""))
1926: {
1927: String create = creates.get(tbIndex).toString().toLowerCase();
1928: int fromIndex = create.indexOf("table") + 5;
1929: int toIndex = create.indexOf("(");
1930: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "")
1931: .trim();
1932: System.out.println("destTable " + destTableName);
1933:
1934: } else
1935: destTableName = convertText(names.get(tbIndex).toString());
1936:
1937: // for id kram
1938: Vector vec = null;
1939: Vector vecDest = null;
1940: // tempo
1941: beanDest.setConnection(destination);
1942: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
1943: String tempID = bean.getQC() + idField + bean.getQC();
1944: String tempIDdest = beanDest.getQC() + convertText(idField) + beanDest.getQC();
1945:
1946: int endIndex = -1;
1947: String tempQuery = query;
1948: String destQuery = query.replaceAll(names.get(tbIndex).toString(), destTableName);
1949: destQuery = destQuery.replaceAll(bean.getQC(), beanDest.getQC());
1950: destQuery = removeLayoutPartFromQuery(destQuery, layouts.get(tbIndex).toString());
1951: // TODO remove layout part for destQuery
1952: String tempQueryDest = destQuery;
1953: // remove extra query parts
1954: // destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
1955: System.out.println("new Query " + tempQueryDest);
1956: System.out.println("idfield " + idField + " " + ids.get(tbIndex).toString());
1957: if (!idField.equals(""))
1958: {
1959: long startTime = System.currentTimeMillis();
1960: int counter = -1;
1961: TreeSet linesToDelete = null;
1962: PreparedStatement delPSt = null;
1963: while (true)
1964: {
1965: ++counter;
1966: if (counter == 0 && dialog != null)
1967: dialog.title.setText("Check if data is available");
1968: else if (dialog != null)
1969: dialog.title.setText("Check if more data is available");
1970:
1971: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex),
1972: tempQuery, 0);
1973: myIdsDest = beanDest.getIDVector(convertText(idField), destTableName, tempQueryDest, 0);
1974: // System.out.println("status of remove
1975: // "+myIds.remove("b015892"));
1976: System.out.println("ids found for " + idField + " " + !myIds.isEmpty());
1977: if (myIds.isEmpty())
1978: break;
1979: vec = new Vector(myIds);
1980: vecDest = new Vector(myIdsDest);
1981: rowCount = vec.size();
1982: // Deletion will work this way
1983: Vector deleted = new Vector(vec);
1984:
1985: TreeSet linesToAppend = new TreeSet(vec);
1986: linesToAppend.addAll(vec);
1987: linesToDelete = new TreeSet(vecDest);
1988: // remove all lines that are already in dest database
1989: linesToAppend.removeAll(vecDest);
1990: // remove all lines that should not be deleted
1991: linesToDelete.removeAll(deleted);
1992: System.out.println("linesToAppend " + linesToAppend.size() + " " + destTableName);
1993: System.out.println("linesToDelete " + linesToDelete.size() + " " + destTableName);
1994: System.out.println("ID LIST SIZE "
1995: + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIds.size());
1996: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
1997: ConversionProperties prop = getFieldNamesAndDestTableName(creates.get(tbIndex)
1998: .toString(), query, names.get(tbIndex).toString());
1999: StringBuffer insCommand = createInsertCommand(prop.destTableName, prop.fieldNames);
2000: StringBuffer updCommand = createUpdateCommand(prop.destTableName, prop.fieldNames,
2001: tempIDdest);
2002: StringBuffer delCommand = createDeleteCommand(destTableName, tempIDdest);
2003: PreparedStatement insPst = beanDest.getConnection().prepareStatement(
2004: insCommand.toString());
2005: PreparedStatement updPst = beanDest.getConnection().prepareStatement(
2006: updCommand.toString());
2007: delPSt = beanDest.getConnection().prepareStatement(delCommand.toString());
2008: // delPSt.setString(1,"b015892");
2009: // delPSt.execute();
2010: // if (true)
2011: // return;
2012: if (vec.size() <= numIntervalls)
2013: {
2014: endIndex = 0;
2015: deltaID = vec.size();
2016: }
2017: for (int k = 0; k < vec.size() - deltaID; k = k + deltaID)
2018: {
2019: System.out.println(vec.get(k) + " " + vec.get(k + deltaID) + " " + vec.lastElement());
2020: if (query.indexOf("where") > 0)
2021: tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID
2022: + "<='" + vec.get(k + deltaID) + "'";
2023: else
2024: tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID
2025: + "<='" + vec.get(k + deltaID) + "'";
2026: System.out.println(tempQuery);
2027: if (dialog != null)
2028: dialog.title.setText("Reading table data ...");
2029:
2030: // bean.makeQuery(tempQuery, deltaID);
2031: if (dialog != null)
2032: dialog.title.setText("Writing table data ...");
2033:
2034: performSynchronize(idField, vec, tempQuery, linesToDelete, linesToAppend, insPst,
2035: updPst, delPSt, deltaID, delimiter, dialog);
2036: // System.out.println("ID LIST SIZE " +
2037: // Math.round((double) myIds.size() / (double)
2038: // numIntervalls) + " " + myIdsDest.size());
2039: endIndex = k + deltaID;
2040: }
2041: System.out.println(endIndex);
2042: // all data written ? if not write last chunk of data
2043: if (endIndex == vec.size() - 1)
2044: System.out.println("fits");
2045: else
2046: {
2047: System.out.println(" last intervall from " + vec.get(endIndex) + " "
2048: + vec.lastElement());
2049:
2050: if (query.indexOf("where") > 0)
2051: tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and "
2052: + tempID + "<='" + vec.lastElement() + "'";
2053: else
2054: tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and "
2055: + tempID + "<='" + vec.lastElement() + "'";
2056: System.out.println(tempQuery);
2057: if (dialog != null)
2058: dialog.title.setText("Reading table data ...");
2059: // bean.makeQuery(tempQuery, 0);
2060: if (dialog != null)
2061: dialog.title.setText("Writing table data ...");
2062: performSynchronize(idField, vec, tempQuery, linesToDelete, linesToAppend, insPst,
2063: updPst, delPSt, deltaID, delimiter, dialog);
2064: // System.out.println("ID LIST SIZE " +
2065: // Math.round((double) myIds.size() / (double)
2066: // numIntervalls) + " " + myIdsDest.size());
2067: }
2068: // prepare new query for next chunk
2069: if (query.indexOf("where") > 0)
2070: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
2071: else
2072: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
2073:
2074: }
2075: String tableName = names.get(tbIndex).toString();
2076: if (!indexList.isEmpty())
2077: {
2078: IndexList idList = (IndexList) indexList.get(0);
2079: System.out.println("found list " + idList);
2080: Statement stm = beanDest.getConnection().createStatement();
2081: Vector destTables = beanDest.getTableNames();
2082: System.out.println("tempQueryDest" + tempQueryDest);
2083: beanDest.makeQuery(tempQueryDest, 0);
2084: for (Iterator iter = idList.iterator(); iter.hasNext();)
2085: {
2086: String indexField = (String) iter.next();
2087: indexField = convertText(indexField);
2088: String indexName = destTableName + "_" + indexField;
2089: if (destTables.contains(indexName))
2090: {
2091: stm.execute("DROP INDEX " + destTableName + "_" + indexField);
2092: // continue;
2093: }
2094: // stm.execute("DROP INDEX
2095: // "+destTableName+"_"+indexField);
2096:
2097: String type = beanDest.getColumnType(indexField).toLowerCase();
2098: // System.out.println(indexField+" "+type+"
2099: // "+(type.indexOf("text") >= 0 ||
2100: // type.indexOf("varchar") >= 0 || type.indexOf("char")
2101: // >= 0));
2102: if (type.indexOf("text") >= 0 || type.indexOf("varchar") >= 0
2103: || type.indexOf("char") >= 0)
2104: {
2105: if (beanDest.url.indexOf("mysql") >= 0)
2106: {
2107: // System.out.println("CREATE INDEX " +
2108: // indexName + " ON " + destTableName + " (" +
2109: // indexField + "(10))");
2110: // TODO problem if index exist !!!
2111: stm.execute("CREATE INDEX " + indexName + " ON " + destTableName + " ("
2112: + indexField + "(10))");
2113: } else
2114: {
2115: stm.execute("CREATE INDEX " + indexName + " ON " + destTableName + " (lower( "
2116: + indexField + "))");
2117:
2118: }
2119:
2120: } else
2121: {
2122: stm.execute("CREATE INDEX " + destTableName + "_" + indexField + " ON "
2123: + destTableName + "(" + indexField + ")");
2124:
2125: }
2126:
2127: // stm.execute("DROP INDEX
2128: // "+destTableName+"_"+indexField);
2129:
2130: }
2131: }
2132: // CREATE UNIQUE INDEX title_idx ON films (title);
2133: for (Iterator iter = linesToDelete.iterator(); iter.hasNext();)
2134: {
2135: String id = (String) iter.next();
2136: delPSt.setString(1, id);
2137: delPSt.execute();
2138:
2139: }
2140:
2141: long endTime = System.currentTimeMillis();
2142: System.out.println("Time for incremental synchronize elapsed " + (endTime - startTime));
2143: } // to idfield if
2144: } // table loop
2145:
2146: } catch (Exception e)
2147: {
2148: System.out.println("Error while connecting to database " + e);
2149: e.printStackTrace();
2150: if (isGUI)
2151: showExceptionDialog(dialog, command, e);
2152: }
2153: if (isGUI)
2154: {
2155: resetGUI(dialog);
2156: }
2157: }
2158:
2159: /**
2160: * @param destQuery
2161: * @param string
2162: * @return
2163: */
2164: private static String removeLayoutPartFromQuery(String destQuery, String layoutName)
2165: {
2166: String removeString = "layout " + beanDest.getQC() + layoutName + beanDest.getQC();
2167: destQuery = destQuery.replaceFirst(removeString, "");
2168: System.out.println("destQuery change to " + destQuery);
2169: return destQuery;
2170: }
2171:
2172: private static void performSynchronize(String idField, Vector vec, String tempQuery,
2173: TreeSet linesToDelete, TreeSet linesToAppend, PreparedStatement insPst,
2174: PreparedStatement updPst, PreparedStatement delPSt, int deltaID, String delimiter,
2175: FM2SQL.ProgressDialog dialog) throws SQLException, ParseException, Exception
2176: {
2177: if (dialog != null)
2178: {
2179: dialog.progress.setValue(0);
2180: dialog.title.setText("Retrieving new data");
2181: }
2182:
2183: Vector[] vectors = bean.getQueryData(tempQuery, deltaID);
2184: int count = 0, size = vectors[0].size();
2185: int idIndex = vectors[1].indexOf(idField);
2186: // System.out.println(idIndex + " " + vectors[1] + " " + idField);
2187: // todo arraylist code has to be added
2188: if (dialog != null)
2189: dialog.title.setText("Synchronize with new data");
2190:
2191: for (Iterator iter = vectors[0].iterator(); iter.hasNext();)
2192: {
2193: Vector line = (Vector) iter.next();
2194: Object lineIDIndex = line.get(idIndex);
2195: if (linesToAppend.contains(lineIDIndex))
2196: System.out.println("line " + linesToAppend.contains(line.get(idIndex)) + " " + lineIDIndex);
2197: if (linesToAppend.contains(lineIDIndex))
2198: {
2199: for (int l = 0; l < line.size(); ++l)
2200: {
2201: Object obj = line.get(l);
2202: if (obj instanceof ArrayList)
2203: obj = formatFileMakerArray((List) obj, delimiter);
2204: if (obj != null)
2205: insPst.setString(l + 1, obj.toString());
2206: else
2207: insPst.setNull(l + 1, Types.NULL);
2208: }
2209: insPst.execute();
2210:
2211: } else
2212: // update
2213: {
2214: for (int l = 0; l < line.size(); ++l)
2215: {
2216: Object obj = line.get(l);
2217: if (obj instanceof ArrayList)
2218: obj = formatFileMakerArray((List) obj, delimiter);
2219: if (obj != null)
2220: updPst.setString(l + 1, obj.toString());
2221: else
2222: updPst.setNull(l + 1, Types.NULL);
2223: }
2224: updPst.setString(line.size() + 1, line.get(idIndex).toString());
2225: // updPst.addBatch();
2226: // updPst.execute();
2227: }
2228: if (dialog != null)
2229: {
2230: int value = (int) Math.round(((double) count / (double) size) * 100.0);
2231: dialog.progress.setValue(value);
2232: count++;
2233: }
2234: }
2235: // updPst.executeBatch();
2236: } // to method
2237:
2238: /**
2239: * Converts input String in norman encoding to unicode
2240: *
2241: * @param inp
2242: * @return converted String
2243: */
2244: static public String normanToUnicode(String inp)
2245: {
2246: StringBuffer buf = new StringBuffer();
2247: for (int i = 0; i < inp.length(); i++)
2248: {
2249: char c = inp.charAt(i);
2250: // System.out.println("char "+c+" "+(int)c);
2251: switch (c)
2252: {
2253: case 1:
2254: buf.append("\u00d0");
2255: break; // Eth
2256: case 2:
2257: buf.append("\u00f0");
2258: break; // eth
2259: case 3:
2260: buf.append("\u0141");
2261: break; // Lslash
2262: case 4:
2263: buf.append("\u0142");
2264: break; // lslash
2265: case 5:
2266: buf.append("\u0160");
2267: break; // S caron
2268: case 6:
2269: buf.append("\u0161");
2270: break; // s caron
2271: case 7:
2272: buf.append("\u00dd");
2273: break; // Y acute
2274: case 8:
2275: buf.append("\u00fd");
2276: break; // y acute
2277: case 11:
2278: buf.append("\u00de");
2279: break; // Thorn
2280: case 12:
2281: buf.append("\u00fe");
2282: break; // thorn
2283: case 14:
2284: buf.append("\u017d");
2285: break; // Z caron
2286: case 15:
2287: buf.append("\u017e");
2288: break; // z caron
2289: case 17:
2290: buf.append("\u0073");
2291: break; // asciitilde
2292: case 18:
2293: buf.append("j\u0305");
2294: break; // j macron [does a single char exist?]
2295: case 19:
2296: buf.append("^");
2297: break; // circumflex
2298: case 20:
2299: buf.append("\u0303");
2300: break; // tilde
2301: case 21:
2302: buf.append("\u00bd");
2303: break; // onehalf
2304: case 22:
2305: buf.append("\u00bc");
2306: break; // onequarter
2307: case 23:
2308: buf.append("\u00b9");
2309: break; // onesuperior
2310: case 24:
2311: buf.append("\u00be");
2312: break; // threequarters
2313: case 25:
2314: buf.append("\u00b3");
2315: break; // threesuperior
2316: case 26:
2317: buf.append("\u00b2");
2318: break; // twosuperior
2319: case 27:
2320: buf.append("\u00a6");
2321: break; // brokenbar
2322: case 28:
2323: buf.append("-");
2324: break; // minus
2325: case 29:
2326: buf.append("\u00d7");
2327: break; // multiply
2328: case 39:
2329: buf.append("'");
2330: break; // quotesingle
2331: case 94:
2332: buf.append("\u0302");
2333: break; // circumflex
2334: case 96:
2335: buf.append("\u0300");
2336: break; // grave
2337: case 196:
2338: buf.append("\u00c4");
2339: break; // A dieresis
2340: case 197:
2341: buf.append("\u00c5");
2342: break; // A ring
2343: case 201:
2344: buf.append("\u00c9");
2345: break; // E acute
2346: case 209:
2347: buf.append("\u00d1");
2348: break; // N tilde
2349: case 214:
2350: buf.append("\u00d6");
2351: break; // O dieresis
2352: case 220:
2353: buf.append("\u00dc");
2354: break; // U dieresis
2355: case 225:
2356: buf.append("\u00e1");
2357: break; // a acute
2358: case 224:
2359: buf.append("\u00e0");
2360: break; // a grave
2361: case 226:
2362: buf.append("\u00e2");
2363: break; // a circumflex
2364: case 228:
2365: buf.append("\u00e4");
2366: break; // a dieresis
2367: case 227:
2368: buf.append("\u00e3");
2369: break; // a tilde
2370: case 229:
2371: buf.append("\u0101");
2372: break; // a macron
2373: case 231:
2374: buf.append("\u00e7");
2375: break; // c cedilla
2376: case 233:
2377: buf.append("\u00e9");
2378: break; // e acute
2379: case 232:
2380: buf.append("\u00e8");
2381: break; // e grave
2382: case 234:
2383: buf.append("\u00ea");
2384: break; // e circumflex
2385: case 235:
2386: buf.append("\u00eb");
2387: break; // e dieresis
2388: case 237:
2389: buf.append("\u00ed");
2390: break; // i acute
2391: case 236:
2392: buf.append("\u00ec");
2393: break; // i grave
2394: case 238:
2395: buf.append("\u00ee");
2396: break; // i circumflex
2397: case 239:
2398: buf.append("\u00ef");
2399: break; // i dieresis
2400: case 241:
2401: buf.append("\u00f1");
2402: break; // n tilde
2403: case 243:
2404: buf.append("\u00f3");
2405: break; // o acute
2406: case 242:
2407: buf.append("\u00f2");
2408: break; // o grave
2409: case 244:
2410: buf.append("\u00f4");
2411: break; // o circumflex
2412: case 246:
2413: buf.append("\u00f6");
2414: break; // o dieresis
2415: case 245:
2416: buf.append("\u00f5");
2417: break; // o tilde
2418: case 250:
2419: buf.append("\u00fa");
2420: break; // u acute
2421: case 249:
2422: buf.append("\u00f9");
2423: break; // u grave
2424: case 251:
2425: buf.append("\u00fb");
2426: break; // u circumflex
2427: case 252:
2428: buf.append("\u00fc");
2429: break; // u dieresis
2430: case 8224:
2431: buf.append("\u1e6d");
2432: break; // t underdot
2433: case 176:
2434: buf.append("\u00b0");
2435: break; // degree
2436: case 162:
2437: buf.append("\u1ebd");
2438: break; // e tilde
2439: case 163:
2440: buf.append("\u00a3");
2441: break; // sterling
2442: case 167:
2443: buf.append("\u00a7");
2444: break; // section
2445: case 182:
2446: buf.append("\u00b6");
2447: break; // paragraph
2448: case 223:
2449: buf.append("\u015b");
2450: break; // s acute
2451: case 174:
2452: buf.append("\u1e5b");
2453: break; // r underdot
2454: case 169:
2455: buf.append("\u1e45");
2456: break; // n overdot
2457: case 353:
2458: buf.append("\u1e45");
2459: break; // n overdot
2460: case 180:
2461: buf.append("\u0301");
2462: break; // acute
2463: case 168:
2464: buf.append("\u0308");
2465: break; // dieresis
2466: case 8800:
2467: buf.append("\u1e6d");
2468: break; // t underdot
2469: case 198:
2470: buf.append("\u00c6");
2471: break; // AE
2472: case 216:
2473: buf.append("\u014d");
2474: break; // o macron
2475: case 8734:
2476: buf.append("\u0129");
2477: break; // i tilde
2478: case 177:
2479: buf.append("\u00b1");
2480: break; // plusminus
2481: case 165:
2482: buf.append("\u012b");
2483: break; // i macron
2484: case 181:
2485: buf.append("\u1e43");
2486: break; // m underdot
2487: case 8706:
2488: buf.append("\u1e0d");
2489: break; // d underdot
2490: case 240:
2491: buf.append("\u1e0d");
2492: break; // d underdot
2493:
2494: case 8721:
2495: buf.append("\u1e63");
2496: break; // s underdot
2497: case 960:
2498: buf.append("\u017a");
2499: break; // z acute
2500: case 8747:
2501: buf.append("\u1e45");
2502: break; // n overdot
2503: case 937:
2504: buf.append("\u0169");
2505: break; // u tilde
2506: case 230:
2507: buf.append("\u00e6");
2508: break; // ae
2509: case 248:
2510: buf.append("\u00f8");
2511: break; // oslash
2512: case 191:
2513: buf.append("\u0304\u0306");
2514: break; // macron breve
2515: case 172:
2516: buf.append("\u1e37");
2517: break; //
2518: case 8730:
2519: buf.append("j\u0305");
2520: break; // j macron [does a single char exist?]
2521: case 402:
2522: buf.append("\u0103");
2523: break; // a breve
2524: case 8776:
2525: buf.append("\u016d");
2526: break; // u breve
2527: case 187:
2528: buf.append("\u1e42");
2529: break; // M underdot
2530: case 8230:
2531: buf.append("\u2026");
2532: break; // ellipsis
2533: case 192:
2534: buf.append("\u00c0");
2535: break; // A grave
2536: case 195:
2537: buf.append("\u00c3");
2538: break; // A tilde
2539: case 213:
2540: buf.append("\u00d5");
2541: break; // O tilde
2542: case 338:
2543: buf.append("m\u0306");
2544: break; // m breve
2545: case 339:
2546: buf.append("\u0153");
2547: break; // oe
2548: case 8211:
2549: buf.append("\u2013");
2550: break; // endash
2551: case 8212:
2552: buf.append("\u2014");
2553: break; // emdash
2554: case 8220:
2555: buf.append("\u201c");
2556: break; // quotedblleft
2557: case 8221:
2558: buf.append("\u201d");
2559: break; // quotedblright
2560: case 8216:
2561: buf.append("\u2018");
2562: break; // quoteleft
2563: case 8217:
2564: buf.append("\u2019");
2565: break; // quoteright
2566: case 247:
2567: buf.append("\u1e37");
2568: break; // l underring [actually underdot]
2569: case 9674:
2570: buf.append("\u1e41");
2571: break; // m overdot
2572: case 255:
2573: buf.append("n\u0306");
2574: break; // n breve
2575: case 376:
2576: buf.append("\u00d7");
2577: break; // multiply
2578: case 8364:
2579: buf.append("\u1e5b");
2580: break; // r underring [actually underdot]
2581: case 8249:
2582: buf.append("\u1e44");
2583: break; // N overdot
2584: case 8250:
2585: buf.append("\u1e62");
2586: break; // S underdot
2587: case 64257:
2588: buf.append("\u1e24");
2589: break; // H underdot
2590: case 64258:
2591: buf.append("\u1e0c");
2592: break; // D underdot
2593: case 8225:
2594: buf.append("\u2021");
2595: break; // daggerdbl
2596: case 8218:
2597: buf.append("\u1e36");
2598: break; // L underdot
2599: case 8222:
2600: buf.append("\u0113");
2601: break; // e macron
2602: case 194:
2603: buf.append("\u1e5f");
2604: break; // r underbar
2605: case 202:
2606: buf.append("r\u0324");
2607: break; // r underdieresis
2608: case 193:
2609: buf.append("\u012a");
2610: break; // I macron
2611: case 8486:
2612: case 203:
2613: buf.append("\u016b");
2614: break; // u macron
2615: case 200:
2616: buf.append("\u1e6c");
2617: break; // T underdot
2618: case 205:
2619: buf.append("\u1e64");
2620: break; // S acute
2621: case 206:
2622: buf.append("\u2020");
2623: break; // dagger
2624: case 207:
2625: buf.append("\u0115");
2626: break; // e breve
2627: case 204:
2628: buf.append("\u014f");
2629: break; // o breve
2630: case 211:
2631: buf.append("\u0100");
2632: break; // A macron
2633: case 212:
2634: buf.append("\u1e46");
2635: break; // N underdot
2636: case 210:
2637: buf.append("\u1e3b");
2638: break; // l underbar
2639: case 218:
2640: buf.append("\u016a");
2641: break; // U macron
2642: case 219:
2643: buf.append("\u0179");
2644: break; // Z acute
2645: case 217:
2646: buf.append("\u1e5a");
2647: break; // R underdot
2648: case 305:
2649: buf.append("\u0131");
2650: break; // dotlessi
2651: case 710:
2652: buf.append("\u1e47");
2653: break; // n underdot
2654: case 732:
2655: buf.append("\u1e49");
2656: break; // n underbar
2657: case 175:
2658: buf.append("\u0304");
2659: break; // macron
2660: case 728:
2661: buf.append("\u0306");
2662: break; // breve
2663: case 729:
2664: case 215:
2665: buf.append("\u1e25");
2666: break; // h underdot
2667: case 730:
2668: buf.append("\u012d");
2669: break; // i breve
2670: case 184:
2671: buf.append("\u0327");
2672: break; // cedilla
2673: case 733:
2674: buf.append("\u030b");
2675: break; // hungarumlaut
2676: case 731:
2677: buf.append("\u0328");
2678: break; // ogonek
2679: case 711:
2680: buf.append("\u030c");
2681: break; // caron
2682: case 199:
2683: buf.append("\u012b\u0303");
2684: break; // imacron tilde
2685: case 8226:
2686: buf.append("\u1e5d");
2687: break; // runderdot macron
2688: case 8482:
2689: buf.append("\u016b\0306");
2690: break; // umacron breve
2691: case 8804:
2692: buf.append("\u0101\u0301");
2693: break; // amacron acute
2694: case 8805:
2695: buf.append("\u016b\u0301");
2696: break; // umacron acute
2697: case 8719:
2698: buf.append("\u0113\u0301");
2699: break; // emacron acute
2700: case 170:
2701: buf.append("\u0113\u0300");
2702: break; // emacron breve
2703: case 186:
2704: buf.append("\u014d\u0300");
2705: break; // omacron breve
2706: case 161:
2707: buf.append("\u0101\u0306");
2708: break; // amacron breve
2709: case 8710:
2710: buf.append("\u0101\u0303");
2711: break; // amacron tilde
2712: case 171:
2713: buf.append("\u012b\u0301");
2714: break; // imacron acute
2715: case 8260:
2716: buf.append("\u1e00");
2717: break; // runderdotmacron acute
2718: case 183:
2719: buf.append("\u1e5b\u0301");
2720: break; // runderdot acute
2721: case 8240:
2722: buf.append("\u012b\u0306");
2723: break; // imacron breve
2724: case 63743:
2725: buf.append("\u016b\u0303");
2726: break; // umacron tilde
2727: default:
2728: buf.append(c);
2729: if ((int) c > 127)
2730: System.out.println("char " + c + " " + (int) c);
2731: break;
2732: }
2733: }
2734: return buf.toString();
2735: }
2736:
2737: static public String normanToUnicodeOld(String inp)
2738: {
2739: StringBuffer buf = new StringBuffer();
2740: for (int i = 0; i < inp.length(); i++)
2741: {
2742: char c = inp.charAt(i);
2743: switch (c)
2744: {
2745: case 1:
2746: buf.append("\u00d0");
2747: break; // Eth
2748: case 2:
2749: buf.append("\u00f0");
2750: break; // eth
2751: case 3:
2752: buf.append("\u0141");
2753: break; // Lslash
2754: case 4:
2755: buf.append("\u0142");
2756: break; // lslash
2757: case 5:
2758: buf.append("\u0160");
2759: break; // S caron
2760: case 6:
2761: buf.append("\u0161");
2762: break; // s caron
2763: case 7:
2764: buf.append("\u00dd");
2765: break; // Y acute
2766: case 8:
2767: buf.append("\u00fd");
2768: break; // y acute
2769: case 11:
2770: buf.append("\u00de");
2771: break; // Thorn
2772: case 12:
2773: buf.append("\u00fe");
2774: break; // thorn
2775: case 14:
2776: buf.append("\u017d");
2777: break; // Z caron
2778: case 15:
2779: buf.append("\u017e");
2780: break; // z caron
2781: case 17:
2782: buf.append("\u0073");
2783: break; // asciitilde
2784: case 18:
2785: buf.append("j\u0305");
2786: break; // j macron [does a single char exist?]
2787: case 19:
2788: buf.append("^");
2789: break; // circumflex
2790: case 20:
2791: buf.append("\u0303");
2792: break; // tilde
2793: case 21:
2794: buf.append("\u00bd");
2795: break; // onehalf
2796: case 22:
2797: buf.append("\u00bc");
2798: break; // onequarter
2799: case 23:
2800: buf.append("\u00b9");
2801: break; // onesuperior
2802: case 24:
2803: buf.append("\u00be");
2804: break; // threequarters
2805: case 25:
2806: buf.append("\u00b3");
2807: break; // threesuperior
2808: case 26:
2809: buf.append("\u00b2");
2810: break; // twosuperior
2811: case 27:
2812: buf.append("\u00a6");
2813: break; // brokenbar
2814: case 28:
2815: buf.append("-");
2816: break; // minus
2817: case 29:
2818: buf.append("\u00d7");
2819: break; // multiply
2820: case 39:
2821: buf.append("'");
2822: break; // quotesingle
2823: case 94:
2824: buf.append("\u0302");
2825: break; // circumflex
2826: case 96:
2827: buf.append("\u0300");
2828: break; // grave
2829: case 128:
2830: buf.append("\u00c4");
2831: break; // A dieresis
2832: case 129:
2833: buf.append("\u00c5");
2834: break; // A ring
2835: case 131:
2836: buf.append("\u00c9");
2837: break; // E acute
2838: case 132:
2839: buf.append("\u00d1");
2840: break; // N tilde
2841: case 133:
2842: buf.append("\u00d6");
2843: break; // O dieresis
2844: case 134:
2845: buf.append("\u00dc");
2846: break; // U dieresis
2847: case 135:
2848: buf.append("\u00e1");
2849: break; // a acute
2850: case 136:
2851: buf.append("\u00e0");
2852: break; // a grave
2853: case 137:
2854: buf.append("\u00e2");
2855: break; // a circumflex
2856: case 138:
2857: buf.append("\u00e4");
2858: break; // a dieresis
2859: case 139:
2860: buf.append("\u00e3");
2861: break; // a tilde
2862: case 140:
2863: buf.append("\u0101");
2864: break; // a macron
2865: case 141:
2866: buf.append("\u00e7");
2867: break; // c cedilla
2868: case 142:
2869: buf.append("\u00e9");
2870: break; // e acute
2871: case 143:
2872: buf.append("\u00e8");
2873: break; // e grave
2874: case 144:
2875: buf.append("\u00ea");
2876: break; // e circumflex
2877: case 145:
2878: buf.append("\u00eb");
2879: break; // e dieresis
2880: case 146:
2881: buf.append("\u00ed");
2882: break; // i acute
2883: case 147:
2884: buf.append("\u00ec");
2885: break; // i grave
2886: case 148:
2887: buf.append("\u00ee");
2888: break; // i circumflex
2889: case 149:
2890: buf.append("\u00ef");
2891: break; // i dieresis
2892: case 150:
2893: buf.append("\u00f1");
2894: break; // n tilde
2895: case 151:
2896: buf.append("\u00f3");
2897: break; // o acute
2898: case 152:
2899: buf.append("\u00f2");
2900: break; // o grave
2901: case 153:
2902: buf.append("\u00f4");
2903: break; // o circumflex
2904: case 154:
2905: buf.append("\u00f6");
2906: break; // o dieresis
2907: case 155:
2908: buf.append("\u00f5");
2909: break; // o tilde
2910: case 156:
2911: buf.append("\u00fa");
2912: break; // u acute
2913: case 157:
2914: buf.append("\u00f9");
2915: break; // u grave
2916: case 158:
2917: buf.append("\u00fb");
2918: break; // u circumflex
2919: case 159:
2920: buf.append("\u00fc");
2921: break; // u dieresis
2922: case 160:
2923: buf.append("\u1e6d");
2924: break; // t underdot
2925: case 161:
2926: buf.append("\u00b0");
2927: break; // degree
2928: case 162:
2929: buf.append("\u1ebd");
2930: break; // e tilde
2931: case 163:
2932: buf.append("\u00a3");
2933: break; // sterling
2934: case 164:
2935: buf.append("\u00a7");
2936: break; // section
2937: case 166:
2938: buf.append("\u00b6");
2939: break; // paragraph
2940: case 167:
2941: buf.append("\u015b");
2942: break; // s acute
2943: case 168:
2944: buf.append("\u1e5b");
2945: break; // r underdot
2946: case 169:
2947: buf.append("\u1e67");
2948: break; // s caron
2949: case 171:
2950: buf.append("\u0301");
2951: break; // acute
2952: case 172:
2953: buf.append("\u0308");
2954: break; // dieresis
2955: case 173:
2956: buf.append("\u1e6d");
2957: break; // t underdot
2958: case 174:
2959: buf.append("\u00c6");
2960: break; // AE
2961: case 175:
2962: buf.append("\u014d");
2963: break; // o macron
2964: case 176:
2965: buf.append("\u0129");
2966: break; // i tilde
2967: case 177:
2968: buf.append("\u00b1");
2969: break; // plusminus
2970: case 180:
2971: buf.append("\u012b");
2972: break; // i macron
2973: case 181:
2974: buf.append("\u1e43");
2975: break; // m underdot
2976: case 182:
2977: buf.append("\u1e0d");
2978: break; // d underdot
2979: case 183:
2980: buf.append("\u1e63");
2981: break; // s underdot
2982: case 185:
2983: buf.append("\u017a");
2984: break; // z acute
2985: case 186:
2986: buf.append("\u1e45");
2987: break; // n overdot
2988: case 189:
2989: buf.append("\u0169");
2990: break; // u tilde
2991: case 190:
2992: buf.append("\u00e6");
2993: break; // ae
2994: case 191:
2995: buf.append("\u00f8");
2996: break; // oslash
2997: case 192:
2998: buf.append("\u0304\u0306");
2999: break; // macron breve
3000: case 194:
3001: buf.append("\u1e37");
3002: break; //
3003: case 195:
3004: buf.append("j\u0305");
3005: break; // j macron [does a single char exist?]
3006: case 196:
3007: buf.append("\u0103");
3008: break; // a breve
3009: case 197:
3010: buf.append("\u016d");
3011: break; // u breve
3012: case 200:
3013: buf.append("\u1e42");
3014: break; // M underdot
3015: case 201:
3016: buf.append("\u2026");
3017: break; // ellipsis
3018: case 203:
3019: buf.append("\u00c0");
3020: break; // A grave
3021: case 204:
3022: buf.append("\u00c3");
3023: break; // A tilde
3024: case 205:
3025: buf.append("\u00d5");
3026: break; // O tilde
3027: case 206:
3028: buf.append("m\u0306");
3029: break; // m breve
3030: case 207:
3031: buf.append("\u0153");
3032: break; // oe
3033: case 208:
3034: buf.append("\u2013");
3035: break; // endash
3036: case 209:
3037: buf.append("\u2014");
3038: break; // emdash
3039: case 210:
3040: buf.append("\u201c");
3041: break; // quotedblleft
3042: case 211:
3043: buf.append("\u201d");
3044: break; // quotedblright
3045: case 212:
3046: buf.append("\u2018");
3047: break; // quoteleft
3048: case 213:
3049: buf.append("\u2019");
3050: break; // quoteright
3051: case 214:
3052: buf.append("\u1e37");
3053: break; // l underring [actually underdot]
3054: case 215:
3055: buf.append("\u1e41");
3056: break; // m overdot
3057: case 216:
3058: buf.append("n\u0306");
3059: break; // n breve
3060: case 217:
3061: buf.append("\u00d7");
3062: break; // multiply
3063: case 219:
3064: buf.append("\u1e5b");
3065: break; // r underring [actually underdot]
3066: case 220:
3067: buf.append("\u1e44");
3068: break; // N overdot
3069: case 221:
3070: buf.append("\u1e62");
3071: break; // S underdot
3072: case 222:
3073: buf.append("\u1e24");
3074: break; // H underdot
3075: case 223:
3076: buf.append("\u1e0c");
3077: break; // D underdot
3078: case 224:
3079: buf.append("\u2021");
3080: break; // daggerdbl
3081: case 226:
3082: buf.append("\u1e36");
3083: break; // L underdot
3084: case 227:
3085: buf.append("\u0113");
3086: break; // e macron
3087: case 229:
3088: buf.append("\u1e5f");
3089: break; // r underbar
3090: case 230:
3091: buf.append("r\u0324");
3092: break; // r underdieresis
3093: case 231:
3094: buf.append("\u012a");
3095: break; // I macron
3096: case 232:
3097: buf.append("\u016b");
3098: break; // u macron
3099: case 233:
3100: buf.append("\u01e6c");
3101: break; // T underdot
3102: case 234:
3103: buf.append("\u1e64");
3104: break; // S acute
3105: case 235:
3106: buf.append("\u2020");
3107: break; // dagger
3108: case 236:
3109: buf.append("\u0115");
3110: break; // e breve
3111: case 237:
3112: buf.append("\u014f");
3113: break; // o breve
3114: case 238:
3115: buf.append("\u0100");
3116: break; // A macron
3117: case 239:
3118: buf.append("\u1e46");
3119: break; // N underdot
3120: case 241:
3121: buf.append("\u1e3b");
3122: break; // l underbar
3123: case 242:
3124: buf.append("\u016a");
3125: break; // U macron
3126: case 243:
3127: buf.append("\u0179");
3128: break; // Z acute
3129: case 244:
3130: buf.append("\u1e5a");
3131: break; // R underdot
3132: case 245:
3133: buf.append("\u0131");
3134: break; // dotlessi
3135: case 246:
3136: buf.append("\u1e47");
3137: break; // n underdot
3138: case 247:
3139: buf.append("\u1e49");
3140: break; // n underbar
3141: case 248:
3142: buf.append("\u0304");
3143: break; // macron
3144: case 249:
3145: buf.append("\u0306");
3146: break; // breve
3147: case 250:
3148: buf.append("\u1e25");
3149: break; // h underdot
3150: case 251:
3151: buf.append("\u012d");
3152: break; // i breve
3153: case 252:
3154: buf.append("\u0327");
3155: break; // cedilla
3156: case 253:
3157: buf.append("\u030b");
3158: break; // hungarumlaut
3159: case 254:
3160: buf.append("\u0328");
3161: break; // ogonek
3162: case 255:
3163: buf.append("\u030c");
3164: break; // caron
3165: case 130:
3166: buf.append("\u012b\u0303");
3167: break; // imacron tilde
3168: case 165:
3169: buf.append("\u1e5d");
3170: break; // runderdot macron
3171: case 170:
3172: buf.append("\u016b\0306");
3173: break; // umacron breve
3174: case 178:
3175: buf.append("\u0101\u0301");
3176: break; // amacron acute
3177: case 179:
3178: buf.append("\u016b\u0301");
3179: break; // umacron acute
3180: case 184:
3181: buf.append("\u0113\u0301");
3182: break; // emacron acute
3183: case 187:
3184: buf.append("\u0113\u0300");
3185: break; // emacron breve
3186: case 188:
3187: buf.append("\u014d\u0300");
3188: break; // omacron breve
3189: case 193:
3190: buf.append("\u0101\u0306");
3191: break; // amacron breve
3192: case 198:
3193: buf.append("\u0101\u0303");
3194: break; // amacron tilde
3195: case 199:
3196: buf.append("\u012b\u0301");
3197: break; // imacron acute
3198: case 218:
3199: buf.append("\u1e00");
3200: break; // runderdotmacron acute
3201: case 225:
3202: buf.append("\u1e5b\u0301");
3203: break; // runderdot acute
3204: case 228:
3205: buf.append("\u012b\u0306");
3206: break; // imacron breve
3207: case 240:
3208: buf.append("\u016b\u0303");
3209: break; // umacron tilde
3210: default:
3211: buf.append(c);
3212: break;
3213: }
3214: }
3215: return buf.toString();
3216: }
3217:
3218: static public String normanToUnicodeNew(String inp)
3219: {
3220: StringBuffer buf = new StringBuffer();
3221: for (int i = 0; i < inp.length(); i++)
3222: {
3223: char c = inp.charAt(i);
3224: switch (c)
3225: {
3226: case 1:
3227: buf.append("\u00d0");
3228: break; // Eth
3229: case 2:
3230: buf.append("\u00f0");
3231: break; // eth
3232: case 3:
3233: buf.append("\u0141");
3234: break; // Lslash
3235: case 4:
3236: buf.append("\u0142");
3237: break; // lslash
3238: case 5:
3239: buf.append("\u0160");
3240: break; // S caron
3241: case 6:
3242: buf.append("\u0161");
3243: break; // s caron
3244: case 7:
3245: buf.append("\u00dd");
3246: break; // Y acute
3247: case 8:
3248: buf.append("\u00fd");
3249: break; // y acute
3250: case 11:
3251: buf.append("\u00de");
3252: break; // Thorn
3253: case 12:
3254: buf.append("\u00fe");
3255: break; // thorn
3256: case 14:
3257: buf.append("\u017d");
3258: break; // Z caron
3259: case 15:
3260: buf.append("\u017e");
3261: break; // z caron
3262: case 17:
3263: buf.append("\u0073");
3264: break; // asciitilde
3265: case 18:
3266: buf.append("j\u0305");
3267: break; // j macron [does a single char exist?]
3268: case 19:
3269: buf.append("^");
3270: break; // circumflex
3271: case 20:
3272: buf.append("\u0303");
3273: break; // tilde
3274: case 21:
3275: buf.append("\u00bd");
3276: break; // onehalf
3277: case 22:
3278: buf.append("\u00bc");
3279: break; // onequarter
3280: case 23:
3281: buf.append("\u00b9");
3282: break; // onesuperior
3283: case 24:
3284: buf.append("\u00be");
3285: break; // threequarters
3286: case 25:
3287: buf.append("\u00b3");
3288: break; // threesuperior
3289: case 26:
3290: buf.append("\u00b2");
3291: break; // twosuperior
3292: case 27:
3293: buf.append("\u00a6");
3294: break; // brokenbar
3295: case 28:
3296: buf.append("-");
3297: break; // minus
3298: case 29:
3299: buf.append("\u00d7");
3300: break; // multiply
3301: case 39:
3302: buf.append("'");
3303: break; // quotesingle
3304: case 94:
3305: buf.append("\u0302");
3306: break; // circumflex
3307: case 96:
3308: buf.append("\u0300");
3309: break; // grave
3310: case 196:
3311: buf.append("\u00c4");
3312: break; // A dieresis
3313: case 197:
3314: buf.append("\u00c5");
3315: break; // A ring
3316: case 201:
3317: buf.append("\u00c9");
3318: break; // E acute
3319: case 209:
3320: buf.append("\u00d1");
3321: break; // N tilde
3322: case 214:
3323: buf.append("\u00d6");
3324: break; // O dieresis
3325: case 220:
3326: buf.append("\u00dc");
3327: break; // U dieresis
3328: case 225:
3329: buf.append("\u00e1");
3330: break; // a acute
3331: case 224:
3332: buf.append("\u00e0");
3333: break; // a grave
3334: case 226:
3335: buf.append("\u00e2");
3336: break; // a circumflex
3337: case 228:
3338: buf.append("\u00e4");
3339: break; // a dieresis
3340: case 227:
3341: buf.append("\u00e3");
3342: break; // a tilde
3343: case 229:
3344: buf.append("\u0101");
3345: break; // a macron
3346: case 231:
3347: buf.append("\u00e7");
3348: break; // c cedilla
3349: case 233:
3350: buf.append("\u00e9");
3351: break; // e acute
3352: case 232:
3353: buf.append("\u00e8");
3354: break; // e grave
3355: case 234:
3356: buf.append("\u00ea");
3357: break; // e circumflex
3358: case 235:
3359: buf.append("\u00eb");
3360: break; // e dieresis
3361: case 237:
3362: buf.append("\u00ed");
3363: break; // i acute
3364: case 236:
3365: buf.append("\u00ec");
3366: break; // i grave
3367: case 238:
3368: buf.append("\u00ee");
3369: break; // i circumflex
3370: case 239:
3371: buf.append("\u00ef");
3372: break; // i dieresis
3373: case 241:
3374: buf.append("\u00f1");
3375: break; // n tilde
3376: case 243:
3377: buf.append("\u00f3");
3378: break; // o acute
3379: case 242:
3380: buf.append("\u00f2");
3381: break; // o grave
3382: case 244:
3383: buf.append("\u00f4");
3384: break; // o circumflex
3385: case 246:
3386: buf.append("\u00f6");
3387: break; // o dieresis
3388: case 245:
3389: buf.append("\u00f5");
3390: break; // o tilde
3391: case 250:
3392: buf.append("\u00fa");
3393: break; // u acute
3394: case 249:
3395: buf.append("\u00f9");
3396: break; // u grave
3397: case 251:
3398: buf.append("\u00fb");
3399: break; // u circumflex
3400: case 252:
3401: buf.append("\u00fc");
3402: break; // u dieresis
3403: case 8224:
3404: buf.append("\u1e6d");
3405: break; // t underdot
3406: case 176:
3407: buf.append("\u00b0");
3408: break; // degree
3409: case 162:
3410: buf.append("\u1ebd");
3411: break; // e tilde
3412: case 163:
3413: buf.append("\u00a3");
3414: break; // sterling
3415: case 167:
3416: buf.append("\u00a7");
3417: break; // section
3418: case 182:
3419: buf.append("\u00b6");
3420: break; // paragraph
3421: case 223:
3422: buf.append("\u015b");
3423: break; // s acute
3424: case 174:
3425: buf.append("\u1e5b");
3426: break; // r underdot
3427: case 169:
3428: buf.append("\u1e45");
3429: break; // n overdot
3430: case 180:
3431: buf.append("\u0301");
3432: break; // acute
3433: case 168:
3434: buf.append("\u0308");
3435: break; // dieresis
3436: case 8800:
3437: buf.append("\u1e6d");
3438: break; // t underdot
3439: case 198:
3440: buf.append("\u00c6");
3441: break; // AE
3442: case 216:
3443: buf.append("\u014d");
3444: break; // o macron
3445: case 8734:
3446: buf.append("\u0129");
3447: break; // i tilde
3448: case 177:
3449: buf.append("\u00b1");
3450: break; // plusminus
3451: case 165:
3452: buf.append("\u012b");
3453: break; // i macron
3454: case 181:
3455: buf.append("\u1e43");
3456: break; // m underdot
3457: case 8706:
3458: buf.append("\u1e0d");
3459: break; // d underdot
3460: case 8721:
3461: buf.append("\u1e63");
3462: break; // s underdot
3463: case 960:
3464: buf.append("\u017a");
3465: break; // z acute
3466: case 8747:
3467: buf.append("\u1e45");
3468: break; // n overdot
3469: case 937:
3470: buf.append("\u0169");
3471: break; // u tilde
3472: case 230:
3473: buf.append("\u00e6");
3474: break; // ae
3475: case 248:
3476: buf.append("\u00f8");
3477: break; // oslash
3478: case 191:
3479: buf.append("\u0304\u0306");
3480: break; // macron breve
3481: case 172:
3482: buf.append("\u1e37");
3483: break; //
3484: case 8730:
3485: buf.append("j\u0305");
3486: break; // j macron [does a single char exist?]
3487: case 402:
3488: buf.append("\u0103");
3489: break; // a breve
3490: case 8776:
3491: buf.append("\u016d");
3492: break; // u breve
3493: case 187:
3494: buf.append("\u1e42");
3495: break; // M underdot
3496: case 8230:
3497: buf.append("\u2026");
3498: break; // ellipsis
3499: case 192:
3500: buf.append("\u00c0");
3501: break; // A grave
3502: case 195:
3503: buf.append("\u00c3");
3504: break; // A tilde
3505: case 213:
3506: buf.append("\u00d5");
3507: break; // O tilde
3508: case 338:
3509: buf.append("m\u0306");
3510: break; // m breve
3511: case 339:
3512: buf.append("\u0153");
3513: break; // oe
3514: case 8211:
3515: buf.append("\u2013");
3516: break; // endash
3517: case 8212:
3518: buf.append("\u2014");
3519: break; // emdash
3520: case 8220:
3521: buf.append("\u201c");
3522: break; // quotedblleft
3523: case 8221:
3524: buf.append("\u201d");
3525: break; // quotedblright
3526: case 8216:
3527: buf.append("\u2018");
3528: break; // quoteleft
3529: case 8217:
3530: buf.append("\u2019");
3531: break; // quoteright
3532: case 247:
3533: buf.append("\u1e37");
3534: break; // l underring [actually underdot]
3535: case 9674:
3536: buf.append("\u1e41");
3537: break; // m overdot
3538: case 255:
3539: buf.append("n\u0306");
3540: break; // n breve
3541: case 376:
3542: buf.append("\u00d7");
3543: break; // multiply
3544: case 8364:
3545: buf.append("\u1e5b");
3546: break; // r underring [actually underdot]
3547: case 8249:
3548: buf.append("\u1e44");
3549: break; // N overdot
3550: case 8250:
3551: buf.append("\u1e62");
3552: break; // S underdot
3553: case 64257:
3554: buf.append("\u1e24");
3555: break; // H underdot
3556: case 64258:
3557: buf.append("\u1e0c");
3558: break; // D underdot
3559: case 8225:
3560: buf.append("\u2021");
3561: break; // daggerdbl
3562: case 8218:
3563: buf.append("\u1e36");
3564: break; // L underdot
3565: case 8222:
3566: buf.append("\u0113");
3567: break; // e macron
3568: case 194:
3569: buf.append("\u1e5f");
3570: break; // r underbar
3571: case 202:
3572: buf.append("r\u0324");
3573: break; // r underdieresis
3574: case 193:
3575: buf.append("\u012a");
3576: break; // I macron
3577: case 203:
3578: buf.append("\u016b");
3579: break; // u macron
3580: case 200:
3581: buf.append("\u1e6c");
3582: break; // T underdot
3583: case 205:
3584: buf.append("\u1e64");
3585: break; // S acute
3586: case 206:
3587: buf.append("\u2020");
3588: break; // dagger
3589: case 207:
3590: buf.append("\u0115");
3591: break; // e breve
3592: case 204:
3593: buf.append("\u014f");
3594: break; // o breve
3595: case 211:
3596: buf.append("\u0100");
3597: break; // A macron
3598: case 212:
3599: buf.append("\u1e46");
3600: break; // N underdot
3601: case 210:
3602: buf.append("\u1e3b");
3603: break; // l underbar
3604: case 218:
3605: buf.append("\u016a");
3606: break; // U macron
3607: case 219:
3608: buf.append("\u0179");
3609: break; // Z acute
3610: case 217:
3611: buf.append("\u1e5a");
3612: break; // R underdot
3613: case 305:
3614: buf.append("\u0131");
3615: break; // dotlessi
3616: case 710:
3617: buf.append("\u1e47");
3618: break; // n underdot
3619: case 732:
3620: buf.append("\u1e49");
3621: break; // n underbar
3622: case 175:
3623: buf.append("\u0304");
3624: break; // macron
3625: case 728:
3626: buf.append("\u0306");
3627: break; // breve
3628: case 729:
3629: buf.append("\u1e25");
3630: break; // h underdot
3631: case 730:
3632: buf.append("\u012d");
3633: break; // i breve
3634: case 184:
3635: buf.append("\u0327");
3636: break; // cedilla
3637: case 733:
3638: buf.append("\u030b");
3639: break; // hungarumlaut
3640: case 731:
3641: buf.append("\u0328");
3642: break; // ogonek
3643: case 711:
3644: buf.append("\u030c");
3645: break; // caron
3646: case 199:
3647: buf.append("\u012b\u0303");
3648: break; // imacron tilde
3649: case 8226:
3650: buf.append("\u1e5d");
3651: break; // runderdot macron
3652: case 8482:
3653: buf.append("\u016b\0306");
3654: break; // umacron breve
3655: case 8804:
3656: buf.append("\u0101\u0301");
3657: break; // amacron acute
3658: case 8805:
3659: buf.append("\u016b\u0301");
3660: break; // umacron acute
3661: case 8719:
3662: buf.append("\u0113\u0301");
3663: break; // emacron acute
3664: case 170:
3665: buf.append("\u0113\u0300");
3666: break; // emacron breve
3667: case 186:
3668: buf.append("\u014d\u0300");
3669: break; // omacron breve
3670: case 161:
3671: buf.append("\u0101\u0306");
3672: break; // amacron breve
3673: case 8710:
3674: buf.append("\u0101\u0303");
3675: break; // amacron tilde
3676: case 171:
3677: buf.append("\u012b\u0301");
3678: break; // imacron acute
3679: case 8260:
3680: buf.append("\u1e00");
3681: break; // runderdotmacron acute
3682: case 183:
3683: buf.append("\u1e5b\u0301");
3684: break; // runderdot acute
3685: case 8240:
3686: buf.append("\u012b\u0306");
3687: break; // imacron breve
3688: case 63743:
3689: buf.append("\u016b\u0303");
3690: break; // umacron tilde
3691: default:
3692: buf.append(c);
3693: break;
3694: }
3695: }
3696: return buf.toString();
3697: }
3698:
3699: public static ConversionProperties getFieldNamesAndDestTableName(String create, String query,
3700: String tableName)
3701: {
3702: String[] fieldNames = null;
3703: String destTableName = null;
3704: // determine destTableName from createStatement or from source table
3705: // name
3706: if (!create.equals(""))
3707: {
3708: int fromIndex = create.toLowerCase().indexOf("table") + 5;
3709: int toIndex = create.indexOf("(");
3710: int endIndex = create.indexOf(")", toIndex);
3711:
3712: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
3713: System.out.println("destTable " + destTableName);
3714: // retrieve field_names from select statement
3715: // TODO problem with different fieldNames in create statement will
3716: // overwrite them
3717: if (query.indexOf("*") < 0 && create.equals(""))// quick hack for hartmut
3718: {
3719: int selectEndIndex = query.indexOf("from");
3720: StringTokenizer tokenizer = new StringTokenizer(query.substring(6, selectEndIndex), ",");
3721: int numFields = tokenizer.countTokens();
3722: fieldNames = new String[numFields];
3723: int fieldIndex = 0;
3724: while (tokenizer.hasMoreTokens())
3725: {
3726: String fieldName = tokenizer.nextToken().trim();
3727: fieldNames[fieldIndex] = convertText(fieldName);
3728: System.out.println(fieldNames[fieldIndex]);
3729: fieldIndex++;
3730: }
3731:
3732: } else
3733: {
3734: // use create statement for field names
3735: StringTokenizer tokenizer = new StringTokenizer(create.substring(toIndex + 1, endIndex),
3736: ",");
3737: int numFields = tokenizer.countTokens();
3738: fieldNames = new String[numFields];
3739: int fieldIndex = 0;
3740: while (tokenizer.hasMoreTokens())
3741: {
3742: String fieldName = tokenizer.nextToken().trim();
3743: int index = fieldName.lastIndexOf(" ");
3744: fieldNames[fieldIndex] = fieldName.substring(0, index);
3745: System.out.println(fieldNames[fieldIndex]);
3746: fieldIndex++;
3747: }
3748: }
3749: } else
3750: {
3751: destTableName = convertText(tableName);
3752:
3753: // retrieve field_names from select statement
3754: if (query.indexOf("*") < 0)
3755: {
3756: int selectEndIndex = query.lastIndexOf("from");
3757: StringTokenizer tokenizer = new StringTokenizer(query.substring(6, selectEndIndex), ",");
3758: int numFields = tokenizer.countTokens();
3759: fieldNames = new String[numFields];
3760: int fieldIndex = 0;
3761: while (tokenizer.hasMoreTokens())
3762: {
3763: String fieldName = tokenizer.nextToken().trim();
3764: String text = convertText(fieldName);
3765: if (text.indexOf("\"") >= 0)
3766: fieldNames[fieldIndex] = convertText(fieldName);
3767: else
3768: fieldNames[fieldIndex] = beanDest.getQC() + convertText(fieldName) + beanDest.getQC();
3769: // System.out.println("field "+ fieldNames[fieldIndex]);
3770: fieldIndex++;
3771: }
3772:
3773: } else
3774: {
3775: Vector fieldNamesVec = bean.getColumnNames();
3776: fieldNames = new String[fieldNamesVec.size()];
3777: int fieldIndex = -1;
3778: for (Iterator iter = fieldNamesVec.iterator(); iter.hasNext();)
3779: {
3780: String element = (String) iter.next();
3781: fieldNames[++fieldIndex] = beanDest.getQC() + convertText(element) + beanDest.getQC();
3782: // System.out.println("field " + fieldNames[fieldIndex]);
3783: }
3784: }
3785: }
3786: return new ConversionProperties(destTableName, fieldNames);
3787: }
3788:
3789: /**
3790: * creates an insert into statement for the specified table and given field
3791: * names
3792: *
3793: * @param destTableName
3794: * @param fieldNames
3795: * @return
3796: */
3797: public static StringBuffer createInsertCommand(String destTableName, String[] fieldNames)
3798: {
3799: StringBuffer command = new StringBuffer();
3800: command.append("INSERT INTO ");
3801: command.append(beanDest.getQC());
3802: command.append(destTableName); // convertText((String)
3803: // names.get(tbIndex)));
3804: command.append(beanDest.getQC());
3805: command.append(" (");
3806: for (int i = 0; i < fieldNames.length; i++)
3807: {
3808: command.append(fieldNames[i]);
3809: if (i < fieldNames.length - 1)
3810: command.append(",");
3811: }
3812: command.append(") ");
3813:
3814: command.append(" values ( ");
3815: // add a question marks for every field
3816: for (int i = 0; i < fieldNames.length - 1; ++i)
3817: command.append("?,");
3818: command.append("?)");
3819: return command;
3820: }
3821:
3822: public static StringBuffer createUpdateCommand(String destTableName, String[] fieldNames,
3823: String id)
3824: {
3825: StringBuffer command = new StringBuffer();
3826:
3827: command.append("UPDATE ");
3828: command.append(beanDest.getQC());
3829: command.append(destTableName);
3830: command.append(beanDest.getQC());
3831: command.append(" SET ");
3832:
3833: int size = bean.getColumnNames().size();
3834: for (int i = 0; i < size - 1; ++i)
3835: command.append(fieldNames[i] + " = ? ,");
3836: command.append(fieldNames[size - 1] + " = ? ");
3837: command.append("WHERE " + id + " = ?");
3838: return command;
3839: }
3840:
3841: public static StringBuffer createDeleteCommand(String destTableName, String idField)
3842: {
3843: StringBuffer command = new StringBuffer();
3844:
3845: command.append("DELETE FROM");
3846: command.append(beanDest.getQC());
3847: command.append(destTableName);
3848: // command.append(convertText((String) names.get(tbIndex)));
3849: command.append(beanDest.getQC());
3850: command.append("WHERE " + idField + " = ?");
3851: return command;
3852: }
3853:
3854: public void makeTest(String table, String idField, String tempQuery) throws Exception
3855: {
3856: int counter = 0;
3857:
3858: // ****** test code *****
3859:
3860: bean.getConnection();
3861: ResultSet resultSet = null;
3862: String lastResult = "P227634.11";// "P227625.79554";//"P227625.77391";//"P116034.970998";
3863: String myQuery = "select " + bean.getQC() + idField + bean.getQC() + ",serial " + " from "
3864: + bean.getQC() + table + bean.getQC();
3865: System.out.println("Query is now " + myQuery);
3866: JDialog statusDialog = new JDialog();
3867: statusDialog.setTitle("Status Information");
3868: JLabel status = new JLabel("actual DataSet : ");
3869: JLabel status2 = new JLabel(Integer.toString(++counter));
3870: JLabel status3 = new JLabel(lastResult);
3871:
3872: JPanel statusPanel = new JPanel();
3873: JPanel statusPanel2 = new JPanel();
3874: statusPanel.add(status);
3875: statusPanel.add(status2);
3876: statusPanel2.add(status3);
3877: statusDialog.getContentPane().add(statusPanel, "North");
3878: statusDialog.getContentPane().add(statusPanel2, "Center");
3879: statusDialog.setLocation(400, 500);
3880: statusDialog.setSize(300, 150);
3881: statusDialog.setVisible(true);
3882: while (true)
3883: {
3884: if (!statusDialog.isVisible())
3885: statusDialog.setVisible(true);
3886: tempQuery = myQuery + " where " + bean.getQC() + idField + bean.getQC() + ">'" + lastResult
3887: + "'";
3888: resultSet = bean.makeQuery(tempQuery, 1);
3889: if (resultSet == null)
3890: {
3891: System.out.println("lastResult was " + lastResult + " counter was " + counter);
3892: break;
3893: } else
3894: {
3895: resultSet.next();
3896: lastResult = resultSet.getString(1);
3897: counter++;
3898: status2.setText(Integer.toString(counter));
3899: status3.setText(lastResult + " " + resultSet.getString(2));
3900: if (counter % 100 == 0)
3901: {
3902: System.out.println("actual Result was " + lastResult + " counter was " + counter);
3903: // break;
3904: }
3905: }
3906: resultSet = null;
3907: }
3908: System.exit(0);
3909:
3910: // ****** end Test ******
3911:
3912: }
3913:
3914: public final static String generateSuffix(final int step)
3915: {
3916: String fileString = null;
3917: if (step < 10)
3918: fileString = "00" + step;
3919: else if (step < 100)
3920: fileString = "0" + step;
3921: else
3922: fileString = step + "";
3923: return fileString;
3924: }
3925:
3926: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/cvs/cvsweb/icons/back.gif){kind=icon}
Return to Convert.java CVS log ![[TXT]](/cvs/cvsweb/icons/text.gif){kind=icon}
![[DIR]](/cvs/cvsweb/icons/dir.gif){kind=icon}
Up to [Repository] / FM2SQL / src