Can't connect to PostgreSQL database

[hennes]

Hi.

I'm trying hard to connect to a PostgreSQL database on another server. Here is my code:

Code: Select all


#include "Poco/Data/ODBC/Connector.h"
#include "Poco/Data/Session.h"
#include "Poco/Data/SessionFactory.h"


int main(int argc, char** argv)
{
   Poco::Data::ODBC::Connector::registerConnector();
   Poco::Data::Session dbSession(Poco::Data::SessionFactory::instance().create("ODBC", "DRIVER=PostgreSQL ANSI; SERVER=...; DATABASE=...; UID=...; PWD=..."));
   Poco::Data::ODBC::Connector::unregisterConnector();
   
   return 0;
}



which I then compile with

Code: Select all


g++ src/main.cpp -o main -Wall -lPocoXML -lPocoODBC


On execution I get the following error:

Code: Select all


jm[core]$ ./main
terminate called after throwing an instance of 'Poco::Data::ODBC::HandleException'
  what():  ODBC handle exception
Aborted


Any ideas?

[alex]

> Any ideas?

I'm not sure how is it that you are able to get away without linking the libPocoData in.

Be that as it may, if you have the PostgreSQL ODBC driver installed, the remote database listens on the default port (5432) and you provide valid values for server, database, uid and pwd, there should be no problem connecting.

Although your connect string should be enough for default case, see ODBCPostgreSQLTest.cpp for the full connect string.

To find out exactly why your connection fails, wrap your code in try-catch, like this:

Code: Select all


//...
try
{
   Session dbSession(Connector::KEY, "DRIVER=PostgreSQL ANSI; SERVER=...; DATABASE=...; UID=...; PWD=...");
}
catch(ConnectionException& ce)
{
   std::cout << ce.toString() << std::endl;
}
//...


It should give you something like this:

Code: Select all


ODBC Error: ODBC handle exception
===================
Connection:Not applicable
Server:Not applicable
===========================
ODBC Diagnostic record #1:
===========================
SQLSTATE = 08001
Native Error Code = 101
Could not connect to the server;
Could not resolve hostname.


HTH

Alex

[hennes]

Hi Alex.

Thanks for helping me out. Here is what I get:

Code: Select all


ODBC Error: ODBC handle exception
===================
Connection:Not applicable
Server:Not applicable
===========================
ODBC Diagnostic record #1:
===========================
SQLSTATE = IM002
Native Error Code = 0
[unixODBC][Driver Manager]Data source name not found, and no default driver specified


So I guess I misconfigured my unixodbc installation. Probably not the right place, I know, but I'll post my .inis. Maybe someone has an idea. (I have no ~/.odbc.ini)

Code: Select all


jm[core]$ cat /etc/odbc.ini
[PostgreSQL]
Description         = Test to Postgres
Driver              = PostgreSQL
Trace               = Yes
TraceFile           = sql.log
Database            = spacegame
Servername          = 10.8.0.1
UserName            =
Password            =
Port                = 5432
Protocol            = 6.4
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No
ConnSettings        =
jm[core]$ cat /etc/odbcinst.ini
[PostgreSQL]
Description     = PostgreSQL driver for Linux & Win32
Driver          = /usr/lib/libodbcpsql.so
Setup           = /usr/lib/libodbcpsqlS.so
FileUsage       = 1
jm[core]$


[alex]

> Maybe someone has an idea. (I have no ~/.odbc.ini)

Your code does not use odbc.ini.

Since the entry in odbcinst.ini is "PostgreSQL", your connect string should specify that driver name. Change "PostgreSQL ANSI" to "PostgreSQL" .

Alex