Upload file to MySQL

[fobos]

Hey guys,
i have looked all over the web on how to upload a file to MySQL database and retrieve using coldfusion. I know i can do it in PHP, but im not using that anymore. Is there a script or some place that someone can point me so i can learn? I know i can move the file to a location and store the link, but was hoping to find a way to store it in MySQL?

Thanks for any help.

[arrgh]

You mean store the file content in the db, not just the file path (ie "c:\path\myImage.gif")? While you can do it, it can drastically bloat your database. But if you really want to do it, here is an example. It's for Oracle but the overall process is the same for any db.

http://awads.net/wp/2006/01/25/savingdownloading-files-tofrom-oracle-using-coldfusion/

Just upload the file as usual ie using <cffile action="upload" ...>. But instead of storing the file path, you read the *contents* of the file into a variable. Then insert the variable's binary data into the database instead of the path. When you're done, you can delete the uploaded file.

<cffile action="readBinary" 
     file="#cffile.serverDirectory#/#cffile.serverFile#" 
     variable="fileContents">

<cfquery name="addFile" ....>
    INSERT INTO Table ( FileName ) 
    VALUES (
     <cfqueryparam value="#fileContents#" cfsqltype="cf_sql_blob">
    )
</cfquery>

<!--- once done, delete the physic. file --->
<cffile action="delete" 
     file="#cffile.serverDirectory#/#cffile.serverFile#" >

Retrieving the file from the db is the same as any SELECT. Just retrieve the record, and use CFCONTENT, with the proper mime type to display it in the browser. If you need any help with that part, just let me know.

One thing to watch out for is your CF datasource settings. If you have files that bigger than 64K, CF will only return the 1st 64K bytes unless you enabled the "BLOB" setting in your datasource.
http://awads.net/wp/2006/02/21/lobs-gotcha-in-coldfusion/

[fobos]

Sweet arrgh,
Thanks for that one. Well i got it working just like in the example that was provided where i can upload and download.
My only question is, how do i get the Filename and File Extension? Reason is, because now i dont have to manually specify the name and extension or just restrict the upload to certain types of files.

[arrgh]

Grab it from the #CFFILE# scope. After you do a CFFILE upload, that scope will contain all kinds of information about the uploaded file like: cffile.serverFile, cffile.serverFileExt, etc..

See the reference docs for a full list
http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-738f.html

... or just cfdump the whole scope after your upload. So you can see examples of all of the values :-)

<cffile action="upload" ...>
<cfdump var="#CFFILE#">

[fobos]

lol, i was just reading that, but didnt know how to empliment that. ill give it a try. Thanks for the help. Ill go ahead and mark this solved.

[fobos]

Sweet the dump is what i needed

[arrgh]

Lol. Cfdump knows and reveals all. Better than a crystal ball. ;-)