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.
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.