Calling Database Procedure From Ruby on Rails

Question

I'm developing an application using Ruby on Rails and NuoDB and I'm trying to CALL procedures from the database. I'm testing it using the Rails console but I'm only getting a "TRUE" response when I call it using the following command.

ActiveRecord::Base.connection.execute("CALL SHOW_FEEDBACKS_PRC( 'form_name' )")

My stored procedure is this:

CREATE PROCEDURE database_name.show_feedbacks_prc (IN P_IN_form string) 

     returns tmp_feedbacks (txt1 string, rating integer, comment string, created_at timestamp, updated_at timestamp)
     language sql
     security invoker

as
     insert into tmp_feedbacks 
       select txt1, rating, comment, created_at, updated_at
         from database_name.feedbacks
        where form = p_in_form;

END_PROCEDURE

It's a simple query that returns only a list of "feedbacks" which are under a specific "form".

The procedure works when I use NuoDB's console and it returns a table that displays the requested data but when I call it using Rail's console it would only return a "true" response when I execute the SQL command.

Is it possible to get a response as an array of requested data and how do I do this?

I'm trying to execute procedures inside the database instead of making loops inside the rails controllers.

hello tim, I forgot about this question. I've posted an answer just in case somebody needs it. — Patrick Angodung

Patrick Angodung Patrick Angodung · Accepted Answer · 2015-02-20T07:34:23

So I totally forgot about this but I solved this issue a while back and here's a sample of what I did:

SAMPLE_PROCEDURE:

CREATE PROCEDURE sample_procedure ( IN input_1 INTEGER ) 
    RETURNS return_msg ( col_1 STRING , col_2 INTEGER ) AS 
            VAR value_string STRING; 
            VAR value_integer INTEGER;
            value_string = input_1;
            value_integer = input_1+10;
        INSERT INTO return_msg VALUES ( value_string , value_integer);
    RETURN; 
END_PROCEDURE

And here is how I call it:

ActiveRecord::Base.connection.execute("call sample_procedure(1)")

Rails would return the following:

[{"col_1"=>"1", "col_2"=>11}]

I hope this helps.

Calling Database Procedure From Ruby on Rails

1 Answers