Want to read Slashdot from your mobile device? Point it at m.slashdot.org and keep reading!

 


Forgot your password?
Close
typodupeerror
×
Perl Programming

MySQL Gets Perl Stored Procedures 266

Posted by CmdrTaco from the well-isn't-that-special dept.
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.
This discussion has been archived. No new comments can be posted.

MySQL Gets Perl Stored Procedures More

MySQL Gets Perl Stored Procedures

Comments Filter:

  • Better stored proc languages... (Score:3, Interesting)

    by Hagabard ( 461385 ) on Friday August 24, 2001 @11:57AM (#2213941)
    Why didn't they use a Transact-SQL compatible stored proc syntax? This would ease migrating and also enable people who prototype DBs on MySQL to move it to either Sybase or MS-SQL with a minimal of fuss.

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

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

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

    • MySQL found dead (Score:1, Funny)

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

    • I would also like ... (Score:2, Insightful)

      by Da VinMan ( 7669 )
      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 ... (Score:4, Informative)

        by Malcontent ( 40834 ) on Saturday August 25, 2001 @02:56AM (#2215849)
        " 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.
    • 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.

  • That's great. (Score:1, Offtopic)

    by briggsb ( 217215 )
    I've been waiting for that. Good job Krow.

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

  • Bring on the GUI apps. Guess now I've got yet another reason to learn Perl
  • 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@!!@!@
  • 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
    • 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.
      • 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.
    • 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.
      • 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.

    • 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
      • 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 (Score:2, Insightful)

      by msheppard ( 150231 )
      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.
    • 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.

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

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

    • 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

    • 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
  • ...but you have to wonder about a product that is made *easier* by adding *Perl*.
  • whether to write application logic as Perl in MySQL, or as SQL in Perl..

  • Subselects? (Score:3, Funny)

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

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

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

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

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

    • Re:Subselects? (Score:2, Interesting)

      by dorkstar ( 318427 )
      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...
    • 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 [postgresql.org]

  • 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!
    • 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... (Score:3, Interesting)

        by don_carnage ( 145494 )
        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... (Score:3, Interesting)

        by bwt ( 68845 )
        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... (Score:3, Interesting)

          by krow ( 129804 )
          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.
          • 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.
  • Wow, now they only have to implement constraints, foreign keys, and transactions, and they'll almost be on the level of Postgresql.

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

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

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

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

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

  • Mysql todo list (Score:3)

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

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

    • Wait, I forgot sequences.
    • 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?

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

      Usable.

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

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

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

  • One other reason (Score:2, Insightful)

    by Dalroth ( 85450 )
    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).
  • 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? :)

  • 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.
    • Perl is not a standard language in the DB world..

      It is now.

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

  • Schwartzian Transforms take on a new level! (Score:3, Interesting)

    by ajs ( 35943 ) <[ajs] [at] [ajs.com]> on Friday August 24, 2001 @04:21PM (#2215470) Homepage Journal
    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.
  • 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.
  • 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 [cpan.org]?

Slashdot Top Deals

Whoever dies with the most toys wins.

Close