Become a fan of Slashdot on Facebook


Forgot your password?
Java Data Storage Programming IT Technology

MySQL Gets Functions in Java 318

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?"
This discussion has been archived. No new comments can be posted.

MySQL Gets Functions in Java

Comments Filter:
  • Now how about. (Score:5, Interesting)

    by DAldredge ( 2353 ) <SlashdotEmail@GMail.Com> on Friday December 19, 2003 @03:26PM (#7767373) Journal
    Now how about a way to do online backups of the new table types with out having to buy a license to do it?

    • Re:Now how about. (Score:5, Informative)

      by jamie ( 78724 ) * <> on Friday December 19, 2003 @03:35PM (#7767520) Journal
      Replicate to a slave DB that isn't used for anything but backups. On the slave, you can do a 'mysqldump -x'. That'll block updating while it does the write, but you won't care. The only problem arises if your hardware is too slow to catch up replication before the next time you do the dump, in which case you're kind of screwed anyway. This works on both myisam and innodb tables.
      • Re:Now how about. (Score:2, Insightful)

        by DAldredge ( 2353 )
        If you have have a second system to run as a slave that may work. In quite a few realworld(tm) situations you don't have that luxury.

        There needs to be someway of doing online back ups of MySQL with out spending money.
        • Re:Now how about. (Score:3, Informative)

          by Cajal ( 154122 )

          There needs to be someway of doing online back ups of MySQL with out spending money.

          Why not just use PostgreSQL? It's had hot-backup of tables for years.

    • Why don't you just support it?
    • Re:Now how about. (Score:2, Informative)

      You actually can take online backups of InnoDB without buying the InnoDB Hot Backup tool. The non-free tool allows you to take online *binary* backups, but if you want to take an online data backup you can do so using mysqldump with the --single-transaction method. This will work basically identical to how you take the online backup with pg_dump. It takes a snapshot in time using the normal transaction isolation level semantics and dumps it into a plain text file. Keep in mind that dumping the data is s
    • by drix ( 4602 )
      Yeah, fuck paying for a feature that took a lot of work to implement and that you want badly. The nerve of those assholes, charging money for their work.

      A real man would other get off his ass and code it himself, or support the damn developers who do. Note "Bitch on /." is not listed. Free software is turning the world into a bunch of whining ingrates.
  • Hmmmm (Score:5, Insightful)

    by cluge ( 114877 ) on Friday December 19, 2003 @03:29PM (#7767428) Homepage
    I wonder how this affects performance especially compared to regular user defined functions? (Available in later releases of MySQL). This is indeed an interesting twist. It certainly can help speed up development of large projects (java works well in a large/many programmer env.) Like a lot of other tools, it remains to be seen how people put this to use. Too often people learn one thing, and like the saying goes, when all you have is a hammer, every problem looks like a nail. Lets see where this goes shall we?

    • There are times where it is possible to compute stuff in, say, WHERE clauses using standard SQL, but it's an enormous pain and being able to write a little function in a "real" programming language makes things vastly easier.

      MS Access programmers have been doing this for a very long time, with little VBA (or Access Basic if you're old enough) functions saving vast screeds of incomprehensible and possibly rather slow SQL arithmetic and/or substring manipulation.

      With Java (or C or anything else) functions r
    • Oracle has had this functionality since 8.x. Java is *very* fast inside of Oracle, and is more efficient than PL/SQL in a few cases. Again, OSS is playing catch up.
      • Re:Old hat (Score:4, Insightful)

        by ibbey ( 27873 ) * on Friday December 19, 2003 @11:11PM (#7771246) Homepage
        Oracle has had this functionality since 8.x. Java is *very* fast inside of Oracle, and is more efficient than PL/SQL in a few cases. Again, OSS is playing catch up.

        SO? Oracle has lots of features that MySQL lacks. The point of the post isn't that this is some wonderful new feature never before seen in the world, only that it's new to MySQL.

        Oh, and one of the fabulous features that Oracle has over MySQL is the price. I mean, who wouldn't prefer to spend many thousands of dollars? This is obviously another area where OSS needs to start playing catch-up.
  • So when will someone do Ruby?

    MySQL Ruby Interface []
    Google Cache here [].

    Google's your friend []

  • by Anonymous Coward on Friday December 19, 2003 @03:31PM (#7767449)
    So when will someone do Ruby?
    Warning, engaging humor mode *puts on asbestos suit just in case*

    After someone does perl and python ?

    (notice that both of those languages, as well as tcl, are already included in the other free database project: postgres)

    • Ruby, perl, python, tcl, and I believe java language (there's a pljava project, but I'm not sure what the status is on that one) plugins are all available for postgres. Many of them have been there for a while.

  • by tcopeland ( 32225 ) * <> on Friday December 19, 2003 @03:31PM (#7767451) Homepage start up the Java VM. From
    // Create the Java VM
    jint res = JNI_CreateJavaVM (&jvm, (void **) &env, &vm_args);
    Embedding a Ruby interpreter would reduce startup time, probably.
  • Sounds like... (Score:3, Insightful)

    by AndroidCat ( 229562 ) on Friday December 19, 2003 @03:34PM (#7767499) Homepage
    More code to bog down the servers with. I don't know if I see the need for Java inside the DB server. (Sure, server Java between the DB and the client app, but that doesn't require Java inside the DB server itself.)

    I hope this isn't a "Hey wouldn't it be really neat!" feature. The last time that happened, someone at MS thought executable email would really neat.

    • "More code to bog down the servers with"

      Don't think of it that way. Think of it as "now we can code stored procedures in Java instead of weird, proprietary scripting languages" or "Now we can use MySQL for our application that requires stored procedures"!
    • Re:Sounds like... (Score:5, Insightful)

      by the uNF cola ( 657200 ) on Friday December 19, 2003 @04:02PM (#7767870)
      We all don't like learning the database language of a database. It's annoying that oracle, sybase, postgresql and mysql support different sets of ansi92 (or 98) sql, but they all have different gotchas..

      limiting the # of rows of output is different between oracle, sybase and postresql/mysql.

      None of them even have remotely the same stored proc language. Of course, everyone may embed a different language, but java seems to be a more common one.

      Now when you go from sybase to oracle, you don't have to worry so much about the stored procedure code, since it'd all be in java anyway.. riight?
  • That's great (Score:5, Insightful)

    by j0hndoe ( 677869 ) on Friday December 19, 2003 @03:34PM (#7767507) Journal
    I used Java stored procedures a lot back when I was working at a .com. For someone who's already using Java its a lot easier than learning each database vendors proprietary language. It's also good for keeping MySQL feature competitive with open source dbs, since Java stored prcedures have already been implemented for PostgreSQL []
    • Re:That's great (Score:3, Insightful)

      by Brento ( 26177 ) *
      I used Java stored procedures a lot back when I was...

      Just FYI - MySQL still doesn't support stored procedures, let alone Java inside stored procedures. Seems like MySQL is trying to run before it's gotten the walking thing down yet.
    • Java stored procedures are an idea that sounds neat on the surface, but in reality is completely brain-dead.

      In an N-tier systems, the client and application tiers can scale horizontally -- if you need more horsepower, you just plant another server in the farm. Databases, on the other hand, only scale vertically (Even the heavyweight commercial enterprise-grade databases have difficulty scaling horizontally)

      If your DB server can't handle the load, pretty much your only choice is to buy a bigger one. If

  • Ok, so you are storing java functions/objects in the database? Or is it more like you can program your own functions like insert/modify/etc in java, and call them with querys too? I'm confused, someone help!! ;)
  • by Anonymous Coward on Friday December 19, 2003 @03:39PM (#7767577)
    Yay, this project has finally hit Alpha 3 version 0.1, which means that it's the closest OpenSource project yet to a 1.0 release.

    Yes, I'm being sarcastic, I just think it's hilarious that someone would post this implying that the code is anywhere near done. It's barely beyond a twinkle in some kids eye.
  • Eric Herman and MySQL's Brian "Krow" Aker have released code to allow the DBMS MySQL to run Java nativily inside of the database.

    "Nativily"? Boy, the Christmas spirit is just busting out everywhere today!

  • by BigGerman ( 541312 ) on Friday December 19, 2003 @03:42PM (#7767620)
    The ability to write stored procedures in Java has been in Oracle for some time but I still cannot figure out why anyone would do that.
    Java is a nice programming language. Go write web apps, middleware, network software, desktop apps with it but not stored procedures.
    Is mySQL process going to start the whole new JVM on every hit? Or VM is going to run separately and it is bridged somehow (God, not over the network)?
    Now if you ask me, even stored procedures in general become more and more evil.
    And in our age of $50 2Ghz CPUs and Gigabit ehternet the performance is no longer an issue.
    To me, a database is a collection of tables and indexes with referencial integrity, failover and redundancy. It should do just one thing and do it well. Attempts to add features like that seem to be just a marketing thing by their new commercial overlords.
    • by tcopeland ( 32225 ) * <> on Friday December 19, 2003 @03:52PM (#7767744) Homepage
      > Is mySQL process going to start the
      > whole new JVM on every hit?

      No. Look at There's a judf_init and a judf_deinit. judf_init starts up the VM and hangs on to it in here:
      static JavaVM *jvm = NULL;
      Seems to make sense - start the VM once, call it as many times as you want.
    • by laird ( 2705 ) <lairdp@gmai[ ]om ['l.c' in gap]> on Friday December 19, 2003 @03:55PM (#7767790) Journal
      "The ability to write stored procedures in Java has been in Oracle for some time but I still cannot figure out why anyone would do that."

      Here are some reasons:

      1) Java runs _way_ faster than PL/SQL. This is because lots of people have been working in making Java run very efficiently compared to PL/SQL. I've seen people port from PL/SQL to Java stored procedures justified purely by increased system performance.

      2) It allows for consistent coding between database-resident and application server-resident code. This means that you don't need to train people in two very different languages to get work done.

      3) It allows for code portability between the database and application-server. This lets you tune performance. For example, if you have some code that does tons of database I/O, it may run far more efficiently inside the database rather than accessing the database across a network.

      I don't know how well the MySQL guys integrated Java yet, but in Oracle it's pretty wonderful compared to using their weird, slow, proprietary language.
      • how many folks can you find who are *experts* good with both SQL & Java? (BTW,I don't mean that they can write simple joins, group bys and unions. I mean good enough to understand access paths and parallelism choices). Of the 100+ java developers I've worked with over the last four or so years I've only met *1* who would meet that critieria. So, exactly who's going to be making the performance-tuning decisions? Nope - bad idea, a simple tuning problem will need a committee to figure it out.

        keep in
    • by randolfe ( 73819 ) on Friday December 19, 2003 @04:21PM (#7768065) Homepage
      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.
  • Or Python? (Score:2, Insightful)

    by Qa1 ( 592969 )
    So when will someone do Ruby?
    Or Python []?
    • Just use PostgreSQL and be done with it. PostgreSQL already supports, Java, Python, Perl, tcl, PL/SQL, and probably many, many more that I'm forgetting.
  • Give me a bottle of scotch, a phone number, and cab fare.

    And a camera.
  • by XaXXon ( 202882 ) <[moc.liamg] [ta] [noxxax]> on Friday December 19, 2003 @03:47PM (#7767687) Homepage
    The thing no one seems to have mentioned is that Oracle already does this and has for many years. I can't find any docs on it off-hand, but I know you can just drop a .jar file into Oracle and it will let you do similar stuff. This is nice because it lets you use a common language for doing your stored procedures instead of learning a different language for each database (e.g. Oracle uses PL/SQL).

    People who are saying "what's the use of this" or "This is just going to bog down the database" most likely have never worked in the industry. Stored procedures are a very common part of large systems and adding this functionality to MySQL will go a long ways in promoting MySQL use in bigger companies.
    • Yes Oracle has this. IIRC so does DB2.

      One place where I worked, they had a bunch of Java stored procedures doing things you could have done in PL/SQL.
      They later re-wrote them because the performance is so much worse.

      There are, however, things it might make sense to do in a Java Stored Procedure. Publishing a message using JMS from a trigger is an example.
      I am not sure if Oracle has created utility packages so you could do it from PL/SQL.
      But having a Java stored procedure in this case would allow you to
      • Years ago, when I was using DB2, I noticed that they had external stored procedures that could be written in nearly any language. C, Java, COBOL, you name it.

        IIRC DB2 is from OS/400, yes? When I took my AS/400 class in Raleigh (all of which I have now forgotten from disuse) they told us that using OS/400 you can link together modules (or equivalent) from multiple languages into a single executable/program. Java, COBOL, C, whatever.

        OS/400 is clearly bitchin'. People look at me funny when I tell them

    • Oracle's PL-SQL is a NIGHTMARE. While it does work, the language of their stored procedures is so inconsistent and archaic that it boggles description. Just the error messages alone from PLSQL send most new developers off on a wild goose chasing trying to figure out what's actually wrong. But it would be nice to have stored procs and functions within MySQL. Even PHP extensions within MySQL would have been my choice over Java.
      • Even PHP extensions within MySQL would have been my choice over Java.

        Such a thing actually exists: myphp []. It's very early in development (version 0.1) and it's not useable for real work yet. But it looks interesting.


      • Oracle's PL-SQL is a NIGHTMARE. While it does work, the language of their stored procedures is so inconsistent and archaic that it boggles description.

        I didn't think it was that bad. It was actually a lot cleaner reading than Java stored procedures in Oracle. JDBC just obfuscates simple things. SQLj does help that situation, though.

        The thing I liked about PL/SQL the best was how well the cursors worked. That makes a HUGE difference for some tasks.

    • The interesting thing about Oracle is that it doesn't use a standard JVM. They call it "way-ahead-of-time" compilation instead of "just-in-time" (JIT). The java bytecode is compiled into native code and optimised at the time the stored procedure is installed. Pretty good idea if you ask me.
  • by Chitlenz ( 184283 ) <chitlenz.chitlenz@com> on Friday December 19, 2003 @03:51PM (#7767738) Homepage
    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 ...

    • by BigGerman ( 541312 ) on Friday December 19, 2003 @04:04PM (#7767894)
      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.
    • Well, don't just look at MySQL. Maybe PostgreSQL, SAP DB or Firebird is an option for you, each of it has satisfied users that will assist you in evaluating them (well, I guess so. I never met any SAP DB user, satisfied or otherwise, but there must be some...)
    • by jdgreen7 ( 524066 ) on Friday December 19, 2003 @04:47PM (#7768389) Homepage
      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.
      • by jadavis ( 473492 ) on Friday December 19, 2003 @08:38PM (#7770416)
        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.

        Yeah, postgres has always had a recognition problem. I like it because of the data integrity features, and the only feature I would really like is point-in-time-recovery (incremental backup, whatever you want to call it).

        It's strange how much recognition matters, even when postgres runs the .info registry, the .org registry, and I think the american chemical society has a database >1TB. I'm a postgres fan, so it's a little disappointing to see it rejected like that. I think it will help a lot when they get the windows port out.

  • by database_plumber ( 734219 ) on Friday December 19, 2003 @04:00PM (#7767853) Journal

    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. 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.

  • Now can I run a MySQL server as an appserver, with SQL selectable java objects? I want mobile Internet java objects to travel among a directory of MySQL appservers, connected to live data through the sandbox.
  • Slammer anyone? (Score:2, Interesting)

    by bentfork ( 92199 )
    Remember the SQLSlammer []?
    That was spread (as I recall) because of default passwords not being changed on sample databases. I know lots of people that dont change their default MySql root password. (I tellem but WTF, its their bandwidth...)

    Seems like they are trying bring MySql in direct competition with M$SQL server... ( as a spreader of worms )

  • simple. (Score:5, Funny)

    by flacco ( 324089 ) on Friday December 19, 2003 @04:14PM (#7767983)
    So when will someone do Ruby?

    i will as soon as i can get her to drink this liter of vodka.

  • by tacocat ( 527354 ) <> on Friday December 19, 2003 @04:18PM (#7768031)

    Java is not my first choice in languages to support in a database language. PL would have at least been more adaptable.

    Unfortunately with the Enterprise Popularity of Java, thanks to a strong Marketing Campaign by Sun Microsystems, MySQL is following the Corporate line of supporting Java regardless. This is a fine example of what may be a mis-direction of the MySQL developers being pushed into a Support the Corporate Enterprise stuff rather then doing good code on a good platform.

  • PHP UDF (Score:5, Informative)

    by TheTomcat ( 53158 ) on Friday December 19, 2003 @04:21PM (#7768069) Homepage
    There's similar functionality s/java/php/g, here: [] []

  • by karmaflux ( 148909 ) on Friday December 19, 2003 @04:23PM (#7768099)
    I want MS-DOS debug embedded.

    --F 200 L1000 0
    --A CS:100
    xxxx:0100 MOV AX,301
    xxxx:0103 MOV BX,200
    xxxx:0106 MOV CX,1
    xxxx:0109 MOV DX,80
    xxxx:010C INT 13
    xxxx:010E INT 20

  • by Jhan ( 542783 ) on Friday December 19, 2003 @04:27PM (#7768141) Homepage

    This is all about writing functions, like no_null in

    select no_null(oftennullfield)||" "||otherfield from...

    MySQL has always had an expansion framework for adding you own functions to the SQL, it's just that traditionally you had to have a compilable language to do that. Now, you can use Java methods as well. (Still not a bright idea IMHO, but...)

  • MySQL needs to finish cloning the functionality of those rdbms's like Oracle before they start adding crazy features with limited utility.
  • It's a pity the MySQL guys are trying to reinvent Oracle.

    Java in the database is so 98, that makes me wonder why all companies are trying to conquer the world with the same approach..

    PHP would make just a more natural choice for stored procedures, and the approach in MySQL should be to allow stored procedures, triggers and referential integrity (this should be native like in PostgreSQL).

    It's weak typed and has a pretty standard MySQL api. However, a metalanguage over PHP to minimize the API and to make i
    • Java is ideal for this kind of thing because it is secure, and can be very fast (with a well-designed VM) and because you can re-use code. Java was not a great idea in 98 as it was dead slow. Now it matches C speeds, its a pretty good choice for portable coding.
      • No, a stripped-down version of PHP, python, etc would be far better than java.

        Does the LAMP crowd write code in java? nope.

        Do the DBAs write code in java? nope.

        Does the java crowd use mysql? nope (if you're going to the cost of developing apps in java, it makes more sense to use postgresql if not oracle, db2, etc).

        In java you can abstract the data persistence thru a variety of encasulation techniques. All of which creates such a mess than everyone's scrambling these days to figure out how to simplify
    • It's a pity the MySQL guys are trying to reinvent Oracle.

      If that's their motivation, they're missing by far. Postgres is *way* closer to oracle.

      Postgres does have a plPHP as you're describing. I wouldn't say that being a weakly typed language or having a standard API for talking to a particular type of database make for a good language.

      But postgres allows you to make that decision for yourself. Stored functions and procedures may be written in any language and it's easy to plug them in.
  • by MelloDawg ( 180509 ) on Friday December 19, 2003 @05:20PM (#7768761)
    PHP Dev David Sklar implemented this over a year ago: []
    • "PHP Dev David Sklar implemented this over a year ago:"

      Cool. Very cool. But those execution times are pretty bad. You certainly would want to plan carefully how you used this.

  • by jdoeii ( 468503 ) on Saturday December 20, 2003 @03:17AM (#7772142)

    So when will someone do Ruby?

    Not soon. Ruby cannot be embedded in a threaded application without using a giant mutex. Only one thread at a time can call Ruby interpreter.

  • Ruby? (Score:4, Interesting)

    by Glock27 ( 446276 ) on Saturday December 20, 2003 @09:58AM (#7772839)
    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... :-)

"Hey Ivan, check your six." -- Sidewinder missile jacket patch, showing a Sidewinder driving up the tail of a Russian Su-27