Click here to Skip to main content
Click here to Skip to main content

Tagged as

Using Polling Statement and Executing Custom SQL using BizTalk Oracle Adapter

, 19 May 2009
Rate this:
Please Sign up or sign in to vote.
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. The for-each loops that iterate 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/
[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='Return' and 
namespace-uri()='http://schemas.microsoft.com/
[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='rowData' 
and namespace-uri()='http://schemas.microsoft.com/
[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='columnData' 
and namespace-uri()='http://schemas.microsoft.com/[OracleDb://OLTPDEV/NativeSQL]‘]”>

//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/
[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='Return' and 
namespace-uri()='http://schemas.microsoft.com/
[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='rowData' and 
namespace-uri()='http://schemas.microsoft.com/
[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='columnData' 
and namespace-uri()='http://schemas.microsoft.com/
[OracleDb://OLTPDEV/NativeSQL]‘ and position() = 
$index]/*[local-name()='string' and namespace-uri()=
'http://schemas.microsoft.com/[OracleDb://OLTPDEV/NativeSQL]‘ 
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 :

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Abdul_Rafay
Architect
Qatar Qatar
I am a BizTalk Server MVP for 2010 and an active blogger and a participant on the MSDN BizTalk Server Forums. I am currently working as a BizTalk Architect at a Bank in Qatar. Before this position I worked as a Sharepoint Consultant in UAE , BizTalk Architect in Bank Saudi Fransi in Saudi Arabia and United Bank Ltd in Pakistan.
 
I also achieved MCTS certification in BizTalk Application development in June 2008.
 
Click here to check out my Blog.

SQL Server Query Notification with BizTalk Server 2009 WCF SQL Adapter.

Envelope Wrapper.

Aggregator (Sequential Convoy)

Splitter (Debatching multiple records)
Resequencer
Recipient List
Scatter and Gather using Self Correlating Port

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 19 May 2009
Article Copyright 2009 by Abdul_Rafay
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid