/*
 * 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.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 java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.StringTokenizer;
import java.util.TreeSet;
import java.util.Vector;

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 = "";
    String[] fieldNames = null;
    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("(");
          int endIndex = create.indexOf(")", toIndex);

          destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
          System.out.println("destTable " + destTableName);
          // retrieve field_names from select statement
          if (query.indexOf("*") < 0)
          {
            int selectEndIndex = query.indexOf("from");
            StringTokenizer tokenizer = new StringTokenizer(query.substring(6, selectEndIndex), ",");
            int numFields = tokenizer.countTokens();
            fieldNames = new String[numFields];
            int fieldIndex = 0;
            while (tokenizer.hasMoreTokens())
            {
              String fieldName = tokenizer.nextToken().trim();
              fieldNames[fieldIndex] = convertText(fieldName);
              System.out.println(fieldNames[fieldIndex]);
              fieldIndex++;
            }

          } else
          {
            // use create statement for field names
            StringTokenizer tokenizer = new StringTokenizer(create.substring(toIndex + 1, endIndex), ",");
            int numFields = tokenizer.countTokens();
            fieldNames = new String[numFields];
            int fieldIndex = 0;
            while (tokenizer.hasMoreTokens())
            {
              String fieldName = tokenizer.nextToken().trim();
              int index = fieldName.lastIndexOf(" ");
              fieldNames[fieldIndex] = fieldName.substring(0, index);
              System.out.println(fieldNames[fieldIndex]);
              fieldIndex++;
            }
          }
        } else
        {
          destTableName = convertText(names.get(tbIndex).toString());

          // retrieve field_names from select statement
          if (query.indexOf("*") < 0)
          {
            int selectEndIndex = query.indexOf("from");
            StringTokenizer tokenizer = new StringTokenizer(query.substring(6, selectEndIndex), ",");
            int numFields = tokenizer.countTokens();
            fieldNames = new String[numFields];
            int fieldIndex = 0;
            while (tokenizer.hasMoreTokens())
            {
              String fieldName = tokenizer.nextToken().trim();
              fieldNames[fieldIndex] = convertText(fieldName);
             // System.out.println("field "+ fieldNames[fieldIndex]);
              fieldIndex++;
            }

          } else
          {
            Vector fieldNamesVec = bean.getColumnNames();
            fieldNames = new String[fieldNamesVec.size()];
            int fieldIndex = -1;
            for (Iterator iter = fieldNamesVec.iterator(); iter.hasNext();)
            {
              String element = (String) iter.next();
              fieldNames[++fieldIndex] = bean.getQC() + convertText(element) + bean.getQC();
             // System.out.println("field " + fieldNames[fieldIndex]);
            }
          }
        }
        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(" (");
        for (int i = 0; i < fieldNames.length; i++)
        {
          command.append(fieldNames[i]);
          if (i < fieldNames.length - 1)
            command.append(",");
        }
        command.append(") ");

        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)
          obj = formatFileMakerArray((List) obj, delimiter);

        String str = (obj == null) ? "NULL" : obj.toString();
        if (obj instanceof Double)
        {
          pstm.setDouble(k + 1, ((Double) obj).doubleValue());
        } else 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
  
  /**
   * Converts input String in norman encoding to unicode 
   * @param inp
   * @return converted String
   */
  static public String normanToUnicode(String inp) {
    StringBuffer buf = new StringBuffer();
    for (int i = 0; i < inp.length(); i++) {
      char c = inp.charAt(i);
     // System.out.println("char "+c+" "+(int)c);
      switch (c) {
      case 1: buf.append("\u00d0"); break; // Eth
      case 2: buf.append("\u00f0"); break; // eth
      case 3: buf.append("\u0141"); break; // Lslash
      case 4: buf.append("\u0142"); break; // lslash
      case 5: buf.append("\u0160"); break; // S caron
      case 6: buf.append("\u0161"); break; // s caron
      case 7: buf.append("\u00dd"); break; // Y acute
      case 8: buf.append("\u00fd"); break; // y acute
      case 11: buf.append("\u00de"); break; // Thorn
      case 12: buf.append("\u00fe"); break; // thorn
      case 14: buf.append("\u017d"); break; // Z caron
      case 15: buf.append("\u017e"); break; // z caron
      case 17: buf.append("\u0073"); break; // asciitilde
      case 18: buf.append("j\u0305"); break; // j macron [does a single char exist?]
      case 19: buf.append("^"); break; // circumflex
      case 20: buf.append("\u0303"); break; // tilde
      case 21: buf.append("\u00bd"); break; // onehalf
      case 22: buf.append("\u00bc"); break; // onequarter
      case 23: buf.append("\u00b9"); break; // onesuperior
      case 24: buf.append("\u00be"); break; // threequarters
      case 25: buf.append("\u00b3"); break; // threesuperior
      case 26: buf.append("\u00b2"); break; // twosuperior
      case 27: buf.append("\u00a6"); break; // brokenbar
      case 28: buf.append("-"); break; // minus
      case 29: buf.append("\u00d7"); break; // multiply
      case 39: buf.append("'"); break; // quotesingle
      case 94: buf.append("\u0302"); break; // circumflex
      case 96: buf.append("\u0300"); break; // grave
      case 196: buf.append("\u00c4"); break; // A dieresis
      case 197: buf.append("\u00c5"); break; // A ring
      case 201: buf.append("\u00c9"); break; // E acute
      case 209: buf.append("\u00d1"); break; // N tilde
      case 214: buf.append("\u00d6"); break; // O dieresis
      case 220: buf.append("\u00dc"); break; // U dieresis
      case 225: buf.append("\u00e1"); break; // a acute
      case 224: buf.append("\u00e0"); break; // a grave
      case 226: buf.append("\u00e2"); break; // a circumflex
      case 228: buf.append("\u00e4"); break; // a dieresis
      case 227: buf.append("\u00e3"); break; // a tilde
      case 229: buf.append("\u0101"); break; // a macron
      case 231: buf.append("\u00e7"); break; // c cedilla
      case 233: buf.append("\u00e9"); break; // e acute
      case 232: buf.append("\u00e8"); break; // e grave
      case 234: buf.append("\u00ea"); break; // e circumflex
      case 235: buf.append("\u00eb"); break; // e dieresis
      case 237: buf.append("\u00ed"); break; // i acute
      case 236: buf.append("\u00ec"); break; // i grave
      case 238: buf.append("\u00ee"); break; // i circumflex
      case 239: buf.append("\u00ef"); break; // i dieresis
      case 241: buf.append("\u00f1"); break; // n tilde
      case 243: buf.append("\u00f3"); break; // o acute
      case 242: buf.append("\u00f2"); break; // o grave
      case 244: buf.append("\u00f4"); break; // o circumflex
      case 246: buf.append("\u00f6"); break; // o dieresis
      case 245: buf.append("\u00f5"); break; // o tilde
      case 250: buf.append("\u00fa"); break; // u acute
      case 249: buf.append("\u00f9"); break; // u grave
      case 251: buf.append("\u00fb"); break; // u circumflex
      case 252: buf.append("\u00fc"); break; // u dieresis
      case 8224: buf.append("\u1e6d"); break; // t underdot
      case 176: buf.append("\u00b0"); break; // degree
      case 162: buf.append("\u1ebd"); break; // e tilde
      case 163: buf.append("\u00a3"); break; // sterling
      case 167: buf.append("\u00a7"); break; // section
      case 182: buf.append("\u00b6"); break; // paragraph
      case 223: buf.append("\u015b"); break; // s acute
      case 174: buf.append("\u1e5b"); break; // r underdot
      case 169: buf.append("\u1e45"); break; // n overdot
      case 353: buf.append("\u1e45"); break; // n overdot
      case 180: buf.append("\u0301"); break; // acute
      case 168: buf.append("\u0308"); break; // dieresis
      case 8800: buf.append("\u1e6d"); break; // t underdot
      case 198: buf.append("\u00c6"); break; // AE
      case 216: buf.append("\u014d"); break; // o macron
      case 8734: buf.append("\u0129"); break; // i tilde
      case 177: buf.append("\u00b1"); break; // plusminus
      case 165: buf.append("\u012b"); break; // i macron
      case 181: buf.append("\u1e43"); break; // m underdot
      case 8706: buf.append("\u1e0d"); break; // d underdot
      case 240: buf.append("\u1e0d"); break; // d underdot

      case 8721: buf.append("\u1e63"); break; // s underdot
      case 960: buf.append("\u017a"); break; // z acute
      case 8747: buf.append("\u1e45"); break; // n overdot
      case 937: buf.append("\u0169"); break; // u tilde
      case 230: buf.append("\u00e6"); break; // ae
      case 248: buf.append("\u00f8"); break; // oslash
      case 191: buf.append("\u0304\u0306"); break; // macron breve
      case 172: buf.append("\u1e37"); break; // 
      case 8730: buf.append("j\u0305"); break; // j macron [does a single char exist?]
      case 402: buf.append("\u0103"); break; // a breve
      case 8776: buf.append("\u016d"); break; // u breve
      case 187: buf.append("\u1e42"); break; // M underdot
      case 8230: buf.append("\u2026"); break; // ellipsis
      case 192: buf.append("\u00c0"); break; // A grave
      case 195: buf.append("\u00c3"); break; // A tilde
      case 213: buf.append("\u00d5"); break; // O tilde
      case 338: buf.append("m\u0306"); break; // m breve
      case 339: buf.append("\u0153"); break; // oe
      case 8211: buf.append("\u2013"); break; // endash
      case 8212: buf.append("\u2014"); break; // emdash
      case 8220: buf.append("\u201c"); break; // quotedblleft
      case 8221: buf.append("\u201d"); break; // quotedblright
      case 8216: buf.append("\u2018"); break; // quoteleft
      case 8217: buf.append("\u2019"); break; // quoteright
      case 247: buf.append("\u1e37"); break; // l underring [actually underdot]
      case 9674: buf.append("\u1e41"); break; // m overdot
      case 255: buf.append("n\u0306"); break; // n breve
      case 376: buf.append("\u00d7"); break; // multiply
      case 8364: buf.append("\u1e5b"); break; // r underring [actually underdot]
      case 8249: buf.append("\u1e44"); break; // N overdot
      case 8250: buf.append("\u1e62"); break; // S underdot
      case 64257: buf.append("\u1e24"); break; // H underdot
      case 64258: buf.append("\u1e0c"); break; // D underdot
      case 8225: buf.append("\u2021"); break; // daggerdbl
      case 8218: buf.append("\u1e36"); break; // L underdot
      case 8222: buf.append("\u0113"); break; // e macron
      case 194: buf.append("\u1e5f"); break; // r underbar
      case 202: buf.append("r\u0324"); break; // r underdieresis
      case 193: buf.append("\u012a"); break; // I macron
      case 8486:
      case 203: buf.append("\u016b"); break; // u macron
      case 200: buf.append("\u1e6c"); break; // T underdot
      case 205: buf.append("\u1e64"); break; // S acute
      case 206: buf.append("\u2020"); break; // dagger
      case 207: buf.append("\u0115"); break; // e breve
      case 204: buf.append("\u014f"); break; // o breve
      case 211: buf.append("\u0100"); break; // A macron
      case 212: buf.append("\u1e46"); break; // N underdot
      case 210: buf.append("\u1e3b"); break; // l underbar
      case 218: buf.append("\u016a"); break; // U macron
      case 219: buf.append("\u0179"); break; // Z acute
      case 217: buf.append("\u1e5a"); break; // R underdot
      case 305: buf.append("\u0131"); break; // dotlessi
      case 710: buf.append("\u1e47"); break; // n underdot
      case 732: buf.append("\u1e49"); break; // n underbar
      case 175: buf.append("\u0304"); break; // macron
      case 728: buf.append("\u0306"); break; // breve
      case 729:case 215: buf.append("\u1e25"); break; // h underdot
      case 730: buf.append("\u012d"); break; // i breve
      case 184: buf.append("\u0327"); break; // cedilla
      case 733: buf.append("\u030b"); break; // hungarumlaut
      case 731: buf.append("\u0328"); break; // ogonek
      case 711: buf.append("\u030c"); break; // caron
      case 199: buf.append("\u012b\u0303"); break; // imacron tilde
      case 8226: buf.append("\u1e5d"); break; // runderdot macron
      case 8482: buf.append("\u016b\0306"); break; // umacron breve
      case 8804: buf.append("\u0101\u0301"); break; // amacron acute
      case 8805: buf.append("\u016b\u0301"); break; // umacron acute
      case 8719: buf.append("\u0113\u0301"); break; // emacron acute
      case 170: buf.append("\u0113\u0300"); break; // emacron breve
      case 186: buf.append("\u014d\u0300"); break; // omacron breve
      case 161: buf.append("\u0101\u0306"); break; // amacron breve
      case 8710: buf.append("\u0101\u0303"); break; // amacron tilde
      case 171: buf.append("\u012b\u0301"); break; // imacron acute
      case 8260: buf.append("\u1e00"); break; // runderdotmacron acute
      case 183: buf.append("\u1e5b\u0301"); break; // runderdot acute
      case 8240: buf.append("\u012b\u0306"); break; // imacron breve
      case 63743: buf.append("\u016b\u0303"); break; // umacron tilde
      default: buf.append(c);    
       if((int)c>127)
       System.out.println("char "+c+" "+(int)c);
       break;
      }
    }
    return buf.toString();
  }

  static public String normanToUnicodeOld(String inp) {
    StringBuffer buf = new StringBuffer();
    for (int i = 0; i < inp.length(); i++) {
      char c = inp.charAt(i);
      switch (c) {
      case 1: buf.append("\u00d0"); break; // Eth
      case 2: buf.append("\u00f0"); break; // eth
      case 3: buf.append("\u0141"); break; // Lslash
      case 4: buf.append("\u0142"); break; // lslash
      case 5: buf.append("\u0160"); break; // S caron
      case 6: buf.append("\u0161"); break; // s caron
      case 7: buf.append("\u00dd"); break; // Y acute
      case 8: buf.append("\u00fd"); break; // y acute
      case 11: buf.append("\u00de"); break; // Thorn
      case 12: buf.append("\u00fe"); break; // thorn
      case 14: buf.append("\u017d"); break; // Z caron
      case 15: buf.append("\u017e"); break; // z caron
      case 17: buf.append("\u0073"); break; // asciitilde
      case 18: buf.append("j\u0305"); break; // j macron [does a single char exist?]
      case 19: buf.append("^"); break; // circumflex
      case 20: buf.append("\u0303"); break; // tilde
      case 21: buf.append("\u00bd"); break; // onehalf
      case 22: buf.append("\u00bc"); break; // onequarter
      case 23: buf.append("\u00b9"); break; // onesuperior
      case 24: buf.append("\u00be"); break; // threequarters
      case 25: buf.append("\u00b3"); break; // threesuperior
      case 26: buf.append("\u00b2"); break; // twosuperior
      case 27: buf.append("\u00a6"); break; // brokenbar
      case 28: buf.append("-"); break; // minus
      case 29: buf.append("\u00d7"); break; // multiply
      case 39: buf.append("'"); break; // quotesingle
      case 94: buf.append("\u0302"); break; // circumflex
      case 96: buf.append("\u0300"); break; // grave
      case 128: buf.append("\u00c4"); break; // A dieresis
      case 129: buf.append("\u00c5"); break; // A ring
      case 131: buf.append("\u00c9"); break; // E acute
      case 132: buf.append("\u00d1"); break; // N tilde
      case 133: buf.append("\u00d6"); break; // O dieresis
      case 134: buf.append("\u00dc"); break; // U dieresis
      case 135: buf.append("\u00e1"); break; // a acute
      case 136: buf.append("\u00e0"); break; // a grave
      case 137: buf.append("\u00e2"); break; // a circumflex
      case 138: buf.append("\u00e4"); break; // a dieresis
      case 139: buf.append("\u00e3"); break; // a tilde
      case 140: buf.append("\u0101"); break; // a macron
      case 141: buf.append("\u00e7"); break; // c cedilla
      case 142: buf.append("\u00e9"); break; // e acute
      case 143: buf.append("\u00e8"); break; // e grave
      case 144: buf.append("\u00ea"); break; // e circumflex
      case 145: buf.append("\u00eb"); break; // e dieresis
      case 146: buf.append("\u00ed"); break; // i acute
      case 147: buf.append("\u00ec"); break; // i grave
      case 148: buf.append("\u00ee"); break; // i circumflex
      case 149: buf.append("\u00ef"); break; // i dieresis
      case 150: buf.append("\u00f1"); break; // n tilde
      case 151: buf.append("\u00f3"); break; // o acute
      case 152: buf.append("\u00f2"); break; // o grave
      case 153: buf.append("\u00f4"); break; // o circumflex
      case 154: buf.append("\u00f6"); break; // o dieresis
      case 155: buf.append("\u00f5"); break; // o tilde
      case 156: buf.append("\u00fa"); break; // u acute
      case 157: buf.append("\u00f9"); break; // u grave
      case 158: buf.append("\u00fb"); break; // u circumflex
      case 159: buf.append("\u00fc"); break; // u dieresis
      case 160: buf.append("\u1e6d"); break; // t underdot
      case 161: buf.append("\u00b0"); break; // degree
      case 162: buf.append("\u1ebd"); break; // e tilde
      case 163: buf.append("\u00a3"); break; // sterling
      case 164: buf.append("\u00a7"); break; // section
      case 166: buf.append("\u00b6"); break; // paragraph
      case 167: buf.append("\u015b"); break; // s acute
      case 168: buf.append("\u1e5b"); break; // r underdot
      case 169: buf.append("\u1e67"); break; // s caron
      case 171: buf.append("\u0301"); break; // acute
      case 172: buf.append("\u0308"); break; // dieresis
      case 173: buf.append("\u1e6d"); break; // t underdot
      case 174: buf.append("\u00c6"); break; // AE
      case 175: buf.append("\u014d"); break; // o macron
      case 176: buf.append("\u0129"); break; // i tilde
      case 177: buf.append("\u00b1"); break; // plusminus
      case 180: buf.append("\u012b"); break; // i macron
      case 181: buf.append("\u1e43"); break; // m underdot
      case 182: buf.append("\u1e0d"); break; // d underdot
      case 183: buf.append("\u1e63"); break; // s underdot
      case 185: buf.append("\u017a"); break; // z acute
      case 186: buf.append("\u1e45"); break; // n overdot
      case 189: buf.append("\u0169"); break; // u tilde
      case 190: buf.append("\u00e6"); break; // ae
      case 191: buf.append("\u00f8"); break; // oslash
      case 192: buf.append("\u0304\u0306"); break; // macron breve
      case 194: buf.append("\u1e37"); break; // 
      case 195: buf.append("j\u0305"); break; // j macron [does a single char exist?]
      case 196: buf.append("\u0103"); break; // a breve
      case 197: buf.append("\u016d"); break; // u breve
      case 200: buf.append("\u1e42"); break; // M underdot
      case 201: buf.append("\u2026"); break; // ellipsis
      case 203: buf.append("\u00c0"); break; // A grave
      case 204: buf.append("\u00c3"); break; // A tilde
      case 205: buf.append("\u00d5"); break; // O tilde
      case 206: buf.append("m\u0306"); break; // m breve
      case 207: buf.append("\u0153"); break; // oe
      case 208: buf.append("\u2013"); break; // endash
      case 209: buf.append("\u2014"); break; // emdash
      case 210: buf.append("\u201c"); break; // quotedblleft
      case 211: buf.append("\u201d"); break; // quotedblright
      case 212: buf.append("\u2018"); break; // quoteleft
      case 213: buf.append("\u2019"); break; // quoteright
      case 214: buf.append("\u1e37"); break; // l underring [actually underdot]
      case 215: buf.append("\u1e41"); break; // m overdot
      case 216: buf.append("n\u0306"); break; // n breve
      case 217: buf.append("\u00d7"); break; // multiply
      case 219: buf.append("\u1e5b"); break; // r underring [actually underdot]
      case 220: buf.append("\u1e44"); break; // N overdot
      case 221: buf.append("\u1e62"); break; // S underdot
      case 222: buf.append("\u1e24"); break; // H underdot
      case 223: buf.append("\u1e0c"); break; // D underdot
      case 224: buf.append("\u2021"); break; // daggerdbl
      case 226: buf.append("\u1e36"); break; // L underdot
      case 227: buf.append("\u0113"); break; // e macron
      case 229: buf.append("\u1e5f"); break; // r underbar
      case 230: buf.append("r\u0324"); break; // r underdieresis
      case 231: buf.append("\u012a"); break; // I macron
      case 232: buf.append("\u016b"); break; // u macron
      case 233: buf.append("\u01e6c"); break; // T underdot
      case 234: buf.append("\u1e64"); break; // S acute
      case 235: buf.append("\u2020"); break; // dagger
      case 236: buf.append("\u0115"); break; // e breve
      case 237: buf.append("\u014f"); break; // o breve
      case 238: buf.append("\u0100"); break; // A macron
      case 239: buf.append("\u1e46"); break; // N underdot
      case 241: buf.append("\u1e3b"); break; // l underbar
      case 242: buf.append("\u016a"); break; // U macron
      case 243: buf.append("\u0179"); break; // Z acute
      case 244: buf.append("\u1e5a"); break; // R underdot
      case 245: buf.append("\u0131"); break; // dotlessi
      case 246: buf.append("\u1e47"); break; // n underdot
      case 247: buf.append("\u1e49"); break; // n underbar
      case 248: buf.append("\u0304"); break; // macron
      case 249: buf.append("\u0306"); break; // breve
      case 250: buf.append("\u1e25"); break; // h underdot
      case 251: buf.append("\u012d"); break; // i breve
      case 252: buf.append("\u0327"); break; // cedilla
      case 253: buf.append("\u030b"); break; // hungarumlaut
      case 254: buf.append("\u0328"); break; // ogonek
      case 255: buf.append("\u030c"); break; // caron
      case 130: buf.append("\u012b\u0303"); break; // imacron tilde
      case 165: buf.append("\u1e5d"); break; // runderdot macron
      case 170: buf.append("\u016b\0306"); break; // umacron breve
      case 178: buf.append("\u0101\u0301"); break; // amacron acute
      case 179: buf.append("\u016b\u0301"); break; // umacron acute
      case 184: buf.append("\u0113\u0301"); break; // emacron acute
      case 187: buf.append("\u0113\u0300"); break; // emacron breve
      case 188: buf.append("\u014d\u0300"); break; // omacron breve
      case 193: buf.append("\u0101\u0306"); break; // amacron breve
      case 198: buf.append("\u0101\u0303"); break; // amacron tilde
      case 199: buf.append("\u012b\u0301"); break; // imacron acute
      case 218: buf.append("\u1e00"); break; // runderdotmacron acute
      case 225: buf.append("\u1e5b\u0301"); break; // runderdot acute
      case 228: buf.append("\u012b\u0306"); break; // imacron breve
      case 240: buf.append("\u016b\u0303"); break; // umacron tilde
      default: buf.append(c); break;
      }
    }
    return buf.toString();
  }
  static public String normanToUnicodeNew(String inp) {
    StringBuffer buf = new StringBuffer();
    for (int i = 0; i < inp.length(); i++) {
      char c = inp.charAt(i);
      switch (c) {
        case 1: buf.append("\u00d0"); break; // Eth
        case 2: buf.append("\u00f0"); break; // eth
        case 3: buf.append("\u0141"); break; // Lslash
        case 4: buf.append("\u0142"); break; // lslash
        case 5: buf.append("\u0160"); break; // S caron
        case 6: buf.append("\u0161"); break; // s caron
        case 7: buf.append("\u00dd"); break; // Y acute
        case 8: buf.append("\u00fd"); break; // y acute
        case 11: buf.append("\u00de"); break; // Thorn
        case 12: buf.append("\u00fe"); break; // thorn
        case 14: buf.append("\u017d"); break; // Z caron
        case 15: buf.append("\u017e"); break; // z caron
        case 17: buf.append("\u0073"); break; // asciitilde
        case 18: buf.append("j\u0305"); break; // j macron [does a single char exist?]
        case 19: buf.append("^"); break; // circumflex
        case 20: buf.append("\u0303"); break; // tilde
        case 21: buf.append("\u00bd"); break; // onehalf
        case 22: buf.append("\u00bc"); break; // onequarter
        case 23: buf.append("\u00b9"); break; // onesuperior
        case 24: buf.append("\u00be"); break; // threequarters
        case 25: buf.append("\u00b3"); break; // threesuperior
        case 26: buf.append("\u00b2"); break; // twosuperior
        case 27: buf.append("\u00a6"); break; // brokenbar
        case 28: buf.append("-"); break; // minus
        case 29: buf.append("\u00d7"); break; // multiply
        case 39: buf.append("'"); break; // quotesingle
        case 94: buf.append("\u0302"); break; // circumflex
        case 96: buf.append("\u0300"); break; // grave
        case 196: buf.append("\u00c4"); break; // A dieresis
        case 197: buf.append("\u00c5"); break; // A ring
        case 201: buf.append("\u00c9"); break; // E acute
        case 209: buf.append("\u00d1"); break; // N tilde
        case 214: buf.append("\u00d6"); break; // O dieresis
        case 220: buf.append("\u00dc"); break; // U dieresis
        case 225: buf.append("\u00e1"); break; // a acute
        case 224: buf.append("\u00e0"); break; // a grave
        case 226: buf.append("\u00e2"); break; // a circumflex
        case 228: buf.append("\u00e4"); break; // a dieresis
        case 227: buf.append("\u00e3"); break; // a tilde
        case 229: buf.append("\u0101"); break; // a macron
        case 231: buf.append("\u00e7"); break; // c cedilla
        case 233: buf.append("\u00e9"); break; // e acute
        case 232: buf.append("\u00e8"); break; // e grave
        case 234: buf.append("\u00ea"); break; // e circumflex
        case 235: buf.append("\u00eb"); break; // e dieresis
        case 237: buf.append("\u00ed"); break; // i acute
        case 236: buf.append("\u00ec"); break; // i grave
        case 238: buf.append("\u00ee"); break; // i circumflex
        case 239: buf.append("\u00ef"); break; // i dieresis
        case 241: buf.append("\u00f1"); break; // n tilde
        case 243: buf.append("\u00f3"); break; // o acute
        case 242: buf.append("\u00f2"); break; // o grave
        case 244: buf.append("\u00f4"); break; // o circumflex
        case 246: buf.append("\u00f6"); break; // o dieresis
        case 245: buf.append("\u00f5"); break; // o tilde
        case 250: buf.append("\u00fa"); break; // u acute
        case 249: buf.append("\u00f9"); break; // u grave
        case 251: buf.append("\u00fb"); break; // u circumflex
        case 252: buf.append("\u00fc"); break; // u dieresis
        case 8224: buf.append("\u1e6d"); break; // t underdot
        case 176: buf.append("\u00b0"); break; // degree
        case 162: buf.append("\u1ebd"); break; // e tilde
        case 163: buf.append("\u00a3"); break; // sterling
        case 167: buf.append("\u00a7"); break; // section
        case 182: buf.append("\u00b6"); break; // paragraph
        case 223: buf.append("\u015b"); break; // s acute
        case 174: buf.append("\u1e5b"); break; // r underdot
        case 169: buf.append("\u1e45"); break; // n overdot
        case 180: buf.append("\u0301"); break; // acute
        case 168: buf.append("\u0308"); break; // dieresis
        case 8800: buf.append("\u1e6d"); break; // t underdot
        case 198: buf.append("\u00c6"); break; // AE
        case 216: buf.append("\u014d"); break; // o macron
        case 8734: buf.append("\u0129"); break; // i tilde
        case 177: buf.append("\u00b1"); break; // plusminus
        case 165: buf.append("\u012b"); break; // i macron
        case 181: buf.append("\u1e43"); break; // m underdot
        case 8706: buf.append("\u1e0d"); break; // d underdot
        case 8721: buf.append("\u1e63"); break; // s underdot
        case 960: buf.append("\u017a"); break; // z acute
        case 8747: buf.append("\u1e45"); break; // n overdot
        case 937: buf.append("\u0169"); break; // u tilde
        case 230: buf.append("\u00e6"); break; // ae
        case 248: buf.append("\u00f8"); break; // oslash
        case 191: buf.append("\u0304\u0306"); break; // macron breve
        case 172: buf.append("\u1e37"); break; // 
        case 8730: buf.append("j\u0305"); break; // j macron [does a single char exist?]
        case 402: buf.append("\u0103"); break; // a breve
        case 8776: buf.append("\u016d"); break; // u breve
        case 187: buf.append("\u1e42"); break; // M underdot
        case 8230: buf.append("\u2026"); break; // ellipsis
        case 192: buf.append("\u00c0"); break; // A grave
        case 195: buf.append("\u00c3"); break; // A tilde
        case 213: buf.append("\u00d5"); break; // O tilde
        case 338: buf.append("m\u0306"); break; // m breve
        case 339: buf.append("\u0153"); break; // oe
        case 8211: buf.append("\u2013"); break; // endash
        case 8212: buf.append("\u2014"); break; // emdash
        case 8220: buf.append("\u201c"); break; // quotedblleft
        case 8221: buf.append("\u201d"); break; // quotedblright
        case 8216: buf.append("\u2018"); break; // quoteleft
        case 8217: buf.append("\u2019"); break; // quoteright
        case 247: buf.append("\u1e37"); break; // l underring [actually underdot]
        case 9674: buf.append("\u1e41"); break; // m overdot
        case 255: buf.append("n\u0306"); break; // n breve
        case 376: buf.append("\u00d7"); break; // multiply
        case 8364: buf.append("\u1e5b"); break; // r underring [actually underdot]
        case 8249: buf.append("\u1e44"); break; // N overdot
        case 8250: buf.append("\u1e62"); break; // S underdot
        case 64257: buf.append("\u1e24"); break; // H underdot
        case 64258: buf.append("\u1e0c"); break; // D underdot
        case 8225: buf.append("\u2021"); break; // daggerdbl
        case 8218: buf.append("\u1e36"); break; // L underdot
        case 8222: buf.append("\u0113"); break; // e macron
        case 194: buf.append("\u1e5f"); break; // r underbar
        case 202: buf.append("r\u0324"); break; // r underdieresis
        case 193: buf.append("\u012a"); break; // I macron
        case 203: buf.append("\u016b"); break; // u macron
        case 200: buf.append("\u1e6c"); break; // T underdot
        case 205: buf.append("\u1e64"); break; // S acute
        case 206: buf.append("\u2020"); break; // dagger
        case 207: buf.append("\u0115"); break; // e breve
        case 204: buf.append("\u014f"); break; // o breve
        case 211: buf.append("\u0100"); break; // A macron
        case 212: buf.append("\u1e46"); break; // N underdot
        case 210: buf.append("\u1e3b"); break; // l underbar
        case 218: buf.append("\u016a"); break; // U macron
        case 219: buf.append("\u0179"); break; // Z acute
        case 217: buf.append("\u1e5a"); break; // R underdot
        case 305: buf.append("\u0131"); break; // dotlessi
        case 710: buf.append("\u1e47"); break; // n underdot
        case 732: buf.append("\u1e49"); break; // n underbar
        case 175: buf.append("\u0304"); break; // macron
        case 728: buf.append("\u0306"); break; // breve
        case 729: buf.append("\u1e25"); break; // h underdot
        case 730: buf.append("\u012d"); break; // i breve
        case 184: buf.append("\u0327"); break; // cedilla
        case 733: buf.append("\u030b"); break; // hungarumlaut
        case 731: buf.append("\u0328"); break; // ogonek
        case 711: buf.append("\u030c"); break; // caron
        case 199: buf.append("\u012b\u0303"); break; // imacron tilde
        case 8226: buf.append("\u1e5d"); break; // runderdot macron
        case 8482: buf.append("\u016b\0306"); break; // umacron breve
        case 8804: buf.append("\u0101\u0301"); break; // amacron acute
        case 8805: buf.append("\u016b\u0301"); break; // umacron acute
        case 8719: buf.append("\u0113\u0301"); break; // emacron acute
        case 170: buf.append("\u0113\u0300"); break; // emacron breve
        case 186: buf.append("\u014d\u0300"); break; // omacron breve
        case 161: buf.append("\u0101\u0306"); break; // amacron breve
        case 8710: buf.append("\u0101\u0303"); break; // amacron tilde
        case 171: buf.append("\u012b\u0301"); break; // imacron acute
        case 8260: buf.append("\u1e00"); break; // runderdotmacron acute
        case 183: buf.append("\u1e5b\u0301"); break; // runderdot acute
        case 8240: buf.append("\u012b\u0306"); break; // imacron breve
        case 63743: buf.append("\u016b\u0303"); break; // umacron tilde
        default: buf.append(c); break;
      }
    }
    return buf.toString();
  }


}