MOSS.BDC.&.Informix.101

I'm back doing Office Sharepoint Server again! ARGGGGGGGGGGGGH!!!!

This time, I will need to get my hands dirty on Business Data Catalog (BDC). I'm supposed to help my customer to expose some data from an Informix database to MOSS using BDC *sounds exciting eh?*

Here's a tip, if you are new to BDC, check out this documentation on MSDN. Spend about two days reading it and you will become an expert! You can also watch a short screencast here. And of course, no learning is complete without practice and so, you got to do it on an actual MOSS installation. *I recommend Hyper-V-ing an image*

To make your life easier in editing the Application Definition File (and to maintain your sanity), make sure you download the latest Office SharePoint Server 2007 SDK. Why? Because there is this handy tool called Business Data Catalog Definition Editor that can help you edit the application definition file. It is not perfect but it gets 90% of the job done. For the remaining 10%, use trusty Visual Studio 2008.

The tool isn't installed by default. You have to install it from Drive:\Program Files\2007 Office System Developer Resources\Tools\BDC Definition Editor. There is a setup.exe file in there which is safe to double-click. :)

If you feel like typing out the xml in Visual Studio 2008, then I suggest you load in the schema. It is in a file named bdcmetadata.xsd located in drive:\Program Files\Microsoft Office Server\12.0\Bin. It will help get some intellisense into Visual Studio to reduce the number of typos.

To get data out from Informix, you will need to install the IBM Informix Client SDK on the MOSS machine where it will contain the necessary drivers and providers to the database. You can't use the Informix .NET Managed Provider with the BDC Definition Editor and I can't get the Informix OLEDB Provider to work. So, the only way (for me) is to use good old ODBC.

If you are using the BDC Definition Editor, do becareful that the generated SQL statements for the Informix tables will be incorrect. Remove all double quotes from the column names and replace named parameters i.e. @CategoryID with the question mark (?). It is recommended that you define primary keys for your tables in order for the editor to do its magic. Otherwise, you will go through a whole lot of clicking.

When primary keys are defined, the editor will automagically create a SpecificFinder and an IdEnumerator method for us. We just need to create a Finder method for the BDC Data List Webpart. You can test the methods by clicking the Execute button (note: this is context sensitive and only appears when a method instance is selected).

Finally, when everything is defined, right click on the BDC Application name (root) and Export the definition file. From here-on, you can import it to MOSS via the BDC section under your Shared Services Administration page.

If everything works accordingly, you should be able to use the BDC webparts to expose the data from the database. Take note that you may need to configure Enterprise SSO in order to get things working correctly.

Anyway, if you would like to play around with BDC, I suggest you use a SQL Server as it is much easier.