Solved

Inserting and Retrieving image to and from MySQL

Posted on 2004-10-26
1,134 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
Question by:widiyanto
    29 Comments
     
    LVL 35

    Expert Comment

    by:TimYates
    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
    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
    >  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
     
    LVL 35

    Expert Comment

    by:TimYates
    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
    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
    This is JSP, not PHP tho ;-)
    0
     
    LVL 27

    Expert Comment

    by:rrz
    0
     
    LVL 35

    Expert Comment

    by:TimYates
    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
    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
    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
    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
    try to give complete path(i.e. absolute or relative)
    0
     

    Author Comment

    by:widiyanto
    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
    > 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
    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
    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
    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
    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
    ok.. I try first...
    0
     

    Author Comment

    by:widiyanto
    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
    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
    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
    Should be:

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

    Author Comment

    by:widiyanto
    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:
    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
    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
    Hmmm....  can you post your code?

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

    Tim
    0
     

    Author Comment

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

    Expert Comment

    by:TimYates
    Hehehe cool :-)

    Good luck with it!

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

    Tim
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    maven scope 1 113
    Images won't display in JSP pages 30 161
    immutable object concept 5 91
    spring example non maven 4 40
    School is back in session! The beginning of the school year is a fresh slate. One way for students to get started on the right foot is to get organized.
    It’s an age old story, whether you’re looking for full-time employment or contract work. In order to land a job, you must have experience.
    This video is in connection to the article "The case of a missing mobile phone (https://www.experts-exchange.com/articles/28474/The-Case-of-a-Missing-Mobile-Phone.html)". It will help one to understand clearly the steps to track a lost android phone.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    913 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

    19 Experts available now in Live!

    Get 1:1 Help Now