asked on

Coldfusion SQL Upload PDF File then Download Problem

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">
        </cfstoredproc>
        <cfreturn rsAttachmentsByCFID />
    </cffunction>
 
    <!--- 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">
        </cfstoredproc>
        <cfreturn rsAttachmentByAID />
    </cffunction>
 
    <!--- 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#">
        </cfinvoke>
        
        <!--- 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">
        </cfstoredproc>
        
        <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#">
    </cffunction>
 
	<!--- 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>
        
		<cfscript>
        AttachDir = ExpandPath("updir");
        </cfscript>
        
        <!--- 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/vnd.ms-excel, text/plain, application/vnd.ms-powerpoint, 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 />
    </cffunction>
 
    <!--- 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">
        </cfstoredproc>
    
        <cfset varReturn = #CFSTOREDPROC.STATUSCODE#>
        <cfreturn varReturn />
    </cffunction>
 
</cfcomponent>

Open in new window

duncancumming

<cfcontent type="unknown"

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

<cfcontent type="application/pdf"

Von-Cooke

ASKER

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

RickEpnet

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.

Von-Cooke

ASKER

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

ASKER CERTIFIED SOLUTION

RickEpnet

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

elassa

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.

elassa

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.