Joachim Zobel wrote:
Am Donnerstag, den 22.03.2007, 14:03 +0100 schrieb Helmut Tessarek:
Writing stored procedures is usually part of the DBA, but if the database server
is not on the same machine as the web server, stored procedures are most of the
time faster than SQL statements invoked from the client.
The main point with stored procedures is IMHO that thanks to embedded
SQL they are _the_ way to write readable transaction code involving
multiple DML statements.
Anyway, st. p. support is good.
Sincerely,
Joachim
re: "stored procedure support"
Stored procedures are, to some extent, usable with dbd now.
If procedures only require input parameters and return a result set
(like the example procedure GROUP_SP in mod_auth_ibmdb2) - they can be
invoked by apr_dbd_select with the SQL statement:
"CALL GROUP_SP(?)"
Some database systems might use EXECUTE or EXEC verbs rather than CALL,
but most allow procedure calls where a SELECT statement can be used.
The example procedure USER_SP could be changed to return a single-row
result set containing the password rather than using an output
parameter. I think it would fit into the current dbd scheme this way,
although I don't use DB2 so I'm not 100% sure.
Explicit stored procedure support might be useful in dbd, but it can
become messy with database-specific details very quickly.
For example: Oracle output parameters (or input-output parameters) can
return arrays; or even return result sets (i.e. refcursors). It will
take some effort to deal with all of this properly in dbd.
re: "stored procedures are most of the time faster than SQL statements"
True for complex SQL, but I find the difference too small to measure
when the procedure just contains a simple SELECT. They still have the
big advantage of encapsulating the SQL and forcing a bunch of disparate
apps to all do things the same way. IMHO they are worth it just for
this benefit alone.
Just my 2cents...
-tom-
