Help with PHP & MSSQL

[simon-says]

There is a group of us and we have a problem. The problem is that we are storing files in a MSSQL database. We are storing images, word documents, excel documents, pretty much anything. Now the problem is when trying to retrieve the data, we can only get 4KB back (4,096 bytes). Normally if the file is not a text document we get a Resource ID #8 error. Would you guys happen to know whether there is a setting to change so that we can get all the file back? In the database we are storing the file name, size, mime type and extension. If there is anyone that can offer any solution to our problem please help out.

[AstroTeg]

Is the field type something besides a blob? If so, that may be a problem.

Also, if memory serves me correctly, MS SQL does funny things to blobs when you query on them. If this is the case, you may need to contact Microsoft about possible workarounds (don't quote me, but have you tried to just query the file itself out without any other fields in the select statement? If it works, then its a bug.).

Personally, this is why you never ever want to store files in a database. Keep the data for databases and files for file systems. You're killing the performance your queries and inflicting unnecessary pain trying to troubleshoot the problems. Instead, store the file name and optionally the path in the database and keep the files else where.

[bigswifty]

more info is needed.

are you storing the images, the excel files and everything else in the db? or you storing information that points to the physical file location on your server in the db.

at first glance, I would assume you are using the db in a way it was not designed for, using it to store files and not data that refers to the files.
while you can store images and whatnot in a mysql db, but it is not the best way, certainly no better then storing the file on the file system and storing a pointer to it in the db.

the other thing I assume is that your tables are setup wrong, massive amounts of data will be truncated unless you override the 255 char limit by defining a column as "text" or "longtext" or one of the other large data names. (sorry, im a mysql person 🙂 what you want is blobs, not text...)

if this is the case your data is unrecoverable since the db only stored the first 255 characters of whatever you entered into it.

like I said.. more info is needed

[simon-says]

Yes, we are storing the actual files in the database. We have tried everything under the sun really. Right now we are converting the files to hex and storing them in that way.

The reason we are doing this is for easy portability. We do realize the huge take on the performance this will take.

We just realized it is being truncated to 256 characters. We are using the image data-type in MSSQL.

Any more info you guys need I'll be more than happy to help out.

[lesleye]

i am able to retrieve images from a database without trouble. I just write the html code to retrieve them. for example, here is my code for one of my databases:

echo"<td><a href='detailphp.php?name=$row[name]'>$row[name]<br>$row[image]</a><img src='images/yellowspacer.png'></td>";
where detailphp is the website linked to the image. and $row[image] is the extracted image from the database.

I would assume you would want to hyperlink the excel files and the word documents.

Hope this helps

[AstroTeg]

Originally posted by simon-says

The reason we are doing this is for easy portability. We do realize the huge take on the performance this will take.

Portability to what? I doubt you'll be able to easily export the files and import them into another database engine.

I HIGHLY recommend you hit up MSDN and read all you can on blobs Microsoft style. They are very buggy under MS SQL v7 and buggy, but not as buggy under MS SQL 2000. You may be bumping up against an issue with the amount of data MS SQL will bring back on a per row basis.

The place I was at thought about blobs for a little bit and discovered how buggy it was. It was a ton easier to leave the file in the filesystem and point to the file itself. In fact, we had so many files, we had to get a network storage device to handle the files. If this was the database server, it would have been more difficult to do this.

[simon-says]

See we are making a system for someone else. These people prefer to use SQL Server rather than MySQL, and also prefer to run on Windows and not Linux. We are trying to make the job easier that if they ever have to change servers they could just copy the database over. We have no clue about the people that run the server, for all we know they could be freaking geniuses and know the answer to this. We don't know if they will just transfer the database over and not transfer the files if they were stored outside of the database, or not put the folder in the right place.

So far we still have yet to be able to get anywhere further than what we had.

We have searched up and down the net but there seems to be no solution to it, but there are more people than just me trying to figure this out. It seems the only answer is a question in the form of why would you want to do this.

Right now I'm not even sure which version of SQL Server we are running, but it is off a Windows Server 2003 machine. Nothing fancy, just a little server box for testing.

We have been able to find examples where people have done this sucessfully with ASP, but we assume this is because both products are MS.

If worse comes to worse I guess we have to store outside the database and that will be all there is to it.

[AstroTeg]

Think about this for a moment:

Files stored in a database: How are you going to transfer them to MySQL or another database engine later on? All blobs are not created equal between databases (nor is their support for them consistent or bug free).

Someone will have to write a script to export the data and import the data into the new database. What if they botch up the blob export? What if they botch up the blob import to the new system? Where will you find someone who will know how to code for both database engines? Are you absolutely sure you can just do a CSV file export and get all your data out without it getting mistranslated?

---

Now think about storing the files on the filesystem:

How will you transfer the files over to the new system? Before answering this, how many files are there? If its a boat load, then maybe by CD, tape, hard drive, you name it. Or if you have bandwidth, you could FTP the files directly from old system to the new system. Sounds pretty easy - if anything, it would just take a lot of time, but you'd know the content is all there.

But what if they don't store the files in exactly the right place? Well, somewhere you should specify the base path to where the files are currently stored. On a Windows box, it might be something like: C:/thewebfiles/gohere/ and all the files work off of that base path. On a *nix system, you'd have to change the path because they don't use C: so it might need to be something like: /thewebifiles/gohere/. Ideally, you want this stored in one place so when its time to move the files, you only need to go to one place to make the change.

Also by doing this, you add in the ability for these files to be hosted on a completely different machine within the internal network. Meaning you could design a setup where the web server talks to the outside world and also talks to a local, private network. On that network, you have a database server to handle the load, and a file server to handle all the files (it could even be one of those nifty 1U rack hard drive arrays which could hold 1 terabyte or more of data).

The flexibility you'd have could be endless depending on how you design your system.

Storing the files in the database is a bit dangerous in my opinion.

Something else to think about: What if someone blows away a row accidentally? What if someone blows away the entire database accidentally (been there, done that, not fun). If you're files are stored outside of the database, you might be able to do a few things: recover the database faster since you're not recovering a boat load of files stored in blobs, since the files still exist. You could change the web pages to access the files without using the database (this could be ugly, but if its going to take a few hours to restore the database, it might be a supper quick fix to keep customers happy).

Another thought: what if a file has a virus? If the file is in the database, will you still be able to scan it and fix it? If its stored in the filesystem, you could at least run the virus scanner on that machine on a regular basis and do house cleaning.

Again, if you're still serious about doing it in blobs in MS SQL, I HIGHLY recommend looking at MSDN (Microsoft Developers Network). There's a lot of technical notes regarding blobs and their useage (and their bugs). With MSDN, you may be able to figure out your blob problem.

[titansolaris]

I was told to never store actual files in databases, but just link to those files where they lie in some other folder, like on a server. But just dont store files in a database jesus, it kills the performance!!! overloading your resources!! dont do that! just store the links to the files.