1 Reply Latest reply on Jul 12, 2005 9:01 AM by John Pallot

    ORACLE BLOBS

    John Pallot Newbie

      JBOSS 4.0.2 Managed to set up a thin connection with an oracle database (using an oracle datasource).

      However the result set cannot handle Oracle blobs > 4k in size.

      I have taken this approach ( which has worked for me before):

      get connection (called conn in this example) then:

      Statement st = conn.createStatement();
      resultSet = st.executeQuery("SELECT IMAGE_BLOB FROM ......);
      oracle.sql.BLOB blob = null;
      blob = ((oracle.jdbc.driver.OracleResultSet) resultSet).getBLOB(1);

      But JBOSS throws a classcast exception at the last line!!


      14:40:31,986 ERROR [[DVDImageServlet]] Servlet.service() for servlet

      DVDImageServlet threw exception
      java.lang.ClassCastException
      at datasource.DVDImageServlet.doPost(DVDImageServlet.java:77)
      at datasource.DVDImageServlet.doGet(DVDImageServlet.java:106)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
      ilterChain.java:173)
      at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFi
      lter.java:81) etc................................

      Any ideas? I have found very little out there (on the internet) but this must be a common problem!!

      John

        • 1. Re: ORACLE BLOBS
          John Pallot Newbie

          Answer to this is as follows:

          1. ensure that the 10g version of ojdbc14.jar is deployed to server/default/lib (if using default)


          2. write method to obtain BLOB from table and write it to an OutputStream (in this example has been placed in a class called ProductBO - see 3 below)

          public void writeImageForProduct(String id, OutputStream os) throws DAOException {
          Connection conn = null;
          ResultSet rs = null;
          int v = 0;
          conn = connectionHelper.getConnection();
          PreparedStatement stmt = null;
          try
          {
          stmt = conn.prepareStatement("SELECT dvd_title_id, cover_image FROM dvdtitle where dvd_title_id = '" + id + "'");
          rs = stmt.executeQuery();
          while (rs.next())
          {
          Blob blob = rs.getBlob(2);

          try {
          InputStream imageStream = blob.getBinaryStream();
          while ((v = imageStream.read()) != -1) {
          os.write(v);
          }
          imageStream.close();
          }
          catch (IOException e) {
          throw new DAOException(e);
          }
          }
          rs.close();
          }
          catch (SQLException e) {
          throw new DAOException("SQL Exception " + e.getMessage(), e);
          }
          finally {
          connectionHelper.closeConnection(conn, null);
          }
          }

          3. Extract from servlet:
          .................
          response.setContentType("image/gif");
          ProductBO productBO = new ProductBO();
          try {
          ServletOutputStream outputStream = response.getOutputStream();
          productBO.writeImageForProduct(dvd_image_id,outputStream);
          response.flushBuffer();

          } catch (ProductException e) {
          throw new ServletException(e.getMessage());
          }
          ..................