Storing blob > 4kb in oracle db through jdbc

[IM Mahesh]

Hello friends,

I am facing problem to store a file data in oracle blob having file size grater than 4kb. However if i try to store file smaller than 4kb it's working fine.

I get following error.

java.sql.SQLException: Io exception: Software caused connection abort: socket write error
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:189)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:231)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:345)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2094)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1986)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2697)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:457)
at com.evermind.sql.FilterPreparedStatement.executeUpdate(FilterPreparedStatement.java:240)
at com.evermind.sql.FilterPreparedStatement.executeUpdate(FilterPreparedStatement.java:240)
at com.evermind.sql.FilterPreparedStatement.executeUpdate(FilterPreparedStatement.java:240)


What could be the solution for this?

Thanks in advance.


Mahesh

[viju vi]

Hi man,

even im getting the same kind of error, i tried all possible ways but its giving me the same error. you see if you try a file or data less than 4k it works well and fine but data which exceeds that you get this error. Can you post the code and which database you are trying to use...

[viju vi]

i found one. database using oracle 8i

first what you do is get the OracleConnection instead of jdbc.Connection object. next insert a empty blob object into database then select the blob object that was inserted previously by using

for update

clause because we do update when we are fetching it.
now read the file and input it into the blob object.

hope below code might help.

    
<table>
 create table blob_test(id integer, desc bolb);
</table>
 
OracleConnection con = null;
PreparedStatement ps = null;
OracleResultSet rs = null;
try{
    con = getOracleConnection();
    con.setAutoCommit(false);
    ps = con.prepareStatement("Insert into blob_test(id , desc) values(? , empty_blob())");
    ps.setInt(1,idcnt); // id for indexing 
    ps.executeUpdate();
    ps.close();
    ps = con.prepareStatement("Select desc from blob_test where id = ? for update"); // for update clause is important here.
    ps.setInt(1,idcnt); // retrive previously inserted element;
    rs = (oracleResultSet)ps.executeQuery();
    if(rs.next()){
       Oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBLOB(1);
       OutputStream outStrm = blob.getBinaryOutputStream();
       InputStream instrm = new FileInputStream(filename); // filename ur trying to upload
       byte[] bytes = new byte[blob.getBufferSize()];
       int byteread = 0;
       while ((byteread = instrm.read(bytes)) != -1) {// write to BLOB
   outstrm.write(bytes,0,byteread);
}
       outstrm.close();
       instrm.close();
    }
    con.commit();// commit the updation u made..
}catch(Exception e){e.printStackTrace();}

hope this code helps you

[Kajal Chakole]

it worked.. thanks viju vi.
i was also getting same exception in oracle10g with type 4 jdbc driver.
instead of overcoming the limitation of the existing driver by upgrading it, (cannot upload > 4k data) we can use this code with little modifications.

try 
			{
		    	        File myFile = new File(filePath);
				fis = new FileInputStream(myFile);
                                int len = (int)myFile.length();
				con = OraConnection.getConnection();
				con.setAutoCommit(false); 
				byte[] bytes = new byte[len];
		        int offset = 0;
		        int numRead = 0;
		        while (offset < bytes.length
		               && (numRead=fis.read(bytes, offset, bytes.length-offset)) >= 0) {
		            offset += numRead;
		        }
		        if (offset < bytes.length) {
		            throw new IOException("Could not completely read file "+myFile.getName());
		        }
				StringBuilder sql = new StringBuilder("Insert into attachment_upload values (?,?,empty_blob())");
				stmt = con.prepareStatement(sql.toString());
				stmt.setString(1, "abc.pdf");
				stmt.setString(2, "pdf");
				stmt.executeUpdate();
				stmt.close();
				// for update clause is important here.  
				stmt = con.prepareStatement("Select file_data from attachment_upload where file_type = ? for update");
				stmt.setString(1,"pdf"); // retrive previously inserted element;  
				rs = stmt.executeQuery();  
				if(rs.next()){  
					BLOB blob = (oracle.sql.BLOB)rs.getBlob(1);  
					OutputStream outStrm = blob.getBinaryOutputStream();  
					InputStream instrm = new FileInputStream(myFile); // filename you are trying to upload  
					outStrm.write(bytes);
					outStrm.close();  
				}
				con.commit();
			}
			catch (Exception exception) 
			{
				exception.printStackTrace();
			} 
			finally 
			{
				if( fis != null ) 
				{
					fis.close();
					fis = null;
				}
			}

[Martin Vajsar]

Welcome to the Ranch!

You can use the code tag to make your code much more readable. I've added it for you this time, so you can see how much better it looks

Kajal Chakole wrote:instead of overcoming the limitation of the existing driver by upgrading it...

Upgrading the driver should be the first option. Even the newest driver is compatible with older databases (all the way back to 9i) and contains some important optimizations. See also our Oracle FAQ.