storing image in DB table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

storing image in DB table

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-02-15 : 16:30:41

hi

can anybody please help me out in... how i will store image in database table???

T.I.A

Papillon

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-15 : 16:32:42

First, it isn't recommended. In fact, it's highly not recommended. You should store the image on disk and just the path and file name in the database.

Second, if you really want to do this, then you'll need a front-end application to do it as you can't do it via T-SQL, well that is receive an image and insert it purely through T-SQL. Your application will have to give th binary bits of the image to the insert statement.

Tara Kizer
aka tduggan

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-02-15 : 16:35:45

Hi Tara...
can u please give me e.g. for "You should store the image on disk and just the path and file name in the database".

T.I.A

Papillon

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-15 : 16:39:41

INSERT INTO YourTable(PKColumn, PathFileName)
VALUES(1, 'E:\images\FileNameGoesHere.jpg')

So your application would save the file to disk (but not on the database server, it should be on your own app/web server). Your application would store the path and file name in the database like above.

I'd actually not store the drive letter in the database though in case you ever want to move things around. I'd put that value in a config file. Then concatenate that value with the rest stored in the database to get the whole path and file name.

Tara Kizer
aka tduggan

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-02-15 : 16:41:07

just tell me which data type u used here for 2nd column?

thanks a lot tara!!!!!!!

papillon

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-15 : 16:44:29

Ummm, varchar or nvarchar of course. Make the size big enough to fit the largest path and file name, but not too big that you surpass the table size limit. I believe SQL Server stores paths in sysname data type, which is nvarchar(256).

Tara Kizer
aka tduggan

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-02-15 : 17:25:54

http://www.sqlteam.com/item.asp?ItemID=986
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37980
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45564

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-16 : 02:07:55

Also read this
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx

Madhivanan

Failing to plan is Planning to fail

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-02-16 : 02:30:28

make sure you check the links afrika posted.

-ec

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 05:58:26

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Image

Kristen