Doug Boude (rhymes with 'loud')

22 January 2009

Special Character/Unicode Issue in Ajax Data Retrieval

Categories: i18n SQL javascript Coldbox goog JSON

My most recent project has caused me to have to be "unicode aware" at times (something I've never had to do before), and so I am learning a lot about encoding and display of special characters as I go along. My latest challenge related to this topic involved a User Manager section I created, wherein the users could very well have names that contain special characters (foreign names). This particular section performs its updates, deletes, and inserts via Ajax calls and client-side JS manipulation of a JSON data set. My Ajax is performed via the Prototype library, my code is all ColdFusion living within the Coldbox framework, I'm using Coldspring to manage my object relationships, Transfer is my ORM, and my backend database is MSSQL 2005.

The Challenge: Data that contained special characters was being successfully inserted/updated via my Ajax calls, but the JSON data set returned via those calls did NOT contain those special characters (or contained an incorrect interpretation of them, like numbers, question marks, etc.). A quick check of the database verified that the data was indeed stored in the tables properly.

Setting the Stage
At this stage in the game for me, the smorgasbord of terms, acronyms, and concepts revolving around properly handling unicode is a bit foggy for me. (On a side note, I WISH someone who has the full understanding would put together a simple "checklist" of "Things you need to do in order to handle special characters in ColdFusion"!) From what I currently understand, the physical template you write has to be "encoded" properly (set within the IDE you are using); The database you are using has to have the proper encoding(called Collation in MSSQL 2005); The fields in your table have to be of the proper type to store unicode text(ie: 'nVarchar' instead of 'Varchar', etc.); your browser has to have the proper languages associated in order to display certain sets of special characters(Tools/Internet Options/Languages in IE); your JS functions, if living in a separate file, must have the page encoded properly(again, via your IDE); your ColdFusion datasource has to have the checkbox for "Enable High ASCII characters and Unicode for data sources configured for non-Latin characters " checked; and to top it all off, after having handled all of that, your JS functionality still yet needs to have ITS encoding types set in the proper place.

That sounds like a LOT of fiery hoops just to be able to deal with special characters, right? Well I'm with ya...it's on the verge of being a nightmare for someone who's never had to deal with it. And I do realize that for some of you reading this, the first time YOU tried to deal with special characters, everything just frickin "worked right out of the box" and you probably didn't have to do but one or two of those things, at the most. I say that you got lucky that things were configured just so for the particular character set you were dealing with, and even though from your perspective it didn't seem like that big of a deal, the fact is having an understanding of what's going on behind the scenes can be pretty doggone important anyway, just in case you suddenly get the directive to start storing characters from some other encoding scheme that you AREN'T prepared for out of the box.

Okay, so back to my challenge. Here's the nutshell of how my process flows:

My initial page load is provided with a query of all of the users in the system. That query is then translated to a JS object using a line in my template like the following:

When a user is chosen for edit, I load up the values for that user from the client-side data set into form fields, allow them to be edited, then submit the form values back to Coldbox via an Ajax call where the record gets updated. After the update occurs, my event grabs a fresh copy of the user query (which now contains the updated record), serializes it, and returns it as a JSON string to the Ajax call. Here's the line of JS that performs the Ajax call:

new Ajax.Request(saveURL,{parameters: myparams, method:'post',onCreate:showWorking,onComplete:postSave});

Here is the line in my handler(controller) that returns the data to the call:

<cfset arguments.event.renderData(type="plain",data=serializeJSON(variables.userService.getAllUsers(activeOnly=false),true),contentType="application/json) />

Bear in mind that the "getAllUsers" method call you see is the exact same method call being used during the initial page load to retrieve the data, which DOES contain the special characters as it should.

So here is where the problem manifests itself. The JSON string that the "postSave" method is provided with has the special characters stripped out! Poof, they are just gone. Okay, so let me go and investigate some of the optional parameters that Prototype provides for its Ajax.Request method and see if any of them might apply in this situation.... Ah, here are a few! 'encoding', 'evalJSON', 'sanitizeJSON'. Well, playing with all three of these resulted in zero changes to the symptoms. Sheesh, I've encoded everything I can possibly think to encode...what else is there? After a lot of google time, skimming page after page of semi-related (but not directly relevant) info, I came across a tiny little tweek to the contentType being returned that I tried, and lo and behold it frickin worked! Here is the new line that returns a CORRECT data set to my Ajax call:

The difference: adding in "charset=UTF-8" to the contentType of the data being returned. Apparently THAT'S what JS was looking for all along.

I hope this helps someone else avoid a huge loss of time. And again, for those of you out there who know this stuff inside and out and can actually visualize how it all works in your head, it sure would be an assett to the community if you could put that info into a kind of "checklist" a person could use to make sure they have all of their Unicode ducks in a row when trying to deal with special characters! Pretty please?

Doug out.

Posted by dougboude at 10:54 PM | PRINT THIS POST! | Link | 6 comments

09 March 2008

Client-Side Drilldowns Made Easy

Categories: javascript goog JSON ColdFusion code snippets Model-Glue

Last September I shared a post on an alternative to Ajax for client-side interactivity leveraging Coldfusion's WDDX. I'd like to take it a step further now and share an approach (and corresponding code) I often use in my Model-Glue apps when needing to create tiered or drilldown-type select lists withOUT having to make numerous calls to the server. The gist of this methodology is the same as in my previous post:

Retrieve all needed data sets for populating the drilldowns;
Convert those CF queries to a form that Javascript can manipulate;
Write the necessary functions to populate the dropdowns based on previous options selected.

Here is the working sample. Go ahead, play with it!

The main difference in this example is the fact that I'm not utilizing WDDX, but rather JSON to make the data Javascript friendly.

Let me show you what the queries look like for each tier of this particular drilldown:

Tier 1	Tier 2	Tier 3

An important item of note here is that the data set for a given tier must include the parent ID of the previous tier in order to perform the filtering operations you'll see soon.

Okay, so how to get the queries into a format Javascript can manipulate. The route I chose to go was to convert the queries to JSON via one of two methods, depending on what version of CF you're using. Since not everyone is using version 8 yet, I made this example compatible with anything 6 or higher (versions previous to 8 are dependent on outside conversion; I chose to use JSON.CFC. Version 8 can utilize the built in function "serializeJSON"). Here's the statement where the queries are transformed (using the commonly known custom tag "QuerySim" to create the data for this example):

<cf_querysim>
    level_1
    level_1_id,name
    1|Colors
    2|Shapes
    3|Foods
</cf_querysim>
<cf_querysim>
    level_2
    level_2_id,level_1_id,name
    1|1|Red
    2|1|Blue
    3|1|Yellow
    4|2|Triangle
    5|2|Square
    6|2|Circle
    7|3|Bread
    8|3|Meat
    9|3|Fruit
</cf_querysim>
<cf_querysim>
    level_3
    level_3_id,level_2_id,name
    1|1|Fuschia
    2|1|Brick Red
    3|1|InfraRed
    4|2|Teal
    5|2|Cyan
    6|2|Navy Blue
    7|3|Light Yellow
    8|3|Dark Yellow
    9|4|Isosceles
    10|4|Equilateral
    11|4|Right Triangle
    12|5|Rectangle
    13|5|Parallelogram
    14|6|Ellipse
    15|6|Oval
    16|7|Matzah
    17|7|Hot Cross Buns
    18|7|Brioche
    19|8|Steak
    20|8|Fajitas
    21|8|Hamburger
    22|9|Kiwi
    23|9|Grapes
    24|9|Oranges
</cf_querysim>



<cfif listfirst(SERVER.ColdFusion.ProductVersion) gt 7>
    <cfset level_2_json = serializeJSON(level_2, true) />
    <cfset level_3_json = serializeJSON(level_3, true) />
<cfelse>
    <cfset objJSON = createobject("component","json") />
    <cfset level_2_json = replace(objJSON.encode(level_2),"data","DATA","all") />
    <cfset level_3_json = replace(objJSON.encode(level_3),"data","DATA","all") />
</cfif>

<script>
    //set our json data into Javascript objects
    var lev2data = <cfoutput>#level_2_json#</cfoutput>;
    var lev3data = <cfoutput>#level_3_json#</cfoutput>;
</script>

You'll notice in the section where I rely on JSON.CFC, I am doing a replace on the lower case word "data" to make it upper case. This is to make the JSON string produced consistent with the one produced utilizing serializeJSON. Since Javascript is case sensitive, case consistency in the JSON is required if you wish to utilize only one javascript function to perform the select list population. You'll also notice the use of the optional secondary parameter in the serializeJSON function call. This is needed in order to produce a JSON string that can be accessed by Javascript exactly the same as the JSON.CFC string.

MG NOTE: Regarding the fact that I utilize this approach in Model-Glue apps...I have my controller return an already formatted JSON string to my view rather than return a query and then perform the transformation there. Many of my controller methods have an optional "returnJSON" argument that I use when I need a JSON string back rather than a query.

Okay, data sets are available to Javascript. Now to write a few Javascript functions that can spin through that data and populate the appropriate select list. Here are the functions needed to perform the necessary tasks:

Of note is the fact that we are working with three select lists. The first select list is always populated at load time and needs no Javascript intervention at all. Select lists "level_2" and "level_3", however, are being completely manipulated by the JS calls by one generically written function.

Last but not least, the HTML with the Javascript calls embedded in the select lists' onChange event:

<body>
    <h2>Client-Side Drilldown Example</h2>
    Level 1: <select name="level_1" id="level_1" onChange="resetAll('level_2,level_3');repopulate('level_2',lev2data,this.options[this.selectedIndex].value,'level_1_id','name','level_2_id');">
        <option value=""><cfoutput>#repeatstring("-",15)#</cfoutput></option>
        <cfoutput query="level_1">
            <option value="#level_1_id#">#name#</option>
        </cfoutput>
    </select>
    <hr width="25%" align="left">
    Level 2:
    <select name="level_2" id="level_2" onChange="resetAll('level_3');repopulate('level_3',lev3data,this.options[this.selectedIndex].value,'level_2_id','name','level_3_id');">
        <option value=""><cfoutput>#repeatstring("-",15)#</cfoutput></option>
    </select>
    <input type="button" value="show ID" onClick="alert(document.getElementById('level_2').options[document.getElementById('level_2').selectedIndex].value);">
    <hr width="25%" align="left">
    Level 3:
    <select name="level_3" id="level_3" >
        <option value=""><cfoutput>#repeatstring("-",15)#</cfoutput></option>
    </select>
    <input type="button" value="show ID" onClick="alert(document.getElementById('level_3').options[document.getElementById('level_3').selectedIndex].value);">
</body>

Voila! That's it! instant, client-side drilldown with only a single call to the server!

Hope it saves someone a little time. ;)

Doug out.

Posted by dougboude at 10:46 PM | PRINT THIS POST! | Link | 0 comments

06 February 2008

JSON.CFC, MAYBE CF8 DIDN'T MAKE YOU OBSOLETE AFTER ALL!

Categories: goog JSON ColdFusion

Back in July I shared the results of my personal forray into JSON as a blog post, comparing it to XML as a means of returning Ajax data. In order to conduct my experiments, I had utilized an open source CFC called JSON.CFC in order to do the conversion from CF data types to JSON and vica versa. After the post, one individual made the comment that once CF 8 came out, there wouldn't be a need for JSON.CFC anymore. Well, until today, I would have agreed, but now I'm not so sure.

I'm working on a project where I want to retrieve some data sets and keep them client side for fast lookups, so my approach was to return the data to my template as a JSON string, and then just 'pop' it into Javascript, like so:

FYI, the my data has these fields: SERIESGROUPID,NAME

What I expected was that, once I did this, I would be able to access my values in JS like this:

...

My data was converted from a query to JSON using CF8's new "serializeJSON()" function. But to my mild irritation, I was not able to access the data as expected. I thought to myself, "self, this is an odd thing, because when I performed the same task using the JSON component a few months ago, it worked fine". Just to double check myself, I re-read my blog post on JSON and decided to use JSON.CFC to see if the results were the same. Using JSON.CFC, I was able to get the expected results.

So here's the thing, then: JSON.CFC, MAYBE YOU'RE NOT SO OBSOLETE AFTER ALL!

Or, it could also be that I am just not adept enough at reading JSON strings and can't figure out the appropriate way to access my JS object in such a way that I could easily loop over it looking for specific values. If anyone knows how one SHOULD go about accessing data that was serialized using "serializeJSON" in a JS context, I would love to see an example! I could probably figure it out if I had the luxury of time, but alas, I do not.

Okay, just to top things off with a side by side comparison (which I love doing), here are the results of the CF 8 and JSON.CFC conversions:

Method JSON String

JSON.CFC {"recordcount":1,"columnlist":"name,seriesgroupid","data":{"name":["Exports"],"seriesgroupid":[1]}}

serializeJSON() {"COLUMNS":["SERIESGROUPID","NAME"],"DATA":[[1,"Exports"]]}

Method	JSON String
JSON.CFC	{"recordcount":1,"columnlist":"name,seriesgroupid","data":{"name":["Exports"],"seriesgroupid":[1]}}
serializeJSON()	{"COLUMNS":["SERIESGROUPID","NAME"],"DATA":[[1,"Exports"]]}

JSON.CFC dump	serializeJSON dump

Posted by dougboude at 4:20 PM | PRINT THIS POST! | Link | 8 comments

05 July 2007

DEMYSTIFYING JSON (for myself)

Categories: javascript goog JSON ColdFusion code snippets placeblogger

I'm doing this post because the term 'JSON' has continued to appear here and there within blog posts, conference sessions, articles, and emails that I consume as part of my professional growth regimen. Despite the fact that the term is so very often mentioned casually as if everybody has known about it since Kindergarten, the greater part of my understanding of JSON is barren except for the few clues I have managed to glean through context. So, I decided to take the time to get to know JSON a little more intimately, and learned some interesting things.

WHAT I THOUGHT IT WAS
Based solely on the info I managed to gather from "between the lines", I knew that JSON stood for JavaScript Object Notation, and that it was an alternative to XML when dealing with the results of Ajax calls. Knowing that much, I could deduce that it was basically "data in a string". But what it looked like, how to handle it, and why I would want to do it were still questions in my mind.

WHAT I FOUND IT TO BE
JSON is indeed a string representation of simple or complex data, just as is XML, only without the tags AND without the need to treat that string as a document type in order to transverse it elegantly. JSON contains two indicators: curly braces to indicate that a structure follows, and square brackets to indicate an array. Here's a structure written in JSON: {key1:"val1",key2:"val2",key3:"val3"} and a one dimensional array: ["val1","val2","val3"]. You can nest these types within one another, too; for example, the value of a structure key could be an array, and would be written as this: {key1:["val1","val2","val3"],key2:"val2"}. Pretty cool, eh?

Now, how to get data back and forth between JSON and raw CF types. It's a no-brainer using a tag made available as an open source project on RIA Forge by Andrew Powell: CFJSON . I downloaded it and was using it in less than two minutes on a test template. Very simple, very easy.

All of this good information led me to pose more questions, which I went on to get answers to.

Were there any performance advantages of using JSON over XML?
Was there significant differences in the length of the string produced by a JSON conversion as opposed to an XML conversion (significant in the realm of web services where that string will have to be sent over the wire)?
Were there any compelling reasons to adopt JSON in leiu of XML or WDDX?

Following are the results of me finding out the answers to those questions.

THE EXPERIMENT
Working with a 500 row recordset, each row containing 52 fields of mixed data types, including some sql text, convert it to both JSON and XML and record the results of the time it took and the resulting string length. Do this in both a local template/cfc environment AND over the wire via a web service. I used CFJSON as the JSON converter, and Ray Camden's XML.cfc as the XML converter.

THE RESULTS

LOCAL TESTS

1
	JSON	XML
String Size	539906	1250561
Time (ms)	36374	2046

2
	JSON	XML
String Size	539906	1250561
Time (ms)	36156	2640

3
	JSON	XML
String Size	539906	1250561
Time (ms)	37103	2156

WEB SERVICE TESTS

1
	JSON	XML
String Size	539906	1250561
Time (ms)	36581	2390

2
	JSON	XML
String Size	539906	1250561
Time (ms)	37832	2496

3
	JSON	XML
String Size	539906	1250561
Time (ms)	37644	2406

The string size was consistent, as expected. JSON produced a string that was 57% smaller than the same data represented as XML. Even so, the actual difference in the time it takes to send the larger string doesn't even come close to compensating for the additional time it took to produce the shorter JSON string. It took 1,370% LONGER to produce the JSON string than it did the XML string. Wow, at this point it's almost a no-brainer that I would NOT want to use JSON for much if anything. But this huge difference got me to thinking: What was so vastly different about the way the XML was being produced and the way the JSON was being produced? So I dug into the CFCs to find out.

THE DIFFERENCE
Both CFC methods make use of lots and lots of looping and string concatenation. The only glaring difference was in the way this was done. XML.CFC leverages the Java.lang.stringbuffer object and CFJSON uses straight CF string manipulation. So, I altered the portion of the method in CFJSON.CFC that converts queries to utilize the java.lang.stringbuffer object as well. The results were VERY favorable! check out these time tests after I made the switch:

CFJSON.CFC TESTS AFTER STRINGBUFFER CONVERSION
TEST	LOCAL TIME(ms)	WEB SERVICE TIME(ms)
1	2156	4437
2	2125	3843
3	2031	4022

Wow. The conversion to JSON now runs neck in neck with the XML conversion, simply by leveraging the stringbuffer object. Sweeeeet.

Okay, now that I know that JSON data strings are significantly smaller, just as fast to create, how about manipulation? How painful is that? To find out I decided I was going to use my JSON data in a javascript function. I scaled down the size of the data set to only five records and a handful of fields for this test. Check out how simple it was to 'dump' my data into javascript and to access it afterwards:

CONCLUSION
I have come to no solid conclusion as of yet, but I must say that I'm leaning heavily towards JSON in lieu of XML. It's got a smaller footprint all the way around, has the ability to capture complex nesting, and is really straightforward to navigate using Javascript. Additionally, the same way CFJSON can encode complex data, it also DECODES it on the receiving end, turning it back into something that CF recognizes.

Oh, and I think I'm going to email the CFJSON guys and let them know about the significant performance improvement I got when switching to stringbuffer. They may have already experimented in this arena, but just in case, I'll let them know.

That's it for my personal "JSON Demystification". :)

Doug out.

P.S. If you would like to see an actual JSON representation of my five record query, here it is:

{"recordcount":5,"columnlist":"complete,date_desired,dept,details,developer,due_date,est_hours","data":{"complete":["100%","100%","0%","100%","0%"],"date_desired":["Less Than 2 weeks","Less Than 1 week","More Than 2 weeks","Less Than 1 week","Less Than 1 week"],"dept":["Eligibility","Marketing","Benplan_com","Repricing","EDI"],"details":["1. For rehires, if a rehire date is present on the file and the rehire date is greater than the date of hire, then I need to use it.\r\n\r\n2. Cobra reason - make sure the term reason for a benefit is being put on the AE2 file. This is tied in with Scott\'s work.\r\n\r\n3. Some dependents have termed benefits but no effective dates, also no effective date for the employee. I need to find out where these are coming from.\r\n\r\n4. Terms need to be removed from the file 60 days after the employment term date. This will actually be added to the TSS AE2 file as well.","Dave Lawson, SWI, would like for us to send him the annual report we created for him that gives him EE and Dependent data for Group #s 90947 and 90947P.","- We will send?? or receive daily RX claims\/deductible file updates to one or two PBM\'s--Innoviant for sure and maybe Catalyst.\r\n"," 1) Show correct network for each group. 2) Change elapsed days to start counting on the day the claim was sent out. ","Correct the SSN validation on inbound EDI returned repriced claims. "],"developer":["Dan Crouch","Kelly Young","Gemma Anthony","Kelly Billen","Kelly Billen"],"due_date":["09\/09\/2004","8\/19\/04","NA","08\/30\/2004","08\/13\/2004"],"est_hours":["",1,400,1,1 ]}}

Posted by dougboude at 5:50 PM | PRINT THIS POST! | Link | 4 comments

<<	June, 2013					>>
S	M	T	W	T	F	S
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30