Geeks With Blogs
Stuart Brierley Integration Management - BizTalk and More

As previsouly noted in my post on Schema Generation using the Community ODBC Adapter, I ran into a problem when trying to generate a schema to represent a MySQL stored procedure that had input parameters. 

After a bit of investigation and a few deadends I managed to figure out a way around this issue - detailed below are both the problem and solution in case you ever run into this yourself.

The Problem

Imagine a stored procedure that is coded as follows in MySQL:

StuTest(in DStr varchar(80))
BEGIN
  Declare GRNID int;
  Select grn_id into GRNID from grn_header where distribution_number = DStr;

  Select GRNID;
END

This is quite a simple stored procedure but can be used to illustrate the issue with parameters quite niceley.

When generating the schema using the Add Generated Items wizard, I tried selecting "Stored Procedure" and then in the Statement Information window typing the stored procedure name:

StuTest

Pressing generate then gives the following error:

"Incorrect Number of arguments for Procedure StuTest; expected 1, got 0"

If you attempt to supply a value for the parameter you end up with a schema that will only ever supply the parameter value that you specify.  For example supplying StuTest('123') will always call the procedure with a parameter value of 123.

The Solution
 
I tried contacting Two Connect about this, but their experience of testing the adapter with MySQL was limited.

After looking through the code for the ODBC adapter myself and trying a few things out, I was eventually able to use the ODBC adapter to call a test stored procedure using a two way send port.

In the generate schema wizard instead of selecting Stored Procedure I had to choose SQL Script instead, detailing the following script:

Call StuTest(@InputParameter)

By default this would create a request schema with an attribute called InputParameter, with a SQL type of NVarChar(1).  In most cases this is not going to be correct for the stored procedure being called.

To change the type from the default that is applied you need to select the "Override default query processing" check box when specifying the script in the wizard. 

BizTalk 2009 ODBC Adapter Schema Generation with Paramters - ODBC Statement Information

This then opens the BizTalk ODBC Override window which lets you change the properties of the parameters and also test out the query script. 

BizTalk 2009 ODBC Adapter Schema Generation with Paramters - BizTalk ODBC Override

Once I had done this I was then able to generate the correct schema, which included an attribute representing the parameter.  By deploying the schema assembly I was then able to try the ODBC adapter out on a two way send port.

When supplied with an appropriate message instance (for the generated request schema) this send port successfully returned the expected response.
 

Posted on Wednesday, June 9, 2010 3:27 PM BizTalk Adapters | Back to top


Comments on this post: BizTalk 2009 - The Community ODBC Adapter: Schema Generation with Input Parameters

# re: BizTalk 2009 - The Community ODBC Adapter: Schema Generation with Input Parameters
Requesting Gravatar...
Thanks for all the posts on the ODBC adapter. They have been very helpful.

Left by Paul on Sep 16, 2010 3:36 PM

# re: BizTalk 2009 - The Community ODBC Adapter: Schema Generation with Input Parameters
Requesting Gravatar...
Thanks for the feedback - its always nice to know that someone else finds my posts useful.
Left by Stuart Brierley on Oct 01, 2010 4:10 PM

# re: BizTalk 2009 - The Community ODBC Adapter: Schema Generation with Input Parameters
Requesting Gravatar...
In this adress there is also a good discusion about ODBC Adapter - Problem Generating Schemas from MySQL.
adress: http://stackoverflow.com/questions/2975279/biztalk-2009-community-odbc-adapter-problem-generating-schemas-from-mysql-store
Left by Ahsap Merdiven on Feb 22, 2011 8:49 AM

# re: BizTalk 2009 - The Community ODBC Adapter: Schema Generation with Input Parameters
Requesting Gravatar...
It's great.
I have tried it already. It is very useful.
But, I found a question.

When entry data is traditional chinese, write into table the datas not fullness.

Is it BIG-5 charset's problem?
How can I fix it?
Left by Peter on Mar 09, 2011 3:08 PM

# re: BizTalk 2009 - The Community ODBC Adapter: Schema Generation with Input Parameters
Requesting Gravatar...
Hi,
I am using MS SQL Server 2008 to try this.
I don't have Override default query processing item.
Any advise.
Thanks.
Left by joe on Apr 22, 2011 10:01 AM

Your comment:
 (will show your gravatar)


Copyright © Stuart Brierley | Powered by: GeeksWithBlogs.net