Stored Procedures MYSQL and Rails 2.3.2

rubyonrails-talk
1

I've still had to hack the mysql_adapter to set a flag upon opening the MySQL database connection in order to be able to use SPs:

module ActiveRecord   class Base     def self.mysql_connection(config)        ...        # last line of method - add 65536 flag        ConnectionAdapters::MysqlAdapter.new(mysql, logger, [host, username, password, database, port, socket, 65536], config)     end   end end

This has worked for me, and I have not read about a fix in 2.x to handle this.

2

This works for me:

ActiveRecord::Base.connection.execute(“call SP_name (#{param1}, #{param2}, … )”)

3

Check this

4

nas wrote:

Check this

Stored Procedures and Rails – Part 1 – Nasir Jamal

On Aug 27, 3:09�pm, Chris Dekker <rails-mailing-l...@andreas-s.net>

Thanks, but that link did not help me.

For MYSQL it doesn't even work. Stored procedures are called through 'CALL', not 'EXECUTE'.

Also as I wrote in my third post, I already get the stored procedure to execute, the problem seems to lie in the closing / freeing of the result set.

Calling .free on the returned result set does not solve anything. Tested on both the latest 5.0 and 5.1 MySQL databases with the newest 2.8.1 mysql gem

5

Try extending mysql_adapter.rb and add this method:

      def select_sp(sql, name = nil)         rows = select(sql, name = nil)         while (@connection.more_results?())           @connection.next_result()         end         return rows       end

Then call the SP using this method from your controller/model where the sql param is: "call my_sp..."

6

E. Litwin wrote:

I've still had to hack the mysql_adapter to set a flag upon opening the MySQL database connection in order to be able to use SPs:

module ActiveRecord   class Base     def self.mysql_connection(config)        ...        # last line of method - add 65536 flag        ConnectionAdapters::MysqlAdapter.new(mysql, logger, [host, username, password, database, port, socket, 65536], config)     end   end end

This has worked for me, and I have not read about a fix in 2.x to handle this.

I've submitted a patch to Rails 2.3.4 fixing this problem - any support for my ticket is appreciated!

#3151 MySQL adapter update to enable use of stored procedures - Ruby on Rails - rails

7

From the script/console:

$ script/console Loading development environment (Rails 2.3.5) /Library/Ruby/Gems/1.8/gems/rails-2.3.5/lib/rails/gem_dependency.rb:119:Warning: Gem::Dependency#version_requirements is deprecated and will be removed on or after August 2010. Use #requirement

ActiveRecord::Base.connection.active?

=> true

User.all.size

=> 2

ActiveRecord::Base.connection.active?

=> true

ActiveRecord::Base.connection.execute("CALL proc01")

=> #<Mysql::Result:0x103429c90>

ActiveRecord::Base.connection.execute("CALL proc01")

ActiveRecord::StatementInvalid: Mysql::Error: Commands out of sync; you can't run this command now: CALL proc01   from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract_adapter.rb:219:in `log'   from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/mysql_adapter.rb:323:in `execute'   from (irb):5

ActiveRecord::Base.connection.active?

=> false

ActiveRecord::Base.connection.reconnect!

=> nil

ActiveRecord::Base.connection.execute("CALL proc01")

=> #<Mysql::Result:0x1034102e0>

ActiveRecord::Base.connection.active?

=> false

ActiveRecord::Base.connection.reconnect!

=> nil

ActiveRecord::Base.connection.execute("CALL proc01")

=> #<Mysql::Result:0x1033fc8a8>

ActiveRecord::Base.connection.reconnect!

=> nil

ActiveRecord::Base.connection.execute("CALL proc01")

=> #<Mysql::Result:0x1033f0b98>

It looks like CALLing a stored procedure will drop a ActiveRecord connection. Maybe a "reconnect! if !active?" can be a quick patch...

p.s. there is a ticket: https://rails.lighthouseapp.com/projects/8994/tickets/3151-mysql-adapter-update-to-enable-use-of-stored-procedures (not tried yet)