Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

MySQL Gets Perl Stored Procedures

CmdrTaco posted more than 11 years ago | from the well-isn't-that-special dept.

Perl 266

ryarger writes "Woo Hoo! After a seeming eternity of wait, there is finally an implementation of stored procedures for MySQL. It uses Perl as the stored proc language, too!" Also note that this piece of work was done by OSDNs own Krow. Very cool work I must say.

cancel ×
This is a preview of your comment

No Comment Title Entered

Anonymous Coward 1 minute ago

No Comment Entered


MySQL handle Perl Stored Procs? (-1, Redundant)

Anonymous Coward | more than 11 years ago | (#2213924)

That's Unpossible!

Re:MySQL handle Perl Stored Procs? (0)

Anonymous Coward | more than 11 years ago | (#2214334)

I n c o n c e i v a b l e !

I'd like to give (-1, Troll)

Anonymous Coward | more than 11 years ago | (#2213927)

MySQL a perl necklace.

Better stored proc languages... (3, Interesting)

Hagabard (461385) | more than 11 years ago | (#2213941)

Why didn't they use a Transact-SQL compatible stored proc syntax? This would ease migrating and also enable people who prototype DBs on MySQL to move it to either Sybase or MS-SQL with a minimal of fuss.

I'm not saying Transact-SQL is great or anything but it'd be nice if it was a bit more compatible with other systems.

Re:Better stored proc languages... (0)

Anonymous Coward | more than 11 years ago | (#2213976)

T-SQL, PL-SQL, hell, any type of SQL would be preferable to Perl. We can guess how much optimization is going on there. Oooo Lookie! Now I can do client-side joins *in the database*. Truely a perl hack's wet dream.

Once again, MySQL seems to have entirely missed the point. Remember when they tried to FUD foreign keys support?

Re:Better stored proc languages... (1)

Hagabard (461385) | more than 11 years ago | (#2214025)

Didn't really mean to criticize the person who released the added functionality. After looking into it more it seems it's an impressive hack, though, to a serious limitation with the database.

Really, though, I hope the release does not dampen any other development efforts that may be going on for true SP support within MySQL.

t-sql (0)

Anonymous Coward | more than 11 years ago | (#2214030)

t sql lacks many obvious features

how bout a 'for each row' operator

stupid cursors

Re:Better stored proc languages... (1)

Diamon (13013) | more than 11 years ago | (#2214042)

A better choice would be IBM's stored procedure language which is actually closer to SQL itself. People who are using MS-SQL are doing so (IMHO) because they don't know any better, they're not going to be going to MySQL. The target needs to be the DB2 / Oracle crowd.

Re:Better stored proc languages... (1)

rjamestaylor (117847) | more than 11 years ago | (#2214058)

The target needs to be the DB2 / Oracle crowd.

Amen! MS SQL is "Access Enterprise" compared to Oracle or DB2...or Informix, for that matter.

Re:Better stored proc languages... (1)

mal3 (59208) | more than 11 years ago | (#2214165)

Bullshit. I hate MS as much as the next guy, but MSSQL is the one product they did right. Since version 7.0 it's been rock solid. Don't make me start about how bad PL/SQL(cursors everywhere) is as a database language.


Re:Better stored proc languages... (0)

buttfucker2000 (240799) | more than 11 years ago | (#2214333)

Fuck you. We've got a highly experienced Oracle DBA here who can't do half the shit I do with MS SQL 7. He's got 900 different SQL related utilities installed on his desktop, and has to jump all over the place just to run a quick and dirty query. I've got almost everything I need in Enterprise Manager. He's got a bunch of fucked up processes pushing around data running using AT commands and such, I just create a DTS and schedule it. If I want to reschedule maintenance, or kill a potentially deadlocked process? Click-click-click done. MS SQL 7 works amazingly for me, and if I could convince the powers that be, we would get SQL 2000 in a heartbeat.

Re:Better stored proc languages... (1)

horster (516139) | more than 11 years ago | (#2214168)

Don't forget that Sybase still has the copyright to T-SQL. There is no such thing as 'MS-SQL' just T-SQL, thought SQL Server is an MS product.

Say what you want about SQL Server, but Sybase is a solid product.

Re:Better stored proc languages... (-1, Troll)

buttfucker2000 (240799) | more than 11 years ago | (#2214109)

This is pretty fucking stupid. I've written several 3 tier apps with MSSQL on the data tier, and I use T-SQL stored procedures a LOT. They're precompiled, optimized, and a hell of a lot faster than building up some complex inner join on the middle tier and passing it as a SQL statement. I'm open to the possibility of building new servers with MySQL, but this stupid sort of shit makes that impossible.

Why can't these fucktards concentrate their efforts on creating a standards-compliant product first, rather than fucking around with this sort of worthless feature shit? This is the shit that pisses me off. If I want to use perl, I'll use it on the middle tier. Give me a feature complete SQL db (ACID compliant, stored procedures, UDTs, UDFs, triggers, etc) first, and then you can jerk off and add your little fun features and have penguins pop up and shit. Fuck you Krow. DO YOU HEAR ME? FUCK YOU KROW.

MySQL found dead (1, Funny)

Anonymous Coward | more than 11 years ago | (#2214133)

MySQL was found dead in a shabby skidrow hotel room today, it would have been 9. Authorities stated that the cause of death was choking on a foreign object. Apparently Perl stored procedures had been shoved down its throat, and in it's already feeble state, MySQL succumbed. You may not have ever used MySQL, but it was the anemic aircooled volkswagon engine powering such web sites such as slashdot, truly an American icon.

Re:Better stored proc languages... (2, Insightful)

bzhou (138968) | more than 11 years ago | (#2214170)

Why is the suggestion of T-SQL funny? It's just an alternate, and IMHO a better-than-perl alternate. It's more compatible with core SQL and more compatible with the calling layer. I don't think people using Python or JDBC will be too fond of the myperl solution. T-SQL started from Sybase, not everything MS using is bad. But kudo and congrats to the myperl author anyway, at least people start to feel the need of stored procedures.

I would also like ... (2, Insightful)

Da VinMan (7669) | more than 11 years ago | (#2214241)

if they put hooks into a generic MySQL facility which allows *any* programming language to serve as a SP language in the server. Why can't I use Python? Why can't I use xxx? It's widely rumored that Microsoft is doing this same thing for the next version of SQL Server, so this really isn't such a radical idea. The trick is to devise an abstraction within MySQL that represents all stored procedure capabilities, and then interface each target language to that layer.

I agree that having a Transact-SQL equivalent will be key to consideration by serious database users, but it's just a starting point.

Re:I would also like ... (1)

NineNine (235196) | more than 11 years ago | (#2214292)

That's truly stupid. I wish I had some mod points for you. The whole point of stored procedures is to improve performance, and to encapsulate business logic. Why in the HELL would you want another layer in there? For flexibility? Why do you need to be able to write stored procs in 10 different scripting languages? That's asinine. You need performance, period. A PERL hack on top of a layer on top of MySQL is definately NOT going to give you that. If they actually wanted MySQL to be used by people who knew what they were doing, they would've integrated in PL/SQL.

Oh come on... (1)

Da VinMan (7669) | more than 11 years ago | (#2214339)

I agree that the point of SPs is performance. But nothing I described would really preclude that. I wouldn't have used Perl either, but that doesn't mean it's a bad start. For now anyway, it's just a hack. They're not caching execution plans, etc. anyway, so this debate is almost entirely worthless.

On that note, you could afford to be nice you know, it wouldn't cost you a dime. Besides, I tried to re-butt by looking at the logic you employed above, and their really isn't anything other than a couple assertions. We could both come up with an entire list of social/political and technical ways of how to improve on the idea, but the tone of your message is completely insulting, so why should I waste my time?

You're not nearly as great yet as you would like to be. Your post gives that away more clearly than meeting you would.

That's great. (1, Offtopic)

briggsb (217215) | more than 11 years ago | (#2213943)

I've been waiting for that. Good job Krow.

Now if we could only get high school students [bbspot.com] to understand Perl and MySQL we'd be all set.

Re:That's great. (0)

Anonymous Coward | more than 11 years ago | (#2214012)

Now if we could only get high school students [bbspot.com] to understand Perl and MySQL we'd be all set.

Just because you had trouble learning one of the most braindead languages in existance doesn't mean we all did.

WhY? (-1, Offtopic)

Anonymous Coward | more than 11 years ago | (#2213944)

HOW GAY IS THIS? Would anyone use it?

I am not, the message centre, local or direct.

Java (1)

sean@thingsihate.org (121677) | more than 11 years ago | (#2213958)

But what about the java? The java support on Oracle is pretty damn nice, and damn it, I don't think it's "crazy" to expect the same kind Oracle-quality from mysql...


Re:Java (0)

Anonymous Coward | more than 11 years ago | (#2213999)

To think that MySQL will ever attain anything even remotely like Oracle-quality is insane. You can support open source without getting stupid can't you?

Oracle's Java (0)

Anonymous Coward | more than 11 years ago | (#2214010)

Oracle's Java support is terrible. Why would you want to slow your server down to a crawl?

WTF? (0)

Anonymous Coward | more than 11 years ago | (#2214069)

Java stored procs? Jeeze, that's as bad as Perl stored procs. When can we expect tcl/tk stored proc support?

This is in the damn database, people. You don't want to be able to mangle files or make gifs bounce around on the screen.

Ok, so it is a neat hack. Sounds like an architecturally stupid idea.

Re:WTF? (3, Informative)

dameatrius (182345) | more than 11 years ago | (#2214176)

Actually, this is very useful. If you want to do say 6000 inserts using a comma delimited string or something along those lines, to open a connection and call a specific stored procedure is EXTREMELY slow compared to parsing the string internal to the stored procedure (recent test I did when designing some software showed an insert called 6000x took 16 seconds compared to 1.5 seconds parsing the string in the db). Now when you use a language like java or perl to do that internal to the db, it will drop that time even more as SQL design wise has string manipulation features but isn't meant to be doing it. I would much rather have a Java parser that I call inside my sp and have it take half a second versus 1.5 seconds to have PL/SQL parse my strings. If you actually did any development involving database interaction, this would be pretty obvious.

Get the fuck out!! No way!! (-1, Troll)

Anonymous Coward | more than 11 years ago | (#2213965)

MySQL can handle stored procedures? No fucking way. Holy shit, batman. And Perl is the stored procedure language, too? Oh my God! I wouldn't have believed it if I didn't see it with my own eyes. Oh the humanity.

Man, this will really change everything. It's like the internet is brand new, all over again. This is a sea change, to say the least. MySQL is born again. Krow, if I knew anything about what this meant, I'd say you rock dude.

Not a DB guru (2)

Sir_Real (179104) | more than 11 years ago | (#2213968)

Could someone enlighten me as to the usefulness of stored procedures? Are they significantly faster? Are they easier to use than the straight jdbc/dbi api?


Yes and yes (2)

wiredog (43288) | more than 11 years ago | (#2213987)

My experience has been that someone programming an app doesn't have to learn all the inticacies of Oracle, etc, to get work done. The dba writes procerdures and other people just call that procedure instead of writing huge ugle SQL queries.

Re:Yes and yes (1)

spudnic (32107) | more than 11 years ago | (#2214072)

This is the only explanation that I see that could make a difference to me. I see how giving users a simple way to run a common complex query would be very helpfull.

Now help me understand. Say I'm writing custom web apps for people using Perl and mySQL. There is no ad-hoc reporting capability, etc. How does this benefit me?

Any insight would be greatly appreciated.

Re:Yes and yes (1)

dameatrius (182345) | more than 11 years ago | (#2214216)

Stored procedures are MUCH faster than passing SQL strings into the db to be run. An execution plan has already been computed and the sql has been in essence, compiled. The weird thing is, building a dynamic sql string inside a stored procedure and executing it is even faster than passing sql in both MS SQL Server AND Oracle. I have no idea why that is true, but in testing it, it has been faster. It is also nicer design wise in that if you use the same sql statement in 30 different places and then you decide the calculation for one of the values is wrong, in a stored procedure, you change it once, with embedded sql, you have to change it 30 times (code re-use)

Re:Not a DB guru (2)

Overt Coward (19347) | more than 11 years ago | (#2213994)

The biggest advantage is that all of the processing takes place in the database, so you don't have the performance hit of reading records out of the DB, doing the processing, and then sending back the results.

Re:Not a DB guru (2)

rjamestaylor (117847) | more than 11 years ago | (#2214192)

Basically, it keeps application logic within the DB.

One benefit, as you mentioned, is reduced data transfer out of the DB into the application over either a system bus or network connection (which can be a SERIOUS performance problem, especially if that network connection is thin or the application resides on a low-memory, slow CPU client).

Secondly, this allows business-rule enforcement at the DBMS, instead of relying on the application logic to do the same. This second reason is perhaps more important than the performance benefit.

The problem with SPs are: breaks n-tier model and increases processing strain on DB server (possible performance hit -- and increasing CPUs usually raises license cost of proprietary DBMSes), ties the application to the DBMS perhaps inextricably.

Re:Not a DB guru (2)

drudd (43032) | more than 11 years ago | (#2214004)

It's nice when you can code something up to run automatically... let's say you have several different ways a purchase order gets into your table, and you need to insert a row into another table any time a purchase order is added.

Rather than having to remember to put that second insert everywhere you have a purchase order insert, it's nice to have it automatically run on the server (particularly since you don't have to communicate with the client).

Another use is if you need to delete all of someone's addressbook when they delete their account... then you just monitor a delete on the "person" table and delete all records associated with it in the "addressbook" table.


Re:Not a DB guru (1)

bare_naked_linux (306356) | more than 11 years ago | (#2214124)

The above examples are great uses for stored procedures, however, the best way to accomplish the examples require triggers as well as stored procedures. Either example can be accomplished by having the client app request that the database run the appropriate stored procedure rather than using embedded SQL. However, it's much easier and safer to have the proper stored procedures run whenever an insert or delete is done on the tables in question. That way, if you add embedded SQL later, it still takes care of the necessary stuff. MySQL doesn't have Triggers yet, I don't think.

Re:Not a DB guru (0)

Anonymous Coward | more than 11 years ago | (#2214289)

It's true triggers are useful, but I think what the parent was getting at was that procedures allow you to abstract your database interaction away from the the table(s).

For example, you could have "INSERT PurchOrders VALUES (0,1,2,3)" or you could have "InsertPO (0,1,2,3)" with optional and named parameters. Thus possibly protecting client code if the underlying table changes.

Re:Not a DB guru (2, Insightful)

msheppard (150231) | more than 11 years ago | (#2214067)

SP's let you delgate a lot of the processing that should be done on the database (performance/encapsulation being good reasons to do this) to the database. And your biz layer doesn't have to do it and deal with the interface overhead. Things like complex sorting, or reference other tables.

Given that ideal: Most stored procedures are just very complex select statements anyway.

Re:Not a DB guru (2)

smack.addict (116174) | more than 11 years ago | (#2214079)

Stored procedures are generally a bad idea in distributed and multi-tier Web applications. They are a product of the client/server era when there was no clear tier for business and logic and thus the only real way to share business logic was to place it in the database.

Today, if you are building a multi-tier Web application, you should be placing your business logic in a mid-tier application server like Orion. Stored procedures, in this environment, have only a limited role for VERY specific optimizations.

Perl stored procedures, IMHO, are an abomination.

Why not (1)

Camel Pilot (78781) | more than 11 years ago | (#2214162)

O.K. lots of comments here like:

Perl stored procedures, IMHO, are an abomination

But can anyone cogently argue why not?

One reason is obviously non-standard and compatibility. However, all the XX-SQL syntax's are mutually incompatible too, right?

On the other hand Perl is a supreme text parsing language and most database functions are text handling of arrays and record hashes. Perl is very fast, mature and stable.

Just interest in more experienced thoughts.

Re:Why not (1)

dameatrius (182345) | more than 11 years ago | (#2214263)

You both have to be kidding, not using stored procedures is a really bad idea. Although you have PL/SQL and T-SQL and whatever other SQL's there may be, it is easy enough to write sql statements that can be used in both Oracle and SQL Server. In terms of them being an out of date thing, PUT THE CRACK PIPE DOWN!!!. They are faster than embeded sql. They are VERY useful in terms of code re-use. I don't think I can think of any problems with them other than maybe missing functionality like recursion but then that is a problem with SQL.

Re:Not a DB guru (1)

mal3 (59208) | more than 11 years ago | (#2214236)

This appplies to MSSQL server, I'm not positive about Oracle, DB2, or Postgres but I'm pretty sure they work the same way.

When a stored procedure is created on the Server it's compiled the first time it's run, and an execution plan is generated based off of statistics gathered from the data. After that it's not compiled again(until the server reboots or something). This compilation can take quite a long time even if it's just a select statement. If you don't use a stored proc your recompiling every time you run a statement(unless it's the EXACT same statement as previous and the execution plan is still in the cache). This alone is good enough reason to use stored procedures.

I won't go into transactional control and the fact that frontend developers love to muck databases.


Re:Not a DB guru (1)

Diamon (13013) | more than 11 years ago | (#2214081)

In addition to the other benefits stored prcedures are useful in that you can apply logic to the data while it's still in the DB and cut down the amount of data being sent to the client for processing.

Re:Not a DB guru (1)

ChristTrekker (91442) | more than 11 years ago | (#2214097)

Stored procedures offer several advantages to the programmer that needs to interface with a db.

  • Encapsulation. The logic is stored in the database. This means you can just call a procedure, and BAM!, stuff happens. You don't need to know all the intricacies or even know SQL that well. You just need to know the expected inputs and outputs.
  • Performance. The SQL is (or at least can be) stored in a compiled, optimized form. This saves the SQL engine the work of doing that on the fly like it would have to if you just passed a string of statements.
  • Bandwidth. Related to that, you're also going to save a bit by passing a smaller string around.

There's probably some more I'm forgetting. All in all, if you have a bunch of queries that are repetitious and seldom change (other than parameters) stored procedures are a great thing.

BSD^H^H^H MySQL is dying.... (0)

Anonymous Coward | more than 11 years ago | (#2214149)

"Hey! that's just Richard Nixon's enemies list with his name crossed out and yours written on top."

No! and no! (1)

bartwol (117819) | more than 11 years ago | (#2214171)

The justifying theory was that "business rules" would reside with the data, in one place, and thereby avoid the hazards of duplicated logic across applications. The motives were: 1) by the database publishers to create a proprietary dependency upon their particular dialect of stored procedure language, and 2) by benchmark-driven dweebs who mistakenly think the incremental speed gains are material to customer satisfaction (they are not).

The reality is that few (none that I know) development shops will give up their preferred programming languages in favor of these more proprietary languages. SO, the value of isolating business rules in the database is not realized. BUT, the dweebs come home to roost again, as they insist on doing SOME of their coding in stored procedures under the guise of the previously mentioned excuses as well as any other forms of obfuscated logic that they may employ. And why? Because it's their idea of fun, whether they know it or not. And the cost? One more language in use, another skill set needed, more cross-training, another MAJOR blow to portability across SQL databases and the increased vendor-specific dependency that comes with it.

Score one for the database publishers. Score one for the geeks (they get their vice). Loss goes to The Company as their costs escalate unnecessarily.


Re:Not a DB guru (1)

SpaceLifeForm (228190) | more than 11 years ago | (#2214217)

The primary advantage of stored procedures besides performance reasons is that you can store all of your DML (Data Manipulation Language) *in* the database. If done properly (you rarely see this in the wild), there is *NO* external code that does any INSERTs, DELETEs, or UPDATEs, only SELECTs from views and calls to stored procedures.

This results in three main benefits. First, your entire schema (tables, indexes, and stored procedures, etc) is more easily validated and kept consistent. Second, schema changes made by the DBA will only impact stored procedures and views, which can be properly maintained by the DBA. If the changes are just new columns or normalization activities, the existing external code will not have to be changed unless that code needs to see new columns in the new or modified views, or the external code has to pass additional parameters to stored procedures. Basically, you can reduce maintenance costs. Lastly, and most importantly in my experience, it prevents crap programmers from screwing up the data in the database. All data rules can be properly enforced in the database. If you let anyone do the INSERTs, DELETEs, or UPDATEs and you are missing the rules (triggers, referential integrity constraints, etc), then your database can be invalid. By keeping all of the rules *IN* the database, you'll have much better control and less problems.

Re:Not a DB guru (2)

CrackElf (318113) | more than 11 years ago | (#2214303)

The only time I ever used them was to create joins between a hierarchical and a relational database (using cobal rpc's). Of course the old hierarchical db should have been trashed years ago.

The biggest advantage (that I see) is that it allows you to shift some of the work to the db engine / hardware. For instance, if you have a mainframe with a lot of extra cycles, you might want to shift some of the business logic to it instead of the transaction manager, or the client.

The speed depends on the speed of the connection, the speed of the languages being used at the various tiers of the application, and the speed of the hardware at the various tiers, as well as the load. Are they easier to use? Depends on what language they are in, and which language you know better.


Great uses for perl (0)

Anonymous Coward | more than 11 years ago | (#2213982)

Gnumeric should follow MySQL's lead, and
dump GB (vb clone) and just use Perl for

Call me a troll.... (1)

BillyGoatThree (324006) | more than 11 years ago | (#2213986)

...but you have to wonder about a product that is made *easier* by adding *Perl*.

Re:Call me a troll.... (0)

Anonymous Coward | more than 11 years ago | (#2214291)

you're a troll. perl and mysql are not a *product*. they're free you idiot. free, as in free beer. in other words, they're giving it to you, so you shouldn't have a stupid attitude like that. product. hah.

perl is lame (0)

Anonymous Coward | more than 11 years ago | (#2213989)

and this website proves it.

plus, you guys are horrible coders too.

Not bad. (0)

Anonymous Coward | more than 11 years ago | (#2213991)

Perl stored procs - as if Oracle or Sybase stored procedure languages were not convoluted enough! Bravo!

Hmm...Not the only problem is.. (1)

Si (9816) | more than 11 years ago | (#2213998)

whether to write application logic as Perl in MySQL, or as SQL in Perl..

Perl? (0)

Anonymous Coward | more than 11 years ago | (#2214005)

What kind of a moron would use perl, which awards stupidity and promotes bad coding, and has to GUESS whether what you gave it is a regex or not?

Lame (0)

Anonymous Coward | more than 11 years ago | (#2214014)

Gee, Perl stored procedures... that will certainly make MySQL enterprise-ready... oh, except for that locking business it can't seem to get around, and the speed, and the instability, and the bugginess (but that's typical in OSS "projects")... oh well, back to MS SQL Server - fast, reliable, easy to use, all the functionality a business could need, beats the pants off any "free (if your time is worth nothing)" software solutions any day.

Re:Lame (1)

Drazi100 (458128) | more than 11 years ago | (#2214206)

you may be right about MySQl being that. I like MS SQL. Its the only product they have made that is any good. too bad it only functions on that buggy OS which makes linux look like King.("buggy OSS" project as you like to call it)
BTW as a SQL SERVER dba, I usually reach for my pistol when anyone asks me to use ASP( another buggy MS crap) on it.

MySQL (0)

Anonymous Coward | more than 11 years ago | (#2214019)

How about making it robust and reliable first? You know, so that it it doesn't corrupt itself to shit at the first sign of trouble.

Perl Yuck! (0)

Anonymous Coward | more than 11 years ago | (#2214028)

Why on earth did they use perl and not something a little more maintainable such as python? Just another good reason for me not to use a MySQL toy database.

MySQL becomes more and more useless (0)

Anonymous Coward | more than 11 years ago | (#2214049)

sigh. MySQL seems to be losing it. Instead of focusing on implementing missing features in a way that is at least half-way compatible with the other database systems out there (db2, oracle, mssql, etc.), they go off and implement some half-assed freak solution. The more MySQL diverges from the defacto standards set by $$$$-ware databases, the less relevant it becomes.

Most calls case core dumps currently (0)

Anonymous Coward | more than 11 years ago | (#2214064)

From the web page:


At the moment most calls to modules causes mysql to core (Something is up with the loader). Keep in mind that this is still experimental.


Subselects? (3, Funny)

hetfield (129762) | more than 11 years ago | (#2214071)

My boss (Windows NT admin) and I were just discussing MySQL. We're running a number of small databases with Oracle on NT (with a University License), but we started talking about MySQL when I mentioned Slashdot was powered by it. Our web server and my workstation are Linux in NT land, and I try to plug Linux wherever I can. My boss is even learning Perl so he can code for our web server.

He liked MySQL until he heard that it couldn't do two things: stored procedures and subselects. He said "I don't see how it could be useful without those things." All of the database apps he's ever written use those.

It's great to see stored procedures being implemented. It would be even better if/when subselects are implemented. I could make a stronger case for moving some things over.

Any chance of it happening?

Re:Subselects? (0)

Anonymous Coward | more than 11 years ago | (#2214104)

but we started talking about MySQL when I mentioned Slashdot was powered by it.

My boss and I do this too, and then both break into uncontrollable laughter. MySQL is a hack product, by hacks, for hacks. Which is fine, just don't expect high standards from it. And now that the Perl necklace is included, it is even more laughable. Nice work "Krow" (cool handle too bro), you certainly are a "database thug".

MySQL Touched My Winkie! (1, Funny)

Anonymous Coward | more than 11 years ago | (#2214152)

I had MySQL "running" (shit man, it was slow!) on a 900Mhz Intel Pentium 4 Linux box. I just got a nice, shiny, brand new 386 (4MB of RAM!) running Windows 3.1. Man alive! SQL Server running on the 386 kicked the poop out of MySQL. Baby! Then, while I wasn't expecting it, MySQL grabbed my winkie! Damn!

Re:MySQL Touched My Winkie! (1)

Drazi100 (458128) | more than 11 years ago | (#2214244)

SQL Server doesnt run an 3.1 you moron.
and if it did it was version 4.x. that version makes mySQL look enterprise ready.MSSQL didnt become good till version 7

Re:Subselects? (2)

baptiste (256004) | more than 11 years ago | (#2214121)

He liked MySQL until he heard that it couldn't do two things: stored procedures and subselects. He said "I don't see how it could be useful without those things." All of the database apps he's ever written use those

Well if he needs those two things, why not mention PostgreSQL [postgresql.org] to him? It can do both things - its a more feature complete DB than MySQL though in SOME cases its slower. But it all depends on teh application and just like WIndows vs Linux you'll see lots of MySQL vs Postgre flame wars too. I use both. I use MySQL for web based apps that have fairly simple backend needs and PostgreSQL for more complex setups.

Re:Subselects? (2, Interesting)

dorkstar (318427) | more than 11 years ago | (#2214218)

Well, this probably won't help convince your boss, but IIRC subselects are mathematically unnecessary. You can flatten any query down to a single select and what you get is much more efficient. Read the real scoop in the first chapter of any database textbook.

Actually, IIRC it's not even that hard to do it for 90% of queries...

But wait... (2)

don_carnage (145494) | more than 11 years ago | (#2214082)

WoohooO! Now, if they can only work out sub-queries, then I'd be 100% happy! Oh yeah...and get something like SQL*Loader cause I hate doing it the other way!

So? (0)

Anonymous Coward | more than 11 years ago | (#2214095)

Only a moron would use an open source database for an enterprise-wide application.

This would have been great, fifteen years ago. (2, Troll)

The_Messenger (110966) | more than 11 years ago | (#2214099)

Wow, now they only have to implement constraints, foreign keys, and transactions, and they'll almost be on the level of Postgresql.

Who knows, maybe MySQL will one day be considered a real database product.Until then, though, those of use doing Real Work will continue to use Oracle, DB2, and SQL Server. Of course, these databases already have professional GUI development tools, spatial data modeling, XML table translation, and tons of other fun toys, so the MySQL developers better get to work!

Honestly, besides cheapo webhosts and poorly designed weblogs [slashdot.org] , who uses MySQL?

DB2 rocks on GNU/Linux, by the way, and it's free as in beer. You should check it out.

Re:This would have been great, fifteen years ago. (1)

thunderbee (92099) | more than 11 years ago | (#2214108)

Well, Postgresql is OK - just lacking replication, but it's coming our way I think. It's the best alternative to closed-source DB IMHO.

Re:This would have been great, fifteen years ago. (2)

mgkimsal2 (200677) | more than 11 years ago | (#2214143)

it's free as in beer

Really? Can you point me to a place on IBM where I can get DB2 on Linux for free? There seems to be NOTHING about how to actually PURCHASE their DB2 (broken links or circular references).

Re:This would have been great, fifteen years ago. (1)

The_Messenger (110966) | more than 11 years ago | (#2214186)

The personal developer's version is free as in ber.
Here is the download page. [ibm.com] The full UDB apparently does cost money now, but in the past it was possible to run it for free (yes, legally!). I'm trying to find a link to corroborate this.

Re:This would have been great, fifteen years ago. (1)

The_Messenger (110966) | more than 11 years ago | (#2214200)

I can't find the link. Alright, I'm willing to accept that the free as in beer full DB2 UDB installation I'm running on my dev server was possibly some sort of promotional thing. My bad.

I'll have to download the personal version and see how limited it is.

Re:This would have been great, fifteen years ago. (1)

Drazi100 (458128) | more than 11 years ago | (#2214280)

well even so you can get it for $900. the unlimited processor liscence is now $3000. which makes it even cheaper than MSSQL. The only difference between that version and the enterprise is that it wont run on AIX or OS390( who cares unless you get a billion transactions per sec)

Yahoo! (0)

Anonymous Coward | more than 11 years ago | (#2214180)

We use it quite a lot with Perl. Yea, we use Oracle too but MySQL usage is growing by leaps and bounds.

Re:Yahoo! (1)

The_Messenger (110966) | more than 11 years ago | (#2214271)

You can't buy a professional RDBMS for FreeBSD, guys, so that doesn't count. :-) Don't get me wrong, I love FreeBSD, but it's rather difficult to get any work done on an OS with almost zero industry support.

I don't consider Yahoo! to be a very impressive service anyway. I know that the FreeBSD core team loves to brag about Yahoo!, but your search functionality is provided by third parties [google.com] running GNU/Linux. Sort of embarassing, eh?

Oh My God. This is GREAT News. (0)

Anonymous Coward | more than 11 years ago | (#2214101)

Yes, people, for the first time in three years I got a woody. This makes my dinky stand on end. Only Perl, combined with the greatness of MySQL, and then both being open source software, can turn me on enough to get a boner. Good job, boys and girls! Jolly good show!

I do hope this is the beginning and not the end... (2)

leereyno (32197) | more than 11 years ago | (#2214103)

Having stored proceedures in any language is better than not having them. The advantages of them are that they can be readily used by other programs, and they don't have to be compiled to be run, so they are faster. Since perl is an interpreted language I'd suspect that the latter benefit is lost. So what I'm hoping for is the future inclusion of stored proceedures written in SQL itself like what is offered in other DBMS systems.


Re:I do hope this is the beginning and not the end (1)

Schoos (4736) | more than 11 years ago | (#2214204)

and they don't have to be compiled to be run, so they are faster

Compiling makes code slower? wow. How fast must C-Code be if you interpret it.

So what I'm hoping for is the future inclusion of stored proceedures written in SQL itself like what is offered in other DBMS systems.

You can't implement stored procedures in SQL, at least not very effectivly, as SQL usually lacks condtions (ok, most SQL variants have at least something like that), and loops.

No, I think, Perl is a quite good idea, it's rather easy to learn (if you can code) and it has lots of features.

Is it offical (1)

fava (513118) | more than 11 years ago | (#2214131)

From mysql.com's todo page (http://www.mysql.com/development/todo.html) under the heading of "Things that have to be done sometime":
Stored procedures. This is currently not regarded to be very important as stored procedures are not very standardized yet. Another problem is that true stored procedures make it much harder for the optimizer and in many cases the result is slower than before We will, on the other hand, add a simple (atomic) update language that can be used to write loops and such in the MySQL server.
What are the chances that this patch might make it into an offical release of mySQL, I know that that they are reluctant to add features that are not created by themselves.

Yes I also know that I can/should recompile from source but most people will simply install the binaries.

Hmmm....perl....haven't we learned from Oracle? (4, Interesting)

mbpark (43131) | more than 11 years ago | (#2214161)

What I find extremely funny about this all is that Microsoft is doing the same thing in SQL Server 9.0, by putting the CLR in the SQL Server database. This way you can write your procedures for SQL Server in many languages, including Perl. OUCH. It causes more overhead than you realize to have an interpreter for more than SQL in the database.

I'm a DBA. I have seen the last few versions of Oracle with their Java Stored Procedure and SQLJ support, which is pretty bad. Oracle can't even get their PL/SQL running right between queries and views and stored procedures (the engine has not changed for PL/SQL since 7.3 in 8i, and 9i does not change it that much. Yes, they run 2 engines, one for SQL and one for PL/SQL. It makes Oracle perf tuning a complete nightmare). Yet they find it necessary to shoehorn a complete JVM in. No, no one really uses it, because it doesn't provide advantages.

It only makes the code completely unmaintainable since it's nothing more than code that calls the internal JDBC driver rather than an external one.

PL/SQL, T-SQL, and the other stored procedure languages at least are written in a superset of the main DML/DDL language. This allows you to use the same language optimizer, which reduces code size, and allows for code consistency across the entire project. In other words, all the queries, including parts of stored procedures, get the same optimization treatment.

Having ONE optimizer means that you can make it run really well, and share query plans and cached information. Pretty cool :).

The other important reason you have stored procedures is because if they are written in the main language, you can leverage the optimizer for query plans and caching of frequently-used or prototyped statements. That's part of the other reason for stored procedures. You can share queries and query prototypes with views and user queries, and have optmization that is better than what writing a procedure in X language can do.

Now we've got Microsoft coming in with their CLR, and mySQL using Perl. This is going to lead to even more unmaintainable code, because you're going to have people coding business logic that can be optimized in the DDL/DML language used in a higher-level language that cannot be.

Talk about a performance problem :).

From a language and optimization perspective, you always try and use a derivative of the main DML/DDL language of the database, so that you can use the same optimizer for making the statements run faster and perform well.

Anyone can write internal hooks to have a code interpretation engine in a SQL database. Oracle's been doing it for years, and so has Sybase. No one I know uses it because it doesn't provide the real advantages of stored subprograms in a database, which is to store frequently-used and prototyped query statements and aggregations in such a way so that they can be optimally retrieved versus just executed. When you add additional languages, you lose that. Oracle's Java Stored Procedures are nothing more than Java code that calls a different JDBC driver. I don't even want to think of what ADO.NET is going to do in SQL Server 9.

While this seems like a good idea, remember that it's been out for a few years in two other products, and is coming out for another. It's not as big a deal as real SQL stored procedures, because it's not as optimal as they are due to their loose coupling (which describes it perfectly IMHO), and can't share in the same optimization techniques as user SQL queries.

In other words, this isn't something to be too happy about, since it's something that people already have and don't use.

Re:Hmmm....perl....haven't we learned from Oracle? (0)

Anonymous Coward | more than 11 years ago | (#2214215)

Jesus Murphy.. It's a good thing I didn't read all that. It could've made me impotent! I'll just stick with Windows and SQL Server, thank you very much.

Re:Hmmm....perl....haven't we learned from Oracle? (1)

Drazi100 (458128) | more than 11 years ago | (#2214337)

thats fine .. but if any developer asks to put something else in the DB other than T-SQL especially (if yuo read the entire article) youre going to get a beating.

Re:Hmmm....perl....haven't we learned from Oracle? (2)

sheldon (2322) | more than 11 years ago | (#2214316)

Does mySQL even implement caching of query plans?

I can't find any information to suggest that it does, so maybe this doesn't really matter.

Re:Hmmm....perl....haven't we learned from Oracle? (1)

Justinian (125601) | more than 11 years ago | (#2214328)

Are you saying the CLR is interpreted? You might want to read up on its architecture if you think so. Then you would know that the IL is jitted into machine language as needed.

I do believe that when SQL server uses the CLR that T-SQL will become another CLR language, and it should be interesting to see how they handle data manipulation in other languages like c#, but using something like ado.net makes sense. Remeber that in SQL server 7 and above the engine itself uses OLEDB, so it wouldnt be that hard to imagine any CLR using that directly and effeciently, perhaps it might even be converted to use the SQL manage provider.

Its also not hard to imagine that the stored query plans will function like asp.net and acutally be compiled CLR code that was created by the optimizer using the codedom, or perhaps just emitted IL code. In any case I think we will see some very interesting stuff when MS starts using the CLR in SQL server.

Cool! (1)

Anomolous Cow Herd (457746) | more than 11 years ago | (#2214182)

Now I can edit GIFs and add GUIs to my database functions! This is great, now if only I can convince my boss that we need this. Oh, and also convince him to upgrade from the dual Xeon to the 8-way machine to handle all the overhead.

Sheesh, could they have made a more braindead move?

Why not stored procs in SQL? (1)

dave-fu (86011) | more than 11 years ago | (#2214208)

I'll probably get modded down for daring to ask this, but am I missing something here? Why stored procs in Perl and not in, say... SQL?
Or is being server-agnostic a Bad Thing now?

Mysql todo list (3)

Jeffrey Baker (6191) | more than 11 years ago | (#2214212)

Great! MySQL crosses off another thing on their list of things they need to do to catch up with PostgreSQL:
  • New logo (check)
  • Stored procedures in Perl (check)
  • Stored procedures in C, C++, Python, TCL
  • Langauge similar to PL/pgSQL
  • User-defined datatypes
  • Transactions
  • Subqueries
  • Constraints
  • Stop being a bunch of whining Euro sue-boys

Looks like it might be a while. Better just get PostgreSQL [postgresql.org] in the meantime.

Re:Mysql todo list (2)

gmhowell (26755) | more than 11 years ago | (#2214283)

You'll probably get flamed for mentioning the lawsuits, but lately, I've been thinking about switching to postgresql. Not only for subselects, but because there is a clear source for updates, upgrades, etc.

If you're running on ancient hardware, or running a huge database, perhaps the speed is important in MySQL. But for my needs (relatively modern hardware, and small datasets) why deal with it?

Re:Mysql todo list (1)

The_Messenger (110966) | more than 11 years ago | (#2214300)

Glad to see someone else on the side of Truth. Just because Slashdot uses MySQL doesn't mean it's not crap.

Does Anyone Still Use Berkeley DB? (0)

Anonymous Coward | more than 11 years ago | (#2214233)

Anyone? Is it still called Berkeley DB? Or is it now Sleepycat DB?

and why? (1)

orcldba (195785) | more than 11 years ago | (#2214299)

The biggest advantage of stored procedure is that it is compiled and can be executed immediately after load into memory and binding variables to it. You also can nicely pin stored procedures into a memory so it will not get booted out and it will perform pretty well.
Correct me if I am wrong, but perl is interpreted language. So what is the point? It will take exactly the same time to execute the thing as I would have the proc outside of the database...
Just my 2C (canadian)
Load More Comments
Slashdot Account

Need an Account?

Forgot your password?

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>
Sign up for Slashdot Newsletters
Create a Slashdot Account