Rails - May 2006 - Equivalence of TSQL Stored Proc and User defined functions

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.


-- 
Posted via http://www.ruby-forum.com/.

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.

-- 
Posted via http://www.ruby-forum.com/.

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 Duxbury 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.

-- 
Posted via http://www.ruby-forum.com/.

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)

-- 
Jakob Skjerning - http://mentalized.net

> 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..

-- 
Posted via http://www.ruby-forum.com/.

> 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.
-- 
Phillip Hutchings
http://www.sitharus.com/

> 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.




-- 
Posted via http://www.ruby-forum.com/.

> 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.


-- 
Posted via http://www.ruby-forum.com/.

On Mon, May 29, 2006 at 02:54:59AM +0200, Bryan Duxbury
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

Bryan Duxbury 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.


-- 
Posted via http://www.ruby-forum.com/.

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 Skjerning 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)

-- 
Posted via http://www.ruby-forum.com/.