There's always another bug...

G'day:
Whilst playing with objectSave() and objectLoad() yesterday, I came across yet another bug in ColdFusion. It really does seem sometimes that if one attempts to do anything other than the "obvious" in CF, there are bugs. Or illogical quirks. Or omissions in the implementation.

This is just a bug (NB: in ColdFusion 9.0.1+. But not Railo).


We've given up on JSON as an option for serialisation (in CF9, anyhow), so we were looking at using objectSave() and objectLoad() instead. Initially this approach seemed to work well in serialising queries. However when I shifted the testing onto our actual DB / data environment - as opposed to test queries - I got an error straight away.

This is a specifics-neutralised facsimile of the code I was running:

<cfstoredproc procedure="getRecords" datasource="scratch_mysql">
    <cfprocresult name="numbers" resultset="1">
    <cfprocresult name="months" resultset="2">
</cfstoredproc>

<cfscript>
serialised = objectSave(numbers);
deserialised = objectLoad(serialised);
writeDump([numbers,deserialised]);
</cfscript>

When running this, I got this error:


java.lang.reflect.InvocationTargetException

The error occurred in D:/websites/www.scribble.local/jsonBungness/testObjectSave2.cfm: line 8


So I was going "goddammit!", and casting aspersions on ColdFusion's parentage etc. To try to work out where things were going wrong, I tried a more simple proc (single small recordset, vanilla data), and that worked fine. And tried a more comprehensive proc (large recordset, more complex data, with more data types involved) and it worked fine. Then I tried a third proc (multiple recordsets, etc), and it failed again.

It occurred to one of my colleagues that one difference between "working" and "not working" was that the not-working ones were all from proc calls which returned multiple recordsets. We were like "please tell me it isn't this", because:

1. that would be f***ing stupid;
2. it would invalidate this approach, because we need it to work on these procs.

I tested some more, and this theory was borne out.

What's weird, if I modified the code thus, it all worked:

<cfstoredproc procedure="getRecords" datasource="scratch_mysql">
    <cfprocresult name="numbers" resultset="1">
</cfstoredproc>

<cfscript>
serialised = objectSave(numbers);
deserialised = objectLoad(serialised);
writeDump([numbers,deserialised]);
</cfscript>

This is the same proc call, I'm just only grabbing one of the recordsets. And this is fine. If I grabbed just the second recordset instead: fine.

So let me get this straight... something about how ColdFusion returns multiple recordsets from a proc changes the nature of the recordsets themselves in such a way that it means they're not deserialisable via objectLoad(). Because, note, it's not objectSave() that's erroring, it's objectLoad(). This is another one of those occasions which has me going "what are you doing Adobe?" How can they have written code that works this way? I don't mean "how did they not notice this?", because this is edge-case-y, I know. But converting data from a JDBC result to a CF query should be the same no matter how many recordsets are returned, so how the hell can the results end up different? It's not even a case of multiple-recordset-returning procs are handled differently... it's just how CF creates the recordsets. Unbelievable.

---

Anyway, there's only so much hand-wringing one can do about this, so we cracked on with trying to come up with a solution. Another of my colleagues wondered if whatever ColdFusion was messing up with the query might be removed or mitigated if we copy the query somehow. We tried a simple assignment:

fixed = broken;

Didn't work. We didn't expect it to, really. We tried duplicate():

fixed = duplicate(broken);

Secretly hoping that when duplicating a query it got rebuild, rather than just having the value copied in memory. No.

Then I had a flash. Under the hood in the coldfusion.sql.QueryTable class, there's a few methods called populate():

public void populate(coldfusion.sql.QueryTable, int)
throws java.sql.SQLException, java.lang.IllegalAccessException

public void populate(com.allaire.cfx.Query)
throws java.sql.SQLException, java.lang.IllegalAccessException

public void populate(java.sql.ResultSet)
throws java.sql.SQLException

public void populate(java.sql.ResultSet, int)
throws java.sql.SQLException


(I used ClassViewer.cfc to pull that info out, btw).

I initially tried just this:

fixed = queryNew(broken.columnList);
fixed.populate(broken);


And this did not fix the issue. Sad face.

On a whim, I thought: "what if when populate() gets an entire query to populate from, it simply copies the passed-in query over the top of the query being populated... this would just copy the problem too". So I tried this:

fixed = queryNew(broken.columnList, broken.recordCount);
fixed.populate(broken);


This version of the method copies n rows of the passed in query into the query being populated. Doing this would mean the data would need to be copied, not simply replacing one whole query with another.

And it worked!  Hurrah!

What's better, all the date and null nonsense I'd been suffering via my attempts to get JSON to work are not issues with this approach, and it's really quick too. Win.

Now I can move on. I have raised a bug regarding this problem too: 3588308.

--
Adam