Storing Uploaded Audio Files in Your Database

[ProWebService]

I have been using the cffile tag to upload files into a directory on the server
and then writing the details of that file into a database, but I would like to
now instead of uploading and writing the file to a directory, I would like to
write that file into a field of a database table and then call it out later to
listen to it or view it.

Does anyone have any experience with writing a file into a database along with
it's file details and then code to read it out and display the file details and
view or listen to the file?

I'm guessing that I would need a field in the database table that is a byte
array, data type "image" to hold the file and then the other fields would be
"type" "varchar" to hold the details of the file.

MySQL or SQL Server are my choices of databases.

Thank you!
Tom

[Ian Skinner]

No experience, but I know, theoretically, it can be done. You are
looking at what is generically called a BLOB [Binary Large OBject]
field. What the exact data type of such a field is will very from
database to database.

Then you will want to familiarize yourself with the ToBase64(),
ToBinary(), BinaryDecode(), BinaryEncode() and related functions as well
as the <cfcontent...> tag. These are CFML's methods to handle and
deliver alternate content such as binary files to and from clients.

[tclaremont]

Depending on the size of the audio files your DB is going to get pretty large (Not that it should stop you). Why are you averse to storing the pointer in the database and the file in a directory?

[ChennaiScreen]

You should have a blob type column (In oracle) to save the content ..

First upload the file to webserver

<cffile action="upload"
filefield="form.FileName"
destination="C:\temp\"
nameconflict="makeunique" >

Convert the same to binary

<cffile
action = "readbinary"
file = "C:\temp\#cffile.serverFile#"
variable="file_blob">

insert statement

insert into files (FILE_ID,file_content)
values (7,
<cfqueryparam
value="#file_blob#"
cfsqltype="cf_sql_blob">
)


delete the file from the server

<cffile
action="delete"
file="C:\temp\#cffile.serverFile#">

[ProWebService]

That looks pretty much straight forward. Thank you!
I do realize about the size issue but was just looking into this technique as another option for storing files.
Thanks again!
Tom