/*
 * 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.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintStream;
import java.io.UnsupportedEncodingException;
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.ParseException;
import java.util.ArrayList;
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
{
	/**
	 * 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();
		}
	}

	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;

	public static void main(String args[])
	{
		/*
		 * try { //byte[] b = "ö".getBytes("UTF-8"); //
		 * System.out.println("QueryString " +b[0]+" "+b[1]+(new
		 * String(b).getBytes()[0])+" "+new String(b).getBytes()[1]);
		 * //System.out.println(new String(b,"UTF-8")); } catch
		 * (UnsupportedEncodingException e) { e.printStackTrace(); }
		 */
		FileOutputStream file = null;
		if (args.length != 1)
		{
			System.out.println("Usage: java Convert <xml config file>");
			System.exit(-1);
		}
		try
		{
			file = new FileOutputStream("./log.txt");
		}
		catch (FileNotFoundException e1)
		{
			e1.printStackTrace();
		}
		PrintStream stream = new PrintStream(file, false);
		if (!debug)
		{
			System.setOut(stream);
			System.setErr(stream);
		}
		readXMLFile(args[0]);
		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);
		if (true)
			return;
		StringBuffer command = null;
		try
		{
			bean.setConnection(source.url);
			if (names == null)
				names = bean.getTableNames();
			//Collections.sort(names);
			int tbIndex = 1;

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

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

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

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

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

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

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

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

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

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

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

				} // to for loop

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

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

		//  dialog.setVisible(false);
	}

	public static String formatFileMakerArray(List list, String delimiter)
	{
		StringBuffer formattedString = new StringBuffer();
		for (int i = 0; i < list.size(); ++i)
		{
			formattedString.append(list.get(i).toString());
			if (i < list.size() - 1)
				formattedString.append(delimiter);
		}
		return formattedString.toString();
	}

	/**
	 * Method for SQL UPDATE
	 * 
	 * @param source
	 * @param destination
	 * @param names
	 * @param layouts
	 * @param selects
	 * @param creates
	 * @param ids
	 * @param mode
	 * @throws Exception
	 */
	public static void update(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
	{

		FM2SQL.ProgressDialog dialog = null;
		if (FM2SQL.fmInstance != null)
		{
			dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
			dialog.setTitle("Conversion running ...");
			dialog.title.setText("Getting table data ...");
			dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
			dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
			FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
			dialog.thread = Thread.currentThread();
		}
		// setting user and passwd
		bean.setUserAndPasswd(user, passwd);
		// setting user and passwd
		beanDest.setUserAndPasswd(userDest, passwdDest);
		if (dialog != null)
			dialog.setSize(400, 250);
		StringBuffer command = null;
		String query = null;
		try
		{
			//bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
			//bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
			bean.setConnection(source);
			if (names == null)
				names = bean.getTableNames();
			// Collections.sort(names);
			int tbIndex = 1;

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

					}
					if (dialog != null)
					{
						dialog.title.setText("Getting table data ...");
						dialog.table.setText(names.get(tbIndex).toString());
						dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
						dialog.show();
					}
					bean.getConnection();
					bean.makeQuery(query, 0);

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

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

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

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

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

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

				int j = -1;

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

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

				int size = bean.getColumnNames().size();
				for (int i = 0; i < size - 1; ++i)
					command.append(beanDest.getQC() + convertText((String) bean.getColumnNames().get(i)) + beanDest.getQC() + " = ? ,");
				command.append(convertText((String) bean.getColumnNames().get(size - 1)) + " = ? ");
				command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " =  ?");
				PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
				System.out.println(command + " " + tbIndex);
				int rowCount = bean.getRowCount(query);
				int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
				while ((row = bean.getNextRow()) != null)
				{
					j++;
					//print rows
					Object obj = null;
					/*
					 * for(int k=0;k <row.size()-1;++k) { obj = row.get(k);
					 * //System.out.println("row "+obj+" "+k); if(obj!=null&&!(obj
					 * instanceof ArrayList))
					 * command.append("'"+convertUml(obj.toString())+"',"); else
					 * if(obj!=null&& obj instanceof ArrayList)
					 * command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
					 * else command.append("NULL,"); } obj = row.get(row.size() - 1); if
					 * (obj != null && !(obj instanceof ArrayList))
					 * command.append("'"+convertUml(obj.toString())+"')"); else
					 * if(obj!=null&& obj instanceof ArrayList)
					 * command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')");
					 * //command.append(obj.toString()+")"); else command.append("NULL)");
					 */
					//command.append("'"+row.get(row.size()-1)+"')");
					//command.append(" )");
					//  for(int k=0;k<row.size();++k)
					// System.out.println();
					//   System.out.println(command+" "+j+" "+row.size()+" "+
					// ((Vector)result2[0].get(j)).size());
					// System.out.println(command);
					for (int k = 0; k < row.size(); ++k)
					{
						obj = row.get(k);
						if (obj instanceof ArrayList)
							obj = ((List) obj).get(0);
						String str = (obj == null) ? "NULL" : obj.toString();
						if (!str.equals("NULL"))
							pstm.setString(k + 1, str);
						else
							pstm.setNull(k + 1, Types.NULL);
					}
					pstm.setString(row.size() + 1, row.get(idIndex).toString());
					//System.out.println(pstm.toString());
					// System.exit(0);
					pstm.execute();
					//stm.executeUpdate(command.toString());
					if (dialog != null)
						dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
					// System.out.println(
					// (int)(((double)(j+1)/(double)result[0].size())*100.0)+"
					// "+result[0].size()+" "+j);
					command = null;
				} // to for loop

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

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

			dialog.setVisible(false);
		}

	}

	/**
	 * transfers the specified array of tables to the destination database and
	 * creates the table if it does not exist if it exists and mode is not append
	 * the table is dropped
	 * 
	 * @param source
	 * @param destination
	 * @param names
	 * @param layouts
	 * @param selects
	 * @param creates
	 * @param ids
	 * @param mode
	 * @throws Exception
	 */

	public static void convert(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode, String delimiter) throws Exception
	{

		FM2SQL.ProgressDialog dialog = null;

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

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

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

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

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

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

				// determine destTableName from createStatement or from source
				// table name
				ConversionProperties prop = getFieldNamesAndDestTableName(creates.get(tbIndex).toString(), query, names.get(tbIndex).toString());
				destTableName = prop.destTableName;
				fieldNames = prop.fieldNames;
				if (mode == Convert.DataBase.CONVERT_MODE)
				{

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

					}
					/*
					 * if(destTableName.equals("")) if (tables.indexOf(names.get(tbIndex)) >=
					 * 0) { stm.executeUpdate("drop table " + beanDest.getQC() +
					 * names.get(tbIndex) + beanDest.getQC()); tables.remove((String)
					 * names.get(tbIndex)); System.out.println("dropped table" +
					 * names.get(tbIndex)); } else if
					 * (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0) {
					 * stm.executeUpdate("drop table " + beanDest.getQC() +
					 * convertText((String) names.get(tbIndex)) + beanDest.getQC());
					 * tables.remove(convertText((String) names.get(tbIndex)));
					 * System.out.println("dropped table" + names.get(tbIndex)); }
					 */
					if ((tables.indexOf(destTableName) < 0)) //&&
					// tables.indexOf(names.get(tbIndex))
					// < 0 &&
					// tables.indexOf(convertText(names.get(tbIndex).toString()))
					// < 0 )
					{

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

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

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

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

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

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

				command.append("INSERT  INTO ");
				command.append(beanDest.getQC());
				command.append(destTableName); //convertText((String)
				// names.get(tbIndex)));
				command.append(beanDest.getQC());
				command.append(" (");
				for (int i = 0; i < fieldNames.length; i++)
				{
					command.append(fieldNames[i]);
					if (i < fieldNames.length - 1)
						command.append(",");
				}
				command.append(") ");

				command.append(" values ( ");

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

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

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

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

					}
					long endTime = System.currentTimeMillis();
					System.out.println("Time for incremental convert elapsed " + (endTime - startTime));
				}
				else
				{
					// read and write all in one big chunk
					long startTime = System.currentTimeMillis();

					bean.makeQuery(query, 0);
					command = writeDatainDestTable(dialog, command, j, pstm, rowCount, delimiter);
					long endTime = System.currentTimeMillis();
					System.out.println("Time for old convert elapsed " + (endTime - startTime));

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

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

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

				if (obj instanceof ArrayList)
					obj = formatFileMakerArray((List) obj, delimiter);

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

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

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

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

					}

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

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

			++j;
		}
		return alterMe.toString();
	}

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

		while (j < length)
		{

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

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

			}
			++j;
		}
		return alterMe.toString();
	}

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

		while (j < length)
		{

			if (alterMe.charAt(j) == '\'')
			{
				alterMe.setCharAt(j, '\\');
				alterMe.insert(j + 1, "'");
				length = length + 1;
				if (j < length - 1)
					j = j + 1;
			}
			/*
			 * else if (alterMe.charAt(j) == '"') { alterMe.setCharAt(j, '\\');
			 * alterMe.insert(j + 1, "\""); length = length + 1; if(j <length-1)
			 * j=j+1; } else if (alterMe.charAt(j) == '>') { alterMe.setCharAt(j,
			 * '\\'); alterMe.insert(j + 1, ">"); length = length + 1; if(j <length-1)
			 * j=j+1; } else if (alterMe.charAt(j) == ' <') { alterMe.setCharAt(j,
			 * '\\'); alterMe.insert(j + 1, " <"); length = length + 1; if(j
			 * <length-1) j=j+1; } else if (alterMe.charAt(j) == '?') {
			 * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "?"); length = length +
			 * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == '&') {
			 * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "&"); length = length +
			 * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == '=') {
			 * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "="); length = length +
			 * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == ',') {
			 * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, ","); length = length +
			 * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == '.') {
			 * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "."); length = length +
			 * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == '[') {
			 * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "."); length = length +
			 * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == ']') {
			 * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "."); length = length +
			 * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == '%') {
			 * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "%"); length = length +
			 * 1; if(j <length-1) j=j+1; }
			 */
			++j;
		}
		return alterMe.toString();
	}

	/**
	 * parses the input xml file for batch conversion called from readXMLFile *
	 * 
	 * @param sb
	 */
	public static void parseXMLConfig(StringBuffer sb)
	{
		boolean finished = false;
		// parse string and build document tree
		Xparse parser = new Xparse();
		parser.changeEntities = true;
		Node root = parser.parse(sb.toString());
		// printContents(root);
		Vector databases = new Vector();
		Vector tables = new Vector();
		Vector layouts = new Vector();
		Vector selects = new Vector();
		Vector creates = new Vector();
		Vector ids = new Vector();
		Vector indexListVec = 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();
				indexListVec = 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 useNormanToUnicodeMapper = root.find("convert/source/database/usenormantounicodemapper", new int[]
				{1, 1, i, 1, 1});

				if (node3 == null)
					throw new Error("parse error database tag missing");
				if (node == null)
					throw new Error("parse error url tag missing");
				if (node1 == null)
					throw new Error("parse error user tag missing");
				if (node2 == null)
					throw new Error("parse error password tag missing");
				if (delimiterNode != null)
					delimiter = delimiterNode.getCharacters();
				if (useNormanToUnicodeMapper != null)
				{
					database.setUseNormanToUnicodeMapper(Boolean.valueOf(useNormanToUnicodeMapper.getCharacters()).booleanValue());
					System.out.println("useMapper " + Boolean.valueOf(useNormanToUnicodeMapper.getCharacters().trim()).booleanValue());
				}

				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;

				//   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 indexList = (String) node4.attributes.get("indexList");

					System.out.println("id was " + id);
					if (name == null)
						throw new Error("parse error required table tag attribute name missing");
					if (layout == null)
						layout = "";
					if (id == null)
						id = "";
					if (indexList == null)
						indexList = "";
					if (name.equals(""))
						throw new Error("parse error table tag attribute must not be empty");
					tables.add(name);
					layouts.add(layout);

					ids.add(id);
					indexListVec.add(indexList);
					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.buildIndexTable(indexListVec);
				databases.add(dataBase);
			}
			DBBean database = new DBBean();
			// parse dataBase
			Node node = root.find("convert/destination/database/url", new int[]
			{1, 1, 1, 1});
			Node node1 = root.find("convert/destination/database/user", new int[]
			{1, 1, 1, 1, 1});
			Node node2 = root.find("convert/destination/database/password", new int[]
			{1, 1, 1, 1, 1});
			String url = node.getCharacters();
			String user = node1.getCharacters();
			String password = node2.getCharacters();
			System.out.println(url);
			database.setURL(url.trim());
			database.setUserAndPasswd(user.trim(), password.trim());
			//databases.add(database);
			for (Iterator iter = databases.iterator(); iter.hasNext();)
			{
				DataBase db = (DataBase) iter.next();
				if (mode == DataBase.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()));
				}
			}
			// printContents(node3);
			//   FM2SQL.fmInstance=new FM2SQL();
		}
		catch (Exception e)
		{

			e.printStackTrace();

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

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

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

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

				DBBean database = new DBBean();
				tables = new Vector();
				layouts = new Vector();
				selects = new Vector();
				creates = new Vector();
				ids = new Vector();
				// parse dataBase
				Node node = root.find("convert/source/database/url", new int[]
				{1, 1, i, 1});
				Node node1 = root.find("convert/source/database/user", new int[]
				{1, 1, i, 1, 1});
				Node node2 = root.find("convert/source/database/password", new int[]
				{1, 1, i, 1, 1});
				Node node3 = root.find("convert/source/database", new int[]
				{1, 1, i});
				Node nodeMode = root.find("convert/source/database/mode", new int[]
				{1, 1, i, 1, 1});
				Node delimiterNode = root.find("convert/source/database/delimiter", new int[]
				{1, 1, i, 1, 1});
				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;

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

				int length2 = countNodes(node3);

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

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

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

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

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

			// System.out.println(((Node)tempNode.contents.v.elementAt(i)).attributes+"
			// "+i);
		}
		return length;
	}

	private static void printContents(Node root)
	{

		Vector contents = (root.index == null) ? root.contents.v : root.index.v;
		for (int i = 0; i < contents.size(); ++i)
		{
			Node n = (Node) contents.elementAt(i);
			if (n.type.equals("element"))
			{
				System.out.println("tag " + n.name);
				System.out.println(n.getCharacters());
				//contents=n.contents.v i=0;
			}
			// System.out.println(n.type);
		}
	}

	/**
	 * reads the specified xml file
	 * 
	 * @param xmlFile
	 */
	public static void readXMLFile(String xmlFile)
	{
		try
		{
			InputStream stream = null;

			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);
			StringBuffer sb = new StringBuffer();
			int c = 0;
			while ((c = buffr.read()) != -1)
			{
				char ch = (char) c;
				sb.append(ch);
				// System.out.print((char)c);
			}
			parseXMLConfig(sb);
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}

	/**
	 * Helper class for Conversion etc Holds the some data
	 * 
	 * @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;

		Vector ids;

		String delimiter = "//";

		/**
		 * 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;

		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_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");

			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 String convertToUTF8(Object command)
	{
		String str = null;
		try
		{
			str = new String(command.toString().getBytes("UTF-8"));
		}
		catch (UnsupportedEncodingException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return str;
	}

	public static void writeConfig(String file, DataBase source, DataBase destination) throws Exception
	{
		if (!file.toLowerCase().endsWith(".xml"))
			file += ".xml";
		File f = new File(file);

		FileOutputStream fout = new FileOutputStream(f);
		OutputStreamWriter outsw = new OutputStreamWriter(fout, "UTF-8");
		BufferedWriter buffw = new BufferedWriter(outsw);
		buffw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		buffw.newLine();
		buffw.write("<convert>\n");
		buffw.write("  <source>\n");
		source.exportToXML(buffw);
		buffw.write("  </source>\n");
		buffw.write("\n  <destination>\n");
		destination.exportToXML(buffw);
		buffw.write("  </destination>\n");
		buffw.write("</convert>\n");
		buffw.close();
	}

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

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

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

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

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

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

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

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

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

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

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

						int j = -1;

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

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

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

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

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

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

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

	/**
	 * synchronize method based on delete method code
	 * 
	 * @param source
	 * @param destination
	 * @param names
	 * @param layouts
	 * @param selects
	 * @param creates
	 * @param ids
	 * @param mode
	 * @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)
			throws Exception
	{
		System.out.println(" bin in synchronize!!!");
		FM2SQL.ProgressDialog dialog = null;
		if (FM2SQL.fmInstance != null)
		{
			dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
			dialog.setTitle("Synchronize running ...");
			dialog.title.setText("Getting table data ...");
			dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
			dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
			FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
			dialog.thread = Thread.currentThread();
		}
		// setting user and passwd
		bean.setUserAndPasswd(user, passwd);
		// setting user and passwd
		beanDest.setUserAndPasswd(userDest, passwdDest);
		if (dialog != null)
			dialog.setSize(400, 250);
		StringBuffer command = null;
		String query = null;
		try
		{
			//bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
			//bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
			bean.setConnection(source);
			if (names == null)
				names = bean.getTableNames();
			// Collections.sort(names);
			int tbIndex = 1;

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

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

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

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

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

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

				int endIndex = -1;
				String tempQuery = query;
				String destQuery = query.replaceAll(names.get(tbIndex).toString(), destTableName);
				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());
				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(convertText(idField), 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.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);
							// 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.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);
							// System.out.println("ID LIST SIZE " +
							// Math.round((double) myIds.size() / (double)
							// numIntervalls) + " " + myIdsDest.size());
						}
						// prepare new query for next chunk
						if (query.indexOf("where") > 0)
							tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
						else
							tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";

					}
					String tableName = names.get(tbIndex).toString();
					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 = (String) iter.next();
						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 (dialog != null)
			{
				dialog.setVisible(false);
				dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
				FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
			}
			java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
			java.io.PrintStream stream = new java.io.PrintStream(b);
			stream.print(command + "\n\n");
			e.printStackTrace(stream);
			FM2SQL.showErrorDialog(b.toString(), "Error occured !");

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

			dialog.setVisible(false);
		}
	}

	/**
	 * @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) throws SQLException, ParseException
	{
		Vector[] vectors = bean.getQueryData(tempQuery, deltaID);
		int idIndex = vectors[1].indexOf(idField);
		System.out.println(idIndex + " " + vectors[1] + " " + idField);
		// todo arraylist code has to be added
		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(line.get(idIndex)) + " " + 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();
				updPst.execute();
			}
		}
		//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)
			{
				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.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] = 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 < bean.getColumnNames().size() - 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(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(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 ******

	}
}