berniedolan

Query Data Getting Truncated? Check Your Buffer

January 18, 2007

A coworker of mine ran into a problem the other day with query data he was trying to display on a page getting truncated. The database he was querying has a text column that stores huge XML documents, and he noticed one document was getting chopped off...exactly 64000 characters into the display.

By default, ColdFusion datasources are defined with a "Long Text Buffer" size of 64000 chr -- if your query data is any larger than that, the remaining characters are lost. It's important to note that no error messages are thrown when this occurs -- CF just fills the buffer to the max and continues processing.

To resolve this issue, you have two options. Under the Advanced Settings for the datasource you can either increase the size of the Long Text Buffer, or simply check the box for CLOB to "Enable long text retrieval"...

According to LiveDocs, when you have the CLOB setting selected your queries will "return the entire contents of any CLOB/Text columns in the database for this data source. If not selected, ColdFusion MX retrieves the number of characters specified in the Long Text Buffer setting."

We ultimately decided against enabling CLOB just in case there were ever super size documents in there -- we didn't want to bring the server to its knees :) Ultimately, we bumped the Long Text Buffer size to 512000 which turned out to be sufficient for most of the documents in the database.

Comments (3) | Related Categories: ColdFusion

3 responses to “Query Data Getting Truncated? Check Your Buffer”

Steve Bryant
Jan 19, 2007 at 11:17 AM

Good advise. This one used to trip me up from time to time. Incidentally, I noticed that the unsubscribe URL has your email address in it instead of mine (not that I wanted to unsubscribe or anything)

Bernie Dolan
Jan 19, 2007 at 1:19 PM

Yeah, I noticed that with the unsubscribe URL (I have a test address in there for blog subscriptions). When I got the notification email it had YOUR address for unsubscribing. Clearly an unexpected bug with MachBlog. I disabled blog subscriptions / notifications for now and I'll open a bug report this weekend. I have another bug open for comment subscriptions since they don't appear to be sending notifications at all right now.