Rails community doesn’t like deriving business logic to the database, but in some cases stored procedures are very helpful and many people trying to use them in Rails, however it’s not so easy as you can imagine.
ActiveRecord::Base.connection.execute("CALL proc01") will give you a bunch of errors in different cases.
Let’s say your procedure returns some result set. So running that procedure will give you exception
In case your procedure doesn’t return any result set, then running it twice will give you another exception
In other case when stored procedure doesn’t return any result set at all, you’ll get
All these issues are well known, however they aren’t fixed yet, even in Rails 3.
Let’s look at the first issue. When MySQL runs stored procedure it has to know that client can handle multiple result sets. By default MySQL assumes that client cannot handle this unless you set
CLIENT_MULTI_RESULTS flag when establishing connection to MySQL server. It’s not a surprise that neither Rails or MySQL2 doesn’t do this, because in most projects you don’t need multiple result sets. In the future we’ll probably have an option to configure this, but until then let’s create a workaround.
We use MySQL2. Its latest 0.2.6 gem release is kind of outdated for Ruby 1.9.2, so we did a fork from edge version at some stable point. MySQL2 defines its own mysql adapter for Rails in
lib/active_record/connection_adapters/mysql2_adapter.rb. We’re interested in a method that creates connection object:
1 2 3 4 5 6 7 8 9 10 11
This place looks good to put our additional flag for MySQL, but wait! There is other flags already, so let’s just re-use this and let adapter pass it further.
Create a file in
config/initializers with the following content:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
So now you can pass any additional options from your
database.yml. See that
131072? This is the value of
CLIENT_MULTI_RESULTS constant. Not so clear, because you have to know those magic numbers, but OK for the beginning.
If you want to pass more options, remember that you must use bitwise
OR operator, so in
database.yml it will be
65536 is the value of
CLIENT_MULTI_STATEMENTS constant. BTW, enabling only
CLIENT_MULTI_STATEMENTS will automatically enable