Reading large lob data from SQL

Report · Aug 06, 2012

I gotta be missing something, but don't see what. Have seen postings on this topic for years, and the answer is always the same, but I've done all those things.

I have a CF script that grabs a webpage via CFHTTP, and stores its content in a SQL Server 2008 table in a TEXT datatype. It also stores at the time of the INSERT the value of the CF expression len(cfhttp.filecontent) in another column (BIGINT).

Another scripts comes along and tries to do some analysis, so it does a SELECT of the data. The most it ever gets back is 64,000 bytes. It displays the value that was stored as the length during the insert, the current datalength as reported by SQL, and the datalength of the variable in the CF resutls set from the query. The data is definitely in the database. I can also do a query in SSMS with a LIKE clause that finds "/HTML" in the data, which would also indicate the all of the data is there.

Both scripts use the same datasource. The datasource is setup (correctly I believe) as:

Login Timeout (sec)
CLOB	-- Enable long text retrieval (CLOB).
BLOB	-- Enable binary large object retrieval (BLOB).
Long Text Buffer (chr)
Blob Buffer(bytes)
Disable Autogenerated Keys

I cannot think of any other CF admin paramaters to change. This happens on multiple CF servers across multiple versions (8, 9 , 10) of CF.

The code that does the retrieval is:

		<cfquery name="results" datasource="talbot_exsto">
			SELECT page_text,page_size ,datalength(page_text) AS dblen
			FROM urlcheck..urlcheck_results WITH (nolock)
			WHERE Result_ID=#url.resultID#
		</cfquery>

#results.page_size# bytes in http results, #results.dblen# bytes in database,

#len(results.page_text)# bytes in ColdFusion resultset

for record #url.resultID#

Which outputs:

108016 bytes in http results, 108001 bytes in database, 64000 bytes in ColdFusion resultset for record 1774479

What have I missed?

thanks everone,

Reed

Report · Aug 16, 2012

Tried a few more variations on admin settings, no luck. Anyone have any ideas?

thanks

reed