Oracle/OJDBC BLOB 在不存在的行上更新问题？

发布于 2024-12-02 08:11:21 字数 2274 浏览 2 评论 0原文

我在 Oracle 中遇到了关于 BLOB 的非常特殊的问题。我正在使用 OracleXE 10g（10.2.0.1.0 版本的数据库），并尝试使用 ojdbc14_g 驱动程序版本 10.2.0.1.0、10.2.0.4.0 和 10.2.0.5.0。同样的事情总是发生。根据我到目前为止在各个论坛上读到的内容，我认为这与驱动程序有关，但我不确定......这就是问题：

我有这段代码，它准备更新两个 blob 的语句，其中实际上是 zip 存档：

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

我正在测试表中给定记录不存在的更新情况。如果这两个 zip 文件较小（如 2、5 或 10KB），则 line:

int no = pstmt.executeUpdate();

返回 0 行更新，考虑到 WHERE 子句中定义的行不存在，这是预期的。但是，如果 zip 文件稍大一点（30、40KB），executeUpdate() 会抛出 SQLException 并带有各种消息，例如：

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)

或

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

或

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

这些异常有效地终止了底层套接字连接，因此它变得不可用。有趣的是，如果表中存在行，则一切正常，更新执行没有问题，返回 1 作为更新的行数。

我想知道是否有人已经遇到过这种奇怪的行为，是否有任何绕过方法？（除了明显的一个 - 检查该行是否存在:)）

谢谢。

原文

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)

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

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.

分享到QQ

分享到微博