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.

I have no idea why this information was so difficult to find, but I’m posting it here in case I ever need it again.

To do a reverse lookup in coldfusion:

<cfscript>
rawIP = createObject(“java”, “java.net.InetAddress”).getByName(‘#ipaddress#’).getAddress();
hostname = createObject(“java”, “java.net.InetAddress”).getByAddress(‘#rawIP#’).getHostName();
chostname = createObject(“java”, “java.net.InetAddress”).getByAddress(‘#rawIP#’).getCanonicalHostName();
</cfscript>

Note — this is slow. If you’re doing them in bulk, you may need to go in small batches or set a higher timeout.

Also, this was perhaps useful: http://api.hostip.info/get_html.php?ip=#ipaddress#&position=true — returns the country, city, state, and geotag info for an IP address, where available.

Assumption: table named locations that has a location name and the location’s latitude & longitude as a single field, with lat and lon comma separated, both in signed degrees format.

This is an intentional cartesian join… so if you have a lot of locations, use with care.

<cfquery datasource=”MyDSN” name=”getDist”>
SELECT DISTINCT????????????
???????????? a.latlon AS thestart,
???????????? a.locName AS startname,
???????????? b.latlon AS theend,
???????????? b.locName AS endname
FROM???????????????? locations a
CROSS JOIN???????????? locations b
WHERE???????????????? a.locName<> b.locName
ORDER BY???????????????? a.locName, b.locName
</cfquery>

<cfoutput query=”getDist” group=”startname”>
<h4>#getDist.startname# to…</h4>
<ul><cfoutput>
<li>#getDist.endname# — #3963.0*acos(sin(listfirst(thestart)/57.295779513082323)*sin(listfirst(theend) / 57.295779513082323)+ cos(listfirst(thestart) / 57.295779513082323)*cos(listfirst(theend) / 57.295779513082323)*cos((listlast(theend) – listlast(thestart)) / 57.295779513082323))# Miles
</li>
</cfoutput>
</ul>
</cfoutput>

———————

This is based on a snippet of SQL code someone forwarded to me which was signed “RBarryYoung, 31-Jan-2009″ — so credit to RBarryYoung for it.