db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Regarding Derby Stored Procedures
Date Tue, 08 Sep 2009 19:09:55 GMT
Hi Shazin,

Some comments inline...

Shazin Sadakath wrote:
> Hi Dag/Rick
> Thanks for the very descriptive information on the advantages of Java 
> Routines. I am a huge fan of derby and I really appreciate the work 
> you guys have put in to bring derby where it is now.
> But I would like to list some cons of Java Routines. Correct me if I 
> am wrong.
> 1. Java Routine is not really stored procedures.
I'm not following you. Java routines are defined in part 13 of the 
ANSI/ISO SQL spec. As such they are full-fledged functions and routines 
according to the standard.
> 2. A Change in Java Routine code implementation requires recompilation
> 3. A Change in Java Routine code implementation requires repackaging (Jar)
> 4. A Change in Java Routine code implementation requires replacing of 
> the old jar with the new one
> using sqlj.replace_jar
Yes except for (4). If you put the user-coded jar alongside the derby 
jar on the application classpath then you don't need to use the sqlj 
procedures to load and replace your user-code.
> 5. Requires extensive knowledge of Java. For server database this is a 
> disadvantage because in the future if you are hoping to support other 
> languages (PHP, C++, .NET) to access derby server, those developers 
> have to know Java to create Java Routines.
Yes, this is a weakness for developers who don't know Java.

Note that Java routines give you good integration with other languages, 
including more dynamic languages, which run on the Java VM.
> 6. Database servers such as Oracle, Sybase also support Java Routines 
> along with their own SQL Stored Procedures (SQL/PSM) whereas derby 
> only supports Java Routines.
That's right. In simple cases, SQL/PSM can be a productive language. But 
for even a modestly complicated routine, Java has significant advantages 
in terms of its expressiveness, safety, pluggability, libraries, and 
> I am in no means against derby database. In fact I am a Java Developer 
> myself and I am currently studying the derby database for academic 
> reasons.
As you can tell, I am a Java enthusiast. I regard Java as a well 
designed, expressive, and productive language. And I regard the Java 
platform as a very secure computing environment.

> Regards,
> Shazin
> On Tue, Sep 8, 2009 at 6:56 PM, Rick Hillegas 
> <Richard.Hillegas@sun.com <mailto:Richard.Hillegas@sun.com>> wrote:
>     Hi Shazin,
>     In addition to what Dag said, I would say this:
>     1) Writing your procedural code in Java (rather than SQL/PSM)
>     makes it possible to factor your code so that pieces (like
>     integrity and validation logic) can be shared across all tiers of
>     your application. As you tune your application, you can move that
>     code up and down the call-stack to the tier where you get the best
>     performance. The same code can run in your client, in the middle
>     tier, and in the database itself.
>     2) Writing your procedural code in Java (rather than SQL/PSM)
>     gives you access to a large ecosystem of development tools,
>     including debuggers. You can debug your application in embedded
>     mode--here the same debugger is responsible for your procedural
>     code and your client code.
>     3) Writing your procedural code in Java gives your database
>     procedures access to all the advantages of the Java language
>     itself, including:
>     a) a large ecosystem of off-the shelf freeware
>     b) compilers which do half of your debugging for you
>     c) powerful exception handling
>     The "Java in the Database" presentation (from Apache Con USA 2006)
>     may be useful: http://db.apache.org/derby/papers/ApacheCon.html
>     Hope this helps,
>     -Rick
>     Shazin Sadakath wrote:
>         Hi,
>         Recently I have been going through the Wiki
>         http://wiki.apache.org/db-derby/DerbySQLroutines
>         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"
>         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?
>         Thanks in advance,
>         Shazin

View raw message