Back to Top

Tuesday, September 30, 2008

Using Perl to access PostgreSQL under Windows

This appears by a non-intuitive problem for people. Below I assume that you are using some version of ActivePerl for Windows (5.8 or 5.10). First of all:

Under no circumstances (ok, I rephrase: only under extreme circumstances) should you use DBD::PgPP. It is old, not very performant (given that its implemented in Pure Perl) and has some nontrivial bugs. An example for one such bug is the following:

my $sth = $dbh->prepare('SELECT foo FROM bar WHERE baz = ? AND fuzz = ?');
$sth->execute('a?b', 'c');

With PgPP the actual query it will try to execute will be something like: SELECT foo FROM bar WHERE baz = a'c'b AND fuzz = ?. I assume that what it does is that it takes the parameters provided to execute one by one and looks for the first question mark in the query. But given that the first parameter itself contains a question mark, the method fails...

So how do you install DBD::Pg if there aren't in the ActiveState PPM repositories?

  1. Go to the dbdpgppm project on pgfoundry
  2. Download the correct PPD file for your needs (depending on the Perl version - 5.6, 5.8 or 5.10 - and if you need SSL support or not)
  3. Issue the following command:
    ppm install the_downloaded.ppd

4 comments:

  1. Anonymous10:35 PM

    Thank you sooo much!
    You're the man!

    ReplyDelete
  2. Anonymous12:22 AM

    Many people seem to run into this problem right after install:

    Can't load 'C:/Perl/site/lib/auto/DBD/Pg/Pg.dll' for module DBD::Pg: load_file:The specified module could not be found

    (or similar). This was reported as a bug on the project site:

    http://pgfoundry.org/tracker/index.php?func=detail&aid=1010467&group_id=1000199&atid=754

    The solution described there worked for me:

    copy pg.dll.manifest to the dir of perl.exe, then rename it to perl.exe.manifest

    Also see this forum thread:

    http://pgfoundry.org/forum/forum.php?thread_id=1145&forum_id=739

    ReplyDelete
  3. @Anonymous: thank you for the tip!

    ReplyDelete
  4. How I got it to finally work on 2008 x64:

    The DBD::pg package is the one currently maintained, however, the binary of it is compiled by pgfoundary and is compiled in a way where the DLL’s are incompatible when I used our version of ActiveState Perl on windows 2008 x64. Therefore, I found another third party repository and used theirs:

    1. On the command line, run: ppm rep add trouchelle.com http://trouchelle.com/ppm/
    2. Then start the package manager by running: ppm
    3. View all packages, find DBD_Pg and install it.
    4. If you are on a 64 bit OS, it will require krb5_32.dll on the system which is the Kerberos DLL from MIT. You can get this by installing the 32 bit version of PgAdmin III, it’s in its install directory.

    ReplyDelete