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?"
Now how about. (Score:5, Interesting)
Re:Now how about. (Score:5, Informative)
Re:Now how about. (Score:2, Insightful)
There needs to be someway of doing online back ups of MySQL with out spending money.
Re:Now how about. (Score:3, Informative)
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.
Re:Now how about. (Score:3, Informative)
It's trivial really. Announce to the database that you want to perform an online backup. This marks the database in a special mode that lets it know that data writes are going to be at the block level vs. row level.
Also data modifications are still written to the datafiles and also are always written to special files (simultaneously and at the sime time
Re:Now how about. (Score:2)
Re:Now how about. (Score:2, Informative)
Re:Now how about. (Score:3, Troll)
A real man would other get off his ass and code it himself, or support the damn developers who do. Note "Bitch on
Hmmmm (Score:5, Insightful)
Better than pulling stuff to the client (Score:2)
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
Old hat (Score:2)
Re:Old hat (Score:4, Insightful)
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.
Apparently somebody already did (Score:2, Informative)
MySQL Ruby Interface [google.com]
Google Cache here [216.239.57.104].
Google's your friend [google.com]
My bad (Score:4, Insightful)
Re:Apparently somebody already did (Score:2)
I didn't see a way to do that... all the examples look like pretty straightforward query processing.
Re:Apparently somebody already did (Score:2)
there is no built-in ruby interpreter in mysql such that you can write functions of mysql in ruby.
not missing the point is your friend!
database language? (Score:5, Funny)
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)
Re:database language? (Score:2)
judf uses the Java Native Interface... (Score:5, Informative)
That link in your sig. (Score:2, Funny)
Now leave me alone as I am going to spend the next 2-3 hours playing with it. Really Cool.
Sounds like... (Score:3, Insightful)
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.
Re:Sounds like... (Score:2)
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:2)
Re:Sounds like... (Score:2, Insightful)
Re:Sounds like... (Score:5, Insightful)
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)
Re:That's great (Score:3, Insightful)
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.
Re:That's great (Score:4, Informative)
Re:That's great (Score:2)
5.0 isn't out yet - it's a development tree. That's like saying MS SQL Server has C#-based stored procedures - it will, in the next version, but the current public version does not.
Re:That's great (Score:2)
I have it running right now and I know it is working for others.
That and I know for a fact that the first binary release is pretty close to happening
Re:That's great (Score:2)
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
Re:That's great (Score:3, Informative)
Re: (Score:2)
Re:waaaaiiiit a minute... (Score:4, Informative)
> in the database?
Nope, they're external to the DB.
> program your own functions like
> insert/modify/etc in java
You can program functions in Java, and then call them from MySQL queries. From the README: Nifty!
Re: (Score:2)
Production Quality OpenSource! (Score:3, Funny)
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.
Nativily? (Score:2)
"Nativily"? Boy, the Christmas spirit is just busting out everywhere today!
Java in the DB - very, very bad idea (Score:3, Insightful)
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.
Re:Java in the DB - very, very bad idea (Score:5, Informative)
> whole new JVM on every hit?
No. Look at judf.cc. There's a judf_init and a judf_deinit. judf_init starts up the VM and hangs on to it in here: Seems to make sense - start the VM once, call it as many times as you want.
Re:Java in the DB - very, very bad idea (Score:5, Informative)
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.
and here is a great reasons not to (Score:3, Interesting)
keep in
Re:Java in the DB - very, very bad idea (Score:3, Informative)
I agree that the strongest argument for running Java inside the database is because Java ia full featured programming language. Your example with string parsing sent shivers down my spine.
Re:Java in the DB - very, very bad idea (Score:5, Interesting)
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.
Re:Java in the DB - very, very bad idea (Score:2)
You must have knees like Olive Oil.
Or Python? (Score:2, Insightful)
Re:Or Python? (Score:2)
Re:Or Python? (Score:2)
So when will someone do Ruby? (Score:2, Funny)
And a camera.
Oracle already does this... (Score:5, Informative)
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.
Re:Oracle already does this... (Score:3, Informative)
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
Re:Oracle already does this... (Score:2)
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
Re:Oracle already does this... (Score:2)
Re:Oracle already does this... (Score:2)
Even PHP extensions within MySQL would have been my choice over Java.
Such a thing actually exists: myphp [sklar.com]. It's very early in development (version 0.1) and it's not useable for real work yet. But it looks interesting.
JP
Re:Oracle already does this... (Score:2)
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.
Re:Oracle already does this... (Score:3, Informative)
Where is MySQL anyways? (Score:5, Interesting)
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? (Score:5, Interesting)
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? (Score:2)
Re:Where is MySQL anyways? (Score:5, Interesting)
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:Where is MySQL anyways? (Score:4, Informative)
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
Why you would do this . . (Score:3, Interesting)
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.
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.
MySQL java appserver? (Score:2)
Slammer anyone? (Score:2, Interesting)
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)
i will as soon as i can get her to drink this liter of vodka.
Not my first priority (Score:3, Insightful)
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.
Re:Not my first priority (Score:3, Insightful)
PHP UDF (Score:5, Informative)
http://talks.php.net/show/phpquebec/27 [php.net]
http://www.sklar.com/page/article/myphp [sklar.com]
S
While we're at it... (Score:3, Funny)
--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
--G
BWAHAHAH
Oh come on, NOT stored procedures (Score:5, Informative)
This is all about writing functions, like no_null in
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...)
get useful features first.. (Score:2)
PHP would have been a much better choice (Score:2, Informative)
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
Re:PHP would have been a much better choice (Score:2)
except that for the skill requirements (Score:3, Insightful)
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
Re:PHP would have been a much better choice (Score:3, Informative)
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.
PHP has this for over a year (Score:3, Informative)
Re:PHP has this for over a year (Score:3, Insightful)
Cool. Very cool. But those execution times are pretty bad. You certainly would want to plan carefully how you used this.
--Richard
Embedded Ruby is not thread-safe (Score:4, Informative)
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)
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... :-)
Re:Keep this out. (Score:4, Insightful)
The 'lusers' may not use this new feature but Java programmers will and hopefully will enjoy it.
Re:Keep this out. (Score:2)
Re:Keep this out. (Score:2)
Have you even worked with Java ? I don't mean puttering around at home in between failed job interviews. I mean in a production environment.
Javascript != Java.
Re:Keep this out. (Score:3, Insightful)
But it doesn't go away.
I have noticed that there are several technologies that are held in high regard outside of the Linux/Free Software that are despised within it.
One is Object Oriented programming. By extension, C++, Java, and UML also fit into this category.
I wonder how many folks who bash these things have ever actually bothered to learn them?
It is easier to say that XYZ is "bloated and ugly" than to say "I never learned the
Re:Keep this out. (Score:2)
Note that this is a very different kettle of fish from running Java on the server. Server side Java is just getting bigger, basically, and one of the things that is making this happen is increasing scalability.
However, three tier apps are generally bottlenecked around the database - because by necessity there can only be one of them. Therefore, while stored procedures and triggers are vali
Re:Keep this out. (Score:2)
Such that I uninstalled Java.
Re:Keep this out. (Score:5, Insightful)
1) Java isn't going to slow down any queries unless you use Java functions.
2) What do you care that someone else isn't smart enough to write good software?
3) MySQL as it stands has no other way to really embed functions easily, and it's actually more effecient to run code on the server and transfer data back afterwords.
Re:Keep this out. (Score:5, Informative)
Most of the time we detect who it is and suspend their account, but I still wouldn't want them running java code inside mysql.
Re:Keep this out. (Score:2)
Re:Keep this out. (Score:2)
Did you not think about that post? Sounds like you just dislike Java so much that hearing it in the same sentance as MySQL makes you cringe.
I think the answer to both questions is yes.
1) Java isn't going to slow down any queries unless you use Java functions.
Which is exactly what the poster fears they will do.
2) What do you care that someone else isn't smart enough to write good software?
I think this is the heart of your disconnect with the poster. True, if you are using MySQL as most do, in a
Re:Keep this out. (Score:2)
That's the issue -- the poster's complaint actually has nothing to do with MySQL embedding Java in particular -- he's worried that if he allows users to execute stored procedures they can bog down his database.
I don't know about MySQL, but in Oracle, Sybase, etc., you can put a CPU time limit on queries so that if someone starts sucking the system down their queries time out.
Re:Keep this out. (Score:2)
"Sounds like a job for stored procedures ala Oracle. If MySQL does not have them it should."
That's the issue -- the poster's complaint actually has nothing to do with MySQL embedding Java in particular -- he's worried that if he allows users to execute stored procedures they can bog down his database.
Actually, I think that is exactly what the poster is worried about. Correct me if I am wrong, but I had the understanding that Oracle stored procedures were compiled C code. In this case, we are talking
Re:Keep this out. (Score:3, Interesting)
I work in such a company, as an application programmer. Where I work, it's not up to the DBA to make things
Re:Keep this out. (Score:2)
"But in most shops employing RDBMS, you have one team that maintains the OS, one team that maintains the database, one team that maintains the network, and finally a team that writes and maintains the actual applications. What the poster is probably worried about is being a DBA when some application programmer uses Java badly and then his boss leans on him, as the DBA, to make the process work faster."
I work in such a company, as an application programmer. Where I work, it's not up to the DBA to make thin
Re:Keep this out. (Score:2)
Re:Keep this out. (Score:2, Funny)
Oh, come on, everyone knows mySQL doesn't support subqueries because they allow a lazy user to slow down the database. You're supposed to do simple queries against the database, and then JOIN the results in your C application. Anyone who can't write a safe, fast JOIN in C is just a bad programmer, and should not be using an enterprise-level RDBMS like mySQL. Go back to FileMaker, you SQLamers!
(This troll broug
Re:mysql (Score:3, Interesting)
I would say that it is definitely one of Java's advantages. I think this feature is useful from the perspective that more small applications will be written by developers that may not be skilled with SQL coding. Many open source projects start out with one developer, and that single developer might not have an arsenal of skills. That is where this sort of thing helps out, that single developer will now be able to use his/h
Re:give me a break (Score:3, Interesting)
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?
Re:Firebird SQL (Score:2)
Re:silly question perhaps.. (Score:2)
Re:Obligatory comment/troll (Score:2, Interesting)
Spoken like a true linux zealot with absolutely no foundation in relational data management.
Do you realize that referential integrity is a fairly new feature in MySQL? Do you understand the reasoning behind a transaction based DBMS? How about the simple normal forms layed out by Boyce and Codd?
Anyone who's done any extensive DB work will tell you how lacking MySQL is. I *love* linux, its the on
Re:Krow (Score:4, Informative)
I resigned because I had been working on Slash for 3 years and wanted to do something new. I rather like the people who run this site, and still follow the development of it and point out feature improvements from time to time. I no longer develop the code myself, except for a few sites that I happen to help with.
Just to poke another hole in this, if I was fired would I still be an author on the site some 7 months later? I think not.