for the last few months i've been writing some scripts that require database access, and in the process have run into many of the day-to-day issues attendant to using perl with mysql and postgresql. i've generally had to track these down (although they were often in some bit of documentation somewhere) and it occurred to me that we would be doing ourselves and others following the same path a service if we were to summarize our assembled wisdom. i'm sure that there are those who have higher levels of enlightenment to shed on some of these items ... we could probably assemble a skeleton with a great deal of utility. in that spirit, here are a few things for which i've learned to watch.

mysql-

--installs with "skip networking" specified in /etc/mysql/my.cnf ... a little thing that can hang you for a bit if you don't know it.

--watch disk space ... especially with logging turned on mysql can eat it up pretty quickly, and then simply start failing insert statements with curiously inconsistent error statements.

--dbd driver sensitive to memory leaks when performing a large number of operations on very large tables. these problems may manifest themselves in sections not specifically dealing with database access.

--quirky insert statement syntax ... i.e., "insert into <table_name> set column1=$whatever, column2=$whaterver2 ... etc.";

--access control system offers finely-grained control, but reminds me of reverse polish notation in that if you don't work with it reasonably frequently it'll drive you crazy each time you deal with it. if someone has a good heuristic for application here i'm sure a great many would regard that as a blessing.

--o'reilly book (MySQL and mSQL) has a few errors ... e.g., isamchk identified as repair utility, rather than myisamchk. not being critical, just heading off the time someone might spend trying to figure out why it doesn't work.

postgresql

--insert statement very sensitive to quoting. use $dbh->quote() whenever applicable or the statement will flat bomb out. parser just doesn't like it.

--i've had some difficulty with a select max statement when the operant column is character but holds numbers. for some columns the statement will work and for others not, and the reason for that is not presently apparent to me.

--trying to keep numeric strings appropriately aligned by stuffing white space in the beginning of the string doesn't apparently work after insertion, even after quoting. i've had to stuff with zeros ... maybe not the best solution, but it sure is nice to get back records you've put in when you do a select statement. <grin>

i know i have others that i'm not thinking of right now ... perhaps other would like to make a running thread of this? i'm not particularly interested in advocacy of one or the other, each had a slighly different niche. if we do this right our comments should also map those niches for individuals trying to make a decision regarding which to choose.

Edit by tye

Replies are listed 'Best First'.
Re: mysql and postgresql gotchas and tips
by lestrrat (Deacon) on Aug 03, 2001 at 07:34 UTC

    Why use $dbh->quote when you can just bind the parameters? 

          my $sth = $dbh->prepare( "INSERT INTO table VALUES ( ?, ?, ?, ? 
+)" );
      $sth->execute( 'a', 1234, undef, 't' );
      ## samething for do

    [download]

    To me that looks much much cleaner than just embedding the parameters using quote()

    I guess this thing about insert statements doean't bother me because PostgreSQL is the first real database that I had to deal with.... :-)

[reply]
[d/l]
      to tell the truth, i just hadn't tried that. sometimes juggling a number of balls means you can't pull one out of the air and look at it more closely <grin>. regardless, a good post for the thread, precisely the kind of thing i was hoping to accumulate.
[reply]
      Yupski. I tend to find that PostgreSQL comes back with less than useful error messages sometimes, so if you aren't using placeholders (I did find one case where I couldn't) you should be doing a:

      my $sql = "INSERT INTO $table VALUES ....";
print STDERR "About to execute the sql statement $sql\n" if $debug;
my $sth = prepare($sql);
...etc...

      [download]

      That's pretty obvious but it wasn't instinctive to me until about halfway through my database program.

      ____________________
      Jeremy
      I didn't believe in evil until I dated it.

[reply]
[d/l]
        Note that interpolating the sql first becomes exactly the same as the original, in that you need to use quote again.

        I'm curious what the case where you can't bind the value is? The worst I've hit was having to use :1 :2 instead of ? ?
        --
        Snazzy tagline here

[reply]
Re: mysql and postgresql gotchas and tips
by ralphie (Friar) on Aug 02, 2001 at 23:18 UTC
    sorry ... the formatting of the previous didn't hold.
[reply]

Back to Perl Monks Discussion