Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Inserting and Retrieving image to and from MySQL

Posted on 2004-10-26
29
Medium Priority
?
1,236 Views
Last Modified: 2008-02-01
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...
0
Comment
Question by:widiyanto
[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
  • 13
  • 12
  • 3
  • +1
29 Comments
 
LVL 35

Expert Comment

by:TimYates
ID: 12408905
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
 

Author Comment

by:widiyanto
ID: 12408977
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
 
LVL 35

Expert Comment

by:TimYates
ID: 12409083
>  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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 35

Expert Comment

by:TimYates
ID: 12409091
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
 
LVL 1

Expert Comment

by:krishnaashu
ID: 12409704
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
 
LVL 35

Expert Comment

by:TimYates
ID: 12409768
This is JSP, not PHP tho ;-)
0
 
LVL 28

Expert Comment

by:rrz
ID: 12410938
0
 
LVL 35

Expert Comment

by:TimYates
ID: 12411069
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
 

Author Comment

by:widiyanto
ID: 12417488
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
 
LVL 1

Expert Comment

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

Author Comment

by:widiyanto
ID: 12418459
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
 
LVL 1

Expert Comment

by:krishnaashu
ID: 12418568
try to give complete path(i.e. absolute or relative)
0
 

Author Comment

by:widiyanto
ID: 12418809
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
 
LVL 35

Expert Comment

by:TimYates
ID: 12419083
> 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
 

Author Comment

by:widiyanto
ID: 12419157
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
 
LVL 35

Expert Comment

by:TimYates
ID: 12419197
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
 

Author Comment

by:widiyanto
ID: 12419235
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
 
LVL 35

Expert Comment

by:TimYates
ID: 12419273
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
 

Author Comment

by:widiyanto
ID: 12419504
ok.. I try first...
0
 

Author Comment

by:widiyanto
ID: 12429799
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
 

Author Comment

by:widiyanto
ID: 12430810
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
 

Author Comment

by:widiyanto
ID: 12430930
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
 
LVL 35

Expert Comment

by:TimYates
ID: 12431086
Should be:

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

Author Comment

by:widiyanto
ID: 12431789
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
 
LVL 35

Accepted Solution

by:
TimYates earned 300 total points
ID: 12431820
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
 

Author Comment

by:widiyanto
ID: 12440899
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
 
LVL 35

Expert Comment

by:TimYates
ID: 12441793
Hmmm....  can you post your code?

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

Tim
0
 

Author Comment

by:widiyanto
ID: 12441918
haha... it's ok.. I've solved it already.. thanks..
0
 
LVL 35

Expert Comment

by:TimYates
ID: 12442027
Hehehe cool :-)

Good luck with it!

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

Tim
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

With so many activities to perform, Exchange administrators are always busy in organizations. If everything, including Exchange Servers, Outlook clients, and Office 365 accounts work without any issues, they can sit and relax. But unfortunately, it…
Tech spooks aren't just for those who are tech savvy, it also happens to those of us running a business. Check out the top tech spooks for business owners.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

618 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