How to migrate Stored Procedure

Hi all,

I was wondering how to migrate a stored procedure by using rake
db:migrate .
my code is :
class StoredProcedures < ActiveRecord::Migration
  def self.up

  end
end
My stored procedure is
DELIMITER $$

DROP PROCEDURE IF EXISTS `stocks`.`stock_details`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `stock_details`(IN l_stock
INT,IN userid INT,OUT l_stockid INT,OUT l_stockbalance FLOAT,OUT sid
INT)
BEGIN
SELECT stock_id,units,held_by,id INTO
l_stockid,l_stockbalance,userid,sid FROM user_stock_details WHERE
stock_id=l_stock AND held_by=userid;
END$$

DELIMITER ;

How to add this in my migration file to create when run rake db:migrate
??

thanks,
sri..

Hi,

You can use execute in your self.up and self.down methods to create and destroy stored procedures

NAYAK

Hi,
Could you plz explain me in brief with sample code, i tried but got rake
errors !!. I'm new to SP

thanks
sri..

Vishwanath Nayak wrote:

Hi,

We have created procedures like this
execute(“Create Procedure Clear_Session LANGUAGE SQL Delete from sessions where updated_at <(current timestamp - 4 HOURS)”)

In your case you can use,
sql = %Q! CREATE DEFINER=root@localhost PROCEDURE stock_details(IN l_stock
INT,
IN userid INT,OUT l_stockid INT,OUT l_stockbalance FLOAT,OUT sid
INT)
BEGIN
SELECT stock_id,units,held_by,id INTO
l_stockid,l_stockbalance,

userid,sid FROM user_stock_details WHERE

stock_id=l_stock AND held_by=userid;!

execute(“DROP PROCEDURE IF EXISTS stocks.stock_details”)
execute sql

In case of any issues please revert back

NAYAK

Hi Nayak,
Thanks for the Reply.
But i was facing this error
-- execute("DROP PROCEDURE IF EXISTS 'stocks'.'stock_details'")
rake aborted!
Mysql::Error: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near ''stocks'.'stock_details'' at line 4: DROP PROCEDURE IF EXISTS
'stocks'.'stock_details'

i was strugling to catch the issue !

thanks again
....

Vishwanath Nayak wrote:

Hi Nayak,
Thanks for the Reply.
But i was facing this error
-- execute("DROP PROCEDURE IF EXISTS 'stocks'.'stock_details'")

Try:
     execute('DROP PROCEDURE IF EXISTS "stocks"."stock_details"')
The type of quote makes a difference. You can also use the specific ID-QUOTE for MySQL like:
     execute('DROP PROCEDURE IF EXISTS `stocks`.`stock_details`')
But the back-quote looks more like you're trying to do something at the Ruby level so the normal double-quote might be preferred. (I don't know if it would be more portable.)

-Rob

Rob Biedenharn http://agileconsultingllc.com
Rob@AgileConsultingLLC.com