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.. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. < rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > 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.. > -- > Posted via http://www.ruby-forum.com/. > > > >-- - NAYAK --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. < > rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote: > >> end >> stock_id=l_stock AND held_by=userid; >> -- >> Posted via http://www.ruby-forum.com/. >> >> > >> > > > -- > - NAYAK-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. <
rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:
>
> 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. <
> > rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:
> >
> >> end
> >> stock_id=l_stock AND held_by=userid;
> >> --
> >> Posted via http://www.ruby-forum.com/.
> >>
> >> >
> >>
> >
> >
> > --
> > - NAYAK
>
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>
--
- NAYAK
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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. <
> rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:
>
>>
>> >
>> > - NAYAK
>>
>> --
>> Posted via http://www.ruby-forum.com/.
>>
>> >
>>
>
>
> --
> - NAYAK
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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 Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> > 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. < >> rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote: >>>> >>>> - NAYAK >>> -- >>> Posted via http://www.ruby-forum.com/. >>>> >> -- >> - NAYAK--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---