Friday, December 19, 2008

Getting Started with Mirth (Part 2)

December 19, 2008 Posted by Jason Irwin , , 7 comments
This is the second post in a 2 part series. The first post is available here
To continue with our Mirth messaging channel, lets first describe the setup of the web service which will be called by our Mirth channel. This is somewhat of a contrived example will little attention to coding standards, referential integrity or database normalization…but we wont let that stop us!
Firstly, I’ve created a second database named demodetailsdb. Ideally these databases would be on disparate servers (otherwise there would be little need for such an elaborate messaging system) but for our purposes I will run both off the same (localhost) box. In the demodetailsdb I have created a single user_details table.
Loading ....
I have created a simple .NET Web Service to which we will pass the information gathered from the database in our previous post, perform some simple lookups in the user_details table based on the input data and return relevant information. The service contains one webmethod named GetUserData which does exactly as the name suggests – it takes returns user information based on the input parameters. Below is the ASMX.vb file:
Loading ....
The ASMX.VB file calls a class named dummyClass used to perform the actual database connection and call the GetUserInfo stored procedure.
Loading ....

All returned information is passed in the form of a dummyUserClass object which returns the DOB, SSN and address of the input user. The dummyUserClass code looks as follows:
Loading ....

Now that we have the web service and database set up the way we want them (the tables aren’t yet populated – i’ll leave that up to you…) we can move on to the destination tab of the Mirth we previously created. By default you’ll have a placeholder destination created for you. image

We want a SOAP connector type, so choose SOAP from the connector type dropdownlist. You’ll notice that the screen refreshes with controls specific to our new connector type. Here’s where things start to get cool…There are a number of ways to create your soap envelope. By far the most simple is entering the path of your WSDL definition file. Doing so and clicking Get Methods results in the method dropdown being populated with all of the web methods defined in the WSDL file – on selection of a method the SOAP envelope is automatically generated (and is editable) and a tree is created allowing parameters to be passed to the method with next to no work on the developer’s part.
image

For demonstrative purposes we’ll create a couple of transformers and filters on the destination and we’ll be ready to talk to our web service. First of all the filter. One of the fields we pulled from the database was created date. This may correspond to an employees start date or entry into payroll. In our case we don’t want to process any users whose created date is in the future. There are two types of filters: 1. Rule based filters 2. Javascript filters. The first are great for smaller quicker tasks, the second when you need more functionality.

We use some simple Javascript to check that today’s date is greater than or equal to the created_date field. If so we return true to indicate that we accept the record, otherwise false to reject the record…simple! (note: in order to access the inbound message i had to enter the expected xml - the output template from the source - into the message template textbox. I remember this being automatically generated and am using 1.8.0 RC3 right now, so you might not need to take this step.)
image

The functionality contained in the following transformers can be achieved in a number of different ways – it is included here for demonstrative purposes. Simply, we will define channel level variables for the fields we wish to pass to our web service. For instance, dragging the value node from the id field into the top-left pane creates an id mapping. Doing the same for first_name creates another mapping and so forth…
image


Returning to our SOAP sender (exiting the transformer screen…) you will now notice the variables that we mapped in the Destination Mappings list. From here it is a simple matter of dragging the relevant variable from this list into the Value field (highlighted in red below) of the corresponding variable in the SOAP web method signature. For the easily frustrated: 1) Make sure to click in the value field to give the field focusing before dragging and dropping 2) After dragging the variable into the value field hit return, otherwise the assignment wont hold.
image

Once all of the relevant variables have been mapped and assigned, your SOAP envelope (bottom-most textbox) should look something like this:
Loading ....
In terms of calling the web service, we are done…pretty simple, right? At this point you can save the changes to your channel.

The final piece of the puzzle is handling the web service response. For the sake of this post lets take the returned data and log it in a wsvc_responses table in the demodb database that we created for the first post. I defined the table as follows:
Loading ....

Back in Mirth create a second channel named WSVC Response Handler. Again, the data type will be XML and you can enter a description of your choosing. Under the summary tab make sure to uncheck Strip namespace from messages. If you leave this checked then the channel will not know how to handle the SOAP namespace and will inevitable error out.

Under the source tab choose Channel Reader. The output of our web service sender channel will become the input for this channel, so there is no need to specify any other data connections. In the source tab create a transformer and paste the SOAP you expect to receive from your web service in the Inbound Message Template field. This information will be available through the web service API, or in the case .NET web services, the ASMX page itself:
image

The resulting Inbound Message Template Tree will look like this:
image

Repeating one of our steps above, we want to create variable mappings for the fields of import. In this case there is a gotcha: Mirth doesn’t handle SOAP in the most predictable of fashions (specifically where namespaces are concerned) so it is better to use relative rather than absolute paths where variable assignments are concerned.
image

Once you have defined mappings for all of the fields of interest you can finish off your connector by modifying the destination of the channel to point to your database and insert the data into the wsvc_responses table that we created earlier. It will look a little like this:
image

Save this channel and return to the original sender channel that we used to send the data to the web service to begin with. In order to hook the two channels up, choose your response handler channel from the Send Response To dropdownlist.
image

Save your channel and return to the Channels screen. Right-click anywhere on the screen and choose deploy all.

image
After deploying you will be routed back to the dashboard where you can monitor your channels. You’ll notice that we’ve sent 5 records (there were 5 entries in the mytable table) from the sender channel and all 5 were received and sent (to the database) by the response handler channel. One record was filtered by the sender channel and there were no errors.
image
Double clicking on either channel name in this list allows further drilldown. Not only can we view our data for each user at each step (source/destination) of the transformer but we can also view the raw/transformed/encoded message and any variable mappings and errors encountered. Lets look at the filtered record. Double clicking on the sender channel we can see one record with the status ‘filtered’. This record was for the user Tom Smith. His created date is January of 2009 – greater than today’s date, therefore falling into our filter criteria.
image
The ability to drill down into the information is key to debugging. Below you’ll see the SOAP response for the user Mark Jones. We can see exactly what information was sent back from our web service and also what variable mappings were made.
image
Finally, looking in our wsvc_responses table we can see that all of our records have been processed and the web service responses logged to the database:
image
To summarize, we’ve taken data from a SQL Server database, packaged it up in a SOAP envelope, sent it to a .NET web service, awaited response on an asynchronous channel and logged the parsed response into another database. Moreover, we have the ability to monitor our Channels and delve deep into the details should the needs arise. Pretty sweet for 20 minutes work!

Thursday, December 18, 2008

Getting Started with Mirth (Part 1)

December 18, 2008 Posted by Jason Irwin 7 comments

This is the first post in a 2 part series. The second post is here

Recently I posted about my positive experience when using Mirth by Webreach as an open source communication layer between one of our in-house applications and a third party and proprietary web service interface. That post received a large amount of attention (relative to my other posts :o) ) so now I will post a quickstart guide to creating a similar setup.

In this post I will go over the very basics of using Mirth (please dig in and play around with some more advanced options/configurations), specifically I will demonstrate how to use Mirth to read data from a database, package the data up in a SOAP envelope, send the data to a web service and process the SOAP response. I'll also show you how easy it is to monitor channels once deployed.

Once you've installed and opened Mirth for the first time you'll be presented with an empty dashboard - but it wont stay empty for long. This is where you'll see the status of your live channels, so let's build one! Part 1 of this post will deal with the channel source. Part 2 will deal with the destination.

image

Clicking on the 'Channels' link on the LHS opens a blank pane. Right-click anywhere in the blank pane and choose 'New Channel'.

image

The screen that opens will present you with 4 tabs: summary, source, destination, scripts. First thing's first - enter some summary data. I named my channel ("Jason's Sender Channel"), changed the 'Incoming Data' type to XML (most other options are specific to medical information systems), added a description and chose to encrypt stored messages and prune them from the database after 5 days. That's all I need from the summary tab for right now.

image

The source tab is where you'll specify where your data is coming from. There are plenty of options here as you can see from the (partially) displayed dropdownlist in the screenshot below. For my purposes I'll need a database reader.

image

For a SQL Server database I choose SQL Server/Sybase as the Driver. The URL is of the format jdbc:jtds:sqlserver://<host>/<database>. In my case it goes as follows:

jdbc:jtds:sqlserver://localhost/demodb

For demonstrative purposes i'll set the polling type to interval and set it to 10000 ms. Otherwise I could set it to Time and have the channel run at a given time every day. There are two choices for how the query itself will be written: Javascript or SQL. I'll stick with SQL in this post. I've created a simple table with 3 fields in my demo database. The table creation script is as follows:

  1: USE [demodb]
  2: GO
  3:
  4: /****** Object:  Table [dbo].[mytable]    Script Date: 12/18/2008 22:49:20 ******/
  5: SET ANSI_NULLS ON
  6: GO
  7:
  8: SET QUOTED_IDENTIFIER ON
  9: GO
 10:
 11: SET ANSI_PADDING ON
 12: GO
 13:
 14: CREATE TABLE [dbo].[mytable](
 15:  [id] [int] IDENTITY(1,1) NOT NULL,
 16:  [name] [varchar](30) NOT NULL,
 17:  [isactive] [bit] NOT NULL,
 18:  [created_date] [datetime] NOT NULL
 19: ) ON [PRIMARY]
 20:
 21: GO
 22:
 23: SET ANSI_PADDING OFF
 24: GO
 25:
 26: 


Once completed, my source looks as follows:



image



Here's where things get cool...I want to specify what data gets passed to my destination and how I want that data to be formatted. The easiest way to do this is to use a transformer (on the menu on the LHS) on the source.



First thing is first: update your message template. In this case the inbound message template will be automatically created based on the database fields we are querying. I want to specify an outbound template that will be passed to the channel's destination. See the textbox outlined in red below:



image



To summarize, I changed the names of the field (my query prefixed them with the table name) and instead of using a single 'name' field, I created a 'first_name' and 'last_name' field.



Now that the message templates are ready there are numerous ways to map from the inbound message to the outbound message - all are performed in the message tree tab. The easiest by far is to drag a value from the inbound message template tree to the outbound message template tree. In the below screenshot I have dragged the value node under 'mytable_id' in the inbound tree onto the value node under 'id' in the outbound tree resulting in step #0 displayed in the middle section of the window. I will do this for the mytable_isactive and mytable_created_date fields.



image



To map the mytable_name to first_name and last_name we'll have to be a little trickier. Right-click the white space in the middle pane and click 'Add New Step'.



image



Name this new step and set its type to Javascript. Obviously you can then use the power of the javascript (and java too) language to perform the desired functionality. (TIP: Feel free to drag items from the message templates into the javascript editor window. This will provide you with the correct object names when assigning etc.).



I have added the following Javascript to split the string and assign the respective values to the first_name and last_name output fields



  1: combinedNameString = msg['mytable_name'].toString()
  2: arrName = combinedNameString.split(" ")
  3: tmp['first_name'] = arrName[0]
  4: tmp['last_name'] = arrName[1]


Our finished transformer will look as follows:



image



In the next couple of days i'll post part 2 which will demonstrate using the channel destination to call a SOAP method, passing this info wrapped up in a SOAP envelope. Thanks for reading..

Wednesday, December 17, 2008

Sysinternals in the Cloud

December 17, 2008 Posted by Jason Irwin 2 comments
 

In an interesting turn of events Microsoft's sysinternal tools are now available online (link) without needing to be installed...This is about as portable as it gets and makes me wonder what the "alternate distribution mechanism" as mentioned in the Microsoft readme file will be used for in the future. Office in the cloud anyone?

 

What is this?

This is a file share allowing access to all Sysinternals utilities.
We have developed this to test an alternate distribution mechanism 
for our utilities. This will allow you to run these tools from any 
computer connected to the Internet without having to navigate to a 
webpage, download and extract the zip file. If you are unfamiliar 
with Microsoft Windows Sysinternals, it is highly recommended that 
you visit the website at http://technet.microsoft.com/sysinternals 
before using these tools.

If you have any questions or comments on this file share, please email
syssite@microsoft.com

Regards,

The Microsoft Windows Sysinternals Team
 

SQL Server Reporting Services 2008 Palette Bug

December 17, 2008 Posted by Jason Irwin , , No comments

I've spent the greater part of my morning investigating an issue with SQL Server Reporting Services 2008.

Specifically, I have created a report which uses colors as physical cues on a timeline. If criterion X is met then the cell is filled in green, if criterion Y then it is filled in blue. A combination of both X and Y will result in an orange field...quite simple....

CropperCapture[6]

I have similar reports written in SSRS 2005 that are in production and used on a day-to-day basis. Recently we have adopted SSRS 2008 and the first of its kind that I have published using the newer version.

The Problem

Everything works as desired and I can export to Excel without issue. The report renders correctly and everything works as desired...until I attempt to copy the worksheet (right-click->move or copy) to another workbook. The worksheet is copied, but the colors change drastically so that the colors (white, orange, green and blue) are completely different in the newer version.

In my report I use the hexadecimal representation of standard excel colors (link) so there is no problem translating the colors themselves. The problem, it turns out, is that each report generated has a different color palette in excel.

Notice the differences between the following palettes:

CropperCapture[2]

CropperCapture[3]

The above palettes are taken from two excel spreadsheets generated with different criteria from the same report. Any attempt to combine these sheets into a single workbook fails as palettes overwrite the whole

The Solution

For once there appears to be no easy solution. I rewrote the report in SSRS 2005 and, lo and behold, this issue does not occur. This issue has been reported on the Microsoft Connect site (link) and I can only hope there is a resolution in the near future.

Monday, December 15, 2008

WPF Designer Woes - "could not create an instance of type..."

December 15, 2008 Posted by Jason Irwin , , , 1 comment

If you've delved into WPF since its inception a couple of years ago you will undoubtedly have run into...hmm, lets say...idiosyncrasies...with the WPF designer. Visual Studio 2008 certainly improved on the VS2005 plugin, but now and again bugs till pop up...

What is really frustrating is receiving an error message in the designer despite a project that successfully builds and runs...Today I received the following error message:

"could not create an instance of type UserSelect"

The designer refused to reload and the design time error pointed to the following lines of code in my MainWindow.xaml file. My 'usr' namespace was defined and correctly referenced but no amount of rebuilding fixed my problem.

  1:         <Canvas Name="UserCanvas" Height="38" VerticalAlignment="Top" HorizontalAlignment="Left" Width="150">
  2:             <pat:UserSelect x:Name="myUserSelect" Margin="0,0,0,0" Canvas.Left="50" Canvas.Top="10" HorizontalAlignment="Left" VerticalAlignment="Top" Width="450" />                    
  3:         </Canvas








After a little (manual) investigation I realized that the root of the issue was a remoting call which could not be evaluated at design time. The initialized event of the UserSelect class contained a call to a PopulateUsers method. This method contains a further call to a remoting object on a remote server used to connect to a database and retrieve a list of users visible to the users whose ID is passed to the method.









  1: _userId = Environment.UserName
  2: PopulateUsers(GetUserListByUserID(_userId))


As is usual the solution itself was 10% of the work (90% having been expended on the investigation). Using the GetIsInDesignMode function (link) a clause was added providing different logic for run and design modes. Running the logic through at run time resulted in the second execution path being taken,the remote object returning a list users and the dropdownlist being populated accordingly.









More importantly, execution of the code in the designer resulted in the first execution path being followed. Rather than calling the remote method, designer specific code was added, populating the dropdownlist with a single dummy record (John Smith).









  1:  Private Sub UserSelect_Initialized(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Initialized
  2:         If ComponentModel.DesignerProperties.GetIsInDesignMode(Application.Current.MainWindow) Then
  3:             ' Designer Logic
  4:             ' Manually add record to combobox
  5:             Me.cmbUsers.Items.Add(New User(123456, "John Smith"))
  6:         Else
  7:             ' Production logic
  8:             _userId = Environment.UserName          
  9:             PopulateUsers(GetUserListByUserID(_userId))
 10:         End If      
 11: End Sub








Following a rebuild of the UserSelect component and a reload of the MainWindow designer (I did not close this window before rebuilding, therefore a reload was necessary and prompted for) the issue was resolved and the error message no longer appeared.