1 Reply Latest reply on Aug 16, 2012 6:30 AM by Reed Powell

    Reading large lob data from SQL

    Reed Powell Level 3

      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#



      #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,