Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

which database is best for perl ?

by tos (Deacon)
on Feb 09, 2004 at 10:32 UTC ( #327587=perlquestion: print w/replies, xml ) Need Help??

tos has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

a long time passed since i had to do with databases the last time. Therefore my knowledge about DB is rudimentary. I want to change that.

Naturally i want to interact "perlish" with the DB. I've installed the cygwin-Postgres (psql 7.4.1) which appeals to me. But i'm not sure if it is just right for perl.

What do you think about ? Is mysql eventually better suited for the use with perl ?

Thanks in advance for your experiences, opinions, and suggestions.

regards, tos

Replies are listed 'Best First'.
Re: which database is best for perl ?
by Abigail-II (Bishop) on Feb 09, 2004 at 10:43 UTC
    Is mysql eventually better suited for the use with perl ?
    No. Most, if not all, popular databases can be connected with from Perl using the DBI. Meaning the interface is the same.

    Your decision of what is the appropriate database to use should not depend on the language you use, but on your data. How important is the data to you? How volatile is it? What kind of queries are you going to perform? How often? How important is performance? How much data are you going to store?

    Abigail

      Listen to Abigail-II

      Don't think of it a what is right for Perl but what is right for you. Perl can be used to touch practically any database, the list of compatible DBI modules keeps growing. We seem to adopt that which we first come in contact with. Which you seem to have done, but it doesn't hurt to look around. Here is a link to MySQL's db comparison page.
      Some other things to consider:

      How stable is it?
      How portable is it?
      How secure it is?
      How easy is it to administer?
      What kind of support is available and does it have a cost?
      How much does the db cost?(for the budget minded)
      How mature is the DBD module for the db you want? If you are using DBI.
      What third party apps are available for it?

      Cheers
      Gyro

•Re: which database is best for perl ?
by merlyn (Sage) on Feb 09, 2004 at 12:18 UTC
      If I can ask a further question here... I'm moving towards Pg myself (from MySQL) and one "gotcha" that I've come across is getting the insertid from the previously-inserted record. In MySQL, it was something like:
      $sth_insert->execute( $chain, 'chain' ); push @relationship_ids, $dbh->{'mysql_insertid'};
      In Postgres, I have to do something like this (b/c I don't know any better {yet}):
      $sth_insert->execute( $chain, 'chain' ); push @relationship_ids, ( $dbh->selectrow_array( 'SELECT last_value FR +OM entities_id_seq' ) );
      Thanks!

      Jason

Re: which database is best for perl ?
by dominix (Deacon) on Feb 09, 2004 at 10:45 UTC
    I think the reply doesn't depend on the software you choose but the needs you have.

    for simple persistence DBM will often be enought (or DBM::Deep which is very perlish ) , if you plan complicate data structure you may need a SQL interface involving DBI. which you can plug on different DataBase.

    as Database itself DBI interface plenty of it. Postgres has a full featured SQL implementation, while MySQL is simple to install and performant. But if you just want to train yourself Postgres is ok as well as SQLite or any DBD you can install on your machine and code with.
    --
    dominix
Re: which database is best for perl ?
by ctilmes (Vicar) on Feb 09, 2004 at 12:36 UTC
    I agree with others that most will work just fine. I will note that PostgreSQL now has some support for stored procedures written in Perl.
Re: which database is best for perl ?
by Ryszard (Priest) on Feb 09, 2004 at 12:09 UTC
    Without performing a survey I would think the top three databases used with perl are oracle, mysql and postgres (in no particualr order).

    You can access them all using the same interface DBI. As all others have said, ask not what you can do for your database, but what your database can do for you.

    PS, i use postgres..

Re: which database is best for perl ?
by borisz (Canon) on Feb 09, 2004 at 10:51 UTC
    If I need a database, I choice postgres. Your particular case is postgres or mysql. Both are driven by DBI. The code for both are very similar.
    Boris

      Another vote for Postgres. Historically, Postgres has been a little more stable, robust, and feature-rich than MySQL, though the gap is closing rapidly. It's also a higher performance database, at the cost of some additional complexity and resources.

      I think my preference has much more to do with the preference for the elephant mascot and my anti-microsoftian dislike of "My" in front of program names :)

      Still, tons of Linux apps are written exclusively to use MySQL. I'd like to see them written with tools such as DBI, ODBC (pain!), etc, more often so that they are portable and users can choose what DB they want to use.

      If it's for learning purposes though, I'd definitely go the Postgres route.

      Sidenote: you said you were using the Cygwin version. Looks like a Win32 port is preceeding here: http://momjian.postgresql.org/main/writings/pgsql/project/win32.html. Until then, perhaps (just perhaps) MySQL might be better on Windows. Of course, anything other than MS SQL Server is a good thing :) Also, I believe Informix (disclaimer: untested & I have no opinion) is also free for Win32.

        Still, tons of Linux apps are written exclusively to use MySQL. I'd like to see them written with tools such as DBI, ODBC (pain!), etc, more often so that they are portable and users can choose what DB they want to use.
        Don't get me wrong: I, too, would love to see more apps that are written exclusively for MySQL to be database-agnostic, but the problem is not in the lack of using DBI. The problem is that all of the major databases I can think of (Oracle, MySQL, Postrgess, Sybase, MS-SQL) use different dialects of SQL. That is, you have to write out a different SQL statement for one DB than the other to do exactly the same thing.

        Granted, using DBI, or whatever the closest analog is in your language of choice, will make such porting issues easier, but there's no getting around the fact that, for example, join syntax is different between MySQL and Oracle. If you want your app to be usable on both DBs, simply coding it with DBI doesn't make it happen. You need to EITHER make all of your SQL so ridiculously simple that you might as well use a flat DB instead of an RDBMS, OR to make separate DB access sub-modules in your app for the databases you plan to support.

        ------------ :Wq Not an editor command: Wq
Re: which database is best for perl ?
by inman (Curate) on Feb 09, 2004 at 12:37 UTC
    Oracle appears to work quite well with most languages and environments. Oracle database servers have been around for a while and enjoy reliable support using the C API, ODBC and modules such as DBD::Oracle. Oracle can be installed on a number of operating systems including Windows and Solaris which makes development less of a chore since you can develop on a small box and upgrade. It can also scale from desktop right through to a multi-server enterprise installation with a toolset to complement each variety.

    Oracle server is going to cost quite a bit depending on the version, number of CPUs etc. It does however meet the requirement of being right for Perl. It just might not be so right for your bank balance!

      And depending on the version you buy. They dropped their prices on certain versions to compete with MS recently. 5000 for one node, one cpu ain't that bad.

      Play that funky music white boy..
        MS SQL server and the cut down version MSDE would also be on the list of database engines to try. Unfortunately the only DBI driver that I can find that works is the ODBC one. This limits a number of the features that you should be able to use with SQL server. Other than that, I find that SQL server works pretty well.
Re: which database is best for perl ?
by zakzebrowski (Curate) on Feb 09, 2004 at 13:14 UTC
    See posts above about choosing different databases... It's also very neat the perl has bindings to just about any database, which means you can treat any database about the same... I've had to work with some really arcane databases and or flat files, but because there was dbd bindings, I was able to 'get it to work' like a charm...


    ----
    Zak - the office
Re: which database is best for perl ?
by chance (Beadle) on Feb 09, 2004 at 19:42 UTC
    my rule of thumb, based on bone-headed decisions in the past:

    if you think it might be nice to use a database, but you're not sure, and you might be able to get away with just flat file, use MySql.

    if you know you need a database, and you know you are going to be using it in an 'advanced' way, but think you might be able to get away with MySql, use Postgres.

    otherwise use MySql

    when it comes down to the actual interaction with perl though, DBI makes both pretty easy, once you climb a pretty small learning curve. (assuming you know exactly what SQL you want executed)
Re: which database is best for perl ?
by MCS (Monk) on Feb 09, 2004 at 21:48 UTC

    A lot of people have offered good reply's but I'll offer my own.

    I personally use mySQL and I love it. It meets my needs and it's free. (for personal use/open source software) PostgreSQL is another good database. Either way you are mostly using SQL commands which are pretty much interchangable. The only other database I would suggest you look at would be oracle. While not free I think they have a stripped down version for free.

    For learning, either mySQL or PostgreSQL are both good options. Pick on and go from there. Perl is very good at accessing databases. (Oracle, mySQL, PostgreSQL and more)

Re: which database is best for perl ?
by dwhitney (Beadle) on Feb 10, 2004 at 21:02 UTC
    If your feeling like you want to beat yourself up, try using DBD::JDBC...
    The CPAN packages makes you install a little server which acts as the bridge between Perl and the DB. It is such a pain, but it works pretty well in our environment.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://327587]
Approved by dominix
Front-paged by broquaint
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (1)
As of 2021-10-16 01:03 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My first memorable Perl project was:







    Results (69 votes). Check out past polls.

    Notices?