Now we know how to call our good and reliable stored procedures but there is a difference when we get records from database using Rails ORM/ActiveRecord compared to a stored procedure. This requires a different approach to access records returned from a stored procedure.
Lets say we have a table orders and a corresponding model Order. We all know, how to access data using ActiveRecord, eg.
@orders = Order.find(:all, :conditions => ["some simple condition"])
then what is returned to us is an array of order objects which we can access by either using a block or a for loop. For instance,
for order in @orders puts order.order_name end
but this is not possible in case if we use a stored procedure to get data on orders because we don’t get back an array of order objects instead we simply get back an array of hashes, ie. an array of records with their attributes and values. Hence, the above code will not work.
Lets say you call your stored procedure, like this
@orders = DbStoredProcedure.fetch_records("exec procedure_name 'args1', 'args2'")
as mentioned in the previous post.
Then you need to access records with a little modification within your for loop
for order in @orders puts order['order_name'] end
I thought this might save sometime who are new to using stored procedures.
NOTE: The only downside of using stored procedures in Rails currently is; you cannot use the Object Relational Mapping which you might have been used to by know, ie. picking all children of an order by simply typing
The best thing to do is to use stored procedures judiciously and mainly for complex logic/calculation in your database but when you want to do something simple with your data like simply showing something or a quick update then use ORM over stored procedures.