ColdFusion CLIENT cookie issue

MSSQL error: The data types text and varchar are incompatible in the equal to operator.

In the ColdFusion administrator under there is the option to save CLIENT variables as cookies, in the registry, a datasource or not at all.

If the client variables are stored as cookies, it is fairly easy to retrieve and parse to determine things like the identity of a returning user from the cookie. This cookie is called CFCLIENT_<cfapplication name>. If the client variables are stored in the database, it's not so easy. Actually impossible, the cookie variables are NOT stored in the datasource.

First to locate the CLIENT variables, which are stored in a table called CDATA. CFGLOBALS client variables are session variables, like CFID, CFTOKEN and timestamp details and are stored in the table CGLOBAL.

So what about the error? The data types text and varchar are incompatible in the equal to operator. I ran some queries to try and see if there was some information I could glean from the client variables table.

view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] != ''
Error: The data types text and varchar are incompatible in the not equal to operator.

[data] is a text field permitting nulls. So I checked for null:

view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] is null
pulled up everything, to test, I ran this:
view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] is not null
which turned up nothing! So I added a space in the quotes:
view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] != ' '
The data types text and varchar are incompatible in the not equal to operator.
No go! But this worked:
view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] not like ''
which pulled up what I wanted to look at, but unfortunately could not use. No cookie info is stored in with the datasource client session storage option in the ColdFusion administrator.

Trouble with cfloop lists and arrays with cfqueryparam

I was looping an array and and using each item in a cfqueryparam. Running a query in each loop. Each iteration was displaying a null query result, which was incorrect. The same problem occurred with a list of the same items.

The simple answer was to surround the cfqueryparam value with the Trim() function! It worked with the list loop too.

view plain print about
1<cfqueryparam value="#Trim(MSArray[j])#" cfsqltype="CF_SQL_VARCHAR">

Here is my effort:

view plain print about
1<cfset MSList = "
2010,
3020,
4004,
5075.5,
6027,
7030,
8020.5,
9021,
10023,
11041,
12041.1,
13041.5,
14041.3,
15040,
16080,
17102,
18088.1,
19095,
20104.5,
21104.1,
22104.3,
23110,
24096,
25100,
26107.1,
27112,
28124,
29025,
30101,
31143,
32143.1,
33126,
34132,
35146,
36149,
37153,
38154,
39155,
40160
41"

42/
>

43
44<cfloop list="#MSList#" index="j">
45    <cfquery datasource="#myDataSource#" name="getID">
46         SELECT [ID]
47         FROM     [dbo].[MS_Table]
48         where [Activity_ID] = 1
49         and     [MS] = <cfqueryparam value="#Trim(j)#" cfsqltype="CF_SQL_VARCHAR">
50    </cfquery>
51    <cfdump var="#getID#">
52</cfloop>

Custom Logging Template for SQL queries and Stored Procedures

We'll combine the last two blog posts, Where Does CF Debug Information Come from Mommy? Or How to use the ColdFusion Service Factory and ColdFusion Custom Debugging Template. The task is to create a template to log any database activity, specifically looking for consistently long running queries and to make a baseline for our code optimization.

Let's take a look at the data we are gathering. I created this table to keep data which I thought would be pertinent.

Hey this image will be loaded soon!

Here's a key explaining the basics on this table:
ATTUID = individual user id
RequestID = Individual ID per request (more below)
Body = the actual query
Line = line number of query or stored procedure
Name = assigned name of query or stored procedure
CFScript = not what you think, this is the script name calling the query or stored procedure
CFTimeStamp = ColdFusion's time stamp
SQLTimeStamp = MS SQL's time stamp
Type = query or stored procedure

Now the code...
First we'll utilize the ColdFusion Service Factory:

view plain print about
1<!--- initialize the CF Server's Service Factory --->
2<cfset factory = createObject("java","coldfusion.server.ServiceFactory") />
3<!--- get all the debugging data --->
4<cfset queries = factory.getDebuggingService().getDebugger().getData() />
We create the logging service by calling Illudium PU-36 generated beans and DAO (Data Access Objects). Basically these are the getters and setters.
view plain print about
1<!--- Set up Logging Service - call it once and reuse it --->
2<cfset CFLog = createObject("component","optimization.cfc.Optimization_Code_Log").init() />
3<cfset CFLogger = createObject("component","optimization.cfc.Optimization_Code_LogDAO").init(application.sqlSource) />

Add this in to help keep individual page requests identified together.

view plain print about
1<!--- Unique ID per request --->
2<!--- keep all requests per page call together --->
3<cfset RequestID = Val(Replace(Rand(),"0.","")) />
A little detail about this... I desired a random number be generated but wanted it to be a whole number. The Rand() function creates a decimal. I am treating the Rand() produced number as string, removing the preceding "0.", and converting to a number with the Val() function. The number length varied, so multiplication would not be work.

To get the data we want we are going to query the debugging data, "queries" called above, as shown below:

view plain print about
1<cfquery dbType="query" name="cfdebug_queries" debug="false">
2    SELECT *, (endTime - startTime) AS executionTime
3    FROM queries
4    WHERE type = 'SqlQuery'
5</cfquery>

Two things of note. One is the calculation of Execution Time, really what we are after, and the type = 'SqlQuery'. For Stored Procedures, we replace this with "StoredProcedure".

view plain print about
1<cfquery dbType="query" name="cfdebug_storedproc" debug="false">
2    SELECT *, (endTime - startTime) AS executionTime
3    FROM queries
4    WHERE type = 'StoredProcedure'
5</cfquery>

Loop the query containing all the debug data for queries run on your template, then set the data in the bean using the Illudium PU-36 setters, e.g. CFLog.setBlahBlah(variablescope.blahblah). Once they are all set, then we save using the generated DAO.

view plain print about
1<cfloop query="cfdebug_queries">
2
3    <cfset CFLog.setATTUID(client.ATTUID) />
4    <cfset CFLog.setRequestID(RequestID) />
5    <cfset CFLog.setBody(cfdebug_queries.Body) />
6    <cfset CFLog.setDatasource(cfdebug_queries.Datasource) />
7    <cfset CFLog.setIPAddress(CGI.REMOTE_ADDR) />
8    <cfset CFLog.setEndTime(cfdebug_queries.EndTime) />
9    <cfset CFLog.setExecutionTime(cfdebug_queries.ExecutionTime) />
10    <cfset CFLog.setLine(cfdebug_queries.Line) />
11    <cfset CFLog.setName(cfdebug_queries.Name) />
12    <cfset CFLog.setCFRowCount(cfdebug_queries.RowCount) />
13    <cfset CFLog.setStartTime(cfdebug_queries.StartTime) />
14    <cfset CFLog.setCFScript(CGI.SCRIPT_NAME) />
15    <cfset CFLog.setURLString(CGI.QUERY_STRING) />
16    <cfset CFLog.setTemplate(cfdebug_queries.Template) />
17    <cfset CFLog.setCFTimeStamp(cfdebug_queries.TimeStamp) />
18    <cfset CFLog.setSQLTimeStamp(Now()) />
19    <cfset CFLog.setType(cfdebug_queries.Type) />
20
21    <cfset CFLogger.save(CFLog) />
22</cfloop>

You can see that we are gathering more information than what was provided from the service factory, for example, the client user id, and several CGI variables.

We do the same thing for the Stored Procedures, except for one small but important change. Note also that I do not have to create another set of bean and DAO objects, I just reuse the existing ones, CFLog and CFLogger.

view plain print about
1<cfloop query="cfdebug_storedproc">
2
3    <cfset CFLog.setATTUID(client.ATTUID) />
4    <cfset CFLog.setRequestID(RequestID) />
5    <cfset CFLog.setBody(cfdebug_storedproc.Body) />
6    <cfset CFLog.setDatasource(cfdebug_storedproc.Datasource) />
7    <cfset CFLog.setIPAddress(CGI.REMOTE_ADDR) />
8    <cfset CFLog.setEndTime(cfdebug_storedproc.EndTime) />
9    <cfset CFLog.setExecutionTime(cfdebug_storedproc.ExecutionTime) />
10    <cfset CFLog.setLine(cfdebug_storedproc.Line) />
11    <cfset CFLog.setName(cfdebug_storedproc.result[cfdebug_storedproc.currentRow][1].name) />
12    <cfset CFLog.setCFRowCount(cfdebug_storedproc.RowCount) />
13    <cfset CFLog.setStartTime(cfdebug_storedproc.StartTime) />
14    <cfset CFLog.setCFScript(CGI.SCRIPT_NAME) />
15    <cfset CFLog.setURLString(CGI.QUERY_STRING) />
16    <cfset CFLog.setTemplate(cfdebug_storedproc.Template) />
17    <cfset CFLog.setCFTimeStamp(cfdebug_storedproc.TimeStamp) />
18    <cfset CFLog.setSQLTimeStamp(Now()) />
19    <cfset CFLog.setType(cfdebug_storedproc.Type) />
20
21    <cfset CFLogger.save(CFLog) />
22</cfloop>

That change is for Name. Inexplicably, in successive Stored Procedure calls in my template, the query output name remained the same for the following Stored Procs, ignoring the actual name. I had to dig down into the result column output to get the correct name for the Stored Procedure called.

view plain print about
1<cfset CFLog.setName(cfdebug_storedproc.result[cfdebug_storedproc.currentRow][1].name) />

If you pull a <cfdump> on the query output, you'll readily see from where this came, and possibly the situation in to which I ran.

Here is the entire chunk of code for my logging template. I'll not provide the Illudium PU-36 generated bean and DAO unless there are some requests. It's easy enough to generate your own.

view plain print about
1<!--- Get the Debugger Service --->
2<cfset factory = createObject("java","coldfusion.server.ServiceFactory") />
3<cfset queries = factory.getDebuggingService().getDebugger().getData() />
4
5<!--- Set up Logging Service - call it once and reuse it --->
6<cfset CFLog = createObject("component","optimization.cfc.Optimization_Code_Log").init() />
7<cfset CFLogger = createObject("component","optimization.cfc.Optimization_Code_LogDAO").init(application.sqlSource) />
8
9<!--- Unique ID per request --->
10<cfset RequestID = Val(Replace(Rand(),"0.","")) />
11
12<!--- EVENT: SQL Queries --->
13<cftry>
14    <cfquery dbType="query" name="cfdebug_queries" debug="false">
15        SELECT *, (endTime - startTime) AS executionTime
16        FROM queries
17        WHERE type = 'SqlQuery'
18    </cfquery>
19    <cfscript>
20        if( cfdebug_queries.recordCount eq 1 and not len(trim(cfdebug_queries.executionTime)) )
21        {
22            querySetCell(cfdebug_queries, "executionTime", "0", 1);
23        }
24    
</cfscript>
25    <cfcatch type="Any">
26        <cfscript>
27            cfdebug_queries = queryNew('ATTRIBUTES, BODY, CACHEDQUERY, CATEGORY, DATASOURCE, ENDTIME, EXECUTIONTIME, LINE, MESSAGE, NAME, PARENT, PRIORITY, RESULT, ROWCOUNT, STACKTRACE, STARTTIME, TEMPLATE, TIMESTAMP, TYPE, URL, et');
28        
</cfscript>
29    </cfcatch>
30</cftry>
31
32<!--- EVENT: Stored Procedures --->
33<cftry>
34    <cfquery dbType="query" name="cfdebug_storedproc" debug="false">
35        SELECT *, (endTime - startTime) AS executionTime
36        FROM queries
37        WHERE type = 'StoredProcedure'
38    </cfquery>
39    <cfscript>
40        if( cfdebug_storedproc.recordCount eq 1 and not len(trim(cfdebug_storedproc.executionTime)) )
41        {
42            querySetCell(cfdebug_storedproc, "executionTime", "0", 1);
43        }
44    
</cfscript>
45    <cfcatch type="Any">
46        <cfscript>
47            cfdebug_storedproc = queryNew('ATTRIBUTES, BODY, CACHEDQUERY, CATEGORY, DATASOURCE, ENDTIME, EXECUTIONTIME, LINE, MESSAGE, NAME, PARENT, PRIORITY, RESULT, ROWCOUNT, STACKTRACE, STARTTIME, TEMPLATE, TIMESTAMP, TYPE, URL');
48        
</cfscript>
49    </cfcatch>
50</cftry>
51
52<!--- SQL Queries Logging --->
53<cftry>
54    <cfloop query="cfdebug_queries">
55
56        <cfset CFLog.setATTUID(client.ATTUID) />
57        <cfset CFLog.setRequestID(RequestID) />
58        <cfset CFLog.setBody(cfdebug_queries.Body) />
59        <cfset CFLog.setDatasource(cfdebug_queries.Datasource) />
60        <cfset CFLog.setIPAddress(CGI.REMOTE_ADDR) />
61        <cfset CFLog.setEndTime(cfdebug_queries.EndTime) />
62        <cfset CFLog.setExecutionTime(cfdebug_queries.ExecutionTime) />
63        <cfset CFLog.setLine(cfdebug_queries.Line) />
64        <cfset CFLog.setName(cfdebug_queries.Name) />
65        <cfset CFLog.setCFRowCount(cfdebug_queries.RowCount) />
66        <cfset CFLog.setStartTime(cfdebug_queries.StartTime) />
67        <cfset CFLog.setCFScript(CGI.SCRIPT_NAME) />
68        <cfset CFLog.setURLString(CGI.QUERY_STRING) />
69        <cfset CFLog.setTemplate(cfdebug_queries.Template) />
70        <cfset CFLog.setCFTimeStamp(cfdebug_queries.TimeStamp) />
71        <cfset CFLog.setSQLTimeStamp(Now()) />
72        <cfset CFLog.setType(cfdebug_queries.Type) />
73
74        <cfset CFLogger.save(CFLog) />
75    </cfloop>
76    <cfcatch type="Any">
77        <!--- Error reporting query event --->
78    </cfcatch>
79</cftry>
80
81<!--- Stored Procedures Logging --->
82<cftry>
83    <cfloop query="cfdebug_storedproc">
84
85        <cfset CFLog.setATTUID(client.ATTUID) />
86        <cfset CFLog.setRequestID(RequestID) />
87        <cfset CFLog.setBody(cfdebug_storedproc.Body) />
88        <cfset CFLog.setDatasource(cfdebug_storedproc.Datasource) />
89        <cfset CFLog.setIPAddress(CGI.REMOTE_ADDR) />
90        <cfset CFLog.setEndTime(cfdebug_storedproc.EndTime) />
91        <cfset CFLog.setExecutionTime(cfdebug_storedproc.ExecutionTime) />
92        <cfset CFLog.setLine(cfdebug_storedproc.Line) />
93        <cfset CFLog.setName(cfdebug_storedproc.result[cfdebug_storedproc.currentRow][1].name) />
94        <cfset CFLog.setCFRowCount(cfdebug_storedproc.RowCount) />
95        <cfset CFLog.setStartTime(cfdebug_storedproc.StartTime) />
96        <cfset CFLog.setCFScript(CGI.SCRIPT_NAME) />
97        <cfset CFLog.setURLString(CGI.QUERY_STRING) />
98        <cfset CFLog.setTemplate(cfdebug_storedproc.Template) />
99        <cfset CFLog.setCFTimeStamp(cfdebug_storedproc.TimeStamp) />
100        <cfset CFLog.setSQLTimeStamp(Now()) />
101        <cfset CFLog.setType(cfdebug_storedproc.Type) />
102
103        <cfset CFLogger.save(CFLog) />
104    </cfloop>
105    <cfcatch type="Any">
106        <!--- Error reporting query event --->
107    </cfcatch>
108</cftry>

ColdFusion Custom Debugging Template

Build your own ColdFusion Custom Debugging Template

You can create your own ColdFusion custom debugging template. Build it and drop it into your CF web root WEB-INF/debug folder.

In my case on Windows the folder is C:\ColdFusion10\cfusion\wwwroot\WEB-INF\debug

However, it's not so easy to build your own template! You'll have to use the ColdFusion Service Factory explained in this blog post.

The easiest way to start is to take classic.cfm in the debug folder and experiment.

If you don't believe me, believe Ben Forta! Displaying Query Contents In Debug Output

Why would you want to create your own custom debugging template? In my case, I wanted to create a feature that would log actions from a particular template when accessed only by particular users. It's on a shared development server where debugging is normally not utilized, thus no conflicting debugging usage. I dropped in my custom debugging (logging) template, turned on debugging in the CF Admin and added the appropriate IP addresses of the developers we were logging. There are certainly other ways to skin this cat, for example, we could have dropped in the debug/log template into any directory and added a <cfinclude> in the template we were tracking. But, in my particular case, we didn't want to make any changes to existing code.

Next, I'll go into detail on how I created a Custom Debugging template (logging template) utilizing the ColdFusion Service Factory.

Where Does CF Debug Information Come from Mommy? Or How to use the ColdFusion Service Factory

Have you ever wondered where ColdFusion gets the information presented when debugging is turned on in the CF Admin? It comes from the ColdFusion Server's Service Factory. Of the many things that this produces, one is the debugging service.

Drop this code into one of your templates:

view plain print about
1<cfdump var="#createObject("java","coldfusion.server.ServiceFactory").getDebuggingService().getDebugger().getData()#" label="Debug Data" />

You can see first that we are calling the Service Factory, then the Debugging service, then the Debugger, and finally the data produced. If you care to investigate what each does, simply eliminate calls from the right end of the createObject statement and dump it.

The multi-part identifier "variable" could not be bound.

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer]The multi-part identifier "my.variable.name" could not be bound.

This error means that you did not surround your variable(s) with pound signs (hash marks) in your query.

view plain print about
1<cfquery name="getData" >
2    SELECT     [ID],
3         [Value],
4         [Identifier],
5         [Identifier_id]
6    FROM     [MASTER_PLANNING]
7    WHERE    0 = 0
8        <cfif StructKeyExists(arguments, "ID")>
9            AND ID = arguments.ID
10        </cfif>
11        <cfif StructKeyExists(arguments, "Value")>
12            AND [Value] = arguments.Value
13        </cfif>
14        <cfif StructKeyExists(arguments, "Identifier")>
15            AND Identifier = arguments.Identifier
16        </cfif>
17        <cfif StructKeyExists(arguments, "Identifier_id")>
18            AND Identifier_id = arguments.Identifier_id
19        </cfif>
20</cfquery>

Should look like:

view plain print about
1<cfquery name="getData>
2    SELECT     [ID],
3         [Value],
4         [Identifier],
5         [Identifier_id]
6    FROM     [MASTER_PLANNING]
7    WHERE    0 = 0
8        <cfif StructKeyExists(arguments, "
ID")>
9            AND ID = #arguments.ID#
10        </cfif>
11        <cfif StructKeyExists(arguments, "
Value")>
12            AND [Value] = #arguments.Value#
13        </cfif>
14        <cfif StructKeyExists(arguments, "
Identifier")>
15            AND Identifier = #arguments.Identifier#
16        </cfif>
17        <cfif StructKeyExists(arguments, "
Identifier_id")>
18            AND Identifier_id = #arguments.Identifier_id#
19        </cfif>
20</cfquery>

can't cast String [] to a value of type [numeric]

Railo Error Message
invalid call of the function getJerseyColor (), first Argument (Color) is of invalid type, can't cast String [] to a value of type [numeric]

Using a UDF (User Defined Function) created to convert a numeric value to a string within a returned query. We have an integer stored in the database table which represents a color value. It is much easier for the user to actually read the color name instead of mentally trying to convert the number to a the color value each time. We are actually looking at Jersey colors and Jersey numbers, so coaches and evaluators can quickly and easily identify each player.

Here's the UDF:

view plain print about
1<cffunction name="getJerseyColor">
2    <cfargument name="Color" type="numeric" required="true" />
3    <cfset var JerseyColor = "" />
4    <cfswitch expression="#arguments.Color#">
5        <cfcase value="1">
6            <cfset JerseyColor = "Yellow" />
7        </cfcase>
8        <cfcase value="2">
9            <cfset JerseyColor = "Blue" />
10        </cfcase>
11        <cfcase value="3">
12            <cfset JerseyColor = "Purple" />
13        </cfcase>
14        <cfcase value="4">
15            <cfset JerseyColor = "Yellow/Blue" />
16        </cfcase>
17        <cfcase value="5">
18            <cfset JerseyColor = "Green/Blue" />
19        </cfcase>
20    </cfswitch>
21    <cfreturn JerseyColor />
22</cffunction>

Here's how the UDF is called:

view plain print about
1<cfset filterPlayer.Color = getJerseyColor(filterPlayer.Color) />

Here it is utilized replacing the returned query value for "Color":

view plain print about
1<cfquery name="filterPlayer" datasource="#application.datasource#">
2    SELECT     playerid    AS "ID",
3        rate1        AS "Color",
4        rate2        AS "Num"
5    FROM     tblplayerinfo
6</cfquery>
7<!--- make jersey color human readable --->
8<cfloop query="filterPlayer">
9    <cfset filterPlayer.Color = getJerseyColor(filterPlayer.Color) />
10</cfloop>

Now about the error....

Testing on development produced no error, while Production generated the error. Both columns in each MySQL database were numeric, of type smallint(6) and defaulted to Null. In this case, we are talking about "rate1" column aliased as "Color". The only difference between the two database columns was that the development MySQL database had actual numeric values in each row. Production had Null values in every "rate1" column row.

Doing a little reasearch, mainly Googling "can't cast string to a number value" turned up some issues about Railo and null values while casting strings to numbers. Some pondering led to realization that the column needed non Null numeric values to allow the UDF to run.

The solution was to replace the Null values in the numeric int() column to actual numeric values. Setting to 0 (zero) worked. Now the UDF runs perfectly.

Adding Spaces to Column Name Alias in a Query of Query

How does one add a space to an alias of a column name in Railo, MySQL in a query of queries?

I ran into an issue when attempting to import a query data set as a CSV (comma seperated value). The application to which I was importing required that the CSV column names have spaces, e.g., "Mom Email", "Mom Phone". My column headers correctly did not have any spaces, e.g., "momemail", "momphone". The way to change the column headers was to alias them with the "AS" statement and quotes.

My initial error message was:

Encountered "\"First Name\"" at line 1, column 25.
Was expecting one of:
"AS" ...
"FROM" ...
...
"," ...
"." ...
"+" ...
"-" ...
"*" ...
"||" ...
"/" ...
"**" ...
".*" ...
"(" ...

My query was:

view plain print about
1<cfquery name="filterPlayer" dbtype="query">
2SELECT     firstname     AS "First Name",
3    lastname     AS "Last Name",
4    homephone     AS "Home Phone",
5    cellphone     AS "Cell Phone",
6    dadphone     AS "Dad Phone",
7    momphone     AS "Mom Phone",
8    email         AS "Email",
9    dademail     AS "Dad Email",
10    momemail     AS "Mom Email"
11FROM     getPlayers
12ORDER BY lastname
13</cfquery>

The first thing to look for is the double quotes around the alias. AS "First Name". You will also note the dbtype="query", the query is a query-of-queries. The original query is "getPlayers" as seen in the FROM getPlayers statement.

The query appears to be formed correctly, which was really confusing.

The solution was to change the query from a query-of-queries to query the database directly. I changed the "dbtype=query" to datasource=#application.datasource#" and reference the table name in the FROM statement, as shown below, and I was able to add the space into the alias name as expected.

view plain print about
1<cfquery name="filterPlayer" datasource="#application.datasource#">
2SELECT     firstname     AS "First Name",
3    lastname     AS "Last Name",
4    homephone     AS "Home Phone",
5    cellphone     AS "Cell Phone",
6    dadphone     AS "Dad Phone",
7    momphone     AS "Mom Phone",
8    email         AS "Email",
9    dademail     AS "Dad Email",
10    momemail     AS "Mom Email"
11FROM     tblplayerinfo
12ORDER BY lastname
13</cfquery>

Invalid JSON property [object Error] found when trying to apply resultTemplate or paging.summaryTemp

Invalid JSON property [object Error] found when trying to apply resultTemplate or paging.summaryTemplate. Please check your spelling and try again. Error: Unable to get value of the property 'name': object is null or undefined

Error with jQuery, combobox, flexbox in IE7 and IE8. Runs fine on IE9, Chrome, and Firefox.

Solution:
My combobox jQuery was dynamically generated using ColdFusion code concatenating JavaScript strings while looping data. I found some bad logic that was erroneously adding an extraneous trailing comma to my dropdown array.

Read more here....

Debugging CF and JavaScript, flexbox combobox jQuery

Invalid JSON property [object Error] found when trying to apply resultTemplate or paging.summaryTemplate. Please check your spelling and try again. Error: Unable to get value of the property 'name': object is null or undefined

Getting errors when using flexbox combobox on IE7 and IE8. The code runs on IE9, Chrome, and all Firefox.

Using ColdFusion to generate JavaScript can sure lead to complications when trying to debug. It's complicated trying to write the CFML to generate the code, but we do it to make our lives easier!

This post is more about the process of debugging than writing or even the actual solution. First I will list efforts that proved worthless. I'll bet you tried at least one of these before finding this post.

  1. Searching for "resultTemplate" and/or "summaryTemplate"
  2. Editing jquery.flexbox.js. I actually put in "alert"s to step debug – waste of time.
  3. Edited the generating CF code to add spaces in the resultant JS.
  4. Did a LOT of Googling. This only helped if you found this article, which wasn't here when I went through the process!
So what worked? or how do we find the cause?

The first objective is to narrow down the area which is causing the problem. Because it was clicking in the combobox throwing the error, that sure helped. Next is to look at the JavaScript generated for the combobox. Here's what I had.

view plain print about
1<scr!pt type="text/javascript">var values_6789 = {};values_6789.results = [{id: "View The Collection", name: "View The Collection"},{id: "View The Trunk Show Collection", name: "View The Trunk Show Collection"},{id: "Personal Appearance", name: "Personal Appearance"},];values_6789.total = values_6789.results.length;$(document).ready(function () {$("#misc2").flexbox(values_6789, {autoCompleteFirstMatch: false, selectBehavior: true, paging: false, width: 180, noResultsText: "" });});</script>

Looking through this code, something very small is out of place, a comma.

view plain print about
1......name: "Personal Appearance"},];values_6789.total =......

The comma just before the closing bracket, "]".

Here's the code that builds that part of the JavaScript

view plain print about
1<cfloop query="arguments.qProductAddorValues">
2 <cfif arguments.qProductAddorValues.productAddorID eq arguments.productAddorID>
3 <!--- write element for the JSON object --->
4 <cfset fldString.append('{id: "#arguments.qProductAddorValues.value#", name: "#arguments.qProductAddorValues.name#"} ' )>
5 <cfif arguments.qProductAddorValues.currentrow neq arguments.qProductAddorValues.recordcount>
6 <cfset fldString.append(',')>
7 </cfif>
8 <cfif (len(arguments.fldval) and not cbBoxFldValAdded)>
9 <!--- this string is used for initialization of the control --->
10 <cfset defaultSelection = ',initialValue: "#arguments.fldval#"'>
11 <cfif arguments.qProductAddorValues.currentrow neq arguments.qProductAddorValues.recordcount>
12 <cfset fldString.append(',')>
13 </cfif>
14 <cfset cbBoxFldValAdded = 1>
15 <cfelse>
16 <cfif (arguments.qProductAddorValues.default eq 1 and not cbBoxFldValAdded)>
17 <cfset defaultSelection = ',initialValue: "#arguments.qProductAddorValues.value#"'>
18 </cfif>
19 </cfif>
20 </cfif>
21</cfloop>

Why is that extra comma there? There's a checking for the end...

view plain print about
1<cfif arguments.qProductAddorValues.currentrow neq arguments.qProductAddorValues.recordcount>
2 <cfset fldString.append(',')>
3</cfif>

The logic is probably bad. To prove it we have to output the 2 variables in the test. We have to output them and NOT break jQuery so the page will render AND still display the variable output.

This is done by commenting out the variable output with JavaScript syntax, /* stuff */. First, I create a string with the 2 test variables and concatenate them together with opening and closing JS comments.

view plain print about
1<cfset JSStringTest = "/* " & arguments.qProductAddorValues.currentrow & " = " & arguments.qProductAddorValues.recordcount & " */" />

Then I stick output of the string into the generated JS string where I think it will be innocuous. Outside of the array but inside the single quote.

view plain print about
1<cfset fldString.append('{id: "#arguments.qProductAddorValues.value#", name: "#arguments.qProductAddorValues.name#"} #JSStringTest#' )>

Here is the resultant JavaScript output:

view plain print about
1<scr!pt type="text/javascript">var values_6789 = {};values_6789.results = [{id: "View The Collection", name: "View The Collection"} /* 2 = 63 */,{id: "View The Trunk Show Collection", name: "View The Trunk Show Collection"} /* 3 = 63 */,{id: "Personal Appearance", name: "Personal Appearance"} /* 4 = 63 */,];values_6789.total = values_6789.results.length;$(document).ready(function () {$("#misc2").flexbox(values_6789, {autoCompleteFirstMatch: false, selectBehavior: true, paging: false, width: 180, noResultsText: "" });});</script>

You can see that the commented /* 4 = 63 */ for the final entry is obviously false. Thus proving the test is bad.

From here I have 2 options

  1. Find an accurate test.
  2. Just remove the last comma.

In this instance there really wasn't a valid test that could be easily and accurately generated. Information was being pulled from too many different places. I decided to just add the trailing comma after every iteration and remove it from end of the string after the loop is completed, but before we appended more JavaScript.

This turned out to not be easy either. If you recall, the string, fldString was appending JavaScript using:

view plain print about
1fldString.append()

This was because fldString is not really a string but a Java object created with:

view plain print about
1fldString = createobject("java", "java.lang.StringBuffer").init()

using CF's create Java object and Java's StringBuffer class. So the "fldString.append()" is a StringBuffer method.

What other StringBuffer method can I use to trim off the pesky trailing comma? We could use deleteCharAt() but how to find what the index is of the comma. It's at the end of a variable length string, changing every time the function is called. Well we just find the length of the string using another StringBuffer method, length(). Can't get much simpler than that! So here is how that code looks:

view plain print about
1<!--- remove trailing comma which is always added above ~ line 156, using Java StringBuffer class --->
2<cfset fldString.deleteCharAt(fldString.length()-1) />

Starting from the inside, we find the fldString length, subtract one and delete the character in that position.

So here's the final bit of completed code:

view plain print about
1<cfloop query="arguments.qProductAddorValues">
2 <cfif arguments.qProductAddorValues.productAddorID eq arguments.productAddorID>
3 <!--- write element for the JSON object --->
4 <cfset fldString.append('{id: "#arguments.qProductAddorValues.value#", name: "#arguments.qProductAddorValues.name#"},' )>
5 <cfif (len(arguments.fldval) and not cbBoxFldValAdded)>
6 <!--- this string is used for initialization of the control --->
7 <cfset defaultSelection = ',initialValue: "#arguments.fldval#"'>
8 <cfset cbBoxFldValAdded = 1>
9 <cfelse>
10 <cfif (arguments.qProductAddorValues.default eq 1 and not cbBoxFldValAdded)>
11 <cfset defaultSelection = ',initialValue: "#arguments.qProductAddorValues.value#"'>
12 </cfif>
13 </cfif>
14 </cfif>
15</cfloop>
16<!--- remove trailing comma which is always added above ~ line 156, using Java StringBuffer class --->
17<cfset fldString.deleteCharAt(fldString.length()-1) />

Bam! The JSON error is gone in IE7 and IE8 and the code still runs perfectly in all other browsers. This is a long story with a few important pieces. I'll break out the useful tidbits and post those in separate blog entries so that others may find specific answers without having to read this saga.

More Entries

BlogCFC was created by Raymond Camden. This blog is running version 5.9.7. Contact Blog Owner