Return to Convert.java CVS log

Up to [Repository] / FM2SQL

some changes in cancel of progress dialog (closes connection)

/*
 * Convert.java -- Converter class -  Filemaker to SQL Converter 
 * Copyright (C) 2003 Robert Gordesch (rogo@mpiwg-berlin.mpg.de) 
 * This program is free software; you can redistribute it and/or modify it
 * under the terms of the GNU General Public License as published by the Free
 * Software Foundation; either version 2 of the License, or (at your option)
 * any later version.  Please read license.txt for the full details. A copy of
 * the GPL may be found at http://www.gnu.org/copyleft/lgpl.html  You should
 * have received a copy of the GNU General Public License along with this
 * program; if not, write to the Free Software Foundation, Inc., 59 Temple
 * Place, Suite 330, Boston, MA 02111-1307 USA  Created on 15.09.2003 by
 * rogo  
 */

import java.util.*;
import java.sql.*;
import java.awt.Cursor;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintStream;
import java.io.UnsupportedEncodingException;

import com.exploringxml.xml.Node;
import com.exploringxml.xml.Xparse;

class Convert
{
  static DBBean bean = new DBBean();
  static DBBean beanDest = new DBBean();

  static String user = "", passwd = "e1nste1n";
  static String userDest = "postgres", passwdDest = "rogo";
  static boolean batchRun = false;
  static Vector databases = new Vector();
  final static int numHits = 5000;
  final static int numIntervalls = 2;
  public static void main(String args[])
  {
    /*    try
        {
          //byte[] b = "ö".getBytes("UTF-8");
        //  System.out.println("QueryString " +b[0]+" "+b[1]+(new String(b).getBytes()[0])+" "+new String(b).getBytes()[1]);
        //System.out.println(new String(b,"UTF-8"));
        } catch (UnsupportedEncodingException e)
        {
          e.printStackTrace();
        }*/
    FileOutputStream file = null;
    if (args.length != 1)
    {
      System.out.println("Usage: java Convert <xml config file>");
      System.exit(-1);
    }
    if (!(new File(args[0]).exists()))
      System.exit(0);
    try
    {
      file = new FileOutputStream("./log.txt");
    } catch (FileNotFoundException e1)
    {
      e1.printStackTrace();
    }
    PrintStream stream = new PrintStream(file);
    System.setOut(stream);
    System.setErr(stream);
    readXMLFile(args[0]);
    System.out.println("Finished!");
    //convert("jdbc:fmpro:http://141.14.237.74:8050","jdbc:postgresql://erebos/test",null,null);
  }
  public static void convertBatch(DBBean source, DBBean destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids,int mode,String delimiter) throws Exception
  {
    bean = source;
    beanDest = destination;
    convert(null,null,names,layouts,selects,creates,ids,mode,delimiter);
    if(true) return;
    StringBuffer command = null;
    try
    {
      bean.setConnection(source.url);
      if (names == null)
        names = bean.getTableNames();
      //Collections.sort(names);
      int tbIndex = 1;

      for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
      {
        Vector[] result = null;
        try
        {
          String query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
          String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
          query = (selects != null) ? selects.get(tbIndex).toString() : query;
          //if  vectors[1].get(i) != null)
          if (!layout.equals(""))
          {
            System.out.println("before " + query + " table" + names.get(tbIndex));
            layout = " layout " + bean.getQC() + layout + bean.getQC();
            String name = names.get(tbIndex).toString();
            StringBuffer queryLayout = new StringBuffer(query);
            queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
            query = queryLayout.toString();
            System.out.println("added layout " + query);

          }
          System.out.println(" performing query " + query);
          //result = bean.getQueryData(query, null, 0);
          bean.getConnection();
          bean.makeQuery(query, 0);
        } catch (Exception e)
        {
          System.out.println(e.getMessage());
          e.printStackTrace();
          continue;
        }
        //beanDest.setConnection("jdbc:postgresql://erebos/test3");
        beanDest.setConnection(destination.url);

        Statement stm = beanDest.getConnection().createStatement();

        Vector tables = beanDest.getTableNames();
        //   Collections.sort(tables);
        System.out.println("converting table " + names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames()); 
        tables = beanDest.getTableNames();
        // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
        stm = beanDest.getConnection().createStatement();
        // System.exit(0);
        if (mode == Convert.DataBase.CONVERT_MODE)
        {
          if (tables.indexOf(names.get(tbIndex)) >= 0)
          {
            stm.executeUpdate("drop table " + beanDest.getQC() + names.get(tbIndex) + beanDest.getQC());
            tables.remove((String) names.get(tbIndex));
            System.out.println("dropped table " + names.get(tbIndex));
          } else if (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0)
          {
            stm.executeUpdate("drop table " + beanDest.getQC() + convertText((String) names.get(tbIndex)) + beanDest.getQC());
            tables.remove(convertText((String) names.get(tbIndex)));
            System.out.println("dropped table " + names.get(tbIndex));
          }

          if (tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0)
          {
            if (creates.get(tbIndex).equals("") || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
            {
              System.out.println("Warning empty or invalid create statement - creating one for you\n");

              command = new StringBuffer(50);
              command.append("CREATE TABLE ");
              command.append(beanDest.getQC());
              command.append(convertText((String) names.get(tbIndex)));
              command.append(beanDest.getQC());
              command.append("(");
              String type = null;
              Vector columnNames = bean.getColumnNames();
              for (int i = 0; i < columnNames.size() - 1; ++i)
              {
                type = bean.metaData.getColumnTypeName(i + 1);
                //   System.out.println(i+" "+result[1].get(i)+" "+type);
                type = (type.equals("NUMBER")) ? "INT4" : type;
                type = (type.equals("CONTAINER")) ? "TEXT" : type;

                command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
              }
              type = bean.metaData.getColumnTypeName(columnNames.size());
              type = (type.equals("NUMBER")) ? "INT4" : type;
              type = (type.equals("CONTAINER")) ? "TEXT" : type;
              command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
              command.append(" )");
            } else
              command = new StringBuffer().append(creates.get(tbIndex).toString());

            System.out.println(command);
            //  System.exit(0);
            //command.append(DBBean.getQC());   
            stm.executeUpdate(command.toString());

          }
        }
        Vector row = null;
        command = new StringBuffer();

        command.append("INSERT  INTO ");
        command.append(beanDest.getQC());
        command.append(convertText((String) names.get(tbIndex)));
        command.append(beanDest.getQC());
        command.append(" values ( ");

        for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
          command.append("?,");
        command.append("?)");
        PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
        System.out.println(command);
        while ((row = bean.getNextRow()) != null)
        {
          //print rows
          Object obj = null;
          for (int k = 0; k < row.size(); ++k)
          {
            obj = row.get(k);
            if (obj instanceof ArrayList)
              obj = formatFileMakerArray((List) obj,"\n");
            String str = (obj == null) ? "NULL" : obj.toString();
            if (!str.equals("NULL"))
              pstm.setString(k + 1, str);
            else
              pstm.setNull(k + 1, Types.NULL);
          }
          pstm.execute();

        } // to for loop    

      }
    } catch (Exception e)
    {
      System.out.println("Error while connecting to database " + e);
      //dialog.setVisible(false);
      //dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
      //FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
      java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
      java.io.PrintStream stream = new java.io.PrintStream(b);
      stream.print(command + "\n\n");
      e.printStackTrace(stream);
      System.err.println(b);
      //FM2SQL.showErrorDialog(b.toString(), "Error occured !");

    }
    //  dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
    //FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));

    //  dialog.setVisible(false); 
  }
	public static String formatFileMakerArray(List list, String delimiter)
	{ 
	  StringBuffer formattedString = new StringBuffer();
	  for(int i=0;i<list.size();++i)
	  {
		  formattedString.append(list.get(i).toString());
			if(i<list.size()-1)
			 formattedString.append(delimiter);
	  } 
	  return formattedString.toString();
	}
  /**
   * Method for SQL UPDATE
   * @param source
   * @param destination
   * @param names
   * @param layouts
   * @param selects
   * @param creates
   * @param ids
   * @param mode
   * @throws Exception
   */
  public static void update(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
  {
    FM2SQL.ProgressDialog dialog = null;
    if (FM2SQL.fmInstance != null)
    {
      dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance,bean);
      dialog.setTitle("Conversion running ...");
      dialog.title.setText("Getting table data ...");
      dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
      dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
      FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
      dialog.thread = Thread.currentThread();
    }
    // setting user and passwd 
    bean.setUserAndPasswd(user, passwd);
    // setting user and passwd 
    beanDest.setUserAndPasswd(userDest, passwdDest);
    if (dialog != null)
      dialog.setSize(400, 250);
    StringBuffer command = null;
    String query = null;
    try
    {
      //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");    
      //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
      bean.setConnection(source);
      if (names == null)
        names = bean.getTableNames();
      // Collections.sort(names);
      int tbIndex = 1;
      
      // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
      for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
      {
        Vector[] result = null;
        String destTableName = "";
        try
        {
          query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
          String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
          query = (selects != null) ? selects.get(tbIndex).toString() : query;
          //if  vectors[1].get(i) != null)
          if (layout != "")
          {
            layout = " layout " + bean.getQC() + layout + bean.getQC();
            String name = names.get(tbIndex).toString();
            StringBuffer queryLayout = new StringBuffer(query);
            queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
            query = queryLayout.toString();
            System.out.println("added layout  " + query);

          }
          dialog.title.setText("Getting table data ...");
          dialog.table.setText(names.get(tbIndex).toString());
          dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
          dialog.show();
          bean.getConnection();
          bean.makeQuery(query, 0);
        } catch (Exception e)
        {
          continue;
        }
        // determine destTableName from createStatement or from source table name
          if(!creates.get(tbIndex).equals(""))
          {
            String create =creates.get(tbIndex).toString().toLowerCase();
            int fromIndex = create.indexOf("table")+5;
            int toIndex   = create.indexOf("(");
            destTableName = create.substring(fromIndex,toIndex).replaceAll(beanDest.getQC(),"").trim();
            System.out.println("destTable "+destTableName);
            
          } else
            destTableName = convertText(names.get(tbIndex).toString());

        //beanDest.setConnection("jdbc:postgresql://erebos/test3");
        beanDest.setConnection(destination);

        Statement stm = beanDest.getConnection().createStatement();

        Vector tables = beanDest.getTableNames();
        // Collections.sort(tables);
        System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames()); 
        tables = beanDest.getTableNames();
        // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
        stm = beanDest.getConnection().createStatement();
        // System.exit(0);

        if (dialog != null)
          dialog.title.setText("Updating table data ...");

        int j = -1;

        Vector row = null;
        command = new StringBuffer();

        command.append("UPDATE ");
        command.append(beanDest.getQC());
        command.append(destTableName);
        //command.append(convertText((String) names.get(tbIndex)));
        command.append(beanDest.getQC());
        command.append(" SET  ");

        int size = bean.getColumnNames().size();
        for (int i = 0; i < size - 1; ++i)
          command.append(beanDest.getQC() + convertText((String) bean.getColumnNames().get(i)) + beanDest.getQC() + " = ? ,");
        command.append(convertText((String) bean.getColumnNames().get(size - 1)) + " = ? ");
        command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " =  ?");
        PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
        System.out.println(command + " " + tbIndex);
        int rowCount = bean.getRowCount(query);
        int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
        while ((row = bean.getNextRow()) != null)
        {
          j++;
          //print rows
          Object obj = null;
          /* for(int k=0;k<row.size()-1;++k)
           {
          		obj = row.get(k);
          		//System.out.println("row "+obj+" "+k);
          	 if(obj!=null&&!(obj instanceof ArrayList))
          	 command.append("'"+convertUml(obj.toString())+"',"); 
          	 else if(obj!=null&&   obj instanceof ArrayList)
          	 command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',"); 
          	 else command.append("NULL,");
           }
           obj = row.get(row.size() - 1);
           if (obj != null && !(obj instanceof ArrayList))
           command.append("'"+convertUml(obj.toString())+"')"); 
          	else
          	if(obj!=null&&   obj instanceof ArrayList)
          	 command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')");         //command.append(obj.toString()+")");
          	 else command.append("NULL)");
          	*/
          //command.append("'"+row.get(row.size()-1)+"')"); 
          //command.append(" )");
          //  for(int k=0;k<row.size();++k)

          // System.out.println();
          //   System.out.println(command+" "+j+" "+row.size()+" "+  ((Vector)result2[0].get(j)).size());
          // System.out.println(command);
          for (int k = 0; k < row.size(); ++k)
          {
            obj = row.get(k);
            if (obj instanceof ArrayList)
              obj = ((List) obj).get(0);
            String str = (obj == null) ? "NULL" : obj.toString();
            if (!str.equals("NULL"))
              pstm.setString(k + 1, str);
            else
              pstm.setNull(k + 1, Types.NULL);
          }
          pstm.setString(row.size() + 1, row.get(idIndex).toString());
          //System.out.println(pstm.toString());
          // System.exit(0);
          pstm.execute();
          //stm.executeUpdate(command.toString());
          if (dialog != null)
            dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
          // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
          command = null;
        } // to for loop    

      }
    } catch (Exception e)
    {
      System.out.println("Error while connecting to database " + e);
      if (dialog != null)
      {
        dialog.setVisible(false);
        dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
        FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
      }
      java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
      java.io.PrintStream stream = new java.io.PrintStream(b);
      stream.print(command + "\n\n");
      e.printStackTrace(stream);
      FM2SQL.showErrorDialog(b.toString(), "Error occured !");

    }
    if (dialog != null)
    {
      dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
      FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));

      dialog.setVisible(false);
    }

  }
  /**
   *   transfers the specified array of tables  to the destination database
      and creates the table if it does not exist if it exists and mode is not append the table is dropped
 
   * @param source
   * @param destination
   * @param names
   * @param layouts
   * @param selects
   * @param creates
   * @param ids
   * @param mode
   * @throws Exception
   */ 
  
     
  public static void convert(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode,String delimiter) throws Exception
  {

    FM2SQL.ProgressDialog dialog = null;

    if (FM2SQL.fmInstance != null)
    {
      dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance,bean);
      dialog.setTitle("Conversion running ...");
      dialog.title.setText("Getting table data ...");
      dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
      dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
      FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
      dialog.thread = Thread.currentThread();
      dialog.setSize(400, 250);
    }
    java.util.TreeSet myIds = new TreeSet();
    int deltaID = 1;
    String idField = "";
    String destTableName = "";
    if (source != null && destination != null)
    {
      // setting user and passwd 
      bean.setUserAndPasswd(user, passwd);
      // setting user and passwd 
      beanDest.setUserAndPasswd(userDest, passwdDest);
    }
    StringBuffer command = null;
    String query = null;
    try
    {
      if(source!=null)
      bean.setConnection(source);
      else
      bean.setConnection(bean.url);
      
      if (names == null)
        names = bean.getTableNames();
      // Collections.sort(names);
      int tbIndex = 1;

      // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
      for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
      {
        Vector[] result = null;
        try
        {
          query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
          String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
          query = (selects != null) ? selects.get(tbIndex).toString() : query;
          //if  vectors[1].get(i) != null)
          if (layout != "")
          {
            layout = " layout " + bean.getQC() + layout + bean.getQC();
            String name = names.get(tbIndex).toString();
            StringBuffer queryLayout = new StringBuffer(query);
            queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
            query = queryLayout.toString();
            System.out.println("added layout  " + query);

          }
          //  if ( layout!= "")
          //	 query += " layout " + bean.getQC() + layout + bean.getQC();
          if (dialog != null)
          {
            dialog.title.setText("Reading table data ...");
            dialog.table.setText(names.get(tbIndex).toString());
            dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
            dialog.show();
          }
          //result = bean.getQueryData(query, dialog, 0);
          bean.getConnection();
          bean.makeQuery(query, 50);
          idField = ids.get(tbIndex).toString();

        } catch (Exception e)
        {
          System.out.println(e);
          continue;
        }
        if(destination!=null)
        beanDest.setConnection(destination);
        else
        beanDest.setConnection(beanDest.url);
        Statement stm = beanDest.getConnection().createStatement();

        Vector tables = beanDest.getTableNames();
        // Collections.sort(tables);
        System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames()); 
        tables = beanDest.getTableNames();
        // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
        stm = beanDest.getConnection().createStatement();
        // System.exit(0);
      
      // determine destTableName from createStatement or from source table name
        if(!creates.get(tbIndex).equals(""))
        {
          String create =creates.get(tbIndex).toString().toLowerCase();
          int fromIndex = create.indexOf("table")+5;
          int toIndex   = create.indexOf("(");
          destTableName = create.substring(fromIndex,toIndex).replaceAll(beanDest.getQC(),"").trim();
          System.out.println("destTable "+destTableName);
            
        } else
          destTableName = convertText(names.get(tbIndex).toString());

        if (mode == Convert.DataBase.CONVERT_MODE)
        {

          if (tables.indexOf(destTableName) >= 0)
          {
            stm.executeUpdate("drop table " + beanDest.getQC() + destTableName + beanDest.getQC());
            tables.remove(destTableName);
            System.out.println("dropped table" + destTableName);

          }
          /*
          if(destTableName.equals(""))
          if (tables.indexOf(names.get(tbIndex)) >= 0)
          {
            stm.executeUpdate("drop table " + beanDest.getQC() + names.get(tbIndex) + beanDest.getQC());
            tables.remove((String) names.get(tbIndex));
            System.out.println("dropped table" + names.get(tbIndex));
          } else if (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0)
          {
            stm.executeUpdate("drop table " + beanDest.getQC() + convertText((String) names.get(tbIndex)) + beanDest.getQC());
            tables.remove(convertText((String) names.get(tbIndex)));
            System.out.println("dropped table" + names.get(tbIndex));
          }
*/
          if ((tables.indexOf(destTableName) < 0)) //&& tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0   )
          {
            
            if (creates.get(tbIndex).equals("") || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
            {
              System.out.println("Warning empty or invalid create statement - creating one for you\n");

              command = new StringBuffer(50);
              command.append("CREATE TABLE ");
              command.append(beanDest.getQC());
              command.append(convertText((String) names.get(tbIndex)));
              command.append(beanDest.getQC());
              command.append("(");
              String type = null;
              Vector columnNames = bean.getColumnNames();
              for (int i = 0; i < columnNames.size() - 1; ++i)
              {
                type = bean.metaData.getColumnTypeName(i + 1);
                //   System.out.println(i+" "+result[1].get(i)+" "+type);
                type = (type.equals("NUMBER")) ? "INT4" : type;
                type = (type.equals("CONTAINER")) ? "TEXT" : type;

                command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
              }
              type = bean.metaData.getColumnTypeName(columnNames.size());
              type = (type.equals("NUMBER")) ? "INT4" : type;
              type = (type.equals("CONTAINER")) ? "TEXT" : type;
              command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
              command.append(" )");

              // System.out.println(command);
              //  System.exit(0);
              //command.append(DBBean.getQC());   
            } else
              command = new StringBuffer().append(creates.get(tbIndex).toString().toLowerCase());
            stm.executeUpdate(command.toString());

          }
        }
        if(dialog!=null)
        dialog.title.setText("Writing table data ...");

        // prepare the insert statement
        int j = -1;
        Vector row = null;
        command = new StringBuffer();

        command.append("INSERT  INTO ");
        command.append(beanDest.getQC());
        command.append(destTableName); //convertText((String) names.get(tbIndex)));
        command.append(beanDest.getQC());
        
        command.append(" values ( ");

        // add a question marks for every field 
        for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
          command.append("?,");
        command.append("?)");
        PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
        System.out.println(command);
        int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
        Vector vec = new Vector(myIds);
        int endIndex = -1;
        String tempQuery = query;
        String tempID = bean.getQC() + idField + bean.getQC();
        // if id_field not do incremental conversion else do it all at once
        if (!idField.equals(""))
        {
          long startTime = System.currentTimeMillis();
          int counter = -1;
          while (true)
          {
            ++counter;
            if (counter == 0&&dialog!=null)
              dialog.title.setText("Check if data  is available");
            else
              if(dialog!=null)
              dialog.title.setText("Check if more  data  is available");
            myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
            if (myIds.isEmpty())
              break;
            vec = new Vector(myIds);
            rowCount = vec.size();
            System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIds.size());
            deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
            if (vec.size() <= numIntervalls)
            {
              endIndex = 0;
              deltaID = vec.size();
            }
            for (int k = 0; k < vec.size() - deltaID; k = k + deltaID)
            {
              System.out.println(vec.get(k) + " " + vec.get(k + deltaID) + " " + vec.lastElement());
              if (query.indexOf("where") > 0)
                tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID)+"'";
              else
                tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID)+"'";
              System.out.println(tempQuery);
              if(dialog!=null)
              dialog.title.setText("Reading table data ...");

              bean.makeQuery(tempQuery, deltaID);
              if(dialog!=null)
              dialog.title.setText("Writing table data ...");

              command = writeDatainDestTable(dialog, command, k, pstm, rowCount,delimiter);
              endIndex = k + deltaID;
            }
            System.out.println(endIndex);
            //all data written ? if not write last chunk of data
            if (endIndex == vec.size() - 1)
              System.out.println("fits");
            else
            {
              System.out.println(" last intervall from " + vec.get(endIndex) + " " + vec.lastElement());

              if (query.indexOf("where") > 0)
                tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement()+"'";
              else
                tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement()+"'";
              System.out.println(tempQuery);
              if(dialog!=null)
              dialog.title.setText("Reading table data ...");
              bean.makeQuery(tempQuery, 0);
              if(dialog!=null)
              dialog.title.setText("Writing table data ...");
              command = writeDatainDestTable(dialog, command, endIndex, pstm, rowCount,delimiter);
            }
            // prepare new query for next chunk
            if (query.indexOf("where") > 0)
              tempQuery = query + " and " + tempID + ">'" + vec.lastElement()+"'";
            else
              tempQuery = query + " where " + tempID + ">'" + vec.lastElement()+"'";

          }
          long endTime = System.currentTimeMillis();
          System.out.println("Time for incremental convert elapsed " + (endTime - startTime));
        } else
        {
          // read and write all in one big chunk
          long startTime = System.currentTimeMillis();
          
          bean.makeQuery(query, 0);
          command = writeDatainDestTable(dialog, command, j, pstm, rowCount,delimiter);
          long endTime = System.currentTimeMillis();
          System.out.println("Time for old convert elapsed " + (endTime - startTime));

        }
      }
    } catch (Exception e)
    {
      System.out.println("Error while connecting to database " + e);
      if (dialog != null)
      {
        dialog.setVisible(false);
        dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
        FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
        java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
        java.io.PrintStream stream = new java.io.PrintStream(b);
        stream.print(command + "\n\n");
        e.printStackTrace(stream);
        FM2SQL.showErrorDialog(b.toString(), "Error occured !");
      } else
      {
        e.printStackTrace();

      }
    }
    if (dialog != null)
    {
      dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
      FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
      dialog.setVisible(false);
    }
  }
  /**
   * Writes data to the destination table 
   * @param dialog  progress dialog
   * @param command 
   * @param j       data index for progress bar
   * @param pstm    prepared statement
   * @param rowCount number of datasets
   * @return   command
   * @throws Exception
   * @throws SQLException
   */
  private static StringBuffer writeDatainDestTable(FM2SQL.ProgressDialog dialog, StringBuffer command, int j, PreparedStatement pstm, int rowCount,String delimiter) throws Exception, SQLException
  {
    Vector row;
    while ((row = bean.getNextRow()) != null)
    {
      j++;
      // row = (Vector) result[0].get(j);
      /*   command = new StringBuffer();
      
           command.append("INSERT  INTO ");
           command.append(beanDest.getQC());
           command.append(convertText((String) names.get(tbIndex)));
           command.append(beanDest.getQC());
           command.append(" values ( ");
        */
      //print rows
      Object obj = null;
      /* for(int k=0;k<row.size()-1;++k)
       {
          obj = row.get(k);
          //System.out.println("row "+obj+" "+k);
         if(obj!=null&&!(obj instanceof ArrayList))
         command.append("'"+convertUml(obj.toString())+"',"); 
         else if(obj!=null&&   obj instanceof ArrayList)
         command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',"); 
         else command.append("NULL,");
       }
       obj = row.get(row.size() - 1);
       if (obj != null && !(obj instanceof ArrayList))
       command.append("'"+convertUml(obj.toString())+"')"); 
        else
        if(obj!=null&&   obj instanceof ArrayList)
         command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')");         //command.append(obj.toString()+")");
         else command.append("NULL)");
        */
      //command.append("'"+row.get(row.size()-1)+"')"); 
      //command.append(" )");
      //  for(int k=0;k<row.size();++k)

      // System.out.println();
      //   System.out.println(command+" "+j+" "+row.size()+" "+  ((Vector)result2[0].get(j)).size());
      // System.out.println(command);
      for (int k = 0; k < row.size(); ++k)
      {
        obj = row.get(k);
        if (obj instanceof ArrayList)
				if (obj instanceof ArrayList)
			 obj = formatFileMakerArray((List) obj,delimiter);
      
        String str = (obj == null) ? "NULL" : obj.toString();
        if (!str.equals("NULL"))
          pstm.setString(k + 1, str);
        else
          pstm.setNull(k + 1, Types.NULL);
      }
      pstm.execute();
      //stm.executeUpdate(command.toString());
      if (dialog != null)
        dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
      // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
      command = null;
    } // to while loop    
    return command;
  }

  /**
   *  removes special characters from the input string as well as .fp5 
   * @param newName String to change
   * @return
   */
  public static String convertText(String newName)
  {
    StringBuffer alterMe = new StringBuffer(newName.trim().toLowerCase());
    int length = alterMe.length();
    int j = 0;
    int index = alterMe.indexOf(".fp5");
    if (index >= 0)
    {
      alterMe.delete(index, index + 4);
      length = length - 4;
    }

    while (j < length)
    {
      if (alterMe.charAt(j) == ' ')
      {
        alterMe.setCharAt(j, '_');
        //    if(j<length-1) j=j+1;
      } else if (alterMe.charAt(j) == '_')
      {

        if (alterMe.charAt(j + 1) == '_')
          alterMe.deleteCharAt(j);
        length = length - 1;
        //  if(j<length-1) j=j+1;
      } else if (alterMe.charAt(j) == 'ä')
      {
        alterMe.setCharAt(j, 'a');
        alterMe.insert(j + 1, "e");
        length = length + 1;
        if (j < length - 1)
          j = j + 1;
      } else if (alterMe.charAt(j) == 'ö')
      {
        alterMe.setCharAt(j, 'o');
        alterMe.insert(j + 1, "e");
        length = length + 1;
        if (j < length - 1)
          j = j + 1;
      } else if (alterMe.charAt(j) == 'ü')
      {
        alterMe.setCharAt(j, 'u');
        alterMe.insert(j + 1, "e");
        length = length + 1;
        if (j < length - 1)
          j = j + 1;
      } else if (alterMe.charAt(j) == 'ß')
      {
        alterMe.setCharAt(j, 's');
        alterMe.insert(j + 1, "s");
        length = length + 1;
        if (j < length - 1)
          j = j + 1;
      } else if (alterMe.charAt(j) == ':')
      {
        if (j < length - 1)
        {
          if (alterMe.charAt(j + 1) == ':')
          {
            alterMe.setCharAt(j, '_');
            alterMe.delete(j + 1, j + 2);
            length = length - 1;

          }

          if (j < length - 1)
            j = j + 1;
        }
      } else if (alterMe.charAt(j) == '-')
      {
        alterMe.setCharAt(j, '_');

      } else if (alterMe.charAt(j) == '?')
      {
        // changed ? to _ because of update statement
        alterMe.setCharAt(j, '_');
        // length = length + 1;
        // j=j+1;
        System.out.println(alterMe);
      } else if (alterMe.charAt(j) == '.')
      {
        if (j == length - 1)
        {
          alterMe.delete(j, j);
          length--;
        } else
          alterMe.setCharAt(j, '_');
      }

      ++j;
    }
    return alterMe.toString();
  }
  /**
   * Converts > and < in an entity (&gt; or &lt;)
   * @param newName
   * @return
   */
  public static String convertToEntities(String newName)
  {
    StringBuffer alterMe = new StringBuffer(newName.trim());
    int length = alterMe.length();
    int j = 0;

    while (j < length)
    {

      if (alterMe.charAt(j) == '>')
      {
        alterMe.setCharAt(j, '&');
        alterMe.insert(j + 1, "gt;");
        length = length + 2;
        if (j < length - 1)
          j = j + 1;

      } else if (alterMe.charAt(j) == '<')
      {
        alterMe.setCharAt(j, '&');
        alterMe.insert(j + 1, "lt;");
        length = length + 2;
        if (j < length - 1)
          j = j + 1;

      }
      ++j;
    }
    return alterMe.toString();
  }
  /**
   * Masks the single quote character '-->\'
   * @param newName
   * @return
   */
  public static String convertUml(String newName)
  {
    StringBuffer alterMe = new StringBuffer(newName.trim());
    int length = alterMe.length();
    int j = 0;

    while (j < length)
    {

      if (alterMe.charAt(j) == '\'')
      {
        alterMe.setCharAt(j, '\\');
        alterMe.insert(j + 1, "'");
        length = length + 1;
        if (j < length - 1)
          j = j + 1;
      }
      /*   else
         if (alterMe.charAt(j) == '"')
         {
      	 alterMe.setCharAt(j, '\\');
      	 alterMe.insert(j + 1, "\"");
      	 length = length + 1;
      	 if(j<length-1) j=j+1;
         }
        else
        if (alterMe.charAt(j) == '>')
         {
      	 alterMe.setCharAt(j, '\\');
      	 alterMe.insert(j + 1, ">");
      	 length = length + 1;
      	 if(j<length-1) j=j+1;
         }
        else
        if (alterMe.charAt(j) == '<')
         {
      	 alterMe.setCharAt(j, '\\');
      	 alterMe.insert(j + 1, "<");
      	 length = length + 1;
      	 if(j<length-1) j=j+1;
         }
       else
       if (alterMe.charAt(j) == '?')
         {
      	 alterMe.setCharAt(j, '\\');
      	 alterMe.insert(j + 1, "?");
      	 length = length + 1;
      	 if(j<length-1) j=j+1;
         }
       else
       if (alterMe.charAt(j) == '&')
         {
      	 alterMe.setCharAt(j, '\\');
      	 alterMe.insert(j + 1, "&");
      	 length = length + 1;
      	 if(j<length-1) j=j+1;
         }
       else
       if (alterMe.charAt(j) == '=')
         {
      	 alterMe.setCharAt(j, '\\');
      	 alterMe.insert(j + 1, "=");
      	 length = length + 1;
      	 if(j<length-1) j=j+1;
         }
       else
       if (alterMe.charAt(j) == ',')
         {
      	 alterMe.setCharAt(j, '\\');
      	 alterMe.insert(j + 1, ",");
      	 length = length + 1;
      	 if(j<length-1) j=j+1;
         }
       else
       if (alterMe.charAt(j) == '.')
         {
      	 alterMe.setCharAt(j, '\\');
      	 alterMe.insert(j + 1, ".");
      	 length = length + 1;
      	 if(j<length-1) j=j+1;
         }
       else
       if (alterMe.charAt(j) == '[')
         {
      	 alterMe.setCharAt(j, '\\');
      	 alterMe.insert(j + 1, ".");
      	 length = length + 1;
      	 if(j<length-1) j=j+1;
         }
      else
       if (alterMe.charAt(j) == ']')
         {
      	 alterMe.setCharAt(j, '\\');
      	 alterMe.insert(j + 1, ".");
      	 length = length + 1;
      	 if(j<length-1) j=j+1;
         }
      else
       if (alterMe.charAt(j) == '%')
         {
      	 alterMe.setCharAt(j, '\\');
      	 alterMe.insert(j + 1, "%");
      	 length = length + 1;
      	 if(j<length-1) j=j+1;
         }*/
      ++j;
    }
    return alterMe.toString();
  }
/**
 * parses the input xml file for batch conversion
 * called from readXMLFile
 * * @param sb
 */
  public static void parseXMLConfig(StringBuffer sb)
  {
    boolean finished = false;
    // parse string and build document tree
    Xparse parser = new Xparse();
    parser.changeEntities = true;
    Node root = parser.parse(sb.toString());
    // printContents(root);
    Vector databases = new Vector();
    Vector tables = new Vector();
    Vector layouts = new Vector();
    Vector selects = new Vector();
    Vector creates = new Vector();
    Vector ids = new Vector();
    String delimiter = "|";
    int mode = -1;

    try
    {
      Node tempNode = root.find("convert/source", new int[] { 1, 1 });
      if (tempNode == null)
        throw new Error("parse error source tag missing");
      System.out.println(tempNode.name);
      int length = countNodes(tempNode);
      for (int i = 1; i <= length; i++)
      {

        DBBean database = new DBBean();
        tables = new Vector();
        layouts = new Vector();
        selects = new Vector();
        creates = new Vector();
        ids = new Vector();
        // parse dataBase
        Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
        Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
        Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
        Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
        Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
        Node delimiterNode =root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
        if (node3 == null)
          throw new Error("parse error database tag missing");
        if (node == null)
          throw new Error("parse error url tag missing");
        if (node1 == null)
          throw new Error("parse error user tag missing");
        if (node2 == null)
          throw new Error("parse error password tag missing");
        if(delimiterNode!=null) delimiter = delimiterNode.getCharacters();
        String url = node.getCharacters();
        String user = node1.getCharacters();
        String password = node2.getCharacters();
        database.setURL(url.trim());
        database.setUserAndPasswd(user.trim(), password.trim());
        System.out.println(node.name + " " + node.getCharacters());
        System.out.println(node1.name + " " + node1.getCharacters());
        System.out.println(node2.name + " " + node2.getCharacters());
        String modeString = "";
        if (nodeMode == null)
          modeString = "convert";
        else
          modeString = nodeMode.getCharacters();
        if (modeString.equals("convert"))
          mode = DataBase.CONVERT_MODE;
        else if (modeString.equals("append"))
          mode = DataBase.APPEND_MODE;
        else if (modeString.equals("update"))
          mode = DataBase.UPDATE_MODE;
        else if (modeString.equals("delete"))
          mode = DataBase.DELETE_MODE;

        //   if(node3!=null)
        // System.out.println(node3.name);

        int length2 = countNodes(node3);

        System.out.println("number of tables " + length2);

        for (int j = 1; j <= length2; ++j)
        {
          Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
          Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
          Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
          if (node4 != null)
            System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
          if (node5 != null)
            System.out.println(node5.name + " " + node5.getCharacters());
          if (node6 != null)
            System.out.println(node6.name + " " + node6.getCharacters());
          if (node4 == null)
            throw new Error("parse error table tag missing");
          // if(node5==null) throw new Error("parse error select tag missing");
          // if(node6==null) throw new Error("parse error create tag missing");
          String name = (String) node4.attributes.get("name");
          String layout = (String) node4.attributes.get("layout");
          String id = (String) node4.attributes.get("id");
          System.out.println("id was " + id);
          if (name == null)
            throw new Error("parse error required table tag attribute name missing");
          if (layout == null)
            layout = "";
          if (id == null)
            id = "";
          if (name.equals(""))
            throw new Error("parse error table tag attribute must not be empty");
          tables.add(name);
          layouts.add(layout);
          ids.add(id);
          String query = (node5 == null) ? "" : node5.getCharacters();
          if (query.equals(""))
            System.err.println("Warning empty select tag or  select tag missing !!");
          query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
          selects.add(query);
          if (node6 != null)
            creates.add(node6.getCharacters().trim());
          else
            creates.add("");

        }
        DataBase dataBase = new DataBase(database, tables, layouts, selects, creates, ids, mode);
        dataBase.delimiter = delimiter;
        databases.add(dataBase);
      }
      DBBean database = new DBBean();
      // parse dataBase
      Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
      Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
      Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
      String url = node.getCharacters();
      String user = node1.getCharacters();
      String password = node2.getCharacters();
      System.out.println(url);
      database.setURL(url.trim());
      database.setUserAndPasswd(user.trim(), password.trim());
      //databases.add(database);
      for (Iterator iter = databases.iterator(); iter.hasNext();)
      {
        DataBase db = (DataBase) iter.next();
        if (mode != DataBase.UPDATE_MODE)
          convertBatch(db.bean, database, db.tables, db.layouts, db.selects, db.creates, db.ids,mode,db.delimiter);
        else
          update(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids, mode);

      }
      // printContents(node3);
      //   FM2SQL.fmInstance=new FM2SQL();
    } catch (Exception e)
    {
    
      e.printStackTrace();
    }
  }
  public static Vector getXMLConfig(String xmlFile)
  {
    StringBuffer sb = null;
    try
    {
      // read XML Metadata from a file
      FileInputStream fi = new FileInputStream(xmlFile);
      InputStreamReader isr = new InputStreamReader(fi, "UTF-8");
      BufferedReader buffr = new BufferedReader(isr);
      sb = new StringBuffer();
      int c = 0;
      while ((c = buffr.read()) != -1)
      {
        char ch = (char) c;
        sb.append(ch);
        // System.out.print((char)c);
      }

    } catch (Exception e)
    {
      e.printStackTrace();
    }

    boolean finished = false;
    // parse string and build document tree
    Xparse parser = new Xparse();
    parser.changeEntities = true;
    Node root = parser.parse(sb.toString());
    // printContents(root);
    Vector databases = new Vector();
    Vector tables = new Vector();
    Vector layouts = new Vector();
    Vector selects = new Vector();
    Vector creates = new Vector();
    Vector ids = new Vector();
		String delimiter = "|";
    int mode = -1;
    try
    {
      Node tempNode = root.find("convert/source", new int[] { 1, 1 });
      if (tempNode == null)
        throw new Error("parse error source tag missing");
      System.out.println(tempNode.name);
      int length = countNodes(tempNode);
      for (int i = 1; i <= length; i++)
      {

        DBBean database = new DBBean();
        tables = new Vector();
        layouts = new Vector();
        selects = new Vector();
        creates = new Vector();
        ids = new Vector();
        // parse dataBase
        Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
        Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
        Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
        Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
        Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
				Node delimiterNode =root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
				
        if (delimiterNode != null)
          delimiter = delimiterNode.getCharacters();
        if (node3 == null)
          throw new Error("parse error database tag missing");
        if (node == null)
          throw new Error("parse error url tag missing");
        if (node1 == null)
          throw new Error("parse error user tag missing");
        if (node2 == null)
          throw new Error("parse error password tag missing");
        String url = node.getCharacters();
        String user = node1.getCharacters();
        String password = node2.getCharacters();
        database.setURL(url.trim());
        database.setUserAndPasswd(user.trim(), password.trim());
        System.out.println(node.name + " " + node.getCharacters());
        System.out.println(node1.name + " " + node1.getCharacters());
        System.out.println(node2.name + " " + node2.getCharacters());
        String modeString = "";
        if (nodeMode == null)
          modeString = "convert";
        else
          modeString = nodeMode.getCharacters();
        if (modeString.equals("convert"))
          mode = DataBase.CONVERT_MODE;
        else if (modeString.equals("append"))
          mode = DataBase.APPEND_MODE;
        else if (modeString.equals("update"))
          mode = DataBase.UPDATE_MODE;
        else if (modeString.equals("delete"))
          mode = DataBase.DELETE_MODE;

        //   if(node3!=null)
        // System.out.println(node3.name);

        int length2 = countNodes(node3);

        System.out.println("number of tables " + length2);

        for (int j = 1; j <= length2; ++j)
        {
          Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
          Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
          Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
          if (node4 != null)
            System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
          if (node5 != null)
            System.out.println(node5.name + " " + node5.getCharacters());
          if (node6 != null)
            System.out.println(node6.name + " " + node6.getCharacters());
          if (node4 == null)
            throw new Error("parse error table tag missing");
          // if(node5==null) throw new Error("parse error select tag missing");
          // if(node6==null) throw new Error("parse error create tag missing");
          String name = (String) node4.attributes.get("name");
          String layout = (String) node4.attributes.get("layout");
          String id = (String) node4.attributes.get("id");
          System.out.println("id was " + id);

          if (name == null)
            throw new Error("parse error required table tag attribute name missing");
          if (layout == null)
            layout = "";
          if (id == null)
            id = "";
          if (name.equals(""))
            throw new Error("parse error table tag attribute must not be empty");
          tables.add(name);
          layouts.add(layout);
          ids.add(id);
          String query = (node5 == null) ? "" : node5.getCharacters();
          if (query.equals(""))
            System.err.println("Warning empty select tag or  select tag missing !!");
          query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
          selects.add(query);
          if (node6 != null)
            creates.add(node6.getCharacters().trim());
          else
            creates.add("");

        }
        DataBase dataBase=new DataBase(database, tables, layouts, selects, creates, ids, mode);
        dataBase.delimiter=delimiter;
        databases.add(dataBase);
      }
      DBBean database = new DBBean();
      // parse dataBase
      Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
      Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
      Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
      String url = node.getCharacters();
      String user = node1.getCharacters();
      String password = node2.getCharacters();
      System.out.println(url);
      database.setURL(url.trim());
      database.setUserAndPasswd(user.trim(), password.trim());
      databases.add(new DataBase(database, null, null, null, null, null, 0));
      //databases.add(database);
      /*    for (Iterator iter = databases.iterator(); iter.hasNext();)
         {
           DataBase db = (DataBase) iter.next();
           convertBatch(db.bean,database,db.tables,db.layouts,db.selects,db.creates);
          
         }*/
      // printContents(node3);
      //   FM2SQL.fmInstance=new FM2SQL();
    } catch (Exception e)
    {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return databases;
  }

  private static int countNodes(Node tempNode)
  {
    int length = 0;
    for (int i = 0; i < tempNode.contents.v.size(); ++i)
    {
      Node node = (Node) tempNode.contents.v.elementAt(i);
      if (node.type.equals("element"))
      {
        if (node.name.equals("database"))
          length++;
        if (node.name.equals("table"))
          length++;
      }

      // System.out.println(((Node)tempNode.contents.v.elementAt(i)).attributes+" "+i);
    }
    return length;
  }
  private static void printContents(Node root)
  {

    Vector contents = (root.index == null) ? root.contents.v : root.index.v;
    for (int i = 0; i < contents.size(); ++i)
    {
      Node n = (Node) contents.elementAt(i);
      if (n.type.equals("element"))
      {
        System.out.println("tag " + n.name);
        System.out.println(n.getCharacters());
        //contents=n.contents.v i=0;
      }
      // System.out.println(n.type);
    }
  }
  /**
   * reads the specified xml file
   * @param xmlFile
   */
  public static void readXMLFile(String xmlFile)
  {
    try
    {
      // read XML Metadata from a file
      FileInputStream fi = new FileInputStream(xmlFile);
      InputStreamReader isr = new InputStreamReader(fi, "UTF-8");
      BufferedReader buffr = new BufferedReader(isr);
      StringBuffer sb = new StringBuffer();
      int c = 0;
      while ((c = buffr.read()) != -1)
      {
        char ch = (char) c;
        sb.append(ch);
        // System.out.print((char)c);
      }
      parseXMLConfig(sb);
    } catch (Exception e)
    {
      e.printStackTrace();
    }
  }
  
  /**
   * Helper class for XML-File parsing
   * Holds the parsed data
   * @author rogo
   *
   */
  public static class DataBase
  {
    DBBean bean;
    Vector creates;
    Vector selects;
    Vector layouts;
    Vector tables;
    Vector ids;
    String delimiter = "//";
    final static int CONVERT_MODE = 1;
    final static int APPEND_MODE = 2;
    final static int UPDATE_MODE = 3;
    final static int DELETE_MODE = 4;

    int mode = -1;

    public DataBase(DBBean bean, Vector tables, Vector layouts, Vector selects, Vector creates, Vector ids, int mode)
    {
      this.bean = bean;
      this.tables = tables;
      this.layouts = layouts;
      this.selects = selects;
      this.creates = creates;
      this.ids = ids;
      this.mode = mode;
      this.bean.setIDVector(ids);
    }
    /**
     * writes the data contained in this object to the buffered writer
     * * @param buffr
     * @throws Exception
     */
    public void exportToXML(BufferedWriter buffr) throws Exception
    {
      // ids=bean.getIDVector();
      buffr.write("    <database>\n");
      buffr.write("      <url>" + bean.url + "</url>\n");
      buffr.write("      <user>" + bean.user + "</user>\n");
      buffr.write("      <password>" + bean.passwd + "</password>\n");
      buffr.write("      <delimiter>"+delimiter+"</delimiter>\n");
      String modeString = "";
      if (mode == CONVERT_MODE)
        modeString = "convert";
      else if (mode == APPEND_MODE)
        modeString = "append";
      else if (mode == UPDATE_MODE)
        modeString = "update";
      else if (mode == DELETE_MODE)
             modeString = "delete";

      buffr.write("      <mode>" + modeString + "</mode>\n");
      int index = 0;
      while (index < tables.size())
      {
        String table = (String) tables.get(index);
        String layout = (String) layouts.get(index);
        String select = (String) selects.get(index);
        String create = (String) creates.get(index);
        String id = (String) ids.get(index);

        buffr.write("      <table name = \"" + table + "\" layout = \"" + layout + "\" id = \"" + id + "\" >\n");
        buffr.write("         <select>" + convertToEntities(select) + "</select>\n");
        if (!create.equals(""))
          buffr.write("         <create>" + create + "         </create>\n");
        buffr.write("      </table>\n");
        index++;
      }
      buffr.write("    </database>\n");
    }
    public String toString()
    {
      return bean.url + " " + tables;
    }

  }
  public static String convertToUTF8(Object command)
  {
    String str = null;
    try
    {
      str = new String(command.toString().getBytes("UTF-8"));
    } catch (UnsupportedEncodingException e)
    {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return str;
  }
  public static void writeConfig(String file, DataBase source, DataBase destination) throws Exception
  {
    if (!file.toLowerCase().endsWith(".xml"))
      file += ".xml";
    File f = new File(file);

    FileOutputStream fout = new FileOutputStream(f);
    OutputStreamWriter outsw = new OutputStreamWriter(fout, "UTF-8");
    BufferedWriter buffw = new BufferedWriter(outsw);
    buffw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
    buffw.newLine();
    buffw.write("<convert>\n");
    buffw.write("  <source>\n");
    source.exportToXML(buffw);
    buffw.write("  </source>\n");
    buffw.write("\n  <destination>\n");
    destination.exportToXML(buffw);
    buffw.write("  </destination>\n");
    buffw.write("</convert>\n");
    buffw.close();
  }
  public static void delete(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
  {
    FM2SQL.ProgressDialog dialog = null;
    if (FM2SQL.fmInstance != null)
    {
      dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance,bean);
      dialog.setTitle("Conversion running ...");
      dialog.title.setText("Getting table data ...");
      dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
      dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
      FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
      dialog.thread = Thread.currentThread();
    }
    // setting user and passwd 
    bean.setUserAndPasswd(user, passwd);
    // setting user and passwd 
    beanDest.setUserAndPasswd(userDest, passwdDest);
    if (dialog != null)
      dialog.setSize(400, 250);
    StringBuffer command = null;
    String query = null;
    try
    {
      //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");    
      //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
      bean.setConnection(source);
      if (names == null)
        names = bean.getTableNames();
      // Collections.sort(names);
      int tbIndex = 1;

      // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
      for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
      {
        Vector[] result = null;
        java.util.TreeSet myIds = new TreeSet();
        java.util.TreeSet myIdsDest = new TreeSet();
        int deltaID = 1;
        String idField = "";
        String destTableName = "";

        try
        {
          query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
          String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
          query = (selects != null) ? selects.get(tbIndex).toString() : query;
          //if  vectors[1].get(i) != null)
          if (layout != "")
          {
            layout = " layout " + bean.getQC() + layout + bean.getQC();
            String name = names.get(tbIndex).toString();
            StringBuffer queryLayout = new StringBuffer(query);
            queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
            query = queryLayout.toString();
            System.out.println("added layout  " + query);

          }
          dialog.title.setText("Getting table data ...");
          dialog.table.setText(names.get(tbIndex).toString());
          dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
          dialog.show();
          bean.getConnection();
          bean.makeQuery(query, 50);
          idField = ids.get(tbIndex).toString();

        } catch (Exception e)
        {
          continue;
        }
        // determine destTableName from createStatement or from source table name
        if (!creates.get(tbIndex).equals(""))
        {
          String create = creates.get(tbIndex).toString().toLowerCase();
          int fromIndex = create.indexOf("table") + 5;
          int toIndex = create.indexOf("(");
          destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
          System.out.println("destTable " + destTableName);

        } else
          destTableName = convertText(names.get(tbIndex).toString());

        // for id kram
        Vector vec = null;
        Vector vecDest = null;
        //      tempo
        beanDest.setConnection(destination);
        int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
        String tempID = bean.getQC() + idField + bean.getQC();
        String tempIDdest = beanDest.getQC() + convertText(idField) + beanDest.getQC();

        int endIndex = -1;
        String tempQuery = query;
        String destQuery = query.replaceAll(names.get(tbIndex).toString(), destTableName);
        String tempQueryDest = destQuery;
        // remove extra query parts destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
        System.out.println("new Query " + tempQueryDest);
        if (!idField.equals(""))
        {
          long startTime = System.currentTimeMillis();
          int counter = -1;
          while (true)
          {
            ++counter;
            if (counter == 0 && dialog != null)
              dialog.title.setText("Check if data  is available");
            else if (dialog != null)
              dialog.title.setText("Check if more  data  is available");
            myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
            myIdsDest = beanDest.getIDVector(convertText(idField), destTableName, tempQueryDest, numHits);
            if (myIds.isEmpty())
              break;
            vec = new Vector(myIds);
            vecDest = new Vector(myIdsDest);
            rowCount = vec.size();
            // Deletion will work this way
            Vector deleted = new Vector(vec);
            Vector linesToDelete = new Vector(vecDest);
            // remove all lines that should not be deleted
            linesToDelete.removeAll(deleted);
            // System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIdsDest.size());
            /// @TODO complete delete task remove query show lines to be deleted let user choose if he wants that
            System.out.println("number of lines to  be deleted " + linesToDelete.size());
            deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
            beanDest.setConnection(destination);

            Statement stm = beanDest.getConnection().createStatement();

            Vector tables = beanDest.getTableNames();
            // Collections.sort(tables);
            System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames()); 
            tables = beanDest.getTableNames();
            // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
            stm = beanDest.getConnection().createStatement();

            if (dialog != null)
              dialog.title.setText(" Deleting table data ...");

            int j = -1;

            Vector row = null;
            command = new StringBuffer();

            command.append("DELETE FROM");
            command.append(beanDest.getQC());
            command.append(destTableName);
            //command.append(convertText((String) names.get(tbIndex)));
            command.append(beanDest.getQC());
            int size = bean.getColumnNames().size();
            command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " =  ?");
            PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
            System.out.println(command + " " + tbIndex);
            //int rowCount = bean.getRowCount(query);
            //        int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
            while (true)
            {

              ++j;
              if (j == linesToDelete.size())
                break;
              //print rows
              pstm.setString(1, linesToDelete.get(j).toString());
              System.out.println(pstm.toString());
                pstm.execute();
              if (dialog != null)
                dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
              command = null;
            }
            // prepare new query for next chunk
            if (query.indexOf("where") > 0)
              tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
            else
              tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";

          } //to outer while
        } // to idfield if  
      } // table loop

    } catch (Exception e)
      {
        System.out.println("Error while connecting to database " + e);
        if (dialog != null)
        {
          dialog.setVisible(false);
          dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
          FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
        }
        java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
        java.io.PrintStream stream = new java.io.PrintStream(b);
        stream.print(command + "\n\n");
        e.printStackTrace(stream);
        FM2SQL.showErrorDialog(b.toString(), "Error occured !");

      }
      if (dialog != null)
      {
        dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
        FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));

        dialog.setVisible(false);
      }
    } // to method

  }