The Future of Middleware and the BizTalk Roadmap

If you interested in future of BizTalk watch the latest talk of Tony Meleg during WPC 2011 Los Angeles: AP03 - The Future of Middleware and the BizTalk Roadmap.

Calling Stored Procedure Oracle 11g Using WCF-Custom OracleDbBinding

In this post I like to show how to call a stored procedure in Oracle 11g XE performing an insert and an update. To create a stored procedure in Oracle 11g I look for guidance and found at Oracle® Database PL/SQL Packages and Types Reference, and PSOUG Insert and Update. I created the following two procedures:

create or replace procedure "INSERT_CUSTOMER"
(customer_name IN VARCHAR2,
customer_street IN VARCHAR2,
customer_city IN VARCHAR2,
customer_state IN VARCHAR2,
customer_photo IN BLOB)
is
begin
INSERT into customer (name, street, city, state, photo) values (customer_name, customer_street, customer_city, customer_state, customer_photo);
end;

create or replace procedure "UPDATE_CUSTOMER_ADDRESS"
(customer_name IN VARCHAR2,
customer_street IN VARCHAR2,
customer_city IN VARCHAR2,
customer_state IN VARCHAR2,
customer_photo IN BLOB)
is
begin
UPDATE customer SET street=customer_street, city=customer_city, state=customer_state WHERE name=customer_name;
end;

Both are very basic stored procedures. Next step in this little endeavor is creating a BizTalk project and create schemas for both procedures using the Consume Service Adapter Wizard.

After hitting OK a schema and binding file is created. Schema’s are depicted in screenshot below.

I build the solution after signing and providing it with application name. Next step is deploying the schema and configure the send port (by importing the binding file). Created Send Port contain two SOAP Actions and this needs to be one in my scenario (messaging). Two SOAP Action result in an error:

For calling stored-procedure to insert a new customer I have send port with following SOAPAction defined:

<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Operation Name="INSERT_CUSTOMER" Action="http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure/INSERT_CUSTOMER" />
</BtsActionMapping>

With a filter for MessageType:

http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure/INSERT_CUSTOMER#INSERT_CUSTOMER

And I created a new Send Port with SOAPAction:

<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Operation Name="UPDATE_CUSTOMER_ADDRESS" Action=”http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure/UPDATE_CUSTOMER_ADDRESS” />
</BtsActionMapping>

With a filter for MessageType:

http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure/UPDATE_CUSTOMER_ADDRESS#INSERT_CUSTOMER_ADDRESS

In both Send Port I added credentials and changed useAmbientTransaction setting to False (see also my previous post). Finally I created a Send Port to send both response to file with following filter:

BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure#INSERT_CUSTOMERResponse
Or
BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure#UPDATE_CUSTOMER_ADDRESSResponse

and a receive port for incoming messages. When I test my application with following message:

<ns0:INSERT_CUSTOMER xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure">
<ns0:CUSTOMER_NAME>Sarah Ralls</ns0:CUSTOMER_NAME>
<ns0:CUSTOMER_STREET>8th Street</ns0:CUSTOMER_STREET>
<ns0:CUSTOMER_CITY>Bellevue</ns0:CUSTOMER_CITY>
<ns0:CUSTOMER_STATE>WA</ns0:CUSTOMER_STATE>
<ns0:CUSTOMER_PHOTO></ns0:CUSTOMER_PHOTO>
</ns0:INSERT_CUSTOMER>

I receive this message in my out folder:

<INSERT_CUSTOMERResponse xmlns="http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure" />

And I check the database to see if record with Sarah Ralls has been created:

This works out. I then offered the following message to update the address:

<ns0:UPDATE_CUSTOMER_ADDRESS xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure">
<ns0:CUSTOMER_NAME>Sarah Ralls</ns0:CUSTOMER_NAME>
<ns0:CUSTOMER_STREET>9th Street</ns0:CUSTOMER_STREET>
<ns0:CUSTOMER_CITY>Bellevue</ns0:CUSTOMER_CITY>
<ns0:CUSTOMER_STATE>WA</ns0:CUSTOMER_STATE>
<ns0:CUSTOMER_PHOTO></ns0:CUSTOMER_PHOTO>
</ns0:UPDATE_CUSTOMER_ADDRESS>

And I received this in my out folder:

<UPDATE_CUSTOMER_ADDRESSResponse xmlns="http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure" />

This works too and to be sure I checked database again:

I can tell you that calling a stored-procedure in Oracle 11g XE is straight forward, though I have to say I used pretty standard PL/SQL and did not use a cursor or anything. Something for another post. I hope this detail will show how to call stored procedure with BizTalk using the OracleDbBinding and Consume Adapter Service Wizard.

Technorati: biztalk server 2010

Using BTSHTTPReceive in x64 Environment

I have a VM with BTS 2010 installed on a Windows Server 2008 R2 x64. In this environment I wanted to find out how to work with HTTP Adapter. Fortunately there two samples found in <install folder>:\Program Files(x86)\BizTalk Server 2010\SDK\Samples\AdapterUsage\HTTPRequestResponse or HTTPSolicitResponse (you will find them if you have selected the SDK feature during BizTalk Installation). Before running the example you will need to follow these steps if working in x64 environment (i.e. my VM):

1. Open IIS and click Machine Name on left panel, then click "ISAPI and CGI restrictions" on the right panel, then Add the ISAPI or CGI path:

On a 64 bit machine add: C:\Program Files (x86)\Microsoft BizTalk Server 2010\HttpReceive64\BTSHTTPReceive.dll

2. Check allowed extension path or execute.

For sample for instance RequestResponse, you will need to click setup in HTTPRequestResponse folder. After refreshing IIS you will see HTTPRequestResponseSample.

Now you will have to perform the following steps:

1. Click "HTTPRequestResponseSample" on left panel, then click "Handler Mapping" on middle panel, then click "Add script mapping” with the following setting:
   Request path:BTSHTTPReceive.dll
   Executable:
   On 64 bit machine add: C:\Program Files (x86)\Microsoft BizTalk Server 2010\HttpReceive64\
   
2. Click Request Restriction

Click OK and click YES if ask to add this ISAPI extension.

From sample text on MSDN I understand I had to configure the virtual directory for this sample to run under the context of a user in the BizTalk Isolated Host Users and IIS_IURS user groups, So I had to configure the virtual directory to run in a new IIS application pool by completing the following steps (these differ from original text as I have IIS 7.0):

1. Click Start, point to All Programs, point to Administrative Tools, and then click Internet Information Services (IIS) Manager.
2. In the Internet Information Services (IIS) Manager, navigate to the Application Pools folder.
3. Right-click the Application Pools folder and click New, Application Pool...
4. Enter a name for the Application Pool ID: such as BizTalkSDKSamples, verify that the .NET Framework 4.0.30319 option is selected, and Managed Pipeline Mode Integrated (leave Start the Application pool immediately unchecked) and click OK to create the new application pool.
5. Click Advanced Settings in Edit Application Pool pane on left.
6. Click the Identity of Process model and change the identity under which this application pool runs to a user that is a member of the BizTalk Isolated Host Users user group. This user should also be a member of the local IIS_IURS user group.
7. Configure the virtual directory for this SDK sample to run under the new application pool. The Application pool setting is available on the Advanced Setting in Actions Pane tab of the HTTPRequestResponseSample.
8. Start application pool.

To test if this is working I browsed to web application belonging to this sample.

I entered 1000 in Estimated Price and clicked Place Order.

I got the above as result. I check in BizTalk Administration Console if any error occurred, but I could find any. So I can conclude from that this sample work in my x64 environment. I hope this little exploration of HTTP Adapter in 64-bits environment will help others when configuring their HTTP configuration.

Technorati: biztalk server 2010

SQLExecute Oracle 11g XE using WCF OracleDbBinding

In previous post I detailed polling an Oracle 11g XE (Express) database using OracleDbBinding in WCF-Custom Adapter. Now I like to share how I can perform SQL statements through SQLExecute operation. You can run parameterized SQL statement on an Oracle database through WCF-Custom with OracleDbBinding. This binding supports the SQLExecute operation, which acts on an input parameter block comprised of parameter sets that enable execution of the same SQL statement once for each set. The operation will in the end return the results of the SQL statement in a generic record set.

The SQLEXECUTE operation is surfaced under the root node (/) in the Select a category pane in the Consume Adapter Service BizTalk Project Add-in. To be able to use this operation through orchestration or messaging in BizTalk you need to create a schema. The following steps will lead to creation of this specific schema:

• Right click project, and then Add –> Add Generated Item –> Consume Adapter Service –> Add.
• This action opens a new window that provides us the interface to connect to, browse, and select database objects for service interaction. The very first thing we need to do here is choose the oracleDBBinding as the service binding, and then configure a connection string. The simplest working connection string consists of an Initial Catalog value for the database, and a valid Server name entry. Note that the adapter now uses a connection string in the form of a URI instead of the traditional Data Source=;Initial_Catalog=;User_ Id=;Password= style.
• Once a satisfactory connection string is defined, the Connect button can be clicked to establish an active connection to the target database. If the connection is successful, one see the category browser with a set of database object categories.
• Select root node of category pane belonging to Client (outbound operations) and you will be able to find SQLExecute in Available categories and operations pane.
• Click Add and the OK.

• Binding file and schema’s will be generated.

• You could opt to build and deploy your schema’s.

I created a BizTalk project, generated the SQLExecute schema’s and then provided the project with a name. I signed the project with strong name and deployed it. I imported the generated binding into the new BizTalk application and reviewed the configuration of Send Port that was created.

It is important that credentials for accessing Oracle 11g XE are present. If not you will be confronted with error depicted below.

You add credentials with sufficient authorization in credentials tab (I have used system).

Last but not least in Binding tab change the useAmbientTransaction property from True to False, otherwise you will run into a exception, see my last post.

I added a filter expression to the send port as I want to SQLExecute Operation in BizTalk completely message based; filter expression is:

BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/SQLEXECUTE#SQLEXECUTE

Next a receive port and location is created. Latter is configured using FILE Adapter to pickup message with SQLEXECUTE statement. Finally a send port is created to sent response of SQLEXECUTE to file. This port has a filter on response message (type); filter expression is:

BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/SQLEXECUTE#SQLEXECUTEResponse

Messaging based SQLExecute application in BizTalk is now configured and will be started. Next I copy message below to in folder, where it will be picked up and routed to send port. Message will arrive at Oracle 11g and SQLStatement will be executed the resulted will be returned to send port and routed to other send port that has subscription on response message. Response message is depicted below also.

SQLEXECSTATEMENT

SQLEXECSTATEMENTResponse

I hope this little detail of my experience with SQLExecute operation using BizTalk 2010 and its 2010 Adapter Pack can be helpful in your exploration of this operation in Oracle 11g. I have based my detail here on sample (Invoking SQL Statements Using SQLEXECUTE) you can download from BizTalk Adapter Pack 2010 samples. I did not use orchestration example provided nor the schema’s.

Polling Oracle 11g XE with OracleDbBinding

In previous post I detailed how to perform some table operations in Oracle 11g using OracleDbBinding (WCF). I now like to show how to do polling in Oracel 11g XE. You can download a number of BizTalk Server 2010 Adapter Pack samples and I will use the Polling an Oracle Database sample. What sample will do is configuring a polling transaction (query) that targets the <schema name>.ACCOUNT_PKG.ACCOUNTACTIVITY table. For each polling transaction, the following actions are performed:

• Executes a polling statement specified for the PollingStatement binding property. The specified polling statement is a query that selects all records from the <schema name>..ACCOUNTACTIVITY table. The response for the select query is copied to a FILE location.
• Executes a post poll statement specified for the PostPollStatement binding property. The post poll PL/SQL code block moves the records in the query result set to the <schema name>..ACTIVITYHISTORY table.
• Execute the polling statement again after the timeout specified as the PollingInterval binding property.

You will see SCOTT in stead of <schema name> in the readme fill, but reason I am saying <schema name> here is that in Oracle 11g you need to create schema your self as there is no SCOTT. You basically have to do the following:

• Oracle Database 11g Expression Edition –> Getting started
• Select APEX and login with SYSTEM account
• Select a new database user, fill in APEX username and password (I used POLLING)
• Create a new workspace
• You will then login into the workspace
• Select SQL Workshop –> SQL Commands
• Here you have to paste the create_account_package commands

You will notice when trying to execute the sql script that you sometimes have to execute them one by one or leave out semicolon. In SQL Workshop Object Browser you will see the following:

As soon as you have executed the commands we will create an application through BizTalk Administration Console and name it PollingQuery. As soon as application is created you can import binding file belonging to solution. You will need to change value inside <ApplicationName> element to PollingQuery. When binding file is imported you can see that a receive port and send port is created. Go to receive location and double click. Go to binding tab and review in the OracleDBBindingConfigurationElement the polledDataAvailabelStatement, pollingStatement and postPollStatement (you can leave them as is).

Now you can open the pollingquery visual studio 2010 solution. You will notice in solution a BizTalk project with Orchestration and Schema, both can be deleted as we will create polling schema ourselves. Then following steps will lead to creation of polling schema:

• Right click project, and then Add –> Add Generated Item –> Consume Adapter Service –> Add
• This action opens a new window that provides us the interface to connect to, browse, and select database objects for service interaction. The very first thing we need to do here is choose the oracleDBBinding as the service binding, and then configure a connection string. The simplest working connection string consists of an Initial Catalog value for the database, and a valid Server name entry. Note that the adapter now uses a connection string in the form of a URI instead of the traditional Data Source=;Initial_Catalog=;User_ Id=;Password= style.

• Last thing to do is go to Binding Properties and fill in polledDataAvailabelStatement, pollingStatement and postPollStatement. This will depend on which schema name you use, so SELECT * FROM <Schema name>.ACCOUNTACTIVITY FOR UPDATE

• Once a satisfactory connection string is defined, the Connect button can be clicked to establish an active connection to the target database. If the connection is successful, one see the category browser with a set of database object categories. If you select (in my case POLLING) category than you will see in Package the ACCOUNT_PKG.

• For creating polling schema we will have to change contract type to Server(Inbound Operations).
• Select POLLINGSTMT from Available categories and operations and click Add

• Click OK and schema and binding file will be created.
• Create a strong-name key file required to build and deploy the solution.

The newly created binding is what we will use, and therefor the receive port in PollingQuery application will be deleted. Import the created binding in the PollingQuery application and review binding

As you can see the polling statement and post poll statement are based on <Schema name>.Table name or Package name (in my case schema name was Polling). In the Other tab you need to set the user account.

Another thing to do is adjust the Send Port (FILE Adapter require correct file location for the response) and filter expression for message it will subscribe to; in this scenario filter expression is: BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/POLLINGSTMT#POLLINGSTMT.

Final step is starting the application and monitor the folder. For a couple minutes I saw nothing, so I checked the eventlog and found the following Error:

I changed useAmbientTransaction property in binding from True to False. Next I noticed a xml file in folder I was monitoring.

There you go polling data from Oracle 11g database using OracleDbBinding (WCF-Custom). I hope this little detail of my experience with polling data from Oracle 11g database using BizTalk 2010 and its 2010 Adapter Pack will be useful if you yourself are exploring it. If you have to work with packages in Oracle 11g using BizTalk 2010 I suggest to read this excellent post by TenaliNaga!

See Code Gallery sample for solution described in this post.

Technorati: biztalk server 2010

Passed BizTalk 2010 exam (70-595)

Today I passed the BizTalk 2010 exam and I am certified technology specialist again. In the past I passed the BizTalk 2006 (70-235) and BizTalk 2006 R2 (70-241), but both of them where retired just recently (30th of June 2011). I prepared my self using resources you will find in one of my previous posts. Having a virtual machine with BizTalk 2010 with a lot samples is a great help in preparing. It was a challenging exam with some though questions I had to think about, but also a lot I could answer easily through experience. Any one that will take the exam in near future I would like to wish them good luck and success. It is definitely worth the effort!

Technorati: biztalk server 2010

BizTalk Server MVP 2011

Just now I have received an e-mail from Microsoft with exciting news that I’ve been re-awarded the MVP title.

For me this is the second time to receive this award. The first year has been an tremendous experience, which gave me the opportunity to do great things and meet inspiring and very skilled people.

I would like to thank:
- my MVP leads William Jansen 
- Ed Hickey and all other Microsoft employees involved
- people at Ordina Netherlands and Bert van de Belt in particular for all his support
- fellow MVP's Randal, Saravana, Richard, Kent, Stephen, Alan, Mikael, Johan, Oliver, Thiago, Ben, Jon, Jeffrey and Sandro, who I got learn better and supported and helped me out in this program
- and finally my wife and children Stan, Ellis and Cato for their support

I’m looking forward to another year in the program.