Saving/Downloading files to/from Oracle using ColdFusion

Here is how to store and download Excel files to/from an Oracle database using ColdFusion.

First, create a table to store the files as BLOB’s:

SQL> create table files (
  2     file_id raw(16) default sys_guid() primary key,
  3     file_content blob)
  4  /

Table created.

Then, create a ColdFusion template to upload and save your local Excel file into the Oracle table.

Here is a file upload form, pure and simple HTML here, no CFML tags, let’s call it upload.html:

<form action="upload_action.cfm" 
    method="POST" 
    enctype="multipart/form-data">

    File Name: 
    <input 
        name="FileName" 
        type="file">

    <input 
        name="Submit" 
        type="submit" 
        value="Save to DB">

</form>

And here is upload_action.cfm:

<cfif isdefined("Form.Submit") 
    and trim(form.FileName) neq "">
    <cftry>
        <!---  
          Upload the file to a folder on the webserver and 
          outside the web root.
        --->
        <cffile action="upload"
            filefield="form.FileName"
            destination="C:\temp\"
            nameconflict="makeunique" 
            accept="application/octet-stream, application/vnd.ms-excel">

            <!---  
              The file is on the web server now. Read it as a binary
              and put the result in the ColdFusion variable file_blob
            --->
            <cffile 
                action = "readbinary" 
                file = "C:\temp\#cffile.serverFile#" 
                variable="file_blob">


            <!---  
              Insert the ColdFusion variable file_blob
              into the table, making sure to select
              cf_sql_blob as the sql type.
            --->
            <cfquery name="q" datasource="#dsn#">
                insert into files (file_content)
                values (
                    <cfqueryparam 
                        value="#file_blob#" 
                        cfsqltype="cf_sql_blob">
                )
            </cfquery>

            <!---  
              No  need to keep the file on the webserver
              because it was just stored in the database. So,
              delete it from the folder.
            --->
            <cffile 
                action="delete" 
                file="C:\temp\#cffile.serverFile#">                 

            File successfully saved.

    <cfcatch type="application">
        You've got an error!        
    </cfcatch>
    </cftry>
</cfif>

If you run upload.htm and submit to upload_action.cfm by providing a path to your local Excel file, you will end up with a new row in the table files:

SQL> select file_id,
  2      dbms_lob.getlength(
  3          file_content
  4      ) file_size_bytes
  5  from files
  6  /

FILE_ID                          FILE_SIZE_BYTES
-------------------------------- ---------------
0B2284648FFB5227E040DE090A0A1441           44544

Now, let’s create a ColdFusion template to download the stored file, name it download.cfm:

<!---  
  myFile.xls will be the default file name 
  when prompted to download. 
  You can name it whatever.
--->
<cfheader 
    name="content-disposition" 
    value="attachment; filename=myFile.xls">

<!---  
 Query a specific file id (which 
 can be a URL paramter if you like) returning
 file_content into q.file_content
--->
<cfquery name="q" datasource="#dsn#">
   select file_content
   from files
   where file_id = 
    <cfqueryparam 
        cfsqltype="cf_sql_varchar" 
        value="0B2284648FFB5227E040DE090A0A1441">
</cfquery>

<!---  
 Set the MIME content encoding header to ms-excel and 
 send the contents of q.file_content 
 (containining the binary data) as the page output.
--->
<cfcontent 
    type="application/vnd.ms-excel" 
    variable="#q.file_content#">

When you run download.cfm, your browser will prompt you to download myFile.xls and save it locally on your machine (or just open it if you want).

You can also slightly modify the above code to manipulate other types of binary files, like Word documents and images…

Possibly related:

Tagged , | Post a Comment