CODEWITHSUNDEEP
javaoracle-databaseblobprepared-statement
Less
Less

Reputation: 3207

Oracle/OJDBC BLOB update problem on non-existing rows?

I ran into very peculiar problem with BLOBs in Oracle. I'm using OracleXE 10g (10.2.0.1.0 version of database), and tried it with ojdbc14_g drivers version 10.2.0.1.0, 10.2.0.4.0 and 10.2.0.5.0. The same thing happens always. I think it's something with the drivers based on what I read so far on the various forums, but I'm not sure... And this is the problem:

I have this piece of code, that prepares statement to update two blobs, which are actually zip archives:

File fRst = new File("archive1.zip");
File fPro = new File("archive2.zip");
//...
statement = "UPDATE CURR_STATE" + 
                    " SET ZIP_RST=?, ZIP_PRO=?" +
                    " WHERE SERIAL_NUMBER=" + "'" + serialNo + "'" + " AND" +
                    " YEAR_MONTH=" + "'" + yearMonth + "'";
pstmt = this.connection.prepareStatement(statement);
FileInputStream isR = new FileInputStream(fRst);
FileInputStream isP = new FileInputStream(fPro);
pstmt.setBinaryStream(1, isR, (int) fRst.length());
pstmt.setBinaryStream(2, isP, (int) fPro.length());

int no = pstmt.executeUpdate();
System.out.println("rows: " + no);
this.connection.commit();
pstmt.close();

I was testing the case of update where given record does not exist in the table. if these two zip files are smaller in size (like 2, 5 or 10KB), line:

int no = pstmt.executeUpdate();

returns 0 rows updated, which is expected considering that row defined in WHERE clause does not exist. However, if zip files are a bit bigger (30, 40KB), executeUpdate() throws SQLException with various messages, like:

java.sql.SQLException: Io exception: Software caused connection abort: socket write error
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:190)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:363)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1142)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1278)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3498)

or

java.sql.SQLException: No more data to read from socket

or

java.sql.SQLException: OALL8 is in an inconsistent state

These exceptions effectively kill the underlying socket connection, so it becomes unusable. Funny thing is, if the row exists in the table, everything works fine, update executes with no problem, returning 1 as number of updated rows.

I wonder if anybody has already encountered this strange behaviour, and if there's any bypass for this? (except the obvious one - to check if the row exists :) )

Thanks.

Upvotes: 0

Views: 2073

Answers (1)

Matthew Watson
Matthew Watson

Reputation: 14233

java.sql.SQLException: No more data to read from socket

Whenever I've seen this, its because the oracle server you are connected to has crashed ( not the instance ).

Have you checked your alert log while this is happening?

Upvotes: 3

Related Questions