// Copyright (c) 2004, 2022, Oracle and/or its affiliates.
//
// This program is free software; you can redistribute it and/or modify
// it under the terms of the GNU General Public License, version 2.0, as
// published by the Free Software Foundation.
//
// This program is also distributed with certain software (including
// but not limited to OpenSSL) that is licensed under separate terms,
// as designated in a particular file or component or in included license
// documentation. The authors of MySQL hereby grant you an
// additional permission to link the program and your derivative works
// with the separately licensed software that they have included with
// MySQL.
//
// Without limiting anything contained in the foregoing, this file,
// which is part of MySQL Connector/NET, is also subject to the
// Universal FOSS Exception, version 1.0, a copy of which can be found at
// http://oss.oracle.com/licenses/universal-foss-exception.
//
// This program 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, version 2.0, for more details.
//
// 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.,
// 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA

using MySql.Data.Common;
using MySql.Data.MySqlClient.Replication;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Threading;

namespace MySql.Data.MySqlClient
{
  /// <summary>
  /// Represents a SQL statement to execute against a MySQL database. This class cannot be inherited.
  /// </summary>
  /// <remarks>
  /// <para>
  /// You can reset the <see cref="CommandText"/> property and reuse the <see cref = "MySqlCommand" />
  /// object. However, you must close the <see cref = "MySqlDataReader" /> object before you can execute a new or previous command.
  /// </para>
  /// <para>
  /// If an exception of type <see cref="MySqlException"/> is generated by the method executing <see cref="MySqlCommand"/>,
  /// the <see cref="MySqlConnection"/> instance remains open. It is the responsibility of the programmer to close the connection.
  /// </para>
  /// <para>
  /// You can read more about it <see href="https://dev.mysql.com/doc/connector-net/en/connector-net-programming-mysqlcommand.html">here</see>.
  /// </para>
  /// <note>
  /// Using the '@' symbol for paramters is now the preferred approach although the old pattern of using
  ///  '?' is still supported. Please be aware that using '@' can cause conflicts when user variables
  ///  are also used. For more information, see the documentation on the <b>AllowUserVariables</b> connection string option.
  /// </note>
  /// </remarks>
  [DesignerCategory("Code")]
  public sealed class MySqlCommand : DbCommand, IDisposable, ICloneable
  {
    MySqlConnection connection;
    string cmdText;
    private PreparableStatement statement;
    private int commandTimeout;
    private bool resetSqlSelect;
    CommandTimer commandTimer;
    private bool useDefaultTimeout;
    private static List<string> keywords = null;
    private bool disposed = false;

    /// <summary>
    /// Initializes a new instance of the MySqlCommand class.
    /// </summary>
    /// <remarks>
    /// The base constructor initializes all fields to their default values.
    /// </remarks>
    public MySqlCommand()
    {
      CommandType = CommandType.Text;
      Parameters = new MySqlParameterCollection(this);
      Attributes = new MySqlAttributeCollection(this);
      cmdText = String.Empty;
      useDefaultTimeout = true;
      UpdatedRowSource = UpdateRowSource.Both;
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="MySqlCommand"/> class with the text of the query.
    /// </summary>
    /// <param name="cmdText">The text of the query.</param>
    public MySqlCommand(string cmdText)
      : this()
    {
      CommandText = cmdText;
    }

    /// <summary>
    ///  Initializes a new instance of the <see cref="MySqlCommand"/> class with the text of the query and a <see cref="MySqlConnection"/>.
    ///</summary>
    /// <param name="cmdText">The text of the query.</param>
    /// <param name="connection">A <see cref="MySqlConnection"/> that represents the connection to an instance of MySQL Server.</param>
    public MySqlCommand(string cmdText, MySqlConnection connection)
      : this(cmdText)
    {
      Connection = connection;
    }

    /// <summary>
    ///  Initializes a new instance of the <see cref="MySqlCommand"/> class with the text of the query, 
    ///  a <see cref="MySqlConnection"/>, and the <see cref="MySqlTransaction"/>.
    /// </summary>
    /// <param name="cmdText">The text of the query.</param>
    /// <param name="connection">A <see cref="MySqlConnection"/> that represents the connection to an instance of MySQL Server.</param>
    /// <param name="transaction">The <see cref="MySqlTransaction"/> in which the <see cref="MySqlCommand"/> executes.</param>
    public MySqlCommand(string cmdText, MySqlConnection connection, MySqlTransaction transaction)
      : this(cmdText, connection)
    {
      Transaction = transaction;
    }

    #region Destructor
    ~MySqlCommand()
    {
      Dispose(false);
    }
    #endregion

    #region Properties

    /// <summary>
    /// Provides the ID of the last inserted row.</summary>
    /// <value> ID of the last inserted row. -1 if none exists.</value>
    /// <remarks>
    ///  An important point to remember is that this property can be used in batch SQL scenarios but it's important to remember that it will
    ///  only reflect the insert ID from the last insert statement in the batch. This property can also be used when the batch includes select statements
    ///  and ExecuteReader is used. This property can be consulted during result set processing.
    /// </remarks>
    [Browsable(false)]
    public Int64 LastInsertedId { get; internal set; }

    /// <summary>
    ///  Gets or sets the SQL statement to execute at the data source.
    /// </summary>
    /// <value>The SQL statement or stored procedure to execute. The default is an empty string.</value>
    /// <remarks>
    /// You can read more about it <see href="https://dev.mysql.com/doc/connector-net/en/connector-net-programming-mysqlcommand.html#connector-net-programming-mysqlcommand-text-type">here</see>.
    /// </remarks>
    [Category("Data")]
    [Description("Command text to execute")]
#if NET452
    [Editor("MySql.Data.Common.Design.SqlCommandTextEditor,MySqlClient.Design", typeof(System.Drawing.Design.UITypeEditor))]
#endif
    public override string CommandText
    {
      get { return cmdText; }
      set
      {
        cmdText = value ?? string.Empty;
        statement = null;
        BatchableCommandText = null;
        if (cmdText != null && cmdText.EndsWith("DEFAULT VALUES", StringComparison.OrdinalIgnoreCase))
        {
          cmdText = cmdText.Substring(0, cmdText.Length - 14);
          cmdText = cmdText + "() VALUES ()";
        }
      }
    }

    /// <summary>
    /// Gets or sets the wait time before terminating the attempt to execute a command
    /// and generating an error.
    /// </summary>
    /// <value>The time (in seconds) to wait for the command to execute. The default is 30 seconds.</value>
    /// <remarks>
    ///   CommandTimeout is dependent on the ability of MySQL to cancel an executing query.
    /// </remarks>
    [Category("Misc")]
    [Description("Time to wait for command to execute")]
    [DefaultValue(30)]
    public override int CommandTimeout
    {
      get { return useDefaultTimeout ? 30 : commandTimeout; }
      set
      {
        if (value < 0)
          Throw(new ArgumentException("Command timeout must not be negative"));

        // Timeout in milliseconds should not exceed maximum for 32 bit
        // signed integer (~24 days), because underlying driver (and streams)
        // use milliseconds expressed ints for timeout values.
        // Hence, truncate the value.
        int timeout = Math.Min(value, Int32.MaxValue / 1000);
        if (timeout != value)
        {
          MySqlTrace.LogWarning(connection.ServerThread,
          "Command timeout value too large ("
          + value + " seconds). Changed to max. possible value ("
          + timeout + " seconds)");
        }
        commandTimeout = timeout;
        useDefaultTimeout = false;
      }
    }

    /// <summary>
    ///   Gets or sets a value indicating how the <see cref="CommandText"/> property is to be interpreted.
    /// </summary>
    /// <value>
    ///   One of the <see cref="System.Data.CommandType"/> values. 
    ///   The default is <see cref="CommandType.Text"/>.
    /// </value>
    /// <remarks>
    /// You can read more about it <see href="https://dev.mysql.com/doc/connector-net/en/connector-net-programming-mysqlcommand.html#connector-net-programming-mysqlcommand-text-type">here</see>.
    /// </remarks>
    [Category("Data")]
    public override CommandType CommandType { get; set; }

    /// <summary>
    /// Gets a boolean value that indicates whether the <see cref="Prepare"/> method has been called.
    /// </summary>
    /// <value>True if it is Prepared; otherwise, false.</value>
    [Browsable(false)]
    public bool IsPrepared => statement != null && statement.IsPrepared;

    /// <summary>
    ///  Gets or sets the <see cref="MySqlConnection"/> object used by this instance of the <see cref="MySqlCommand"/>.
    /// </summary>
    /// <value>
    ///  The connection to a data source. The default value is a null reference.
    /// </value>
    [Category("Behavior")]
    [Description("Connection used by the command")]
    public new MySqlConnection Connection
    {
      get { return connection; }
      set
      {
        /*
        * The connection is associated with the transaction
        * so set the transaction object to return a null reference if the connection 
        * is reset.
        */
        if (connection != value)
          Transaction = null;

        connection = value;

        // if the user has not already set the command timeout, then
        // take the default from the connection
        if (connection == null) return;

        if (useDefaultTimeout)
        {
          commandTimeout = (int)connection.Settings.DefaultCommandTimeout;
          useDefaultTimeout = false;
        }

        EnableCaching = connection.Settings.TableCaching;
        CacheAge = connection.Settings.DefaultTableCacheAge;
      }
    }

    /// <summary>
    ///  Gets the <see cref="MySqlParameterCollection"/> object.
    /// </summary>
    /// <value>
    ///  The parameters of the SQL statement or stored procedure. The default is an empty collection.
    /// </value>
    /// <remarks>
    ///  Connector/NET does not support unnamed parameters. Every parameter added to the collection must
    ///  have an associated name.
    /// You can read more about it <see href="https://dev.mysql.com/doc/connector-net/en/connector-net-programming-mysqlcommand.html#connector-net-programming-mysqlcommand-parameters">here</see>.
    ///  <note>Parameters can be used along with <see cref="MySqlAttribute"/>. There are no restrictions in this regard.</note>
    /// </remarks>
    [Category("Data")]
    [Description("The parameters collection")]
    [DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
    public new MySqlParameterCollection Parameters { get; }

    /// <summary>
    ///  Gets the <see cref="MySqlAttributeCollection"/> object.
    /// </summary>
    /// <value>
    ///  The query attributes defined for the statement. The default is an empty collection.
    /// </value>
    /// <remarks>
    ///  Connector/NET does not support unnamed query attributes. Every query attribute added to the collection must
    ///  have an associated name. 
    /// You can read more about it <see href="https://dev.mysql.com/doc/connector-net/en/connector-net-programming-mysqlcommand.html#connector-net-programming-mysqlcommand-query-attributes">here</see>.
    ///  <note>Query Attributes can be used along with <see cref="MySqlParameter"/>. There are no restrictions in this regard.</note>
    /// </remarks>
    [Category("Data")]
    [Description("The attributes collection")]
    [DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
    public MySqlAttributeCollection Attributes { get; }

    /// <summary>
    ///  Gets or sets the instance of <see cref="MySqlTransaction"/> within which <see cref="MySqlCommand"/> executes.
    /// </summary>
    /// <value>
    ///  The <see cref="MySqlTransaction"/>. The default value is a null reference (<b>Nothing</b> in Visual Basic).
    /// </value>
    /// <remarks>
    ///  You cannot set the <see cref="Transaction"/> property if it is already set to a
    ///  specific value, and the command is in the process of executing. If you set the
    ///  transaction to use a <see cref="MySqlTransaction"/> object that is not connected
    ///  to the same <see cref="MySqlConnection"/> as the <see cref="MySqlCommand"/> object,
    ///  an exception will be thrown the next time you attempt to execute a statement.
    /// </remarks>
    [Browsable(false)]
    public new MySqlTransaction Transaction { get; set; }

    /// <summary>
    /// Gets or sets a <see cref="Boolean"/> value that indicates whether caching is enabled.
    /// </summary>
    /// <value>True if it is enabled; otherwise, false.</value>
    public bool EnableCaching { get; set; }

    /// <summary>
    /// Gets or sets the seconds for how long a TableDirect result should be cached.
    /// </summary>
    /// <value>Number of seconds.</value>
    public int CacheAge { get; set; }

    internal List<MySqlCommand> Batch { get; private set; }

    internal bool Canceled { get; private set; }

    internal string BatchableCommandText { get; private set; }

    internal bool InternallyCreated { get; set; }

    /// <summary>
    ///  Gets or sets how command results are applied to the <see cref="DataRow"/>
    ///  when used by the <see cref="DbDataAdapter.Update"/> method of the <see cref="DbDataAdapter"/>.
    /// </summary>
    /// <value>
    ///  One of the <see cref="UpdateRowSource"/> values.
    /// </value>
    /// <remarks>
    ///  <para>
    ///    The default <see cref="UpdateRowSource"/> value is
    ///    <b>Both</b> unless the command is automatically generated (as in the case of the
    ///    <see cref="MySqlCommandBuilder"/>), in which case the default is <b>None</b>.
    ///  </para>
    /// </remarks>
    public override UpdateRowSource UpdatedRowSource { get; set; }

    /// <summary>
    /// Gets or sets a value indicating whether the command object should be visible in a Windows Form Designer control. 
    /// </summary>
    /// <value>True if it should be visible; otherwise, false.</value>
    [Browsable(false)]
    public override bool DesignTimeVisible { get; set; }

    protected override DbParameter CreateDbParameter()
    {
      return new MySqlParameter();
    }

    /// <summary>
    /// Gets or sets the <see cref="DbConnection"/> used by this <see cref="DbCommand"/>.
    /// </summary>
    /// <value>The connection.</value>
    protected override DbConnection DbConnection
    {
      get { return Connection; }
      set { Connection = (MySqlConnection)value; }
    }

    /// <summary>
    /// Gets the collection of <see cref="MySqlParameter"/> objects.
    /// </summary>
    /// <value>The collection.</value>
    protected override DbParameterCollection DbParameterCollection
    {
      get { return Parameters; }
    }

    /// <summary>
    /// Gets or sets the <see cref="DbTransaction"/> within which this <see cref="DbCommand"/> object executes.
    /// </summary>
    /// <value>The transaction.</value>
    protected override DbTransaction DbTransaction
    {
      get { return Transaction; }
      set { Transaction = (MySqlTransaction)value; }
    }

    #endregion

    #region Methods

    /// <summary>
    /// Attempts to cancel the execution of a currently active command
    /// </summary>
    /// <remarks>
    /// Cancelling a currently active query only works with MySQL versions 5.0.0 and higher.
    /// </remarks>
    public override void Cancel()
    {
      if (connection != null)
        connection.CancelQuery(connection.ConnectionTimeout);
      Canceled = true;
    }

    /// <summary>
    /// Creates a new instance of a <see cref="MySqlParameter"/> object.
    /// </summary>
    /// <remarks>
    /// This method is a strongly-typed version of <see cref="System.Data.IDbCommand.CreateParameter"/>.
    /// </remarks>
    /// <returns>A <see cref="MySqlParameter"/> object.</returns>
    /// 
    public new MySqlParameter CreateParameter()
    {
      return (MySqlParameter)CreateDbParameter();
    }

    /// <summary>
    /// Check the connection to make sure
    ///		- it is open
    ///		- it is not currently being used by a reader
    ///		- and we have the right version of MySQL for the requested command type
    /// </summary>
    private void CheckState()
    {
      // There must be a valid and open connection.
      if (connection == null)
        Throw(new InvalidOperationException("Connection must be valid and open."));

      if (connection.State != ConnectionState.Open && !connection.SoftClosed)
        Throw(new InvalidOperationException("Connection must be valid and open."));

      // Data readers have to be closed first
      if (connection.IsInUse && !this.InternallyCreated)
        Throw(new MySqlException("There is already an open DataReader associated with this Connection which must be closed first."));
    }

    /// <summary>
    ///  Executes a SQL statement against the connection and returns the number of rows affected.
    /// </summary>
    /// <returns>Number of rows affected</returns>
    /// <remarks>
    ///  You can use <see cref="ExecuteNonQuery"/> to perform any type of database operation,
    ///  however any resultsets returned will not be available. Any output parameters
    ///  used in calling a stored procedure will be populated with data and can be
    ///  retrieved after execution is complete.
    ///  For UPDATE, INSERT, and DELETE statements, the return value is the number
    ///  of rows affected by the command. For all other types of statements, the return
    ///  value is -1.
    /// </remarks>
    public override int ExecuteNonQuery()
    {
      int records = -1;

      // give our interceptors a shot at it first
      if (connection?.commandInterceptor != null && connection.commandInterceptor.ExecuteNonQuery(CommandText, ref records))
        return records;

      // ok, none of our interceptors handled this so we default
      using (MySqlDataReader reader = ExecuteReader())
      {
        reader.Close();
        return reader.RecordsAffected;
      }
    }

    internal void ClearCommandTimer()
    {
      if (commandTimer == null) return;

      commandTimer.Dispose();
      commandTimer = null;
    }

    internal void Close(MySqlDataReader reader)
    {
      statement?.Close(reader);
      ResetSqlSelectLimit();
      if (statement != null)
        connection?.driver?.CloseQuery(connection, statement.StatementId);
      ClearCommandTimer();
    }

    protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
    {
      return ExecuteReader(behavior);
    }

    /// <summary>
    /// Reset reader to null, to avoid "There is already an open data reader"
    /// on the next ExecuteReader(). Used in error handling scenarios.
    /// </summary>
    private void ResetReader()
    {
      if (connection?.Reader == null) return;

      connection.Reader.Close();
      connection.Reader = null;
    }

    /// <summary>
    /// Reset SQL_SELECT_LIMIT that could have been modified by CommandBehavior.
    /// </summary>
    internal void ResetSqlSelectLimit()
    {
      // if we are supposed to reset the sql select limit, do that here
      if (!resetSqlSelect) return;

      resetSqlSelect = false;
      MySqlCommand command = new MySqlCommand("SET SQL_SELECT_LIMIT=DEFAULT", connection);
      command.InternallyCreated = true;
      command.ExecuteNonQuery();
    }

    /// <summary>
    ///  Sends the <see cref="CommandText"/> value to <see cref="MySqlConnection"/>
    ///  and builds a <see cref="MySqlDataReader"/> object.
    /// </summary>
    /// <returns>A <see cref="MySqlDataReader"/> object.</returns>
    /// <remarks>
    ///  <para>
    ///    When the <see cref="CommandType"/> property is set to <b>StoredProcedure</b>,
    ///    the <see cref="CommandText"/> property should be set to the name of the stored
    ///    procedure. The command executes this stored procedure when you call
    ///    <b>ExecuteReader</b>.
    ///  </para>
    ///  <para>
    ///    While <see cref="MySqlDataReader"/> is in use, the associated
    ///    instance of <see cref="MySqlConnection"/> is busy serving it
    ///    and no other operations can be performed on <see cref="MySqlConnection"/>, other than closing it.
    ///    This is the case until the <see cref="MySqlDataReader.Close"/> method of <b>MySqlDataReader</b> is called.
    ///  </para>
    /// </remarks>
    public new MySqlDataReader ExecuteReader()
    {
      return ExecuteReader(CommandBehavior.Default);
    }

    /// <summary>
    ///  Sends the <see cref="CommandText"/> to the <see cref="MySqlConnection">Connection</see>,
    ///  and builds a <see cref="MySqlDataReader"/> using one of the <see cref="CommandBehavior"/> values.
    /// </summary>
    /// <param name="behavior">One of the <see cref="CommandBehavior"/> values.</param>
    /// <remarks>
    ///  <para>
    ///    When the <see cref="CommandType"/> property is set to <b>StoredProcedure</b>,
    ///    the <see cref="CommandText"/> property should be set to the name of the stored
    ///    procedure. The command executes this stored procedure when you call
    ///    <b>ExecuteReader</b>.
    ///  </para>
    ///  <para>
    ///    If the <b>MySqlDataReader</b> object is created with <b>CommandBehavior</b> set to
    ///    <b>CloseConnection</b>, closing the <b>MySqlDataReader</b> instance closes the connection
    ///    automatically.
    ///  </para>
    ///  <note>
    ///    When calling ExecuteReader with the <b>SingleRow</b> behavior, you should be aware that using a <i>limit</i>
    ///    clause in your SQL will cause all rows (up to the limit given) to be retrieved by the client. The
    ///    <see cref="MySqlDataReader.Read"/> method will still return false after the first row but pulling all rows of data
    ///    into the client will have a performance impact. If the <i>limit</i> clause is not necessary, it should
    ///    be avoided.
    ///  </note>
    /// </remarks>
    /// <returns>
    ///  A <see cref="MySqlDataReader"/> object.
    /// </returns>
    public new MySqlDataReader ExecuteReader(CommandBehavior behavior)
    {
      // give our interceptors a shot at it first
      MySqlDataReader interceptedReader = null;
      if (connection?.commandInterceptor != null && connection.commandInterceptor.ExecuteReader(CommandText, behavior, ref interceptedReader))
        return interceptedReader;

      // interceptors didn't handle this so we fall through
      bool success = false;
      CheckState();
      Driver driver = connection.driver;

      cmdText = cmdText.Trim();
      if (String.IsNullOrEmpty(cmdText))
        Throw(new InvalidOperationException(Resources.CommandTextNotInitialized));

      string sql = cmdText.Trim(';');

      // Load balancing getting a new connection
      if (connection.hasBeenOpen && !driver.HasStatus(ServerStatusFlags.InTransaction))
      {
        ReplicationManager.GetNewConnection(connection.Settings.Server, !IsReadOnlyCommand(sql), connection);
      }

      lock (driver)
      {

        // We have to recheck that there is no reader, after we got the lock
        if (connection.Reader != null)
        {
          Throw(new MySqlException(Resources.DataReaderOpen));
        }

        System.Transactions.Transaction curTrans = System.Transactions.Transaction.Current;

        if (curTrans != null)
        {
          bool inRollback = false;
          //TODO: ADD support for 452 and 46X
          if (driver.currentTransaction != null)
            inRollback = driver.currentTransaction.InRollback;
          if (!inRollback)
          {
            System.Transactions.TransactionStatus status = System.Transactions.TransactionStatus.InDoubt;
            try
            {
              // in some cases (during state transitions) this throws
              // an exception. Ignore exceptions, we're only interested 
              // whether transaction was aborted or not.
              status = curTrans.TransactionInformation.Status;
            }
            catch (System.Transactions.TransactionException)
            {
            }
            if (status == System.Transactions.TransactionStatus.Aborted)
              Throw(new System.Transactions.TransactionAbortedException());
          }
        }

        commandTimer = new CommandTimer(connection, CommandTimeout);

        LastInsertedId = -1;

        if (CommandType == CommandType.TableDirect)
          sql = "SELECT * FROM " + sql;
        else if (CommandType == CommandType.Text)
        {
          // validates single word statetment (maybe is a stored procedure call)
          if (sql.IndexOf(" ") == -1)
          {
            if (AddCallStatement(sql))
              sql = "call " + sql;
          }
        }

        // if we are on a replicated connection, we are only allow readonly statements
        if (connection.Settings.Replication && !InternallyCreated)
          EnsureCommandIsReadOnly(sql);

        if (statement == null || !statement.IsPrepared)
        {
          if (CommandType == CommandType.StoredProcedure)
            statement = new StoredProcedure(this, sql);
          else
            statement = new PreparableStatement(this, sql);
        }

        // stored procs are the only statement type that need do anything during resolve
        statement.Resolve(false);

        // Now that we have completed our resolve step, we can handle our
        // command behaviors
        HandleCommandBehaviors(behavior);

        try
        {
          MySqlDataReader reader = new MySqlDataReader(this, statement, behavior);
          connection.Reader = reader;
          Canceled = false;
          // execute the statement
          statement.Execute();
          // wait for data to return
          reader.NextResult();
          success = true;
          return reader;
        }
        catch (TimeoutException tex)
        {
          connection.HandleTimeoutOrThreadAbort(tex);
          throw; //unreached
        }
        catch (ThreadAbortException taex)
        {
          connection.HandleTimeoutOrThreadAbort(taex);
          throw;
        }
        catch (IOException ioex)
        {
          connection.Abort(); // Closes connection without returning it to the pool
          throw new MySqlException(Resources.FatalErrorDuringExecute, ioex);
        }
        catch (MySqlException ex)
        {

          if (ex.InnerException is TimeoutException)
            throw; // already handled

          try
          {
            ResetReader();
            ResetSqlSelectLimit();
          }
          catch (Exception)
          {
            // Reset SqlLimit did not work, connection is hosed.
            Connection.Abort();
            throw new MySqlException(ex.Message, true, ex);
          }

          // if we caught an exception because of a cancel, then just return null
          if (ex.IsQueryAborted)
            return null;
          if (ex.IsFatal)
            Connection.Close();
          if (ex.Number == 0)
            throw new MySqlException(Resources.FatalErrorDuringExecute, ex);
          throw;
        }
        finally
        {
          if (connection != null)
          {
            if (connection.Reader == null)
            {
              // Something went seriously wrong,  and reader would not
              // be able to clear timeout on closing.
              // So we clear timeout here.
              ClearCommandTimer();
            }
            if (!success)
            {
              // ExecuteReader failed.Close Reader and set to null to 
              // prevent subsequent errors with DataReaderOpen
              ResetReader();
            }
          }
        }
      }
    }

    private void EnsureCommandIsReadOnly(string sql)
    {
      sql = StringUtility.ToLowerInvariant(sql);
      if (!sql.StartsWith("select") && !sql.StartsWith("show"))
        Throw(new MySqlException(Resources.ReplicatedConnectionsAllowOnlyReadonlyStatements));
      if (sql.EndsWith("for update") || sql.EndsWith("lock in share mode"))
        Throw(new MySqlException(Resources.ReplicatedConnectionsAllowOnlyReadonlyStatements));
    }

    private bool IsReadOnlyCommand(string sql)
    {
      sql = sql.ToLower();
      return (sql.StartsWith("select") || sql.StartsWith("show"))
        && !(sql.EndsWith("for update") || sql.EndsWith("lock in share mode"));
    }


    /// <summary>
    ///  Executes the query, and returns the first column of the first row in the
    ///  result set returned by the query. Extra columns or rows are ignored.
    /// </summary>
    /// <returns>
    ///  The first column of the first row in the result set, or a null reference if the
    ///  result set is empty
    /// </returns>
    /// <remarks>
    ///  <para>
    ///    Use the <b>ExecuteScalar</b> method to retrieve a single value (for example,
    ///    an aggregate value) from a database. This requires less code than using the
    ///    <see cref="ExecuteReader()"/> method, and then performing the operations necessary
    ///    to generate the single value using the data returned by a <see cref="MySqlDataReader"/>
    ///  </para>
    /// </remarks>
    public override object ExecuteScalar()
    {
      LastInsertedId = -1;
      object val = null;

      // give our interceptors a shot at it first
      if (connection != null &&
          connection.commandInterceptor.ExecuteScalar(CommandText, ref val))
        return val;

      using (MySqlDataReader reader = ExecuteReader())
      {
        if (reader.Read())
          val = reader.GetValue(0);
      }

      return val;
    }

    private void HandleCommandBehaviors(CommandBehavior behavior)
    {
      if ((behavior & CommandBehavior.SchemaOnly) != 0)
      {
        new MySqlCommand("SET SQL_SELECT_LIMIT=0", connection).ExecuteNonQuery();
        resetSqlSelect = true;
      }
      else if ((behavior & CommandBehavior.SingleRow) != 0)
      {
        new MySqlCommand("SET SQL_SELECT_LIMIT=1", connection).ExecuteNonQuery();
        resetSqlSelect = true;
      }
    }

    private void Prepare(int cursorPageSize)
    {
      using (new CommandTimer(Connection, CommandTimeout))
      {
        // if the length of the command text is zero, then just return
        string psSQL = CommandText;
        if (psSQL == null ||
             psSQL.Trim().Length == 0)
          return;

        statement = CommandType == CommandType.StoredProcedure ? new StoredProcedure(this, CommandText) : new PreparableStatement(this, CommandText);

        statement.Resolve(true);
        statement.Prepare();
      }
    }

    /// <summary>
    ///  Creates a prepared version of the command on an instance of MySQL Server.
    /// </summary>
    public override void Prepare()
    {
      if (connection == null)
        Throw(new InvalidOperationException("The connection property has not been set."));
      if (connection.State != ConnectionState.Open)
        Throw(new InvalidOperationException("The connection is not open."));

      Prepare(0);
    }

    /// <summary>
    /// Creates a clone of this <see cref="MySqlCommand"/> object. CommandText, Connection, and Transaction properties
    /// are included as well as the entire parameter and the arribute list.
    /// </summary>
    /// <returns>The cloned <see cref="MySqlCommand"/> object.</returns>
    public object Clone()
    {
      MySqlCommand clone = new MySqlCommand(cmdText, connection, Transaction)
      {
        CommandType = CommandType,
        commandTimeout = commandTimeout,
        useDefaultTimeout = useDefaultTimeout,
        BatchableCommandText = BatchableCommandText,
        EnableCaching = EnableCaching,
        CacheAge = CacheAge
      };

      foreach (MySqlParameter p in Parameters)
        clone.Parameters.Add(p.Clone());

      foreach (MySqlAttribute a in Attributes)
        clone.Attributes.SetAttribute(a.Clone());

      return clone;
    }
    #endregion

    #region Async Methods
    private IAsyncResult asyncResult;

    internal delegate object AsyncDelegate(int type, CommandBehavior behavior);
    internal AsyncDelegate Caller;
    internal Exception thrownException;

    internal object AsyncExecuteWrapper(int type, CommandBehavior behavior)
    {
      thrownException = null;
      try
      {
        if (type == 1)
          return ExecuteReader(behavior);
        return ExecuteNonQuery();
      }
      catch (Exception ex)
      {
        thrownException = ex;
      }
      return null;
    }

    /// <summary>
    /// Initiates the asynchronous execution of the SQL statement or stored procedure 
    /// that is described by this <see cref="MySqlCommand"/>, and retrieves one or more 
    /// result sets from the server. 
    /// </summary>
    /// <returns>An <see cref="IAsyncResult"/> that can be used to poll, wait for results, 
    /// or both; this value is also needed when invoking EndExecuteReader, 
    /// which returns a <see cref="MySqlDataReader"/> instance that can be used to retrieve 
    /// the returned rows.</returns>
    public IAsyncResult BeginExecuteReader()
    {
      return BeginExecuteReader(CommandBehavior.Default);
    }

    /// <summary>
    /// Initiates the asynchronous execution of the SQL statement or stored procedure 
    /// that is described by this <see cref="MySqlCommand"/> using one of the 
    /// <b>CommandBehavior</b> values. 
    /// </summary>
    /// <param name="behavior">One of the <see cref="CommandBehavior"/> values, indicating 
    /// options for statement execution and data retrieval.</param>
    /// <returns>An <see cref="IAsyncResult"/> that can be used to poll, wait for results, 
    /// or both; this value is also needed when invoking EndExecuteReader, 
    /// which returns a <see cref="MySqlDataReader"/> instance that can be used to retrieve 
    /// the returned rows.</returns>
    public IAsyncResult BeginExecuteReader(CommandBehavior behavior)
    {
      if (Caller != null)
        Throw(new MySqlException(Resources.UnableToStartSecondAsyncOp));

      Caller = AsyncExecuteWrapper;
      asyncResult = Caller.BeginInvoke(1, behavior, null, null);
      return asyncResult;
    }

    /// <summary>
    /// Finishes asynchronous execution of a SQL statement, returning the requested 
    /// <see cref="MySqlDataReader"/>.
    /// </summary>
    /// <param name="result">The <see cref="IAsyncResult"/> returned by the call to 
    /// <see cref="BeginExecuteReader()"/>.</param>
    /// <returns>A <b>MySqlDataReader</b> object that can be used to retrieve the requested rows. </returns>
    public MySqlDataReader EndExecuteReader(IAsyncResult result)
    {
      result.AsyncWaitHandle.WaitOne();
      AsyncDelegate c = Caller;
      Caller = null;
      if (thrownException != null)
        throw thrownException;
      return (MySqlDataReader)c.EndInvoke(result);
    }

    /// <summary>
    /// Initiates the asynchronous execution of the SQL statement or stored procedure 
    /// that is described by this <see cref="MySqlCommand"/>. 
    /// </summary>
    /// <param name="callback">
    /// An <see cref="AsyncCallback"/> delegate that is invoked when the command's 
    /// execution has completed. Pass a null reference to indicate that no callback is required.</param>
    /// <param name="stateObject">A user-defined state object that is passed to the 
    /// callback procedure. Retrieve this object from within the callback procedure 
    /// using the <see cref="IAsyncResult.AsyncState"/> property.</param>
    /// <returns>An <see cref="IAsyncResult"/> that can be used to poll or wait for results, 
    /// or both; this value is also needed when invoking <see cref="EndExecuteNonQuery"/>, 
    /// which returns the number of affected rows. </returns>
    public IAsyncResult BeginExecuteNonQuery(AsyncCallback callback, object stateObject)
    {
      if (Caller != null)
        Throw(new MySqlException(Resources.UnableToStartSecondAsyncOp));

      Caller = AsyncExecuteWrapper;
      asyncResult = Caller.BeginInvoke(2, CommandBehavior.Default,
          callback, stateObject);
      return asyncResult;
    }

    /// <summary>
    /// Initiates the asynchronous execution of the SQL statement or stored procedure 
    /// that is described by this <see cref="MySqlCommand"/>. 
    /// </summary>
    /// <returns>An <see cref="IAsyncResult"/> that can be used to poll or wait for results, 
    /// or both; this value is also needed when invoking <see cref="EndExecuteNonQuery"/>, 
    /// which returns the number of affected rows. </returns>
    public IAsyncResult BeginExecuteNonQuery()
    {
      if (Caller != null)
        Throw(new MySqlException(Resources.UnableToStartSecondAsyncOp));

      Caller = AsyncExecuteWrapper;
      asyncResult = Caller.BeginInvoke(2, CommandBehavior.Default, null, null);
      return asyncResult;
    }

    /// <summary>
    /// Finishes asynchronous execution of a SQL statement. 
    /// </summary>
    /// <param name="asyncResult">The <see cref="IAsyncResult"/> returned by the call 
    /// to <see cref="BeginExecuteNonQuery()"/>.</param>
    /// <returns></returns>
    public int EndExecuteNonQuery(IAsyncResult asyncResult)
    {
      asyncResult.AsyncWaitHandle.WaitOne();
      AsyncDelegate c = Caller;
      Caller = null;
      if (thrownException != null)
        throw thrownException;
      return (int)c.EndInvoke(asyncResult);
    }
    #endregion

    #region Private Methods

    /*		private ArrayList PrepareSqlBuffers(string sql)
                {
                    ArrayList buffers = new ArrayList();
                    MySqlStreamWriter writer = new MySqlStreamWriter(new MemoryStream(), connection.Encoding);
                    writer.Version = connection.driver.Version;

                    // if we are executing as a stored procedure, then we need to add the call
                    // keyword.
                    if (CommandType == CommandType.StoredProcedure)
                    {
                        if (storedProcedure == null)
                            storedProcedure = new StoredProcedure(this);
                        sql = storedProcedure.Prepare( CommandText );
                    }

                    // tokenize the SQL
                    sql = sql.TrimStart(';').TrimEnd(';');
                    ArrayList tokens = TokenizeSql( sql );

                    foreach (string token in tokens)
                    {
                        if (token.Trim().Length == 0) continue;
                        if (token == ";" && ! connection.driver.SupportsBatch)
                        {
                            MemoryStream ms = (MemoryStream)writer.Stream;
                            if (ms.Length > 0)
                                buffers.Add( ms );

                            writer = new MySqlStreamWriter(new MemoryStream(), connection.Encoding);
                            writer.Version = connection.driver.Version;
                            continue;
                        }
                        else if (token[0] == parameters.ParameterMarker) 
                        {
                            if (SerializeParameter(writer, token)) continue;
                        }

                        // our fall through case is to write the token to the byte stream
                        writer.WriteStringNoNull(token);
                    }

                    // capture any buffer that is left over
                    MemoryStream mStream = (MemoryStream)writer.Stream;
                    if (mStream.Length > 0)
                        buffers.Add( mStream );

                    return buffers;
                }*/

    internal long EstimatedSize()
    {
      return CommandText.Length + Parameters.Cast<MySqlParameter>().Sum(parameter => parameter.EstimatedSize());
    }

    /// <summary>
    /// Verifies if a query is valid even if it has not spaces or is a stored procedure call
    /// </summary>
    /// <param name="query">Query to validate</param>
    /// <returns>If it is necessary to add call statement</returns>
    private bool AddCallStatement(string query)
    {
      if (string.IsNullOrEmpty(query)) return false;

      string keyword = query.ToUpper();
      int indexChar = keyword.IndexOfAny(new char[] { '(', '"', '@', '\'', '`', '\t', '\n' });
      if (indexChar > 0)
        keyword = keyword.Substring(0, indexChar);

      if (keywords == null)
        keywords = SchemaProvider.GetReservedWords().AsDataTable().
          Select().
          Select(x => x[0].ToString()).ToList();

      return !keywords.Contains(keyword);
    }

    #endregion

    #region Batching support

    internal void AddToBatch(MySqlCommand command)
    {
      if (Batch == null)
        Batch = new List<MySqlCommand>();
      Batch.Add(command);
    }

    internal string GetCommandTextForBatching()
    {
      if (BatchableCommandText == null)
      {
        // if the command starts with insert and is "simple" enough, then
        // we can use the multi-value form of insert
        if (String.Compare(CommandText.Substring(0, 6), "INSERT", StringComparison.OrdinalIgnoreCase) == 0)
        {
          string sql_mode = Connection.driver.Property("sql_mode").ToUpperInvariant();
          MySqlTokenizer tokenizer = new MySqlTokenizer(CommandText);
          tokenizer.AnsiQuotes = sql_mode.IndexOf("ANSI_QUOTES") != -1;
          tokenizer.BackslashEscapes = sql_mode.IndexOf("NO_BACKSLASH_ESCAPES") == -1;
          string token = StringUtility.ToLowerInvariant(tokenizer.NextToken());
          while (token != null)
          {
            if (StringUtility.ToUpperInvariant(token) == "VALUES" &&
                !tokenizer.Quoted)
            {
              token = tokenizer.NextToken();
              Debug.Assert(token == "(");

              // find matching right paren, and ensure that parens 
              // are balanced.
              int openParenCount = 1;
              while (token != null)
              {
                BatchableCommandText += token;
                token = tokenizer.NextToken();

                if (token == "(")
                  openParenCount++;
                else if (token == ")")
                  openParenCount--;

                if (openParenCount == 0)
                  break;
              }

              if (token != null)
                BatchableCommandText += token;
              token = tokenizer.NextToken();
              if (token != null && (token == "," ||
                  StringUtility.ToUpperInvariant(token) == "ON"))
              {
                BatchableCommandText = null;
                break;
              }
            }
            token = tokenizer.NextToken();
          }
        }
        // Otherwise use the command verbatim
        else BatchableCommandText = CommandText;
      }

      return BatchableCommandText;
    }

    #endregion

    // This method is used to throw all exceptions from this class. 
    private void Throw(Exception ex)
    {
      connection?.Throw(ex);
      throw ex;
    }

    /// <summary>
    /// Releases the resources used by the <see cref="MySqlCommand"/>
    /// </summary>
    public new void Dispose()
    {
      Dispose(true);
      GC.SuppressFinalize(this);
    }

    /// <summary>
    /// Releases the resources used by the <see cref="MySqlCommand"/>
    /// </summary>
    protected override void Dispose(bool disposing)
    {
      if (disposed)
        return;

      if (!disposing)
        return;

      if (statement != null && statement.IsPrepared)
        statement.CloseStatement();

      base.Dispose(disposing);

      disposed = true;
    }
  }
}
