getGeneratedKeys返回一个空的ResultSet [英] getGeneratedKeys returns an empty ResultSet

问题描述

在一句话中:Statement.getGeneratedKeys()偶尔返回一个空的ResultSet，而我试图找出原因.

In one sentence: Statement.getGeneratedKeys() is occasionally returning an empty ResultSet, and I'm trying to figure out why.

背景:我一直在跟踪用户有关订单及其某些物品的请求.我有两个表，分别称为Header和Detail.为了正确链接标题和详细信息，Header有一个名为request_id的SERIAL列，当在Detail中插入行时会用到.长期以来，这一直很好(据我所知)，但是最近我注意到有时，getGeneratedKeys()返回的ResultSet为空.

Background: I am keeping track of users' requests regarding orders and some of their items. I have two tables, call them Header and Detail. To link the header and details properly, Header has a SERIAL column named request_id, which I use when inserting rows into Detail. This has been working fine for a long time (as far as I know), but recently I noticed that sometimes, the ResultSet returned by getGeneratedKeys() is empty.

为澄清起见，下面是代码(标识为安全起见已删除的详细信息):

To clarify, here is the code (identifying details removed for security):

String hdrSql = "insert into Header "
    + "(request_id, order_id, user_id) values (0, ?, ?)";
String dtlSql = "insert into Detail "
    + "(request_id, order_id, line_no, comment) values (?, ?, ?, ?)";
PreparedStatement header = null, details = null;
int rows = 0;
long orderId = request.getOrderId();    // request is an object parameter
try {
    header = connection.prepareStatement(hdrSql, Statement.RETURN_GENERATED_KEYS);
    header.setAutoCommit(false);
    header.setLong(1, orderID);
    header.setString(2, request.getUserId());
    rows = header.executeUpdate();
    ResultSet rs = header.getGeneratedKeys();
    if (!rs.next()) {
        log("Could not get next request_id");   // This statement logged.
        return false;                       // But no exception thrown.
    }
    int requestId = rs.getInt("request_id");

    details = connection.prepareStatement(dtlSql);
    details.setInt(1, requestId);
    details.setLong(2, orderId);
    for (Item item : request.getItems()) {
        details.setInt(3, item.getLineNumber());
        details.setString(4, item.getComment());
        rows += details.executeUpdate();
    }
    // Should have inserted 1 row for header, plus 1 row for each item
    return rowsInserted == request.getItems().size() + 1;
} catch (SQLException sqle) {
    log("Database error" + sqle.getMessage());
    throw sqle;
} finally {
    try {       // Commit or roll back, depending on status:
        if (rows == request.getItems().size() + 1) {
            header.commit();
        } else {
            header.rollback();
            log("Rolled back request");     // This gets logged, too.
        }
        if (header != null) header.close();
        if (details != null) details.close();
    } catch (SQLException sqle) { sqle.printStackTrace(); }

再次，可以正常工作一年以上，但是我发现-在同一天-取得了一些成功，但大多数都是失败.第二天再次工作，一次都没有失败，也没有异常.

Again, been working fine for more than a year, but I found - on the same day - some successes but mostly failures. Worked again the next day, with nary a single failure, and no exceptions at all.

我希望有人可以帮助解释.如果有什么不同，我正在通过JDBC与Informix数据库进行交谈.

I'm hoping someone can help explain. If it makes any difference, I'm talking to an Informix database over JDBC.

推荐答案

我将此问题发布到 IIUG ，然后我收到的答复指出了Informix(APAR IC98247)中的一个错误，该错误的版本比我使用的版本晚.

I posted this question to IIUG, and the response I got there pointed to a bug in Informix (APAR IC98247), fixed in a version later than the one I am using.

在一个困境中，一个朋友推荐的我现在尝试的解决方案是从刚刚插入的行中读取request_id，其中该行是由-p的变体形式插入的.

The solution I'm trying now, recommended by a friend in the same predicament, is to read the request_id from the row just inserted by variation of

SELECT max(request_id) from Header where order_id = ${orderId} ;
// Avoid race condition, accidentally reading the last row inserted by
//  another thread.

这似乎对他有用.

这篇关于getGeneratedKeys返回一个空的ResultSet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！