# Rails MySQL Stored Procedure Fun **URL:** \ **Category:** Rails\ **Created:** [May 5, 2006, 3:42pm UTC](https://www.ruby-forum.com/t/rails-mysql-stored-procedure-fun/59692 "2006-05-05T15:42:25Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![paulie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/e495f1/32.png) [@paulie](https://www.ruby-forum.com/u/paulie)\ **Post date:** [May 5, 2006, 3:42pm UTC](https://www.ruby-forum.com/t/rails-mysql-stored-procedure-fun/59692/1 "2006-05-05T15:42:25Z")
Hi All. I have a question about calling MySQL stored procedures that is a little different from the usual. I know stored procedures are not really the ‘Rails way’ but in this case there is no other way to accomplish the same task and I have used them successfully. Background: I am using a query to produce a real-time status of items flowing through a factory. The query must trawl through thousands of records working out the first time an item hit a certain stage, this data is then further rolled up to produce a count of items processed at each stage each hour. The results are then generated as a cross-tab (again using a MySQL stored procedure) and displayed in a web-page. This data cannot be precalculated as scanning an item at a stage that has been scanned there previously should not register in the count. This query is quite simple to write in MySQL 5 (running on Ubuntu) as a stored procedure and runs very quickly. I have designed the results page in Rails (using model calls to find\_by\_sql to get results) and tested successfully on Webrick on a windows box. I previously had a problem with returning data from a MySQL stored procedure (due to Activerecord’s MySQL connection adapter not passing the CLIENT\_MULTI\_STATEMENTS and CLIENT\_MULTI\_RESULTS flags in the connection options) but overcame this by hacking the ActiveRecord MySQL connection adapter and adding the required options to be passed to the mysql\_real\_connect call. I ported the application to a production Apache / Ubuntu server which runs Rails MySQL sites without problems (in fact in the past it was able to access MySQL stored procedures as well via the connection adapter hack(I think!)) Unfortunately the app is unable to retrieve data from MySQL’s stored procedures. I get the ‘cannot return a result set in the given context’ error message which I initially had before I hacked the MySQL connection adapter on windows. I have checked the connection adapter on the Ubuntu box and it still has the hack in (I ensured that the adapter I was editing was the latest gem on the box (1.13.2). I have tried running the app in Webrick on Ubuntu and get the same results. I have even written a small ruby script that uses ActiveRecord to call the stored procedure and get the same error message. Has anyone any idea as to why the app behaves differently on Linux and Windows. Both have similar Rails versions, Ruby (1.8.2) and Gem installs. Is it possible one or the other is not using the C MySQL shared object vs. the ruby one (or something at a lower level that the connection adapter which is ignoring the extra connection options)? Where could I look to see if I can differentiate between the two installs to see if there is something mismatched? The fact that one system is win32 and the other \*nix doesnâ??t help. Thanks and Regards, Paulie ---
**Author:** ![paulie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/e495f1/32.png) [@paulie](https://www.ruby-forum.com/u/paulie)\ **Post date:** [May 5, 2006, 10:39pm UTC](https://www.ruby-forum.com/t/rails-mysql-stored-procedure-fun/59692/2 "2006-05-05T22:39:36Z")
On May 5, 2006, at 6:42 AM, Paulie wrote: > other \*nix doesn?t help. > The pure-Ruby MySQL driver likely doesn’t support for the feature you > rely on. $ ./script/console Loading development environment. > > require ‘mysql’ > > =\> true # Native bindings: > > Mysql.client\_info > > =\> “5.0.19” # Pure-Ruby driver bundled with Rails: > > Mysql.client\_info > > =\> “4.0-ruby-0.2.5” If you consider this feature widely useful, please consider contributing a patch to Active Record ([http://dev.rubyonrails.org](http://dev.rubyonrails.org)) Best, jeremy\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Rails mailing list removed\_email\_address@domain.invalid [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails) ---
**Author:** ![paulie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/e495f1/32.png) [@paulie](https://www.ruby-forum.com/u/paulie)\ **Post date:** [May 6, 2006, 2:01pm UTC](https://www.ruby-forum.com/t/rails-mysql-stored-procedure-fun/59692/3 "2006-05-06T14:01:23Z")
Jeremy K. wrote: > On May 5, 2006, at 6:42 AM, Paulie wrote: > > > other \*nix doesn?t help. > > The pure-Ruby MySQL driver likely doesn’t support for the feature you > > rely on. > > $ ./script/console > Loading development environment. > > > > require ‘mysql’ > > > =\> true > > # Native bindings: > > > > Mysql.client\_info > > > =\> “5.0.19” > > # Pure-Ruby driver bundled with Rails: > > > > Mysql.client\_info > > > =\> “4.0-ruby-0.2.5” > > If you consider this feature widely useful, please consider > contributing a patch to Active Record ([http://dev.rubyonrails.org](http://dev.rubyonrails.org)) > > Best, > jeremy\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ > Rails mailing list > removed\_email\_address@domain.invalid > [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails) Hi Jeremy, thanks for taking the time to reply. I have checked the Ubuntu box and it seems that it is using the non-ruby driver, but it reports its version as 4.0.24. This may be the cause of my problems. I have libmysqlclient version 12, 14 and 15 installed and it looks like rails is using an older one, can I safely remove the older libs (I do not need mysql clients other than for rails access) or can I specify which to use in the environment somewhere. I’m just concerned that if Rails is using the older client and I remove it, it may stop working. As to the patch for using stored procedures from Rails, I’ll tidy up the change and submit as you suggest. Thanks and regards, paulie