How to save an image in SQL Server

**cutecode** · Nov 2, 2001, 11:07

Could you, PLEASE, give me an example
of how to save an image in SQL Server?

Suppose I have a path to the image 'c:\image.gif'
and datafield in SQL Server database is 'Image'

What SQL statement should I write then ...?

Thank you

**shane** · Nov 2, 2001, 17:33

Try this MS KB article.

**wdmny** · Nov 2, 2001, 20:31

I haven't done this myself, but I believe you could read the data of the image and then just set it in a binary field. When you wanted to retrieve it, you could SELECT it from the database and, set the Content-Type to image and then dump the data. Like I said, I haven't done this, but in theory, it *should* work.

**cutecode** · Nov 3, 2001, 06:23

The code sample in "MS KB article" requies
that I had ActiveX Data Objects (ADO) 2.5
and SQL Server 7
but
I have ActiveX Data Objects (ADO) 2.1 and SQL Server 6.5

I have tried the following code for the pubs database which is included in SQL Server
and has "Image" datafield. But it does not show me the pictures.
It writes all the characters in this data field. Where am I wrong?

Code:

<%@ Language=VBScript %>
<!-- #INCLUDE FILE="adovbs.inc" //-->
<% Response.Expires = -1000 
Response.Buffer = True 'Buffers the content so our Response.Redirect will work
%>
<HTML>
<HEAD>
<TITLE> New Document </TITLE>
</HEAD>

<BODY>
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "FILEDSN=pubs.dsn"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "Select * from pub_info", cn, adOpenKeyset, adLockOptimistic
do while not rs.eof
	pic = rs.Fields("logo").value
	Response.ContentType = "image/GIF" 
	response.BinaryWrite  pic
	response.write "<br>---------------------------------------------------------------<br>"
	rs.MoveNext
loop
%>
</BODY>
</HTML>

Could someone, PLEASE, give an ASP example how write/show pictures in SQL Server ?

Thank you.

**wdmny** · Nov 3, 2001, 12:13

Well, for one thing, you can't output HTML after you set the content type to image. If you want to retrieve the image, you need a specific page such as "viewimage.asp", which you call on as if it is an image in your page.

For instance, the code below would be your webpage, and an adaptation of the code you wrote would be viewimage.asp

Code:

<img src="viewimage.asp" width="50" height="50">

Hope this clears things up a little.

**cutecode** · Nov 3, 2001, 15:09

Thank you Wes DeMoney.

Now, I know how to display images from SQL Server,
but now I want to write this image data onto SQL Server
I'm trying this code

Code:

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "FILEDSN=pubs.dsn"

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("c:\5lakes.gif")
Set ts = f.OpenAsTextStream(1, -2)
str = ts.readline
cn.execute("update pub_info set logo='" & str & "' where pub_Id='0736'")

But it does not working.
Where am I wrong?

**hillsy** · Nov 4, 2001, 08:11

You may find this article on SQLTeam useful as well...

http://www.sqlteam.com./item.asp?ItemID=986

**cutecode** · Nov 4, 2001, 09:19

Hello hillsy.

The link you gave me - shows only how to display images
from a database.
But I need to know how to save this image
in database.

**hillsy** · Nov 4, 2001, 10:08

True...

But I think they're basically saying you shouldn't store images in a SQL Server database, period. Putting them in and getting them out are really just two sides of the same coin.

**cutecode** · Nov 4, 2001, 12:41

Could someone, PLEASE, show me the code how to save an image onto database !!!!

I'm using this code now, but it doesn't work.

Code:

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "FILEDSN=pubs.dsn"
set RS = Server.CreateObject("ADODB.Recordset")
rs.Open "pub_info1", Cn, 1, 2
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile("c:\5lakes.gif")
Set ts = f.OpenAsTextStream(1, -2)
bytBLOB = ts.ReadAll

rs.addnew
rs.fields("pub_Id")="2"
rs.fields("logo").AppendChunk bytBLOB
rs.Update

I know that saving images as .gif files is better,
but my boss wants me to save them in SQL Server,
because our SQL Server is backed up every 6 hours.

Thank you.

**kevin_tremblay** · Nov 6, 2001, 07:15

If your server is backed up every 6 hours then what is the big deal of getting the images into the database? Unless the only thing on the whole server being backed up is SQL.

If this is the case then I would talk you your IT guys and have them backup the whole server then you can capture your images wherever they are stored on the server.

Just a suggestion from a SYS ADMIN. Seems to be a real easy solution for you rather than coding something that is no really suggested anyway for SQL like Hillsy said.

I know all my SQL guys along with myself never keep our images in the datbase. But everyone does things a little different across the board.

The SitePoint Forums have moved.

User Tag List

Thread: How to save an image in SQL Server

Thread Tools

Display

How to save an image in SQL Server

Bookmarks

Bookmarks

Posting Permissions