Recently, we have seen a number of customers come to us regarding connectivity issues when using the Data Direct ODBC driver manager. They are able to connect to the community PostgreSQL with the Data Direct ODBC manager, but not with the EDB Postgres Advanced Server. What we found was that there is a limitation on the Data Direct ODBC side where it does not license EDB Postgres Advanced Server, even though it does support community PostgreSQL and the two share the same base code. Until support for EDB Postgres Advanced Server is implemented on the Data Direct ODBC end, here are two suggested workarounds that we have:
Switching ODBC systems - the UNIXODBC driver manager is open source and can be configured to allow for EDB Postgres Advanced Server easily, as it already supports it by default. Simply download the Unix ODBC driver from the website, http://www.unixodbc.org/, and you can find the EDB Postgres Advanced Server ODBC driver in the /connectors/odbc folder after installation.
Changing the version() function - if changing ODBC systems is not a reasonable solution, one method that we have found that has allowed users to continue using Data Direct ODBC with EDB Postgres Advanced Server is to override the version() function. By entering the following:
> CREATE SCHEMA myversion schema;
> CREATE USER foo PASSWORD 'foo';
> GRANT USAGE,CREATE ON SCHEMA myversion schema TO foo;
> CREATE FUNCTION myversionschema.version() RETURNS text AS $$ SELECT 'PostgreSQL 9.5'::text $$ LANGUAGE sql;
> ALTER USER foo SET search_path=myversionschema, pg_catalog, public;
we can have Data Direct ODBC work with EDB Postgres Advanced Server. This will require the changing of the search_path variable for each user that is used to connect through the Data Direct ODBC driver, but once that is updated for each user, there should be interoperability.