Results 1 to 10 of 10
  1. #1
    Sukeshps is offline Member
    Join Date
    Jan 2012
    Posts
    10
    Rep Power
    0

    Default Inserting images in DB using blob type

    Hi,
    Im trying to insert images and other files into the DB using BLOB. I was able to insert data but onluy small files are getting inserted.For larger files it is not showing any errors but only null gets inserted into the column.Even if i use empty_Blob after insertion it shanges to null. I tried using for update in the query and then update the column. But BinaryOutputStream is not present for BLOB. Using setbinaryStream also throws exception.Any help is highly appreciated.
    Im using oracle DB and ojdbc14.

    The below code worked for small files(very small)


    buffer.append("update imagetable set image=?, img_name='no' where img_id=1");
    ps = conn.prepareStatement(buffer.toString());
    ps.setBinaryStream(1,file.getInputStream(), (int) file.getSize());
    ps.setBytes(1,file);
    ps.executeUpdate();




    I tried the below code but getOutputStream() depreciated and setoutputstream also gave error "Unsupported feature". I have given autocommit off.
    Never knew file upload would be this complex.


    try
    {
    conn.setAutoCommit (false);
    byte[] imgData = photo.getPhoto( conn, iNumPhoto ) ;
    response.setContentType("image/gif");
    OutputStream o = response.getOutputStream();
    o.write(imgData);
    o.flush();
    o.close();
    }
    catch (Exception e)
    {
    e.printStackTrace();
    throw e;
    }
    Last edited by Sukeshps; 01-03-2012 at 03:13 PM.

  2. #2
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,006
    Rep Power
    20

    Default Re: Inserting images in DB using blob type

    What happens (in the top code snippet) if you use the setBinaryStream() that does not take a length parameter?
    In fact...why are you setting the single parameter twice? Once with setBinaryStream and once with setBytes()?
    The latter is really quite wrong.

  3. #3
    Sukeshps is offline Member
    Join Date
    Jan 2012
    Posts
    10
    Rep Power
    0

    Default Re: Inserting images in DB using blob type

    im sorry the ps.setBytes(1,file); is actually a commented line..
    But this stuff works quite fine (for small files ofcourse(

    Below is the code that i tried latest, gives me "unsupported feature" error at the line OutputStream os=blob.setBinaryStream(1L);



    buffer.append("SELECT IMAGE FROM imagetable where img_id=1 for update");

    ps = conn.prepareStatement(buffer.toString());
    rs = ps.executeQuery();
    while(rs.next())
    {
    Blob blob=rs.getBlob(1);
    OutputStream os=blob.setBinaryStream(1L);
    byte [] data=new byte[1];
    int i=0;
    while((i=fis.read(data))!=-1)
    {
    os.write(data,0,i);
    }
    os.close();

    }

  4. #4
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,006
    Rep Power
    20

    Default Re: Inserting images in DB using blob type

    Don't do "for update".
    Waste of time.
    Do a straight forward UPDATE statement (as in your original post).
    Then do this:
    Java Code:
    buffer.append("update imagetable set image=?, img_name='no' where img_id=1");
    ps = conn.prepareStatement(buffer.toString()); 
    ps.setBinaryStream(1,file.getInputStream());
    ps.executeUpdate();
    as I suggested.

    Then tell me what happens.

    You have no need to loop over the streams or anything.
    The driver will do that all for you.

  5. #5
    Sukeshps is offline Member
    Join Date
    Jan 2012
    Posts
    10
    Rep Power
    0

    Default Re: Inserting images in DB using blob type

    That gives me an exception


    java.lang.AbstractMethodError: org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStat ement.setBinaryStream(ILjava/io/InputStream;)V

  6. #6
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,006
    Rep Power
    20

    Default Re: Inserting images in DB using blob type

    What's the full exception?

    Ah, hang on.
    You've got a mismatch between your Java version and your JDBC version.
    Just noticed you're using ojdbc14, which was built for Java 1.4.
    If you're using 1.6 you really ought to be using ojdbc16 (or is it ojdbc6?).
    You need to check that that works with your version of Oracle (it should, but no guarantee if you're on an unsupported Oracle version).

  7. #7
    Sukeshps is offline Member
    Join Date
    Jan 2012
    Posts
    10
    Rep Power
    0

    Default Re: Inserting images in DB using blob type

    Thanks..
    I tried using ojdbc6.jar
    atleast i was able to use getBinaryOutputStream now.
    but that did not solve the main problem.

    Im still getting exception when using setBinaryStream without the length parameter


    Jan 4, 2012 8:15:27 PM org.apache.catalina.core.StandardWrapperValve invoke
    SEVERE: Servlet.service() for servlet testfileupload threw exception
    java.lang.AbstractMethodError: org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStat ement.setBinaryStream(ILjava/io/InputStream;)V
    at com.att.smartchathome.dao.DataServiceInterfaceImpl .setviaImageinputStream(DataServiceInterfaceImpl.j ava:15688)
    at com.att.smartchathome.testfileupload.doPost(testfi leupload.java:81)
    at javax.servlet.http.HttpServlet.service(HttpServlet .java:637)
    at javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
    at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:128)
    at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(H ttp11Processor.java:849)
    at org.apache.coyote.http11.Http11Protocol$Http11Conn ectionHandler.process(Http11Protocol.java:583)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run( JIoEndpoint.java:454)
    at java.lang.Thread.run(Unknown Source)

  8. #8
    Sukeshps is offline Member
    Join Date
    Jan 2012
    Posts
    10
    Rep Power
    0

    Default Re: Inserting images in DB using blob type

    Also while using the following code i did not get any exception but the Blob column gets set to null when the size of the file is more than say 3-4 KB. Is it because of the way oracle stores larger data in Blob. But if it is, then atleast the reference to the Datatable should be present. Its showing null after insert.

    buffer.append("update imagetabletest set image=? , img_name='test4' where img_id=1");

    ps = conn.prepareStatement(buffer.toString());
    ps.setBinaryStream(1, (InputStream)fis,fis.available());
    rs = ps.executeQuery();

  9. #9
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,006
    Rep Power
    20

    Default Re: Inserting images in DB using blob type

    fis.available() does not give the size of the file.
    From the API:
    "
    Returns an estimate of the number of remaining bytes that can be read (or skipped over) from this input stream without blocking by the next invocation of a method for this input stream.
    "

    As for your other problem, I suspect your DBCP is old as well.

  10. #10
    Sukeshps is offline Member
    Join Date
    Jan 2012
    Posts
    10
    Rep Power
    0

    Default Re: Inserting images in DB using blob type

    Thanks a lot..I am now able to save and read files successfully.

Similar Threads

  1. Replies: 5
    Last Post: 02-16-2012, 09:26 AM
  2. inserting images in database through the jfilechooser
    By marvelk in forum Advanced Java
    Replies: 2
    Last Post: 02-07-2011, 10:31 AM
  3. Replies: 2
    Last Post: 07-01-2010, 02:32 AM
  4. Replies: 2
    Last Post: 04-01-2010, 04:35 AM
  5. Saving Blob through Hibernate
    By Ed in forum JDBC
    Replies: 2
    Last Post: 01-05-2009, 05:03 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •