Solved

ASP & MySQL > Failing to store/retrieve images from BLOB column.

Posted on 2004-04-01
10
544 Views
Last Modified: 2010-05-18
Hi,

Briefly: I'm using an ASP page to store data using the ASPUpload component in a mySQL database field. The code for this is listed below. I am then using some ASP code to display the image from the database, but it doesn't work. Can anyone offer any assistance?

Thanks.

---------------
CODE TO SAVE THE IMAGE TO DB
---------------

<%


set Upload = Server.CreateObject("Persits.Upload")

Count = Upload.Save
Upload.SetMaxSize 100000, True


   ' Build ADO connection string
    Connect = "Driver={mysql odbc 3.51 driver};Server=***;Database=***s;uid=***;pwd=***;OPTION=3"
   ' Use ADO Recordset object
   Set rs = Server.CreateObject("adodb.recordset")

   'Open recordset to insert file
   rs.Open "SELECT * FROM office_plus", Connect, 2, 3

 For each File in Upload.Files

   rs.AddNew
 
    rs("filename") = File.FileName
    rs("filesize") = File.Size
    rs("ContentType") = File.ContentType
    rs("Image").AppendChunk = File.Binary
   
    rs("Created") = Now()
   
         rs("ProductCode") = Upload.Form("PartNumber")
      rs("ProductName") = Upload.Form("ItemName")
      rs("FormerPrice") = Upload.Form("WasPrice")
      rs("CurrentPrice") = Upload.Form("NowPrice")
      rs("Expires") = Upload.Form("Expires")
      
   rs.Update
 
  Next

  rs.close
  set rs = nothing
 
  response.redirect("saved.asp")
 
%>


-----------------
CODE TO DISPLAY IMAGE
-----------------

<%
' Build ADO connection string
    Connect = "Driver={mysql odbc 3.51 driver};Server=***;Database=***;uid=***;pwd=***;OPTION=3"
   ' Use ADO Recordset object
   Set rs = Server.CreateObject("adodb.recordset")

   'Open recordset to insert file
   rs.Open "SELECT * FROM office_plus WHERE pid = [whatever_i_use]", Connect, 2, 3
   
   do while not rs.eof
   i=i+1
   rs.movenext
   loop
   
   if i > 0 then
   
   rs.movefirst    
 
   response.ContentType = rs("contenttype")
   response.binarywrite rs("image")
   
  else
        
  response.write "NO image"
 
  end if

   rs.close
   set rs = nothing

%>

Thanks people!
0
Comment
Question by:icx
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 15

Expert Comment

by:deighc
ID: 10732090
> but it doesn't work

How about some more information.

What doesn't work - the upload or the display? What is the exact error message you get? What line is the error occuring on?
0
 
LVL 1

Author Comment

by:icx
ID: 10732596
Well,

There is no specific error message at all. MySQL shows that the BLOB field is present (6KB) and there are no recordset errors etc. The only problem is, when attempting to use response.binarywrite to output the image, it doesn't display (red square in IE). I also attempted to force download using the Response.AddHeader, which works, but then the file that is downloaded contains no data.

Any more information you need?

Thanks.
0
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 10732660
Look at this:
http://www.aspfaqs.com/ASPScripts/PrintFAQ.asp?FAQID=172


If the only thing you are showing on the page is an image then you need to put this on the ASP page.
Try this if the image is a gif:

Response.ContentType = "image/gif"
'Send the binary bits to the browser
Response.BinaryWrite(objRS("Picture"))

To mix content, ie have text and images on the same page, then you need to do something like this:
<ing src="dispImageFromDB.asp?imgid=1232">
The asp page retreives the image from the db and uses the above code to return the image
0
 
LVL 15

Expert Comment

by:deighc
ID: 10732662
> Any more information you need?

No, that explains things nicely. Thanks.

So you know that there's data in the DB.

In that case make absolutely certain that there are no whitespace characters outside the opening "<%" and closing "%>" tags in your ASP page that displays the image. If there is, these characters will output as character data and could corrupt the binary image stream.

Also, for testing purposes just type the URL to the image display page directly into the browser address bar. Then, if the image doesn't display, view the source. There may be an ASP error which you'd never see if you only used the page as an image src.
0
 
LVL 1

Author Comment

by:icx
ID: 10733182
Hi Folks,

Done all that - there is NO whitespace in the document that is displaying the image, and there is nothing in the source of the document. Note: my show code should display the words "NO IMAGE" if there is no record found.

Read that article, but there doesn't appear to be anything different in it to what I have already done, Slimshaneey.

Any more ideas?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 15

Expert Comment

by:deighc
ID: 10733265
Are you sure that the value if rs("contenttype") is a valid image content-type string?
0
 
LVL 1

Author Comment

by:icx
ID: 10733633
Yup.... it's set to "image/jpeg" and was set automatically by ASPUpload.
0
 
LVL 15

Expert Comment

by:deighc
ID: 10733675
Hmmm, very mysterious.

I can only suggest you have a good look thru the MySQL site and/or other forums and see if there are any issues with the MySQL ODBC driver and BLOB fields.
0
 
LVL 11

Accepted Solution

by:
Slimshaneey earned 250 total points
ID: 10733692
Actually, your code in the display page doesnt look right...

Ive made some corrections:

<%
' Build ADO connection string
    Connect = "Driver={mysql odbc 3.51 driver};Server=***;Database=***;uid=***;pwd=***;OPTION=3"
   ' Use ADO Recordset object
   Set rs = Server.CreateObject("adodb.recordset")

   'Open recordset to insert file
   rs.Open "SELECT * FROM office_plus WHERE pid = [whatever_i_use]", Connect, 2, 3
   
   '//Cant have more than one binarywrite - do while not rs.eof
 if not (rs.eof and rs.bof)
   
   response.ContentType = rs("contenttype")
   response.binarywrite rs("image")
   
  else
       
  response.write "NO image"
 
  end if

   rs.close
   set rs = nothing

%>
0
 
LVL 10

Expert Comment

by:Banthor
ID: 14481168
It's a Bitmap Image what should
   response.ContentType = rs("contenttype") Read
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How do i Properly Convert Date time String in C# 29 66
CSS Question.. 3 77
JQuery Date Time picker not showing 29 111
Copy only dates 3 80
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now