Sql - Export Blob

Exporting Images from Database

[brodier]

I have an SQL 2000 database which has 30,000+ small images stored as blob files. I would like to create an export function (to run daily), so that i can save these blob images on a local drive. Is this possible? and if so could you please point me in the right direction.

Cheers,

Brodie

[mcibor]

I would add another row: url - where you will store link to the file

then I would run query:
SELECT id, name, extension FROM images WHERE url='';

next I would set the counter -
files = 0

and in a loop when counter is 0 I would create a new directory (it can be eg.
url = microtime() )

save the file under name:
images/url/id_name.extension

and store the link in db:
UPDATE images SET url='images/url/id_name.extension' WHERE id='id';

the same loop through eg. 100 files and reset the counter to create a new dir:
number_of_files = 100
counter = ++counter modulo number_of_files;

Under windows:

The folder structure of a document library is usually similar to the folder structure of a typical file store. For optimal performance, it is recommended that each folder contain fewer than 1,000 files. Performance and ease of navigation will degrade as the number of files in a folder increases. The depth of the folder structure might also be limited by the number of characters in the file and folder name. There can be no more than 260 total characters in the URL for a file in a document library. The maximum file or folder name length is 128 characters, including the extension.

[microsoft.com...]

I don't think there is such problem in linux.

Sorry for writing only algorithm, but you didn't specify what language you will be writing in.

Regards
Michal