Moving images from blob to filesystem

is it possible?

[dinob]

Hi there

Just a quick question about the possibility to move images from the database to the file-system on the server....I have a MySql database which is just over 5 GB in size. People who designed it, stored things such as pdf files and images in the database, as blob, instead of file system (I know that there is a huge discussion on which is better and I'm not going in that direction)

Now, I was wondering if there is a way to move all the files that are in blob to the filesystem on the server? I mean, is there a script or something that could automatically do the process or does it have to be done manually? Any help would be appreciated...

p.s. I'm on dedicated server if it makes any difference....

[coopster]

Welcome to WebmasterWorld, dinob.

I'm not aware of an automated process to do so, I would likely script it. You could loop through your table and read each blob column into a string and open a file handle to your filesystem and write the file out.

[dinob]

Thanks coopster...I thought to do something similar but I'll admit that I don't have a huge knowledge in the area...can you please explain for me the sentence "open a file handle to your filesystem and write the file out"?

thanks

[coopster]

What server-side scripting language are you using? In perl, it may look something like this (untested, pseudo code follows):

my $pathToImages = '/path/to/my/images/'; 
# Loop through your query result set and get the $imageName and $imageBlob 
  my $imageName = $pathToImages . 'myImageName.jpg'; 
  open (FH1, ">$imageName"); 
  flock (FH1, 2); 
  print FH1 "$imageBlob"; 
  close (FH1); 
# get next record in result set and process

You loop through the results of your query and retrieve the filename (or make one up) and also retrieve the blob data. Open up a file handle to write to the filename in the directory path specified and write the blob data to the file. Close the file. Repeat (via your loop process) until you reach the end of your result set.

[dinob]

I'm using php...can you give me some hints on how to construct the code...?