Solved

Cfserialize (Coldfusion) erroneously outputting JSON data as numeric

Posted on 2012-04-02
14
600 Views
Last Modified: 2012-04-17
Hi,

I'm using the cfserialize command in Coldfusion to output some data into JSON format.  In the developer edition of CF it works perfectly but in a hosted environment running enterprise it's not running correctly even though everything appears the same as far as version etc.  So, what's happening is the data that is defined to be char or varchar in the database is being converted to numeric in SOME instances in the JSON output if the data itself is numeric.

Does anyone know of simple a solution to this?  

I thought a regular expression might be the best way to go though I have very little experience with regular expressions.  For my particular situation all it would have to do with the JSON is check to see if each item of data is quoted and, if it isn't, add quotes.

Any assistance would be greatly appreciated.
0
Comment
Question by:necomm
  • 8
  • 6
14 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 37796730
Yeah, the JSON stuff makes some assumptions and doesn't always convert the way you want with boolean ie yes/no/true/false and numeric strings.  If you're running CF9, you might check out the updates. There was a JSON fix in Update 1

82706 - AJAX Plumbing
When you use serializeJSON or deserializeJSON, data type conversion occurs automatically. For example, 000001 is converted to numeric 1.

A pre-CF9 fix was to append a blank space onto values you didn't want converted to force json to treat them as strings, not numbers.

        ie   "01234(blank space)" instead of "01234"
0
 

Author Comment

by:necomm
ID: 37799536
For the hosted site the version being run is 9.01 (9,0,1,274733 specifically, same as being run locally) so I would I assume 82706 is addressed in that though the problem still remains.  

I also tried appending a blank at the end of the values.  This may have worked in 8 but it isn't in version 9.

(Just noticed I type "cfserialize" rather than serializejson.)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37801607
> 9.01 (9,0,1,274733

Can you post a before and after example, because I'm not seeing the problem you're describing with that version. Numeric strings like "01234" are unchanged. There is a problem with "yes/no" strings being converted to true/false, but the space hack fixes that too. So I'm wondering what's different about your code/values.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37813694
Any progress?
0
 

Author Comment

by:necomm
ID: 37813782
It appeared to be working OK for strings with numeric data if they had leading zeros but not for those without.  Kind of strange.

I think I've worked out an alternate means of getting it to work by just modifying the Json that's output.  Not the most ideal solution but I think it will work.

I appreciate your help and follow-up.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37813879
if they had leading zeros but not for those without.  

I'm not sure you mean by that ;-) If it's numeric, no leading zero, how is it changed by json? Added decimals, etc..? I'd have to see an actual before and after sample, but I'd be surprised if the space hack didn't fix that.  

If you have time, feel free to post what you came up with (with a few sample values). Just curious if there's another option.
0
 

Author Comment

by:necomm
ID: 37813951
The field was defined in the database (zipcode) as char but it was outputting without quotes in the JSON.

I added the trailing space in the SQL but it still came out in the JSON without quotes.  Was that correct?

My "solution" was nothing that could be widely applicable or useful to anyone.  It just involved finding the numeric values that should be in quotes and then putting quotes around them.  It would not have been an effective solution were I dealing with a large set of data or data with lots of columns containing numbers that should be in quotes.

I'll work on providing an example in the next few days.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:necomm
ID: 37813980
An example of output where leading zeros postal codes had quotes but those without didn't.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37814139
The field was defined in the database (zipcode) as char but it was outputting without quotes in the JSON.

Ok, now I understand.  I'll give it a whirl to see if I get the same results and if there's another option.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37823863
FWIW I couldn't duplicate either problem with CHAR fields using sample zip codes like 12345, 22222, 01234  So I suspect it's something different in your code or data.  But that's about all I can say without your code and a concrete example.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37835563
Since you solved it yourself, don't forget to close it out by accepting your own answer or possibly deleting the thread.
0
 

Accepted Solution

by:
necomm earned 0 total points
ID: 37837488
I wound up creating a new query from the existing query (QueryNew), defining the data types within the new query as varchars then doing the serializejson.

This appears to work consistently in local and shared hosting development environments though I'm still not clear why, without this, the same code against the same database worked differently between the two environments.

Thanks very much for your help.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37838227
Yeah, I'm not sure either. But glad you found a workable solution, and thanks for posting in case someone else runs into the issue.
0
 

Author Closing Comment

by:necomm
ID: 37854907
This solution worked consistently in both a local development and shared hosting environment.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Article by: DanRollins
This article describes a JavaScript program that creates a maze made of hexagonal cells.  In Part 2 (http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/A_7850-Hex-Maze-Part-2.html), we'll extend the program by adding a depth-…
JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now