MySql 5.0 introduced stored procedures and functions, but I wasn’t able to get them to work right away using Rails 1.1. This is what I did to get them to work using Ruby 1.8.4 and MySql 5.0.20 on Windows 2000:
Caution: This involves modifying some (very easy) code in the Rails MySql Adapter file. If you update rails you will likely have to re-do these changes.
1. Verify you have MySql 5.0+: Make sure you are using Mysql 5.0 or greater, since stored procs did not exist before this. If you want stored functions to work you have to use an even later version (I used 5.0.20) since there was a bug in earlier MySql 5.0 versions which results in an execute permission error on stored functions. (This bug did not effect stored procedures).
2. Install native MySql Connector: Install the native MySQl/Ruby API http://tmtm.org/en/mysql/ruby . This is supposedly 15% faster than the ruby-based API that ships standard with Rails. The ruby-based one also has other difficulties operating with newer versions of MySql. The latest version of Instant Rails says it includes the native API, but I didn’t confirm this.
Just install this using ruby gems : “c:>gem install mysql”, choose the mswin32 compiled version (not the ruby one which you woul have to compile yourself).
3. Change MySql Connection String: Change some Rails code to allow the client connection to be created with the correct options. The reason Stored Procs don’t work is because MySql knows there is a possibility they might return more than one result set. If you don’t specify the CLIENT_MULTI_STATEMENTS option in your connection, MySql will not return any result sets, but will give you the error: Error ‘PROCEDURE [Procedure Name] can’t return a result set in the given context’. it will give you this error regardless of how many result sets the Stored Procedure was programmed to (or actually does) return.
So, in the mysql_adaptor.rb file (located at C:\ruby\lib\ruby\gems\1.8\gems\activerecord-1.14.0\lib\active_record\connection_adapters\mysql_adapter.rb
in my installation .. ruby was installed at c:\ruby):
Find the line that looks like this:
It exists around line 37 (near the last line in the ActiveRecord module, Base class, self.mysql_connection(config) function).
Add a “, 65536” to the line so that it looks like this:
ConnectionAdapters::MysqlAdapter.new(mysql, logger, [host, username, password, database, port, socket, 65536], config)(65536 is the ID of the flag for CLIENT_MULTI_STATEMENTS).
4. Add a method to call the Stored Procs: If you don’t do this step you will get a “Lost connection to MySql server during query” error on any query you try to execute after successfully calling a stored procedure. It get’s annoying so here’s how to avoid it:
Add this new method to your mysql_adaptor.rb file. just place this new method directly under the select_one and select_all methods that are already there. Use this new method when calling stored procs. It just cycles through any extra result sets returned. For some reason it fixes the problem.
def select_sp(sql, name = nil) rows = select(sql, name = nil) while (@connection.more_results?()) @connection.next_result() end return rows end5. The End: That’s it!!! Your stored procs should return record sets now. If you are using WEBrick you need to restart your web service (a.k.a. restart WEBrick) for the changes to take effect. To actually use your newfound stored proc powers, do something like this in your model:
class Login < ActiveRecord::Base def self.PerformLoginRequest(screenname, password) sql = “call login_request(” + connection.quote(screenname) + “,” + connection.quote(password) + “);” connection.select_sp(sql) end endThe above example returns an array of hashes represents your records (the array) and your column name/ value pairs (the hashes).
Addendum
If after making the above edits you still encounter an error refering to the procedure context such as:
“ActiveRecord::StatementInvalid: Mysql::Error: PROCEDURE vc.testsp can’t return a result set in the given context:”
then try modifying the line in mysql_adapter.rb to look like this:
ConnectionAdapters::MysqlAdapter.new(mysql, logger, [host, username, password, database, port, socket, 65536 + 131072, config)
See http://dev.mysql.com/sources/doxygen/mysql-5.1/mysql__com_8h-source.html. CLIENT_MULTI_RESULTS = 131072
This has been seen to solve the above error when a MySQL stored procedure called another stored procedure during its processing.
MySql 5.0 introduced stored procedures and functions, but I wasn’t able to get them to work right away using Rails 1.1. This is what I did to get them to work using Ruby 1.8.4 and MySql 5.0.20 on Windows 2000:
Caution: This involves modifying some (very easy) code in the Rails MySql Adapter file. If you update rails you will likely have to re-do these changes.
1. Verify you have MySql 5.0+: Make sure you are using Mysql 5.0 or greater, since stored procs did not exist before this. If you want stored functions to work you have to use an even later version (I used 5.0.20) since there was a bug in earlier MySql 5.0 versions which results in an execute permission error on stored functions. (This bug did not effect stored procedures).
2. Install native MySql Connector: Install the native MySQl/Ruby API http://tmtm.org/en/mysql/ruby . This is supposedly 15% faster than the ruby-based API that ships standard with Rails. The ruby-based one also has other difficulties operating with newer versions of MySql. The latest version of Instant Rails says it includes the native API, but I didn’t confirm this.
Just install this using ruby gems : “c:>gem install mysql”, choose the mswin32 compiled version (not the ruby one which you woul have to compile yourself).
3. Change MySql Connection String: Change some Rails code to allow the client connection to be created with the correct options. The reason Stored Procs don’t work is because MySql knows there is a possibility they might return more than one result set. If you don’t specify the CLIENT_MULTI_STATEMENTS option in your connection, MySql will not return any result sets, but will give you the error: Error ‘PROCEDURE [Procedure Name] can’t return a result set in the given context’. it will give you this error regardless of how many result sets the Stored Procedure was programmed to (or actually does) return.
So, in the mysql_adaptor.rb file (located at C:\ruby\lib\ruby\gems\1.8\gems\activerecord-1.14.0\lib\active_record\connection_adapters\mysql_adapter.rb
in my installation .. ruby was installed at c:\ruby):
Find the line that looks like this:
It exists around line 37 (near the last line in the ActiveRecord module, Base class, self.mysql_connection(config) function).
Add a “, 65536” to the line so that it looks like this:
ConnectionAdapters::MysqlAdapter.new(mysql, logger, [host, username, password, database, port, socket, 65536], config)(65536 is the ID of the flag for CLIENT_MULTI_STATEMENTS).
4. Add a method to call the Stored Procs: If you don’t do this step you will get a “Lost connection to MySql server during query” error on any query you try to execute after successfully calling a stored procedure. It get’s annoying so here’s how to avoid it:
Add this new method to your mysql_adaptor.rb file. just place this new method directly under the select_one and select_all methods that are already there. Use this new method when calling stored procs. It just cycles through any extra result sets returned. For some reason it fixes the problem.
def select_sp(sql, name = nil) rows = select(sql, name = nil) while (@connection.more_results?()) @connection.next_result() end return rows end5. The End: That’s it!!! Your stored procs should return record sets now. If you are using WEBrick you need to restart your web service (a.k.a. restart WEBrick) for the changes to take effect. To actually use your newfound stored proc powers, do something like this in your model:
class Login < ActiveRecord::Base def self.PerformLoginRequest(screenname, password) sql = “call login_request(” + connection.quote(screenname) + “,” + connection.quote(password) + “);” connection.select_sp(sql) end endThe above example returns an array of hashes represents your records (the array) and your column name/ value pairs (the hashes).
Addendum
If after making the above edits you still encounter an error refering to the procedure context such as:
“ActiveRecord::StatementInvalid: Mysql::Error: PROCEDURE vc.testsp can’t return a result set in the given context:”
then try modifying the line in mysql_adapter.rb to look like this:
ConnectionAdapters::MysqlAdapter.new(mysql, logger, [host, username, password, database, port, socket, 65536 + 131072, config)
See http://dev.mysql.com/sources/doxygen/mysql-5.1/mysql__com_8h-source.html. CLIENT_MULTI_RESULTS = 131072
This has been seen to solve the above error when a MySQL stored procedure called another stored procedure during its processing.