SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Jun 2001
    Location
    Russia
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to save an image in SQL Server

    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
    Regards,
    Alexander Saprykin
    http://www16.brinkster.com/saprykin

  2. #2
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this MS KB article.

  3. #3
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Zealot
    Join Date
    Jun 2001
    Location
    Russia
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Regards,
    Alexander Saprykin
    http://www16.brinkster.com/saprykin

  5. #5
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SitePoint Zealot
    Join Date
    Jun 2001
    Location
    Russia
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Regards,
    Alexander Saprykin
    http://www16.brinkster.com/saprykin

  7. #7
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You may find this article on SQLTeam useful as well...

    http://www.sqlteam.com./item.asp?ItemID=986
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  8. #8
    SitePoint Zealot
    Join Date
    Jun 2001
    Location
    Russia
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Regards,
    Alexander Saprykin
    http://www16.brinkster.com/saprykin

  9. #9
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  10. #10
    SitePoint Zealot
    Join Date
    Jun 2001
    Location
    Russia
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Regards,
    Alexander Saprykin
    http://www16.brinkster.com/saprykin

  11. #11
    SitePoint Addict kevin_tremblay's Avatar
    Join Date
    Jul 2001
    Location
    Boston Area (North shore)
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Kevin Tremblay-- Sys Admin
    work: kevin_tremblay@hsgmed.com
    "The object of the journey is not to arrive."


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •