PDF binary variable won't save in Oracle blob

[awi_fla]

I am reading in a small, legitimate PDF as a binary variable and them immediately saving that variable into a blob. But the blob doesn't store the data.

<cffile action="readbinary" file="C:\Test_Files\test.pdf" variable="testdoc">

now shows isbinary(testdoc) = YES

In fact, I can output to the screen using cfcontent, and it displays fine.

But if I try to save the testdoc variable to an Oracle blob, CF seems to think it is trying to insert an empty variable.

<cfquery name="ins" datasource="#request.a_datasource#">

INSERT

INTO PROD_WCT.T_DOCS (

PDF_BLOB

)

VALUES (

<cfqueryparam value="#testdoc#" cfsqltype="CF_SQL_BLOB">

)

Now if I retrieve that row (query name=”get_pdf”) and check the value, it appears empty:

now shows isbinary(get_pdf.pdf_blob) = NO

Nothing is done in betweent the cffile and the insert. Any ideas?

[ilssac]

You have activiated the ability to work with BLOB data in your data source name configuration in the ColdFusion Administrator right?

BLOB data can be a big preformance hit to data connections.  They are not enabled by default.

[awi_fla]

Yes, in cfadmin I have checked the box to enable blobs under the datasource.

[GAV1011]

http://awads.net/wp/2006/02/21/lobs-gotcha-in-coldfusion/

[awi_fla]

Just an update here to note that this problem has not yet been resolved.

This could be in incompatibility between Oracle 10g BLOBs and Coldfusion 8.

To review, my BLOB setting in CFADMIN is enabled. To make this as simple and possible, I created a PDF from the words "Test text," so the PDF document can't get much smaller. The PDF does save to the BLOB, because in TOAD I can save the BLOB contents to a file and view it as a PDF just fine. It's when I retrieve the record in CF8 where the trouble arises. Coldfusion 8 doesn't get anything from the BLOB. It has a zero length, and isbinary = NO. We are using Oracle 10g. And using CF8. My understanding is that an Oracle blob uses pointers to locate the blob data in an area external to the database. Apparently, Coldfusion cannot retrieve the data. The process worked for the first few records when retrieving a blob and displaying it with cfcontent, but after a few records it can no longer retrieve the subsequent blob records. It just abruptly fails at that point. But again, the PDF binary data is there in TOAD.

Could this be a bug or incompatibility with Oracle 10g? We are a state government, and it sure would help if the Adobe people could give come help here.

Thanks in advance for any thoughts.

[ilssac]

ColdFusion doesn't retreive the information and knows nothing of pointers.  That is the database's job.  ColdFusion sends a request for data, in the form of a SQL string.  The databases parses that string, retreives the requested data and sends it back to ColdFusion.  ColdFusion then can make use of the data in the record set variable into which ColdFusion put it

The missing piece in that broad flow description, is the database driver that lives between the ColdFusion application server and the Oracle database server.  ColdFusion can make use of serveral database drivers when talking to Oracle.  A native Oracle JDBC4 driver, if you have the Enterprise editions of ColdFusion.  A "JDBC Thin Client" if you have installed it from Oracle and configured ColdFusion to use it, or an ODBC-JDBC bridge driver, if you have installed Oracle ODBC client on the ColdFusion server.

I have experienced differences between these drivers when connecting to Oracle databases.  Sometimes really frustrating issues.

What driver are you using and which ones have you tried?

[ilssac]

Also, you have adjusted the Blob Buffer(bytes) setting in the datasource advanced settings right?  This defaults to 64,000 bytes  (63.5k) as the maxium block of data that can be retreived from a blob request.  I am not experienced enough to know if that is a per record maximum or total recordset maximum.  But it could very well be in play here.

[awi_fla]

Wow. Problem resolved. We had a corrupt table. Now works as expected. Thanks all.