Database-Oriented Document Management
Nearly every time the question of storing documents or images in a database comes up, the answer is almost always "don't do it", and for very good reasons. There are those times, however, when it's very much the appropriate thing to do. In my case at my day job, we have multiple production servers that are maintained by another (very protective) team, and the method of load balancing in place is NOT conducive to keeping server content in sync. Therefore, when we want to post a new document or image on the web, it may very well be hours before said file is actually replicated to all production servers. In order to make an image or document immediately available, we decided that storing them within the database would resolve our challenge. Since the final solution did involve some research and a few fiery hoops, I figured I'd share it with the rest of the Ether in case it might come in handy for someone else. Following is the general process and code snippets involved with making this happen.
Basic Process:
1. All of the following code and snapshots are in regard to CFMX 6.1 and higher. The process described below is similar for CF 5, but there are some additional caveats and steps involved (Email me for particulars if you're still using CF5).
2. You MUST allow the retrieval of BLOBs within your ColdFusion datasource where your document table lives, as in the following snapshot:
3. Below is the script needed to create the sample table in MSSQL. The table used to store the documents should have a field of type IMAGE (if using MSSQL) in order to store binary data directly. If your database doesn't support a field type for binary data, then you'll need to create a field of type TEXT or its equivalent. Any binary data needing to be stored in a TEXT field will have to be BinaryEncoded/BinaryDecoded using Base64 as the second parameter in order to store and display it in this manner.
SQL Script:
On with the actual code then! Here are the details/snippets:
File selection
File selection is done via a form field of type "File", as in
Important: In the FORM tag, it is required that you set the ENCTYPE equal to "multipart/form-data", as in
Basic Process:
- File is selected via a form within a document admin utility;
- Selected file is read into binary and stored in a "Documents" table
- Links to stored documents/images contain the UUID of the target document. Table is queried for that UUID, binary is retrieved from database.
- File's binary is provided to a CFCONTENT tag, along with the mime type, and result is delivered to requestor.
1. All of the following code and snapshots are in regard to CFMX 6.1 and higher. The process described below is similar for CF 5, but there are some additional caveats and steps involved (Email me for particulars if you're still using CF5).
2. You MUST allow the retrieval of BLOBs within your ColdFusion datasource where your document table lives, as in the following snapshot:
3. Below is the script needed to create the sample table in MSSQL. The table used to store the documents should have a field of type IMAGE (if using MSSQL) in order to store binary data directly. If your database doesn't support a field type for binary data, then you'll need to create a field of type TEXT or its equivalent. Any binary data needing to be stored in a TEXT field will have to be BinaryEncoded/BinaryDecoded using Base64 as the second parameter in order to store and display it in this manner.
SQL Script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[docs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[docs]
GO
CREATE TABLE [dbo].[docs] (
[id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[filename] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[extension] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[filesize] [bigint] NULL ,
[uploadedDate] [datetime] NULL ,
[mimetype] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mybinaryblob] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[docs] WITH NOCHECK ADD
CONSTRAINT [DF_docs_id] DEFAULT (newid()) FOR [id]
GO
drop table [dbo].[docs]
GO
CREATE TABLE [dbo].[docs] (
[id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[filename] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[extension] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[filesize] [bigint] NULL ,
[uploadedDate] [datetime] NULL ,
[mimetype] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mybinaryblob] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[docs] WITH NOCHECK ADD
CONSTRAINT [DF_docs_id] DEFAULT (newid()) FOR [id]
GO
On with the actual code then! Here are the details/snippets:
File selection
File selection is done via a form field of type "File", as in
<input type="file" name="myfile" size="50" />
<!--- --->
<!--- --->
<!--- --->
<!--- --->
Important: In the FORM tag, it is required that you set the ENCTYPE equal to "multipart/form-data", as in
<form action="<CFOUTPUT>#cgi.script_name#</CFOUTPUT>" method="post" enctype= "multipart/form-data">
<!--- --->
<!--- --->
<!--- --->
<!--- --->
Form Action code
Step 1 when processing a file upload is to UPLOAD it via CFFILE. This takes the file and places it on the server in a temporary directory designated by the webserver itself. This is also needful so that we can grab the file's metadata (file size, mime type, etc.). The code to upload the submitted file looks like this:
"form.myfile" is the NAME of the form field that was of type "File"; it should be given to CFFILE without pound signs. If we were to place form.myfile in pound signs (#form.myfile#), the UPLOAD process would be unable to locate the target file, so make sure you don't do that.
Once the file has been uploaded, we need to capture its metadata, which exists immediately after the CFFILE call in a structure called none other than CFFILE. Fancy that.
The recommendation is to capture a good portion of that information and store it along with the file's binary. Here we'll capture it to a structure for use later with the insert query, like so...
Step 2 is to read in the binary of the uploaded file, like this:
Notice in this case we aren't just providing cffile with the name of the form variable, we're giving it the actual path to the uploaded file (wrapping in pound signs).
At this point, we're ready to insert our file into the database.
That's it for storage!
RETRIEVAL and DISPLAY
Retrieval is pretty straightforward, too. We're going to retrieve the particular record we're interested in from the database
and then feed the retrieved binary and mimetype to a cfcontent tag
A word on stored images
Images are treated a little differently primarily due to the fact that in most instances you don't just want the page's entire content to consist of one picture, but rather have it retrieved and displayed inline within an <IMG > tag. The only variation in this case is that you have to create an Image Display template that does exactly what the example above states, and use that template as your IMG source, like so:
(Ben has a more detailed post on the topic of retrieving and displaying images from a database, if you're interested)
That's pretty much it. I've boiled our document management system down as far as I am able, but you can grab this code HERE if you'd like to use it as a starter for a system of your own.
Here is the entire self-posting Form:
Step 1 when processing a file upload is to UPLOAD it via CFFILE. This takes the file and places it on the server in a temporary directory designated by the webserver itself. This is also needful so that we can grab the file's metadata (file size, mime type, etc.). The code to upload the submitted file looks like this:
<cffile action="UPLOAD" filefield="form.myfile" destination="#getDirectoryFromPath(getCurrentTemplatePath())#" nameconflict="OVERWRITE">
<!--- --->
<!--- --->
<!--- --->
<!--- --->
"form.myfile" is the NAME of the form field that was of type "File"; it should be given to CFFILE without pound signs. If we were to place form.myfile in pound signs (#form.myfile#), the UPLOAD process would be unable to locate the target file, so make sure you don't do that.
Once the file has been uploaded, we need to capture its metadata, which exists immediately after the CFFILE call in a structure called none other than CFFILE. Fancy that.
 |
| CFDUMP of File Metadata |
The recommendation is to capture a good portion of that information and store it along with the file's binary. Here we'll capture it to a structure for use later with the insert query, like so...
<cfscript>
stats = structnew();
stats.ext = CFFILE.ClientFileExt;
stats.name = CFFILE.ClientFileName;
stats.subtype = CFFILE.ContentSubType;
stats.type = CFFILE.ContentType;
stats.size = CFFILE.FileSize;
</cfscript>
stats = structnew();
stats.ext = CFFILE.ClientFileExt;
stats.name = CFFILE.ClientFileName;
stats.subtype = CFFILE.ContentSubType;
stats.type = CFFILE.ContentType;
stats.size = CFFILE.FileSize;
</cfscript>
Step 2 is to read in the binary of the uploaded file, like this:
<cffile action="readbinary" file="#form.myfile#" variable="vBin">
<!--- --->
<!--- --->
<!--- --->
<!--- --->
Notice in this case we aren't just providing cffile with the name of the form variable, we're giving it the actual path to the uploaded file (wrapping in pound signs).
At this point, we're ready to insert our file into the database.
<cfquery name="insertfile" datasource="#dsn#">
insert into docs (filename,extension,filesize,uploadedDate,mimetype,mybinaryblob) VALUES (
<cfqueryparam value="#stats.name#" cfsqltype="cf_sql_longvarchar" />,
<cfqueryparam value="#stats.ext#" cfsqltype="cf_sql_varchar" />,
<cfqueryparam value="#stats.size#" cfsqltype="cf_sql_bigint" />,
<cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp" />,
<cfqueryparam value="#stats.type#/#stats.subtype#" cfsqltype="cf_sql_varchar" />,
<cfqueryparam value="#vBin#" cfsqltype="cf_sql_blob" />
)
</cfquery>
insert into docs (filename,extension,filesize,uploadedDate,mimetype,mybinaryblob) VALUES (
<cfqueryparam value="#stats.name#" cfsqltype="cf_sql_longvarchar" />,
<cfqueryparam value="#stats.ext#" cfsqltype="cf_sql_varchar" />,
<cfqueryparam value="#stats.size#" cfsqltype="cf_sql_bigint" />,
<cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp" />,
<cfqueryparam value="#stats.type#/#stats.subtype#" cfsqltype="cf_sql_varchar" />,
<cfqueryparam value="#vBin#" cfsqltype="cf_sql_blob" />
)
</cfquery>
That's it for storage!
RETRIEVAL and DISPLAY
Retrieval is pretty straightforward, too. We're going to retrieve the particular record we're interested in from the database
<cfquery name="getFile" datasource="#dsn#">
select id,filename,extension,mimetype,mybinaryblob from docs where id = <cfqueryparam value="#form.getfile#" cfsqltype="cf_sql_char" maxlength="36">
</cfquery>
select id,filename,extension,mimetype,mybinaryblob from docs where id = <cfqueryparam value="#form.getfile#" cfsqltype="cf_sql_char" maxlength="36">
</cfquery>
and then feed the retrieved binary and mimetype to a cfcontent tag
<cfcontent type="#getFile.mimetype#" variable="#getfile.mybinaryblob#" reset="No" >
<!--- --->
<!--- --->
<!--- --->
<!--- --->
A word on stored images
Images are treated a little differently primarily due to the fact that in most instances you don't just want the page's entire content to consist of one picture, but rather have it retrieved and displayed inline within an <IMG > tag. The only variation in this case is that you have to create an Image Display template that does exactly what the example above states, and use that template as your IMG source, like so:
<IMG SRC="myImageDisplayTemplate.cfm?imageID=11111">
<!--- --->
<!--- --->
<!--- --->
<!--- --->
(Ben has a more detailed post on the topic of retrieving and displaying images from a database, if you're interested)
That's pretty much it. I've boiled our document management system down as far as I am able, but you can grab this code HERE if you'd like to use it as a starter for a system of your own.
Here is the entire self-posting Form:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<cfparam name="mode" default="" /><!--- parameter indicating whether we're asking for a file, storing a file, or retrieving a file --->
<cfparam name="dsn" default="test"><!--- datasource name to be used in this template --->
<!--- ********************************** UPLOAD A FILE ************************************************** --->
<cfif mode is "upload">
<!--- process new file... --->
<!--- first, upload it to the server --->
<cffile action="UPLOAD" filefield="form.myfile" destination="#getDirectoryFromPath(getCurrentTemplatePath())#" nameconflict="OVERWRITE">
<!--- Now, before we exectute another CFFILE call, grab the uploaded file's metadata for later use --->
<cfscript>
stats = structnew();
stats.ext = CFFILE.ClientFileExt;
stats.name = CFFILE.ClientFileName;
stats.subtype = CFFILE.ContentSubType;
stats.type = CFFILE.ContentType;
stats.size = CFFILE.FileSize;
</cfscript>
<!--- grab the binary version of the uploaded file --->
<cffile action="readbinary" file="#form.myfile#" variable="vBin">
<!--- insert it into the database --->
<cfquery name="insertfile" datasource="#dsn#">
insert into docs (filename,extension,filesize,uploadedDate,mimetype,mybinaryblob) VALUES (
<cfqueryparam value="#stats.name#" cfsqltype="cf_sql_longvarchar" />,
<cfqueryparam value="#stats.ext#" cfsqltype="cf_sql_varchar" />,
<cfqueryparam value="#stats.size#" cfsqltype="cf_sql_bigint" />,
<cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp" />,
<cfqueryparam value="#stats.type#/#stats.subtype#" cfsqltype="cf_sql_varchar" />,
<cfqueryparam value="#vBin#" cfsqltype="cf_sql_blob" />
)
</cfquery>
<cfoutput><a href="#cgi.script_name#"?mode="">Do It Again</a></cfoutput>
<!--- dump this file's metadata --->
<cfdump var="#stats#">
<!--- *********************************** Retrieve a Stored File *************************************** --->
<cfelseif mode is "retrieve"><!--- we're in 'retrieve a file' mode...' --->
<cfquery name="getFile" datasource="#dsn#">
select id,filename,extension,mimetype,mybinaryblob from docs where id = <cfqueryparam value="#form.getfile#" cfsqltype="cf_sql_char" maxlength="36">
</cfquery>
<!--- this is all there is to it to display a file stored as binary --->
<cfcontent type="#getFile.mimetype#" variable="#getfile.mybinaryblob#" reset="No" >
<!--- ************************************ Let the User Select a File for Upload or Retrieval ********** --->
<cfelse><!--- show the user interface so they can upload a new file --->
<cfquery name="getFiles" datasource="#dsn#">
select id,filename,extension from docs
</cfquery>
<form action="<CFOUTPUT>#cgi.script_name#</CFOUTPUT>" method="post" enctype= "multipart/form-data">
<input type="file" name="myfile" size="50" />
<input type="hidden" name="mode" value="upload" />
<input type="submit" value="upload" />
</form>
<hr>
<form action="<CFOUTPUT>#cgi.script_name#</CFOUTPUT>" method="post">
<select name="getfile">
<cfoutput query="getFiles">
<option value="#id#">#filename#.#extension#</option>
</cfoutput>
</select>
<input type="hidden" name="mode" value="retrieve">
<input type="submit" value="Retrieve File">
</form>
</cfif>
</body>
</html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<cfparam name="mode" default="" /><!--- parameter indicating whether we're asking for a file, storing a file, or retrieving a file --->
<cfparam name="dsn" default="test"><!--- datasource name to be used in this template --->
<!--- ********************************** UPLOAD A FILE ************************************************** --->
<cfif mode is "upload">
<!--- process new file... --->
<!--- first, upload it to the server --->
<cffile action="UPLOAD" filefield="form.myfile" destination="#getDirectoryFromPath(getCurrentTemplatePath())#" nameconflict="OVERWRITE">
<!--- Now, before we exectute another CFFILE call, grab the uploaded file's metadata for later use --->
<cfscript>
stats = structnew();
stats.ext = CFFILE.ClientFileExt;
stats.name = CFFILE.ClientFileName;
stats.subtype = CFFILE.ContentSubType;
stats.type = CFFILE.ContentType;
stats.size = CFFILE.FileSize;
</cfscript>
<!--- grab the binary version of the uploaded file --->
<cffile action="readbinary" file="#form.myfile#" variable="vBin">
<!--- insert it into the database --->
<cfquery name="insertfile" datasource="#dsn#">
insert into docs (filename,extension,filesize,uploadedDate,mimetype,mybinaryblob) VALUES (
<cfqueryparam value="#stats.name#" cfsqltype="cf_sql_longvarchar" />,
<cfqueryparam value="#stats.ext#" cfsqltype="cf_sql_varchar" />,
<cfqueryparam value="#stats.size#" cfsqltype="cf_sql_bigint" />,
<cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp" />,
<cfqueryparam value="#stats.type#/#stats.subtype#" cfsqltype="cf_sql_varchar" />,
<cfqueryparam value="#vBin#" cfsqltype="cf_sql_blob" />
)
</cfquery>
<cfoutput><a href="#cgi.script_name#"?mode="">Do It Again</a></cfoutput>
<!--- dump this file's metadata --->
<cfdump var="#stats#">
<!--- *********************************** Retrieve a Stored File *************************************** --->
<cfelseif mode is "retrieve"><!--- we're in 'retrieve a file' mode...' --->
<cfquery name="getFile" datasource="#dsn#">
select id,filename,extension,mimetype,mybinaryblob from docs where id = <cfqueryparam value="#form.getfile#" cfsqltype="cf_sql_char" maxlength="36">
</cfquery>
<!--- this is all there is to it to display a file stored as binary --->
<cfcontent type="#getFile.mimetype#" variable="#getfile.mybinaryblob#" reset="No" >
<!--- ************************************ Let the User Select a File for Upload or Retrieval ********** --->
<cfelse><!--- show the user interface so they can upload a new file --->
<cfquery name="getFiles" datasource="#dsn#">
select id,filename,extension from docs
</cfquery>
<form action="<CFOUTPUT>#cgi.script_name#</CFOUTPUT>" method="post" enctype= "multipart/form-data">
<input type="file" name="myfile" size="50" />
<input type="hidden" name="mode" value="upload" />
<input type="submit" value="upload" />
</form>
<hr>
<form action="<CFOUTPUT>#cgi.script_name#</CFOUTPUT>" method="post">
<select name="getfile">
<cfoutput query="getFiles">
<option value="#id#">#filename#.#extension#</option>
</cfoutput>
</select>
<input type="hidden" name="mode" value="retrieve">
<input type="submit" value="Retrieve File">
</form>
</cfif>
</body>
</html>
Subscription Options
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
Re: Database-Oriented Document Management
Wow Doug! This is a quality post.
I'm glad I found you!
There was a bit of a formatting issue that you need to be aware of:
When I was browsing this page before logging in, two of the code blocks were minimized to where they only displayed the slider. I'm using IE6.
And did you know your family photo album is displaying broken images?
Also, it's kind of depressing to see a pod that says "ColdFusion Jobs" and it's empty.
Oh, and all the really cool bloggers use blogCFC.
I'm glad I found you!
There was a bit of a formatting issue that you need to be aware of:
When I was browsing this page before logging in, two of the code blocks were minimized to where they only displayed the slider. I'm using IE6.
And did you know your family photo album is displaying broken images?
Also, it's kind of depressing to see a pod that says "ColdFusion Jobs" and it's empty.
Oh, and all the really cool bloggers use blogCFC.
Posted by psenn on September 8, 2006 at 8:41 AM
Re: Database-Oriented Document Management
Well that's weird. Now the pictures are working. Hmmmm.
Posted by psenn on September 8, 2006 at 8:44 AM
Re: Database-Oriented Document Management
Thanks for the input, psenn, I really appreciate it. As far as blogCFC goes, yeah, my coworkers tell me EVERYDAY that I shoulda went with CFC instead of CFM. But it was late one night when I finally decided to start blogging, and when I attempted to get blogCFC working, I couldn't. Rather than wrestle with it, i downloaded and tried teh next one i came across which was blogCFM and it worked like a champ first try. I've been too lazy to convert over thus far, but i may at some point.
As far as the IE incompatibilities...sorry to hear that. I try to "accommodate" Bill's browser by adding empty comment lines to my single line code windows, but I had missed a few. May I recommend that you be a rebel and convert over to Firefox? It's the bomb, and according to my own web stats, somewhere around 65% of visitors to my site are Firefoxers already.
The jobs window...darn it, i thought i had the javascript tuned enough to check for and inform users that they needed to have Flash player version 9 or greater installed in order to see the widget. Worked on my machine :). Anywho, if you upgrade your flash player (http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash&promoid=BIOW) you'll be able to see it...it's written in Flex 2.
As far as the IE incompatibilities...sorry to hear that. I try to "accommodate" Bill's browser by adding empty comment lines to my single line code windows, but I had missed a few. May I recommend that you be a rebel and convert over to Firefox? It's the bomb, and according to my own web stats, somewhere around 65% of visitors to my site are Firefoxers already.
The jobs window...darn it, i thought i had the javascript tuned enough to check for and inform users that they needed to have Flash player version 9 or greater installed in order to see the widget. Worked on my machine :). Anywho, if you upgrade your flash player (http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash&promoid=BIOW) you'll be able to see it...it's written in Flex 2.
Posted by dougboude on September 8, 2006 at 10:23 AM
Re: Database-Oriented Document Management
@ psenn - I totally agree, Doug get with it... Get blogCFC ;)
Posted by Joe Gautreau on September 8, 2006 at 10:43 AM
Re: Database-Oriented Document Management
I have a similiar problem as listed above. I am dealing with data that is stored in columns defined as text in SQL server. When I pull the data back from the database I format it into either a text doc or a word doc. The new doc is created and stored on the server. Once the docs are created I provide a link to them. If I click the link the docs are displayed with no problem. My problem is my custome error handler notifies me that the following error has occured.
Error Message: The cause of this output exception was that: java.io.FileNotFoundException: c:\.... (Access is denied).
Any assistance resolving this issue is greatly appreciated.
Error Message: The cause of this output exception was that: java.io.FileNotFoundException: c:\.... (Access is denied).
Any assistance resolving this issue is greatly appreciated.
Posted by darrrius on February 28, 2007 at 1:15 PM
Re: Database-Oriented Document Management
On personal opinion, I find this very helpful.
Guys, I have also posted some more relevant info further on this, not sure if you find it useful: http://www.bidmaxhost.com/forum/
Guys, I have also posted some more relevant info further on this, not sure if you find it useful: http://www.bidmaxhost.com/forum/
Posted by ocnsss on March 31, 2007 at 2:24 AM
Re: Database-Oriented Document Management
I have incorporated this code into an appliation and it works great! But, my client wants to be able to upload multiple images at one time.
Can this be done with this code. I have tried but no success.
Can this be done with this code. I have tried but no success.
Posted by Jim Dutcher on April 18, 2007 at 11:32 AM
Re: Database-Oriented Document Management
Ah, outstanding question! I am using a custom flash object for this very thing, which I can highly recommend. Check it out at http://www.cftagstore.com/tags/flashmultipleupload.cfm
Posted by dougboude on April 18, 2007 at 12:00 PM
Re: Database-Oriented Document Management
I love this code, however, I have noticed that some computers cannot "retrieve" the documents. It just sits in IE going nowhere when the user clicks the "retrieve" button. Has anyone run into this and found out what is causing the problem?
Posted by Laurie on July 31, 2007 at 2:23 PM
Re: Database-Oriented Document Management
Hi,
I am using Coldfusion 5. I have to upload the files and store them in database using CF5. most of article i read are of CFMX. Could you please guide me HOw to handle this.
Thanks,
Swamy
I am using Coldfusion 5. I have to upload the files and store them in database using CF5. most of article i read are of CFMX. Could you please guide me HOw to handle this.
Thanks,
Swamy
Posted by swamy on October 3, 2007 at 4:01 PM