Connecting BizTalk 2010 To MySQL using TwoConnect ODBC Adapter

July 27, 2012 at 3:26 pm 3 comments

MySQL is the world’s most popular open source database, falling behind only to Oracle and Microsoft SQL Server.

image

Because of its popularity sooner than later you will be facing the need to interact with MySQL from BizTalk Server. MySQL provides ADO .Net and ODBC drivers so you can use helper classes to access the database. However if you want a more message oriented approach the TwoConnect ODBC Adapter is the way to go.

The Adapter is very easy to use, stable and has a good performance. In this post I’ll show you how to use the adapter to execute an stored procedure in MySQL from BizTalk 2010.

For this example we are going to call an stored procedure in a MySQL database that inserts certain values into a table and returns the identity value of the record just added.

image

First of all you need to download the adapter from TwoConnect’s web site. The installation process is quite forward:

image

image

image

image

Once the installation is done you need to add the Adapter in the BizTalk Server Administration Console.

Open the Administration Console and expand BizTalk Group – Platform Settings. Right click in the Adapters folder and Select New – Adapter..

image

In the ODBC – Adapter Properties select the ODBC adapter and enter a proper name for it. Click on the Ok button to close the window.

image

Don’t forget to restart your host instances for the change to take effect.

image

Now that the adapter is ready you need to download the MySQL ODBC driver. The driver is available in x86 and x64 versions. You will need the x86 version for design time tasks in Visual Studio and the BizTalk Administration Console (both are x86 applications). The x64 will be required if the ODBC Adapter is going to run in a x64 Host Instance.

The installation process of the ODBC driver is also straight forward:

image

image

image

image

image

At this point we are ready for the next step, add the adapter metadata to create the artifacts needed to communicate with MySQL thru ODBC.

Right click with your mouse on your BizTalk project and select Add – Add Generated Items…

image

Select Add Adapter Metadata and click on the Add button

image

In Add Adapter Wizard select the ODBC Adapter and click on the Next button

image

Click on Set to define the DSN to be used

image

Select or create a System DSN and click on Ok

image

Type the credentials to be used for the DSN just selected and click on the Ok button

image

Click on Next to continue with the wizard

image

Because you are going to invoke a stored procedure that returns data, in the Schema Information step select Send Port and type the target namespace, request and response root name values.

SNAGHTML97e3bf8

Select Stored Procedure and click Next

image

In the Statement Information step type the proper query in the form databasename.spname an click on Generate. Once the Processed Command window has been populated click on Next

image

Click the Finish button and the wizard proceeds to create the needed XSD and ODX files.

image

At this point the adapter created the Request and Response schemas and a BizTalk Orchestration containing the port and multipart message types

image

image

There are two things to consider at this point:

1) The ODBC adapter created two individual Request and Response port types. You need to delete them because the operation is a synchronous Request/Response and create a new one.

Right click the Port Type folder and Select New Request-Response Port Type

image

Enter a proper name

image

2) The Multi-part Message Types are improperly assigned. Select the correct ones.

image

Once this two errors have been solved you just need to build the orchestration, connect the ports and deploy the BizTalk project.

image

Once the artifacts have been deployed the Send Port to MySQL needs to be created and configured.

In the Configure Application windows select the outbound port and click on New send port…

image

In Send Port Properties select the ODBC adapter and click Configure. Don’t forget to select also XML Transmit and Receive pipelines.

image

Next in the ODBC Transport Properties click on the ellipsis to configure the connection string to be used.

image

Choose your MySQL data source previously created

image

Type the credentials to be used for the DSN just selected and click on the Ok button

image

Close the remaining windows

image

Now you can start your application and test the TwoConnect ODBC Adapter. For example, if you submit a Xml document like this:

image

The adapter would execute the stored procedure and generate the following response with the ID assigned to the record in the MySQL table:

image

If you open MySQL Workbench the record is showed with the values defined in the Xml Request.

image

As you can see the TwoConnect ODBC adapter is very easy to use, has a very good performance and stability. Give it a try and share your experience with me.

Entry filed under: BizTalk. Tags: , , , .

Patch Tuesday: Microsoft fixes remote code execution attack on Microsoft XML Core Services Installing a SharePoint 2013 Development Environment

3 Comments Add your own

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


July 2012
M T W T F S S
« Jun   Aug »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

Categories


%d bloggers like this: