how to call stored procedure in ruby on rails?

Question

I am new to ROR. I want to call the Stored Procedure to process when I click the submit button in the VIEW.

    Model:
    -------

    class Pro::DataImport < ActiveRecord::Base
      attr_accessible :file_name, :process_name, :updated_by, :validates

    end

    Controller:
    -----------------

    class Pro::DataImportsController < ApplicationController
       before_filter :authenticate_user!
      layout "layouts/enr/energy_master"

      def index
       @pro_data_imports = Pro::DataImport.all 
      end

      def new
        @pro_data_import = Pro::DataImport.new
      end

    end

    View
    ----------

     <%= form_for @pro_data_import do %>

  <div class="field">
    Browse the file to upload:<br />
    <%= file_field_tag ':file_name' %>
  </div>

  <div class="actions">
    <%= submit_tag 'Import File' %>
  </div>
<% end %>


    Stored Proc
    ---------------

    ALTER PROCEDURE "DBA"."my_enr_test"(file_name long varchar)
    BEGIN
        INSERT INTO DBA.pro_data_imports(file_name) values(file_name);
    END

Thanks in Advance.. Please Help me. I want to get the filepath from the upload button and store into the database column file_name. How to execute the store procedure for the submit button. Please help me!!

Teddy · Accepted Answer · 2012-09-19T14:51:46.263

9

if you are using the ActiveRecord SQLServer Adapter, checkout:

http://rubydoc.info/gems/activerecord-sqlserver-adapter/3.2.9/ActiveRecord/ConnectionAdapters/Sqlserver/DatabaseStatements:execute_procedure

do something like this in your code

class Pro::DataImport < ActiveRecord::Base
  def self.update(user)
    self.execute_procedure("Stored Procedure Name", arg1, arg2)
  end
end

Every normal SQL query is converted to a stored procedure to be executed. That is how the SQL Server adapter for ActiveRecord works. So you only have to worry about this for permanent stored procedures defined in the database.

edited Sep 19 '12 at 14:51

answered Sep 19 '12 at 14:40

Teddy

18,357
2
30
42

yeah am using sqlserver adapter. But the problem is it showing error in the view page.... – Sri Sep 19 '12 at 14:44
Try replacing connection.execute("my_enr_test") with ActiveRecord::Base.execute_procedure(:my_enr_test) – Unixmonkey Sep 19 '12 at 14:50
Hi, I edit the question. Now its simple. Please tell me how to connect my storedprocedure to my submit buttton. When i click the button it calls the storedproc and get the filepath into the file_name column in the database. please – Sri Sep 19 '12 at 16:18
I would start with a simple proof-of-concept and build up to file uploads. In general, you would have 2 actions defined in your controller: `update` and `create`. Those actions will receive form submission responses. Inside those actions, you can have the code execute a stored procedure. – Teddy Sep 21 '12 at 03:55

score 0 · Answer 2 · answered Nov 10 '15 at 15:11

# PL/SQL records or object type parameters should be passed as Hash

p_employee = { :employee_id => 1, :first_name => 'First', :last_name => 'Last', :hire_date => Time.local(2000,01,31) }
 plsql.test_full_name(p_employee)

# test_full_name is procedure name in oracle
# p_employee holds parameter list 
#employee_id is first param defined in stored procedure

how to call stored procedure in ruby on rails?

2 Answers2

Linked