DB2 Stored Procedures and the Business Connectivity Services in SharePoint 2010

Introduction

In this walkthrough we will demonstrate how to integrate DB2 stored procedures with Microsoft SharePoint 2010 and display the data on SharePoint 2010 page. In order to do that we will describe how to create a new external content type by means of BCS Meta Man.

Prerequisites

Microsoft SharePoint Server 2010
Microsoft Visual Studio 2010 Professional or higher
BCS Meta Man
Download and install “IBM Data Server Runtime Client (Windows 32-bit AMD and Intel x86)” http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217 on a machine where BCS Meta Man runs
Download and install “IBM Data Server Runtime Client (Windows AMD64 and Intel EM64T)” http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217 on a machine where SharePoint 2010 runs
DB2 stored procedures for CRUD operations such as:
1. CREATE PROCEDURE GET_ALL_DEPARTMENTS()
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  DECLARE v_departments CURSOR WITH RETURN for
  SELECT * FROM DEPARTMENT;
  OPEN v_departments;
  END P1
2. CREATE PROCEDURE GET_DEPARTMENT_BY_ID(IN P_ID CHAR(3))
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  DECLARE v_departments CURSOR WITH RETURN for
  select * from DEPARTMENT where DEPTNO=P_ID;
  OPEN v_departments;
  END P1
3. CREATE PROCEDURE GET_DEPARTMENT_IDS()
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  DECLARE v_department_ids CURSOR WITH RETURN for
  select DEPTNO from DEPARTMENT;
  OPEN v_department_ids;
  END P1
4. CREATE PROCEDURE CREATE_DEPARTMENT(IN P_ID CHAR(3), IN P_NAME VARCHAR(36), IN P_MGRNO CHAR(6), IN P_ADMID CHAR(3), IN P_LOCATION CHAR(16))
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  insert into DEPARTMENT values(P_ID, P_NAME, P_MGRNO, P_ADMID, P_LOCATION);
  END P1
5. CREATE PROCEDURE UPDATE_DEPARTMENT(IN P_ID CHAR(3), IN P_NAME VARCHAR(36), IN P_MGRNO CHAR(6), IN P_ADMID CHAR(3), IN P_LOCATION CHAR(16))
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  update DEPARTMENT set DEPTNAME=P_NAME, MGRNO=P_MGRNO, ADMRDEPT=P_ADMID, LOCATION=P_LOCATION where DEPTNO=P_ID;
  END P1
6. CREATE PROCEDURE DELETE_DEPARTMENT(IN P_ID CHAR(3))
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  delete from DEPARTMENT where DEPTNO=P_ID;
  END P1

Walkthrough

Open Visual Studio 2010
Add a New Project
Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
Give your project a name i.e DB2StoredProceduresBCSMetaManDemo and click “OK”
On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button
If prompted, click “Trial” on the licensing dialog
To show the “BCS Meta Man Data Source Explorer” we need to enable it from the Menu item, this new window can be docked so it doesn’t get in your way
Click the “Add Connection” button to show the “Connection Dialog”
Select “ODBC Server” as Data Source type, enter DB2 ODBC connection string (for example it can be “Driver={IBM DB2 ODBC DRIVER};Database=SAMPLE;Hostname=cygnus;port=50000;Protocol=TCPIP;Uid=Administrator;Pwd=12345;Trusted_Connection=false;”), enter double quotes as left and right delimiters and “?” as parameter symbol and click “Connect” button.
The Data Source Explorer will now be populated with your DB2 data
Drag and drop your Finder Stored Procedure onto the design surface and enter External Content type name, i.e “Department”, click “OK”
Choose the Finder as the method type and click “OK”
The External Content Type will be created on the Diagram
In order to set an identifier right-click on the External Content Type, select “Manage External Content type” menu item
Click “Edit Identifiers” button on the “Entity Management” dialog, select an identifier and click “OK” and “Update” button then
The External Content Type will update with the new identifier
From the “BCS Meta Man Data Source Explorer” drag and drop your Specific Finder stored procedure onto the existing External Content Type
The “Stored Procedure Configuration” dialog will be displayed. Select “SpecificFinder” as the Method Type. Select the first Parameter – P_ID and set the Filter Type as Comparison and the Default Value as ‘0’. Click “OK”.
The External Content Type will update with the new Specific Finder Method
Next drag and drop the Id Enumerator stored procedure onto the existing External Content Type
Select “IdEnumerator” as the Method Type, Click “OK”
The External Content Type will update with the new Id Enumerator Method
From the “BCS Meta Man Data Source Explorer” drag and drop your Creator stored procedure onto the existing External Content Type
The “Stored Procedure Configuration” dialog will be displayed. Select “Creator” as the Method Type. For each Parameter select the appropriate column to map it to. Click “OK”.
The External Content Type will update with the new Creator Method
Next drag and drop your Updater stored procedure onto the existing External Content Type
Again the “Stored Procedure Configuration” dialog will be displayed. Select “Updater” as the Method Type. For each Parameter select the appropriate column to map it to. Click “OK”.
The External Content Type will update with the new Updater Method
Finally drag and drop your Deleter stored procedure onto the existing External Content Type
Again the “Stored Procedure Configuration” dialog will be displayed. Select “Deleter” as the Method Type. Click “OK”.
The complete External Content Type will look like the following
Press F5 to deploy The External Content Type, this will load up your SharePoint Page once deployed
Add a new External list by selection on SharePoint page “Site Action”->“More Options…”->“External List”
Give your External List a name and Click on the icon to show the available External Content Types
Select created DB2StoredProceduresBCSMetaManDemo.Department External Content Type, click “OK”. Click “Create” button then.
"244" height="69" />
The external data provided by DB2 stored procedure is now displayed in the SharePoint External List
You can hover over the first column of your list and you’ll see a drop down appear. If you click the drop down arrow you’ll get a number of options for that particular row of data – one of which will be View Item.
You will be presented with the View Item dialog
Now click “Edit item” on the ribbon of the dialog. This option is only available if you create an Updater method for your External Content Type. Clicking on this link will open up a dialog form that allows you to edit that row of data and save it.
While viewing the external list, if you click the Items tab on the ribbon you’ll see a New Item button
This will bring up the “New Item” dialog where you can add a new Department. We will enter information about new Department and click “Save”
This will insert a new row directly to your DB2 database