Solved

Cfserialize (Coldfusion) erroneously outputting JSON data as numeric

Posted on 2012-04-02
14
603 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
JavaScript waiting 14 66
Following as example - where do I place this javascript line? 6 31
Coldfusion Complex Structure Reference 4 23
Javascript and Jquery not firing 9 37
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
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…

930 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

12 Experts available now in Live!

Get 1:1 Help Now