/*
 * 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.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintStream;
import java.net.URL;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.StringTokenizer;
import java.util.TreeSet;
import java.util.Vector;

import javax.swing.JDialog;
import javax.swing.JLabel;
import javax.swing.JPanel;

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

class Convert
{
  private static final String _TEMP = "_temp";

  /**
   * Helper class for index creation
   * 
   * @author rogo
   * 
   */
  public static class IndexList extends Vector
  {
    public String toString()
    {
      StringBuffer buff = new StringBuffer(1000);
      int count = 0;
      for (Iterator iter = this.iterator(); iter.hasNext();)
      {
        String element = (String) iter.next();
        if (count < elementCount - 1)
        {
          buff.append(element).append(", ");
          count++;
        } else
          buff.append(element);

      }

      return buff.toString();
    }
  }

  /**
   * Helper class for pre/or post execution of SQLCommands
   * @author rogo
   *
   */
  public static class SQLCommand
  {
    private DBBean bean;
    private String command;

    public SQLCommand(DBBean bean, String command)
    {
      this.bean = bean;
      this.command = command;
    }

    public void executeCommand() throws SQLException, Exception
    {
      System.out.println("Executing command: \n");
      System.out.println(command);

      java.sql.Connection con = bean.getConnection();
      Statement stm = con.createStatement();
      stm.execute(command);
      stm.close();
    }
  }

  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 = 4;

  static boolean debug = false;

  static boolean isGUI = true;

  /**
   * Vector for all SQLCommands to executed before any conversion action is performed
   */
  static Vector preSQLCommands = new Vector();
  /**
   * Vector for all SQLCommands to executed after any conversion action has been performed
   */

  static Vector postSQLCommands = new Vector();

  static final String versionID = new String("FM2SQL Version 0.9.10b\n");

  private static boolean noError = false;

  public static void main(String args[]) throws IOException
  {
    //    DateFormat d = DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.SHORT,
    //        Locale.GERMANY);
    //    System.out.println(convertText("rogoistSuper" + "_" + d.format(new Date()).replace(':', '_')));

    /*
     * 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(); }
     */
    File tmpPath = new File(System.getProperty("java.io.tmpdir"));
    isGUI = false;
    FileOutputStream file = null;
    if (args.length != 1)
    {
      System.out.println(versionID);
      System.out.println("Usage: java Convert <xml config file>");
      System.exit(-1);
    }
    File temp = null;
    try
    {
      temp = new File(tmpPath, "fm2sql.txt");
      int count = 1;
      while (temp.exists())
      {
        temp = new File(tmpPath, "fm2sql" + generateSuffix(count++) + ".txt");
      }
      file = new FileOutputStream(temp);
    } catch (FileNotFoundException e1)
    {
      e1.printStackTrace();
    }
    PrintStream stream = new PrintStream(file, true);
    // write info for user to stdout
    System.out.println(versionID);
    System.out.println("Loading " + args[0] + "....");
    System.out.println("Log  will be written to " + temp.getCanonicalPath());

    if (!debug)
    {
      System.setOut(stream);
      System.setErr(stream);
    }
    System.out.println(versionID);
    System.out.println("Using config file : " + args[0] + "....");

    StringBuffer sb = readXMLFile(args[0]);
    parseXMLConfig(sb);
    if (!(new File(args[0]).exists()))
    {

      System.exit(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);
  }

  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 (isGUI)
    {
      dialog = initDialog();
    }
    // setting user and passwd
    bean.setUserAndPasswd(user, passwd);
    // setting user and passwd
    beanDest.setUserAndPasswd(userDest, passwdDest);

    StringBuffer command = null;
    String query = null;
    try
    {

      bean.setConnection(source);

      if (names == null)
        names = bean.getTableNames();
      // Collections.sort(names);
      int tbIndex = 1;

      System.out.println("Start at table " + names.firstElement());
      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.equals(""))
          {

            query = addLayoutToQuery(names, query, tbIndex, layout);

          }
          if (dialog != null)
          {
            prepareDialogforUse(names, dialog, tbIndex);
          }
          bean.getConnection();
          bean.makeQuery(query, 0);

        } catch (Exception e)
        {
          System.out.println("Warning exception occured \n " + 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(destination);

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

        Vector tables = beanDest.getTableNames();

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

        if (dialog != null)
          dialog.title.setText("Updating table data ...");
        else
          System.out.println("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(); ++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());
          pstm.execute();
          if (dialog != null)
            dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
          command = null;
        } // to for loop

      }
    } catch (Exception e)
    {
      System.out.println("Error while connecting to database " + e);
      if (isGUI)
      {
        showExceptionDialog(dialog, command, e);
      } else
      {
        e.printStackTrace();

      }
    } finally
    {
      if (isGUI)
      {
        resetGUI(dialog);
      }
    }
  }

  /**
   * @param dialog
   */
  private static void resetGUI(FM2SQL.ProgressDialog dialog)
  {
    dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
    FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
    dialog.setVisible(false);
  }

  /**
   * @param dialog
   * @param command
   * @param e
   */
  private static void showExceptionDialog(FM2SQL.ProgressDialog dialog, StringBuffer command,
      Exception 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);
    FM2SQL.showErrorDialog(b.toString(), "Error occured !");
  }

  /**
   * @return
   */
  private static FM2SQL.ProgressDialog initDialog()
  {
    FM2SQL.ProgressDialog dialog;
    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(420, 250);
    return dialog;
  }

  /**
   * 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
  {
    // TODO implement convert_temp mode!!! 
    FM2SQL.ProgressDialog dialog = null;

    if (isGUI)
    {
      dialog = initDialog();
    }

    System.out.println("connection established " + source + " " + bean.url);

    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();
      int tbIndex = 1;

      for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
      {
        Vector[] result = null;
        try
        {
          String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
          query = (selects != null) ? selects.get(tbIndex).toString() : "select * from "
              + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
          if (layout.intern() != "")
          {
            query = addLayoutToQuery(names, query, tbIndex, layout);

          }
          if (dialog != null)
          {
            prepareDialogforUse(names, dialog, tbIndex);
          }

          bean.getConnection();
          bean.makeQuery(query, 50);
          idField = ids.get(tbIndex).toString();

        } catch (Exception e)
        {
          System.out.println("Warning exception occured \n " + 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
        ConversionProperties prop = getFieldNamesAndDestTableName(creates.get(tbIndex).toString(),
            query, names.get(tbIndex).toString());
        destTableName = prop.destTableName;
        if (mode == Convert.DataBase.CONVERT_TEMP_MODE)
        {
          String tableNameSave=destTableName;
          destTableName += _TEMP;
          if(creates.get(tbIndex)!="")
          {
           System.out.println("Changed tablename of create statement to "+destTableName);
            StringBuffer buffer=new StringBuffer(creates.get(tbIndex).toString());
           int startIndex = buffer.indexOf(tableNameSave);
           buffer.replace(startIndex,startIndex+tableNameSave.length(),destTableName);
            creates.set(tbIndex,buffer.toString());
          }
        }
        System.out.println("destTableName is now "+destTableName);
        
        fieldNames = prop.fieldNames;
        if (mode == Convert.DataBase.CONVERT_MODE || mode == Convert.DataBase.CONVERT_TEMP_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 ((tables.indexOf(destTableName) < 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(destTableName);//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("create statement: "+command.toString());
              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);
        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.toLowerCase().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.toLowerCase().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.toLowerCase().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);
          System.err.println("query for whole table done");
          command = writeDatainDestTable(dialog, command, j, pstm, rowCount, delimiter);
          long endTime = System.currentTimeMillis();
          System.out.println("Time for old convert elapsed " + (endTime - startTime));

        }
        if (isGUI)
          resetGUI(dialog);
        noError = true;
        if (mode == Convert.DataBase.CONVERT_TEMP_MODE)
        {
          String originalName = destTableName.split(_TEMP)[0];
          // TODO 
          if (beanDest.getTableNames().contains(originalName))
          {
            DateFormat d = DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.MEDIUM);
            d.setTimeZone(java.util.TimeZone.getTimeZone("CET"));
          
            String date = d.format(new Date());
            date = date.replace(':', '_');
            String newName = addQuotes(beanDest, convertText(originalName + "_" + date));
            // rename table to newName
            renameTable(beanDest, addQuotes(beanDest, originalName), newName);
          }
          // rename Table_temp to originalName
          renameTable(beanDest, addQuotes(beanDest, destTableName), addQuotes(beanDest,
              originalName));
        }
      }
    } catch (Exception e)
    {
      System.out.println("Error while connecting to database " + e.getMessage());
      noError = false;
      if (isGUI)
      {
        showExceptionDialog(dialog, command, e);
        resetGUI(dialog);
      } else
      {
        e.printStackTrace();

      }
    } catch (Error e)
    {
      noError = false;

      System.out.println(e);
      e.printStackTrace();
    }

  }

  /**
   *
   * @param beanDest2
   * @param originalName
   * @return
   */
  private static String addQuotes(DBBean bean, String originalName)
  {
    return bean.getQC() + originalName + bean.getQC();
  }

  /**
   * @param names
   * @param dialog
   * @param tbIndex
   */
  private static void prepareDialogforUse(Vector names, FM2SQL.ProgressDialog dialog, int tbIndex)
  {
    dialog.title.setText("Reading table data ...");
    dialog.table.setText(names.get(tbIndex).toString());
    dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
    dialog.setVisible(true);
  }

  /**
   * @param names
   * @param query
   * @param tbIndex
   * @param layout
   * @return
   */
  private static String addLayoutToQuery(Vector names, String query, int tbIndex, String 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);
    return query;
  }

  /**
   * 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++;
			Object obj = null;
			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();
			if (isGUI)
				dialog.progress
						.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
			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;
    Vector databases = new Vector();
    try
    {
      databases = getXMLConfig(sb);

      // destination DataBase object
      DataBase dbDest = ((DataBase) databases.lastElement());

      DBBean database = ((DataBase) databases.lastElement()).bean;
      databases.remove(databases.size() - 1);

      for (Iterator iterator = dbDest.preCommands.iterator(); iterator.hasNext();)
      {
        SQLCommand sqlCommand = (SQLCommand) iterator.next();
        sqlCommand.executeCommand();
      }

      // databases.add(database);
      for (Iterator iter = databases.iterator(); iter.hasNext();)
      {
        DataBase db = (DataBase) iter.next();
        for (Iterator iterator = db.preCommands.iterator(); iterator.hasNext();)
        {
          SQLCommand sqlCommand = (SQLCommand) iterator.next();
          sqlCommand.executeCommand();
        }
        int mode = db.mode;
        if (mode == DataBase.CONVERT_TEMP_MODE)
        {
          convertBatch(db.bean, database, db.tables, db.layouts, db.selects, db.creates, db.ids,
              mode, db.delimiter);
          if (noError)
          {
            System.out.println("no Error occured ");
            //                db.bean.setURL(database.url);
            //                db.bean.setUserAndPasswd(database.user,database.passwd);
            //
            //                Convert.user = db.bean.user;
            //                Convert.passwd = db.bean.passwd;
            //                userDest = database.user;
            //                passwdDest = database.passwd;
            //                synchronize(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates,
            //                    db.ids, mode, db.delimiter, new Vector(db.htIndex.values()));
          }
        }
        if (mode == DataBase.CONVERT_MODE || mode == DataBase.APPEND_MODE)
          convertBatch(db.bean, database, db.tables, db.layouts, db.selects, db.creates, db.ids,
              mode, db.delimiter);
        else if (mode == DataBase.UPDATE_MODE)
        {

          Convert.user = db.bean.user;
          Convert.passwd = db.bean.passwd;
          userDest = database.user;
          passwdDest = database.passwd;

          update(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids,
              mode);
        } else if (mode == DataBase.SYNCHRONIZE_MODE)
        {
          Convert.user = db.bean.user;
          Convert.passwd = db.bean.passwd;
          userDest = database.user;
          passwdDest = database.passwd;

          synchronize(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates,
              db.ids, mode, db.delimiter, new Vector(db.htIndex.values()), db.destIDs);
        }
        for (Iterator iterator = db.postCommands.iterator(); iterator.hasNext();)
        {
          SQLCommand sqlCommand = (SQLCommand) iterator.next();
          sqlCommand.executeCommand();
        }

      }
      for (Iterator iterator = dbDest.postCommands.iterator(); iterator.hasNext();)
      {
        SQLCommand sqlCommand = (SQLCommand) iterator.next();
        sqlCommand.executeCommand();
      }

    } catch (Exception e)
    {

      e.printStackTrace();

    } finally
    {
      bean.closeAllConnections();
      beanDest.closeAllConnections();
    }
  }

  /**
   *
   * @param database
   * @param db 
   * @throws Exception 
   * @throws SQLException 
   */
  private static void renameTable(DBBean database, String sourceTable, String destTable)
      throws SQLException, Exception
  {
    String command = "alter table " + sourceTable + " rename " + " to " + destTable;
    SQLCommand sqlCommand = new SQLCommand(database, command);
    sqlCommand.executeCommand();
  }

  public static Vector getXMLConfig(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();
    Vector destIDs = new Vector();

    Vector preSQLCommands = new Vector();
    Vector postSQLCommands = 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();
        destIDs = new Vector();

        preSQLCommands = new Vector();
        postSQLCommands = 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 });

        Node commandNodes = root.find("convert/source/database/sqlcommands", new int[]
        { 1, 1, i, 1, 1 });
        if (commandNodes != null)
        {
          parseCommandNode(commandNodes, database, preSQLCommands, postSQLCommands);
        }
        Node useNormanToUnicodeMapper = root.find(
            "convert/source/database/usenormantounicodemapper", new int[]
            { 1, 1, i, 1, 1 });

        if (delimiterNode != null)
          delimiter = delimiterNode.getCharacters();
        if (useNormanToUnicodeMapper != null)
        {
          database.setUseNormanToUnicodeMapper(Boolean.valueOf(
              useNormanToUnicodeMapper.getCharacters()).booleanValue());
          System.out.println("useMapper "
              + Boolean.valueOf(useNormanToUnicodeMapper.getCharacters().trim()).booleanValue());
        }

        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;

        else if (modeString.equals("synchronize"))
          mode = DataBase.SYNCHRONIZE_MODE;
        else if (modeString.equals("convert_temp"))
          mode = DataBase.CONVERT_TEMP_MODE;

        System.out.println("mode ist "+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");
          String destID = (String) node4.attributes.get("targetID");
          System.out.println("id was " + id);
          System.out.println("targetID was " + destID);

          if (name == null)
            throw new Error("parse error required table tag attribute name missing");
          if (layout == null)
            layout = "";
          if (id == null)
            id = "";
          if (destID == null)
            destID = id ;

          if (name.equals(""))
            throw new Error("parse error table tag attribute must not be empty");
          tables.add(name.intern());
          layouts.add(layout.intern());
          ids.add(id.intern());
          destIDs.add(destID.intern());
          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;
        dataBase.preCommands = new Vector(preSQLCommands);
        dataBase.postCommands = new Vector(postSQLCommands);
        dataBase.destIDs = destIDs;
        databases.add(dataBase);
      }
      DBBean database = new DBBean();

      preSQLCommands.clear();
      postSQLCommands.clear();

      // 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 });
      Node commandNodes = root.find("convert/destination/database/sqlcommands", new int[]
      { 1, 1, 1, 1, 1 });
      if (commandNodes != null)
      {
        parseCommandNode(commandNodes, database, preSQLCommands, postSQLCommands);
      }

      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());
      DataBase db = new DataBase(database, new Vector(), null, null, null, null, 0);
      databases.add(db);
      db.preCommands = new Vector(preSQLCommands);
      db.postCommands = new Vector(postSQLCommands);

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

  /**
   *
   * @param commandNode
   * @param database
   * @param preSQLCommands 
   * @param postSQLCommand 
   */
  private static void parseCommandNode(Node commandNode, DBBean database,
      java.util.Vector preSQLCommands, java.util.Vector postSQLCommands)
  {
    // System.out.println(commandNode.name + " " + countNodes(commandNode));
    int numCommands = commandNode.contents.length();
    for (int j = 0; j < numCommands; ++j)
    {
      Node node = (Node) commandNode.contents.v.elementAt(j);
      if (node.type.equals("element"))
      {
        // System.out.println(node.name + " " + node.getCharacters() + database);
        String execute = node.attributes.get("execute").toString();
        if (execute.equals("before"))
        {
          preSQLCommands.add(new SQLCommand(database, node.getCharacters()));
        }
        if (execute.equals("after"))
        {
          postSQLCommands.add(new SQLCommand(database, node.getCharacters()));
        }

      }

    }
  }

  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++;
        if (node.name.equals("sqlcommand"))
          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 StringBuffer readXMLFile(String xmlFile)
  {
    InputStream stream = null;
    StringBuffer sb = new StringBuffer();

    try
    {

      if (xmlFile.indexOf("file://") >= 0 || xmlFile.indexOf("http://") >= 0)
      {
        URL url = new URL(xmlFile);
        stream = url.openStream();
      } else
        // read XML Metadata from a file
        stream = new FileInputStream(xmlFile);
      InputStreamReader isr = new InputStreamReader(stream, "UTF-8");
      BufferedReader buffr = new BufferedReader(isr);
      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();
    } finally
    {

      try
      {
        stream.close();
      } catch (IOException e1)
      {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }
    }
    return sb;
  }

  /**
   * Helper class for Conversion etc. Contains data needed for the conversion
   * 
   * @author rogo
   * 
   */

  public static class ConversionProperties
  {
    String destTableName;

    String[] fieldNames;

    public ConversionProperties()
    {
    }

    public ConversionProperties(String destTableName, String[] fieldNames)
    {
      this.destTableName = destTableName;
      this.fieldNames = fieldNames;

    }

  }

  /**
   * 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 = new Vector();

    Vector ids;
    Vector destIDs;

    String delimiter = "//";

    Vector preCommands;
    Vector postCommands;

    /**
     * maps table name to index fields
     */
    Hashtable htIndex = new Hashtable();

    boolean useNormanToUnicodeMapper = false;

    final static int CONVERT_MODE = 1;

    final static int APPEND_MODE = 2;

    final static int UPDATE_MODE = 3;

    final static int DELETE_MODE = 4;

    final static int SYNCHRONIZE_MODE = 5;

    final static int CONVERT_TEMP_MODE = 6;

    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);
    }

    /**
     * @param indexListVec
     */
    public void buildIndexTable(Vector indexListVec)
    {
      for (int i = 0; i < tables.size(); i++)
      {
        fillIndexList((String) tables.get(i), (String) indexListVec.get(i));
      }
    }

    /**
     * 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_TEMP_MODE)
        modeString = "convert_temp";
      else 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";
      else if (mode == SYNCHRONIZE_MODE)
        modeString = "synchronize";

      buffr.write("      <mode>" + modeString + "</mode>\n");
      buffr.write("      <usenormantounicodemapper>" + useNormanToUnicodeMapper
          + "</usenormantounicodemapper>\n");
      if (preCommands != null || postCommands != null)
      {
        int count = 0;

        buffr.write("      <sqlcommands> \n");

        if (preCommands != null)
        {
          while (count < preCommands.size())
          {
            SQLCommand sqlcommand = (SQLCommand) preCommands.get(count);
            buffr.write("        <sqlcommand execute=\"before\">" + sqlcommand.command
                + "</sqlcommand>\n");
            count++;
          }
        }
        if (postCommands != null)
        {
          count = 0;
          while (count < postCommands.size())
          {
            SQLCommand sqlcommand = (SQLCommand) postCommands.get(count);

            buffr.write("        <sqlcommand execute=\"after\">" + sqlcommand.command
                + "</sqlcommand>\n");
            count++;
          }
        }
        buffr.write("      </sqlcommands> \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);
        IndexList indexList = (IndexList) htIndex.get(table);
        if (indexList == null)
          indexList = new IndexList();
        buffr.write("      <table name = \"" + table + "\" layout = \"" + layout + "\" id = \""
            + id + "\" indexList =\"" + indexList + "\">\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 void fillIndexList(String table, String list)
    {
      IndexList indexList = new IndexList();
      StringTokenizer tokenizer = new StringTokenizer(list, ",");
      while (tokenizer.hasMoreTokens())
      {
        indexList.add(tokenizer.nextToken());
      }
      System.out.println(indexList);

      htIndex.put(table, indexList);
    }

    public String toString()
    {
      return bean.url + " " + tables;
    }

  }

  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 (isGUI)
    {
      dialog = initDialog();
    }
    // setting user and passwd
    bean.setUserAndPasswd(user, passwd);
    // setting user and passwd
    beanDest.setUserAndPasswd(userDest, passwdDest);
    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.setVisible(true);
          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(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);
            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 (isGUI)
                dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
              command = null;
            }
            // prepare new query for next chunk
            if (query.toLowerCase().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 (isGUI)
      {
        showExceptionDialog(dialog, command, e);
        resetGUI(dialog);
      } else
      {
        e.printStackTrace();
      }
    }
  } // to method

  /**
   * synchronize method based on delete method code
   * @param source
   * @param destination
   * @param names
   * @param layouts
   * @param selects
   * @param creates
   * @param ids
   * @param mode
   * @param destIDs TODO
   * 
   * @throws Exception
   */
  // TODO implement append,update and delete in one method
  // TODO using id based algorithm
  public static void synchronize(String source, String destination, Vector names, Vector layouts,
      Vector selects, Vector creates, Vector ids, int mode, String delimiter, Vector indexList, java.util.Vector destIDs)
      throws Exception
  {
    System.out.println(" bin in synchronize!!!");
    FM2SQL.ProgressDialog dialog = null;
    if (isGUI)
    {
      dialog = initDialog();
      dialog.setTitle("Synchronize running ...");

    }
    // setting user and passwd
    bean.setUserAndPasswd(user, passwd);
    // setting user and passwd
    beanDest.setUserAndPasswd(userDest, passwdDest);
    StringBuffer command = null;
    String query = null;
    try
    {
      bean.setConnection(source);
      if (names == null)
        names = bean.getTableNames();
      int tbIndex = 1;

      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 destIDField = "";
        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.equals(""))
          {
            query = addLayoutToQuery(names, query, tbIndex, layout);

          }
          if (dialog != null)
          {
            prepareDialogforUse(names, dialog, tbIndex);
          }
          bean.getConnection();
          bean.makeQuery(query, 50);
          idField = ids.get(tbIndex).toString();
          destIDField = (destIDs!=null) ? destIDs.get(tbIndex).toString():convertText(idField); 
        } catch (Exception e)
        {
          System.out.println("Warning exception occured \n " + 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);
        destQuery = destQuery.replaceAll(bean.getQC(), beanDest.getQC());
        destQuery = removeLayoutPartFromQuery(destQuery, layouts.get(tbIndex).toString());
        // TODO remove layout part for destQuery
        String tempQueryDest = destQuery;
        // remove extra query parts
        // destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
        System.out.println("new Query " + tempQueryDest);
        System.out.println("idfield " + idField + " " + ids.get(tbIndex).toString()+" targetID is "+destIDField);
        if (!idField.equals(""))
        {
          long startTime = System.currentTimeMillis();
          int counter = -1;
          TreeSet linesToDelete = null;
          PreparedStatement delPSt = null;
          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, 0);
            myIdsDest = beanDest.getIDVector(destIDField, destTableName, tempQueryDest, 0);
            // System.out.println("status of remove
            // "+myIds.remove("b015892"));
            System.out.println("ids found for " + idField + " " + !myIds.isEmpty());
            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);

            TreeSet linesToAppend = new TreeSet(vec);
 //           linesToAppend.addAll(vec);
            linesToDelete = new TreeSet(vecDest);
            // remove all lines that are already in dest database
            linesToAppend.removeAll(vecDest);
            // remove all lines that should not be deleted
            linesToDelete.removeAll(deleted);
            System.out.println("linesToAppend " + linesToAppend.size() + " " + destTableName);
            System.out.println("linesToDelete " + linesToDelete.size() + " " + destTableName);
            System.out.println("ID LIST SIZE "
                + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIds.size());
            deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
            ConversionProperties prop = getFieldNamesAndDestTableName(creates.get(tbIndex)
                .toString(), query, names.get(tbIndex).toString());
            StringBuffer insCommand = createInsertCommand(prop.destTableName, prop.fieldNames);
            StringBuffer updCommand = createUpdateCommand(prop.destTableName, prop.fieldNames,
                tempIDdest);
            StringBuffer delCommand = createDeleteCommand(destTableName, tempIDdest);
            PreparedStatement insPst = beanDest.getConnection().prepareStatement(
                insCommand.toString());
            PreparedStatement updPst = beanDest.getConnection().prepareStatement(
                updCommand.toString());
            delPSt = beanDest.getConnection().prepareStatement(delCommand.toString());
            // delPSt.setString(1,"b015892");
            // delPSt.execute();
            // if (true)
            // return;
            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.toLowerCase().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 ...");

              performSynchronize(idField, vec, tempQuery, linesToDelete, linesToAppend, insPst,
                  updPst, delPSt, deltaID, delimiter, dialog);
              // System.out.println("ID LIST SIZE " +
              // Math.round((double) myIds.size() / (double)
              // numIntervalls) + " " + myIdsDest.size());
              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.toLowerCase().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 ...");
              performSynchronize(idField, vec, tempQuery, linesToDelete, linesToAppend, insPst,
                  updPst, delPSt, deltaID, delimiter, dialog);
              // System.out.println("ID LIST SIZE " +
              // Math.round((double) myIds.size() / (double)
              // numIntervalls) + " " + myIdsDest.size());
            }
            // prepare new query for next chunk
            if (query.toLowerCase().indexOf("where") > 0)
              tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
            else
              tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";

          }
          String tableName = names.get(tbIndex).toString();
          if (!indexList.isEmpty())
          {
            IndexList idList = (IndexList) indexList.get(0);
            System.out.println("found list " + idList);
            Statement stm = beanDest.getConnection().createStatement();
            Vector destTables = beanDest.getTableNames();
            System.out.println("tempQueryDest" + tempQueryDest);
            beanDest.makeQuery(tempQueryDest, 0);
            for (Iterator iter = idList.iterator(); iter.hasNext();)
            {
              String indexField = (String) iter.next();
              indexField = convertText(indexField);
              String indexName = destTableName + "_" + indexField;
              if (destTables.contains(indexName))
              {
                stm.execute("DROP  INDEX " + destTableName + "_" + indexField);
                // continue;
              }
              // stm.execute("DROP INDEX
              // "+destTableName+"_"+indexField);

              String type = beanDest.getColumnType(indexField).toLowerCase();
              // System.out.println(indexField+" "+type+"
              // "+(type.indexOf("text") >= 0 ||
              // type.indexOf("varchar") >= 0 || type.indexOf("char")
              // >= 0));
              if (type.indexOf("text") >= 0 || type.indexOf("varchar") >= 0
                  || type.indexOf("char") >= 0)
              {
                if (beanDest.url.indexOf("mysql") >= 0)
                {
                  // System.out.println("CREATE INDEX " +
                  // indexName + " ON " + destTableName + " (" +
                  // indexField + "(10))");
                  // TODO problem if index exist !!!
                  stm.execute("CREATE  INDEX " + indexName + " ON " + destTableName + " ("
                      + indexField + "(10))");
                } else
                {
                  stm.execute("CREATE  INDEX " + indexName + " ON " + destTableName + " (lower( "
                      + indexField + "))");

                }

              } else
              {
                stm.execute("CREATE  INDEX " + destTableName + "_" + indexField + " ON "
                    + destTableName + "(" + indexField + ")");

              }

              // stm.execute("DROP INDEX
              // "+destTableName+"_"+indexField);

            }
          }
          // CREATE UNIQUE INDEX title_idx ON films (title);
          for (Iterator iter = linesToDelete.iterator(); iter.hasNext();)
          {
            String id = iter.next().toString();
            delPSt.setString(1, id);
            delPSt.execute();

          }

          long endTime = System.currentTimeMillis();
          System.out.println("Time for incremental synchronize  elapsed " + (endTime - startTime));
        } // to idfield if
      } // table loop

    } catch (Exception e)
    {
      System.out.println("Error while connecting to database " + e);
      e.printStackTrace();
      if (isGUI)
        showExceptionDialog(dialog, command, e);
    }
    if (isGUI)
    {
      resetGUI(dialog);
    }
  }

  /**
   * @param destQuery
   * @param string
   * @return
   */
  private static String removeLayoutPartFromQuery(String destQuery, String layoutName)
  {
    String removeString = "layout " + beanDest.getQC() + layoutName + beanDest.getQC();
    destQuery = destQuery.replaceFirst(removeString, "");
    System.out.println("destQuery change to " + destQuery);
    return destQuery;
  }

  private static void performSynchronize(String idField, Vector vec, String tempQuery,
      TreeSet linesToDelete, TreeSet linesToAppend, PreparedStatement insPst,
      PreparedStatement updPst, PreparedStatement delPSt, int deltaID, String delimiter,
      FM2SQL.ProgressDialog dialog) throws SQLException, ParseException, Exception
  {
    if (dialog != null)
    {
      dialog.progress.setValue(0);
      dialog.title.setText("Retrieving new data");
    }

    Vector[] vectors = bean.getQueryData(tempQuery, 0);
    int count = 0, size = vectors[0].size();
    int idIndex = vectors[1].indexOf(idField);
    // System.out.println(idIndex + " " + vectors[1] + " " + idField);
    // todo arraylist code has to be added
    if (dialog != null)
      dialog.title.setText("Synchronize with new data");

    for (Iterator iter = vectors[0].iterator(); iter.hasNext();)
    {
      Vector line = (Vector) iter.next();
      Object lineIDIndex = line.get(idIndex);
      if (linesToAppend.contains(lineIDIndex))
        System.out.println("line " + linesToAppend.contains(lineIDIndex) + " " + lineIDIndex);
      if (linesToAppend.contains(lineIDIndex))
      {
        for (int l = 0; l < line.size(); ++l)
        {
          Object obj = line.get(l);
          if (obj instanceof ArrayList)
            obj = formatFileMakerArray((List) obj, delimiter);
          if (obj != null)
            insPst.setString(l + 1, obj.toString());
          else
            insPst.setNull(l + 1, Types.NULL);
        }
        insPst.execute();

      } else
      // update
      {
        for (int l = 0; l < line.size(); ++l)
        {
          Object obj = line.get(l);
          if (obj instanceof ArrayList)
            obj = formatFileMakerArray((List) obj, delimiter);
          if (obj != null)
            updPst.setString(l + 1, obj.toString());
          else
            updPst.setNull(l + 1, Types.NULL);
        }
        updPst.setString(line.size() + 1, line.get(idIndex).toString());
        // updPst.addBatch();
       // System.out.println("updating "+line.get(idIndex).toString()+" "+line);
        updPst.execute();
      }
      if (dialog != null)
      {
        int value = (int) Math.round(((double) count / (double) size) * 100.0);
        dialog.progress.setValue(value);
        count++;
      }
    }
    // updPst.executeBatch();
  } // 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();
  }

  public static ConversionProperties getFieldNamesAndDestTableName(String create, String query,
      String tableName)
  {
    String[] fieldNames = null;
    String destTableName = null;
    // determine destTableName from createStatement or from source table
    // name
    if (!create.equals(""))
    {
      int fromIndex = create.toLowerCase().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
      // TODO problem with different fieldNames in create statement will
      // overwrite them
      if (query.indexOf("*") < 0 && create.equals(""))// quick hack for hartmut
      {
        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(tableName);

      // retrieve field_names from select statement
      if (query.indexOf("*") < 0)
      {
        int selectEndIndex = query.lastIndexOf("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();
          String text = convertText(fieldName);
          if (text.indexOf("\"") >= 0)
            fieldNames[fieldIndex] = convertText(fieldName);
          else
            fieldNames[fieldIndex] = beanDest.getQC() + convertText(fieldName) + beanDest.getQC();
          // 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] = beanDest.getQC() + convertText(element) + beanDest.getQC();
          // System.out.println("field " + fieldNames[fieldIndex]);
        }
      }
    }
    return new ConversionProperties(destTableName, fieldNames);
  }

  /**
   * creates an insert into statement for the specified table and given field
   * names
   * 
   * @param destTableName
   * @param fieldNames
   * @return
   */
  public static StringBuffer createInsertCommand(String destTableName, String[] fieldNames)
  {
    StringBuffer 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 < fieldNames.length - 1; ++i)
      command.append("?,");
    command.append("?)");
    return command;
  }

  public static StringBuffer createUpdateCommand(String destTableName, String[] fieldNames,
      String id)
  {
    StringBuffer command = new StringBuffer();

    command.append("UPDATE ");
    command.append(beanDest.getQC());
    command.append(destTableName);
    command.append(beanDest.getQC());
    command.append(" SET  ");

    int size = bean.getColumnNames().size();
    for (int i = 0; i < size - 1; ++i)
      command.append(fieldNames[i] + " = ? ,");
    command.append(fieldNames[size - 1] + " = ? ");
    command.append("WHERE " + id + " =  ?");
    return command;
  }

  public static StringBuffer createDeleteCommand(String destTableName, String idField)
  {
    StringBuffer 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());
    command.append("WHERE " + idField + " =  ?");
    return command;
  }

  public void makeTest(String table, String idField, String tempQuery) throws Exception
  {
    int counter = 0;

    // ****** test code *****

    bean.getConnection();
    ResultSet resultSet = null;
    String lastResult = "P227634.11";// "P227625.79554";//"P227625.77391";//"P116034.970998";
    String myQuery = "select " + bean.getQC() + idField + bean.getQC() + ",serial " + " from "
        + bean.getQC() + table + bean.getQC();
    System.out.println("Query is now " + myQuery);
    JDialog statusDialog = new JDialog();
    statusDialog.setTitle("Status Information");
    JLabel status = new JLabel("actual DataSet : ");
    JLabel status2 = new JLabel(Integer.toString(++counter));
    JLabel status3 = new JLabel(lastResult);

    JPanel statusPanel = new JPanel();
    JPanel statusPanel2 = new JPanel();
    statusPanel.add(status);
    statusPanel.add(status2);
    statusPanel2.add(status3);
    statusDialog.getContentPane().add(statusPanel, "North");
    statusDialog.getContentPane().add(statusPanel2, "Center");
    statusDialog.setLocation(400, 500);
    statusDialog.setSize(300, 150);
    statusDialog.setVisible(true);
    while (true)
    {
      if (!statusDialog.isVisible())
        statusDialog.setVisible(true);
      tempQuery = myQuery + " where " + bean.getQC() + idField + bean.getQC() + ">'" + lastResult
          + "'";
      resultSet = bean.makeQuery(tempQuery, 1);
      if (resultSet == null)
      {
        System.out.println("lastResult was " + lastResult + " counter was " + counter);
        break;
      } else
      {
        resultSet.next();
        lastResult = resultSet.getString(1);
        counter++;
        status2.setText(Integer.toString(counter));
        status3.setText(lastResult + " " + resultSet.getString(2));
        if (counter % 100 == 0)
        {
          System.out.println("actual Result was " + lastResult + " counter was " + counter);
          // break;
        }
      }
      resultSet = null;
    }
    System.exit(0);

    // ****** end Test ******

  }

  public final static String generateSuffix(final int step)
  {
    String fileString = null;
    if (step < 10)
      fileString = "00" + step;
    else if (step < 100)
      fileString = "0" + step;
    else
      fileString = step + "";
    return fileString;
  }

}