I am using Brian Rinaldi's coldfusion function to convert a dotnet webservice dataset into a structure of queries. Each query is then returned to the client page as JSON to be used in a jQuery function.
The queries are valid query objects. However, JSON is not being returned. Instead I get WDDX as follows:
<wddxPacket version='1.0'>
<header />
<data>
<recordset rowCount='31'
fieldNames='startdate,starttime,subscribercode,dest_number,description,ConnDuration,Callcharge,Usage,ConnType,pages,CallReference,SettingCount'
type='coldfusion.sql.QueryTable'>
<field name='startdate'>
<string>2010-01-30T00:00:00+13:00</string>
<string>2010-01-29T00:00:00+13:00</string>
</field>
</recordset>
</data>
</wddxPacket>
using the following code:
function internetUsage(){
$.getJSON("system.cfc",{
method:'getInternetUsage',
SessionID:$("#vSessionID").val(),
CustomerCode:$("#vCustomerCode").val(),
FullUserName:$("#selUser").val(),
StartDate:$("#vStartDate").val(),
EndDate:$("#vEndDate").val(),
returnformat:'json',
queryformat:'column'
},function(res,code){
alert('hello'); // THIS NEVER FIRES!
});
}
So, I tried getting the CFC to convert the query into JSON and returning the JSON-ified result. This worked a little better in that valid JSON was returned BUT it is still wrapped in <wddxPacket> tags, as follows:
<wddxPacket version='1.0'><header/><data><string>
{
"recordcount": 31,
"columnlist": "callcharge,callreference,connduration,conntype,description,dest_number,pages,settingcount,startdate,starttime,subscribercode,usage",
"data": [
{
"callcharge": "",
"callreference": "",
"connduration": 86403,
"conntype": "UBS",
"description": "dageorgetti",
"dest_number": "",
"pages": "",
"settingcount": 5,
"startdate": "2010-01-30T00:00:00+13:00",
"starttime": "2010-01-30T00:00:00+13:00",
"subscribercode": "dageorgetti",
"usage": 33.7300
}...<snip>...
...<snip>...
</string></data></wddxPacket>
The call for achieving the above is as follows:
function internetUsage(){
$.getJSON("system.cfc",{
method:'getInternetUsage',
SessionID:$("#vSessionID").val(),
CustomerCode:$("#vCustomerCode").val(),
FullUserName:$("#selUser").val(),
StartDate:$("#vStartDate").val(),
EndDate:$("#vEndDate").val(),
jsonEncode:true // the cfc converts query to JSON
},function(res,code){
alert('Hello'); // still not firing
});
}
I am using returntype="JSON" in the CFC. The cfc is quite complex and I don't think I need to paste it here. I can confirm that it is definitely generating valid query objects, which the conversion function seems to be successfully converting to valid JSON. I don't know why it comes back to the client wrapped in wddxPacket tags.
EDIT - CFC
<cffunction name="invokeInternetUsage" access="remote" returnType="any" output="false">
<cfargument name="SessionID" required="true">
<cfargument name="CustomerCode" required="true">
<cfargument name="FullUserName" required="true">
<cfargument name="StartDate" required="true">
<cfargument name="EndDate" required="true">
<cfset var aTemp = "">
<cftry>
<cfinvoke
webservice="http://Portal/internet.asmx?WSDL"
method="Usage"
returnvariable="aTemp">
<cfinvokeargument name="SessionID" value="#arguments.SessionID#"/>
<cfinvokeargument name="CustomerCode" value="#arguments.CustomerCode#"/>
<cfinvokeargument name="FullUserName" value="#arguments.FullUserName#"/>
<cfinvokeargument name="StartDate" value="#arguments.StartDate#"/>
<cfinvokeargument name="EndDate" value="#arguments.EndDate#"/>
</cfinvoke>
<cfcatch></cfcatch>
</cftry>
<cfreturn aTemp>
</cffunction>
<!--- convertDotNetDataset --->
<cffunction name="convertDotNetDataset" access="remote" returnType="any" output="false"
hint="takes a .Net dataset and converts it to a CF structure of queries">
<cfargument name="dataset" required="true">
<cfset var Local = StructNew()>
<cfset Local.result = structNew() />
<cfset Local.aDataset = arguments.dataset.get_any() />
<cfset Local.xSchema = xmlParse(Local.aDataset[1]) />
<cfset Local.xData = xmlParse(Local.aDataset[2]) />
<!--- Create Queries --->
<cfset Local.xTables = Local.xSchema["xs:schema"]["xs:element"]["xs:complexType"]["xs:choice"] />
<cfloop from="1" to="#arrayLen(Local.xTables.xmlChildren)#" index="Local.i">
<cfset Local.tableName = Local.xTables.xmlChildren[Local.i].xmlAttributes.name />
<cfset Local.xColumns = Local.xTables.xmlChildren[Local.i].xmlChildren[1].xmlChildren[1].xmlChildren/>
<cfset Local.result[Local.tableName] = queryNew("") />
<cfloop from="1" to="#arrayLen(Local.xColumns)#" index="Local.j">
<cfif left(Local.xColumns[Local.j].xmlAttributes.name,6) neq 'Column'>
<cfset queryAddColumn(Local.result[Local.tableName], Local.xColumns[Local.j].xmlAttributes.name, arrayNew(1)) />
</cfif>
</cfloop>
</cfloop>
<!--- see if there are any row of data, if not exit --->
<cfif NOT StructKeyExists(Local.xData["diffgr:diffgram"], "NewDataSet")>
<cfreturn Local.result>
</cfif>
<!--- Populate Queries --->
<cfset Local.xRows = Local.xData["diffgr:diffgram"]["NewDataSet"] />
<cfloop from="1" to="#arrayLen(Local.xRows.xmlChildren)#" index="Local.i">
<cftry>
<cfset Local.thisRow = Local.xRows.xmlChildren[Local.i] />
<cfset Local.tableName = Local.thisRow.xmlName />
<cfset queryAddRow(Local.result[Local.tableName], 1) />
<cfloop from="1" to="#arrayLen(Local.thisRow.xmlChildren)#" index="Local.j">
<cfif left(Local.thisRow.xmlChildren[Local.j].xmlName,6) neq 'Column'>
<cfset querySetCell(Local.result[Local.tableName], Local.thisRow.xmlChildren[Local.j].xmlName, Local.thisRow.xmlChildren[Local.j].xmlText, Local.result[Local.tableName].recordCount) />
</cfif>
</cfloop>
<cfcatch></cfcatch>
</cftry>
</cfloop>
<cfreturn Local.result>
