MySQL Gets Functions in Java - Enlightenment Please

It means that you can create stored procedures/functions which reside in
the database that are written in java (as opposed to the native SQL
programming language).

Why would you want this? If you were more familiar with java than any of
the currently supported procedural languages, then it means that you don't
necessarily have to get to grips with another programming language.

This would be a "nice to have" for Postgres too - I believe that it
supports PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python at present.

Oracle also offers the ability to write stored procedures in java.

It's funny, even though I program in java all the time, I still write my
procedures in the SQL programming language (PL/pgSQL). Even if the java
language were available for Postgres, I'm not sure I would use it...

John Sidney-Woollett

Tony (Unihost) said:

Does this include ALL the functionality of Java? Objects, Polymorphism,
Inheritance, or is it just a subset?

T.

John Sidney-Woollett wrote:

Sorry but I don't know what would be supported. Perhaps someone else can
provide more info.

Here is a document from Oracle detailing their java stored procedure support:

http://otn.oracle.com/tech/java/jroadmap/jsproc/listing.htm#998299

Hope that helps.

John

Tony (Unihost) said:

On Saturday 20 December 2003 16:44, John Sidney-Woollett wrote:

It means that you can create stored procedures/functions which reside in
the database that are written in java (as opposed to the native SQL
programming language).

Does it mean mysql got stored procedures? Wow..

I would like to see details but since I don't know much details about either
mysql or java, I can not describe it.

Can anybody give a summary as in how much it takes mysql as far as stored
procedures go? That would be great..

Shridhar

On Saturday 20 December 2003 16:44, John Sidney-Woollett wrote:

This would be a "nice to have" for Postgres too - I believe that it
supports PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python at present.

http://techdocs.postgresql.org/guides/PLLanguages

It is already supported..:-)

Shridhar

On Sun, 21 Dec 2003 12:05 am, Shridhar Daithankar wrote:

Does it mean mysql got stored procedures? Wow..

I would like to see details but since I don't know much details about
either mysql or java, I can not describe it.

Can anybody give a summary as in how much it takes mysql as far as stored
procedures go? That would be great..

Shridhar

Apparently it's literally a 0.1 release, much of it doesn't work and even tho
a mysql guy is involved its actually a seperate project on sourceforge. You
can use it to effect sub selects and all that jazz but it's not like they've
created some sort of framework for stored procedures.

Essentially they've taken the fact that yes you can extend mysql by writing C
or C++ level functions and creating a custom built server so why not make a
litte generic interface that to do the same thing with java where you dont
need to go thro the rebuild process each time.

Rgds,

Jason

On Sat, 20 Dec 2003, Tony (Unihost) wrote:

Can someone a little more clever than I (which means just about anyone
on this list) tell me what the implications/benefits are of this are
please?

http://developers.slashdot.org/developers/03/12/19/1628241.shtml?tid=108&tid=126&tid=137&tid=156&tid=198

This is not a real stored procedure language and it doesn't claim to be
despite the messages on this list or slashdot. What it does is allow
regular functions to be written in Java. Not to say this isn't valuable,
but the key to an in database language is the ability to query the
database. Now, it should be possible to open a JDBC connection back to
the database like any other java process, but the problem is that it will
not run in the same transaction as the connection which is calling the
function.

Another thing I noticed about this is the prototype for a function is
String exec(String []). So it's type checking certainly won't be
great. It says (and means) version 0.1 and doesn't (yet) deserve all the
noise it has caused.

Kris Jurka

On 20/12/2003 13:05 Shridhar Daithankar wrote:

On Saturday 20 December 2003 16:44, John Sidney-Woollett wrote:

It means that you can create stored procedures/functions which reside

in

the database that are written in java (as opposed to the native SQL
programming language).

Does it mean mysql got stored procedures? Wow..

I read it to mean user-defined functions only. Maybe I missed something?

-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller 
Business |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

I'm actually starting to write one of these for postgres, and apparently
there is another one in the works, from Thomas Hallgren.

There is at least one significant architectural issue here to deal with

Is it more desirable to have a single java vm and communicate via RPC,
or some other mechanism? ie sockets, or ? The alternative is to
instantiate a java vm for every connection, this could be onerous as
there would be considerable overhead for each java vm.

This will all go away as soon as java version 1.5 comes out but in the
meantime, what does everyone think

Comments are welcome.

Dave

John Sidney-Woollett wrote:

It means that you can create stored procedures/functions which reside in
the database that are written in java (as opposed to the native SQL
programming language).

Why would you want this? If you were more familiar with java than any of
the currently supported procedural languages, then it means that you don't
necessarily have to get to grips with another programming language.

This would be a "nice to have" for Postgres too - I believe that it
supports PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python at present.

PostgreSQL supports more than just that including:

pl/php
pl/perl
pl/ruby
pl/python
pl/tcl
pl/c
pl/pgsql
pl/r
plr/bash (I think... or was that just pgBash?)

There was an beta (alpha?) of pl/Java at one time...

Personally I would stick with pl/python, pl/php, or pl/perl

-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org 

On 20/12/2003 18:04 Dave Cramer wrote:

I'm actually starting to write one of these for postgres, and apparently
there is another one in the works, from Thomas Hallgren.

There is at least one significant architectural issue here to deal with

Is it more desirable to have a single java vm and communicate via RPC,
or some other mechanism? ie sockets, or ? The alternative is to
instantiate a java vm for every connection, this could be onerous as
there would be considerable overhead for each java vm.

I think anything other than a single jvm would be too much of a
performance hit.

This will all go away as soon as java version 1.5 comes out but in the
meantime, what does everyone think

Comments are welcome.

On platforms built with GCC3.2+, GCJ might be an alternative.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

On Sun, 2003-12-21 at 08:37, Paul Thomas wrote:

On 20/12/2003 18:04 Dave Cramer wrote:

I'm actually starting to write one of these for postgres, and apparently
there is another one in the works, from Thomas Hallgren.

There is at least one significant architectural issue here to deal with

Is it more desirable to have a single java vm and communicate via RPC,
or some other mechanism? ie sockets, or ? The alternative is to
instantiate a java vm for every connection, this could be onerous as
there would be considerable overhead for each java vm.

I think anything other than a single jvm would be too much of a
performance hit.

Ok, I tend to agree, so does anyone have any preferences for a protocol?
RMI, RPC, XML-RPC ? Anything better?

This will all go away as soon as java version 1.5 comes out but in the
meantime, what does everyone think

Comments are welcome.

On platforms built with GCC3.2+, GCJ might be an alternative.

There are numerous issues with classpath stuff with GCJ, but I'm
certainly willing to look at it.

Dave

Are all of these languages purely preference, or are some better for
writing DB functions than others. Personally, although I write in PHP a
great deal, I'd just get confused writing stored procs in PHP. So I'll
be writing my stored procs in plpgsql, just for the sake of my sanity
more than any other reason.

T.

Dave Cramer wrote:

No not purely preference, the issue is more of being able to call an
enterprise function on a trigger for instance.

Start a thread if something happens in the db.

Dave

On 22/12/2003 13:17 Dave Cramer wrote:

[snip]

I think anything other than a single jvm would be too much of a
performance hit.

Ok, I tend to agree, so does anyone have any preferences for a protocol?
RMI, RPC, XML-RPC ? Anything better?

My instinct would be to go with something fast and lightweight.

On platforms built with GCC3.2+, GCJ might be an alternative.

There are numerous issues with classpath stuff with GCJ, but I'm
certainly willing to look at it.

Yes, it has its problems I agree. And it would only be available on a
small subset of all the platforms on which PostgreSQL runs. But it would
eliminate the need for a jvm which is why I suggested it. Swings and
roundabouts I suppose.

-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller 
Business |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

On Mon, 2003-12-22 at 09:55, Paul Thomas wrote:

On 22/12/2003 13:17 Dave Cramer wrote:

[snip]

I think anything other than a single jvm would be too much of a
performance hit.

Ok, I tend to agree, so does anyone have any preferences for a protocol?
RMI, RPC, XML-RPC ? Anything better?

My instinct would be to go with something fast and lightweight.

No question, this doesn't have to be a fully featured thing, so I'm
soliciting suggestions?

On Sat, 2003-12-20 at 13:04, Dave Cramer wrote:

Is it more desirable to have a single java vm and communicate via RPC,
or some other mechanism? ie sockets, or ? The alternative is to
instantiate a java vm for every connection, this could be onerous as
there would be considerable overhead for each java vm.

If you fire up the jvm at connection creation time rather than at first
pljava function call, wouldn't that make things much simpler and faster
per process? I only ask because I'm thinking most people using pljava
should have some kind of connection pooling going on where initial
connection creation overhead is significantly minimized...

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat <xzilla@users.sourceforge.net> writes:

On Sat, 2003-12-20 at 13:04, Dave Cramer wrote:

Is it more desirable to have a single java vm and communicate via RPC,
or some other mechanism?

If you fire up the jvm at connection creation time rather than at first
pljava function call, wouldn't that make things much simpler and faster
per process?

You could possibly support both approaches by using the "library preload"
mechanism that Joe Conway developed recently. That is, the DBA can
either:

* Preload pljava, whereupon a JVM is created by the postmaster, and the
connection to this JVM is passed down to each backend and shared by
them.

* Do nothing, whereupon each backend that actually makes any pljava
function calls would have to start a JVM upon its first call.

I could see either of these approaches being the more efficient,
depending on how much pljava work is done in a given installation.
So being able to support both seems attractive.

regards, tom lane

Dave Cramer wrote:

This will all go away as soon as java version 1.5 comes out but in the
meantime, what does everyone think

Startup time for the jvm is much faster in 1.4.2 than in previous releases.