Images and SQL Server

By Bill Graziano on 6 November 2000 | Tags: Application Design

One of our most asked questions is "How do you store images (BMP, JPG, GIF, etc.) in SQL Server?" In this article we'll discuss the options, the pitfalls and try to point you in the right direction. I don't have all the answers yet but you should be able to get started looking in the proper places.

Every time someone asks how to store images in SQL Server I have the same response: YOU DON'T. SQL Server is not the best tool to store images. In fact it's not even a good tool to store images. Especially if you want to display them in a browser. You can store images in SQL Server but I'm going to try and convince you not too. If you decide you still want to, I'll try to help you get started. In this article we'll cover pulling images out of SQL Server. In a future article I'll cover storing the images in SQL Server. I'll also give you enough links that you should be able to write your own routines to store the images if I don't get to it soon enough.

Linking to Images

The best way for SQL Server to deal with images is for SQL Server to store a pointer to an image. This can be a file name, directory/file combination or URL. The actual image can be stored on the file system or on the web server. It's very easy to write ASP code to build an IMG tag. The tag is customized with the file name pulled from the database. I'll walk through an example of this using ASP. For example, I have code to select a banner image and display it. My table looks something like this:

CREATE TABLE [BANNER_Ads] (
	[AdId] [int] IDENTITY (1, 1) NOT NULL ,
	[AdName] [char] (100) NOT NULL ,
	[AdType] [char] (10) NOT NULL ,
	[ImageURL] [varchar] (100) NULL ,
	[Height] [smallint] NULL ,
	[Width] [smallint] NULL ,
	[ALTText] [varchar] (100) NULL ,
	[HTML] [varchar] (2000) NULL ,
	[LinkToURL] [varchar] (100) NULL ,
) ON [PRIMARY]
GO

The SELECT statement is also pretty basic:

SELECT *
FROM BANNER_Ads
WHERE AdId = 17

And here is the ASP script to generate the IMG tag.

Response.Write "<a href=" & trim(bobjRS("LinkToURL")) & ">"
Response.Write "<IMG SRC=" & trim(bobjRS("ImageURL")) & " BORDER=0 "
Response.Write " WIDTH=" & trim(bobjRS("Width"))
Response.Write " HEIGHT=" & trim(bobjRS("Height")) 
Response.Write " ALT=" & Chr(34) & trim(bobjRS("ALTText")) & Chr(34)
Response.Write "></a>"

I actually wasn't very smart when I wrote this. My image URL is a fully qualified URL with the domain name, path and file. The most flexable approach would be to split those fields out. That way if code ever moves to a new domain it's easy to update. That's a minor problem though. Visual Basic would handle this just a little differently. After creating an image/picture object on the form you code set it's source to the location and filename of your image. I think Visual Basic requires path names as opposed to URL's for it's image locations. My VB days are far in the past. If someone has a few lines of sample code I'd be happy to post it here.

SELECTing Images

Now we'll retrieve images from SQL Server using ASP. In case you've forgotten (or I didn't mention it enough above) this is really a pain. You are going to need two separate ASP Pages. This code will pulled from a Microsoft Knowledge Base article. The first I called Picture.asp and it looks like this:

   <HTML>
   <HEAD><TITLE>Display Image</TITLE></HEAD>
   <BODY>
   This page will display the image New Moon Books from a SQL Server 6.5
   image field.<BR>
   <IMG SRC="PicShowImg.ASP">
   </BODY>
   </HTML>

This calls the ASP page PicShowImage.asp which looks like this:

   <%@ LANGUAGE="VBSCRIPT" %>
   <%
   ' Clear out the existing HTTP header information
   Response.Expires = 0
   Response.Buffer = TRUE
   Response.Clear

   ' Change the HTTP header to reflect that an image is being passed.
   Response.ContentType = "image/gif"

   Set cn = Server.CreateObject("ADODB.Connection")
   ' The following open line assumes you have set up a System DataSource
   ' by the name of myDSN.
   cn.Open "Provider=SQLOLEDB; Data Source=server9; Initial Catalog=pubs; User ID=sa; Password="
   Set rs = cn.Execute("SELECT logo FROM pub_info WHERE pub_id='0736'")
   Response.BinaryWrite rs("logo")
   Response.End
   %>
This little piece of code will retrieve the image and display it on the screen using the BinaryWrite.  The difficult part is getting the images into SQL Server in the first place but we'll discuss that in a future article.  Here are some related links that I referenced in writing this article:


  http://msdn.microsoft.com/library/periodic/period98/image.htm

  http://msdn.microsoft.com/library/periodic/period98/asp98b1.htm

  http://msdn.microsoft.com/library/partbook/asp/binarydata.htm



    


    

            
                    
                        
                            Related Articles

                            
                            Application Locks (or Mutexes) in SQL Server 2005  (7 January 2008)

                            
                            What I Wish Developers Knew About SQL Server (Presentation)  (11 October 2007)

                            
                            Multiple Active Result Sets (MARS) – Transactions and Debugging  (17 June 2007)

                            
                            Multiple Active Result Sets (MARS)  (3 April 2007)

                            
                            How SQL Server 2005 Enables Service-Oriented Database Architectures  (8 September 2006)

                            
                            Presentation: What I Wish Developers Knew About SQL Server  (17 November 2005)

                            
                            GeoCoding with SQL Server and Google  (8 August 2005)

                            
                            How to Asynchronously Execute a DTS package from ASP or ASP.NET  (27 March 2005)

                            
                

                         
                       
                       
                            Other Recent Forum Posts
                            
                            Troubleshooting Deadlocks in SQL Server (1d)
                            
                            Last Login date and time (2d)
                            
                            Negative effects of High VLF counts (2d)
                            
                            Need to return a value that indicates that a record has been added, but not when a record is modified (3d)
                            
                            Indexex on low cardinality fields (3d)
                            
                            Error in stored procedure (4d)
                            
                            Spam post flagging (4d)
                            
                            Update Microsoft SQL Server (RTM) 12.0.2000.8 to latest v14 (12.0.6449.1) (4d)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources