Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
Yesterday, I was trying to use the arrayContains() method introduced in ColdFusion 9. Unfortunately, it wasn't working. Even when I double-checked all the values in play, arrayContains() kept telling me that a known value was not in the given array. I tried switching over to arrayFind(), but unfortunately, this exhibited the same broken behavior. After about 20 minutes of debugging, I finally isolated the behavior to an odd interaction between deserialized Form data and database integers.
NOTE: I am running ColdFusion 9.0.1. I have not checked to see if this behavior also exists in ColdFusion 10.
Ok, so imagine this scenario: the web-user submits a list of IDs in a form post. The client serializes the data, posts it to the server, where ColdFusion is then used to deserialize it. This deserialized collection is then tested against a query from the database, and records are updated as necessary.
Seems rather straightforward, right? Well, as it turns out, there's something about the serialization / deserialization process that prevents INT values coming out of the database from being "found" in the deserialized collection being submitted by the user. To see this in action, take a look at the following code:
<cfoutput>
<!---
Get our users from the database. In this database schema, the
[ID] column is a INT.
--->
<cfquery name="users" datasource="testing">
SELECT
id,
name
FROM
`user`
<!--- Make sure we have IDs for our example. --->
WHERE
id IN ( 1, 2, 3, 4, 5 )
ORDER BY
id ASC
LIMIT
5
</cfquery>
<!---
Now, let's pretend that the user submitted some data in the
form of an array of user IDs. And, that we have to check those
IDs against the collection in the database. That's why we are
passing it through serialization / deserialization - to mimic
the data transformation for a FORM post.
--->
<cfset selectedUserIDs = deserializeJson(
serializeJson( [ 1, 4 ] )
) />
<!--- Output the users. --->
Selected User IDs: #arrayToList( selectedUserIDs )#<br />
<br />
<!---
Loop over the users to see if any of them match the IDs
submitted by the user form data.
--->
<cfloop query="users">
User #users.id# selected:
<!--- Use database ID, as-is. --->
#arrayContains( selectedUserIDs, users.id )#
-
<!--- Use "stringified" user ID. --->
#arrayContains( selectedUserIDs, toString( users.id ) )#
<br />
</cfloop>
<br />
<!--- As a sanity check, let's try different values manually. --->
As Number: #arrayContains( selectedUserIDs, 1 )#<br />
As String: #arrayContains( selectedUserIDs, "1" )#<br />
As Java: #arrayContains( selectedUserIDs, javaCast( "int", 1 ) )#<br />
</cfoutput>
Here, I am manually running the data through serializeJson() and deserializeJson() in order to mimic the form post lifecycle. Then, I see which known user IDs correspond to the ones submitted by the user. When I run the above code, I get the following output:
Selected User IDs: 1,4
User 1 selected: NO - YES
User 2 selected: NO - NO
User 3 selected: NO - NO
User 4 selected: NO - YES
User 5 selected: NO - NOAs Number: YES
As String: YES
As Java: YES
The important part is the middle output, where I am looping over the users and checking to see if each user ID is found in the submitted collection. For each user record, I am performing two checks: one with the raw database value; one with a "stringified" value. And, as you can see from the output, only the stringified version can be found in the deserialized collection.
After to I perform the query loop, I then run a few sanity checks to see if I can manually find the ID, "1", in the collection. I try using a number, a string, and a Java int. All three can be found.
If I remove the serialization / deserialization step, everything works as expected - the database values can always be found in the collection. And, since I can manually find a Java-Int in the collection, it seems that the strange behavior specifically affects ColdFusion query values being checked against deserialized JSON (JavaScript Object Notation) data.
Super odd, right? Unless I am totally missing something. Also, if it wasn't clear from my introductory paragraph, I found that both arrayFind() and arrayContains() are adversely affected by this interaction.
Want to use code from this post? Check out the license.
Reader Comments
Could your problem be that "users.id" is actually an ARRAY, not a single value? Perhaps try it again with "users.id[1]" (I only have CF8 here at work).
@Tom,
Good thought, but no dice. Both of these still exhibit the same behavior:
users.id[ users.currentRow ]
users[ "id" ][ users.currentRow ]
It's just something whacky happening with the data representations!
When I c&p and run on cf10, I get:
Selected User IDs: 1,4
User 1 selected: YES - YES
User 2 selected: NO - NO
User 3 selected: NO - NO
User 4 selected: YES - YES
User 5 selected: NO - NO
As Number: YES
As String: YES
As Java: YES
@Dana,
Awesome - so it looks like this bug was fixed in ColdFusion 10. Thanks so much for double-checking that.
@Ben:
What version of Java are you using?
Also, did you test users.id to see what Java reports as the data type? I wonder if it's not a Java primitive data type, but getting returned as something else--which is why it's not returning the value.
If this is the case, it could lead to issues elsewhere when the integers are serialized/serialized.
@Ben,
I think the problem is that we're used to loose typing in ColdFusion, like JavaScript. If a value is a number but it's needed in an expression to be a string, noooo problem.
I've encountered this CF9 problem before. It's not really related to SerializeJSON or DeserializeJSON. It's that values from the database are more strongly typed than we're used to in ColdFusion.
My problem was with dates, not integers. And I got around my problems using JavaCast(), not ToString(). But the problem was essentially the same. You were doing a comparison of a String to a non-String and failing because the non-String was retaining its type.
It's good to know that CF10 treats query results more like regular CF variables.
@Dan,
According to the CF Admin, I'm running Java "1.6.0_45". As far as the DB column, in the database it's an INT. I'll see if I can dig into what CF sees it as.
@WebManWalking,
But here's what I don't understand, at the end of the code demo, I manually try to search for number, string, and Java Int in the collection and it can find all of them. So maybe its coming out of the database as a Long or something like that.
Either way, I'm glad it's fixed in CF10.
@Ben,
Are you talking about
As Number: YES
As String: YES
As Java: YES?
If so, that's with 3 different ways of referencing the constant 1, not users.id[1]. Query object references(*) are what seem to be adversely retaining their datatypes in CF9.
(*) It's possible that other-scope references that came directly from query objects by straight cfset may also retain datatype. It's been a while, so I forget. Every so often I see folks using cfset something = "#query.column#" and I think "What a n00b. Don't they know they can just cfset something = query.column instead?" But given that you definitely throw away database datatype with a quoted expression, it may not be so unsophisticated after all. Depends on whether or not cfset something = query.column causes something to retain datatype too.
On the other hand, if by "at the end of the code demo", you meant something in the video, sorry, I haven't seen the video. I uninstalled Flash over 2 years ago. One of the things I like about your site is how you lay out your findings as both video and text. You let people choose their own preferred learning style. I'm one of your text users.
@WebManWalking,
I meant the code at the bottom (not the video). I did try to experiment with an intermediary variable, like:
... but this also failed. Could just be the way the references are passed around. Just some funky kind of stuff!
Glad you like the text / video combo. I really need to work on how to get that video to be HTML5 - the Flash only aspect is kind of super lame :(
@Ben,
Ah, so it was indeed how I vaguely remembered it to be: A direct assignment
causes value to retain the sticky datatype of the query column. Although unnecessary in all other situations, n00bs are right to use
when the quoted assignment involves a non-string datatype that you want to make unsticky.
@WebManWalking,
Ha ha, let's us never speak of justifying "##" notation again :P
Totally unrelated to the problem but watching the video made me want to ask. I'm using my mac more and more for coding - On my desk - In the Sun - with a glass of iced tea. Beats staying in the office.
My question - what IDE did you use in the video? I've used dreamweaver8 for YEARS on the pc because it does what I need. Now I need something for the mac air. Tnx
@Tony,
For the past year, I've been using SublimeText2 (though I think an update is about to come out). I started using it when everyone at cf.Objective() kept going on and on about how awesome it is.
What I like about it:
* Very fast - loads, opens, and searches files extremely fast.
* CMD+P - search for file names in the current project / browser. I can't live without this feature.
* CMD+R - quickly search/jump to a function in the current file. Amazing stuff!
* Multiple cursors - easily add cursors to all lines in a given text selection then type at them all at once. Supports copy/paste. Amazing stuff!
And that's (from what I hear) just the tip of the iceberg. Apparently there's like a million more features and built-in command line and all kinds of craziness that I don't even know about.
Anyway, I love it.
I came across the same issue when using two sets of ArrayFind insdie separate query loops on the same page.
I had to use tostring to find on the second set.
<cfset j = ArrayFind(Myengineer, toString(RsFindCover.engineer_id))>