It was the strangest thing. A query using Coldfusion to a SQL 2005 server, extracting among other things, a large chunk of XML which needed to be parsed and transformed with XSL.
It worked fine most of the time… but occasionally there would be very large XML strings, and they would get cut off at 32k in length.
So, of course, I assume the database column is only that large and the data is being truncated on entry to it.
The DBA mentions that it’s an n-something data type and I immediately assume that’s the problem… darn 16 byte alphabets…
I ask him to convert it to VarChar(MAX).
The problem? Goes away. No more truncation.
I think nothing more of it… including not noticing that even the fields that were previously truncated now work. (probably because I was only parsing one day’s worth of data at a time)
Flash forward a few months… we bring it into production. Bam. Same problem, only much worse because there’s more data.
And now, because it’s in production and the database is from a vendor product, changing the db field is a huge deal.
A week or so goes by with it truncating as we talk to the vendor.
And then I think to myself… what the hell column type has a 32k limit? it’s 8k or frigging huge, with nothing in between. This is what I get for starting my database experience with MySQL… TEXT data type there is 65,535 characters; halve that for it being in unicode, and it’s about 32k. But we’re not working with MySQL here….
So I go hunting. Is it a limit in a stored procedure on insert? I can’t tell.. no acces to the procedures. Or even the database, really.
I finally whip out my CF_DBINFO tag and take a look at the table structure. It’s NText and lists the size as some huge number, way more than 32k. Weird.
I run a query to get the max length of the field and discover that len() is invalid for text fields. I research more and discover that you use datalength() for those. Again… way more than 32k. So where’s the problem???
It’s not my code… that’s a straightforward select. And why did it start working when we changed data types?
I started wondering if maybe our driver didn’t fully support SQL 2005. We had to switch from the default SQL driver that coldfusion uses because the default one doesn’t support SSL and we require encrypted connections. Would that bite us in the arse?
Yes and no. By default, the driver handles data over a given amount as a LOB… it sends it in chunks. But we didn’t have the DSN set to look for LOBs, so it would get the first 32k chunk and assume that was all.
One checkbox (CLOB – Enable Long Text Retrieval) in CF Administrator later… and everything’s magically working.
Oh, and to some extent, I was right… it appears the driver doesn’t fully understand SQL 2005. It only uses CLOBs on TEXT and NTEXT fields… VarChar(MAX) is exempted, despite being the same thing effectively. That’s why the dev system started working when we switched the data type.
So… lesson of the day… Don’t get CLOBbered. Check the box.