Forum: Ruby on Rails Rails MySQL Stored Procedure Fun

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
F444e87b1967cc9cde6b48ad52fb87cd?d=identicon&s=25 Paulie (Guest)
on 2006-05-05 15:42
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
24d2f8804e6bb4b7ea6bd11e0a586470?d=identicon&s=25 Jeremy Kemper (Guest)
on 2006-05-05 22:39
(Received via mailing list)
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)

Best,
jeremy_______________________________________________
Rails mailing list
Rails@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails
F444e87b1967cc9cde6b48ad52fb87cd?d=identicon&s=25 Paulie (Guest)
on 2006-05-06 14:01
Jeremy Kemper 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)
>
> Best,
> jeremy_______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> 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
This topic is locked and can not be replied to.