binary data import

[Panda On Smack]

I have a cold fusion site which uses an access database, i recently converted it to asp and SQL due to hosting. I just have 1 problem which is i currently store images for records in a folder on the server and the filename is a field in the DB.

Now its on SQL im storing the images as binary data in the actual database as well but i need to import the old records so i need to somehow import the images as well as i do it.

Any ideas on if this can be done?

Thanks

 

[fatbusinessman]

First question: what kind of SQL database are you using? MySQL, MSSQL or some other kind?

 

[Panda On Smack]

soz, im using MS

the proper version of SQL

 

[fatbusinessman]

I don't know you

*passes problem to Steve*

 

[SheepCow]

Open images, read binary data, SQL INSERT/UPDATE.

 

[Panda On Smack]

o....k

 

[Jonty]

Hi Panda

I think what SheepCow means is that in PHP/ASP, you have the ability to open files and read the contents. Normally this is done in relation to text files, but I guess there's no reason why it couldn't be done for images files too. I could tell you how to do it in PHP, but obviously that's not much use

Still, if the principles are the same in PHP, all you need to do is write a little script which opens a file you specify, reads the contents, and then inserts the contents into your MSSQL database.

If you want to automate the whole thing, make the script generate a list of all the files in your images directory, and then use these file names to loop through all the images. You would probably want some confirmation/gap in between each file or x number of files, as if you have a lot of images or if they're large in size, the script may stall part way through (unlikely, but you never know).

Kind Regards

Jonty ... goes back to being away

 

[Panda On Smack]

Aha i see, so I import my data into the database and then afterwards i need to create a script that loops through all the records and runs a query which inserts the binary data in an UPDATE query

Think it would be best to do a few at a time as i have quite a lot

Thanks chaps