Storing and retreiving .doc files from an MSSQL database

kdbenner

Okay, I have no clue what to do here, I have read so much conflicting information.

I need to know how to store files in a MSSQL database, .zip, .doc, .pfd, .xsl, .vsd (visio) and they all need to store and pull from the database cleanly with no corruption.

Can anyone point me in the right direction

bastien

Make it easy on yourself and store the documents in separate folders on the filesystem and keep the path in the db. DBs don't actually store files in them, they place the item in a virtual folder and store a pointer in the db.

To avoid corruption and make the programming easier store the path/name as text. You'll find it much easier to handle

hth

kdbenner

I NEED to store all files IN the database the filesystem approach is just not an option, I just need to know how to do this with PHP, I have tried a number of things and keep getting errors, if anyone has an example of how to actually make this work, I would apreciate it.

I am on winXP with IIS, and PHP, and a MSSQL database on a seperate WINNT server.

bastien

AstroTeg

Originally posted by kdbenner
I NEED to store all files IN the database the filesystem approach is just not an option

Is there a particular reason for doing this? You're just bogging down your database.

kdbenner

Major security concerns about having any open files accessable on the file system, even with URL rewriting, any FTP is closed, http access only to specific files, no dynamic content, and the necessity to be able to upload and download specific files related to different records.

AstroTeg

Keep everything you have in place, but instead of putting the file in the database, you store the files in a non-web accessible directory (which is really what MySQL will be doing anyways). This will buy you some flexibility for when the database goes down, database needs to be moved, or you need to put files on another server or location. Plus you're not bogging your database server with all these files that you're not really doing much with other then storing...

kdbenner

I've gotten some of it working but I still get the file corruption I was afraid of. I can get teh data into the DB, but not back out cleanly.

Code below:

to get the contents of the file into the MS SQL database (not MySQL)

function fileopen($file){

$handle = fopen("$file", "rb"); //OPEN THE FILE
$ctype = mime_content_type('$file');
$contents = file_get_contents($handle, filesize($file)); //READ IN THE FILE
fclose($handle);			//CLOSE THE FILE
//echo $contents; //DEBUG TO SEE IF WE HAVE THE FILE
//return($contents);
$data = unpack("H*hex", $contents);
$query = "SELECT * FROM files";

$result = mssql_query( $query );

$count = mssql_num_rows( $result);
$id = $count;
$filename = ltrim(strrchr($file,'\\'),'\\');
$submit = "INSERT into files (file_id,name,data,content_type)values" .
"($id,\"$filename\", 0x".$data['hex'].",\"$ctype\")";
//echo $submit;
mssql_query($submit);
}

And to attempt to retreive it and write it temporarily to the FS for download for testing purposes only, users will not be able to download on te live site:

function fileout($sow_id){
$result = mssql_query("select name,data,content_type from files where file_id = '$sow_id'");
$row = mssql_fetch_assoc($result);
$ctype = $row['content_type'];
echo $ctype;
//header("Content-type: $ctype;");
$name = $row['name'];
echo $name; //debug
$data = $row['data'];
if (!file_exists($name)) {
$handle = fopen($name, 'x');
fwrite($handle,$data);
fclose($handle);
}else{
echo "the file $name already exists.";
}