aspose file tools*
The moose likes JDBC and the fly likes Urgent..Pls.. Problem storing BLOB in Oracle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Urgent..Pls.. Problem storing BLOB in Oracle" Watch "Urgent..Pls.. Problem storing BLOB in Oracle" New topic
Author

Urgent..Pls.. Problem storing BLOB in Oracle

Bhiku Mhatre
Ranch Hand

Joined: Apr 08, 2002
Posts: 127
Hello, I am facing a problem in Inserting file into BLOB column in Oracle database.
Following is the code..
public void storeDATA(File file) throws MessagingException,IOException
{
int num = getNextPK();
String sqlQuery = "insert into mytable values(?,?,?,?,?,?,?,sysdate,?,?,?,EMPTY_BLOB(),?,?,'ddd,'U',null,?)";
DatabaseConnection dbConnection = null;
Connection conn = null;
try {

int size = (file==null)?0 int)(file.length());


conn = pool.getConnection();
conn.setAutoCommit(false);
PreparedStatement prepStatement = conn.prepareStatement(sqlQuery);
prepStatement.setInt(1,num);
prepStatement.setString(2,"DATA");
prepStatement.setString(3,"DATA");
prepStatement.setString(4,"DATA");
prepStatement.setString(5,"DATA");
prepStatement.setString(6,"DATA");
prepStatement.setString(7,"DATA");
prepStatement.setString(8,"DATA");
prepStatement.setString(9,"DATA");
prepStatement.setString(10,"DATA");
prepStatement.setString(11,"DATA");
prepStatement.setInt(12,num);
prepStatement.setString(13,"DATA");
prepStatement.execute();

if(file !=null) {
Statement stmt = conn.createStatement();
String sqlSelect = "SELECT blobdata FROM mytable WHERE pk = "+num+" FOR UPDATE";



ResultSet rs1 = stmt.executeQuery(sqlSelect);
if(rs1.next())
{
java.sql.Blob mapBlob = rs1.getBlob(1);
OutputStream blobOutputStream = ((oracle.sql.BLOB)mapBlob).getBinaryOutputStream();

// Open the file as a stream for insertion into the Blob column
InputStream sampleFileStream = new FileInputStream(file);

// Buffer to hold chunks of data to being written to the Blob.
byte[] buffer = new byte[10* 1024];

// Read a chunk of data from the sample file input stream, and write the
// chunk to the Blob column output stream. Repeat till file has been
// fully read.
int nread = 0; // Number of bytes read
while( (nread= sampleFileStream.read(buffer)) != -1 ) // Read from file
blobOutputStream.write(buffer, 0, nread); // Write to Blob

// Close both streams
sampleFileStream.close();
blobOutputStream.close();
}// END OF if(rs.next())
}// END OF if(file !=null)

// HERE U INSERT A FILE INTO DATABASE......

} catch (Exception ex)
{
ex.printStackTrace();
}
finally {
try {
if(dbConnection != null)
dbConnection.closeConneciton();
} catch (Exception ee) {ee.printStackTrace();}
}
}

}

But I get exception saying :
Error Message: oracle/jdbc/driver/OracleResultSet.getBlob
Error Code: 500
Target Servlet: null
Error Stack:
java.lang.AbstractMethodError: oracle/jdbc/driver/OracleResultSet.getBlob
at TestServlet.storeData(Testervlet.java:233)
at TestServlet.service(SendMailServlet.java:96)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.ibm.servlet.engine.webapp.StrictServletInstance.doService(ServletManager.java:827)
at com.ibm.servlet.engine.webapp.StrictLifecycleServlet._service(StrictLifecycleServlet.java:159)
at com.ibm.servlet.engine.webapp.IdleServletState.service(StrictLifecycleServlet.java:286)
at com.ibm.servlet.engine.webapp.StrictLifecycleServlet.service(StrictLifecycleServlet.java:106)
at com.ibm.servlet.engine.webapp.ServletInstance.service(ServletManager.java:472)
at com.ibm.servlet.engine.webapp.ValidServletReferenceState.dispatch(ServletManager.java:1012)
at com.ibm.servlet.engine.webapp.ServletInstanceReference.dispatch(ServletManager.java:913)
at com.ibm.servlet.engine.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:499)
at com.ibm.servlet.engine.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:278)
at com.ibm.servlet.engine.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:105)
at com.ibm.servlet.engine.srt.WebAppInvoker.doForward(WebAppInvoker.java:67)
at com.ibm.servlet.engine.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:123)
at com.ibm.servlet.engine.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:67)
at com.ibm.servlet.engine.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:106)
at com.ibm.servlet.engine.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:125)
at com.ibm.servlet.engine.oselistener.OSEListenerDispatcher.service(OSEListener.java:315)
at com.ibm.servlet.engine.http11.HttpConnection.handleRequest(HttpConnection.java:60)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:313)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:242)
at com.ibm.ws.util.CachedThread.run(ThreadPool.java:122)

I can not figure out where the problem lies.
Any help is welcome.
Thanks in advance.
Bhiku.


The difference between winner and loser is making things happen and letting things happen.
Sujatha Ranganathan
Greenhorn

Joined: May 29, 2003
Posts: 12
Have a look at the LOB Datatype Sample on OTN for ideas.
Hope this helps.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Urgent..Pls.. Problem storing BLOB in Oracle