Using Polling Statement and Executing Custom SQL using BizTalk Oracle Adapter


The BizTalk oracle adapter can be used to poll a table after specific intervals. The oracle adapter transport properties must be set to poll the specific table. Username, password and Service Name (The TNS alias used in the TNS file) should be configured along with the Bin Path of the Oracle Client installed. You can check whether all the properties are configured properly by clicking the ellipsis in the managing events property in the Oracle transport property. Select the SQLNative as receive property.

 Oracle_NativeSQL

 
This Type of schema should be generated in the project by adding the oracle adapter metadata as shown in the section. A message of Type NativeSQL schema is returned after the execution of the polling statement query containing the data. Optionally a post polling statement query can be set that will run before the polling statement.

 Oracle Transport Properties

 
In the orchestration we have to create the type of Schema NativeSQL from the Oracle Adapter metadata.

Caution: It is better to keep the Oracle Schemas project separate because if another project uses the same service with NativeSQL schema a routing failure will occur.

 When you create the metadata from Visual studio, multi-part messages and port types will be created along with the orchestration by selecting NativeSQL service. If you are using a separate project for Oracle adapter metadata and your working project configure your port and multi-part messages accordingly.

 In this working example create a Request Message of type SQLEvent by configuring the message type property and referencing the message part from the Oracle Schemas project assembly. The response message returned will be of type “SQLEventResponse”.

 Oracle_SQLEvent 

 
Create a new receive only port and connect your receive shape of the request message you configured in the Orchestration.

 Oracle_Port

  

 The response message however is not normalized according to your needs and contains two records “ColumnMetaData” and “RowMetaData”. The Column meta data contains names of the columns and rowdata contains data but with no expected column name as XML tag. Therefore you need an intelligent map to normalize it to make it more useful. I have seen a lot of tutorials on the internet and have never come across a single post or article that explains what this map will look like. I tried it with a table looping funcoid and indexing functoid but it won’t work.

 

At the end we are left with a simple choice of using XSLT for normalizing our response to our desired schema. Below is a simple XSLT inline script that maps the result from SQLEventResponse Message to Our IFX based schema Message. We have to use a scripting functoid and use Inline XSLT Call template script. The script is below which is self explanatory. Thefor-each loops that iterates over the rows and we map it onto our destination schema record.

 

//Name of the template

<xsl:template>

 

//For each loop that iterates over “Column data” which actually makes a record row

 

<xsl:for-each select=”/*[local-name()=’SQLExecuteResponse’ and namespace-uri()=’http://schemas.microsoft.com/%5BOracleDb://OLTPDEV/NativeSQL%5D’%5D/*%5Blocal-name()=’Return&#8217; and namespace-uri()=’http://schemas.microsoft.com/%5BOracleDb://OLTPDEV/NativeSQL%5D’%5D/*%5Blocal-name()=’rowData&#8217; and namespace-uri()=’http://schemas.microsoft.com/%5BOracleDb://OLTPDEV/NativeSQL%5D’%5D/*%5Blocal-name()=’columnData&#8217; and namespace-uri()=’http://schemas.microsoft.com/%5BOracleDb://OLTPDEV/NativeSQL%5D’%5D”&gt;

 

//Use a variable for indexing a record

<xsl:variable select=’position()’ />

 

//Log_Reference is a column in the destination schema

//The text highlighted in the script is the $index variable which returns the

//row being iterated and the column order is known so I hard coded it, alternatively //you can use another foreach loop and index for columns as well

 

  <LOG_REFERENCE><xsl:value-of select=”/*[local-name()=’SQLExecuteResponse’ and namespace-uri()=’http://schemas.microsoft.com/%5BOracleDb://OLTPDEV/NativeSQL%5D’%5D/*%5Blocal-name()=’Return&#8217; and namespace-uri()=’http://schemas.microsoft.com/%5BOracleDb://OLTPDEV/NativeSQL%5D’%5D/*%5Blocal-name()=’rowData&#8217; and namespace-uri()=’http://schemas.microsoft.com/%5BOracleDb://OLTPDEV/NativeSQL%5D’%5D/*%5Blocal-name()=’columnData&#8217; and namespace-uri()=’http://schemas.microsoft.com/%5BOracleDb://OLTPDEV/NativeSQL%5D&#8217; and position() = $index]/*[local-name()=’string’ and namespace-uri()=’http://schemas.microsoft.com/%5BOracleDb://OLTPDEV/NativeSQL%5D’  and position()=1]” /></LOG_REFERENCE>

 In this way the result is a normalized message other than which is returned from the Oracle Adapter.

Alternatively NativeSQL schema is used when we have a custom query that cross-references different tables and Oracle Adapter Metadata cannot be generated for such type of queries. You can create a message of type “SQLEvent” which will contain your custom SQL Query that can be a string created dynamically in your orchestration. To generate the XML change the Root reference property of your NativeSQL schema from Default to SQLEvent. Right click the schema and click generate instance. In the message assignment shape load the XML through the XMLDocument type variable in your orchestration and assign the XMLDocument variable to SQLEvent Type message variable.

 message_assignment

 
The response will be of type SQLEventResponse which can be normalized to the procedure explained above.


technorati tags :
Advertisements

BizTalk Error – Please verify that the receive location exists, and that the isolated adapter runs under an account that has access to the BizTalk databases.


The Messaging Engine failed to register the adapter for “SOAP” for the receive location {Location}”. Please verify that the receive location exists, and that the isolated adapter runs under an account that has access to the BizTalk databases.

 As this is a permission issue the error occurs because the web service is running on the application pool which security account user is not a member of the BizTalk Isolated Host Users group.

 To solve this issue check the application pool security user account in the IIS. Add that user account into the BizTalk Isolated Host Users group. Or change the application pool to the pool which user is already a member of the BizTalk Isolated Host Users Group.


technorati tags :
%d bloggers like this: