Forum: Ruby on Rails Cannot execute stored procedure in Rails 3 and mysql2 gem

Fb040eff21f11e62d309e2c5a848fc90?d=identicon&s=25 Nirmalya Ghosh (nirmalya23)
on 2014-04-02 08:04
Hi All,

Suppose I have a stored procedure named procedure().

Running ActiveRecord::Base.connection.execute("CALL procedure()") will
give you a bunch of errors in different cases while using mysql2 adapter
like:

ActiveRecord::StatementInvalid: Mysql2::Error: PROCEDURE can't return a
result set in the given context: procedure() PROCEDURE

or

ActiveRecord::StatementInvalid: Mysql2::Error: Commands out of sync; you
can't run this command now.

How can The problem be solved? Need Assistance.


Best Regards,

Nirmalya Ghosh
5764263422e3395eab7f91794bbead6b?d=identicon&s=25 Zinia Dutta Gupta (zinia123)
on 2014-04-02 12:10
Hello Nirmalya,

It's a very important question which you have right now, fortunately I
think I know the answer, here I am giving you the solution ---

Solution :
When MySQL runs stored procedure it has to know that client can handle
multiple result sets.

We will create patch file for that in our initializer folder of our
application.

Create mysql2_adapter.rb file in config/initializers with the following
content:

module ActiveRecord
  class Base
  # Overriding ActiveRecord::Base.mysql2_connection
  # method to allow passing options from database.yml
  #
  # Example of database.yml
  #
  #   login: &login
  #   socket: /tmp/mysql.sock
  #   adapter: mysql2
  #   host: localhost
  #   encoding: utf8
  #
  # @param [Hash] config hash that you define in your
  #   database.yml
  # @return [Mysql2Adapter] new MySQL adapter object
  #
  def self.mysql2_connection(config)
    config[:username] = 'root' if config[:username].nil?

    if Mysql2::Client.const_defined? :FOUND_ROWS
      config[:flags] = config[:flags] ? config[:flags] |
Mysql2::Client::FOUND_ROWS : Mysql2::Client::FOUND_ROWS
    end

    client = Mysql2::Client.new(config.symbolize_keys)
    options = [config[:host], config[:username], config[:password],
config[:database], config[:port], config[:socket], 0]
    ConnectionAdapters::Mysql2Adapter.new(client, logger, options,
config)
  end

  def self.find_by_sp(sql)
    connection.select_sp(sanitize_sql(sql), "#{name} Load").collect! {
|record| instantiate(record) }
  end
  end


  module ConnectionAdapters
  class Mysql2Adapter < AbstractMysqlAdapter
    # This method is for running stored procedures.
    #
    # @param [String] the name of procedure
    #   and arguments for procedure
    # @return [Hash]
    #
    def select_sp(sql, name = nil)
      connection = ActiveRecord::Base.connection
      begin
        connection.select_all(sql, name)
      rescue NoMethodError
      ensure
        connection.reconnect! unless connection.active?
      end
    end
  end
  end
end



If by some reason MySQL drops connection after executing stored
procedure, then we have to restore it before doing anything else. For
that purpose we have created the select_sp(sql, name = nil).

This method does several things:

  * it intercepts NoMethodError that could be raised when your procedure
returns nothing
  * it restores connection if it was dropped

So now you can run ActiveRecord::Base.procedure("CALL procedure()")
without any errors and get a hash with results.


I think it will help you.

Best Regards,
Zinia Dutta Gupta.
Fb040eff21f11e62d309e2c5a848fc90?d=identicon&s=25 Nirmalya Ghosh (nirmalya23)
on 2014-04-02 12:13
Thanks Zinia for the reply and I think I got the answer.

Thanks Again.


Best Regards,

Nirmalya Ghosh








Zinia Dutta Gupta wrote in post #1141735:
> Hello Nirmalya,

>
> Solution :
> When MySQL runs stored procedure it has to know that client can handle
> multiple result sets.
>
> We will create patch file for that in our initializer folder of our
> application.
>
> Create mysql2_adapter.rb file in config/initializers with the following
> content:
>
> module ActiveRecord
>   class Base
>   # Overriding ActiveRecord::Base.mysql2_connection
>   # method to allow passing options from database.yml
>   #
>   # Example of database.yml
>   #
>   #   login: &login
>   #   socket: /tmp/mysql.sock
>   #   adapter: mysql2
>   #   host: localhost
>   #   encoding: utf8
>   #
>   # @param [Hash] config hash that you define in your
>   #   database.yml
>   # @return [Mysql2Adapter] new MySQL adapter object
>   #
>   def self.mysql2_connection(config)
>     config[:username] = 'root' if config[:username].nil?
>
>     if Mysql2::Client.const_defined? :FOUND_ROWS
>       config[:flags] = config[:flags] ? config[:flags] |
> Mysql2::Client::FOUND_ROWS : Mysql2::Client::FOUND_ROWS
>     end
>
>     client = Mysql2::Client.new(config.symbolize_keys)
>     options = [config[:host], config[:username], config[:password],
> config[:database], config[:port], config[:socket], 0]
>     ConnectionAdapters::Mysql2Adapter.new(client, logger, options,
> config)
>   end
>
>   def self.find_by_sp(sql)
>     connection.select_sp(sanitize_sql(sql), "#{name} Load").collect! {
> |record| instantiate(record) }
>   end
>   end
>
>
>   module ConnectionAdapters
>   class Mysql2Adapter < AbstractMysqlAdapter
>     # This method is for running stored procedures.
>     #
>     # @param [String] the name of procedure
>     #   and arguments for procedure
>     # @return [Hash]
>     #
>     def select_sp(sql, name = nil)
>       connection = ActiveRecord::Base.connection
>       begin
>         connection.select_all(sql, name)
>       rescue NoMethodError
>       ensure
>         connection.reconnect! unless connection.active?
>       end
>     end
>   end
>   end
> end
>
>
>
> If by some reason MySQL drops connection after executing stored
> procedure, then we have to restore it before doing anything else. For
> that purpose we have created the select_sp(sql, name = nil).
>
> This method does several things:
>
>   * it intercepts NoMethodError that could be raised when your procedure
> returns nothing
>   * it restores connection if it was dropped
>
> So now you can run ActiveRecord::Base.procedure("CALL procedure()")
> without any errors and get a hash with results.

> Best Regards,
> Zinia Dutta Gupta.
Please log in before posting. Registration is free and takes only a minute.
Existing account

NEW: Do you have a Google/GoogleMail, Yahoo or Facebook account? No registration required!
Log in with Google account | Log in with Yahoo account | Log in with Facebook account
No account? Register here.