LOBs Gotcha in ColdFusion

A few weeks ago I wrote a post about how to Save/Download files to/from Oracle using ColdFusion. The example I used in the post was about saving Excel files as a BLOB in an Oracle table.

Today, I ran across an issue with file sizes greater than 244 KB. I was testing with a 7 MB Excel file. The file was correctly saved in Oracle and I could see that the BLOB size was indeed 7MB (using dbms_lob.getlength). However, when I tried to download it, it would not download more than 244 KB.

After some research, it turns out that, in the ColdFusion administrator (CFMX V7), if you go to Data Sources under Data & Services and edit the data source you used to retrieve the BLOB, there is a setting under the Advanced Settings to Enable binary large object retrieval (BLOB). In my case, it was unchecked. So, make sure to check this checkbox if you want to return the entire contents of any BLOB column in the database for your data source. If not checked, ColdFusion MX retrieves the amount specified in the BLOB Buffer setting, which, in my case, was 250000 bytes or approximately 244 KB. That explains it!

To summarize, if your application uses BLOBs or CLOBs to store/retrieve data from the database and you do not know (or do not want to enforce) what the maximum size of your BLOB or CLOB data is, make sure to check the Enable binary large object retrieval (BLOB) or Enable long text retrieval (CLOB) settings for your datasource in the ColdFusion administrator.

Possibly related:

Tagged , | Post a Comment