PDA

View Full Version : mySQL Blob images help

StealthRT
04-03-2009, 07:36 AM
Hey all, i am trying to figure out how to go about loading some images from my database. I have been able to accomplish this with only one image, however. I am looking to loop thought the database and display all images that the query looks for..

Here is my current code:

Pic.asp page

<%
Dim oConnection
Dim oRecordset
Dim Sql

Response.Expires = -10
Response.Buffer = True
Response.Clear

Set oConnection = Server.CreateObject("ADODB.Connection")
Set oRecordset = Server.CreateObject("ADODB.Recordset")

oConnection.Open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=nextbowluser.com; PORT=3306; DATABASE=xxxxx; USER=xxxx; PASSWORD=xxxxxxx; OPTION=3;"
Sql="select CoupPicVoid from coupbook"
oRecordset.Open Sql, oConnection,3,3

do until oRecordset.eof
Response.ContentType = "image/jpeg"
Response.BinaryWrite oRecordset("CoupPicVoid")
oRecordset.movenext
loop

oRecordset.Close
oConnection.Close
Set oRecordset = Nothing
Set oConnection = Nothing
%>


testpic.asp

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<img src="pic.asp">
</body>
</html>

As you can tell, the <img src="pic.asp"> displays the image that the database is finding. But it only displays one pic. What can i do in order to display all the images that the query finds?


Thanks for your time,
David
Spudhead
04-03-2009, 02:27 PM
I have to say I've rarely seen keeping image data in a database as a good idea. It's almost always easier to store your images in the server filesystem and keep the path references in the database.

But anyway - you need to do your loop in testpic.asp, not in pic.asp

I assume you've got some sort of unique ID field in your coupbook table?

You just need to loop through that table, writing out an image tag that calls pic.asp?imageid=whatever

Then your pic.asp will return the image binary data for that image id.
StealthRT
04-03-2009, 07:49 PM
Ah, OK. That's exactly how i ended up doing it.

And the reason behind having the images in the database is purely for security reasons. I don't want the user to be able to type in a URL in order to see/get a picture. If its coming from the database then they are unable to do such a thing.

Thanks again,
David
Old Pedant
04-03-2009, 10:40 PM
You *CAN* put the images into files. Just put the files OUTSIDE the web-accessible directories.

Yes, you then have to use ADODB.Stream to deliver the files, very similar to the DB-based code, but now you do have security.
StealthRT
04-04-2009, 06:09 AM
Mind showing me an example of this, Old Pedant?

David
Old Pedant
04-04-2009, 07:04 AM
<%
Set strm = Server.CreateObject("ADODB.Stream")
strm.Type = 1 ' binary
objStream.Open
objStream.loadFromFile "c:\some\non\web\directory\file.jpg"

Response.ContentType = "image/jpeg"
Response.BinaryWrite strm.Read()

strm.Close
Response.End ' to ensure no junk sent
%>


Naturally, you can pass in the image name or an id or whatever instead of hard-coding as I have here.