Ruby on Rails

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.

--
Posted via http://www.ruby-forum.com/.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/dddb796db19e7c584b355fa384614dd8%40ruby-forum.com.
For more options, visit https://groups.google.com/d/optout.