Hi John,
I'll give it a go, even though I'm still learning about derby.
Others may correct me if I'm wrong, or add more detail :)
1. Is there any advantage (e.g. performance, security) to using
procedures over standard SQL?
Off the top of my head there are a couple of notable advantages of using
stored procedures:
- separate the (biz)logic from the client(s).
This gives greater flexibility wrt clients, resulting in less coding,
and I dare say simpler bugfixing, and (a lot) less administration.
There is one centralized location where a potential update/bugfix must
be applied. No need to run around installing updated clients, so it
eases administration. And it can be done "online", so it's
instantaneous. Combined this probably makes it one of the more important
advantages of stored procedures.
- the database engine *may* be able to precompile the stored procedure,
and reuse that precompiled block at a later invocation of the stored
procedure for a (very slight) performance gain. Impact depends on the
nastyness of the expression and the engine itself.
This is no different from what you would get from a PreparedStatement in
the same engine though.
So, yes, in some cases there are advantages. In others, no there isn't.
It depends on what you want to achieve.
2. Is it possible to disable direct access to tables and provide
the only access via procedures as it is in other RDBMS?
Except using GRANT/REVOKE? I don't think so.
But you might be able to do it anyway - grant access to the table(s)
only to a "sqlproc" (or whatever) user, and then execute the stored
procedure as that user?
Cheers
Thomas
--
Thomas Nielsen
