MySQL Gets Functions in Java

Java Coward writes "Eric Herman and MySQL's Brian "Krow" Aker have released code to allow the DBMS MySQL to run Java natively inside of the database. The code allows users to write functions inside of the database that can be then used in SELECT/INSERT/UPDATE statements. So when will someone do Ruby?"

Comments

Now how about.

[DAldredge]

Now how about a way to do online backups of the new table types with out having to buy a license to do it?

Where is MySQL anyways?

[Chitlenz]

As an Oracle DBA, at a small company, we're constantly looking for less expensive SOLID alternatives to our traditional Oracle/Solaris approach to the back end.

When I say solid, I mean is able to handle very large files (excess of 50GB per datafile), has stored procedures and trigger infrastructure (a traditional MySQL weak point, and the main reason we've passed on it so far), an integrated backup system a la netbackup/RMAN, and prefereably a back end compiled scripting solution a la PL/SQL.

This looks like a sorta kinda solution to the last (PL/SQL alternative), but I'm curious to know about the rest, and also how it performs. Ideally for us, we'd also like to see better clustering and large system support examples in the real world before we embarked onto this particular voyage with.. say a production ERP system.

Are we talking about a good replacement for Access or for DB2 here?

Enquiring minds want to know ...

-chitlenz

Re:Where is MySQL anyways?

[BigGerman]

Postgresql seems to be the ticket.
I have been doing Oracle work for 12 years and find Postgres easy to learn and quite powerful.
Certainly ref integrity, triggers and PL/SQL like stored procedures are all there.
I currently have 80GB PostgreSQL database as a backend for pretty busy websites and it holds well.
THIS is not a solution, just a proof of concept. I looked at the code and it is not even thread-safe.

Re:Where is MySQL anyways?

[jdgreen7]

We ended up moving all of our scattered Access Databases to MySQL about a year ago, and have never looked back. We still use Access as a front-end to get at the data, and everything has worked beautifully. Using Access gives us a consistent UI for each app, and it's quick and relatively painless to add new apps or features. And, using MySQL for the data is orders of magnitude faster that Access MDB files.

As far as 'enterprise level' features, MySQL is still missing Stored Procedures, easy 2-way replication, and clustering (there are many projects out there that add these features, but none of them are included in the main branch AFAIK). They keep getting closer with each release, though.

No, it's not ready to take on Oracle yet, but for mid-size shops (we regularly have 30-50 concurrent users all day from various remote locations), it's a great product. Slashdot runs it, and they seem to be able to handle quite a bit of a load. It's proven itself to me, but then again, I've never played with Oracle or DB2. It has a very active developer base, so things are changing all the time.

PostgreSQL has more enterprise features, but it's not used as much as MySQL. It seems pretty solid, though. We toyed with a bit, but my boss decided to go with MySQL mainly because he had heard of it before.

Re:give me a break

[ajaf]

I agree, migrating from another database to mysql is a pain in the ass and impossible, due to the lack of a lot of functionalities you said.
Postgresql, in the other side, is close to other databases, and migrating to it maybe is a pain in the ass too, but not impossible.
Last version of Postgresql is really fast, so why should I use Mysql?

Why you would do this . .

[database_plumber]

This idea has been around for a while; at least since the late 1980's. The motivation for these kinds of DBMS features is that there are lots of programming situations where SQL's types and expressions aren't powerful enough, and that the language doesn't have a lot of modularity. User-defined functions are supposed to overcome this limitation.

This kind of feature brings MySQL closer to being an "Object-Relational" database.

http://en2.wikipedia.org/wiki/Object-relational_da tabase

From a theory point of view it goes a long way towards implementing the Relational Model's idea of a 'domain' (not just INTEGER, VARCHAR or whatever, but PART_NUM, PERSON_NAME etc). This is supposed to improve the integrity of the data in the database.

From a practical systems point of view it can have a big performance impact. If you're opening cursors and then looping over some Java code on the client to identify only those result rows that you're interested in, then you're paying a pretty big 'system tax' to transfer the data from the DBMS through the connection and into the external program's address space. Pushing the code (which will have to run anyway) into the DBMS eliminates the transfer overhead.

The point of the original Postgres was to figure out how you incorporate these features into a query processing framework. Most modern DBMS products have the feature; some of them do a better job implementing it than others.

Re:Java in the DB - very, very bad idea

[randolfe]

The knees jerk so fast on /. whenever Java is mentioned, in any context, that I'm surprised someone doesn't have their eye put out.

Of course there exist myriad reasons why one would prefer to standardize on a common language for DB SPs. Java, in this regard, is the most mature alternative at present. Even the notoriously skeptical Thomas Kyte and the pontificating Steven Feuerstein see the validity of Java in the database at the SP level.

Of course, *we* can all keep fighting amongst ourselves about such things while Visual Basic and C(flat) become the only languages we have to chose from for everything we endeavor to do.

Ruby?

[Glock27]

So when will someone do Ruby?

Perhaps when there's a Jython-like JVM based Ruby implementation?

Seriously (given the number of ignorant "why use Java it's so slooooow" posts) as far as I can tell the current Ruby implementations are slow compared to Java. Would you really want to use a slow interpreted language for database functions, rather than one with close-to-C performance?

Also on the subject of knee-jerk Java bashing, I can't understand why so many C++ programmers resist Java, tooth and nail. Yes, Java has a somewhat bulky memory footprint (that may not be such a problem going forward with all the new 64-bit architectures out there). However, you get a ton of niceties as well, and a very sane language compared with C++. Java runs very fast these days, given sufficient JVM heap. Gcj is also getting there in terms of being useful, and provides an OSS traditional ahead-of-time compiler for Java code. Java may not be an ECMA standard, but it is open enough to permit free implementations.

Java isn't perfect...but it is better than many of the alternatives, and deserves more respect than it seems to get here on /. and among programmers in general. At least it is well supported on Linux by it's originators, unlike C# and .Net.

OK, time to do something useful now... :-)