How to migrate Stored Procedure

Rails
1

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…

2

Hi,

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

NAYAK

On Thu, Jan 15, 2009 at 7:25 PM, Srinath A. <
[email protected]> wrote:

end
stock_id=l_stock AND held_by=userid;

Posted via http://www.ruby-forum.com/.

–

  • NAYAK
3

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,

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

NAYAK

On Thu, Jan 15, 2009 at 7:25 PM, Srinath A. <
[email protected]> wrote:

end
stock_id=l_stock AND held_by=userid;

Posted via http://www.ruby-forum.com/.

–

  • NAYAK
4

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

On Thu, Jan 15, 2009 at 7:32 PM, Srinath A. <
[email protected]> wrote:

  • NAYAK

–
Posted via http://www.ruby-forum.com/.

–

  • NAYAK
5

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,

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

On Thu, Jan 15, 2009 at 7:32 PM, Srinath A. <
[email protected]> wrote:

  • NAYAK

–
Posted via http://www.ruby-forum.com/.

–

  • NAYAK
6

On Jan 15, 2009, at 9:30 AM, Srinath A. 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 B. http://agileconsultingllc.com
[email protected]