*
The moose likes JDBC and the fly likes to use stored procedure or not Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "to use stored procedure or not" Watch "to use stored procedure or not" New topic
Author

to use stored procedure or not

azhar bharat
Ranch Hand

Joined: Jul 17, 2006
Posts: 87
Hi
I am one of those person who used to and still use the stored procedures. Not because I like it, in fact I have to depend on SQL developers to develop or optimize the stored procedure.
Now, as I read more and more articles on good design, I am beginning to rethink on my practice of using stored procedures(SP). Actually a bit confused too.
Many of these articles say "do not use stored procedures because it pushed the business logic to the database (or model tier in MVC context)".
It sounds logical but doesn't using SP have its advantages?
How do I decide which one is better? When should I use or not use SP?

I hope to get some expert opinions on this. Thanks in advance.
Raghavan Muthu
Ranch Hand

Joined: Apr 20, 2006
Posts: 3344

As you say,


I hope to get some expert opinions on this,


I am also waiting in the queue


Everything has got its own deadline including one's EGO!
[CodeBarn] [Java Concepts-easily] [Corey's articles] [SCJP-SUN] [Servlet Examples] [Java Beginners FAQ] [Sun-Java Tutorials] [Java Coding Guidelines]
Raghavan Muthu
Ranch Hand

Joined: Apr 20, 2006
Posts: 3344

IMHO, Stored Procedures are preferred because of the network time is reduced (round-trip time from JDBC to Database and the reverse) as there will be many number of calls if its not present.

But it depends on the actual requirement on its usage.

Still, i await some good inputs from the *experts*!
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
I avoid SP because they are not portable from one database to another, and because they reflect what I view as an out of date architecture.

In Client-Server days, say PowerBuilder, the SP was the last bastion of protection for the data and the best unit of reuse. Clients called the SP and the SP made sure that things were done correctly. Nowadays the application server owns and protects the data and provides reusable services. Clients call application code and the code calls the database. We never give out database access to other systems.

One opinion only. Any big SP fans out there?


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2495
    
    8

We use them a lot in our Oracle Applications ERP. The public APIs are written in PL/SQL. There's no risk of increasing vendor buy-in. It's Oracle software written for Oracle forms/reports running on Oracle Fusion middleware talking to an Oracle database.

We try not to use them for other applications, where platform independence (software and hardware) matters to us.

Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
Raghavan Muthu
Ranch Hand

Joined: Apr 20, 2006
Posts: 3344


I avoid SP because they are not portable from one database to another, and because they reflect what I view as an out of date architecture.


Thats a good point Stan James. Can you please explain the second part of the statement? What exactly do you mean to say, out-of-date architecture?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41489
    
  53
Originally posted by Raghavan Muthu:
Can you please explain the second part of the statement? What exactly do you mean to say, out-of-date architecture?


It's what Stan talks talks about in the following paragraph: A two-tier architecture with just a client that talks directly to a database (i.e., no app server). In that scenario, there would be no protection from a rogue client if there was none in the database.


Ping & DNS - my free Android networking tools app
Raghavan Muthu
Ranch Hand

Joined: Apr 20, 2006
Posts: 3344

Thanks Ulf. But as per his conclusion on his second paragraph, nowadays the Application Server delegates the call from the client to database - means, by which the intrusion may not happen by that time as we assume previously the client must have been authenticated as well authorized.

What exactly it is related with out-of-date architecture even when the SP's are being used with the presence of App.Server - is what my question.

Am i confusing?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41489
    
  53
The point is that the DB (or the SPs) don't need to perform the kinds of checks they needed to do with a two-tier system, because the app server takes care of that. So that kind of logic should not reside in the DB, in order to have a clean separation of concerns.
Raghavan Muthu
Ranch Hand

Joined: Apr 20, 2006
Posts: 3344


The point is that the DB (or the SPs) don't need to perform the kinds of checks they needed to do with a two-tier system, because the app server takes care of that. So that kind of logic should not reside in the DB, in order to have a clean separation of concerns.


Thank you again Ulf. I did not think of as i was under assumption that the checks would be taken care before even the request/control is passed on to SP. Moreover if there are any violations on schema owner etc (which we have not checked), the db will throw an error. Thats what i have also worked with SPs.

Now its clarified. Thanks
azhar bharat
Ranch Hand

Joined: Jul 17, 2006
Posts: 87
thanks all.

I get the point now.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: to use stored procedure or not