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

Hi2All,

Perl newbie debuting here. I have encountered the following persistent problem.

Using:
- Windows XP Professional SP3
- ActivePerl 5.12.1 Build 1201
- PostgreSQL 8.4.4

According to PPM I have the following relevant drivers installed (all up-to-date):
- DBI 1.613
- DBD-ODBC 1.24 (ODBC DBD for Perl DBI)

But when trying to execute the following simple program: 

#!usr/bin/perl
use warnings;
use diagnostics;
use strict;

use DBI;

my $driver = "ODBC";
my $dbname = "test"; 
my $dsn = "dbi:$driver:$dbname";
my $username = "postgres";
my $password = "************";

my $dbh = DBI->connect($dsn, $username, $password, {PrintError => 0, R
+aiseError => 1}) || die "Error $DBI::err [$DBI::errstr]";

 print "Connected: State is %s\n", $dbh -> state || "OK";

$dbh->disconnect;

[download]

I get the following error message:

Uncaught exception from user code:
DBI connect('test', 'postgres',...) failed: MicrosoftODBC Driver Manager Data source name not found and no default driver specified. (SQL-IM002) at DB_test.plx line 16
at C:/Perl/site/lib/DBI.pm line 675
DBI::__ANON__(undef, undef) called at C:/Perl/site/lib/DBI.pm line 730
DBI::connect('DBI', 'dbi::ODBC::test', 'postgres', '************', 'HASH(0x298dcc)') called at DB_test.plx line 14

After looking for a solution for hours I still can't figure out what's the problem, so in desperation I turn to thy sages of Perl.

Replies are listed 'Best First'.
Re: Cannot connect Perl to PostgreSQL using ODBC (SQL-IM002)
by Corion (Patriarch) on Aug 22, 2010 at 07:27 UTC 
    Data source name not found and no default driver specified.

    [download]

    This means you did not create the proper DSN. You need to create the DSN using (for example) odbcad32.exe, or use a DSNless connection string. See http://connectionstrings.com for how to create the various connection strings.

    Also, a Google search for the error message turns up many relevant replies.

[reply]
[d/l]
[select]
Re: Cannot connect Perl to PostgreSQL using ODBC (SQL-IM002)
by lr3p (Initiate) on Aug 22, 2010 at 11:33 UTC

    Thank you Corion for pointing me in the right direction. I have finally solved the problem which was a combination of lack of PostgreSQL ODBC driver and improper format of connection string.

    Solution

    Remark: I recommend using DBD::ODBC to connect to PostgreSQL on Windows instead of DBD::PgPP (unmaintained and full of bugs, written in Pure Perl) or DBD::Pg (no Windows version on ppm & latest update on http://pgfoundry.org/frs/?group_id=1000199&release_id=1212 is for Perl 5.10 made in late 2008).
    1. Install latest version of DBI & DBD-ODBC using ppm.
    2. Download & install appropriate psqlodbc driver from http://www.postgresql.org/ftp/odbc/versions/msi/ (don't forget to upgrade using the batch file).
    3. Go to Control Panel -> Administrative Tools. Choose Data Sources shortcut. In the ODBC Data Source Administrator dialog box, click Add, then select the driver (e.g.: PostgreSQL Unicode) you want from the Installed ODBC Drivers list and choose OK.
    4. Use the appropriate connection string (http://connectionstrings.com/postgre-sql#p51), e.g.:

    my $dbh = DBI->connect("dbi:ODBC:driver={PostgreSQL Unicode};Server=12
+7.0.0.1;Port=5432;Database=database;Uid=username;Pwd=password;", user
+name, password, {PrintError => 1, RaiseError => 1}) || die "Error $DB
+I::err [$DBI::errstr]";

    [download]
    5. Using telepathy, thank the person who compiled this little manual for you so that you don't have to go through the cycle of frustration, disillusionment and apathy.

[reply]
[d/l]
      Remark: I recommend using DBD::ODBC to connect to PostgreSQL on Windows instead of DBD::PgPP (unmaintained and full of bugs, written in Pure Perl) or DBD::Pg (no Windows version on ppm & latest update on http://pgfoundry.org/frs/?group_id=1000199&release_id=1212 is for Perl 5.10 made in late 2008).

      It's always the same. People using ActiveState perl and ppm, finding out the hard way that ActiveState didn't compile and pack all of CPAN. ActivePerl does not contain a C compiler, nor does it contain a linker. You are expected to buy that from Microsoft. Some other people used their resources to compile some missing modules into ppms, and provide them at various places all over the internet. Some of them contain third party code that may or may not be legally distributed in this way. The ease and beauty of CPAN is lost when you use ActivePerl.

      I learned it years ago, getting more and more frustrated about that situation.

      Then Strawberry Perl was released. CPAN became usable on Windows. XS modules became usable on Windows. Time could be spend for developing instead of hunting for ppm files.

      Short: Get rid of ActiveStates Perl as soon as you can and use Strawberry Perl.

      Regarding your special problem with PostgreSQL: DBD::Pg should compile nicely with Strawberry Perl, but you need a little bit of PostgreSQL on the Windows machine to compile the DBD. This is not a problem of DBD::Pg or PostgreSQL, it happens with every XS module that links against third party code. Most DBDs need a little bit of third party code, DBD::SQLite (comes with the required code bundled) and the "pure perl" DBDs being exceptions to that rule.

      DBD::ODBC allows to connect to many databases, but that flexibility has a price tag: you add at least one extra layer of code to the communication, often slowing down your program notably. Plus, DBD::ODBC has no interface for the special features of each database. My recommendation is to use the native DBD where possible, falling back to DBD::ODBC only when a native DBD is not available or seriously broken. Typically, I use DBD::ODBC only to connect to MS SQL Server (DBD::Sybase is not the native DBD for MS SQL Server, it just happens to be able to connect to MS SQL server using an ancient protocol) and to MS Access.

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]
        set pendantic mode on

        Actually, DBD::Sybase doesn't use an obsolete/old protocol. It uses whatever freetds has available, which is usually TDS 7 or 8, which happens to be the current native protocol for MS-SQL.

        It's just a different implementation from the native ODBC API, but it's the same protocol. And if you use DBD::ODBC on Unix/Linux with freetds, then you really use the same protocol as DBD::Sybase, but with a different API.

        Michael

[reply]
        I dont know how long, but ActiveState also distributes MinGW
        ppm install MinGW

        [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom