Skip to main content
Inspiring
September 2, 2010
Question

Storing and Retrieving PDFs from DB

  • September 2, 2010
  • 2 replies
  • 1094 views

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!

This topic has been closed for replies.

2 replies

Inspiring
September 2, 2010

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

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

Inspiring
September 2, 2010

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


Inspiring
September 2, 2010

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?

Inspiring
September 2, 2010

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">