I want to insert images in ms sql 2000 using image datatype
Can anybody help me please?

Mangala:)

u need images ....he he

Most people think that it is better to save the images in the file system, and to only store a pointer to the image in the database (i.e. the path to the image file). This is because images can get rather large, and this will bloat the size of the database, adversely affecting its performance.

It can also be problematic to manipulate the image data type in client code, since the image byte stream may have to be manipulated in "chunks"...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

If u know please tell me
I dont want to store the path but the image in database

Mangala

Thanks Jeff
But I am using this method of storing the path
So I would like to store actual image in database
& how yo use image datatype if u can please tell me.

Mangala:)

Perhaps you didn't understand me the first time.

You really don't want to do this.

Images stored as the image datatype make the database large and bloated. The image data itself is stored in a manner that makes it slow and inefficient. It is difficult to maintain images; if you need to update the image, the only way is via client code - storing the path instead means all you have to do is replace the image file with another and the new image is instantly available. There is no good way to stream an image into a web page, since the source of an HTTP image must be from a URL.

All in all, IMO it's just a bad idea to do this. So I guess I have a bit of a philosophical problem advising you how to do something that I have very strong feelings against doing. (Besides, since I think it's such a wrong way to do something, it's been a long time since I wrote any code to handle the situation and I'm not sure I remember how to do it :D).

The code is something like this. You can define a stored procedure parameter to be of image datatype and use it to INSERT or UPDATE an image column. Thus:


CREATE PROCEDURE updateimage
    @SomeIDvalue integer
    @Image image
AS
    UPDATE yourtable
    SET imagecolumn=@image
    WHERE keyIDcolumn=@SomeIDvalue;


If you use ADO in your client code, define the parameter as type adVarBinary with a length of 2147483647 (maximum integer value). You construct the image data as a variant and set it as the value of the parameter.

To read image data from the database, you cannot use the image datatype as an output parameter; instead you must either open a recordset and retrieve the image as a field in the recordset then convert it to a byte array, or use the ability for a command object to place its result into a stream object directly via the adExecuteStream execute option.

Good luck. And don't say you weren't warned.


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

What would you do if a desktop application and not a web application needs to work with images in a SQL Server database? Would you still put the images "outside"? if yes, how would you save and fetch the images?

Thanks.

defiant.

I would (and do) store the images "outside", i.e. in the file system. The database contains the path to the image. We display images in a variety of ways; each has its own method. For example, the VB6 PictureBox has a Picture property which can be set to the results of the LoadPicture function, which takes a filename as an argument. We get the filename from the database, construct the full path (since the pathname we store in the database is usually relative to some configuration parameter), and set the Picture property accordingly. It's more awkward to manipulate the image when it is stored in the database, since the controls we typically use 'want' to get the image source from a file.

The VB.NET PictureBox is a little more straightforward and easier to use with images stored in the database, since it uses an image object which is much more flexible.


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

i imagined that you do it this way. i generally agree with your posts, but in this matter i don't. the first time i'm going to have to work with images, i will put them in the db, and not outside. just when i think about the security considerations when storing the images on a share...

one note: i develop only client-server apps (and not web apps).

defiant

I tend to agree with Mr. Mason's approach - as that is the way I have done it before also. I collected many documents for the airline industry and stored them in a DB (not the pix). I used the file system to manage where the pictures were for issues such as Mr. Mason mentioned. There were many sources and destinations for the images so the file path was the best option. IIS security worked well enough for our needs as well as our custom security model we had in place for the web.

The reiterate - another main reason was the DB would get get huge. DB's are written to handle data well - not images.

_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200