Storing Image Vs. URL Path in SQL Server 7.5

Storing Image Vs. URL Path in SQL Server 7.5

Post by Rafael Dano » Tue, 25 Jul 2000 04:00:00



Does anyone have any suggestions of a preferred method?
The db is expected to grow and store a couple hundred thousand of JEPG
images.

Thanks,
Rafael Danon

 
 
 

Storing Image Vs. URL Path in SQL Server 7.5

Post by Neil Pik » Tue, 25 Jul 2000 04:00:00


 Rafael,

 There is no 7.5

 > Does anyone have any suggestions of a preferred method?

Quote:> The db is expected to grow and store a couple hundred thousand of JEPG
> images.

Q.     How do I store/retrieve text and image data in SQL Server?

A. To store/retrieve this sort of data within TSQL scripts you have to use the
WRITETEXT and READTEXT commands rather than standard INSERT/SELECT statements.  
These are documented, with examples, in the books-online but are basically a
real pain to use.  See "HandlingTextImageUsingSPs" FAQ entry for lots more
information on these commands.

There are more manageable commands available from within the relevant
programming languages - e.g. RDO and ADO from VB/C can use GetChunk and
AppendChunk commands - but you still have to manage the image/text
chunks/blocks of data at a time.  About the only upside of storing this sort of
data within SQL Server is that it can be kept transactionally consistent with
the other data.  For sample code see Q194975 - "Sample Functions Demonstrating
GetChunk and AppendChunk".

For native ODBC access use the SQLPutData and SQLGetData commands.

If you just want to insert/retrieve an entire image/text then look at the
TEXTCOPY program (textcopy /? for parameters) in the <sql>\BINN directory.  It
is a command-line program along the lines of BCP.

There is a very useful (free) dll available from
http://www.chriscrawford.com/DBImage (courtesy of Chris Crawford).  The link
had disappeared at time of writing, but hopefully it will return.  It has three
functions:

 GetImage Used for getting an image from the database
 StoreImage Used to send an image to the database
 GetWebImage Used to send a DB image to a browser

If transactional consistency doesn't matter, or can be achieved
programmatically, then it is easier to store the data outside the database as
an ordinary file.  Within the database just hold a UNC pointer to where the
file is held.  This usually makes it much easier to display/edit the data as
the name can simply be passed to whatever tool is doing the manipulation.

===

v1.05 2000.06.13
Applies to SQL Server versions  : All
FAQ Categories                  : Database Administration, Application Design
and Programming
Related FAQ articles            : HandlingTextImageUsingSPs
Related Microsoft Kb articles   : see Q194975 "HOWTO: Sample Functions
Demonstrating GetChunk and AppendChunk" for other references
Other related information       : n/a
Authors                         : Neil Pike

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps (faqrtf.zip - L7
- SQL Public)
 or    http://www.ntfaq.com/Section.cfm?sectionID=34
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq

 
 
 

Storing Image Vs. URL Path in SQL Server 7.5

Post by Joe at Breese dot Co » Tue, 25 Jul 2000 04:00:00


store the URL / UNC

In my limited experience, it seems easier to just reference the file than
store it.  I wouldn't know the reasons to store an image into a database,
but i do know some reasons why you wouldn't: "unnecessary"growth of
database, simplification of image administration (resize, crop, etc.)
simplification of retrieval.

I hope this helps out.

JB


Quote:

> Does anyone have any suggestions of a preferred method?
> The db is expected to grow and store a couple hundred thousand of JEPG
> images.

> Thanks,
> Rafael Danon

 
 
 

Storing Image Vs. URL Path in SQL Server 7.5

Post by Nils Nilse » Tue, 25 Jul 2000 04:00:00


Quote:> In my limited experience, it seems easier to just reference the file than
> store it.  I wouldn't know the reasons to store an image into a database,
> but i do know some reasons why you wouldn't: "unnecessary"growth of
> database, simplification of image administration (resize, crop, etc.)
> simplification of retrieval.

I couldn't agree more
 
 
 

1. Suggestions for storing images in SQL Server vs storing references to physical paths

I'm looking at developing an application which, among other things, will
store information on images associated with individual user's profiles --
such as glamour shots in a photographer's portfolio.  At this time, I'm not
concerned about actual implementation -- it's more of design issue, with
respect to scalability, performance and ease-of-use.

In the end, I'm going to be pulling some sort of data from a database --
binary or text -- and will be displaying it via ASP on a web page.  My two
options are: store the images physically in the web server as binary data,
and thus physically retrieve the file when required to do so; or store a
path (relative or absolute) in the database, keep the image files in a
directory somewhere on the web server, and then display an IMG tag on the
ASP page, which merely points to where the image is.

Does anybody have suggestions as to which architecture might be better, and
why?  I've faced this problem before, although it was never an issue, since
the sites didn't anticipate that much traffic and didn't have that much data
to store.  I've been using the latter option (store images on server; store
path references in DB) up until now, but because this site has the potential
to grow quickly (each model can have 0-6 images; administrators can create
an unlimited number of models; and if they sell this as an ASP-type service,
there will be 0-500 sites with at least one administrator per site).  I'll
be using SQL Server 7.0 or 2000, and ASP 2.0 / 3.0.......... I'm
disregarding hardware for now, and focusing primarily on software design as
the biggest potential source of problems.

Any ideas, or links to discussions concerning this type of issue, would be
greatly appreciated.  Thanks in advance!

Sincerely,

-=Tek Boy=-

2. modify msde or sql table progamatically?

3. Storing A picture in SQL SERVER 7.5

4. OCI and ISAPI

5. Storing Images OR Paths in SQL server database

6. are there ways to edit views by hand?

7. store image, or image path in DB?

8. Changing Text color with a Script?

9. Storing image as BLOB or only storing URL to fileshare

10. SQL Server 7.5 Specs?

11. Sql Server 7.5 Beta

12. Logging facilities in SQL Server 7.5

13. SQL SERVER 7.5