Storing and Retrieving PDFs from DB

[Dinghus]

ColdFusion 8.0.1 E (soon to be 9). Oracle 10g.

I am trying to store PDFs in the database and then retrieve them. So far storing them seems to go okay. But when I retrieve them I get an output that is just binary garbage.

Here is how I put it in the db:

<cffile action="readbinary" file="e:\pdfstorage\my.pdf" variable="testing">
<cfquery name="pdfTest" datasource="#application.datasource#">
      update pdf_data
      set thePDF =  <cfqueryparam value="#testing#" cfsqltype="cf_sql_blob">
    where PDFIDX = 1
</cfquery>

Of course the PDFIDX would change based on other code.  Basically we have a form that is filled in and stored and then a PDF is attached. Right now they are just storing them in the pdfstorage but they want them in the database instead.

To retrieve I am doing this:

<cfquery name="pdfTest" datasource="#application.datasource#">
    SELECT thePDF
    FROM pdf_data
    WHERE PDFIDX = 1
</cfquery>
<cfcontent type="application/pdf" variable="#pdfTest.thePDF#" reset="yes">

My output looks like this:

%PDF-1.5 %���� 465 0 obj <> endobj 477 0 obj <>/Filter/FlateDecode/ID[<633FD514E1E0FD0B97BAD1F9EEAC7F53><16186C0CAA988247B742A0375C859E99>]/Index[465 19]/Info 464 0 R/Length 69/Prev 31457/Root 466 0 R/Size 484/Type/XRef/W[1 2 1]>>stream h�bbd ``b`Z $ YA� �`� �m �JA,  ! �  

HELP!

[Dinghus]

Well, crud. My cf didn't come through. Grrrrrrr.

Here is the input:

<cffile action="readbinary" file="e:\pdfs\thepdf.pdf" variable="testing">
<cfquery name="pdfTest" datasource="#application.datasource#">
     update report_data
    set attachment =  <cfqueryparam value="#testing#" cfsqltype="cf_sql_blob">
    where rep_id = 84050
</cfquery>

Then here is how I pull it out:

<cfquery name="pdfTest" datasource="#application.datasource#">
SELECT attachment
    FROM report_data
    WHERE rep_id = 84050
</cfquery>
<cfcontent type="application/pdf" variable="#pdfTest.attachment#" reset="yes">

[JR__Bob__Dobbs]

Are the "CLOB" and "BLOB" options enabled in your CF datasource?

http://help.adobe.com/en_US/ColdFusion/9.0/Admin/WSc3ff6d0ea77859461172e0811cbf364104-7fe2.html

[JR__Bob__Dobbs]

What is the data type of the "attachment" column in your table?

[Dinghus]

Okay, first I will answer your questions.

The BLOB/CLOB are turned on.

The datatype is BLOB

BUT I went on to do something else and then came back to the page with the pdf stuff on it and it worked. Huh? The only thing I can think is that running the read/store/recover/display all at the same time messed it all up. I had commented out the read/store part just to see the results. Closing the page and reopening it seems to have fixed the problem.  Have to test that more to be sure but right now I am getting the correct output. Stranger than fiction.

Of course NOW I need to get it into a variable so I can put watermarks etc on it. Got any clues on that?

How to get it from database to cfpdf?

[tclaremont]

Have you considered CFDOCUMENT?