This is another article of the same series that has been continuing for the last couple of weeks. Yep! SharePoint and the Business Data Catalog! Following are the recent articles that cover the SharePoint and the Business Data Catalog "Definition Editor Tool":

1. Working with Business Data Column
2. Importing BDC Data into SharePoint
3. Using Business Data Catalog Definition Editor
4. Few Issues With Microsoft Business Data Catalog Definition Editor

In this article, we will discuss how to integrate SharePoint with MySQL database using the Business Data Catalog. We will use the "Business Data Catalog Definition Editor Tool" to create the application file. There has to be a way to connect SharePoint, one of the world's best collaboration portal products, and the MySQL, the world's most popular open source database (as claimed by the MySQL team). SharePoint allows users to index and search data in Line-of-business (LOB) applications.

I used MySQL version 5.0 for this demo.

Create a database

Create a database named "musicrecords". This database has 1 table "albums" and "albums" has following fields (See Figure 1):

Figure 1: Table "albums"

Install ODBC Driver

You will also need to download the ODBC driver from the MySQL site. ODBC 5.1 is a newer version and has improved performance as compared to the older 3.51 version. After downloading, Install the driver on your system. Installation is easy. Wizard guides you through the setup process.

Figure 2: Install the ODBC 5.1 Driver

Configure ODBC Driver

After installing the ODBC driver, you have to add a system DSN. Following are the steps required to add the DSN:

1. Go to "Data Sources". In Windows 2003, Select "Start > Administrative Tools > Data Sources (ODBC)".

2. Select the "System DSN" tab.

3. Click the "Add" button.

4. Select "MySQL ODBC 5.1 Driver" from the drivers list and click "Finish".

Figure 3: Select the driver from the list

5. Add a data source name and description. Data source name and description can be anything but for this demo, use "dsnMusic" as the data source name. This is because we have used this name in the "XML definition file". Add server name. By default, the server name is "localhost" and the port is "3306".  If you configured your server on a port other than the default "3306", enter it in the "Port". Enter user and password. Entering correct user and password is important to connect to the database. If you entered correct user and password, the database drop down will fill with the databases that the user has permissions to access. Select the database, in our case, select "musicrecords" and click "Test". If everything is ok, it will show you "Connection successful" message. These are the basic settings. You can also configure the DSN for other settings like the SSL, log information, etc. To configure other settings, click the "Details" button. It will show you five tabs: Flags 1, Flags 2, Flags 3, Debug and SSL Settings. For this demo, configuring other settings is not required.

Figure 4: Configure ODBC Driver settings

Create Definition File

6. Open "Microsoft Business Data Catalog Definition Editor". Click "Add LOB System".

7. Click "Connect to Database".

8. Select "ODBC" from the "Connection Type" drop down.

9. Enter "dsn=dsnMusic;Trusted_Connection=True" in the "Connection String" box and click "Connect".

10. Click "Add Table" button which is visible on the right side vertical bar.

11. You will see only one table "albums" in the list. Drag and drop that table to the middle.

12. Click "OK". Enter a name for the new LOB system. I had named it "musicrecords". Click "OK". You will see "Creating LobSystem" message in the status bar. It will take a few seconds to create the LOB system. You will see "Ready..." in the status bar once the LOB system is ready.

13. Let's test the new LOB system. By default, two new methods will be added: "Find_albums" and "FindAll_albums". Expand the "FindAll_albums" node, double-click "Instances" folder. Right-click "FindAll_albums_Instance" and select "Execute". Click the "Next" button to execute the method. You should see the following error message (See Figure 5):

Figure 5: The LobSystemInstance Property 'AuthenticationMode' has a value that requires 'Trusted_Connection' in the connection string.

14. Close the window. Go up and open the "Instances" folder just under the main node "musicrecords". Click "musicrecords_instance". Click any where inside the connection string box, a button will appear, click it. It will open a box where you can edit the connection string.

Figure 6: Edit the connection string

Copy and paste the following connection string in the box and click "Ok":

data source={
"Driver={MySQL ODBC 5.1 Driver}};dsn=dsnMusic;Uid=root;Pwd=admin;Trusted_Connection=true;"}

Do not try to align or format this string, copy it as it is. Change the user id and password. By default, the user id is "root". If you are using a different user id, replace the "root" with your own user id. Also, change the password.

Now try to execute the method "FindAll_albums_Instance" again and see if it works. It shouldn't work! You will get the following error message (See Figure 7):

Figure 7: The field named "id" is defined by the Entity as being of Type 'System.Int32', but the back end system returned a value of Type 'System.String'.

To fix this error, click the method name "FindAll_albums". Do not confuse it with the method instance name which is "FindAll_albums_Instance". In the properties section (in the right side pane), modify the "select" statement in the "RdbCommandText" property. Click inside the property box, a  button will appear, click this button to modify the "select" statement. Clicking the button will open a box named "wideTextBoxEditor". You can edit the statement in this box. You can also edit the statement directly in the property box without opening this "wideTextBoxEditor". The statement will be as following:

select "id" from albums

Remove the double quotes around the "id". The statement now should look as follows:

select id from albums

Now, select the method instance "FindAll_albums_Instance", right-click it and select "Execute". Click "Next". It will show you the IDs available in the table.

Now, let's turn towards the "Find_albums_Instance" method. Right-click the method name, click "Execute". You will be required to enter the input parameter which will be an existing id. Go ahead and enter an id that you know exists in the database. Click "Next". What will happen? Will it show you the record? No! It will give you the following error (See Figure 8):

Figure 8: LOB system did not find instance

Great! Let's fix this issue. Click on the "Find_albums" method. In the right side "Properties" pane, You will see the following SQL statement in the "RdbCommandText" property:

Select "name","artist","dateofrelease","numberofsongs","id" from albums where id=@id

Remove the double quotes around the select parameters and replace the input parameter "@id" with "?". The statement after modification will look as following:

select name,artist,dateofrelease,numberofsongs,id from albums where id=?

Now, let's test the method instance "Find_albums_Instance" again. Right click the instance name and select "Execute". Enter an ID that you know exist in the database and click "Execute". The result will be shown in the "Results" grid.

Figure 9: Results grid showing the record retrieved against the ID provided as input

Adding A New Method

15. Let's add a new method that will return all the records. Right-click the "Methods" node and select "Add Method". This will add a new method "Method0". Click the method "Method0" and change it's name in the properties pane. Give it a meaningful name, for example, "FindAll_album_Records". Click anywhere in the "DefaultDisplayName" property, it will automatically acquire the new method name you just entered in the "Name" property. In the "RdbCommandText" property, add the following statement:

select * from albums

Right-click "Parameters" node, and select "Add Parameter". You will see following four options:

a. In
b. InOut
c. Out
d. Return

Select "Return" and click "Ok".

Figure 10: Select a return parameter

Rename the newly added parameter from "Parameter0" to "@albums". To rename it, change the "Name" property. After changing the "Name" property, click anywhere in the "DefaultDisplayName" property to change it to the name you just entered. Right-click the new parameter "@albums" and select "Create Root Typedescriptor". Change the "Name" and "DefaultDisplayName" of this type descriptor from "@albums_RootTypeDescriptor" to "Reader". Right-click root type descriptor "Reader" and select "Add TypeDescriptor". Change "Name" and "DefaultDisplayName" of this type descriptor from "TypeDescriptor0" to "Record". Right-click "Record" and select "Add TypeDescriptor". Change the "Name" and "DefaultDisplayName" to "name" which is one of the table fields. Similarly, for the remaining fields (artist, dataofrelease, numberofsongs, id), add a type descriptor for each field and change it's "Name" and "DefaultDisplayName" properties to its actual name. chFor example, for the field "numberofsongs", change the "Name" and "DefaultDisplayName" to "numberofsongs". Well, it's up to you. If you want to give some other name to your field, add it in the "Name" and "DefaultDisplayName" properties. Field "id" requires special attention. Change its "TypeName" from "System.String" to "System.Int32". Click anywhere in the "Identifier" property, a drop down will appear, select "id[albums]" from the drop down (See Figure 12).

Figure 11: "FindAll_Album_Records" Method

Figure 12: Change "Identifier" property

Right-click "Instances" and select "Add Method Instance". It will show you "Create Method Instance" interface. Select "Finder" as the method instance type. Click "Ok". Change the method instance name and default display name from "MethodInstance0" to "FindAll_album_Records_Instance".

Figure 13: "Create Method Interface" screen

To test this method instance, right-click the instance name and select "Execute". Again, click the "Execute" button. All the records in the table will be shown in the "Results" grid.

Figure 14: "Results" grid showing all the records from the database

Final Step

16. Before the application could be imported into the SharePoint, there is one last step remaining. Click the main node "musicrecords" in the tree view and enter "%" in the "WildcardCharacter" property (in "Properties" pane on the left). Without this your application instance won't appear in the web part's tool pane.

Using BDC Application In SharePoint

17. To use the newly created application in SharePoint, export the LOB system to XML file by selecting the main node "musicrecords" and clicking the "Export" button. Enter a name for the XML file. I used "musicrecords". Click "Save" to save the file on your system.

18. Go to SharePoint Central Administration and open SSP defined for the site where you want to display BDC data.

Figure 15: Select SSP in SharePoint Central Administration

19. Click "Import application definition" (Business Data Catalog section).

20. Click "Browse" and select the definition file. Click "Import" button. Do not change other options on this page. File Type should be "Model".

21. Now go to your SharePoint site. Select "Edit Page" from "Site Actions".

22. Click "Add a Web Part" in the zone of your choice.

23. Select "Business Data List" web part from the list of available web parts. If this web part is not available, then you will have to add it first to the list. Select "Site Settings" from "Site Actions". Go to Top level site settings if this is not your top level site. Select "Web parts" from "Galleries" section. Click "New". Select "BusinessDataListWebPart" (See File Name column in the list) and click "Populate Gallery". Now go back to the site. Select "Edit Page" from "Site Actions". Click "Add a Web Part" in the zone of your choice.

24. Select "Business Data List" from the list of web parts and click "Add".

Figure 16: Add Business Data List Web Part

25. Click the "Open the tool pane" link in the web part.

Figure 17: Select Business Data Type

26. Click the browse button and select the BDC Type. Click "Ok".

27. The BDC web part will display the data retrieved from the database. See following figure to see how the data looks like.

Figure 18: BDC data in a web part

That's it. We saw how we can use the Microsoft Business Data Catalog Definition Editor tool to create the application file needed to connect SharePoint with MySQL.

Click here to view the BDC Application file (musicrecords.xml).

Click here to download the BDC Application file (musicrecords.xml) in zip format.