[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 777
  • Last Modified:

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

0
Von-Cooke
Asked:
Von-Cooke
  • 2
  • 2
  • 2
  • +1
1 Solution
 
duncancummingCommented:
<cfcontent type="unknown"

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

<cfcontent type="application/pdf"
0
 
Von-CookeAuthor Commented:
Ive attempted changing the type to application/pdf but to no success
0
 
RickEpnetCommented:
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.

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Von-CookeAuthor Commented:

Thank you so much for the suggestion - is it going to require a lot of code changes? I'm a novice at this stuff
0
 
RickEpnetCommented:
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.
0
 
elassaCommented:
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.
0
 
elassaCommented:
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.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now