Solved

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

Posted on 2004-04-01
10
570 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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
 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

730 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