Equivalence of TSQL Stored Proc and User defined functions

Rails
1

RoR Equivalence of TSQL Stored Procedures and User defined functions

As I browsed through the tutorials and online eBook â??Programming Rubyâ?,
I have not come across the terms â??Stored Proceduresâ? or â??User-defined
functions� as I know them in SQL 2000.

However, the online documentations in MySQL 5.0 show that Stored
Procedures and User-defined functions are supported in MySQL 5.0.

I wonder what is the Ruby on Rails equivalence of Stored Procedures or
User-defined functions and what books or tutorials or web-sites can I
find examples on these?

Asked in another way, how does Ruby on Rails handle Stored Procedures or
user-defined functions created by using SQL scripting in an existing
database? Are we able to use migrations to translate the SPROC or UDF?

Assistance or comments are much appreciated.

Ling.

2

I struggle to see the advantage of using stored procedures in Ruby on
Rails. Part of the appeal of RoR is getting to use ActiveRecord: you
don’t have to think about the database as a database because it takes
care of all the work for you. Where would a SPROC fit into this
methodology?

Basically, instead of writing a stored procedure, write a model
function. It keeps all of your app in one place and in one language. I
don’t know if this qualifies as DRY, but it sure is “don’t spread
yourself out.”

If you REALLY need to use them for some reason, just use direct database
access to invoke the procedure in SQL.
ActiveRecord::Base.connection.execute(sql) will get you an array result.

3

Re RoR Equivalence of SPROC and UDF

Dear Bryan,

Thank you for your answer.

I am so new to RoR that I know too little of RoR to appreciate the full
significance of your answer. I havenâ??t even finished reading the book
â??Programming Rubyâ?

The reason why I ask about the SPROC & UDF is a bit like a Chinese
beginner learning to speak English who first thinks in Chinese, mentally
translates it into English, and eventually speaks in English. This is
just a transition period. When he is more fluent, he will think and
speak in English.

My early exposure to web application was Asp.Net 2002 from a book
written for VB.Net and SQL 2000, where the author talked about
Data-Driven web-application.

So I built some web-application by first creating a database and then a
bunch of Stored Proc & UDF and used ADO.Net to run the SPROC.

One of the modules was to automatically arrange room-mates for
over-night event participants subject to some pre-defined criteria, such
as single participants can only room with the same sex; participants are
grouped accordingly to their sponsorsâ?? groups, meal preferences, etc.

To accomplish the room-mate pairings, I wrote a few stored procedures
with the first one calling on the subsequent SPROC & UDF. This is
probably not a good way but that was what I knew how.

In ADO.Net I can choose to invoke the Stored Procedure by using the name
of the first SPROC or I can also choose to write the whole sets of SPROC
into one long SQL statement in VB.Net and use ADO.Net to run it.

From your example of â??ActiveRecord::Base.connection.execute(sql)â?, I
guess I can use Ruby to write the set of SPROC into one sql statement
and plug it into a Model function somewhere.

Am I right to guess that there is no way to run the SPROC in RoR by
using the name of the SPROC? If there is a way, then what is the syntax?

Regards,

Ling

Bryan D. wrote:

I struggle to see the advantage of using stored procedures in Ruby on
Rails. Part of the appeal of RoR is getting to use ActiveRecord: you
don’t have to think about the database as a database because it takes
care of all the work for you. Where would a SPROC fit into this
methodology?

Basically, instead of writing a stored procedure, write a model
function. It keeps all of your app in one place and in one language. I
don’t know if this qualifies as DRY, but it sure is “don’t spread
yourself out.”

If you REALLY need to use them for some reason, just use direct database
access to invoke the procedure in SQL.
ActiveRecord::Base.connection.execute(sql) will get you an array result.

4

Ling wrote:

Am I right to guess that there is no way to run the SPROC in RoR by
using the name of the SPROC? If there is a way, then what is the syntax?

ActiveRecord::Base.connection.execute('EXEC NameOfTheStoredProcedure)

5

My early exposure to web application was Asp.Net 2002 from a book
written for VB.Net and SQL 2000, where the author talked about
Data-Driven web-application.

Historically stored procs were used as essentially a programming
language of a database [TSQL/PLSQL] - the emergence of N tier’d
architectures in the late 90s begin to make these obsolete [except as
‘Data Access Layers’ as they were called - which were essentially a way
to decouple a middle tier from the specifics of database tables, etc]…
Unfortunatly the architecture is easily abused [and was] and business
logic ran between the middle tier and the DAL [and sometimes spilled
into the view]… One of RORs strongest points is that the architecture
is fairly strict and straightforward, i.e. it is clear where to put
things and not particularily easy to hack around it. As far as stored
procs go, they’re obsolete. Tools like RoR handle the gritty details of
accessing a database for us and let us move on to focus on solving new
problems…

6

I wonder what is the Ruby on Rails equivalence of Stored Procedures or
User-defined functions and what books or tutorials or web-sites can I
find examples on these?

Ruby on Rails is written with the assumption that the database will be
used by the Rails application alone, so there is no direct support for
user defined functions or stored procedures. Most people write the
entire application in Ruby and use the database as a store only.

You can still execute your own SQL queries if you wish to use those
database features. I suggest analysing your situation and seeing if
Rails is really the best choice for your task.

7

brez! !! wrote:
. One of RORs strongest points is that the architecture
is fairly strict and straightforward, i.e. it is clear where to put
things and not particularily easy to hack around it. As far as stored
procs go, they’re obsolete. Tools like RoR handle the gritty details of
accessing a database for us and let us move on to focus on solving new
problems…

Unfortunately, most of us can’t start “brand new” and incorporate all of
the latest buzzword and techno-babble all at once.

We have existing applications and are trying to fit Ruby into that
CURRENT environment.

And to say that stored procs (SP) are obsolete can’t get any more wrong!

They are still plenty (if not most) database applications still store
the business logic within SP. And with the SP power of Postgresql
(multiple languages) and SQL Server 2005 (.NET compatible), you have to
be crazy not to harness that power within your application.

Don’t believe everything you read, sometimes experience is the best
teacher.

8

On Mon, May 29, 2006 at 02:54:59AM +0200, Bryan D. wrote:

Can you provide an example of a situation where a stored procedure would

Define “best”?
Maximum performance? yes, usually
Controlled by DBA, not developer? yes, in large databases this is good
Easy to change? no, shouldn’t change production easily

Agile to develop? No.

-jim

9

They are still plenty (if not most) database applications still store
the business logic within SP. And with the SP power of Postgresql
(multiple languages) and SQL Server 2005 (.NET compatible), you have to
be crazy not to harness that power within your application.

Can you provide an example of a situation where a stored procedure would
yield the BEST approach to a problem? I’ve never used them, largely
because it doesn’t make sense to me to incorporate yet another
programming language into my application. It appears to me that it is
just another possible point of failure and confusion.

10

Bryan D. wrote:

Can you provide an example of a situation where a stored procedure would
yield the BEST approach to a problem? I’ve never used them, largely
because it doesn’t make sense to me to incorporate yet another
programming language into my application. It appears to me that it is
just another possible point of failure and confusion.

I don’t think a said it was the BEST solution, just that it’s a solution
that should not be overlooked.

SQL is used to create your database layout, so if you are considering
SQL as another programming language, then that’s fine…it’s a language
that is already incorparated in your application.

But if software management is not your strong point, then stored proc
may not be the correct choice for you.

What stored proc(SP) provide for me is the ability to use any
programming language and still provide the same end result to the
customer.

We currently have a full-blown Inventory Management/Point-of-Sale
System(for Brick and mortar) written in C++/Deplhi/CTD and C# with an
integreted Web Store solution.

We are now porting this (Brick and mortar part) over to a web based
solution (hopefully using Ruby).

This task would be have been greatly extended and more problematic if it
weren’t for the fact that the business logic is held within SP/Triggers.
We are mainly just changing the UI.

Our solution works for a single local user, or many local/remote users.

11

Thank you for this syntax.

Am I correct to interpret that the “NameOfTheStoreProcedure” is the name
of the stored procedure in the database such as MySQL, or SQL 2000?

As I don’t have experience in MySQL, I have yet to learn to use the
SProc.

I believe RoR also supports SQL 2000. If so, I can still write SPROC in
SQL 2000, and use this syntax to run them in RoR? At this point of time,
I am more familiar with SQL 2000 than RoR.

I went to bed after posing my quetions last night, and happily see so
many responses.

Thank you all for your comments and suggestions.

Regards,

Ling.

Jakob S. wrote:

Ling wrote:

Am I right to guess that there is no way to run the SPROC in RoR by
using the name of the SPROC? If there is a way, then what is the syntax?

ActiveRecord::Base.connection.execute('EXEC NameOfTheStoredProcedure)