How to connect SharePoint Business Data Catalog to PostgreSQL

By admin Uncategorised 12th February 2010

In this walkthrough we will demonstrate how to integrate PostgreSQL with Microsoft SharePoint Business Data Catalog through ODBC and display PostgreSQL data on SharePoint page. In order to do that we will describe how to create application definition files for the Business Data Catalog by means of BDC Meta Man.

There are one articles which we are going to reference to. It describes Business Data Catalog – getting started

Open BDC MetaMan and click the drop down menu “Connect to data source”.
Select “ODBC – Connection String” from the drop down menu.
We now have a textbox to enter our ODBC connection string and advanced properties. As we are using PostgreSQL so:
- our connection string should be Driver={PostgreSQL ANSI};server=<server name>;port=<port number>;database=<database name>;Uid=<user login>;Pwd=<password>;Trusted_Connection=yes; For example in our case connection string looks like Driver={PostgreSQL ANSI};server=cygnus;port=5432;database=postgres;Uid=postgres;Pwd=test;Trusted_Connection=yes;
- left and right delimiter symbols should be double quote “
- parameter symbol should be question mark ?
BDC Meta Man will now connect to your ODBC data source and display the database. Expand the database you have connected to so you can see all the tables available for you to use. All the next steps about how to display the data on SharePoint page are described in Business Data Catalog – getting started article in details. We will walkthrough briefly.
Drag and Drop one table onto Design Surface. Upon dragging and dropping the table, the entity is created with the Finder, SpecificFinder and IdEnumerator methods.
Open menu item Configuration->Settings in order to set the path to save our application definition file.
Click “Generate” button. If everything is ok then we will see this message.
Import our application definition file to SharePoint 2007, add Business Data List web part and configure it by selecting just imported Business Data catalog Entity (Business Data Catalog – getting started article describes in detail how to do that). Now we will be able to see PostgreSQL data on SharePoint page.