How to connect SharePoint Business Data Catalog to PostgreSQL

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

  1. Open BDC MetaMan and click the drop down menu “Connect to data source”.
  2. Select “ODBC – Connection String” from the drop down menu.
    Select ODBC connection string
  3. 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 ?

    PostgreSQL connection string

  4. 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.
    All PostreSQL tables available to use
  5. 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.

    Drag and Drop one table onto Design Surface

  6. Open menu item Configuration->Settings in order to set the path to save our application definition file.

    Set the path to save application definition file

  7. Click “Generate” button. If everything is ok then we will see this message.

    Application definition file has been created

  8. 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.

    PostgreSQL data on SharePoint page

We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com.

<dmitry/>