File Upload - SQL 2008 FileStream

[WestSide]

Hi,

In SQL 2008, there is something called FILESTREAM.  It is a new feature of SQL Server 2008, which allows storage of and efficient access to BLOB data using a combination of SQL Server 2008 and the NTFS file system.

Has anyone used ColdFusion to upload a document into a database table that has filestream columns?

I want to know how I can upload a document from ColdFusion and get it into the database.

Any help appreciated

-ws

[Dan_Bracuk]

How would you do it with MS Sql Client Tools and a file you just happen to have avaialable?

[JR__Bob__Dobbs-qSBHQ2]

Dan,

My understanding of the FileStream feature of SQL 2008 is that it allows a VARBINARY (aka BLOB) field's contents to be stored on the file system.  This is intended to make storage of files within the database server more efficient.  One *should* be able to treat this field like any other VARBINARY field from a SQL statement perspective.

Someone please correct me if this information is incorrect or incomplete.  My experience with SQL 2008 is limited.

[JR__Bob__Dobbs-qSBHQ2]

Here are the basic steps for uploading a file and storing it in your database.

1. Create a page with a form that includes a file tag.  This form will submit to an 'action.cfm' page.  Note the name of the .cfm page is not important, 'action.cfm' is an example.

2. On your 'action.cfm' use CFFILE with action="upload" tag to save the file to your filesystem.

3. Use the CFFILE tag with action="readbinary" to read the contents of the uploaded file into a variable.   You may also want to use CFFILE to delete the uploaded file after it has been read into a variable.4. Use CFQUERY with CFQUERYPARAM to insert the file conents.
Please reply if you have more questions.