db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag.Wan...@Sun.COM (Dag H. Wanvik)
Subject Re: Regarding Derby Stored Procedures
Date Mon, 07 Sep 2009 17:43:31 GMT

Hi Shazin,

Shazin Sadakath <shazin.sadakath@gmail.com> writes:

> And it states I quote
> "The advantage of Java procedures is that the same procedure will run on any
> database that supports the standard, such as Derby, IBM's DB2 and Oracle"

Many consider portability to be one of the great advantages of open
source - and Derby is open source.

> Apart from this single advantage there aren't much to registering Java
> Procedures in derby database, so why doesn't derby database support standard
> SQL Stored Procedures (Such as Oracle PL/SQL..) without registering public
> static void methods as Stored Procedure. Any particular reason to this?

In fact, Oracle's PL/SQL is not standard [1][2]. The standard SQL for stored
procedures is covered by ISO/IEC 9075 volume 4: "Persistent Stored
Modules (SQL/PSM)" [3], and it is not identical to Oracle's PL/SQL.

Derby does not yet support SQL/PSM; I agree that would be a nice
addition though, but this is open source, if somebody wants is badly
enough, it will get added :)

Btw, using Java stored procedures is also covered by the SQL standard,
cf.  ISO/IEC 9075 volume 13: "Java Routines and Types Using the Java
Programming Language (SQL/JRT)" [4].

Since Derby's provenance is as an embedded Java database (although now
also much used as in a traditional client/server database mode), many Derby
users are familiar with Java, and find writing stored procedures and
functions in that language convenient.

Hope this answers your question!

Cheers,
Dag

[1] http://en.wikipedia.org/wiki/PL/SQL
[2] http://en.wikipedia.org/wiki/SQL/PSM
[3] http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=34135
[4] http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=37102

Mime
View raw message