Hi Brian,
I can give a general opinion on the use of database stored procedures,
unfortunately from traditional client-server experience, rather than
JSP, although I think some similar issues apply.
We investigated the use of stored procedures for any of the core
database transactions that we could easily package into the procedure
4GL language provided by the DBMS, in this case OpenIngres.
This would provided the performance advantage of sending only a
procedure trigger and parameters to the DBMS, rather than a sequence of
SQL statements. This makes more sense the lower the bandwidth of your
connection between the client and DBMS server. Also, the stored
procedures were precompiled and the SQLs in them used stored query plans
(like JDBC prepared statements) for further performance improvements.
But, we decided against them. The problem was with maintainability and
cohesiveness of the system. Our product is installed all over the
country and we regularly (too regularly if you ask our customers :-)
distribute patches and updates.
It's a lot easier to change SQL statements in the source code and
distribute a new version of the program, than to write scripts to unload
and reload stored procedures in the database. Also, if you want to
understand how a particular process works, you just look at the source,
rather than chasing all over the system extracting stored procedures and
reformatting them so you can read them.
Also, we found that individual clients had a habit of playing (well,
trying to administer...) their DBMS server and regularly loosing the
stored procedures. We could distribute the code, but we couldn't easily
ensure the Database had everything in it it needed. One idea was to
have the client code actually (re)create the stored procedures at
runtime if required, either because they were missing or out of date.
This would make all the functionaly visible in the source too.
Maybe the OpenIngres tools for stored procedures aren't up to scratch
and with some other DBMS all this would not be a big issue ? Which
brings the point of portability amoung DBMS vendors. In my limited
experience, SQL may be somewhat standardised (in 26 exciting flavours),
but stored procedures are not.
Just my AUS$0.02 (which is about US$0.012)....
Drew
> -----Original Message-----
> From: Brian Burridge [SMTP:[EMAIL PROTECTED]]
> Sent: Saturday, 8 May 1999 0:47
> To: [EMAIL PROTECTED]
> Subject: JSP & SQL Stores Procedures
>
> I just wanted to get some of your opinions on this. We are having an
> internal discussion about whether or not to use stored procedures for
> our SQL, or to put the SQL in the bean or JSP page.
>
> Does anyone have any recommendations either way? Suggestions?
>
> Brian N. Burridge
> Web Analyst
> Cox Target Media
>
> ======================================================================
> =====
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the
> body
> of the message "signoff JSP-INTEREST". For general help, send email
> to
> [EMAIL PROTECTED] and include in the body of the message "help".
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff JSP-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
