Hi, Can IIB 10 support to connect to Postgresql database via ODBC ? Can you provide a sample?

Hi Regina, 

From looking at the "supported software" tab for IIB10,  linked here, it seems that Postgresql is not supported. 

Hello Regina,

First off: Although we can't track what people connect to, I think we have quite a few customers doing what you want to achieve.  The translation of the full statement below would read "you will be supported, but only up to the ODBC layer", but I am not a lawyer.

This guide for App Connect Enterprise should also work for Integration Bus: https://community.ibm.com/community/user/integration/blogs/srecko-janjic/2023/04/13/connecting-app-connect-enterprise-to-postgresql-us

Here's the full statement from the report: 

IBM Integration Bus provides support for the ODBC 3.5 API on Windows and Unix platforms. Users may supply compatible drivers for database platforms not covered by the set of databases supported by the included database drivers. In these cases IBM will provide support for this configuration subject to the following limitations:

IBM Integration Bus will undertake to make defect fixes that are a result of an incorrect usage of the ODBC API by IBM Integration Bus.
IBM Integration Bus will only undertake to work around issues related to faulty third party drivers on a best-effort basis.

XA is not supported.
For Record & Replay, IIB only supports use of DB2, Oracle or SQL Server.
For Business Transaction Monitoring, IIB only supports use of DB2 and Oracle.
The driver must support the SQLDescribeParam call.
To call Stored Procedures the driver must fully support the SQLProcedures and SQLProcedureColumns API.
On Microsoft Windows platforms the Microsoft Driver Manager must be used to register the DSN for any driver which you wish to use.
On Unix platforms the driver must be supported by version 2.2.14 of the unixODBC driver manager. See http://www.unixodbc.org/drivers.html for a list of drivers which are known to be compatible.

Where a defect is suspected in driver code owned by another IBM product team IIB L3 service will engage with the relevant product team provided that the user is also entitled to support for that driver.

Where a defect is suspected in driver code provided by a third party IIB L3 service will share problem analysis information with third party vendors however third party support must be engaged by the user under a valid support contract.

There is one sample for ODBC.ini in following thread. It is for ACE but it should be similar with IIB.

https://community.ibm.com/community/user/integration/discussion/ace-12-to-postgresql-connection-via-odbc

We tested PostgreSQL ODBC connection from ACE 11 some time ago.  For some reason the stored procedure call failed when using the the normal external stored procedure definition in ESQL. It worked when we sent the stored call via ESQL PASSTRHU statement like:

PASSTHRU('call mySchema.myProc(?,?)', param1,param2);

I suppose using external stored procedure definition would be the preferred way to do it but I am not sure is it possible from IIB/ACE to PostgreSQL.

We used also following setting in ODBC.ini stanza because there were some issues with committing:

ConnSettings=SET AUTOCOMMIT TO 'ON';