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