context: //
  // DO NOT REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
  //
  // @Authors:
  //       timop
  //
  // Copyright 2004-2013 by OM International
  //
  // This file is part of OpenPetra.org.
  //
  // OpenPetra.org 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 3 of the License, or
  // (at your option) any later version.
  //
  // OpenPetra.org is distributed in the hope that it will be useful,
  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  // GNU General Public License for more details.
  //
  // You should have received a copy of the GNU General Public License
  // along with OpenPetra.org.  If not, see <http://www.gnu.org/licenses/>.
  //
  using System;
  using System.Net;
  using System.Data;
  using System.Data.Odbc;
  using System.Data.Common;
  using System.Collections;
  using System.Text.RegularExpressions;
  using MySql.Data.MySqlClient;
  
  namespace Ict.Common.DB
  {
      /// <summary>
      /// this class allows access to MySQL databases
      /// </summary>
      public class TMySQL : IDataBaseRDBMS
      {
          /// <summary>
          /// connect to the database
          /// </summary>
          /// <param name="AServer"></param>
          /// <param name="APort"></param>
          /// <param name="ADatabaseName"></param>
          /// <param name="AUsername"></param>
          /// <param name="APassword"></param>
          /// <param name="AConnectionString"></param>
          /// <param name="AStateChangeEventHandler"></param>
          /// <returns></returns>
          public IDbConnection GetConnection(String AServer, String APort,
              String ADatabaseName,
              String AUsername, ref String APassword,
              ref String AConnectionString,
              StateChangeEventHandler AStateChangeEventHandler)
          {
              if (AConnectionString == "")
              {
                  AConnectionString = "SERVER=" + AServer + ";" + "DATABASE=" + ADatabaseName + ";" + "UID=" + AUsername + ";" + "PASSWORD=";
              }
  
              MySqlConnection TheConnection = null;
  
              try
              {
                  TheConnection = new MySqlConnection(AConnectionString + APassword + ";");
              }
              catch (Exception exp)
              {
                  ArrayList ExceptionList = new ArrayList();
                  ExceptionList.Add((("Error establishing a DB connection to: " + AConnectionString) + Environment.NewLine));
                  ExceptionList.Add((("Exception thrown :- " + exp.ToString()) + Environment.NewLine));
                  TLogging.Log(ExceptionList, true);
              }
  
              if (TheConnection != null)
              {
                  // TODO: need to test this
                  TheConnection.StateChange += AStateChangeEventHandler;
              }
  
              return TheConnection;
          }
  
          /// init the connection after it was opened
          public void InitConnection(IDbConnection AConnection)
          {
          }
  
          /// <summary>
          /// format an error message if the exception is of type MySqlException
          /// </summary>
          /// <param name="AException"></param>
          /// <param name="AErrorMessage"></param>
          /// <returns>true if this is an NpgsqlException</returns>
          public bool LogException(Exception AException, ref string AErrorMessage)
          {
              if (AException is MySqlException)
              {
                  MySqlException exp = (MySqlException)AException;
                  AErrorMessage = AErrorMessage +
                                  "Message: " + exp.Message + Environment.NewLine +
                                  "MySQL Error Number: " + exp.Number.ToString() + Environment.NewLine;
                  return true;
              }
  
              return false;
          }
  
          /// <summary>
          /// format the sql query so that it works for MySQL
          /// see also the comments for TDataBase.FormatQueryRDBMSSpecific
          /// </summary>
          /// <param name="ASqlQuery"></param>
          /// <returns></returns>
          public String FormatQueryRDBMSSpecific(String ASqlQuery)
          {
              string ReturnValue = ASqlQuery;
  
              ReturnValue = ReturnValue.Replace("PUB_", "");
              ReturnValue = ReturnValue.Replace("PUB.", "");
              ReturnValue = ReturnValue.Replace("pub_", "");
              ReturnValue = ReturnValue.Replace("pub.", "");
  
              // replacing the quotes would give trouble with importing initial database, with LOAD FROM
              //ReturnValue = ReturnValue.Replace("\"", "'");
  
              Match m = Regex.Match(ReturnValue, "#([0-9][0-9][0-9][0-9])-([0-9][0-9])-([0-9][0-9])#");
  
              while (m.Success)
              {
                  // needs to be 'yyyy-MM-dd'
                  ReturnValue = ReturnValue.Replace("#" + m.Groups[1] + "-" + m.Groups[2] + "-" + m.Groups[3] + "#",
                      "'" + m.Groups[1] + "-" + m.Groups[2] + "-" + m.Groups[3] + "'");
                  m = Regex.Match(ReturnValue, "#([0-9][0-9][0-9][0-9])-([0-9][0-9])-([0-9][0-9])#");
              }
  
              ReturnValue = ReturnValue.Replace("= false", "= 0");
              ReturnValue = ReturnValue.Replace("= true", "= 1");
              ReturnValue = ReturnValue.Replace("=false", "=0");
              ReturnValue = ReturnValue.Replace("=true", "=1");
              ReturnValue = ReturnValue.Replace(" as ", " AS ");
              ReturnValue = ReturnValue.Replace("true AS ", "1 AS ");
              ReturnValue = ReturnValue.Replace("false AS ", "0 AS ");
  
              // Get the correct function for DAYOFYEAR
              while (ReturnValue.Contains("DAYOFYEAR("))
              {
                  ReturnValue = ReplaceDayOfYear(ReturnValue);
              }
  
              return ReturnValue;
          }
  
          /// <summary>
          /// TODOComment
          /// </summary>
          /// <param name="AParameterArray">Array of DbParameter that is to be converted.</param>
          /// <param name="ASqlStatement">SQL Statement that is to be converted.</param>
          /// <returns>Array of MysqlParameter (converted from <paramref name="AParameterArray" />.</returns>
          public DbParameter[] ConvertOdbcParameters(DbParameter[] AParameterArray, ref string ASqlStatement)
          {
              MySqlParameter[] ReturnValue = new MySqlParameter[AParameterArray.Length];
              OdbcParameter[] AParameterArrayOdbc;
              string ParamName = "";
  
              if (!(AParameterArray is MySqlParameter[]))
              {
                  AParameterArrayOdbc = (OdbcParameter[])AParameterArray;
  
                  // Parameter Type change and Parameter Name assignment
                  for (int Counter = 0; Counter < AParameterArray.Length; Counter++)
                  {
                      ParamName = AParameterArrayOdbc[Counter].ParameterName;
  
                      if (ParamName == "")
                      {
                          ParamName = "param" + Counter.ToString();
  #if DEBUGMODE_TODO
                          if (FDebugLevel >= DBAccess.DB_DEBUGLEVEL_TRACE)
                          {
                              TLogging.Log("Assigned Parameter Name '" + ParamName + "' for Parameter with Value '" +
                                  AParameterArrayOdbc[Counter].Value.ToString() + "'.");
                          }
  #endif
                      }
  
                      switch (AParameterArrayOdbc[Counter].OdbcType)
                      {
                          case OdbcType.Decimal:
  
                              ReturnValue[Counter] = new MySqlParameter(
                              ParamName,
                              MySqlDbType.Decimal, AParameterArrayOdbc[Counter].Size);
  
                              break;
  
                          case OdbcType.VarChar:
                              ReturnValue[Counter] = new MySqlParameter(
                              ParamName,
                              MySqlDbType.String, AParameterArrayOdbc[Counter].Size);
  
                              break;
  
                          case OdbcType.Bit:
                              ReturnValue[Counter] = new MySqlParameter(
                              ParamName,
                              MySqlDbType.Bit);
  
                              break;
  
                          case OdbcType.Date:
  
                              if (AParameterArrayOdbc[Counter].Value != DBNull.Value)
                              {
                                  DateTime TmpDate = (DateTime)AParameterArrayOdbc[Counter].Value;
  
                                  if ((TmpDate.Hour == 0)
                                      && (TmpDate.Minute == 0)
                                      && (TmpDate.Second == 0)
                                      && (TmpDate.Millisecond == 0))
                                  {
                                      ReturnValue[Counter] = new MySqlParameter(
                                          ParamName,
                                          MySqlDbType.Date);
                                  }
                                  else
                                  {
                                      ReturnValue[Counter] = new MySqlParameter(
                                          ParamName,
                                          MySqlDbType.Timestamp);
                                  }
                              }
                              else
                              {
                                  ReturnValue[Counter] = new MySqlParameter(
                                      ParamName,
                                      MySqlDbType.Date);
                              }
  
                              break;
  
                          case OdbcType.DateTime:
                              ReturnValue[Counter] = new MySqlParameter(
                              ParamName,
                              MySqlDbType.DateTime);
  
                              break;
  
                          case OdbcType.Int:
                              ReturnValue[Counter] = new MySqlParameter(
                              ParamName,
                              MySqlDbType.Int32);
  
                              break;
  
                          case OdbcType.BigInt:
                              ReturnValue[Counter] = new MySqlParameter(
                              ParamName,
                              MySqlDbType.Int64);
  
                              break;
  
                          default:
                              ReturnValue[Counter] = new MySqlParameter(
                              ParamName,
                              MySqlDbType.Int32);
  
                              break;
                      }
  
                      ReturnValue[Counter].Value = AParameterArrayOdbc[Counter].Value;
                  }
              }
              else
              {
                  ReturnValue = (MySqlParameter[])AParameterArray;
              }
  
              if (ASqlStatement.Contains("?"))
              {
                  string SQLSelectBeforeQMark;
                  string SQLSelectAfterQMark;
                  string SQLSelectStatementRepl = "";
                  int QMarkPos = 0;
                  int LastQMarkPos = 0;
                  int NextQMarkPos = 0;
                  int ParamCounter = 0;
  
                  /* SQL Syntax change from ODBC style to MySQL style: Replace '?' with
                   * '?xxx' (where xxx is the name of the Parameter).
                   */
                  while (ASqlStatement.IndexOf("?", QMarkPos + 1) > 0)
                  {
                      QMarkPos = ASqlStatement.IndexOf("?", QMarkPos + 1);
  
                      ////                        TLogging.Log("QMarkPos: " + QMarkPos.ToString() +
                      ////                            "; ParamCounter: " + ParamCounter.ToString() +
                      ////                            "; LastQMarkPos: " + LastQMarkPos.ToString());
  
  
gt:                     if (ParamCounter > 0)