Forums Register Login

to use stored procedure or not

+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
As you say,


I hope to get some expert opinions on this,



I am also waiting in the queue
+Pie Number of slices to send: Send
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*!
+Pie Number of slices to send: Send
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?
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
 


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?
+Pie Number of slices to send: Send
 

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.
+Pie Number of slices to send: Send
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?
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
 


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
+Pie Number of slices to send: Send
thanks all.

I get the point now.
straws are for suckers. tiny ads are for attractive people.
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 1335 times.
Similar Threads
Stroed Procedure&PL/SQL
Java discourages intelligent use of database technology: Discuss.
JDBC Stored Procs and Temp tables
Problem with Stored Procedures
Caching EJB stored procedure calls.
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 19, 2024 03:28:07.