Can't connect to PostgreSQL Plus Avanced Server EnterpriseDB

dataservices
1

We’re using PostgreSQL Plus Avanced Server EnterpriseDB, which is a commercial release of PostgreSQL. While connecting to this DB via DataDirect ODBC driver, I got the following error message:
[SAP BusinessObjects][ODBC PostgreSQL Wire Protocol driver]The Connect Series for ODBC PostgreSQL Wire Protocol driver is only licensed
to connect to PostgreSQL databases. You are attempting to connect to the following type of server:
EnterpriseDB 9.3.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit.

I’m using BODS 4.1. Even when I upgraded to BODS 4.2, still have the same problem.

Is there anyone using EDB as me? Can anyone give me some suggestions?

netfloator (BOB member since 2014-04-22)

2

This post is a little old, so maybe you found the answer already? Do you still need an answer? If so, I have one for you, because we went through the same thing.

ht1815 (BOB member since 2008-05-23)

3

Well, I’m going to go ahead and post our solution to this problem anyway, in case someone in the future searches for this problem and finds this post. It would have helped us when we had this problem, that’s for sure.

The problem lays in the fact that the DataDirect driver verifies the type of database it is connecting to and it expects to connect to a Postgresql database, not an EnterpriseDB one. In order to check the version, when it connects to the database, it runs a function in the pg_catalog named version(), which returns a string indicating the type of database, as well as some information about the OS etc. In EnterpriseDB, for example, it will return something like ‘EnterpriseDB 9.2.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit’. From a true Postgres installation, it will return something like ‘PostgreSQL 8.3.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4)’. The driver is looking for a string containing PostgreSQL. In order to get the DataDirect driver to work, you have to return it a string that indicates that it is connecting to a PostgreSQL installation. Here’s how we did that:

We created a new schema in each database to which we are connecting Data Services. We named it ‘force_datadirect_odbc_drivers_to_work’, but it isn’t important what you name it. We then created database Login Roles for use by Data Services and set the search_path variable for the Role to list that new schema first. For us, it looked like this ‘search_path = force_datadirect_odbc_drivers_to_work, pg_catalog, prod;’

Then we created a new version() function in the schema and defined it so that it returns ‘PostgreSQL 9.2’ (or whatever version you’re using). Basically, it’s overloading the version() function. So, when Data Services logs in using the new Role, it runs version(), but because of the search_path, it runs the new version(), thus getting a value it can live with.

This all requires some setup and maintenance, but it works. Hope it helps someone.

ht1815 (BOB member since 2008-05-23)

4

Yes, this is the same solution we used.

Some details here:
http://forums.enterprisedb.com/posts/list/3934.page

even if DataDirect does not support this :frowning:

Anyway, for the moment, based on our tests, this deployment
BODS 4.2SP2 <-> ODBC DataDirect driver 7.1 <-> EDB PPAS 9.3 (with the ‘workaround’ on the version)
works.

ht1815, on your side, did you encounter any problem ?

dida (BOB member since 2013-01-23)

5

No, this solution is working well for us. We have other problems between DS and our EDB databases, but version problems are no longer an issue.

ht1815 (BOB member since 2008-05-23)

6

ht1815, what kind of problems do you have ?
Are they related to the usage of EDB PPAS ?

dida (BOB member since 2013-01-23)

7

I really don’t know what causes them. What we see are times when our DS jobs appear to run successfully to completion, with no errors, but we have incomplete data. What we find when we investigate is that the database is throwing an error during a transaction, often a ‘unique key violation’ type error, which causes the transaction to abort but which does not make it back up the chain through the ODBC driver to DS. Typically, though it depends somewhat on the number of records involved, this means we see 1000 records missing, though only one of those may actually have had a problem.

I’ve tried opening a request with SAP on this, but unfortunately, it’s intermittent and I have not been able to create a set of control circumstances that I can use to show them the problem every time.

It’s weird, because we have plenty of times when the errors do make it all the way back and the jobs do fail as they should.

We don’t know if the problem is in the ODBC driver itself, in DS, or between the two. It’s an ongoing troubleshooting process.

ht1815 (BOB member since 2008-05-23)