BCS filters and DB2 Stored Procedures with Parameters in SharePoint 2010

Introduction

In this tutorial we will show you how to connect to a DB2 Database using BCS Meta Man and a Stored Procedure to return filtered data to a SharePoint Business Data List. In this walkthrough we will be creating a Finder Method with filters so our DB2 Stored Procedure needs to have parameters defined.

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 procedure such as:
    1. CREATE PROCEDURE GET_EMPLOYEES_FILTERED(IN P_FIRST_NAME VARCHAR(12), IN P_BONUS DECIMAL(9,2))
      DYNAMIC RESULT SETS 1
      P1: BEGIN 
      DECLARE v_employees CURSOR WITH RETURN for 
      select * from EMPLOYEE where FIRSTNME like ‘%’ || P_FIRST_NAME || ‘%’ and BONUS > P_BONUS;
      OPEN v_employees;
      END P1

Walkthrough

  1. Open Visual Studio 2010
  2. Add a New Project
  3. Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
  4. Give your project a name i.e BCSMetaManFilteredDB2StoredProcedure and click “OK”

    Create BCSMetaManFilteredDB2StoredProcedure project

  5. On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button

    Deploy as a farm solution

  6. If prompted, click “Trial” on the licensing dialog
  7. 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

    Open the BCS Meta Man Data Source Explorer 

  8. Click the “Add Connection” button to show the “Connection Dialog”
  9. 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.

    DB2 ODBC connection string 

  10. The Data Source Explorer will now be populated with your DB2 data

    See the stored procedures available to use

  11. Drag and drop your Finder Stored Procedure with parameters onto the design surface and enter External Content type name, i.e “FilteredEmployee”, click “OK”

    Drag and drop a Finder stored procedure and Enter External Content type name

  12. Select the Method Type as “Finder’’
  13. Select the first Parameter (p_first_name) and set it’s Filter Type to Comparison and the Default Value to ‘A’

    Configuring Finder DB2 stored procedure and first parameter

  14. Next select the second Parameter (p_bonus) and also set it’s Filter Type to Comparison and the Default Value to ‘100’

    Configuring Finder DB2 stored procedure and second parameter

  15. Click ‘OK’
  16. The External Content Type will be created on the Diagram

    External Content Type created on diagram

  17. In order to set an identifier right-click on the External Content Type, select “Manage External Content type” menu item

    Manage External Content type

  18. Click “Edit Identifiers” button on the “Entity Management” dialog, select an identifier and click “OK” and “Update” button then

    Set identifier

  19. The External Content Type will update with the new identifier

    External Content Type with identifier

  20. Press F5 to deploy, this will load up your SharePoint Page once deployed
  21. Add a new Business Data List Web Part to your SharePoint page

    Add Business Data List Web Part

  22. Click on the ‘Open the tool pane’ link
  23. Click on the icon to show the available External Content Types

    Open the picker to select your External Content Type

  24. Select our ‘BCSMetaManFilteredDB2StoredProcedure.FilteredEmployee’ External Content Type, click ‘OK’

    Select the FilteredEmployee External Content Type

  25. Click ‘OK’ on the tool pane
  26. Click the ‘Add’ link on the Business Data List Web Part to add the second Filter option

    To use multiple BCS filters click the Add link

  27. Change the second Filter to be the P_BONUS and enter values for the filters and click ‘Retrieve Data’
  28. Your DB2 data provided by Stored Procedure is now displayed in the SharePoint Web Part

    View your filtered data provided by DB2 stored procedure

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

<Dmitry Kaloshin/>