Inserting and Retrieving image to and from MySQL

Hi,

I would like to know how to insert and retrieve image to and from mysql. I've read the javazoom.net the uploadbean but still not sure how to do it. Is there anyone who knows how to do it? Is there any sample code or links to do that? Thanks...
widiyantoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimYatesCommented:
I would upload the image (uning an upload bean like the javazoom one, the jakarta FileUpload library, or the OReilly upload servlet), then:

1) create a temp filename for your uploaded images directory inside the web application (using File.createTempFile)
2) Save the image to this temp filename
3) store this temp filename, and the original filename in the mySQL database (as 2 strings fields)

You can then display the image inside normal <IMG tags (rather than having to extract them from the database, and streaming them to the browser using a servlet)

And the database will be smaller

Tim
0
widiyantoAuthor Commented:
Yah, At first I also think like that but my supervisor want the image to be inserted to Mysql...
I tried the javazoom but I always have error which says it can not find the uploadbean class but I already paste the jar file to the classpath. but I haven't try the fileupload library and OReilly upload servlet.. any ideas how to solve this?
0
TimYatesCommented:
>  but I already paste the jar file to the classpath.

Put the jar into

WEB-INF/lib

of your web application :-)

http://www.jguru.com/forums/view.jsp?EID=720163

Shows you how to insert the image into MySQL....  

But it has to be said, that it just makes things tricky for no (apparent) reason ;-)  

But the supervisor is always right :-D
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

TimYatesCommented:
BTW:  Just incase you get no joy from the javazoom upload bean, even after putting the jar in the right place;

The Jakarta Fileupload bean is here:  http://jakarta.apache.org/commons/fileupload/
and the COS servlet (OReilly) is here:  http://www.servlets.com/cos/
Though the licence for the COS one says that you have to buy the book if you use it commercially :-)

Both come with examples :-)

Tim
0
krishnaashuCommented:
Here's my test database for storing images (sorry for the column line-up!):
mysql> use test;
Database changed
mysql> describe blobber;
+--------+---------+------+-----+---------+----------------+
¦ Field ¦ Type ¦ Null ¦ Key ¦ Default ¦ Extra ¦
+--------+---------+------+-----+---------+----------------+
¦ id ¦ int(11) ¦ ¦ PRI ¦ NULL ¦ auto_increment ¦
¦ picbox ¦ blob ¦ YES ¦ ¦ NULL ¦ ¦
¦ info ¦ text ¦ YES ¦ ¦ NULL ¦ ¦
+--------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
You can store pictures in one of two ways: as a path to the file, as suggested, or as a text string in a text or blob mysql field.

In my test table, I store the string version of the picture in the "picbox" field, and the path to the actual image file in the "info" field.

Here's a small piece of what it looks like in the database:

select * from blobber limit 1;
+----+------------------+-------------+
¦ id ¦ picbox ¦ info ¦
+----+------------------+-------------+
¦ 1 ¦ Gif89ai?D?x^.etc.¦ /image.gif ¦
+----+------------------+-------------+
There can be a lot of data in "picbox", depending on how big the picture file is.

I use PHP to send the instructions to "LOAD_FILE('/image.gif')" to put the picture data into "picbox", but you can do it from a terminal or (probably) using one of the graphical mysql managers.

Try just storing and using the path to the image file while you learn, Butterfly_Wing. If you use the "picbox" method of storing the picture file data, you need to do a bunch of other stuff to your PHP installation to get it to work.

<font color=red>LOAD_FILE will serve the purpose</font>
 
regards

Ashu Krishna

0
TimYatesCommented:
This is JSP, not PHP tho ;-)
0
TimYatesCommented:
As someone who's had to try and reclaim BLOB, CLOB, and ordinary data out of an Oracle database after it got too big, and fell over in a bad way (losing timestamp on the transaction logs, and all manner of horribleness), I disagree with his first three paragraphs ;-)

It's a good article tho ;-)
0
widiyantoAuthor Commented:
haha... ok... I'll go and try later.. now my supervisor ask me to do something else first.. as you say supervisor always right... :P
0
krishnaashuCommented:
I know that probel is related to JSP but te funda is same you can try fileupload in insertinto command
:)
0
widiyantoAuthor Commented:
Hi,

I'm trying the code on http://www.jguru.com/forums/view.jsp?EID=720163 but it always give me this error:

image_path:bear.jpg error:bear.jpg (No such file or directory)

Do we need to give the full path of the image file or just the image name?
I'm using 2 jsp file to do this, 1 is addSample1.jsp, 2 is sample_action1.jsp. Can you help me? Thanks

in the addSample1.jsp:
 <tr>
      <td width="25%"><font face="Arial, Helvetica, sans-serif" size="-1">Image Path </font></td>
      <td><input name="image_path" type="FILE" size="75"></td>
    </tr>

in the sample_action1.jsp:

String imagepath = request.getParameter("image_path");
out.println("image_path:"+ imagepath);
sql = "insert into sampledefinition (sample,description,sketch,createdBy,createdOn) values (?,?,?,?)";
    try
    {
          pstmt = con.prepareStatement(sql);
          pstmt.setString(1,sample);
          pstmt.setString(2,description);
          File imageFile = new File(imagepath);
          InputStream is = new FileInputStream(imageFile);
          pstmt.setBinaryStream( 3, is, (int)(imageFile.length()));
          pstmt.setString(4,userId);
          pstmt.setString(5,currentdate);
          retVal = pstmt.executeUpdate();
          stmt.close();
          response.sendRedirect("sample1.jsp");
       } catch(Exception e)
       {
          out.println("error:" + e.getMessage());
        }
            
0
krishnaashuCommented:
try to give complete path(i.e. absolute or relative)
0
widiyantoAuthor Commented:
Yah.. I also tried to give the full path like:

String imagepath = "C://Documents and Settings//Administrator.TECH01//My Documents//My Pictures//bear.jpg";

but still have error:
image_path:C://Documents and Settings//Administrator.TECH01//My Documents//My Pictures//bear.jpg error:C:/Documents and Settings/Administrator.TECH01/My Documents/My Pictures/bear.jpg (No such file or directory)

most likely is that I can not get the file path from the input.... Is it correct wat I've done?
0
TimYatesCommented:
> Is it correct wat I've done?

No, the full filename is the name on the client's computer, not yours...  

What you need to do is call:

pstmt.setBinaryStream( 3, file.getInputStream(), sz ) ;

where "file.getInputStream()" is the inputstream for the multipart file being uploaded -- and sz is the size of it...
0
widiyantoAuthor Commented:
Hi Tim,

Is it like this?

File imageFile = new File(imagepath);
InputStream is = new FileInputStream(imageFile);
pstmt.setBinaryStream( 3, is, (int)(imageFile.length()));

But I also can not pass the parameters from 1 jsp page to another. It always return null.... I guess it's because the:
<form name="form1"  method="post" ENCTYPE='multipart/form-data' action="sample_action1.jsp?cmd=add">

do you know the solution?
0
TimYatesCommented:
Hmmm...  I have had a look at that javazoom upload bean (I've never used that one before), and I can't see how you can do it this way (the documentation seems to rely on jsptags to do all the processing)...

Basically...  if the image is getting saved to a directory on the server, then yes, your way is correct

What you should be able to do, it get back a list of "multipart-parts" from the bean.  you can then go through this list and call "getParameter" on each of them...  What you will find generally is that there are 2 parts...  one contains all your parameters, the other contains the file.

If the image is not getting saved on the server, then you should be able to get an inputstream for this file (in the respective part), and just pass that straight to the setBinaryStream call...

I hope this is a bit clearer...

> But I also can not pass the parameters from 1 jsp page to another. It always return null.... I guess it's because the:

Yeah, that's why...  you have to iterate through the parts, and call getParameter on each part to see if that parameter is there...
0
widiyantoAuthor Commented:
Sorry... but I still don't understand wat you're trying to say...hehe....

> Basically...  if the image is getting saved to a directory on the server, then yes, your way is correct

but my supervisor wants the image to be inserted to mysql which mean I can not use this method is it?
0
TimYatesCommented:
But you are trying to do:

> File imageFile = new File(imagepath);
> InputStream is = new FileInputStream(imageFile);

The "imagePath" that you have, is the path of the image ont the client machine, wheras this code is being run on the server machine, so this will always fail...

Can you actually iterate through the parts using the javazoom upload bean?  Then getInputStream on the file object?
0
widiyantoAuthor Commented:
ok.. I try first...
0
widiyantoAuthor Commented:
hi Tim,

I can run the databaseupload in the javazoom sample code. but I am not sure how to insert it in my program. Do you know how the databaseupload know which table that they want to insert? Sorry, I am a bit blur in this...

here is the part of my program:

String sample = request.getParameter("sample");
String description = request.getParameter("description");

try
 {
if (MultipartFormDataRequest.isMultipartFormData(request))
{
 MultipartFormDataRequest mrequest = new MultipartFormDataRequest(request);
 String todo = null;
 if (mrequest != null) todo = mrequest.getParameter("todo");
 if ( (todo != null) && (todo.equalsIgnoreCase("upload")) )
 {
     Hashtable files = mrequest.getFiles();
     if ( (files != null) && (!files.isEmpty()) )
     {
        UploadFile file = (UploadFile) files.get("image_path");            
        if (file != null) out.println("<li>Form field : uploadfile"+"<BR> Uploaded       file : "+file.getFileName()+" ("+file.getFileSize()+" bytes)"+"<BR> Content Type : "+file.getContentType());
        sql = "insert into sampledefinition (sample,description,sketch,filename,createdBy,createdOn) values (?,?,?,?,?)";
        pstmt = con.prepareStatement(sql);
        pstmt.setString(1,sample);
        pstmt.setString(2,description);
        pstmt.setBinaryStream(3, upBean.store(mrequest, "image_path"));
        pstmt.setString(4,file.getFileName());
        pstmt.setString(5,userId);
        pstmt.setString(6,currentdate);
        retVal = pstmt.executeUpdate();
        pstmt.close();
        response.sendRedirect("sample1.jsp");
     }else
     {
         out.println("<li>No uploaded files");
     }
}
else out.println("<BR> todo="+todo);
}
}catch (Exception e)
{
e.printStackTrace();
}      
                        
0
widiyantoAuthor Commented:
Hey Tim, I think I know what you're trying to say..
IS it like we try to get the parameter by using MultipartFormDataRequest then insert to the mysql something like this? :D but i still got error : "todo:upload sampleikad description:test uploadfile:null error:null"

<tr>
      <td><font face="Arial, Helvetica, sans-serif" size="-1">Sample Type</font></td>
      <td><input name="sample" type="text" size="50" maxlength="50">
      </td>
    </tr>
    <tr>
      <td width="25%"><font face="Arial, Helvetica, sans-serif" size="-1">Description </font></td>
      <td>
        <input type="text" name="description" maxlength="50" size="50">
      </td>
    </tr>
      <tr>
      <td width="25%"><font face="Arial, Helvetica, sans-serif" size="-1">Image Path </font></td>
      <td> <input name="uploadfile" type="FILE" size="50"></td>
    </tr>
    <tr>
      <td>
        <div align="center">&nbsp; </div>
      </td>
      <td>
        <input type="reset" name="RESET" value="Reset">
            <input type="hidden" name="todo" value="upload">
            <input name="addcode" type="submit" id="add" value="Add Code">
      </td>
</tr>

MultipartFormDataRequest mrequest = new MultipartFormDataRequest(request);
String todo = mrequest.getParameter("todo");
String sample = mrequest.getParameter("sample");
String uploadfile = mrequest.getParameter("uploadfile");
String description = mrequest.getParameter("description");
Hashtable files = mrequest.getFiles();
UploadFile file = (UploadFile) files.get("uploadfile");
sql = "insert into sampledefinition (sample,description,sketch,filename,createdBy,createdOn) values (?,?,?,?,?,?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1,sample);
pstmt.setString(2,description);
File imgFile = new File(file.getFileName());
pstmt.setBinaryStream(3, new FileInputStream(file.getFileName()),(int)imgFile.length());
pstmt.setString(4,file.getFileName());
pstmt.setString(5,userId);
pstmt.setString(6,currentdate);
retVal = pstmt.executeUpdate();
0
widiyantoAuthor Commented:
Hi Tim,

Finally, I've managed to do this... I just change the:

pstmt.setBinaryStream(3, new FileInputStream(file.getFileName()),(int)imgFile.length());

to:
pstmt.setBinaryStream(3, file.getInpuStream(),(int)file.getFileSize());

but I can not get the userId and direct to another page though... can help me? cos the userId is retrieved by using session..

String userId = (String)session.getValue("userid");
response.sendRedirect("sample1.jsp");
0
TimYatesCommented:
Should be:

String userId = (String)session.getAttribute("userid");
0
widiyantoAuthor Commented:
OK, One last question... how to retreive from database and display it on the HTML? :D

I use:

byte[] b = rs.getBytes(4);
<td><img src="sampleView.jsp?sampleId=<%=sampleId%>" width="84" height="110"></td>

but it doesn't work.. hehe...
0
TimYatesCommented:
You will have to write a servlet, that writes the bytes from the database to the response object...

something like:

response.setContentType( "image/gif" ) ;
byte[] b = rs.getBytes(4);
response.getOutputStream().write( b ) ;

But it will have to be another jsp or servlet...  then you can do:

<img src="sampleView.jsp?sampleId=<%=sampleId%>" width="84" height="110">

assuming "sampleView.jsp" is the thing that does what I put above ;-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
widiyantoAuthor Commented:
ok.. thanks.. 1 more last question.. hehe..

when I try in my server, there is this error that says "IllegalStateException getOutputStream() has already been called for this response" then I try to put out.flush before or after response.getOutputStream().write( b ) ; but in the end I can not display my picture...
0
TimYatesCommented:
Hmmm....  can you post your code?

Hee hee, what a lot of questions for 100 points ;-)

Tim
0
widiyantoAuthor Commented:
haha... it's ok.. I've solved it already.. thanks..
0
TimYatesCommented:
Hehehe cool :-)

Good luck with it!

Heh, next time I shall try to get an "A" grade :-/  ;-)

Tim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.