Cannot get PostgreSQL ODBC to work in Connection String

1
votes

I am having an issue and just can't seem to resolve it. I cannot get my postgresql connection string to work. Everything works fine with I create a DSN, but when I try to utilize the driver in a connection string I keep getting the datasource not found error. I have PostgreSQL ANSI(x64),PostgreSQL ODBC Driver(ANSI),PostgreSQL ODBC Driver(UNICODE),PostgreSQL Unicode(x64). In the connection string I use the Unicode one. Again, in the DSN and ODBC Manager it works great. But I am using classic ASP (in IIS7) to pull data from the dB and I keep getting error. People were referring me to the connection string site, and I've tried everything I can think of. To me, it looks perfect, but just can't get past that error. Here is the string that I am using:

cst = "Driver={PostgreSQL ODBC Driver(UNICODE)};Server=ipaddress;Port=5432;Database=dbname;Uid=uid;Pwd=pw;"

Please please help?

By the way, I'm new to this site, so if I do something wrong with this question, please let me know. Thanks so much.

2
postgresqlunicodeconnectionodbc
I keep getting error. What is the exact text of the error message? - Craig Ringer
Here's the exact text of the datasource not found error: Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified - user3624483
I know I don't have a default driver specified but don't know why it wont take my driver in connection string? Maybe do I need to install different versions. I have x64 because that it what my server is - user3624483

2 Answers

2
votes

As far as I know classic ASP has a 32-bit server executable.

That means you need the 32-bit ODBC driver, not the 64-bit one, as the driver must match the client architecture not that of the PostgreSQL server.

Install and select the 32 bit PostgreSQL ODBC driver.

2
votes

Just had the same exact problem. Here is how I got it working with classic ASP under IIS7:

  • Install Visual C++ 2010 X64:

https://www.microsoft.com/en-us/download/details.aspx?id=14632

  • Install PSQLODBC X64

http://www.postgresql.org/ftp/odbc/versions/msi/

  • Run windows ODBC Administrator (search for odbc in windows search)
  • In System DSN tab, create a connection to the Database and use the connection name in place of the connection string on the Classic ASP script

Update:

I was also able to get classic ASP to connect to PostgreSQL through a connection string:

DBConn.Open "DRIVER={PostgreSQL UNICODE(x64)};Server=server_ip;Port=5432;DATABASE=database_name;UID=database_username;PWD=user_password;