Coldfusion SQL Upload PDF File then Download Problem

Posted on 2009-04-23
Last Modified: 2013-12-24
Howdy Experts,

Thanks for all your help thus far with my unfortunate situation and thanks in advance for any help yall can provide with my last (hopefully) situation.  Okay...We had an web app developed for us (at least mostly) but this one following feature does not work.  We are running a Windows Server 2003 Server, Coldfusion Version 8,0,1,195765, IIS, and SQL Server 2005 Express.  Like a few other folks on this forum (I tried those solutions) we can upload a PDF to the SQL DB but when we attempt to download it says "There was an error opening this document.  The file is damaged and could not be repaired."  We can upload .doc, .xls, .msg, and .ppt files with no problems whatsoever - only happens with PDF files.  This problem occured when it was previously on a hosted server and well as now after we moved the app to an internal local server.  Please bear with me as I am not experience in this field although unfortunately it has fallen upon me to make it work.  Thanks again for any help yall can provide, I hope I have provided enough information to allow for that.  
<!--- Attachments.cfc --->

<cfcomponent displayname="Attachments">

    <!--- Get list of Attachment_IDs for the CaseFile_ID. --->

    <cffunction name="getAttachmentsByCFID" output="no" access="public" returntype="query">

        <cfargument name="cfid" type="numeric" required="Yes">

        <cfstoredproc procedure="pr_Attachment_Get_AIDs_By_CFID" datasource="#Application.CDP_DSN#">

            <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="#arguments.cfid#" null="No">

            <cfprocresult name="rsAttachmentsByCFID">


        <cfreturn rsAttachmentsByCFID />


    <!--- Returns (name, extension, description) for the input Attachment id. --->

    <cffunction name="getAttachmentByAID" output="no" access="public" returntype="query">

        <cfargument name="aid" type="numeric" required="Yes">

        <cfstoredproc procedure="pr_Attachment_Get_NameExt_By_AID" datasource="#Application.CDP_DSN#">

            <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="#arguments.aid#" null="No">

            <cfprocresult name="rsAttachmentByAID">


        <cfreturn rsAttachmentByAID />


    <!--- Dowonloads the document content for the input Attachment id. --->

    <cffunction name="downloadAttachment" output="yes" access="remote">

        <cfargument name="aid" type="numeric" required="Yes">

        <!--- Get the file name and extension. --->

        <cfinvoke method="getAttachmentByAID" returnvariable="rsAttachmentByAID">

            <cfinvokeargument name="aid" value="#arguments.aid#">



        <!--- Get the Attachment content for downloading. --->

        <cfstoredproc procedure="pr_Attachment_Get_Content_By_AID" datasource="#Application.CDP_DSN#">

            <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="#arguments.aid#" null="No">

            <cfprocresult name="rsAttachmentContent">



        <cfheader name="Cache-Control" value="private">

        <cfheader name="content-disposition" value="attachment; filename=#rsAttachmentByAID.File_Name#.#rsAttachmentByAID.File_Extension#">

        <cfcontent type="unknown" variable="#rsAttachmentContent.File_Content#">


	<!--- Creates a new Attachment. --->

	<cffunction name="uploadAttachment" output="no" access="public" returntype="numeric">

		<cfargument name="file_field" type="any" required="Yes">

		<cfargument name="cf_id" type="numeric" required="Yes">

        <cfargument name="file_description" type="string" required="No">


		<cfset varNewID = 0>



        AttachDir = ExpandPath("updir");



        <!--- AttachDir [<cfoutput>#attachdir#</cfoutput>]<br>

        arguments.file_field [<cfoutput>#arguments.file_field#</cfoutput>]<br> --->

		<!--- Upload the file to a folder on the webserver. --->

		<cffile action="upload" filefield="#arguments.file_field#" destination="#AttachDir#" nameconflict="makeunique" accept="application/octet-stream, application/msword, application/, text/plain, application/, application/pdf">

        <cfset varFileName = #TRIM(clientFileName)#>

        <cfset varFileExt = #TRIM(clientFileExt)#>


        <!--- varFileExt [<cfoutput>#varFileExt#</cfoutput>]<br>

        varFileName [<cfoutput>#varFileName#</cfoutput>]<br>

        cffile.serverFile [<cfoutput>#cffile.serverFile#</cfoutput>]<br> --->


        <!--- Read file as a binary and put the result in the ColdFusion variable file_blob. --->

        <cffile action = "readbinary" file = "#AttachDir#\#cffile.serverFile#" variable="file_blob">


        <!--- Upload to the database. --->

        <cfstoredproc procedure="pr_Attachment_Insert" datasource="#Application.CDP_DSN#" returncode="Yes">

            <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#varFileName#" null="No">

            <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#varFileExt#" null="No">

            <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="#arguments.cf_id#" null="No">

            <cfprocparam type="In" cfsqltype="CF_SQL_BLOB" value="#file_blob#" null="No">

            <cfprocparam type="In" cfsqltype="cf_sql_varchar" value="#file_description#" null="No">

        </cfstoredproc><!--- RETURN @Attachment_ID --->

        <cfset varNewID = #CFSTOREDPROC.STATUSCODE#>

    	<!--- Clean up the file in the temp directory. --->

    	<cffile action="delete" file="#AttachDir#\#cffile.serverFile#">


    	<cfreturn varNewID />


    <!--- Deletes an Attachment by a_id. --->

    <cffunction name="deleteAttachment" access="public" returntype="numeric">

        <cfargument name="aid" type="numeric" required="Yes">

        <cfstoredproc procedure="pr_Attachment_Delete_By_AID" datasource="#Application.CDP_DSN#" returncode="Yes">

            <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="#arguments.aid#" null="No">



        <cfset varReturn = #CFSTOREDPROC.STATUSCODE#>

        <cfreturn varReturn />



Open in new window

Question by:Von-Cooke
    LVL 16

    Expert Comment

    <cfcontent type="unknown"

    Would this work if you put in the correct mime type?

    <cfcontent type="application/pdf"

    Author Comment

    Ive attempted changing the type to application/pdf but to no success
    LVL 14

    Expert Comment

    This is really not the best way to do this. Especially with SQL Express. You should save the file to a Windows 2003 folder and then put in the database a pointer to the file. SQL Express has a size limit non he database and if you are storing binary file in the database you may reach that limit rather fast.

    I know this does not answer you question but just thought you should know.


    Author Comment


    Thank you so much for the suggestion - is it going to require a lot of code changes? I'm a novice at this stuff
    LVL 14

    Accepted Solution

    Add a new Field to the database called filename Data Type nvarchar(255).

    Disregard this Field
    <cfprocparam type="In" cfsqltype="CF_SQL_BLOB" value="#file_blob#" null="No">

    Insert this #cffile.serverFile# into this filename field.

    Delete delete this line

    <cffile action="delete" file="#AttachDir#\#cffile.serverFile#">

    Then just point to the path and the database verbal for filename when you want to reference it on the web page.

    Expert Comment

    Im getting the exact same error, with both PDF and Zip files.  I need to store the files in the database, because Im using clustered servers.  Please help.

    Expert Comment

    In your Answer, you are uploading the file to the server only.  I need to upload the file to the database.  I have multiple internal servers (behind the firewall).  In your solution, you cant guarantee the server location of the uploaded file.  I also need to display the same file on outside servers in the DMZ.  Uploading the file to the database is the only way possible.  Thank you for your help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    The Adobe PDF proprietary file format is recognized as secure and formulated. But these PDF files are also prone to corruption and any external threat like virus attacks, improper storage can hit PDF file integrity.This type of damages can make cruc…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this first video of the three-part Xpdf series, we introduce and describe Xpdf, a library containing nine command line utilities that perform various functions on PDF files. We show where the library is located and how to download it, discuss its…
    In this third video of the Xpdf series, we discuss and demonstrate the PDFtoText utility, which converts PDF files into plain text files. Download and install the software.: You may have already downloaded and installed the Xpdf tools while watching…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now