BizTalk 2009 - The Community ODBC Adapter: Schema Generation with Input Parameters
- by Stuart Brierley
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.
This then opens the BizTalk ODBC Override window which lets you change the properties of the parameters and also test out the query script.
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.
