SQL Server 2005 Endpoints and ColdFusion 8

So I have spent the last month fighting with ColdFusion 8 and its irreverence for SQL Server 2005's XML/SOAP Endpoints.  Mind you, I agree with CF's point - Microsoft's implementation is wonky and *of course* unlike every open standard.  

That being said, I had to be able to get along with those IT folks in the main office, so I had no choice.  Here's what I did to make CF8 consume a SQL Server Endpoint.

The first thing is to make sure you are using a valid SSL Certificate (you can use a self-signed certificate, but it has to be imported into the java cacert file...).  Then make sure SQL Server is setup to accept Basic Auth.  Digest, NTML, and Kerberos didn't work for me.

After that basic stuff is out of the way, create your endpoint in the SQL Server... The default configuration seems to be ok.  My only recommendation is to make sure it always returns a dataset.  Anything else will likely bust the converter function described below.

You should know that you cannot use CFINVOKE.  Why?  Because CFINVOKE seems to want to use HTTP/1.o which SQL Server's webserver refuses to service.

So use a CreateObjet ("webservice"....) block instead.  Here's an example:

<cfscript>
 wsargs = structnew();
 wsargs.refreshwsdl="yes";
 wsargs.username="yourlocalaccountusername";
 wsargs.password="yourlocalaccountpassword";
  
 ws = CreateObject("webservice", "https://webservice.yourcompany.com/sql/whatever_endpoint?wsdl", wsargs);
 ws._setProperty("axis.transport.version", "1.1");
 resultSet = ws.JJData().getSqlRowSet();
</cfscript>

Of course, the secret to this is that little line "axis.transport.version", "1.1" -  that's forces Axis to communicate using HTTP/1.1.

You should be able to execute the webservice and get something back.  It won't be a query quite like you were hoping for... instead it will be proprietary DataSet-esque XML set.  Don't forget, if you want to look at what goes in and out of your query, use the GetSOAPResponse and GetSOAPRequest functions.  They basically return a parsed XML structure that ColdFusion displays nicely with CFDUMP.

To convert the dataset to a query object, I borrowed much of Joe Rinehart's code that he used in "ConvertDotNetDataSet" to create "ConvertSQLEndpointDataset"... Here's the code for it:

<cffunction name="convertSQLEndpointDataset" access="public" returnType="query" output="false" hint="takes a .Net dataset and converts it to a CF structure of queries">
 <cfargument name="dataset" required="true">

 <cfset var newQueryObject = queryNew("") />
 <cfset aDataset = dataset.get_any() />
 <cfset xSchema  = xmlParse(aDataset[2]) />
 <cfset xData  = xmlParse(aDataset[3]) />

  <cftry> 
   
   <!--- Create Queries --->
   <cfset xTables = xSchema["xsd:schema"]["xsd:element"]["xsd:complexType"]["xsd:sequence"] />
   <cfloop from="1" to="#arrayLen(xTables.xmlChildren)#" index="i">
    <cfset tableName = xTables.xmlChildren[i].xmlAttributes.name />
    <cfset xColumns = xTables.xmlChildren[i].xmlChildren[1].xmlChildren[1].xmlChildren/>
    <cfloop from="1" to="#arrayLen(xColumns)#" index="j">
     <cfset queryAddColumn(newQueryObject, xColumns[j].xmlAttributes.name, arrayNew(1)) />
    </cfloop>
   </cfloop>
  <cfcatch>
   <cfthrow message="Schema not defined in the result set.  JJ-fConvertDotNetDataset">
  </cfcatch>
  </cftry>
  
  <cftry>
   <!--- Populate Queries --->
   <cfset xRows = xData["diffgr:diffgram"]["SqlRowSet1"] />
   <cfloop from="1" to="#arrayLen(xRows.xmlChildren)#" index="i">
    <cfset thisRow = xRows.xmlChildren[i] />
    <cfset tableName = thisRow.xmlName />
    <cfset queryAddRow(newQueryObject, 1) />
    <cfloop from="1" to="#arrayLen(thisRow.xmlChildren)#" index="j">
     <cfset querySetCell(newQueryObject, thisRow.xmlChildren[j].xmlName, thisRow.xmlChildren[j].xmlText, newQueryObject.recordCount) />
    </cfloop>
   </cfloop>
  <cfcatch>
   
  </cfcatch>
  </cftry>


 <cfreturn newQueryObject>
</cffunction>

So put those two little tid-bits together and you get this:

<cfscript>
 wsargs = structnew();
 wsargs.refreshwsdl="yes";
 wsargs.username="yourlocalaccountusername";
 wsargs.password="yourlocalaccountpassword";
  
 ws = CreateObject("webservice", "https://webservice.yourcompany.com/sql/whatever_endpoint?wsdl", wsargs);
 ws._setProperty("axis.transport.version", "1.1");
 resultSet = ws.JJData().getSqlRowSet();
</cfscript>

<cfset returnedquery = convertSQLEndpointDataset(resultSet)>

Dump the "returnquery" and set "refreshWSDL='no'" and I bet you'll feel the weight of world lift from your shoulders.

Happy coding!

John J