Forum: Ruby on Rails Stored Procedures MYSQL and Rails 2.3.2

Search yielded only ancients results with a lot of dead links that didnt
help me any further.

In the past it has been a pretty big issue to incorperate stored
procedures into Rails. By now, has 2.3.2 in 2009 made any changes to
this?

How would one go about in 2.3.2 to call a stored procedure and work with
its result set? (Be it a genuine object, or a hash)

Model.connection.execute "CALL storedProc()"
Above line will give the error that it cant return results for the given
context.

My stored proc returns column values from several models and some
dynamicly calculated columns not found in any model.

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.


On Aug 24, 10:15 am, Chris Dekker <rails-mailing-l...@andreas-s.net>

This works for me:
     ActiveRecord::Base.connection.execute("call SP_name (#{param1},
#{param2}, ... )")



2009/8/24 E. Litwin <elitwin@rocketmail.com>

Adding the 65536 flag seems to allow me to gather a resultset from the 
stored procedure, yet my commands go out of sync. Running any query 
(even a simple Model.first) after running a Stored Proc that returns 
results gives this error:

ActiveRecord::StatementInvalid: Mysql::Error: Commands out of sync; you 
can't run this command now: call scores(DATE('2009-08-24'), 3282, 1);

Check this

http://nasir.wordpress.com/2007/12/03/stored-procedures-and-rails

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

nas wrote:
> Check this
> 
> http://nasir.wordpress.com/2007/12/03/stored-procedures-and-rails
> 
> 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

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..."

On Aug 28, 1:10 am, Chris Dekker <rails-mailing-l...@andreas-s.net>

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!

 https://rails.lighthouseapp.com/projects/8994/tickets/3151-mysql-adapter-update-to-enable-use-of-stored-procedures

Fogive me if I'm entering this conversation in the middle, but I'm 
having problems using stored procedures with the latest patch (on rails 
2.3.4) and with rails 2.3.5 (which, from my understanding, has the mysql 
patch included).

When I use find_by_sql("call sp()"), I get the error:

Mysql::Error: Commands out of sync; you can't run this command now: SHOW 
FIELDS FROM `sometable`

When I use connection.execute or connection.select_all to call the 
stored procedure, I get a hash returned. That's great that it works, but 
I really need an AR result set, so I can use the getter methods already 
peppered throughout my application.

If find_by_sql is not supposed to work with stored procedures, is there 
at least a way to convert the hash returned by the connection.execute 
method to an AR result object?




Justin Bailey wrote:
> 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!
> 
>  https://rails.lighthouseapp.com/projects/8994/tickets/3151-mysql-adapter-update-to-enable-use-of-stored-procedures

Bill Richardson wrote:
> ...
> 
> When I use connection.execute or connection.select_all to call the 
> stored procedure, I get a hash returned.
> 
> 


A correction to my previous post...

When I use connection.execute("call sp()"), I get a MySql::Result 
returned.

Sorry for the multiple updates. Last one until I hear good news...

It doesn't matter what method I used to call the stored procedure from 
Rails - I always get the command-out-of-sync error. The stored procedure 
returns data just fine. But any call to mysql after that returns the 
error.

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)