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
??
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
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'
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.)