Why does cffunction returnformat="JSON" return floats for SQL varchar values?

[Guest]

Hello,

I'm calling a remote method that returns a query in JSON format using the returnformat="JSON" parameter.  It seems that ColdFusion attempts to guess the types of the return values and, for the most part, is fairly accurate except in one case: it returns floats when it should return string values.

In my particular case, my query returns a zip code, defined as a VARCHAR2 field in our database, i.e., a string.  In some cases, the value entered will be a string (e.g., 94309-1234) and other times it will resemble a number (e.g., 94309).  In the former case, the JSON format returned for that value will be correct (a quoted string value: "94309-1234").  In the latter case, for whatever reason, the value returned will be a floating point number (94309.0).

Even if I explicitly add surrounding quotes to the SQL column, ColdFusion will ignore them and return a floating point number.

Is there a solution to this issue?  I don't want to have to explicitly add a non-numeric character to the value that I then would have to strip out in order to get this to work.

Thanks in advance for your help,

-Jose

[nancyr21706845]

SSIS SQL to JSON  will help to setup input datasets for JSON Output and pass parameters to source SQL query (Dynamic at runtime).

[nic_tunney]

You don't mention CF version, but JSON serialization in ACF has been troubling.  I prefer to assemble a structure or array from the dataset and then pass that off for serialization.  Seems to work much better.  Assembling a transfer object like this for a query and then using serializeJSON() also cleans up the data structure quite a bit, returning something that is a bit easier to parse on the receiving side.  Even when things seemed to work well with serialization, eventually something would come up that made me return to this pattern.

-Nic