3

Insert to MS-SQL 2008 Database

<cffile action="READBINARY" file="#form.FileContents#" variable="binPDF">
<cfquery name="Uploaded" datasource="#cfbasedatasource#">
INSERT INTO UploadedFiles
    (
    AccountId,
    Filecontent
    )
VALUES
    (
    '#UrlAccountId#',
    <cfqueryparam value='#binPDF#' cfsqltype='cf_sql_blob'>
    )
</cfquery>

========================= This is Show from Database to browser:

<cfset binaryData = #cfGetfilecontentquery.Filecontent[1]#>
<cfheader name="Content-Disposition" value="inline; filename=testDocument.pdf">
<cfcontent variable="#binarydata#" type="application/pdf" reset="yes" />

But I can only see in browser "Fail to Load PDF Document"

agabrys
  • 8,728
  • 3
  • 35
  • 73
Sung Ham
  • 33
  • 1
  • 3
  • Which dbms? What is the data type of the Filecontent column? Also, did you verify the full binary is being saved? What happens if you write the binary to a .pdf file on disk and open it there. – Leigh Oct 13 '15 at 21:07
  • 2
    Your code is correct in principle. Make sure that the settings in the CF admin allow for blob data - otherwise it may be truncated in the DB. – Mark A Kruger Oct 13 '15 at 21:17
  • DB type MS-sql 2008 , fieldtype is Varbinary(MAX) – Sung Ham Oct 13 '15 at 21:26
  • Thank you Mark, I check mark in advanced datasource setting. It's working thanks again. – Sung Ham Oct 13 '15 at 21:35
  • @MarkAKruger - you should post that as answer to close out this thread. – Leigh Oct 16 '15 at 17:33
  • @SungHam - Side note, for performance and security reasons, you should use cfqueryparam on all of the query parameters, not just the blob. – Leigh Oct 23 '15 at 19:57

1 Answers1

7

Your code is correct in principle. Make sure that the settings in the CF admin allow for blob data - otherwise it may be truncated in the DB. It's pretty typical to not allow BLOB data through the driver (by default it is NOT allowed in the DSN settings). If you enable it you should be good.

I'm posting this answer so you can flag it as correct based on the comments (per Leigh's suggestion). :)

Mark A Kruger
  • 7,183
  • 20
  • 21
  • This answer should be marked as the solution. I had a similar issue where PDFs downloaded would load as "damaged." When I checked "Enable binary large object retrieval (BLOB)" in the Advanced Settings of the DSN in the ColdFusion administrator, the PDFs downloaded and opened properly. BTW, thank for this pointer. – DerHaifisch Jun 22 '18 at 21:18
  • FYI, I was doing the same thing and "Enable binary large object retrieval (BLOB)." on the data source was the solution. – Panman May 02 '19 at 14:06