Re: Cannot connect Perl to PostgreSQL using ODBC (SQL-IM002)

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.

Comment on Re: Cannot connect Perl to PostgreSQL using ODBC (SQL-IM002) Download Code

Replies are listed 'Best First'.
Re^2: Cannot connect Perl to PostgreSQL using ODBC (SQL-IM002) by afoken (Chancellor) on Aug 23, 2010 at 06:23 UTC
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]
Re^3: Cannot connect Perl to PostgreSQL using ODBC (SQL-IM002) by mpeppler (Vicar) on Aug 24, 2010 at 12:27 UTC
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]
Re^3: Cannot connect Perl to PostgreSQL using ODBC (SQL-IM002) by Anonymous Monk on Aug 23, 2010 at 06:51 UTC
I dont know how long, but ActiveState also distributes MinGW `ppm install MinGW` [download]	[reply] [d/l]


XP is just a number
	PerlMonks