ORACLE BLOBS

[pallotjk]

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

[pallotjk]

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