msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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…


Filed in ColdFusion, Oracle on 25 Jan 06 | Tags: ,


Reader's Comments

  1. |

    Eddie,

    Thanks. It took us quite a while to figure out on our own what you did with files in blobs. But I want to take it one step further. I’d like to perform keyword searches on those files in the blob. Do you have any clues on this topic. I figure I could make an index of the blob table and search it for keywords. What do you think?

    Thanks for help, Carl

  2. |

    Carl, the answer to your question is Oracle interMedia. More info on AskTom.

  3. |

    […] A few weeks ago I wrote a post about how to Save/Download files to/from Oracle using ColdFusion. The example I used in the post was about saving Excel files as a BLOB in an Oracle table. […]

  4. |

    Thanks for the code. I tried it for dpwnloading a file from oracle.I got the following error: Attribute validation error for tag CFCONTENT. The tag does not allow the attribute(s) VARIABLE. The valid attribute(s) are DELETEFILE,FILE,RESET,TYPE.

  5. |

    The VARIABLE attribute of the CFCONTENT tag is new as of ColdFusion MX 7. It is not available in prior versions.

  6. |

    When i changed the last line of your code to say “File” instead on “variable” I got the following error.

    “ByteArray objects cannot be converted to strings. “

  7. |

    Thanks for your post. I translate and explain it in italian on my blog. One only ask. Is possible to insert blog object on DB, without using: ?

    Thanks

  8. |

    Sorry Merlinox, but I did not actually get your question.

  9. |

    Hi Eddie, Thanks for the good piece of code to upload and download files into oracle. How to store Files with filenames formed using Chinese Characters?

  10. |

    Hi Eddie, the code was a great help!

    I’m experiencing problems downloading certain file types (e.g. PDFs, Powerpoint, RTF) from an Oracle BLOB column.

    They seem to upload fine with the content/sub_types being:

    application/pdf application/vnd.ms-powerpoint

    But, I get this message downloading a PDF file:

    “There was an error opening this document. The file is damaged and could not be repaired”

    And, I get a message similar to this downloading a PowerPoint file:

    “PowerPoint can’t open the type of file represented by ViewScreen[1].ppt”

    One thing I found with PowerPoint, I removed and image from the document and shrunk it from about 13 pages to around 4 pages and it downloaded fine.

    I don’t have an example with RTF.

    Word, Excel, and Text files upload and download fine.

    Sorry for the long winded example, but have you experienced this before and do you know of a fix?

    Thanks in adavance,

    Jeff

  11. |

    Jeff, maybe this will help: LOBs Gotcha in ColdFusion.

  12. |

    Hi Eddie, that seems to have done the trick. Thanks a lot for your quick and accurate response! It was very much appreciated!

  13. |

    the above snippet is erroring out in 6.1MX, is there an alternate. please help. thanks in advance

  14. |

    Thanks a lot! You saved my day :) Tested in CF7 and it works

    Thanx Panos

  15. |

    Hi Eddie, this was a great article “Saving/Downloading files to/from Oracle using ColdFusion” my only problem is…I am using CFMX 6.1 and the doesn’t support the “variable” attribute. Any help would be greatly appreciated. THANK YOU

  16. |

    I have not tested this, but If you have CFMX 6.1 what you can do is save the file content that you query from the database into a file on the server (using cffile). Then, use something like the following to download the Excel file to the client machine:

    <cfcontent type=”application/vnd.ms-excel” file=”c:tempmyFile.xls”>

  17. |

    that worked!! THANK YOU

  18. |

    Hi Eddie, Thank you for the code. I am still have a problem after upload excel file and the error message popup. insert into files (file_content) values ( ) File successfully saved. You’ve got an error!

    Please advise.

  19. |

    hello, I’m using cfcontent tag and using the “file” attribute to point to an Excel file that I fetched out of our Oracle database. It works great for smaller files but blows-up on larger ones. Is there a better way to do this in ColdFusion? I’m using CFMX6.1. THANKS

  20. |

    Jim, I’m not sure what you mean by “blows-up” but this may help you: http://awads.net/wp/2006/02/21/lobs-gotcha-in-coldfusion/

  21. |

    Hi, I did find out the CFSQLType=”CF_SQL_blob” does not work on oracle database, but do not know what need to replace “CF_SQL_blob” to make it work, I have been research and did not find the solution, please help. THANKS in advances,

  22. |

    Thanks Eddie, that was it…I had to make a change in the ColdFusion console. Then it worked fine. THANK YOU

  23. |

    Hi Eddie, I am using CF5 . it would be great help if you could guide me How to upload and download the files from database using CF5. thanks in advance.

  24. |

    Swamy,

    I’m afraid I cannot be of help here because I do not use CF5. You may want to ask your question on the ColdFusion support forums.

  25. |

    Hi Eddie, great article. However I’m having problems with the code above. I’m catching the following database exception: ORA-01460: unimplemented or unreasonable conversion requested Not sure why, couldn’t find a solution anywhere on the net. I’m beginning to suspect it is a jdbc driver issue. But wanted to see if you came across this before. I even tried using the exact table you’ve created. Thank you for your help.

  26. |

    Michael, no I have not encountered such an error before. which statement is giving you the ORA-01460? Oracle DB version? ColdFusion version?

  27. |

    Hi Eddie, the statement giving me the error is the insert statement: insert into files (file_content) values (), we’re using Oracle 10g and ColdFusion 7. After a bit of testing I found that smaller text files work fine but larger ones > 150KB give the ORA-01460 error. Any ideas? Thanks again.

  28. |

    Michael, take a look at comment 19 above.

  29. |

    Hi Eddie, got it to work by downloading the latest jdbc driver from oracle: http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html Again thanks for your help!

  30. |

    hi, i have converted the MPP data into xml formart.But ,after conversion i need to store the XML value in to database.i dont know how to do it….can anyone guide me how to do it. thanks in advance…………..

  31. |

    Hi, how do I create a downloadable form with list of file to select and have a progress while download is in progress? Thanks, Bonn

  32. |

    I noticed that you are using <cfcontent type=”application/vnd.ms-excel” .

    Does this mean that you have to know the file type you stored in your BLOB in order to retrieve it properly?

  33. |

    @Mo: yes, you have to know the file type you stored in your BLOB in order to know how to read/display it after you retrieve it.

  34. |

    Nice one Eddie, saved me some time. Easy to follow too. Cheers.