Hi all,

are there any drawbacks or is it bad design to create stored procedures
for all required data access-scenarios to hide the table structure,
queries etc. (and changes that may be made to them) from the application?

Thanks,
js

"Jo Siffert" <jo.siffert (AT) gmx (DOT) net> schrieb im Newsbeitrag
news:c1kkh2$43i$1 (AT) zeppelin (DOT) rz.uni-potsdam.de...

These are the drawbacks I can think of:

- With MS SQL Server you can't commit transactions inside the SP - at
least not when working via JDBC. MS SQL Server mimics nested transactions
by counting the begin-commit pairs and really committing only on the top
level.

- Some databases don't support stored procedures or the support is not
complete.

You can use views for most queries and on some databases also for
insertions.

robert

On Thu, 26 Feb 2004 12:16:02 +0100, Jo Siffert <jo.siffert (AT) gmx (DOT) net>
wrote:

Many.

Very very bad design.

Tables are the only thing applications should see.


Regards
Alfredo

"Jo Siffert" <jo.siffert (AT) gmx (DOT) net> wrote

example, if you need to tweak the syntax etc. you don't have to redeploy the
application. Just change the stored procedure.
Jim

"Jo Siffert" <jo.siffert (AT) gmx (DOT) net> wrote

Jo,

Data is much more portable as tables (ie. relations). Constraints are much
more portable as well formed formulae (ie. relational expressions) and
foreign key references etc.

No two vendors use the same stored procedure language. In some cases where a
vendor sells multiple dbmses, the vendor's dbmses use different stored
procedure languages.

However, if you want to centralize part of your application for physical
performance reasons, stored procedures are a handy facility for doing that.
Just keep clear in your mind that the stored procedures are actually part of
the application.

Ideally, we would prefer to use a higher level application programming
language that deals directly with relations and a distributed dbms where the
optimizer could assist in the decision of where to execute what.

Jo Siffert <jo.siffert (AT) gmx (DOT) net> wrote:
It can arguably be anti-relational, depending on how the stored
procedures are designed.

To be sure, it diminishes the flexibility of "use cases" for the
system in that (assuming system users are required to use the
procedures) it forces certain access paths.

The flip side is that this allows there to be two sets of developers:

1. Database developers, who are responsible for providing useful
sets of stored procedures, and

2. Application developers, who use what the DB guys provide.

And the separation can allow the DB guys to change the structure of
handling of things in the DBMS without the applications guys having to
worry about it.

Furthermore, it is common for "application developers" to not really
understand relational databases very well. They are likely happier
with a decent API; that means that it is the "DB guys," who are deeply
engaged in DB work, who have to understand the "relationalness" of the
system. That may well be a good way to separate that out...
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://www.ntlug.org/~cbbrowne/rdbms.html
Developmental Psychology
"Schoolyard behavior resembles adult primate behavior because "Ontogeny
Recapitulates Phylogeny" doesn't stop at birth."
-- Mark Miller