Stored Procedures and Rails – Part 1

3 12 2007


There is no documentation in Rails about how to use stored procedures or atleast it is difficult to find where to look at. And according to DHH stored procedures is not the Rails Way to do things. DHH mentioned in his post

I want a single layer of cleverness: My domain model. Object-orientation is all about encapsulating clever. Letting it sieve half ways through to the database is a terrible violation of those fine intentions. And I want no part of it.

I somewhat agree to what DHH has to say. And that’s all fine but what if we don’t have any other option apart from using stored procedure and our stored procedure has some complex logic which we don’t want to put in our Ruby code then here is a little hack to it

ActiveRecord::Base.connection.execute("execute procedure_name")

But the above code will not return anything it will only execute your procedure, considering everything went OK.

Moreover, it’s not DRY if everytime you have to write that line of code just to call a procedure.

Here is a better way to do it:

1) Create a class and let’s call it

Class DbStoredProcedure
  your methods here .......
end

2) Create a class method within it

def self.fetch_db_records(proc_name_with_parameters)
  ActiveRecord::Base.connection.execute(proc_name_with_parameters)
end

Then simply call it as

DbStoredProcedure.fetch_db_records("exec procedure_name 'args1', 'args2', 'and so on' ")

As mentioned above, this will only execute a procedure without returning any value. To return records or values from stored procedures can use other methods provided by the AR,

class DbStoredProcedure < ActiveRecord::Base

  def self.fetch_db_records(proc_name_with_parameters)
    connection.select_all(proc_name_with_parameters)
  end

  def self.insert_update_delete_calculate(proc_name_with_parameters)
    connection.execute(proc_name_with_parameters)
  end

  def self.fetch_val_from_sp(proc_name_with_parameters)
    connection.select_value(proc_name_with_parameters)
  end

end

And then simply call it like

DbStoredProcedure.method_name("exec procedure_name 'args1', 'args2', 'and so on' ")

Sweet…

If you know a better way to do it then I would love to know your views.

Stored Procedures and Rails – Part 2

About these ads

Actions

Information

13 responses

26 03 2008
Nicholaslm

favorited this one, brother

31 07 2008
Do I need Stored Procedures? Maybe or Maybe not. « class Nasir < Jamal

[…] Maybe or Maybe not. 31 07 2008 I have written a couple of posts about 7-8 months back (here and here) on how to call stored procedures from a rails application. After using stored procedures […]

16 09 2008
Stored Procedures in Ruby on Rails « Anand M Muranal

[…] More information click […]

13 02 2009
Ric

Some good ideas here. Thanks.

13 05 2009
Sofija

Thank you! It is a really great to be able to find some solution when there is no core solution.

Anyway, for RoR to be able to be an enterprise class programming language and framework using stored procedures and views in an database should be part of core.
I’m not sure if there are RoR developers that are aware that procedures are best way to implement database logic, except for maybe some trivial models. That’s why I believe using stored procedures should be supported by core ActiveRecord and defined per each available datrabase adapter to support different usages. One ExecProc! should do it…
Also did anyone notice availability explicit of START TRANSACTION END TRANSACTION implementations? These are like crucial for any serious developement.

9 03 2010
Suset Fernandez

I am triying to call my stored procedures using the class CallStoredProcedures as you did suggest but it does not work. Can I have an example please?.

10 03 2010
nasir

@Suset could you please give some more information on

1. what exactly you tried,

2. why do you think it is not working,

3. which db you are using, and

4. any other information or errors, etc.

12 03 2010
Suset Fernandez

1. what exactly you tried,

I need to call a postgre stored procedure i did program some time ago, so I copied the code you suggets before.
I created the class DbStoredProcedure

2. why do you think it is not working,

When I call any method of the class it does not return anything or show any error, simply nothing happens

3. which db you are using, and

Postgres

4. any other information or errors, etc.

I am totally “new” using Ruby on Rails
I think I need a functional example, to understand better.

23 03 2010
Horace Ho

The first call to a MySQL stored procedure is successfully. However, any further call to the same stored procedure returns a “Commands out of sync” error. I posted the details in: http://stackoverflow.com/questions/2498331/how-to-call-mysql-stored-procedure-from-rails

18 08 2010
Samuel Vinícius

very nice Horace

19 08 2010
Petrus

Tried it & ditto the same error, jruby thinks I am trying to do an update. I tried to add the code on an existing model. It just does not work.

19 08 2010
nasir

Please have a look at the solution here http://ho.race.hk/blog/?p=231

Basically you need to reconnect “ActiveRecord::Base.connection.reconnect!”

as somehow mysql just drops the connection. I am not sure why.

3 01 2013
Kai

This was a very helpful post for me. However, when calling complex stored procedures with several result sets, I get strangely structured arrays/hashes and incomplete results with the method “connection.select_all”. I solved this problem by using “ActiveRecord::Base.execute_procedure”, as suggested on the official github site for the sql server adapter:

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

%d bloggers like this: