getGeneratedKeys returns an empty ResultSet

My Java application (a Stateless EJB) is connecting to an Informix database via JDBC. In one sentence: Statement.getGeneratedKeys() is occasionally returning an empty ResultSet, and I'm trying to figure out why. 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 (I believe), 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. I'm hoping someone can help explain. (I previously posted this question on Stack Overflow, with no answers. It was suggested that here may be a better place. Here is a link to my SO question: http://stackoverflow.com/questions/32447602/getgeneratedkeys.) Thank you. -- Menachem Salomon

Hi! Your are not stating which version you are using for the server and JDBC, but APAR IC98247 might be an answer to the problem: When inserting into a table with returnGeneratedKey enabled from JDBC driver, sometimes the server sends insert done back to client without SGK tuple. In result, JDBC application cannot get the inserted key by calling preparedStatement.getGeneratedKeys(). The problem occurs randomly. Regards Ulf