Annotation of FM2SQL/Convert.java, revision 1.54
1.51 rogo 1: /*
2: * Convert.java -- Converter class - Filemaker to SQL Converter
1.52 rogo 3: * Copyright (C) 2003 Robert Gordesch (rogo@mpiwg-berlin.mpg.de)
1.51 rogo 4: * This program is free software; you can redistribute it and/or modify it
5: * under the terms of the GNU General Public License as published by the Free
6: * Software Foundation; either version 2 of the License, or (at your option)
7: * any later version. Please read license.txt for the full details. A copy of
8: * the GPL may be found at http://www.gnu.org/copyleft/lgpl.html You should
9: * have received a copy of the GNU General Public License along with this
10: * program; if not, write to the Free Software Foundation, Inc., 59 Temple
11: * Place, Suite 330, Boston, MA 02111-1307 USA Created on 15.09.2003 by
12: * rogo
13: */
14:
1.1 rogo 15: import java.util.*;
16: import java.sql.*;
17: import java.awt.Cursor;
18: import java.io.BufferedReader;
19: import java.io.BufferedWriter;
20: import java.io.File;
21: import java.io.FileInputStream;
22: import java.io.FileNotFoundException;
23: import java.io.FileOutputStream;
24: import java.io.InputStreamReader;
25: import java.io.OutputStreamWriter;
26: import java.io.PrintStream;
27: import java.io.UnsupportedEncodingException;
28:
29: import com.exploringxml.xml.Node;
30: import com.exploringxml.xml.Xparse;
31:
1.34 rogo 32: class Convert
1.1 rogo 33: {
1.34 rogo 34: static DBBean bean = new DBBean();
35: static DBBean beanDest = new DBBean();
1.1 rogo 36:
1.34 rogo 37: static String user = "", passwd = "e1nste1n";
38: static String userDest = "postgres", passwdDest = "rogo";
39: static boolean batchRun = false;
1.1 rogo 40: static Vector databases = new Vector();
1.30 rogo 41: final static int numHits = 5000;
42: final static int numIntervalls = 2;
1.1 rogo 43: public static void main(String args[])
44: {
1.34 rogo 45: /* try
46: {
47: //byte[] b = "ö".getBytes("UTF-8");
48: // System.out.println("QueryString " +b[0]+" "+b[1]+(new String(b).getBytes()[0])+" "+new String(b).getBytes()[1]);
49: //System.out.println(new String(b,"UTF-8"));
50: } catch (UnsupportedEncodingException e)
51: {
52: e.printStackTrace();
53: }*/
1.1 rogo 54: FileOutputStream file = null;
1.34 rogo 55: if (args.length != 1)
1.1 rogo 56: {
1.34 rogo 57: System.out.println("Usage: java Convert <xml config file>");
58: System.exit(-1);
1.1 rogo 59: }
1.34 rogo 60: if (!(new File(args[0]).exists()))
61: System.exit(0);
1.1 rogo 62: try
63: {
64: file = new FileOutputStream("./log.txt");
65: } catch (FileNotFoundException e1)
66: {
67: e1.printStackTrace();
1.34 rogo 68: }
69: PrintStream stream = new PrintStream(file);
70: System.setOut(stream);
71: System.setErr(stream);
72: readXMLFile(args[0]);
73: System.out.println("Finished!");
1.1 rogo 74: //convert("jdbc:fmpro:http://141.14.237.74:8050","jdbc:postgresql://erebos/test",null,null);
75: }
1.42 rogo 76: public static void convertBatch(DBBean source, DBBean destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids,int mode,String delimiter) throws Exception
1.1 rogo 77: {
1.34 rogo 78: bean = source;
79: beanDest = destination;
1.42 rogo 80: convert(null,null,names,layouts,selects,creates,ids,mode,delimiter);
1.34 rogo 81: if(true) return;
82: StringBuffer command = null;
1.1 rogo 83: try
84: {
85: bean.setConnection(source.url);
1.34 rogo 86: if (names == null)
87: names = bean.getTableNames();
1.1 rogo 88: //Collections.sort(names);
1.34 rogo 89: int tbIndex = 1;
90:
91: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1.1 rogo 92: {
93: Vector[] result = null;
1.34 rogo 94: try
1.1 rogo 95: {
1.34 rogo 96: String query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
97: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
98: query = (selects != null) ? selects.get(tbIndex).toString() : query;
99: //if vectors[1].get(i) != null)
100: if (!layout.equals(""))
101: {
102: System.out.println("before " + query + " table" + names.get(tbIndex));
103: layout = " layout " + bean.getQC() + layout + bean.getQC();
104: String name = names.get(tbIndex).toString();
105: StringBuffer queryLayout = new StringBuffer(query);
106: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
107: query = queryLayout.toString();
108: System.out.println("added layout " + query);
109:
110: }
111: System.out.println(" performing query " + query);
1.1 rogo 112: //result = bean.getQueryData(query, null, 0);
1.34 rogo 113: bean.getConnection();
114: bean.makeQuery(query, 0);
115: } catch (Exception e)
1.1 rogo 116: {
117: System.out.println(e.getMessage());
118: e.printStackTrace();
119: continue;
120: }
121: //beanDest.setConnection("jdbc:postgresql://erebos/test3");
122: beanDest.setConnection(destination.url);
123:
124: Statement stm = beanDest.getConnection().createStatement();
125:
126: Vector tables = beanDest.getTableNames();
1.34 rogo 127: // Collections.sort(tables);
128: System.out.println("converting table " + names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
1.1 rogo 129: tables = beanDest.getTableNames();
130: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
131: stm = beanDest.getConnection().createStatement();
132: // System.exit(0);
1.34 rogo 133: if (mode == Convert.DataBase.CONVERT_MODE)
1.1 rogo 134: {
1.34 rogo 135: if (tables.indexOf(names.get(tbIndex)) >= 0)
136: {
137: stm.executeUpdate("drop table " + beanDest.getQC() + names.get(tbIndex) + beanDest.getQC());
138: tables.remove((String) names.get(tbIndex));
139: System.out.println("dropped table " + names.get(tbIndex));
140: } else if (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0)
1.1 rogo 141: {
1.34 rogo 142: stm.executeUpdate("drop table " + beanDest.getQC() + convertText((String) names.get(tbIndex)) + beanDest.getQC());
143: tables.remove(convertText((String) names.get(tbIndex)));
144: System.out.println("dropped table " + names.get(tbIndex));
145: }
146:
147: if (tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0)
1.1 rogo 148: {
1.34 rogo 149: if (creates.get(tbIndex).equals("") || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
150: {
151: System.out.println("Warning empty or invalid create statement - creating one for you\n");
152:
153: command = new StringBuffer(50);
154: command.append("CREATE TABLE ");
155: command.append(beanDest.getQC());
156: command.append(convertText((String) names.get(tbIndex)));
157: command.append(beanDest.getQC());
158: command.append("(");
159: String type = null;
160: Vector columnNames = bean.getColumnNames();
161: for (int i = 0; i < columnNames.size() - 1; ++i)
162: {
163: type = bean.metaData.getColumnTypeName(i + 1);
164: // System.out.println(i+" "+result[1].get(i)+" "+type);
165: type = (type.equals("NUMBER")) ? "INT4" : type;
166: type = (type.equals("CONTAINER")) ? "TEXT" : type;
167:
168: command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
169: }
170: type = bean.metaData.getColumnTypeName(columnNames.size());
171: type = (type.equals("NUMBER")) ? "INT4" : type;
172: type = (type.equals("CONTAINER")) ? "TEXT" : type;
173: command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
174: command.append(" )");
175: } else
176: command = new StringBuffer().append(creates.get(tbIndex).toString());
177:
178: System.out.println(command);
179: // System.exit(0);
180: //command.append(DBBean.getQC());
181: stm.executeUpdate(command.toString());
1.1 rogo 182:
1.34 rogo 183: }
1.1 rogo 184: }
1.34 rogo 185: Vector row = null;
186: command = new StringBuffer();
1.1 rogo 187:
188: command.append("INSERT INTO ");
189: command.append(beanDest.getQC());
190: command.append(convertText((String) names.get(tbIndex)));
191: command.append(beanDest.getQC());
192: command.append(" values ( ");
1.3 rogo 193:
1.34 rogo 194: for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
195: command.append("?,");
196: command.append("?)");
197: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
198: System.out.println(command);
199: while ((row = bean.getNextRow()) != null)
200: {
201: //print rows
202: Object obj = null;
203: for (int k = 0; k < row.size(); ++k)
204: {
205: obj = row.get(k);
206: if (obj instanceof ArrayList)
1.41 rogo 207: obj = formatFileMakerArray((List) obj,"\n");
1.34 rogo 208: String str = (obj == null) ? "NULL" : obj.toString();
209: if (!str.equals("NULL"))
210: pstm.setString(k + 1, str);
211: else
212: pstm.setNull(k + 1, Types.NULL);
213: }
214: pstm.execute();
215:
216: } // to for loop
217:
218: }
219: } catch (Exception e)
220: {
221: System.out.println("Error while connecting to database " + e);
222: //dialog.setVisible(false);
223: //dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
224: //FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
225: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
226: java.io.PrintStream stream = new java.io.PrintStream(b);
227: stream.print(command + "\n\n");
228: e.printStackTrace(stream);
229: System.err.println(b);
230: //FM2SQL.showErrorDialog(b.toString(), "Error occured !");
231:
1.1 rogo 232: }
1.34 rogo 233: // dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
234: //FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1.1 rogo 235:
1.34 rogo 236: // dialog.setVisible(false);
1.1 rogo 237: }
1.41 rogo 238: public static String formatFileMakerArray(List list, String delimiter)
239: {
240: StringBuffer formattedString = new StringBuffer();
241: for(int i=0;i<list.size();++i)
242: {
243: formattedString.append(list.get(i).toString());
244: if(i<list.size()-1)
245: formattedString.append(delimiter);
246: }
247: return formattedString.toString();
248: }
1.38 rogo 249: /**
250: * Method for SQL UPDATE
251: * @param source
252: * @param destination
253: * @param names
254: * @param layouts
255: * @param selects
256: * @param creates
257: * @param ids
258: * @param mode
259: * @throws Exception
260: */
1.34 rogo 261: public static void update(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
1.12 rogo 262: {
263: FM2SQL.ProgressDialog dialog = null;
264: if (FM2SQL.fmInstance != null)
265: {
1.53 rogo 266: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance,bean);
1.12 rogo 267: dialog.setTitle("Conversion running ...");
268: dialog.title.setText("Getting table data ...");
269: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
270: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
271: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
272: dialog.thread = Thread.currentThread();
273: }
274: // setting user and passwd
275: bean.setUserAndPasswd(user, passwd);
276: // setting user and passwd
277: beanDest.setUserAndPasswd(userDest, passwdDest);
278: if (dialog != null)
279: dialog.setSize(400, 250);
280: StringBuffer command = null;
281: String query = null;
282: try
283: {
284: //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
285: //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
286: bean.setConnection(source);
287: if (names == null)
288: names = bean.getTableNames();
289: // Collections.sort(names);
290: int tbIndex = 1;
1.45 rogo 291:
1.12 rogo 292: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
293: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
294: {
295: Vector[] result = null;
1.45 rogo 296: String destTableName = "";
1.12 rogo 297: try
298: {
299: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
300: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
301: query = (selects != null) ? selects.get(tbIndex).toString() : query;
302: //if vectors[1].get(i) != null)
303: if (layout != "")
304: {
305: layout = " layout " + bean.getQC() + layout + bean.getQC();
306: String name = names.get(tbIndex).toString();
307: StringBuffer queryLayout = new StringBuffer(query);
308: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
309: query = queryLayout.toString();
310: System.out.println("added layout " + query);
311:
312: }
313: dialog.title.setText("Getting table data ...");
314: dialog.table.setText(names.get(tbIndex).toString());
315: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
316: dialog.show();
317: bean.getConnection();
318: bean.makeQuery(query, 0);
319: } catch (Exception e)
320: {
321: continue;
322: }
1.45 rogo 323: // determine destTableName from createStatement or from source table name
324: if(!creates.get(tbIndex).equals(""))
325: {
326: String create =creates.get(tbIndex).toString().toLowerCase();
327: int fromIndex = create.indexOf("table")+5;
328: int toIndex = create.indexOf("(");
329: destTableName = create.substring(fromIndex,toIndex).replaceAll(beanDest.getQC(),"").trim();
330: System.out.println("destTable "+destTableName);
331:
332: } else
333: destTableName = convertText(names.get(tbIndex).toString());
334:
1.12 rogo 335: //beanDest.setConnection("jdbc:postgresql://erebos/test3");
336: beanDest.setConnection(destination);
337:
338: Statement stm = beanDest.getConnection().createStatement();
339:
340: Vector tables = beanDest.getTableNames();
341: // Collections.sort(tables);
342: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
343: tables = beanDest.getTableNames();
344: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
345: stm = beanDest.getConnection().createStatement();
346: // System.exit(0);
1.34 rogo 347:
1.12 rogo 348: if (dialog != null)
1.36 rogo 349: dialog.title.setText("Updating table data ...");
1.12 rogo 350:
351: int j = -1;
1.34 rogo 352:
1.12 rogo 353: Vector row = null;
354: command = new StringBuffer();
355:
356: command.append("UPDATE ");
357: command.append(beanDest.getQC());
1.45 rogo 358: command.append(destTableName);
359: //command.append(convertText((String) names.get(tbIndex)));
1.12 rogo 360: command.append(beanDest.getQC());
361: command.append(" SET ");
362:
363: int size = bean.getColumnNames().size();
364: for (int i = 0; i < size - 1; ++i)
1.34 rogo 365: command.append(beanDest.getQC() + convertText((String) bean.getColumnNames().get(i)) + beanDest.getQC() + " = ? ,");
366: command.append(convertText((String) bean.getColumnNames().get(size - 1)) + " = ? ");
367: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
1.12 rogo 368: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
1.34 rogo 369: System.out.println(command + " " + tbIndex);
370: int rowCount = bean.getRowCount(query);
371: int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
1.12 rogo 372: while ((row = bean.getNextRow()) != null)
373: {
374: j++;
375: //print rows
376: Object obj = null;
377: /* for(int k=0;k<row.size()-1;++k)
378: {
379: obj = row.get(k);
380: //System.out.println("row "+obj+" "+k);
381: if(obj!=null&&!(obj instanceof ArrayList))
382: command.append("'"+convertUml(obj.toString())+"',");
383: else if(obj!=null&& obj instanceof ArrayList)
384: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
385: else command.append("NULL,");
386: }
387: obj = row.get(row.size() - 1);
388: if (obj != null && !(obj instanceof ArrayList))
389: command.append("'"+convertUml(obj.toString())+"')");
390: else
391: if(obj!=null&& obj instanceof ArrayList)
392: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')"); //command.append(obj.toString()+")");
393: else command.append("NULL)");
394: */
395: //command.append("'"+row.get(row.size()-1)+"')");
396: //command.append(" )");
397: // for(int k=0;k<row.size();++k)
398:
399: // System.out.println();
400: // System.out.println(command+" "+j+" "+row.size()+" "+ ((Vector)result2[0].get(j)).size());
401: // System.out.println(command);
402: for (int k = 0; k < row.size(); ++k)
403: {
404: obj = row.get(k);
405: if (obj instanceof ArrayList)
406: obj = ((List) obj).get(0);
407: String str = (obj == null) ? "NULL" : obj.toString();
408: if (!str.equals("NULL"))
409: pstm.setString(k + 1, str);
410: else
411: pstm.setNull(k + 1, Types.NULL);
412: }
1.34 rogo 413: pstm.setString(row.size() + 1, row.get(idIndex).toString());
414: //System.out.println(pstm.toString());
415: // System.exit(0);
1.12 rogo 416: pstm.execute();
417: //stm.executeUpdate(command.toString());
1.34 rogo 418: if (dialog != null)
419: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
1.12 rogo 420: // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
421: command = null;
422: } // to for loop
423:
424: }
425: } catch (Exception e)
426: {
427: System.out.println("Error while connecting to database " + e);
1.18 rogo 428: if (dialog != null)
429: {
430: dialog.setVisible(false);
431: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
432: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
433: }
1.12 rogo 434: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
435: java.io.PrintStream stream = new java.io.PrintStream(b);
436: stream.print(command + "\n\n");
437: e.printStackTrace(stream);
438: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
439:
440: }
1.18 rogo 441: if (dialog != null)
442: {
443: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
444: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
445:
446: dialog.setVisible(false);
447: }
1.12 rogo 448:
449: }
1.34 rogo 450: /**
1.38 rogo 451: * transfers the specified array of tables to the destination database
452: and creates the table if it does not exist if it exists and mode is not append the table is dropped
453:
454: * @param source
455: * @param destination
456: * @param names
457: * @param layouts
458: * @param selects
459: * @param creates
460: * @param ids
461: * @param mode
462: * @throws Exception
463: */
464:
465:
1.42 rogo 466: public static void convert(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode,String delimiter) throws Exception
1.1 rogo 467: {
1.34 rogo 468:
469: FM2SQL.ProgressDialog dialog = null;
470:
471: if (FM2SQL.fmInstance != null)
472: {
1.53 rogo 473: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance,bean);
1.34 rogo 474: dialog.setTitle("Conversion running ...");
475: dialog.title.setText("Getting table data ...");
476: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
477: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
478: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
479: dialog.thread = Thread.currentThread();
480: dialog.setSize(400, 250);
481: }
482: java.util.TreeSet myIds = new TreeSet();
483: int deltaID = 1;
484: String idField = "";
1.38 rogo 485: String destTableName = "";
1.34 rogo 486: if (source != null && destination != null)
487: {
488: // setting user and passwd
489: bean.setUserAndPasswd(user, passwd);
490: // setting user and passwd
491: beanDest.setUserAndPasswd(userDest, passwdDest);
492: }
493: StringBuffer command = null;
494: String query = null;
1.1 rogo 495: try
496: {
1.34 rogo 497: if(source!=null)
1.1 rogo 498: bean.setConnection(source);
1.34 rogo 499: else
500: bean.setConnection(bean.url);
501:
502: if (names == null)
503: names = bean.getTableNames();
504: // Collections.sort(names);
505: int tbIndex = 1;
506:
507: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
508: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1.1 rogo 509: {
510: Vector[] result = null;
1.34 rogo 511: try
1.1 rogo 512: {
1.34 rogo 513: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
514: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
515: query = (selects != null) ? selects.get(tbIndex).toString() : query;
516: //if vectors[1].get(i) != null)
517: if (layout != "")
518: {
519: layout = " layout " + bean.getQC() + layout + bean.getQC();
520: String name = names.get(tbIndex).toString();
521: StringBuffer queryLayout = new StringBuffer(query);
522: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
523: query = queryLayout.toString();
524: System.out.println("added layout " + query);
1.21 rogo 525:
1.34 rogo 526: }
527: // if ( layout!= "")
528: // query += " layout " + bean.getQC() + layout + bean.getQC();
529: if (dialog != null)
530: {
531: dialog.title.setText("Reading table data ...");
532: dialog.table.setText(names.get(tbIndex).toString());
533: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
534: dialog.show();
535: }
536: //result = bean.getQueryData(query, dialog, 0);
537: bean.getConnection();
538: bean.makeQuery(query, 50);
539: idField = ids.get(tbIndex).toString();
540:
541: } catch (Exception e)
542: {
543: System.out.println(e);
544: continue;
1.1 rogo 545: }
1.34 rogo 546: if(destination!=null)
1.1 rogo 547: beanDest.setConnection(destination);
1.34 rogo 548: else
549: beanDest.setConnection(beanDest.url);
1.1 rogo 550: Statement stm = beanDest.getConnection().createStatement();
551:
552: Vector tables = beanDest.getTableNames();
1.34 rogo 553: // Collections.sort(tables);
1.1 rogo 554: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
555: tables = beanDest.getTableNames();
556: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
557: stm = beanDest.getConnection().createStatement();
558: // System.exit(0);
1.45 rogo 559:
560: // determine destTableName from createStatement or from source table name
561: if(!creates.get(tbIndex).equals(""))
562: {
563: String create =creates.get(tbIndex).toString().toLowerCase();
564: int fromIndex = create.indexOf("table")+5;
565: int toIndex = create.indexOf("(");
566: destTableName = create.substring(fromIndex,toIndex).replaceAll(beanDest.getQC(),"").trim();
567: System.out.println("destTable "+destTableName);
568:
569: } else
570: destTableName = convertText(names.get(tbIndex).toString());
571:
1.34 rogo 572: if (mode == Convert.DataBase.CONVERT_MODE)
1.1 rogo 573: {
1.38 rogo 574:
575: if (tables.indexOf(destTableName) >= 0)
576: {
577: stm.executeUpdate("drop table " + beanDest.getQC() + destTableName + beanDest.getQC());
578: tables.remove(destTableName);
579: System.out.println("dropped table" + destTableName);
580:
581: }
582: /*
583: if(destTableName.equals(""))
1.34 rogo 584: if (tables.indexOf(names.get(tbIndex)) >= 0)
585: {
586: stm.executeUpdate("drop table " + beanDest.getQC() + names.get(tbIndex) + beanDest.getQC());
587: tables.remove((String) names.get(tbIndex));
588: System.out.println("dropped table" + names.get(tbIndex));
589: } else if (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0)
1.1 rogo 590: {
1.34 rogo 591: stm.executeUpdate("drop table " + beanDest.getQC() + convertText((String) names.get(tbIndex)) + beanDest.getQC());
592: tables.remove(convertText((String) names.get(tbIndex)));
593: System.out.println("dropped table" + names.get(tbIndex));
594: }
1.38 rogo 595: */
596: if ((tables.indexOf(destTableName) < 0)) //&& tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0 )
1.1 rogo 597: {
1.38 rogo 598:
1.34 rogo 599: if (creates.get(tbIndex).equals("") || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
600: {
601: System.out.println("Warning empty or invalid create statement - creating one for you\n");
602:
603: command = new StringBuffer(50);
604: command.append("CREATE TABLE ");
605: command.append(beanDest.getQC());
606: command.append(convertText((String) names.get(tbIndex)));
607: command.append(beanDest.getQC());
608: command.append("(");
609: String type = null;
610: Vector columnNames = bean.getColumnNames();
611: for (int i = 0; i < columnNames.size() - 1; ++i)
612: {
613: type = bean.metaData.getColumnTypeName(i + 1);
614: // System.out.println(i+" "+result[1].get(i)+" "+type);
615: type = (type.equals("NUMBER")) ? "INT4" : type;
616: type = (type.equals("CONTAINER")) ? "TEXT" : type;
617:
618: command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
619: }
620: type = bean.metaData.getColumnTypeName(columnNames.size());
621: type = (type.equals("NUMBER")) ? "INT4" : type;
622: type = (type.equals("CONTAINER")) ? "TEXT" : type;
623: command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
624: command.append(" )");
625:
626: // System.out.println(command);
627: // System.exit(0);
628: //command.append(DBBean.getQC());
629: } else
1.38 rogo 630: command = new StringBuffer().append(creates.get(tbIndex).toString().toLowerCase());
1.34 rogo 631: stm.executeUpdate(command.toString());
1.1 rogo 632:
1.34 rogo 633: }
1.1 rogo 634: }
1.34 rogo 635: if(dialog!=null)
636: dialog.title.setText("Writing table data ...");
637:
638: // prepare the insert statement
639: int j = -1;
640: Vector row = null;
641: command = new StringBuffer();
642:
643: command.append("INSERT INTO ");
644: command.append(beanDest.getQC());
1.38 rogo 645: command.append(destTableName); //convertText((String) names.get(tbIndex)));
1.34 rogo 646: command.append(beanDest.getQC());
1.38 rogo 647:
1.34 rogo 648: command.append(" values ( ");
649:
1.38 rogo 650: // add a question marks for every field
1.34 rogo 651: for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
652: command.append("?,");
653: command.append("?)");
654: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
655: System.out.println(command);
656: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
657: Vector vec = new Vector(myIds);
658: int endIndex = -1;
659: String tempQuery = query;
660: String tempID = bean.getQC() + idField + bean.getQC();
1.38 rogo 661: // if id_field not do incremental conversion else do it all at once
1.34 rogo 662: if (!idField.equals(""))
663: {
664: long startTime = System.currentTimeMillis();
665: int counter = -1;
666: while (true)
667: {
668: ++counter;
669: if (counter == 0&&dialog!=null)
670: dialog.title.setText("Check if data is available");
671: else
672: if(dialog!=null)
673: dialog.title.setText("Check if more data is available");
674: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
675: if (myIds.isEmpty())
676: break;
677: vec = new Vector(myIds);
678: rowCount = vec.size();
679: System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIds.size());
680: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
681: if (vec.size() <= numIntervalls)
682: {
683: endIndex = 0;
684: deltaID = vec.size();
685: }
686: for (int k = 0; k < vec.size() - deltaID; k = k + deltaID)
687: {
688: System.out.println(vec.get(k) + " " + vec.get(k + deltaID) + " " + vec.lastElement());
689: if (query.indexOf("where") > 0)
1.39 rogo 690: tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID)+"'";
1.34 rogo 691: else
1.39 rogo 692: tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID)+"'";
1.34 rogo 693: System.out.println(tempQuery);
694: if(dialog!=null)
695: dialog.title.setText("Reading table data ...");
696:
697: bean.makeQuery(tempQuery, deltaID);
698: if(dialog!=null)
699: dialog.title.setText("Writing table data ...");
700:
1.43 rogo 701: command = writeDatainDestTable(dialog, command, k, pstm, rowCount,delimiter);
1.34 rogo 702: endIndex = k + deltaID;
703: }
704: System.out.println(endIndex);
1.38 rogo 705: //all data written ? if not write last chunk of data
1.34 rogo 706: if (endIndex == vec.size() - 1)
707: System.out.println("fits");
708: else
709: {
710: System.out.println(" last intervall from " + vec.get(endIndex) + " " + vec.lastElement());
1.1 rogo 711:
1.34 rogo 712: if (query.indexOf("where") > 0)
1.39 rogo 713: tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement()+"'";
1.34 rogo 714: else
1.39 rogo 715: tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement()+"'";
1.34 rogo 716: System.out.println(tempQuery);
717: if(dialog!=null)
718: dialog.title.setText("Reading table data ...");
719: bean.makeQuery(tempQuery, 0);
720: if(dialog!=null)
721: dialog.title.setText("Writing table data ...");
1.43 rogo 722: command = writeDatainDestTable(dialog, command, endIndex, pstm, rowCount,delimiter);
1.34 rogo 723: }
1.38 rogo 724: // prepare new query for next chunk
1.34 rogo 725: if (query.indexOf("where") > 0)
1.39 rogo 726: tempQuery = query + " and " + tempID + ">'" + vec.lastElement()+"'";
1.34 rogo 727: else
1.39 rogo 728: tempQuery = query + " where " + tempID + ">'" + vec.lastElement()+"'";
1.34 rogo 729:
730: }
731: long endTime = System.currentTimeMillis();
732: System.out.println("Time for incremental convert elapsed " + (endTime - startTime));
733: } else
1.29 rogo 734: {
1.38 rogo 735: // read and write all in one big chunk
1.34 rogo 736: long startTime = System.currentTimeMillis();
1.38 rogo 737:
1.34 rogo 738: bean.makeQuery(query, 0);
1.43 rogo 739: command = writeDatainDestTable(dialog, command, j, pstm, rowCount,delimiter);
1.34 rogo 740: long endTime = System.currentTimeMillis();
741: System.out.println("Time for old convert elapsed " + (endTime - startTime));
1.29 rogo 742:
743: }
1.34 rogo 744: }
745: } catch (Exception e)
746: {
747: System.out.println("Error while connecting to database " + e);
748: if (dialog != null)
749: {
750: dialog.setVisible(false);
751: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
752: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
753: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
754: java.io.PrintStream stream = new java.io.PrintStream(b);
755: stream.print(command + "\n\n");
756: e.printStackTrace(stream);
757: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
758: } else
759: {
760: e.printStackTrace();
1.33 rogo 761:
1.3 rogo 762: }
1.1 rogo 763: }
1.34 rogo 764: if (dialog != null)
765: {
766: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
767: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
768: dialog.setVisible(false);
769: }
1.27 rogo 770: }
1.38 rogo 771: /**
772: * Writes data to the destination table
773: * @param dialog progress dialog
774: * @param command
775: * @param j data index for progress bar
776: * @param pstm prepared statement
777: * @param rowCount number of datasets
778: * @return command
779: * @throws Exception
780: * @throws SQLException
781: */
1.43 rogo 782: private static StringBuffer writeDatainDestTable(FM2SQL.ProgressDialog dialog, StringBuffer command, int j, PreparedStatement pstm, int rowCount,String delimiter) throws Exception, SQLException
1.27 rogo 783: {
784: Vector row;
1.34 rogo 785: while ((row = bean.getNextRow()) != null)
786: {
787: j++;
788: // row = (Vector) result[0].get(j);
789: /* command = new StringBuffer();
790:
791: command.append("INSERT INTO ");
792: command.append(beanDest.getQC());
793: command.append(convertText((String) names.get(tbIndex)));
794: command.append(beanDest.getQC());
795: command.append(" values ( ");
796: */
797: //print rows
798: Object obj = null;
799: /* for(int k=0;k<row.size()-1;++k)
800: {
801: obj = row.get(k);
802: //System.out.println("row "+obj+" "+k);
803: if(obj!=null&&!(obj instanceof ArrayList))
804: command.append("'"+convertUml(obj.toString())+"',");
805: else if(obj!=null&& obj instanceof ArrayList)
806: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
807: else command.append("NULL,");
808: }
809: obj = row.get(row.size() - 1);
810: if (obj != null && !(obj instanceof ArrayList))
811: command.append("'"+convertUml(obj.toString())+"')");
812: else
813: if(obj!=null&& obj instanceof ArrayList)
814: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')"); //command.append(obj.toString()+")");
815: else command.append("NULL)");
816: */
817: //command.append("'"+row.get(row.size()-1)+"')");
818: //command.append(" )");
819: // for(int k=0;k<row.size();++k)
820:
821: // System.out.println();
822: // System.out.println(command+" "+j+" "+row.size()+" "+ ((Vector)result2[0].get(j)).size());
823: // System.out.println(command);
824: for (int k = 0; k < row.size(); ++k)
825: {
826: obj = row.get(k);
1.54 ! rogo 827:
! 828: if (obj instanceof ArrayList)
1.43 rogo 829: obj = formatFileMakerArray((List) obj,delimiter);
1.41 rogo 830:
1.34 rogo 831: String str = (obj == null) ? "NULL" : obj.toString();
1.54 ! rogo 832: if(obj instanceof Double)
! 833: {
! 834: pstm.setDouble(k + 1, ((Double)obj).doubleValue());
! 835: }else
! 836: if (!str.equals("NULL"))
1.34 rogo 837: pstm.setString(k + 1, str);
838: else
839: pstm.setNull(k + 1, Types.NULL);
840: }
841: pstm.execute();
842: //stm.executeUpdate(command.toString());
843: if (dialog != null)
844: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
845: // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
846: command = null;
847: } // to while loop
1.27 rogo 848: return command;
1.1 rogo 849: }
850:
1.38 rogo 851: /**
852: * removes special characters from the input string as well as .fp5
853: * @param newName String to change
854: * @return
855: */
1.34 rogo 856: public static String convertText(String newName)
1.1 rogo 857: {
858: StringBuffer alterMe = new StringBuffer(newName.trim().toLowerCase());
859: int length = alterMe.length();
860: int j = 0;
1.34 rogo 861: int index = alterMe.indexOf(".fp5");
862: if (index >= 0)
863: {
864: alterMe.delete(index, index + 4);
865: length = length - 4;
866: }
867:
1.1 rogo 868: while (j < length)
1.34 rogo 869: {
1.1 rogo 870: if (alterMe.charAt(j) == ' ')
871: {
872: alterMe.setCharAt(j, '_');
1.34 rogo 873: // if(j<length-1) j=j+1;
874: } else if (alterMe.charAt(j) == '_')
875: {
876:
877: if (alterMe.charAt(j + 1) == '_')
878: alterMe.deleteCharAt(j);
879: length = length - 1;
880: // if(j<length-1) j=j+1;
881: } else if (alterMe.charAt(j) == 'ä')
882: {
883: alterMe.setCharAt(j, 'a');
884: alterMe.insert(j + 1, "e");
885: length = length + 1;
886: if (j < length - 1)
887: j = j + 1;
888: } else if (alterMe.charAt(j) == 'ö')
889: {
890: alterMe.setCharAt(j, 'o');
891: alterMe.insert(j + 1, "e");
892: length = length + 1;
893: if (j < length - 1)
894: j = j + 1;
895: } else if (alterMe.charAt(j) == 'ü')
896: {
897: alterMe.setCharAt(j, 'u');
898: alterMe.insert(j + 1, "e");
899: length = length + 1;
900: if (j < length - 1)
901: j = j + 1;
902: } else if (alterMe.charAt(j) == 'ß')
903: {
904: alterMe.setCharAt(j, 's');
905: alterMe.insert(j + 1, "s");
906: length = length + 1;
907: if (j < length - 1)
908: j = j + 1;
909: } else if (alterMe.charAt(j) == ':')
1.1 rogo 910: {
1.34 rogo 911: if (j < length - 1)
912: {
913: if (alterMe.charAt(j + 1) == ':')
914: {
915: alterMe.setCharAt(j, '_');
916: alterMe.delete(j + 1, j + 2);
917: length = length - 1;
918:
919: }
1.1 rogo 920:
1.34 rogo 921: if (j < length - 1)
922: j = j + 1;
923: }
924: } else if (alterMe.charAt(j) == '-')
925: {
926: alterMe.setCharAt(j, '_');
1.16 rogo 927:
1.34 rogo 928: } else if (alterMe.charAt(j) == '?')
1.3 rogo 929: {
1.34 rogo 930: // changed ? to _ because of update statement
931: alterMe.setCharAt(j, '_');
932: // length = length + 1;
933: // j=j+1;
934: System.out.println(alterMe);
935: } else if (alterMe.charAt(j) == '.')
936: {
937: if (j == length - 1)
938: {
939: alterMe.delete(j, j);
940: length--;
941: } else
942: alterMe.setCharAt(j, '_');
1.3 rogo 943: }
1.34 rogo 944:
945: ++j;
1.1 rogo 946: }
947: return alterMe.toString();
948: }
1.38 rogo 949: /**
950: * Converts > and < in an entity (> or <)
951: * @param newName
952: * @return
953: */
1.4 rogo 954: public static String convertToEntities(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, "gt;");
967: length = length + 2;
968: if (j < length - 1)
969: j = j + 1;
970:
971: } else if (alterMe.charAt(j) == '<')
972: {
973: alterMe.setCharAt(j, '&');
974: alterMe.insert(j + 1, "lt;");
975: length = length + 2;
976: if (j < length - 1)
977: j = j + 1;
978:
979: }
980: ++j;
981: }
982: return alterMe.toString();
983: }
1.38 rogo 984: /**
985: * Masks the single quote character '-->\'
986: * @param newName
987: * @return
988: */
1.1 rogo 989: public static String convertUml(String newName)
1.34 rogo 990: {
991: StringBuffer alterMe = new StringBuffer(newName.trim());
992: int length = alterMe.length();
993: int j = 0;
994:
995: while (j < length)
996: {
997:
998: if (alterMe.charAt(j) == '\'')
999: {
1000: alterMe.setCharAt(j, '\\');
1001: alterMe.insert(j + 1, "'");
1002: length = length + 1;
1003: if (j < length - 1)
1004: j = j + 1;
1005: }
1006: /* else
1007: if (alterMe.charAt(j) == '"')
1008: {
1009: alterMe.setCharAt(j, '\\');
1010: alterMe.insert(j + 1, "\"");
1011: length = length + 1;
1012: if(j<length-1) j=j+1;
1013: }
1014: else
1015: if (alterMe.charAt(j) == '>')
1016: {
1017: alterMe.setCharAt(j, '\\');
1018: alterMe.insert(j + 1, ">");
1019: length = length + 1;
1020: if(j<length-1) j=j+1;
1021: }
1022: else
1023: if (alterMe.charAt(j) == '<')
1024: {
1025: alterMe.setCharAt(j, '\\');
1026: alterMe.insert(j + 1, "<");
1027: length = length + 1;
1028: if(j<length-1) j=j+1;
1029: }
1030: else
1031: if (alterMe.charAt(j) == '?')
1032: {
1033: alterMe.setCharAt(j, '\\');
1034: alterMe.insert(j + 1, "?");
1035: length = length + 1;
1036: if(j<length-1) j=j+1;
1037: }
1038: else
1039: if (alterMe.charAt(j) == '&')
1040: {
1041: alterMe.setCharAt(j, '\\');
1042: alterMe.insert(j + 1, "&");
1043: length = length + 1;
1044: if(j<length-1) j=j+1;
1045: }
1046: else
1047: if (alterMe.charAt(j) == '=')
1048: {
1049: alterMe.setCharAt(j, '\\');
1050: alterMe.insert(j + 1, "=");
1051: length = length + 1;
1052: if(j<length-1) j=j+1;
1053: }
1054: else
1055: if (alterMe.charAt(j) == ',')
1056: {
1057: alterMe.setCharAt(j, '\\');
1058: alterMe.insert(j + 1, ",");
1059: length = length + 1;
1060: if(j<length-1) j=j+1;
1061: }
1062: else
1063: if (alterMe.charAt(j) == '.')
1064: {
1065: alterMe.setCharAt(j, '\\');
1066: alterMe.insert(j + 1, ".");
1067: length = length + 1;
1068: if(j<length-1) j=j+1;
1069: }
1070: else
1071: if (alterMe.charAt(j) == '[')
1072: {
1073: alterMe.setCharAt(j, '\\');
1074: alterMe.insert(j + 1, ".");
1075: length = length + 1;
1076: if(j<length-1) j=j+1;
1077: }
1078: else
1079: if (alterMe.charAt(j) == ']')
1080: {
1081: alterMe.setCharAt(j, '\\');
1082: alterMe.insert(j + 1, ".");
1083: length = length + 1;
1084: if(j<length-1) j=j+1;
1085: }
1086: else
1087: if (alterMe.charAt(j) == '%')
1088: {
1089: alterMe.setCharAt(j, '\\');
1090: alterMe.insert(j + 1, "%");
1091: length = length + 1;
1092: if(j<length-1) j=j+1;
1093: }*/
1094: ++j;
1095: }
1096: return alterMe.toString();
1097: }
1.38 rogo 1098: /**
1099: * parses the input xml file for batch conversion
1100: * called from readXMLFile
1101: * * @param sb
1102: */
1.34 rogo 1103: public static void parseXMLConfig(StringBuffer sb)
1104: {
1105: boolean finished = false;
1106: // parse string and build document tree
1107: Xparse parser = new Xparse();
1108: parser.changeEntities = true;
1109: Node root = parser.parse(sb.toString());
1110: // printContents(root);
1111: Vector databases = new Vector();
1112: Vector tables = new Vector();
1113: Vector layouts = new Vector();
1114: Vector selects = new Vector();
1115: Vector creates = new Vector();
1116: Vector ids = new Vector();
1.42 rogo 1117: String delimiter = "|";
1.34 rogo 1118: int mode = -1;
1119:
1120: try
1.1 rogo 1121: {
1.34 rogo 1122: Node tempNode = root.find("convert/source", new int[] { 1, 1 });
1123: if (tempNode == null)
1124: throw new Error("parse error source tag missing");
1125: System.out.println(tempNode.name);
1126: int length = countNodes(tempNode);
1127: for (int i = 1; i <= length; i++)
1.1 rogo 1128: {
1.34 rogo 1129:
1.1 rogo 1130: DBBean database = new DBBean();
1.34 rogo 1131: tables = new Vector();
1132: layouts = new Vector();
1133: selects = new Vector();
1134: creates = new Vector();
1135: ids = new Vector();
1.1 rogo 1136: // parse dataBase
1.34 rogo 1137: Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
1138: Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
1139: Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
1140: Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
1141: Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
1.42 rogo 1142: Node delimiterNode =root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
1.34 rogo 1143: if (node3 == null)
1144: throw new Error("parse error database tag missing");
1145: if (node == null)
1146: throw new Error("parse error url tag missing");
1147: if (node1 == null)
1148: throw new Error("parse error user tag missing");
1149: if (node2 == null)
1150: throw new Error("parse error password tag missing");
1.42 rogo 1151: if(delimiterNode!=null) delimiter = delimiterNode.getCharacters();
1.1 rogo 1152: String url = node.getCharacters();
1153: String user = node1.getCharacters();
1154: String password = node2.getCharacters();
1155: database.setURL(url.trim());
1156: database.setUserAndPasswd(user.trim(), password.trim());
1.34 rogo 1157: System.out.println(node.name + " " + node.getCharacters());
1158: System.out.println(node1.name + " " + node1.getCharacters());
1159: System.out.println(node2.name + " " + node2.getCharacters());
1160: String modeString = "";
1161: if (nodeMode == null)
1162: modeString = "convert";
1163: else
1164: modeString = nodeMode.getCharacters();
1165: if (modeString.equals("convert"))
1166: mode = DataBase.CONVERT_MODE;
1167: else if (modeString.equals("append"))
1168: mode = DataBase.APPEND_MODE;
1169: else if (modeString.equals("update"))
1170: mode = DataBase.UPDATE_MODE;
1.48 rogo 1171: else if (modeString.equals("delete"))
1172: mode = DataBase.DELETE_MODE;
1173:
1.34 rogo 1174: // if(node3!=null)
1175: // System.out.println(node3.name);
1176:
1177: int length2 = countNodes(node3);
1178:
1179: System.out.println("number of tables " + length2);
1180:
1181: for (int j = 1; j <= length2; ++j)
1182: {
1183: Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
1184: Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
1185: Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
1186: if (node4 != null)
1187: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1188: if (node5 != null)
1189: System.out.println(node5.name + " " + node5.getCharacters());
1190: if (node6 != null)
1191: System.out.println(node6.name + " " + node6.getCharacters());
1192: if (node4 == null)
1193: throw new Error("parse error table tag missing");
1194: // if(node5==null) throw new Error("parse error select tag missing");
1195: // if(node6==null) throw new Error("parse error create tag missing");
1196: String name = (String) node4.attributes.get("name");
1197: String layout = (String) node4.attributes.get("layout");
1198: String id = (String) node4.attributes.get("id");
1199: System.out.println("id was " + id);
1200: if (name == null)
1201: throw new Error("parse error required table tag attribute name missing");
1202: if (layout == null)
1203: layout = "";
1204: if (id == null)
1205: id = "";
1206: if (name.equals(""))
1207: throw new Error("parse error table tag attribute must not be empty");
1208: tables.add(name);
1209: layouts.add(layout);
1210: ids.add(id);
1211: String query = (node5 == null) ? "" : node5.getCharacters();
1212: if (query.equals(""))
1213: System.err.println("Warning empty select tag or select tag missing !!");
1214: query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
1215: selects.add(query);
1216: if (node6 != null)
1217: creates.add(node6.getCharacters().trim());
1218: else
1219: creates.add("");
1220:
1221: }
1.42 rogo 1222: DataBase dataBase = new DataBase(database, tables, layouts, selects, creates, ids, mode);
1223: dataBase.delimiter = delimiter;
1224: databases.add(dataBase);
1.34 rogo 1225: }
1226: DBBean database = new DBBean();
1227: // parse dataBase
1228: Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
1229: Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
1230: Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
1231: String url = node.getCharacters();
1232: String user = node1.getCharacters();
1233: String password = node2.getCharacters();
1234: System.out.println(url);
1235: database.setURL(url.trim());
1236: database.setUserAndPasswd(user.trim(), password.trim());
1237: //databases.add(database);
1238: for (Iterator iter = databases.iterator(); iter.hasNext();)
1.1 rogo 1239: {
1240: DataBase db = (DataBase) iter.next();
1.34 rogo 1241: if (mode != DataBase.UPDATE_MODE)
1.42 rogo 1242: convertBatch(db.bean, database, db.tables, db.layouts, db.selects, db.creates, db.ids,mode,db.delimiter);
1.17 rogo 1243: else
1.34 rogo 1244: update(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids, mode);
1.17 rogo 1245:
1.1 rogo 1246: }
1.34 rogo 1247: // printContents(node3);
1248: // FM2SQL.fmInstance=new FM2SQL();
1249: } catch (Exception e)
1250: {
1.35 rogo 1251:
1.34 rogo 1252: e.printStackTrace();
1253: }
1254: }
1255: public static Vector getXMLConfig(String xmlFile)
1256: {
1257: StringBuffer sb = null;
1258: try
1259: {
1260: // read XML Metadata from a file
1261: FileInputStream fi = new FileInputStream(xmlFile);
1262: InputStreamReader isr = new InputStreamReader(fi, "UTF-8");
1263: BufferedReader buffr = new BufferedReader(isr);
1264: sb = new StringBuffer();
1265: int c = 0;
1266: while ((c = buffr.read()) != -1)
1267: {
1268: char ch = (char) c;
1269: sb.append(ch);
1270: // System.out.print((char)c);
1.1 rogo 1271: }
1272:
1.34 rogo 1273: } catch (Exception e)
1274: {
1275: e.printStackTrace();
1276: }
1277:
1278: boolean finished = false;
1279: // parse string and build document tree
1280: Xparse parser = new Xparse();
1281: parser.changeEntities = true;
1282: Node root = parser.parse(sb.toString());
1283: // printContents(root);
1284: Vector databases = new Vector();
1285: Vector tables = new Vector();
1286: Vector layouts = new Vector();
1287: Vector selects = new Vector();
1288: Vector creates = new Vector();
1289: Vector ids = new Vector();
1.42 rogo 1290: String delimiter = "|";
1.34 rogo 1291: int mode = -1;
1292: try
1.1 rogo 1293: {
1.34 rogo 1294: Node tempNode = root.find("convert/source", new int[] { 1, 1 });
1295: if (tempNode == null)
1296: throw new Error("parse error source tag missing");
1297: System.out.println(tempNode.name);
1298: int length = countNodes(tempNode);
1299: for (int i = 1; i <= length; i++)
1.1 rogo 1300: {
1.34 rogo 1301:
1302: DBBean database = new DBBean();
1303: tables = new Vector();
1304: layouts = new Vector();
1305: selects = new Vector();
1306: creates = new Vector();
1307: ids = new Vector();
1308: // parse dataBase
1309: Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
1310: Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
1311: Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
1312: Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
1313: Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
1.42 rogo 1314: Node delimiterNode =root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
1315:
1.44 rogo 1316: if (delimiterNode != null)
1317: delimiter = delimiterNode.getCharacters();
1.34 rogo 1318: if (node3 == null)
1319: throw new Error("parse error database tag missing");
1320: if (node == null)
1321: throw new Error("parse error url tag missing");
1322: if (node1 == null)
1323: throw new Error("parse error user tag missing");
1324: if (node2 == null)
1325: throw new Error("parse error password tag missing");
1326: String url = node.getCharacters();
1327: String user = node1.getCharacters();
1328: String password = node2.getCharacters();
1329: database.setURL(url.trim());
1330: database.setUserAndPasswd(user.trim(), password.trim());
1331: System.out.println(node.name + " " + node.getCharacters());
1332: System.out.println(node1.name + " " + node1.getCharacters());
1333: System.out.println(node2.name + " " + node2.getCharacters());
1334: String modeString = "";
1335: if (nodeMode == null)
1336: modeString = "convert";
1337: else
1338: modeString = nodeMode.getCharacters();
1339: if (modeString.equals("convert"))
1340: mode = DataBase.CONVERT_MODE;
1341: else if (modeString.equals("append"))
1342: mode = DataBase.APPEND_MODE;
1343: else if (modeString.equals("update"))
1344: mode = DataBase.UPDATE_MODE;
1.48 rogo 1345: else if (modeString.equals("delete"))
1346: mode = DataBase.DELETE_MODE;
1347:
1.34 rogo 1348: // if(node3!=null)
1349: // System.out.println(node3.name);
1350:
1351: int length2 = countNodes(node3);
1352:
1353: System.out.println("number of tables " + length2);
1354:
1355: for (int j = 1; j <= length2; ++j)
1356: {
1357: Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
1358: Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
1359: Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
1360: if (node4 != null)
1361: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1362: if (node5 != null)
1363: System.out.println(node5.name + " " + node5.getCharacters());
1364: if (node6 != null)
1365: System.out.println(node6.name + " " + node6.getCharacters());
1366: if (node4 == null)
1367: throw new Error("parse error table tag missing");
1368: // if(node5==null) throw new Error("parse error select tag missing");
1369: // if(node6==null) throw new Error("parse error create tag missing");
1370: String name = (String) node4.attributes.get("name");
1371: String layout = (String) node4.attributes.get("layout");
1372: String id = (String) node4.attributes.get("id");
1373: System.out.println("id was " + id);
1374:
1375: if (name == null)
1376: throw new Error("parse error required table tag attribute name missing");
1377: if (layout == null)
1378: layout = "";
1379: if (id == null)
1380: id = "";
1381: if (name.equals(""))
1382: throw new Error("parse error table tag attribute must not be empty");
1383: tables.add(name);
1384: layouts.add(layout);
1385: ids.add(id);
1386: String query = (node5 == null) ? "" : node5.getCharacters();
1387: if (query.equals(""))
1388: System.err.println("Warning empty select tag or select tag missing !!");
1389: query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
1390: selects.add(query);
1391: if (node6 != null)
1392: creates.add(node6.getCharacters().trim());
1393: else
1394: creates.add("");
1395:
1396: }
1.43 rogo 1397: DataBase dataBase=new DataBase(database, tables, layouts, selects, creates, ids, mode);
1398: dataBase.delimiter=delimiter;
1399: databases.add(dataBase);
1.1 rogo 1400: }
1.34 rogo 1401: DBBean database = new DBBean();
1402: // parse dataBase
1403: Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
1404: Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
1405: Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
1406: String url = node.getCharacters();
1407: String user = node1.getCharacters();
1408: String password = node2.getCharacters();
1409: System.out.println(url);
1410: database.setURL(url.trim());
1411: database.setUserAndPasswd(user.trim(), password.trim());
1412: databases.add(new DataBase(database, null, null, null, null, null, 0));
1413: //databases.add(database);
1414: /* for (Iterator iter = databases.iterator(); iter.hasNext();)
1415: {
1416: DataBase db = (DataBase) iter.next();
1417: convertBatch(db.bean,database,db.tables,db.layouts,db.selects,db.creates);
1418:
1419: }*/
1420: // printContents(node3);
1421: // FM2SQL.fmInstance=new FM2SQL();
1422: } catch (Exception e)
1423: {
1424: // TODO Auto-generated catch block
1425: e.printStackTrace();
1.1 rogo 1426: }
1.34 rogo 1427: return databases;
1428: }
1429:
1430: private static int countNodes(Node tempNode)
1431: {
1432: int length = 0;
1433: for (int i = 0; i < tempNode.contents.v.size(); ++i)
1.1 rogo 1434: {
1.34 rogo 1435: Node node = (Node) tempNode.contents.v.elementAt(i);
1436: if (node.type.equals("element"))
1.1 rogo 1437: {
1.34 rogo 1438: if (node.name.equals("database"))
1439: length++;
1440: if (node.name.equals("table"))
1441: length++;
1.1 rogo 1442: }
1.34 rogo 1443:
1444: // System.out.println(((Node)tempNode.contents.v.elementAt(i)).attributes+" "+i);
1.1 rogo 1445: }
1.34 rogo 1446: return length;
1447: }
1448: private static void printContents(Node root)
1449: {
1450:
1451: Vector contents = (root.index == null) ? root.contents.v : root.index.v;
1452: for (int i = 0; i < contents.size(); ++i)
1.1 rogo 1453: {
1.34 rogo 1454: Node n = (Node) contents.elementAt(i);
1455: if (n.type.equals("element"))
1456: {
1457: System.out.println("tag " + n.name);
1458: System.out.println(n.getCharacters());
1459: //contents=n.contents.v i=0;
1460: }
1461: // System.out.println(n.type);
1.1 rogo 1462: }
1.34 rogo 1463: }
1.38 rogo 1464: /**
1465: * reads the specified xml file
1466: * @param xmlFile
1467: */
1.34 rogo 1468: public static void readXMLFile(String xmlFile)
1.1 rogo 1469: {
1.34 rogo 1470: try
1471: {
1472: // read XML Metadata from a file
1473: FileInputStream fi = new FileInputStream(xmlFile);
1474: InputStreamReader isr = new InputStreamReader(fi, "UTF-8");
1475: BufferedReader buffr = new BufferedReader(isr);
1476: StringBuffer sb = new StringBuffer();
1477: int c = 0;
1478: while ((c = buffr.read()) != -1)
1479: {
1480: char ch = (char) c;
1481: sb.append(ch);
1482: // System.out.print((char)c);
1483: }
1484: parseXMLConfig(sb);
1485: } catch (Exception e)
1486: {
1487: e.printStackTrace();
1488: }
1.1 rogo 1489: }
1.38 rogo 1490:
1491: /**
1492: * Helper class for XML-File parsing
1493: * Holds the parsed data
1494: * @author rogo
1495: *
1496: */
1.34 rogo 1497: public static class DataBase
1498: {
1499: DBBean bean;
1500: Vector creates;
1501: Vector selects;
1502: Vector layouts;
1503: Vector tables;
1504: Vector ids;
1.43 rogo 1505: String delimiter = "//";
1.34 rogo 1506: final static int CONVERT_MODE = 1;
1507: final static int APPEND_MODE = 2;
1508: final static int UPDATE_MODE = 3;
1.40 rogo 1509: final static int DELETE_MODE = 4;
1510:
1.34 rogo 1511: int mode = -1;
1512:
1513: public DataBase(DBBean bean, Vector tables, Vector layouts, Vector selects, Vector creates, Vector ids, int mode)
1514: {
1515: this.bean = bean;
1516: this.tables = tables;
1517: this.layouts = layouts;
1518: this.selects = selects;
1519: this.creates = creates;
1520: this.ids = ids;
1521: this.mode = mode;
1522: this.bean.setIDVector(ids);
1523: }
1.38 rogo 1524: /**
1.42 rogo 1525: * writes the data contained in this object to the buffered writer
1.38 rogo 1526: * * @param buffr
1527: * @throws Exception
1528: */
1.34 rogo 1529: public void exportToXML(BufferedWriter buffr) throws Exception
1530: {
1531: // ids=bean.getIDVector();
1532: buffr.write(" <database>\n");
1533: buffr.write(" <url>" + bean.url + "</url>\n");
1534: buffr.write(" <user>" + bean.user + "</user>\n");
1535: buffr.write(" <password>" + bean.passwd + "</password>\n");
1.42 rogo 1536: buffr.write(" <delimiter>"+delimiter+"</delimiter>\n");
1.34 rogo 1537: String modeString = "";
1538: if (mode == CONVERT_MODE)
1539: modeString = "convert";
1540: else if (mode == APPEND_MODE)
1.5 rogo 1541: modeString = "append";
1.34 rogo 1542: else if (mode == UPDATE_MODE)
1543: modeString = "update";
1.48 rogo 1544: else if (mode == DELETE_MODE)
1545: modeString = "delete";
1.34 rogo 1546:
1547: buffr.write(" <mode>" + modeString + "</mode>\n");
1548: int index = 0;
1549: while (index < tables.size())
1550: {
1551: String table = (String) tables.get(index);
1552: String layout = (String) layouts.get(index);
1553: String select = (String) selects.get(index);
1554: String create = (String) creates.get(index);
1555: String id = (String) ids.get(index);
1556:
1557: buffr.write(" <table name = \"" + table + "\" layout = \"" + layout + "\" id = \"" + id + "\" >\n");
1558: buffr.write(" <select>" + convertToEntities(select) + "</select>\n");
1559: if (!create.equals(""))
1560: buffr.write(" <create>" + create + " </create>\n");
1561: buffr.write(" </table>\n");
1562: index++;
1563: }
1564: buffr.write(" </database>\n");
1565: }
1566: public String toString()
1567: {
1568: return bean.url + " " + tables;
1569: }
1.1 rogo 1570:
1.34 rogo 1571: }
1572: public static String convertToUTF8(Object command)
1.1 rogo 1573: {
1.34 rogo 1574: String str = null;
1575: try
1576: {
1577: str = new String(command.toString().getBytes("UTF-8"));
1578: } catch (UnsupportedEncodingException e)
1579: {
1580: // TODO Auto-generated catch block
1581: e.printStackTrace();
1582: }
1583: return str;
1.1 rogo 1584: }
1585: public static void writeConfig(String file, DataBase source, DataBase destination) throws Exception
1586: {
1.34 rogo 1587: if (!file.toLowerCase().endsWith(".xml"))
1588: file += ".xml";
1.1 rogo 1589: File f = new File(file);
1.34 rogo 1590:
1591: FileOutputStream fout = new FileOutputStream(f);
1592: OutputStreamWriter outsw = new OutputStreamWriter(fout, "UTF-8");
1.1 rogo 1593: BufferedWriter buffw = new BufferedWriter(outsw);
1594: buffw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
1595: buffw.newLine();
1596: buffw.write("<convert>\n");
1597: buffw.write(" <source>\n");
1598: source.exportToXML(buffw);
1599: buffw.write(" </source>\n");
1600: buffw.write("\n <destination>\n");
1601: destination.exportToXML(buffw);
1602: buffw.write(" </destination>\n");
1603: buffw.write("</convert>\n");
1604: buffw.close();
1.34 rogo 1605: }
1.46 rogo 1606: public static void delete(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
1607: {
1608: FM2SQL.ProgressDialog dialog = null;
1609: if (FM2SQL.fmInstance != null)
1610: {
1.53 rogo 1611: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance,bean);
1.46 rogo 1612: dialog.setTitle("Conversion running ...");
1613: dialog.title.setText("Getting table data ...");
1614: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
1615: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1616: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1617: dialog.thread = Thread.currentThread();
1618: }
1619: // setting user and passwd
1620: bean.setUserAndPasswd(user, passwd);
1621: // setting user and passwd
1622: beanDest.setUserAndPasswd(userDest, passwdDest);
1623: if (dialog != null)
1624: dialog.setSize(400, 250);
1625: StringBuffer command = null;
1626: String query = null;
1627: try
1628: {
1629: //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
1630: //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
1631: bean.setConnection(source);
1632: if (names == null)
1633: names = bean.getTableNames();
1634: // Collections.sort(names);
1635: int tbIndex = 1;
1636:
1637: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
1638: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1639: {
1640: Vector[] result = null;
1641: java.util.TreeSet myIds = new TreeSet();
1642: java.util.TreeSet myIdsDest = new TreeSet();
1643: int deltaID = 1;
1644: String idField = "";
1645: String destTableName = "";
1646:
1647: try
1648: {
1649: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
1650: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
1651: query = (selects != null) ? selects.get(tbIndex).toString() : query;
1652: //if vectors[1].get(i) != null)
1653: if (layout != "")
1654: {
1655: layout = " layout " + bean.getQC() + layout + bean.getQC();
1656: String name = names.get(tbIndex).toString();
1657: StringBuffer queryLayout = new StringBuffer(query);
1658: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
1659: query = queryLayout.toString();
1660: System.out.println("added layout " + query);
1661:
1662: }
1663: dialog.title.setText("Getting table data ...");
1664: dialog.table.setText(names.get(tbIndex).toString());
1665: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
1666: dialog.show();
1667: bean.getConnection();
1668: bean.makeQuery(query, 50);
1669: idField = ids.get(tbIndex).toString();
1670:
1671: } catch (Exception e)
1672: {
1673: continue;
1674: }
1675: // determine destTableName from createStatement or from source table name
1676: if (!creates.get(tbIndex).equals(""))
1677: {
1678: String create = creates.get(tbIndex).toString().toLowerCase();
1679: int fromIndex = create.indexOf("table") + 5;
1680: int toIndex = create.indexOf("(");
1681: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
1682: System.out.println("destTable " + destTableName);
1683:
1684: } else
1685: destTableName = convertText(names.get(tbIndex).toString());
1686:
1687: // for id kram
1688: Vector vec = null;
1689: Vector vecDest = null;
1.47 rogo 1690: // tempo
1691: beanDest.setConnection(destination);
1.46 rogo 1692: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
1693: String tempID = bean.getQC() + idField + bean.getQC();
1694: String tempIDdest = beanDest.getQC() + convertText(idField) + beanDest.getQC();
1.47 rogo 1695:
1.46 rogo 1696: int endIndex = -1;
1697: String tempQuery = query;
1.47 rogo 1698: String destQuery = query.replaceAll(names.get(tbIndex).toString(), destTableName);
1.49 rogo 1699: String tempQueryDest = destQuery;
1700: // remove extra query parts destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
1.47 rogo 1701: System.out.println("new Query " + tempQueryDest);
1.46 rogo 1702: if (!idField.equals(""))
1703: {
1704: long startTime = System.currentTimeMillis();
1705: int counter = -1;
1706: while (true)
1707: {
1708: ++counter;
1709: if (counter == 0 && dialog != null)
1710: dialog.title.setText("Check if data is available");
1711: else if (dialog != null)
1712: dialog.title.setText("Check if more data is available");
1713: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
1714: myIdsDest = beanDest.getIDVector(convertText(idField), destTableName, tempQueryDest, numHits);
1715: if (myIds.isEmpty())
1716: break;
1717: vec = new Vector(myIds);
1718: vecDest = new Vector(myIdsDest);
1719: rowCount = vec.size();
1.47 rogo 1720: // Deletion will work this way
1721: Vector deleted = new Vector(vec);
1722: Vector linesToDelete = new Vector(vecDest);
1723: // remove all lines that should not be deleted
1724: linesToDelete.removeAll(deleted);
1725: // System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIdsDest.size());
1.46 rogo 1726: /// @TODO complete delete task remove query show lines to be deleted let user choose if he wants that
1.47 rogo 1727: System.out.println("number of lines to be deleted " + linesToDelete.size());
1.46 rogo 1728: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
1.47 rogo 1729: beanDest.setConnection(destination);
1.46 rogo 1730:
1.47 rogo 1731: Statement stm = beanDest.getConnection().createStatement();
1.46 rogo 1732:
1.47 rogo 1733: Vector tables = beanDest.getTableNames();
1734: // Collections.sort(tables);
1735: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
1736: tables = beanDest.getTableNames();
1737: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
1738: stm = beanDest.getConnection().createStatement();
1739:
1740: if (dialog != null)
1741: dialog.title.setText(" Deleting table data ...");
1742:
1743: int j = -1;
1744:
1745: Vector row = null;
1746: command = new StringBuffer();
1747:
1748: command.append("DELETE FROM");
1749: command.append(beanDest.getQC());
1750: command.append(destTableName);
1751: //command.append(convertText((String) names.get(tbIndex)));
1752: command.append(beanDest.getQC());
1753: int size = bean.getColumnNames().size();
1754: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
1755: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
1756: System.out.println(command + " " + tbIndex);
1757: //int rowCount = bean.getRowCount(query);
1758: // int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
1759: while (true)
1.46 rogo 1760: {
1761:
1.48 rogo 1762: ++j;
1.47 rogo 1763: if (j == linesToDelete.size())
1764: break;
1765: //print rows
1766: pstm.setString(1, linesToDelete.get(j).toString());
1767: System.out.println(pstm.toString());
1768: pstm.execute();
1.46 rogo 1769: if (dialog != null)
1.47 rogo 1770: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
1771: command = null;
1.46 rogo 1772: }
1773: // prepare new query for next chunk
1774: if (query.indexOf("where") > 0)
1775: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
1776: else
1777: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
1778:
1.47 rogo 1779: } //to outer while
1780: } // to idfield if
1781: } // table loop
1782:
1783: } catch (Exception e)
1784: {
1785: System.out.println("Error while connecting to database " + e);
1.46 rogo 1786: if (dialog != null)
1787: {
1.47 rogo 1788: dialog.setVisible(false);
1789: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1790: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1791: }
1792: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
1793: java.io.PrintStream stream = new java.io.PrintStream(b);
1794: stream.print(command + "\n\n");
1795: e.printStackTrace(stream);
1796: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
1.46 rogo 1797:
1798: }
1.47 rogo 1799: if (dialog != null)
1800: {
1801: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1802: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1.46 rogo 1803:
1.47 rogo 1804: dialog.setVisible(false);
1805: }
1806: } // to method
1.46 rogo 1807:
1808: }
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