Forum: Ruby on Rails How to migrate Stored Procedure

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Srinath A. (Guest)
on 2009-01-15 15:55
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..
Vishwanath N. (Guest)
on 2009-01-15 15:59
(Received via mailing list)
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. <
removed_email_address@domain.invalid> wrote:

> end
> stock_id=l_stock AND held_by=userid;
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>


--
- NAYAK
Srinath A. (Guest)
on 2009-01-15 16:02
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. <
> removed_email_address@domain.invalid> wrote:
>
>> end
>> stock_id=l_stock AND held_by=userid;
>> --
>> Posted via http://www.ruby-forum.com/.
>>
>> >
>>
>
>
> --
> - NAYAK
Vishwanath N. (Guest)
on 2009-01-15 16:14
(Received via mailing list)
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. <
removed_email_address@domain.invalid> wrote:

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


--
- NAYAK
Srinath A. (Guest)
on 2009-01-15 16:30
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. <
> removed_email_address@domain.invalid> wrote:
>
>>
>> >
>> > - NAYAK
>>
>> --
>> Posted via http://www.ruby-forum.com/.
>>
>> >
>>
>
>
> --
> - NAYAK
Rob B. (Guest)
on 2009-01-15 16:48
(Received via mailing list)
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
removed_email_address@domain.invalid
This topic is locked and can not be replied to.