Inserting and Retrieving image to and from MySQL

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

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?

>  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

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

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

This is JSP, not PHP tho ;-)

Did you see this ?
http://www.ftponline.com/javapro/2004_01/magazine/features/jodonahue/default_pf.aspx

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

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

I know that probel is related to JSP but te funda is same you can try fileupload in insertinto command
:)

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());
        }
            

try to give complete path(i.e. absolute or relative)

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?

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

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?

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

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?

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?

ok.. I try first...

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();
}      
                        

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();

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");

Should be:

String userId = (String)session.getAttribute("userid");

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

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

Hmmm....  can you post your code?

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

Tim

haha... it's ok.. I've solved it already.. thanks..

Hehehe cool :-)

Good luck with it!

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

Tim