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