You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Shazin Sadakath <sh...@gmail.com> on 2009/09/07 11:26:15 UTC
Regarding Derby Stored Procedures
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
Re: Regarding Derby Stored Procedures
Posted by Shazin Sadakath <sh...@gmail.com>.
Hi Dag,
Thanks a lot, that was a really informative response.
Regards,
Shazin
On Mon, Sep 7, 2009 at 11:13 PM, Dag H. Wanvik <Da...@sun.com> wrote:
>
> Hi Shazin,
>
> Shazin Sadakath <sh...@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
>
Re: Regarding Derby Stored Procedures
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Hi Shazin,
Shazin Sadakath <sh...@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
Re: Regarding Derby Stored Procedures
Posted by Shazin Sadakath <sh...@gmail.com>.
Hi Rick,
Thanks once again for the reply. It made things really easy for me.
Thanks for your time.
Regards,
Shazin
On Wed, Sep 9, 2009 at 12:39 AM, Rick Hillegas <Ri...@sun.com>wrote:
> 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 tooling.
>
>>
>>
>>
>> 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,
> -Rick
>
>>
>> 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
>>
>>
>>
>>
>>
>
Re: Regarding Derby Stored Procedures
Posted by Rick Hillegas <Ri...@Sun.COM>.
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
tooling.
>
>
>
> 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,
-Rick
>
> Regards,
> Shazin
>
>
>
> On Tue, Sep 8, 2009 at 6:56 PM, Rick Hillegas
> <Richard.Hillegas@sun.com <ma...@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
>
>
>
>
>
Re: Regarding Derby Stored Procedures
Posted by Shazin Sadakath <sh...@gmail.com>.
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.
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
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.
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.
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.
Regards,
Shazin
On Tue, Sep 8, 2009 at 6:56 PM, Rick Hillegas <Ri...@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
>>
>>
>>
>
>
Re: Regarding Derby Stored Procedures
Posted by Rick Hillegas <Ri...@Sun.COM>.
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
>
>