Solved

Cfserialize (Coldfusion) erroneously outputting JSON data as numeric

Posted on 2012-04-02
14
613 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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
 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Multiple of Image Swaps 5 42
Conformation code 4 37
Jquery syntax 12 30
How do I get my jQuery function to work using 1 class name for each textbox on my page? 2 26
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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…
Suggested Courses

752 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