CODEWITHSUNDEEP
mysqlruby-on-railsruby-on-rails-4rails-activerecordmysql-5.5
Nir
Nir

Reputation: 2629

Calling MySQL stored procedure in ROR 4

There are few example out there but non of them are very clarified (or on old version).

I want to call MySQL procedure and check the return status (in rails 4.2). The most common method I saw is to call result = ActiveRecord::Base.connection.execute("call example_proc()"), but in some places people wrote there is prepared method result = ActiveRecord::Base.connection.execute_procedure("Stored Procedure Name", arg1, arg2) (however it didn't compiled).

So what is the correct way to call and get the status for MySQL procedure?

Edit:

And how to send parameters safly, where the first parameter is integer, second string and third boolean?

Upvotes: 2

Views: 2668

Answers (4)

Puttaraj Koliwad
Puttaraj Koliwad

Reputation: 1

I believe you have tried many other solutions and got some or other errors mostly "out of sync" or "closed connection" errors. These errors occur every SECOND time you try to execute the queries. We need to workaround like the connection is new every time to overcome this. Here is my solution that didn't throw any errors.

#checkout a connection for Model
conn = ModelName.connection_pool.checkout

#use the new connection to execute the query
@records = conn.execute("call proc_name('params')")

#checkout the connection
ModelName.connection_pool.checkin(conn)

The other approaches failed for me, possibly because ActiveRecord connections are automatically handled to checkout and checking for each thread. When our method tries to checkout a connection just to execute the SP, it might conflict since there will be an active connection just when the method started.

So the idea is to manually #checkout a connection for the model instead of for thread/function from the pool and #checkin once the work is done. This worked great for me.

Upvotes: 0

user4776684
user4776684

Reputation:

In general, you should be able to call stored procedures in a regular where or select method for a given model:

YourModel.where("YOUR_PROC(?, ?)", var1, var2)

As for your comment "Bottom line I want the most correct approach with procedure validation afterwards (for warnings and errors)", I guess it always depends on what you actually want to implement and how readable you want your code to be.

For example, if you want to return rows of YourModel attributes, then it probably would be better if you use the above statement with where method. On the other hand, if you write some sql adapter then you might want to go down to the ActiveRecord::Base.connection.execute level.

BTW, there is something about stored proc performance that should be mentioned here. In several databases, database does stored proc optimization on the first run of the stored proc. However, the parameters that you pass to that first run might not be those that will be running on it more frequently later on. As a result, your stored-proc might be auto-optimized in a "none-optimal" way for your case. It may or may not happen this way, but it is something that you should consider while using stored procs with dynamic params.

Upvotes: 0

Rajarshi Das
Rajarshi Das

Reputation: 12320

You can try Vishnu approach below

or

You can also try

ActiveRecord::Base.connections.exec_query("call example_proc('#{arg1}','#{arg2}')")

here is the document

Upvotes: 1

Vishnu Atrai
Vishnu Atrai

Reputation: 2368

Rails 4 ActiveRecord::Base doesn't support execute_procedure method, though result = ActiveRecord::Base.connection still works. ie 

result = ActiveRecord::Base.connection.execute("call example_proc('#{arg1}','#{arg2}')")

Upvotes: 2

Related Questions