ColdFusion and ASP.NET Web Service Interoperability, Part III

Yesterday I described some of the problems I was having getting ColdFusion to talk to an ASP.NET web service as well as a brute force solution to the issue. Well my former co-worker Phil Duba left a comment on that post pointing to the follwing article: Notes on Interfacing ColdFusion MX to External Web Services Requiring Complex-within-Complex XML Documents as Input. This article turned out to be the key to my getting this web service call to work using <cfinvoke>.

[More]

ColdFusion and ASP.NET Web Service Interoperability (or Lack Thereof), Part II

Yesterday I was talking about some of the problems I had getting a service provider's ASP.NET client to work with my company's ColdFusion web service. Once we had that issue resolved the next step was to get our CFMX 6.1 application working with their ASP.NET web services.

Here is a sample SOAP 1.1 request and response for one of their web service methods. (This is from the ASP.NET auto generated documentation):

POST /service.asmx HTTP/1.1
Host: their.service.com
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://tempuri.org/Company_Add"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Company_Add xmlns="http://tempuri.org/">
<txtCompanyName>string</txtCompanyName>
<txtCompanyAddress1>string</txtCompanyAddress1>
<txtCompanyAddress2>string</txtCompanyAddress2>
<txtCompanyZip>string</txtCompanyZip>
<txtCompanyFax>string</txtCompanyFax>
</Company_Add>
</soap:Body>
</soap:Envelope>

----------------

HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Company_AddResponse xmlns="http://tempuri.org/">
<Company_AddResult>int</Company_AddResult>
<txtErrorMessage>string</txtErrorMessage>
</Company_AddResponse>
</soap:Body>
</soap:Envelope>

I originally tried to call this service using <cfinvoke> however I kept getting the following error:

Web service operation "Company_Add" with parameters {txtCompanyName={Test Company}...} could not be found.

I got this error no matter how I tried passing in the parameters so what I ended up doing is taking the brute force approach. I created a cfc which just wrapped <cfhttp>calls to the webservice. Below is an example of what I did.

<cfcomponent>

<cffunction access="public" name="int" output="false" returntype="mywebservice">
<cfargument name="URL" type="string" required="true">
<cfset instance = StructNew() />
<cfset instance.URL = arguments.URL />
<cfreturn this />
</cffunction>

<cffunction access="public" name="companyAdd" output="false" returntype="struct">
<cfargument name="companyName" type="string" required="true" />
<cfargument name="companyAddress1" type="string" required="false" default="" />
<cfargument name="companyAddress2" type="string" required="false" default="" />
<cfargument name="companyZip" type="string" required="false" default="" />
<cfargument name="companyFax" type="string" required="false" default="" />

<cfset var requestXML = "" />

<cfsavecontent variable="requestXML">
<Company_Add xmlns="http://tempuri.org/">
<txtCompanyName>#arguments.companyName#</txtCompanyName>
<txtCompanyAddress1>#arguments.companyAddress1#</txtCompanyAddress1>
<txtCompanyAddress2>#arguments.companyAddress2#</txtCompanyAddress2>
<txtCompanyZip>#arguments.companyZip#</txtCompanyZip>
<txtCompanyFax>#arguments.companyFax#</txtCompanyFax>
</Company_Add>
</cfsavecontent>

<cfreturn makeRequest("Company_Add",requestXML) />

</cffunction>

<cffunction access="private" name="makeRequest" output="false" returntype="struct">
<cfargument name="requestType" type="string" required="true" />
<cfargument name="requestXML" type="string" required="true" />

<cfset var httpXML = "" />
<cfset var cfhttp = "" />
<cfset var resultXML = "" />
<cfset var resultStruct = StructNew() />

<cfsavecontent variable="httpXML">
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>#arguments.requestXML#</soap:Body>
</soap:Envelope>
</cfsavecontent>

<cfhttp method="post" url="#instance.URL#">
<cfhttpparam type="header" name="SOAPAction" value="http://tempuri.org/#arguments.requestType#">
<cfhttpparam type="XML" value="#httpXML#">
</cfhttp>

<cfset resultXML = XMLParse(cfhttp.FileContent) />
<cfset resultStruct.result = resultXML.Envelope.Body['#arguments.requestType#Response']['#arguments.requestType#Result']['XmlText'] />
<cfset resultStruct.errorMessage = resultXML.Envelope.Body['#arguments.requestType#Response']['txtErrorMessage']['XmlText'] />

<cfreturn resultStruct />

</cffunction>

</cfcomponent>

I'm definitely not a SOAP expert and I don't know a whole lot about ColdFusion's underlying Apache Axis web service engine, so if anyone has other suggestions on how to handle this, or any insight whatsoever as to what the issue is I'd be glad to hear it.

ColdFusion and ASP.NET Web Service Interoperability (or Lack Thereof), Part I

For the past few weeks I've been doing some work with a third party service provider trying to integrate their offerings into an in-house application via web services. Their application is written in ASP.NET and ours is written in ColdFusion which has lead to some interesting challenges.

The first issue we had was getting their .NET application to talk to our ColdFusion web service. At first the provider reported that they were seeing the following error when calling our web service:

The underlying connection was closed: An unexpected error occurred on a send.

They were attempting to call our web service every ten minutes, however after looking in our web server logs I was able to determine that they had successfully connected to the web service only once. After doing some research I found a MS Knowledge Base Article which lead me to believe this error was due to a connection timeout. Our server's keep-alive setting was set at 5 minutes. If they were only calling our web service every 10 minutes the connection the client originally opened was being closed by our server. When I pointed out this article and described the issue the provider agreed to have their developers rewrite the calling code in ASP.NET 2.0, which apparently handles the keep-alive properly.

However once the client had been re-written, they reported a new issue. They were now seeing the following error:

The request failed with an empty response.

Just to confirm that the issue wasn't on our end I wrote a quick ColdFusion client which was able to call the web service with no problem. I looked at our logs again and could see that they were not even getting to our web service. This lead me to believe the issue was still in the calling code, but unlike the previous error google didn't turn up anything that seemed to point directly to the issue. At this point I decided to download VB 2005 Express and build a .NET client myself. Having done zero .NET development it took me a few minutes to figure out how to connect to a web service via VB .NET, but once I did it was fairly easy.

In Visual Studio .NET you create a Web Reference to work with web services. Adding a Web Reference is a lot like adding a COM, DLL, or ActiveX reference to you project; basically you just click on Project -> Add Web Reference and type in the URL of the web service you want to work with in the Web Reference window and you are all set. Well almost. Our web service need to be called overSSL so the URL I entered for our Web Reference looked something like "https://our.service.com/component.cfc?wsdl." However whenever I tried to call the web service in my VB client I was getting the same "empty response" error our service provider was reporting. So, I fired up a network sniffer (Ethereal) to see if I could figure out what was going wrong. It turns out that even though I was typing in "https://our.service.com/component.cfc?wsdl" for my Web Reference the VB.NET client was calling "http://our.service.com/component.cfc?wsdl." Security in our application was then trying to redirect the client to the secure URL via <cflocation>, hence the empty response. Now that I knew what the issue was I just had to figure out how to fix it. Turns out it was pretty simple. The VB.NET project has an app.config file which holds, you guessed it, application configuration. One of the entries in this file was the web service URL and upon inspection I found that it was listed as "http://our.service.com/component.cfc?wsdl." I manually edited this entry to use https instead of http and that did the trick. I then notified the provider and they made the corresponding change on their end an we were in business.

This has turned into a bit of a .NET post, but I thought it was worth sharing in case you have others trying to consume your ColdFusion web services via .NET. Up next, what we had to do to get ColdFusion to talk to the service providers .NET web services.

Updated SQL Explorer Extension

Well, I found out some other people are using my CF Debug SQL Explorer Extension so I'm releasing this update. In my original extension I forgot to escape single quotes, so this version fixes that. (I noticed I forgot to handle this while looking through Ben Nadel's code for a bookmarklet which does something similar to this extension.) This release also includes the source if anyone is interested, just look in the .jar. Installation instructions are still the same and you can find those in the related entry. Enjoy!

ColdFusion Debug SQL Explorer Extension

Have you ever wanted to be able to copy a query from ColdFusion's debugging output and run it in SQL tool to see the results? No big deal really, unless you happen to be using cfqueryparam. (You are using cfqueryparam aren't you?) If your query does use cfqueryparam you will notice that the SQL listed in the debugging output has question marks wherever you used a queryparam. The type and value of each queryparam is listed in the debugging output just below the query, but in order to run your query in a SQL tool you have replace each of the question marks in the SQL statement with the actual queryparam value, remembering to quote values as necessary. Still not a big deal, until you have to do this for a 500 line SQL statement with 50 or more queryparams. Then it gets to be a little annoying.

[More]

Querying Excel Spreadsheets

A few weeks ago I had the need to query an Excel spreadsheet, several actually. There are a few different approaches you could take to get the Excel data into a ColdFusion recordset. You could import the spreadsheet into a database and query the database, or if you are handy with Java you could use Jakarta POI (see Matt Liotta's ExcelQuery CFX tag on OpenXCF for an example), but for what I was doing these options seemed like overkill. The approach I ended up taking used a dynamic datasource which allowed me to query my spreadsheets like so:

<cfquery name="test" datasource="dynamicXLS">
SELECT FirstName, LastName
FROM [Sheet1$]
IN 'C:\myXls.xls' 'EXCEL 8.0;'
</cfquery>

You can find out how to do this in a TechNote on the Adobe site. One thing I will note, I'm using Excel 2003 (version 11.0), however the Microsoft Excel Driver used in the Windows XP ODBC Data Source Administrator only allows you to select up to Excel 97-2000. I found that this limited me to using 'EXCEL 8.0;' in my cfquery statements, but it seems to work fine.

UPDATE: I guess I missed this but Rob Gonda had a post on this same topic just a few months ago. He describes one additional method of using the JDBC drivers directly and provides a sample UDF based on this approach.

User Group Manager

Phil Duba has started a new project over on RIAForge for a user group manager. You can find out more on the project page, but the basic idea is to provide a site for managing user group activities. I volunteered to help Phil with this project and I'll be working not only with Phil but also Vadim Chernets, another former co-worker from VirtualEdge.

Phil is going to start using the project's blog to gather requirements but if you have anything you would like to see in user group manager application be sure to let Phil know and we will see what we can do.

I'm looking forward to working on this project as it should give the excuse I've needed to finally dive into Model-Glue. (I've played around with MG but have yet to use it on a project.) Look for more posts as we begin development.

Auto Generating Unit Tests With cfcGenerator

Earlier this week Peter Bell asked if anyone was doing anything in the way of auto generating unit tests. Well, I've been using Brian Rinaldi's cfcGenerator for a project at work and early on I realized that having some unit tests for all the components I was creating with the tool would probably be a good thing. If you haven't seen Brian's tool it is very cool. Out of the box it generates Bean, DAO, Gateway and Service objects for a database table as well as a ColdSpring snippet which you can use in your bean definition XML file. The really great thing about this tool is that it is very easy to extend and customize. So knowing I really should be building unit tests and seeing how easy it was to work with the cfcGenerator I decided to download cfcUnit spent a couple of hours hacking together a way to generate some unit tests using the cfcGenerator.

[More]

DST Confusion

Last week I posted about some Java alternatives to CFDIRECTORY and ConvertDate. I posted a snippet of code which attempted to show how to get the DateLastModified information for files using Java API calls. One of my readers pointed out that the code I posted may not return the correct time given that I was applying the current DST offset to the lastModified value and not the DST offset in effect when the file was written.

I quickly updated my post to attempt to use the DST offset in effect when the file was modified. However after testing I found that this snippet was not returning the correct lastModified time.

[More]

Java Alternatives to CFDIRECTORY and ConvertDate

On Tuesday I was having a discussion with a coworker and somehow it came up that he was having issues using cfdirectory on a rather large directory of log files. I pointed him to Mark Kruger's Java Based Directory List blog post which I had just read last week. He took a look and sent back the following snippet:

[More]

More Entries

BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.