MySQL Gets Perl Stored Procedures

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.

Comments

Better stored proc languages...

[Hagabard]

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

[Hagabard]

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.

Re:Better stored proc languages...

[Diamon]

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

[rjamestaylor]

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

[mal3]

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.

--Mal

Re:Better stored proc languages...

[horster]

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

[bzhou]

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.

Re:Better stored proc languages...

[ethereal]

They did it right, at version 7.0? Do you ever even listen to what you're saying? Taking until version 7.0 to get it right does not equal "rock solid".

Oh well, as long as they're not misplacing nuclear material any more.

Re:Better stored proc languages...

[The_Messenger]

I agree. We shouldn't use Linux, either, because it wasn't perfect at 1.0.

Fucktard.

Re:Better stored proc languages...

[ethereal]

As Microsoft has proved time and time again, it's always version 3.x that's the "getting it right" milestone. I'm just pointing out that you might want to be leery of a product that took twice as long to get stable, and that any claims that something is suddenly "rock solid" at version 7.0 might not be strictly truthful. If it took that long to get "rock solid", I imagine that it's not really.

FWIW, Linux 1.0 was a lot more functional than Microsoft anything 1.0.

Re:Better stored proc languages...

[rjamestaylor]

Why are you mad at me? Be mad at your clueless co-worker.

Re:Better stored proc languages...

[The_Messenger]

Thank you, citizen.

Re:Better stored proc languages...

[The_Messenger]

Linux ripped off the extremely well-known System V kernel. Kids write UNIX-like kernels in college classes. Linus really isn't anything special. It's not really surprising that Linux was functional at 1.0, because Linux ripped off 15 years of Bell Labs, CSRG, and others' research and work. Not to mention the fact that he already had a complete C library, compiler, and tools provided by the FSF.

Re:Better stored proc languages...

[ethereal]

Wow, I'm glad to hear that the only real problem Microsoft is having is a hemorrhage of version numbers :)

As far as nuclear material goes, I quote from the horse's mouth:

More sophisticated tests conducted by KI RRC did confirm that execution of ?SELECT? with ?ORDER BY? statements by SQL Server v.6.5 ?randomly? fails depending upon format of data used in both SELECT and WHERE parts of statements, number of records in resulting data sets, and type of hardware used - CPU frequency, RAM volume and type of HDD used. Minimal size of data set with detected problems in execution of such statements was 5 records. Maximal size of data set achieved in a course of tests for detection these problems was about 100 ths. records. This flaw was detected with the highest frequency on PC with CPU 100-200 Mh and 400-500 Mh with RAM 32-64 Mb.

So, please let me know how the customer's poor query design is causing random failures that vary based on the amount of data and even the hardware used? Microsoft even admitted that this was a bug in their software which they didn't know about until it was discovered in this manner. To somehow blame the victim for this problem is utterly amazing - you must work somewhere in Redmond.... The workaround mentioned was to reformat the data involved (a huge application rewrite), or to upgrade to SQL server 7.0 which had some security problems that were unknown to Microsoft until the Russians figured them out.

I believe the story is coming to the 'Happy End'. But I have no idea that 'Happy End' means except restoration of KI-MACS functionality and operating status we are doing. No additional discoveries of flaws and gaps in Microsoft SQL Server to be expected every day, can change the picture - selection of the Microsoft SQL Server as a backbone for computerized nuclear material accounting systems was a big error for both sides.

Re:Better stored proc languages...

[kc8apf]

If i'm not mistaken, Linux was actually based on the POSIX standard, not the System V kernel. System V has things that are not in POSIX. Because it was based on a standard, the actual implimentation of the kernel itself is unique.

You are likening WINE to be a OS rip-off of Windows since it uses the same API. WINE is far from an OS, even though it uses the same API.

Re:Better stored proc languages...

[mal3]

Technically version 7.0 is actually 1.0 it was rewritten from the ground up for version 7.0. In any event they skipped alot of numbers on the way to 7.0.

It's like saying they got Windows 2000 wrong 1999 times(sorry make that 2000 times).

--Mal

Re:Better stored proc languages...

[denshi]

Microsoft started MSSQL at 4.0, skipping the previous numbers to make the product *look* more mature. So, if the numbers are renormalized, MSSQL 7.0 is their 3.x effort. And yes, compared to their previous efforts, one might call it 'getting it right'.

Of course, that doesn't mean it doesn't totally suck ass compared to Oracle, DB2, Informix, or even *Postgres*.

Re:Better stored proc languages...

[Earlybird]

Microsoft didn't start at 1.0 because Sybase had already done that.

Until 4.2, Microsoft SQL Server and Sybase SQL Server were one. Sybase later morphed into the Advanced Server Enterprise thingy, which incidentally runs splendidly on Linux as an alternative to the open-source stuff; Sybase is a solid product. Anyway, Microsoft bought the rights and forked the code, and MS-SQL evolved independently from then on.

However, they remain highly compatible. The Sybase Transact-SQL flavour remains, as do most of the stored procedures, the client protocol (TDS), etc. Moving between the two implementations is simple, though I would personally never migrate to MS-SQL.

That's great.

[briggsb]

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

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

Rock on!

[xZAQx]

Bring on the GUI apps. Guess now I've got yet another reason to learn Perl

Java

[sean@thingsihate.org]

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

TEH SEANS@!!@!@

Re:Java

[pinkelefant]

are u crazy ?..java on oracle sucks ..
big mistake !

Re:Java

[The_Messenger]

We're supposed to take technical advice from someone who can't spell the word "you?"

Not a DB guru

[Sir_Real]

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?

Unenlightened

Re:Not a DB guru

[Overt Coward]

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

[drudd]

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.

Doug

Re:Not a DB guru

[msheppard]

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

[smack.addict]

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.

Re:Not a DB guru

[Diamon]

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

[ChristTrekker]

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.

Re:Not a DB guru

[bare_naked_linux]

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

[rjamestaylor]

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

[SpaceLifeForm]

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

[mal3]

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.

--Bruce

Re:Not a DB guru

[CrackElf]

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.

-CrackElf

Re:Not a DB guru

[MrBogus]

1) Stored Procedures should be considered a 'tier' in your application. They are the only real way to abstract your business logic away from your underlying database schema.

It's true you don't want business logic in your SQL as much as possible, but you also don't want a bunch of hardcoded SQL in your business logic if you can help it. Stored procedures help here, even at the cost of seriously reduced portability.

2) N-tier programming has the nasty habit of encourging very slow client-side joins and holding transaction locks over the wire. Especially if you get into (gack) EJB entity beans. If you can massivly reduce round-trips to the database by using stored procs, it's a clear win on performance.

Perl stored procedures seems to have it all back-assward. Rather than doing the join on the client side with your 'fast' Perl, they push it into the database, which gives you the abstraction, but doesn't help you an ounce on the speed bit. I suppose this is sorta like running an EJB container in your database, but I'm not completely familiar with how that works.

Re:Not a DB guru

[sro]

What you are referring to is actually not stored procedures per se. "Event handling" in RDBMS is based upon triggers and I suspect that is what you are talking about.

Last time I checked triggers were unsupported in MySQL but the trigger syntax has been supported for quite a while.

Although I really like MySQL for it's speed and simplicity I can not recommend it to anyone doing serious database programming as the lack of triggers and foreign keys is really holding it back.

Re:Not a DB guru

[bare_naked_linux]

I agree. In my original message, I pointed out that the client app could ask the database to execute the stored procedures (InsertPO(0,1,2,3)) rather than having the embeded SQL (INSERT PurchOrders VALUES (0,1,2,3)).

However, I think it's important to understand the way the original message read, you'd need triggers (he spoke of monitoring for a delete on a table) and MySQL doesn't have them.

Re:Not a DB guru

[drudd]

You are correct... the primary uses for stored procedures are triggers, which is what I described, however they can be useful independent of triggers...

Either way, a big advantage of stored procedures is avoiding communication with a client...

Doug

Re:Not a DB guru

[aiken_d]

Yes, they're faster. For several reasons:

- You're not shipping big queries over to the databnase. Some latency saved.

- SP's are compiled once they've been run once, so you're not re-interpreting text SQL queries into DB commands.

- Depending on the database and the type of SP, you may or may not be able to re-use query plans. If so, you save the optimization time. Even if not, you get the benefits of reducing network and interpretation overhead.

What I want to know is if the mySQL SP implementation actually compiles the SP's, or if it has to reinterpret them every time.

-b

Re:Not a DB guru

[xwred1]

Stored procedures reside on the server, so they can be executed locally on the SQL server, rather than over the network from another server (if thats how you have things arranged).

You can also tie stored procedures in with normal SQL queries, which makes them feel alot cleaner to use.

Re:Not a DB guru

[barjam]

Here are some advantages to Stored Procedures:

1. Slower than direct queries (Oracle/jdbc). (Time it yourself if you don't believe me.... jdbc call to a stored procedure vs jdbc call to same type of query on oracle).

2. Force your database folks to become application programmers. PSQL is a really, really poor choice of a language to program application logic in.

3. Vendor lock in.

4. Did I mention vendor lock in?

In all the applications I have been a part of, we keep the ERD pretty much the same as the OOD. This keeps your data dictionary clean... Keeps everyone on the same page.

It has been my experience that stored procedures are a great way for your DBA to hide the fact that he can't coherently design the datamodel. Lets face it, most dba's don't really know much about data modeling or programming, they mostly know about engine administration and that type of thing.

You mention EJBs (gack?) in a negative light... please note that decent appservers can cache these Ejbs in memory so that many queries don't even hit the database.

I say keep the buis logic where it supposed to go... not in the database.

Re:Not a DB guru

[MrBogus]

In all the applications I have been a part of, we keep the ERD pretty much the same as the OOD.

This has been a problem with some of applications I've worked on. Suffice it to say that there has good reasons to let these diverge, and SPs can be used to hold it together, although you could use another application server 'tier' to do this.

your DBA to hide the fact that he can't coherently design the datamodel

Well, IMO, the DBA shouldn't be designing the data model or writing the stored procs and should stick to DBA things. Any project I've been involved in where there's a special 'database guy' has been doomed from the get-go as he basically has to do all of the application analysis *again* between worrying about backup schedules and managing database devices.

please note that decent appservers can cache these Ejbs in memory so that many queries don't even hit the database

It's true that you can 'disconnect' entity beans from the DB, but if you don't, you are holding a transaction lock over the wire. Plus EJBs are really bad for set processing (something that SQL is really good at). The idea of entity beans seems to be that the Java programmer can worry about java and not have to understand the database interactions. This is only true to the extent you are willing to suffer on performance.

Re:Not a DB guru

[SuiteSisterMary]

Nope. Use stored proceedures so that you don't have to do this.

Then, your web interface, your VB desktop app, your mainframe batch job, everything all calls the same stored procedure interface. The only time you have to touch code on any of these is if the inputs or outputs to said stored procedure change. Otherwise, database schema changes, table changes, none of it even remotely affects any methods you have to interface with the database.

Call me a troll....

[BillyGoatThree]

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

Re:Call me a troll....

[paulydavis]

YOUR A TROLL

ok... happy now.

Yes and yes

[wiredog]

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

[spudnic]

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

[dameatrius]

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)

Hmm...Not the only problem is..

[Si]

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

Welcome, lame reader...

[bartwol]

...you too are among us, the trash.

<bart

Subselects?

[hetfield]

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?

[baptiste]

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

[dorkstar]

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

Re:Subselects?

[curmudgeon42]

Oh, I've been wishing for subselects in MySQL for so long. I don't think it'll ever happen. Why couldn't they have worked on those before SP; subselects seem much more useful. You can always find ways around using SP, but sometimes you *need* subselects, or you have to completely rethink the way you are doing things.

Re:Subselects?

[Anthony Boyd]

Huh? Yes it does. How old is your information?

Re: Subselects?

[3247]

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

Actually, no: There are many situaions where doing a subselect first and then only select a few tuples from the second table is much more efficient then doing a join and selecting a few joined tuples.

On the other hand, finding a mathematically equivalent request that is more efficient is the task of the RDMS, not the database user.

But wait...

[don_carnage]

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!

Re:But wait...

[krow]

You know, most of the time someone using a subselect just lacks the imagination needed to do a join :)

On a serious note, you will find that most of the time a subselect ends up being your worst nightmare for performance reasons. Its normally better to find a way around them.

Re:But wait...

[don_carnage]

Yes, but it's a nightmare trying to perform an extended update without subselects. I usually end up just writing a small Perl proggy to do it with DBI.

Re:But wait...

[bwt]

You know, most of the time someone using a subselect just lacks the imagination needed to do a join :)

While some people will always do brain dead things, there are definitely many queries that you simply cannot write unless you do subqueries.

Consider something as simple as finding all students whose IQ is above average.

Re:But wait...

[krow]

Oh very true. Sometimes you can't get around using subselects (there are a couple of places in Slashdot that are exactly in this situation).

There are other things I would rather see though. For instance in Oracle you can represent Graph structures in the databases (quite cool). I would love to have this for comment storage. Right now it is quite a pain in the ass to generate the comment pages in threaded/nested mode.

Re:But wait...

[bwt]

I assume by storing graphs, you are refering to oracle's "connect by prior ... start with" syntax -- which definitely rocks. It should be added to the SQL standard.

In-line views (from clause subqueries) are very useful, and starting in one of the 8i versions, you can even do subqueries in the select clause, which is sort of an inline-function. The analytic SQL functions are pretty cool, too, but Codd is probably rolling over in his grave about them.

The best new oracle SQL functionality though, is unquestionably materialized view SQL rewriting, which is sort of like using a view as an index - it stores the results and uses them transparently to rewrite SQL plans that would recalculate things. The affect on things like simple slice-and-dice tallies is just incredible.

This would have been great, fifteen years ago.

[The_Messenger]

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

[thunderbee]

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.

[mgkimsal2]

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.

[The_Messenger]

The personal developer's version is free as in ber.
Here is the download page. 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.

[The_Messenger]

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.

[Drazi100]

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)

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

[sszabo]

Come on... both programs are good for what they do, and it's not like Postgres doesn't have its own problems (Foreign keys still somewhat broken in some cases, Unique constraint that doesn't follow spec, ...) I really don't understand all of the infighting on places like slashdot.

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

[krow]

Actually, Slashdot's user tables are innodb (the transaction tables). So is the stories table. So far innodb has turned out to work really well. We have always had problems with the SELECT/INSERT penaltly and Innodb has solved that for us.

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

[The_Messenger]

I was unfamiliar with this product, and it looks really cool. Thanks for the heads up, I'm glad to see that Slashdot is open to better ways of getting the job done.

But I'm curious, has the team ever seriously considered something like DB2? The current Slashcode wouldn't take very good advantage of it, but if the code is modularized in the future, an awful lot of logic could be moved into the database. The advantage? Insane speed, security, and scalabilty.

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

[krow]

Actually the code for the DB is very modularized. You just have to exchange one library and you can use a different databases. I have tinkered with this for PostgreSQL and Oracle but never for any other DB (while I have experience with Sybase and Informix, I have never used DB2).

I have had people from IBM approach me about doing a DB2 port, but no one has every offered any code and I have enough to do as is.

One of these days though I would love to setup slash on an IBM mainframe and actually benchmark it. I suspect it would run quite well (even with MySQL).

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

[Reality Master 101]

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

Have they implemented autoincrement columns or sequences yet? That's by FAR the most detestable thing about DB/2, and in fact, is one reason I generally recommend against it.

I do hope this is the beginning and not the end...

[leereyno]

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.

Lee

Is it offical

[fava]

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.

Re:Is it offical

[fava]

I am replying to myself but according to www.mysql.com/doc/M/y/MySQL-PostgreSQL_features.ht ml stored procedures in multiple languages are due in version 4.1. Version 4.0 is supposed to be released next month according to the mailing list.

Also due are subselects (4.1), triggers (4.1) and foreign keys(4.0 and 4.1).

MySQL found dead

[Anonymous Coward]

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.

MySQL Touched My Winkie!

[Anonymous Coward]

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!

[Drazi100]

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

Hmmm....perl....haven't we learned from Oracle?

[mbpark]

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?

[sheldon]

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?

[Justinian]

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.

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

[Drazi100]

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.

Why not

[Camel Pilot]

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

[dameatrius]

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.

No! and no!

[bartwol]

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.

<bart

Re:WTF?

[dameatrius]

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.

Cool!

[Anomolous Cow Herd]

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?

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

[Schoos]

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.

Re:Lame

[Drazi100]

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.

Why not stored procs in SQL?

[dave-fu]

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?

Re:Why not stored procs in SQL?

[Anonymous Coward]

Because SQL isn't a programming language and lacks even the basic conditional and loop control structures.

Re:Why not stored procs in SQL?

[SuiteSisterMary]

Stored proceedures generally need things like variables, control statements, loop capability, stuff like that. SQL doesn't have those.

You'll notice, however, that most other DBs implement sql-like extentions that make sense in their own context; Oracle has 'Procedural Language SQL', SQL Server has 'Transact-SQL' and so on.

I haven't looked at it at all, but I get the impression is that what these guys have done is given you the ability to point to PERL scripts and say 'run that when I tell you to' which is NOT stored procedure capability, but close.

Mysql todo list

[Jeffrey Baker]

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 in the meantime.

Re:Mysql todo list

[Jeffrey Baker]

Wait, I forgot sequences.

Re:Mysql todo list

[gmhowell]

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

[The_Messenger]

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

Re:Mysql todo list

[The_Messenger]

Don't worry, so did the MySQL developers. :-)

Re:Mysql todo list

[gol64738]

jeeez, you are missing the entire point of mysql! mysql was designed for smaller databases, to be lightning fast. add all those true RDBMS features and mysql will just be another hog like the rest of them.

look, the sites i do are not ebay.com or even yahoo.com. i don't need all that piggy overhead.

transactions? transactions are needed for two reasons:

1. you're doing a bank app

2. you're a lousy programmer and need transactions so you don't mess the database up.

Re:Mysql todo list

[The_Messenger]

Actually, the lack of subselects is just a symptom of a larger problem: MySQL doesn't support SQL92. For all their bitching about stored procedures standards (the old excuse was that because Oracle uses PL/SQL, MS uses T/SQL, et cetera, there is no clear standard), it's funny how they can't implement existing standards. Just like Mozilla and XHTML/CSS2. As I'm so fond of saying, someone should open-source a book on project managment and help these poor sods out.

Re:Mysql todo list

[Jeffrey Baker]

I'm used to meditate on how this attitude came to be, but now I believe simply that most MySQL users are natural idoits. MySQL is shit slow. Insert block selects. Even selects can block selects. MySQL lack concurrency in a big way. In PostgreSQL, there is hardly ever any kind of blocking at all. Selects don't block inserts nor the other way about. Certainly no select can block another select.

PostgreSQL is way faster than MySQL in everything besides some contrived read-only benchmarks. In actual benchmarks and the real world where the rest of us live, PostgreSQL's performance shines above MySQL's in every way.

Re:Mysql todo list

[fava]

Does anyone actually support all of the SQL specification?

Re:Mysql todo list

[The_Messenger]

HAHAHAHAHAHA!!!! Holy fuckng shit, I'm about to piss myself. That's classic, man. Yeah, transactions and constraints are only needed by inferior programmers! LOL... my guess is that you've never programmed anything more than a GeoCities guestbook. Come back to the discussion once you've some skill and experience.

Re:Mysql todo list

[The_Messenger]

Perhaps I was too harsh. Now I see that you're just incredibly stupid and/or inexperienced, and are in need of education.

When you're programming a real application, not the GeoCities guestbook that no doubt is your total experience, you may be called upon to perform fifty consecutive database operations. Say that something goes wrong, and the database throws an error on the 48th. Transactions allow you to roll back all 47 previous changes in one step.

When you write a large application, you cannot possibly account for every possible database error. Data integrity is maintained using constraints. Transactions enable cleaner (duh), more elegant (maybe not to a newbie like you, but yes,), faster code (your application can't possibly implement a rollback mechanism that is as fast as the database's -- it's a database, that's what it does, or in the case of MySQL, what it SHOULD do).

I'm sorry that they didn't cover this in your highschool web design class. Perhaps once you have some real work under your belt, you'll know better. But you really do sound dumb... you aren't mentally retarded, are you? I don't mean to be insulting, I really want to know! I mean, it's not your fault, it's probably genetic.

Now stop your pathetic attempts to flame real developers and go back to banging your head against the wall and drooling.

Thanks for posting, fucko. Your parents were right to give you your own Internet connection.

Re:Mysql todo list

[The_Messenger]

What Oracle says:

ANSI and ISO Compliance

Oracle8 conforms to Entry level conformance defined in the ANSI document, X3.135-1992, "Database Language SQL."

The ANSI and ISO SQL standards require conformance claims to state the type of conformance and the implemented facilities. The Oracle8 server, the Oracle Precompiler for Fortran Version 1.8.25, Oracle Precompilers for C/C++ Version 8.0.4, Oracle Precompiler for Cobol Version 8.0.4, and SQL*Module for ADA Version 8.0.4 provide conformance with the ANSI X3.135-1992/ISO 9075-1992 standard:

Compliance at Entry Level (including both SQL-DDL and SQL-DML)
Module Language for ADA
Embedded SQL C
Embedded SQL COBOL
Embedded SQL FORTRAN

FIPS Compliance
Oracle complies completely with FIPS PUB 127-2 for Entry SQL. In addition, the following information is provided for Section 16, "Special Procurement Considerations."

IBM, maker of DB2, says:

Standards compliance
DCE Security services
SNMP agent
X/Open XA
X/Open CLI
ODBC Level 3
JDBC Level 1.20
JDK Level 1.1.x
ANSI SQL92 Entry
SQL3 features
FIPS 127.2

Re:Mysql todo list

[eh2o]

AND...

Automatic join optimization.

(the biggest reason no dba in their right mind will ever use mysql for a complex, normalized schema).

Re:Mysql todo list

[SuiteSisterMary]

If all your after is speed, then append to a text file. Appending and grepping your results is a hell of a lot faster than MySQL.

Re:Mysql todo list

[nagora]

Yep, PostgreSQL only needs to tick off one thing to catch up with mySQL:

Usable.

Re:Mysql todo list

[decefett]

+2 Funny. where's a mod point :)

Re:Mysql todo list

[gol64738]

yes, you're right. my applications don't need 50 database transactions within a single function. now that i think of it, i've never heard of an application that uses 50 transactions inside a single function.

so, on paper, transactions look attractive.
but like my original post declared, it's not needed unless you're doing a 100 million dollar ebay2.com application (or like a bank app, etc) in which you won't be using mysql at all.

Re:Mysql todo list

[gol64738]

2) you are a lousy programmer who doesn't understand how his code interacts with the database.

if that were true, then transactions would be a god send to me. but because it is not true, i don't see the value of tranaction support.

hmm, maybe your statement is true of yourself, which is why in your case transactions are not helpful, but absolutely required.

I would also like ...

[Da VinMan]

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

[NineNine]

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.

Re:I would also like ...

[ethereal]

I wouldn't bitch about performance too much - if that's all you want, just ditch SQL entirely and use an embedded DB like Berkeley. Truth is, features/performance is a sliding scale, not a binary option. For some applications, being able to use stored procedures in a few different languages might be very helpful. For other applications, an SQL parser itself is unacceptable overhead.

Re:I would also like ...

[Dasein]

A vast majority of the performance gains to be had from stored procedure programming comes from two sources -- precompilation and elimination of network round trips.

Neither of these options are precluded by a correct abstract interface.

There's no doubt that such an abstract interface would hurt performance, but I would venture to say that you would give back much less than 1% of the stored procedure benefits by doing this. You can do a large number of JNI-Like calls in the ~8ms required to complete a network round trip. Add compilation and query optimization and you have a large number Vs. a very small number.

To be able to provide a migration path for both sers of MS SQL Server/Sybase, Oracle, and DB/2 seems compelling even though and such migration path is likely to be an 80/20 proposition.

Not a bad idea, in my view.

Re:I would also like ...

[Malcontent]

" 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?"

Postgresql does this. You can use python, perl, TCL, and PL/PGSQL.

They are debating loading up Java but there seems to be some resistance from the hackers.

Re:Yahoo!

[The_Messenger]

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 running GNU/Linux. Sort of embarassing, eh?

and why?

[orcldba]

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)

Oh come on...

[Da VinMan]

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.

the truth is near...

[gol64738]

more people are beginning to realize that between python and php, perl is useless. not to mention the fact that the code syntax was stupid to begin with.

sorry perl, you had your day, but you are now outclassed.

makefile

[kootch]

so uh, anyone mind writing a makefile for this?

i love brian's comment in the "install" file tho. yea...you just need to re-configure perl, and if it's done wrong it will core on you...

From stories past... Slashdot funds MySQL?

[The_Messenger]

I was rereading an old story from when Slashdot moved to Exodus, and Malda has this to say:

Fault tolerance was a big issue. We've started by load balancing anything that could easily be balanced, but balancing MySQL is harder. We're funding development efforts with the MySQL team to add database replication and rollback capabilities to MySQL (these improvements will of course be rolled into the normal MySQL release as well).

I'm curious, Taco, what happened with this? It was before VA Itsux bought Bendover, so maybe the funding got nixed then? Or did they spend all of the money on designing the new logo?

Re:From stories past... Slashdot funds MySQL?

[krow]

I am gone to Linux World Expo, when I get back the failover should be in place. I have been testing the new replication stuff and it seems to be working fine for me (this is for Slashdot).

Re:From stories past... Slashdot funds MySQL?

[s2r]

But you still have to programm you app. to switch between DB servers.

Re:From stories past... Slashdot funds MySQL?

[krow]

Right, which is more then a little tricky since MySQL lacks two phase commit support. Its still going to require a human to do the work. But it will be possible at least. We have backups but no way to switch to those on the fly.

I added the static page option about half a year ago so that folks can at least see the front page when we loose the DB.

The crowd may not like this, but it's true

[DaveWood]

PERL is not a good language, and probably an especially bad choice for a stored procedure langauge.

The syntax is a mess, and like many basic-esque languages it's very easy to get into namespace trouble. There are gotchas with strings and escape sequences. Memory is managed with a reference counting garbage collector, which means circular dependencies will create memory leaks; this isn't as serious with kludge maintenance and CGI scripts, but on a database it will be of signal concern. What passes for a language API is what I would call deliberately obscure (lots of one and two letter functions, a million operators, &c &c). On the whole, it's a complete horror show, and just as with Win32, I'm continually amazed at how many things get written against it.

I say stop the cycle of abuse. There are over a dozen free languages that would have been a 100% better choice.

Re:The crowd may not like this, but it's true

[dmelomed]

Yes, Python is by far a better language. Perl is much easier to write in than to understand written code. Maintainability just sucks.

Re:The crowd may not like this, but it's true

[CaptIronfist]

I don't agree with you at all. PERL is probably the most flexible language around. PERL has functional programming capacities, oriented object capacities and procedural ones as well. In two words it does the freakin dishes ...

On the whole, it's a complete horror show

I bet you xxxxxxxxxx$ you never looked at it seriously, so stop bitchin' and start learning it'll make your life a lot easier. Obscure? When is the last time you wrote a C/C++ lump of code?

I don't know about you, but i prefer writing 10 lines of PERL than a 100 lines of Java that does exactly the same thing.

I'm going to stop now, it'll start to look like a flame, sorry.

Re:The crowd may not like this, but it's true

[Christianfreak]

Maintainability just sucks.

No. Programmer's who can't/won't write commented, readable code suck. Last time I checked you can make any language look like a mess.

Re:The crowd may not like this, but it's true

[apirkle]

This from a recent interview with Tom Phoenix and Randal Schwartz, authors of Learning Perl:

I often tell my students that Python and Perl are about equally capable, but philosophically incompatible: Perl is jam-packed with shortcuts, while Python has essentially none. That makes Python easier to learn, in general; Perl students have to learn many of its common defaults before they can follow along in someone else's program. But Perl is easier to use, even at the expense of being harder to learn. That's a good trade-off, since you learn it only once, then use it again and again.

Perl is an incredibly flexible and useful language, and the forthcoming Perl 6 will only continue to improve upon this.

Don't knock it until you've used it. Really used it.

Re:The crowd may not like this, but it's true

[barjam]

Well...

Thats true to a point.

We have a small group at work (5 developers) working on a distributed java/ejb application. No one comments the code because it simple doesn't need it. Anyone with even minor programming experience could come maintain this stuff.... and we are even doing some fairly complicated image manipulation.... If the code itself isn't self documenting you are doing it wrong.

Some languages lend themselves to readability... perl is not one of those languages.

Re:The crowd may not like this, but it's true

[barjam]

I don't know about you, but i prefer writing 10 lines of PERL than a 100 lines of Java that does exactly the same thing.

do you have an example?

Re:The crowd may not like this, but it's true

[c13v3rm0nk3y]

Boy, are you going to get flamed.

I've had a love-hate thing with Perl since I first saw it. I appreciate it's power, but scratch my head over some of the design choices. And the syntax! Inscrutable.

I do marvel at it's versatility, however. Perl. Is there anything it can't do? That being said, it's just too big for most of the work I do. If I need to hack a script together, I just reach for the Kornshell.

I've used Perl to prototype, and at that it excels, but the maintenance required for anything else hasn't given me the warm fuzzies. You have to be a bit of a wizard to showcase the elegance buried in Perl, and I just can't take the time. I'm sponsored by my company to increase my Java and C kung-fu, and <code>use Perl;</code> is just not part of our culture.

Anyway, to actually finish on topic, the db-powered app my company uses had ruled out mySQL, even on Linux, for lack of store procedures. The introduction of Perl is not going to change this significantly. Compared with Informix, mySQL rules, but it's not corporate-ready, at least for this corp.

Re:The crowd may not like this, but it's true

[VB]

And, some languages defy any attempt to not look like a mess, regardless of commenting:

Private Sub Combo91336_AfterUpdate()
' Find the record that matches the control.

Me.RecordsetClone.FindFirst "[IndexField] = " & Me.Combo91336.Text
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

Wait; sorry that's not a language. >:)

Re:The crowd may not like this, but it's true

[EvlG]

This post hints at the real cause of the "Perl is unmaintainable" mentality that pervades the community with the statement that begins "I've used Perl to prototype..."

Therein lies the problem. Perl really helps the programmer get something done quick. Much moreso than C/C++, and somewhat moreso than Java. However, that speed always comes at a price. Its a lot harder to read code that isn't commented, and that is chock full of clever tricks gleaned from reading comp.lang.perl. The same would certainly be said of C/C++ coders using lots of shortcuts from books and FAQs, and possibly of Java as well. Arguably, Java does enjoy the advantage of verbose method and class naming. This does help make the code a little easier to read, but this isn't something exclusive to Java. Any good programmer should name things properly. Besides, some argue that Java can be too verbose (like Objective C is IMO) and that makes it just as hard to read as if it were too concise.

The real solution is something that is taught in Software Engineering courses, but often ignored: THROW AWAY THE PROTOTYPE. A prototype is meant to be a quick and dirty proof-of-concept, something to give everyone an idea of what the tool is supposed to do. You will run into problems when you extend a prototype into production, whether you are talking Perl, Java, C/C++, or any other language. Prototypes are developed quickly and without much planning. It is this lack of planning that is at the root of most maintenance problems.

Well-written Perl with helpful comments and a good use of whitespace is easy for experienced Perl programmers to read. That same can be said of well-written Java and C/C++. Some even say the expressive nature of Perl can make the code easier to read, and I'd agree. However, I don't believe Perl is inherently unmaintainable. I think that myth is an unfortunate side-effect of Perl's ease of use. You can develop a solution very quickly in Perl, and it will work. But it is unreasonable to expect a maintainable solution to come out of any significant programming project that lacked planning and consideration. Anyone who does prototyping in C/C++ and Java would recognize that it wont be maintainable for production use. Too many simple assumptions are made, and the implementation is often tied too closely to the interface. This is a problem with prototypes in ANY language. Why do some people expect Perl to be different, just because it allows the developer to prototype faster?

Thus, while it may be true that Perl code is often unmaintainable, I don't put the blame on Perl. I place the fault squarely on the shoulders of those who mis-use their prototypes, and don't want to pay the price later.

Re:The crowd may not like this, but it's true

[Camel Pilot]

Ten-to-One my be a bit of hyperbole but the previous poster was probably referring to
was the use of a large number of easy-to-use Perl modules such as CGI.pm, DBI.pm, LWP.pm, etc. results in significant code reuse and reduction in custom code.

However just for fun lets have a look at the venerable Hello world program.

Java:
//- - - - - -
// Must be in a file "hello.java"
public class hello {
static public void main(String[] argv) {
System.out.println("Hello world!");
}
}
//- - - - - -

Perl:
#- - - - - -
print "Hello World\n"
#- - - - - -

Or how about a Servlet/CGI

Java:
// - - - - -
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class HelloWorld extends HttpServlet
{
public void doGet (HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
PrintWriter out;
String text = "Hello World in Java (Servlet)";

response.setContentType("text/html");
out = response.getWriter();

out.println("<html><head><title>");
out.println(text);
out.println("</title></head><body>");
out.println(text);
out.println("</body></html>");

out.close();
}
}
// - - - - -

Perl:
# - - - - - -
$text = "Hello World in Perl CGI";
print "Content-Type: Text/HTML\n\n";
print <<END;

<html><head><title> $text </title></head>
<body>
$text
</body></html>

END
# - - - - - -

Mind you in Perl I would never use HTML directly in the code but use templates, and am sure the same can be said about Java. The intent was to show for better or worse Java is very much more wordy then Perl.

Re:The crowd may not like this, but it's true

[MikeBabcock]

Protoyping & Applications: Python
Text-processing tools: PERL
Prototype that should've been thrown out: BIND.

Re:The crowd may not like this, but it's true

[DaveWood]

I have. I am.

Actually

[Micah]

I've looked through the Slash 2 source code and poked around it some and plan to write some plugins. They didn't do a bad job at all. Sure, Python would probably be a better choice, but Slash is better than 85% of Perl code out there.

Re:Actually

[smallpaul]

Slash is better than 85% of Perl code out there.

Damned by faint praise. :-)

FYI - is this what you're talking about?

[spruce]

Maybe you're talking about a different implementation of looping through a rowset, but you can do this...

DECLARE csrName CURSOR FOR SELECT * FROM #table1

OPEN csrName

FETCH NEXT FROM csrName INTO @Field

WHILE @@FETCH_STATUS = 0
BEGIN
--do something
PRINT @Field
FETCH NEXT FROM csrName INTO @Field
END

DEALLOCATE csrName

Re:FYI - is this what you're talking about?

[mal3]

You can do this, but for sanitys sake DON'T. Cursors are the slowest most miserable performing things in MSSQL.

--Mal

Re:FYI - is this what you're talking about?

[denshi]

Which is a shame considering cursors are high performance basic components in Oracle and Postgres.

Re:Perl Yuck!

[trilucid]

Now, I know I'm responding to an AC post, which is admittedly not terribly bright since it's obviously a troll anyhow, but I feel I need to pop my 2 cents in here...

Back at my old "full time" job, we used MS SQL databases in production at a Windows NT-based NOC. Incidentally, this was for the BOA telephone banking system itself (yep, I was a coder on that team, it's mostly written in VB6). Anyhow, I used mySQL for personal purposes, and eventually got around to (of course) reproducing some of the system logic in C/Perl and mySQL. Guess what? On limited stress tests, my system held up fine. Of course, I wasn't about to try to evangelize my job away, but the point remains valid that it could have worked in production.

Fast forward to the present, where I run a small web hosting and web application design company (see sig fo mo). All our clients use mySQL and Perl for their solutions, most of which are quite robust and flexible. Not to mention fast; we run a pure Linux shop for that stuff and haven't had any competition anxiety with Windows shops.

The progess with things like stored procedures in mySQL and such may have been a bit slow, but it's getting there. Hey, last I checked /. ran on mySQL, not to mention the ever-popular time waster HotOrNot.com. Just my perspective, of course...

Cram your Mod points...

[bwoodring]

> Why in the HELL would you want another layer in there? For flexibility?

You don't know the first damn thing about database programming, do you? The stored procedure code isn't re-parsed every time it's run. The execution plan for the query is cached and *that* is run. The performance hit would only be seen the first time the SP was run, when the recompile occurs. Having multiple SP languages would be a very good thing.

> You need performance, period.

No, not really, it is that kind of attitude that got MySQL into the position it's in today, everyone acknowledges it's fast, but nobody has any respect for it as a real database.

> If they actually wanted MySQL to be used by people who knew what they were doing, they would've integrated in PL/SQL.

No no no, damnit. We need to get past these shitty Procedural SQL hacks. T/SQL and PL/SQL are crap, Why do you think Oracle is integrating Java and Microsoft is integrating ActiveX into the database engine? Because trying to do high-level programming in SQL is complete shit. Why would MySQL want to integrate a legacy language like PL/SQL?

Re:Cram your Mod points...

[mal3]

No no no, damnit. We need to get past these shitty Procedural SQL hacks. T/SQL and PL/SQL are crap, Why do you think Oracle is integrating Java and Microsoft is integrating ActiveX into the database engine? Because trying to do high-level programming in SQL is complete shit. Why would MySQL want to integrate a legacy language like PL/SQL?

I agree we need to get past the procedural hacks, hell we need to get away from procedural programming at the database layer in the firstplace.

--Mal

One other reason

[Dalroth]

A lot of you people are forgetting two other critical reasons why stored procedures are good.

1. Most database pre-parse the stored procedures and keep the cached parsed information in memory. Really complex SQL queries can take a significant amount of time to parse, and cutting down on that overhead can be a huge win for some applications (it was for one of our queries!).

2. Stored procedures can encapsulte logic that requires multiple SQL queries into one call. This saves the network overhead of making multiple trips to the database, which can potentially be huge (and even be REALLY huge if you open up a new connection for each SQL query and then shut the connection immediately).

I don't know if the Perl procedures remain parsed, but at the very least they should be able to accomplish #2. Personally though, I'm going to wait till mySQL supports some sort of Transact SQL like stored procedures. I don't see a justifiable reason for the overhead involved in running Perl on my database. That just strikes me as a bad idea (same goes for java).

PostgreSQL USED to be slow!

[Anonymvs Cowardvs]

In my experience, PostgreSQL solved the performance problems with version 7 -- the "Postgres is slow" meme sticks around from PostgreSQL 6, which was slow.

(Which is interesting, because then, MySQL had a feature disadvantage and PostgreSQL a speed disadvantage, and it seems likely that the speed disadvantage was the easy one to remedy.)

Re:Yahoo!

[The_Messenger]

Hell yeah, we wouldn't want to trust valuable content like the Ate My Balls directory to anything but MySQL, right? When you're looking for premium Ate My Balls websites, you don't want transactions, data integrity, or a standards-compliant query language slowing you down! Yahoo! truly is a world-class portal.

There are very few cases for stored procedures

[01010101]

Relational databases are slow and unscalable.
Can I add another box and double my query or
insert capability? No.

But, I can add another app server and theoretically double my ability to execute code. People who design enterprise applications to use stored procedures are giving up their ability to scale. The exception to this rule is when stored procedures perform aggregations, and time series analysis. However, I will go so far as to say that using a relational database to perform ad-hoc analysis is a stupid thing to do and a billion dollar industry has grown up around solving this problem in a better way (OLAP).

ALL enterprise applications I've seen that use stored procedures were failures because they could not scale. They had to be rewritten and redesigned from scratch.

As other posters have said, this is not much to be happy about.

Re:There are very few cases for stored procedures

[The_Messenger]

You should talk to my boss. He'd sure love to know that one of our $10 million/year products, which makes widespread use of stored procedures, isn't scalable. Especially since it's experienced a 700% increase in users over five years, and has scaled from an 200MHz Pentium box from an $800k RS/6000 farm running clustered Oracle 8i. It must be caught in Jobs' reality distortion field, right?

Come on, admit that the real reason that you don't like stored procedures: you're in the MySQL cult, and can't admit that MySQL is deficient.

Re:There are very few cases for stored procedures

[beanerspace]

Certainly, there are limitations, but to say there are very few cases ignores situations where programmers are migrating from thick to thin clients, using the same query between various languages and is unsure or not satisfied with client-side performance.

Just a very quick example. Let's say you wrote a database application using, oh, I dunno, Sybase and Powerbuider about 4 years ago. Now the client wants everything web-based. Guess what ? Your stored procedures work just fine in the JDBC and the PERL DBI. Problem is, all your code is in client-side/inline sql.

Re:There are very few cases for stored procedures

[Drazi100]

yeah lets all use OLAP... how do your an adhoc query from a command line again? oh yeah u can only use proprietary microsoft API's and no SQL statements.

wonder why their are all those pesky unscalabe huge relational data warehouses behind OLAP tools.

Re:There are very few cases for stored procedures

[01010101]

Mph:-) That's so funny it deserves a response:-)

I agree that it's deficient. Though I like MySQL I can not use it, nor could I recommend it (yet) for my employer's enterprise nation-wide and international fortune clients.

About your earlier comment, it would seem you have simply affirmed my point. If you had to grow from a 200MHz Pentium to an 800K RS/6000 farm just to get a 700% increase you have fallen into the scalability trap I was trying to explain: given your current scalability/cost ramp (which isn't linear) most applications (yours may not) on such a ramp will soon not be able to scale no matter how much money you pump into it.

Re:There are very few cases for stored procedures

[01010101]

I ignored the case you mentioned and I should have stated it.

However, if an app is now moving web based and the user base grows exponentially a stored-procedure based system will not be able to scale to handle the growth.

Something else I didn't mention (out of lots of other reasons) is that a corporation wishing to reuse its business logic should not be placing its business logic in stored procedures which can't be scaled and can be very hard to maintain if you use more than one database vendor.

Web Services are a great solution and in most cases should be used instead of stored procedures because web services use http (everything supports this), and have bindings for most languages.

Note that Linux web services using Tux are several times faster than JDBC/ODBC/Java RMI/CORBA and even quite a bit faster (and less CPU intensive) than rolling your own sockets based protocol.

PostgreSQL

[howardjp]

PostgreSQL has had Perl, Tcl/Tk, C, and hooks for other languages for some time now.

Oracle Perl Stored Procedures?

[Craig Maloney]

I wonder if Oracle will allow Perl Stored Procedures. I despised PL/SQL when I had to use it, since I could do everything PL/SQL did in Perl (minus triggers).

Well, one can dream, can't they? :)

A crust of bread to a hungry populace...

[dasmegabyte]

PERL????

Jesus, PERL????

You know, the strength of query languages is that you don't have to use (and in face, are usually punished for using) loops and cursors to make massive changes. Perl is the most loop oriented language on earth. And even if, underneath it all, the optimizer is turning your code into a loop anyway, it's goddamn doing it more efficiently than Perl ever would. This addition is NOT going to increase the likelihood of people migrating from sybase or other TSQL based databases to MySql...it's going to increase the number of hardliners who feel that MySQL is a pathetic ghost of "real" servers, and as such decrease the cadence of better open source solutions like PostGreSQL. MySQL and Perl...it's fast becoming a database for control freaks who don't believe in doing anything automatically, or allowing the machine to do our optimizations for us -- and that's what computers are all about, goddamnit!

It is nice that there's finally a way to perform object operations on a server without performing the logic in scripted code, and it's nice that MySQL is trying to make a grab for usefulness beyond its INSERT, SELECT, DELETE simplicity. But Perl is not a standard language in the DB world...it's asking for DBAs and programmers used to TSQL and looking for a cheaper, freer alternative to gain new custom knowledge that is complex and no better then the knowledge they already have! All those linux sysadmins to have a little database are going to be overjoyed...but for the rest of us, this is totally useless, just like the rest of MySQL's features.

Re:A crust of bread to a hungry populace...

[nagora]

Perl is not a standard language in the DB world..

It is now.

Re:A crust of bread to a hungry populace...

[dasmegabyte]

Oh, excellent point. MySQL, a common database among non-DBAs, is creating a "new standard" rather than using the one that's available. It seems to me that that's widening the rift between those in charge of maintaining and optimizing database interaction and a non-optimizable database (okay, in all fairness you can optimize MySQL well enough, but not as easily or with as much benefit as other SQL servers). And in a world where marketshare is often determined by supportability, it seems that MySQL is telling decision makers "hey, you can use our database, which is inefficient and inelegant but maintainable by your cheapest UN*X admin." That's great and all for small databases...but for anything that's intended to scale (that means with an increased development personnel base as well as an increased server load) it seems to me that clinging to the defacto standard makes more sense than creating a newer, shittier one.

And it's not even like a TSQL stored procedure system is hard to implement! The MySQL programmers are just more Open Sourcers lost in an idiom of freedom that says that a feature is better released as a halfassed hack than an actual solution. But then again, with all the foolish squabbling between developmental agencies, it's a surprise they get anything done at all.

This "feature" only means that they'll eventually have to add an adjunct system for writing stored procs in TSQL, adding to the bloat of the application and slowly destroying the only nice feature of this server: how quickly and efficiently it can return a simple rowset.

I think that's the point of T-SQL and PL/SQL...

[Da VinMan]

and it's a worthy goal. Unfortunately, there's two major problems with them:

1. Too many people use them for procedural programming anyway. There's just things you can't do otherwise, so the capability has to be there. But the capability is often abused.

2. They're too proprietary. I'm not up on ANSI SQL standards enough to know by how much each one deviates and whether the ANSI standard provides a complete enough standard for SP operations, but it occurs to me that MySQL could stand to gain a lot by exactly toeing the ANSI standards line. Just a thought I guess.

Re:so use postgresql

[kolding]

Does PostgreSQL actually support stored procedures? I took a look at PostgreSQL a couple of months back, and it appeared to have no such thing. It has something called a stored function, that is a very different beast.

Schwartzian Transforms take on a new level!

[ajs]

Wow, can you imagine doing

• UPDATE 'foo', map { ... } grep { ... } sort SELECT 'bar', ...

This is the heart of the power of Perl, and if the interface is built right, it could be a huge boon to database work.

Of course, done wrong it could be slow, difficult to maintain and immediately obsolete.

Such as Java

[Baki]

If anything, then Java.
Oracle even moves PL/SQL (which isn't bad either) to Java. I'm sure Java will be the de facto SP language in the future for Oracle and DB2 (which have more than 60% of the market together). Just drop a JVM in the database core, and write a special JDBC driver for this.

No need to implement and maintain a fully new language.

subselects so hard?

[l00sr]

I'm sorry, but all this talk of MySQL's lack of subselects has got me all worked up. Could someone explain to the rest of us why the suckers are so hard to implement? It always seemed to me like it would be intuitively simple -- just execute the subselect, dump the result set into a temporary table, replace the subselect text in the main statement with the name of the temporary table, execute the main statement, delete the temporary table, etc. Is there any reason why it can't be this simple?

Re:subselects so hard?

[whiny]

It's not that simple. Why not? Two words: Correlated subqueries.

Postgresql

[jabbo]

Pgsql has subselects, joins, replication, write-ahead logging, transactions, and has had Perl as a procedural language for years. It's not quite as fast as MySQL, and it doesn't have as many idiots writing knicknacks for it in PHP, but the most useful ones IMHO (Thoth, ACID, PgMyAdmin, OpenACS, etc.) run fine on top of it. If you need transactional/sp features and do not want to pay for Oracle, it may be your best choice.

Postgres home page

SQL sucks

[chrysalis]

Most people are using SQL engines just to store basic records, that sometimes even don't need any sort of indexation.
SQL engines are slow and unreliable. Almost everytime I see a web site down (even Freshmeat) it's due to a database crash. SQL is a brain damaged query language. SQL tables have an horrible obsolete Cobol-like structure (every record must have a fixed len to be handled efficiently, types are fixed, etc) .
Sure, they can be useful for something.
But for 99% of the projects they are used for, they could be easily replaced with a simple indexing library like CDB, GDBM or BerkeleyDB (BDB itself is very powerful, it has a lot of nifty features, plus it's rock solid and damn fast) .
Or even flat files. I've seen so many people using complex SQL tables just to store 50 poor records. Just crazy. Do people know that filesystems can store data, too ? Does Squid need Oracle to store the cache ?
I never used SQL (although I coded large search engines and other stuff that stores and index a lot of data) . And I don't want to. BerkeleyDB achieve the same thing on a 386 than *SQL on a Thunderbird.

Re:SQL sucks

[Tablizer]

>> Most people are using SQL engines just to store basic records, that sometimes even don't need any sort of indexation. <<

Perhaps, but we don't want to have to overhaul all our calls when such a need later comes up.

Requirements change.

>> SQL engines are slow and unreliable. Almost everytime I see a web site down (even Freshmeat) it's due to a database crash. <<

Do you have any empiricle evidence of this?

>> every record must have a fixed len to be handled efficiently <<

The SQL standard does not dictate a fixed length. However, making anything open-ended usually requires some tradeoffs. This is mostly an implementation issue, not a protocol issue. Go yell at Oracle.

>> But for 99% of the projects they are used for, they could be easily replaced with a simple indexing library like .... <<

And if new requirements ask for multiple indexes, cross-references, etc.?

>> I've seen so many people using complex SQL tables just to store 50 poor records. <<

Any tool can be misused by idiots. Even flat files.

>> Do people know that filesystems can store data, too ? <<

Only hierarchically. But, the world is not a big tree. (Well, it is if you think Bill Gates is a big dog.) It is hard to find things using multiple, orthogonal criteria with just directory trees.

Umm... Doesn't this defeat the purpose?

[blueforce]

Perl is a Scripting language ( read interpreted) Stored procedures, by default are SQL statements which are compiled by the server and used as db objects... so, doesn't this defeat the purpose of having a (speedy, compiled) SQL statement?

Right on !

[Betcour]

I also can't understand why it takes so long for them to add subselects. It's easy to implement and a great much-needed feature. Why is it taking so long ?

Re:Why MySQL ?

[Betcour]

Why ? Because up to a few months ago things like BLOB had to be handled in a screwed up ways, because some types are missing, because indexes on 64 bit values seems to not work, etc...

postgreSQL is much more feature rich but not perfect, and the documentation is seriously not in sync with latest release (forcing you to dig thru mailling list archives to find how to use this or this feature)

PostgreSQL has it all ...

[nograz]

I enjoy seeing MySQL becoming a mature database, but I do not exactly know what this hype is all about!? PostgreSQL supports server-side programming for a long time now (the same way it supported transactions long before!). At the moment it supports three different (and working) implementations:

- PL/pgSQL (this is the counterpart to ORACLE PL/SQL)
- PL/Tcl (TCL Procedural Language)
- PL/Perl (yes, this is server-side perl implementation!)

Here the pointer to the corresponding manual entry

Same thing we always knew

[Proud Geek]

Basically, it says that MySQL is faster and more stable, and that PostgreSQL has transactions. Which is more important? How about a database that doesn't crash when you put a tiny bit of load onto it. I'll stick to MySQL, thank you, and the addition of Perl as a language for stored procedures will make it even better and more useful.

Because the person who succeeded prefers perl?

[TheLink]

I bet that's the reason why.

I mean the code is all there for anyone to change/add to.

Link.

Ok, so I'm not much of a a programmer...

[smartfart]

...but what is wrong with having your application (let's say you are using php in a web page) access the database (mysql) in a two-stage fashion, if you are needing to insure that these purchase orders get updated correctly, regardless of how the order get entered in.

• you write each method of order-entry
  
• instead of actually having each script write directly to the DB, have it pass the sql as string variables to a function
  
• have this sublayer function actually write to the DB, insuring that any order place updates everything correctly, leaving out no little-used rules, etc.
  

What's wrong with this? Just code the sublayer correctly, and you're on your way...

Ok, so all they ever learned me at collidge was procedural programming, no OOP :/

Steaming Pile of FUD

[Camel Pilot]

Over the years memory leakage (and usage) has been alot bigger problem with Java then with Perl. Nevertheless, on the whole after 5 years of programming with Perl I have never encounted any significant bugs with a stable release.

lots of one and two letter functions
Be a little more specific . . . Here is list from the reference:

abs, chr, cos, do, die exp, int, hex, kill, lc, log. m//, new, not, oct, pos, pop, ref, s///, sin, sub, tell, tie, vec

All these are clear english words or are derived from Unix or regexp. Anyone with a Unix or C background would immediately recognize most if not all.

a million operators
At least, probably more.

Really?!

[Da VinMan]

Apparently, I need to become more familiar with PostgreSQL. I looked at using it for a client a while back, but I quickly backed off of it when I realized that it's not a viable option for Windows OSs (there is a port of it for Windows, but I guess it's not very well tested or trusted). That is still an issue, but being able to do SPs with it would be nice.

On the other hand, MySQL is coming along nicely on Windows, so I might just wait to see what they come up with. I probably won't get clients to use either anyway and I don't know how hard I would push at this point, but I'm definitely going to keep these options in mind for clients that hate using proprietary products (which is a tough thing when you start talking about database technology!).

Re:Really?!

[Malcontent]

I certainly wouldn't use postgresql on windows (although I might if I bought it from great bridge). If you want a windows DB that open source, proven, stable, mature, reliable, fast, supports stored procedures, triggers, and has fantastic GUI tools may I recomend interbase. It does not have loadable stored procedures but does support domains and column level localization. Also consider SAPdb which is a very robust server from SAP the giant ERP company. They open sourced it a while ago.

How can we comment?

[bad-badtz-maru]

How can we comment on how good or bad this is when (per the development web site) there is no benchmark as to how expensive this functionality is? If it makes queries execute at a tenth of the "normal" speed and also adds 15 megs onto each mysql instance, then that would certainly factor significantly into the "usefulness" equation.

maru
www.mp3.com/pixal

Re:they didnt make it

[Drazi100]

true

Not Always - splitting related logic problematic

[Tablizer]

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.

The problem is that this seperates related logic. when doing maintenance; one may then have to hunt down the SP and switch between a SP editor and their application code.

It is often a better arrangment to keep related logic together. But, if the developers don't know SQL (which makes them not very good developers IMO), then SP's may simplify their lives.

Real programmers only use SP for speed, not better code management.

Re:so use postgresql

[ninejaguar]

In general programming terms, a function is a procedure that returns a value to be stored in a variable. So, if postgresql supports functions, perhaps they're being more specific in their purpose. But, it might still fall into the broader category of a procedure.

Language X possible through Perl Inline Module ?

[mattr]

I don't know if this will work with the subject of this story, but if the Inline module is supported it will let you also use Python, Java, C, C++, Tcl, Assembler, Guile, and whatever somebody else feels like glueing in.

Ought to work.. anybody tried using it?

Cursor speed

[spruce]

When I read your post I was a little surprised, when I've used cursors they've been extremely
efficient. What types of things were you doing, with how much data? Granted I'm not using millions of records, but I don't think cursors are appropriate for such large data sets.
In my test I'm running a SQL 7 on a 700mhz (or something close) P3 with 512k.

I open a local fast_forward cursor, select 5 fields (integer & 4 varchar(50)'s ) into variables,
loop through all of the records and print the integer at the end. Here are my result times:

1000 : Under 1 second
10000 : 1 second
201000 : 10 seconds

Maybe I'm missing something or I don't use cursors the way the rest of the industry does, but I can deal with those performance levels.

Another note on cursors

[spruce]

ADO, OLE DB, ODBC and DB-Library all expose the functionality of cursors through recordsets. From my experience recordset operations have to be one of the most common operations of database applicaions. How often do you open a recordset and loop through the records? Sound like the functionality of a cursor? Is it a coincidence that there are arguments to OpenRecordset methods (such as adOpenForwardOnly, adOpenKeyset) that map almost directly to arguments that are passed when creating T-SQL cursors. These API's create cursors when you use OpenRecordset. So basically from your statement you can conclude that one of the most common data operations performed against an MSSQL database from a client application is absolutely horrible in performance. I'm not buying it. Neither are the millions of applications and developers using ADO and ODBC recordsets with a SQL server database - I think somebody might have noticed.

Now since we know it's not cursors that are slow, perhaps it's just the database product itself? Nope, it's the fastest tested database in the world in several areas.

Re:Comparison Mysql vs PostgreSQL

[denshi]

Man, that's a funny page. On stability, he compares MySQL 3.23 with Postgres 6.4 -- a 4 year old product. Suprise! Guess which is more stable.

All I want to see is just *one* benchmark from the MySQL folks that isn't blatant dishonesty or incompetence.