Cfserialize (Coldfusion) erroneously outputting JSON data as numeric

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.
necommAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
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"
necommAuthor Commented:
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.)
_agx_Commented:
> 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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

_agx_Commented:
Any progress?
necommAuthor Commented:
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.
_agx_Commented:
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.
necommAuthor Commented:
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.
necommAuthor Commented:
An example of output where leading zeros postal codes had quotes but those without didn't.
_agx_Commented:
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.
_agx_Commented:
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.
_agx_Commented:
Since you solved it yourself, don't forget to close it out by accepting your own answer or possibly deleting the thread.
necommAuthor Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_agx_Commented:
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.
necommAuthor Commented:
This solution worked consistently in both a local development and shared hosting environment.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.