Code Throwing error sometimes.

[Upen1]

Hi All,

I am using the following code to convert a ColdFusion Query Object to ColdFusion structure to work in Kendo UI datagrid.

<cffunction name="convertQueryToKendoJSON" access="public" returntype="Struct" hint="Converts a ColdFusion Query Object into Kendo UI format JSON">

          <cfargument name="queryObject" required="true" type="query" hint="ColdFusion Query Object" />

          <cfset var returnStruct = structNew() />

          <cfset var tempArray = arrayNew(1) />

          <!--- Loop over the query Object and create one structure Object for each row and query fields are the structure keyfields

                    Store the structure into an array Object. --->

          <cfloop query="arguments.queryObject">

                    <cfset tempStruct = structNew() />

                    <cfloop list="#arguments.queryObject.columnList#" index="columnName">

                              <cfset tempStruct[columnName] = arguments.queryObject[columnName][arguments.queryObject.currentRow] />

                    </cfloop>

                    <cfset arrayAppend(tempArray, tempStruct) />

          </cfloop>

          <!--- Store the Array into result key field of the returnStruct --->

          <cfset returnStruct['results'] = tempArray />

          <!--- If record Count value exists in the query then only set the totalRecords value --->

          <cfif findNoCase("ntRecordCount", arguments.queryObject.columnList)>

                    <cfset returnStruct['totalRecords'] = arguments.queryObject.ntRecordCount />

          </cfif>

          <cfreturn returnStruct />

</cffunction>

It is working fine most of the times. But, in some cases when I am getting the query object from a stored Proc call or from QoQ opearation then I am getting the following error.

[Table (rows 4 columns NTRECORDCOUNT, NTROWINDEX, NTLEAD_ROUTING_ALGOID, VCROUTING_ALGO, DTCREATED, DTLAST_UPDATED, NTSORT_ORDER): [NTRECORDCOUNT: coldfusion.sql.QueryColumn@10ec6d0] [NTROWINDEX: coldfusion.sql.QueryColumn@11e9be0] [NTLEAD_ROUTING_ALGOID: coldfusion.sql.QueryColumn@8b96e1] [VCROUTING_ALGO: coldfusion.sql.QueryColumn@5e9f7e] [DTCREATED: coldfusion.sql.QueryColumn@3947f5] [DTLAST_UPDATED: coldfusion.sql.QueryColumn@a35419] [NTSORT_ORDER: coldfusion.sql.QueryColumn@e669f3] ] is not indexable by NTSERVICE_TYPEID

NOTE: I am not getting the error always, sometimes I am getting this error.

System Info:

DataBase: MS SQL 2008

CF - ColdFusion  9,0,2,282541 Enterprise Edition

[Adam Cameron.]

It's telling you that queryObject doesn't always have the NTSERVICE_TYPEID column. You should be looking into why that is sometimes the case. Recommend putting a try/catch around it, and dumping out the query (dev environment), or serialising it and writing it to a log (prod environment). Once you see what it does contain in these situations, you should be able to work out where your logic is wrong.

--

Adam

[Upen1]

Sorry for the late reply and Thanks Adam for your suggestion.

I got the exact issue what you had told. But, why I am getting different column list when I am taking the list from a query Object

arguments.queryObject.columnList ???

[Dan_Bracuk]

Excellent question.  Make sure a dump of the columnlist is included in your catch block.

[Upen1]

I can see the particular column is not exist in query object from the log.

Also, I have added log to get the column list by arguments.queryObject.columnList.

Need some time to get the result.

[Upen1]

Hi Dan,

I got following log report.

ColumnList:

BTACTIVE,NTCOMPANYID,VCCOMPANY_NAME

Error Message:

[Table (rows 311 columns ntCompanyID, vcCompany_Name, btActive): [ntCompanyID: coldfusion.sql.QueryColumn@5d213b6c] [vcCompany_Name: coldfusion.sql.QueryColumn@30c45f38] [btActive: coldfusion.sql.QueryColumn@2c476e8b] ] is not indexable by VCOFFICE_NAME

QueryObject Loged in Error Log:

{"QUERYOBJECT":{"COLUMNS":["NTCOMPANYID","VCCOMPANY_NAME","BTACTIVE"],"DATA":[[

I haven't posted the data of query object.