Skip navigation
Currently Being Moderated

Reading large lob data from SQL

Aug 6, 2012 1:02 PM

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:

    -- Enable long text retrieval (CLOB).
    -- Enable binary large object retrieval (BLOB).

 

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

 
Replies

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points