java.sql.SQLException: ORA-22275: 无效的 LOB 定位器指定

huangapple go评论87阅读模式
英文:

java.sql.SQLException: ORA-22275: invalid LOB locator specified

问题

I can help you with the translation. Here's the content you provided translated into Chinese:

如何更新数据库中的记录?

我使用的是Oracle数据库。我有一个包含列RQUID(VARCHAR)、STATUS(VARCHAR)和XMLDATA(CLOB)的表格。我想要通过RQUID来更新这个表中的记录。当我更新STATUS和XMLDATA时,一切都正常。但是当xmlData等于null时,我想要在表格中将XMLDATA设置为null,但是在这一行int rows = pstmt.executeUpdate();中,我收到以下消息:

[WARN] 2020-08-11 10:46:54,038 [pool-2-thread-1] com.example.dao.AbstractDAO closeCLob - java.sql.SQLException: ORA-22275: invalid LOB locator specified

并且我的程序会冻结。同时记录也没有被更新。您有没有任何建议,如何强制我的程序在两种情况下都更新记录:当xmlData="someData"以及当xmlData=null?

我的代码:

public boolean updateRecord(String rqUid, Integer code, String xmlData) throws MyException {
    if (rqUid != null) {
        String sql = "UPDATE MY_TABLE SET STATUS=?, XMLDATA=? WHERE RQUID=?";
        try (Connection con = pool.getConnection();
             PreparedStatement pstmt = con.prepareCall(sql)) {
            pstmt.setString(1, (code != null) ? code.toString() : null);
            pstmt.setString(2, xmlData);
            pstmt.setString(3, rqUid);
            int rows = pstmt.executeUpdate();
            return rows > 0;
        } catch (Exception e) {
            String errorMessage = "错误更新记录";
            LOGGER.info(errorMessage, e);
            throw new MyException(errorMessage + "", e);
        }
    } else {
        return false;
    }
}

感谢您的帮助!

英文:

How can I update records in my database?

I work with db in Oracle. I have the table with columns RQUID(VARCHAR), STATUS(VARCHAR) and XMLDATA(CLOB). I want to update records in this table by RQUID. When I update STATUS and XMLDATA, things are good. When xmlData=null, I want to set null to XMLDATA in table, but in the line int rows = pstmt.executeUpdate(); I get next message:

[WARN] 2020-08-11 10:46:54,038 [pool-2-thread-1] com.example.dao.AbstractDAO closeCLob - java.sql.SQLException: ORA-22275: invalid LOB locator specified

and my program freezes. Also the record is not updated. Can you have any suggestions, how to force my program to update records in both cases: when xmlData="someData" and when xmlData=null?

My code:

public boolean updateRecord(String rqUid, Integer code, String xmlData) throws MyException {
    if (rqUid != null) {
        String sql = "UPDATE MY_TABLE SET STATUS=?, XMLDATA=? WHERE RQUID=?";
        try (Connection con = pool.getConnection();
             PreparedStatement pstmt = con.prepareCall(sql)) {
            pstmt.setString(1, (code != null) ? code.toString() : null);
            pstmt.setString(2, xmlData);
            pstmt.setString(3, rqUid);
            int rows = pstmt.executeUpdate();
            return rows > 0;
        } catch (Exception e) {
            String errorMessage = "Ошибка обновления записи";
            LOGGER.info(errorMessage, e);
            throw new MyException(errorMessage + ".", e);
        }
    } else {
        return false;
    }
}

Thanks for any help!

答案1

得分: 1

你可以显式将其设置为null:

if (xmlData == null || xmlData.length() == 0) {
    pStmt.setNull(2, java.sql.Types.CLOB);
}
else {
    pstmt.setString(2, xmlData);
}
英文:

You could explicitly set it to null:

if (xmlData == null || xmlData.length() == 0) {
    pStmt.setNull(2, java.sql.Types.CLOB);
}
else {
    pstmt.setString(2, xmlData);
}

答案2

得分: 1

当字段 XMLDATA 为空时,应该像这样更新表格:

UPDATE MY_TABLE SET XMLDATA = EMPTY_CLOB();

我认为当 XMLDATA 为空时,你可以添加一个新的字符串 sql:

String sql = "UPDATE MY_TABLE SET STATUS=?, XMLDATA=empty_clob() WHERE RQUID=?";
英文:

When the field XMLDATA is null, you should update the table like this

UPDATE MY_TABLE  SET XMLDATA = EMPTY_CLOB();

I think you might add a new String sql when XMLDATA is null

String sql = "UPDATE MY_TABLE SET STATUS=?, XMLDATA=empty_clob() WHERE RQUID=?";

huangapple
  • 本文由 发表于 2020年8月11日 16:46:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/63354650.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定