ResultSet with ownInsertsAreVisible can't read row after insert

[prrvchr]

Hi,

I am trying to implement in LibreOffice Base ResultSet that support the visibility of inserts. During this implementation I realized that it was not possible to reread a new inserted row (in fact we get the last row before the insert)

Here is the code to reproduce the error:

    public void testInsertVisibility(java.sql.Connection connection) {
        String select = "select * from t1";
        int rstype = java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE;

        try {
            populateTable(connection);

            java.sql.PreparedStatement ps = connection.prepareStatement(select,
                                                                        rstype,
                                                                        java.sql.ResultSet.CONCUR_UPDATABLE);

            java.sql.ResultSet rs = ps.executeQuery();

            boolean visible = connection.getMetaData().ownInsertsAreVisible(rstype);
            System.out.println("testInsertVisibility() ResultSet Inserts are Visible: " + visible);

            if (rs.last()) {
                System.out.println("testInsertVisibility() Before insert last row is: " + rs.getRow());
            }
            rs.moveToInsertRow();
            rs.updateInt(1, 10);
            rs.updateString(2, "s10");
            rs.insertRow();
            rs.moveToCurrentRow();
            if (rs.last()) {
                System.out.println("testInsertVisibility() After insert last row is: " + rs.getRow());
                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    Object value = rs.getObject(i);
                    if (rs.wasNull()) {
                        System.out.println("testInsertVisibility() Column Index: " + i + " Value was Null");
                    }
                    else {
                        System.out.println("testInsertVisibility() Column Index: " + i + " Value: " + value.toString());
                    }
                }
            }

        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    void populateTable(java.sql.Connection connection) throws SQLException {
        java.sql.Statement statement = connection.createStatement();
        statement.execute("recreate table t1 (i int primary key, v varchar(10))");
        statement.close();

        String insert = "insert into t1 values(?, ?)";
        java.sql.PreparedStatement ps = connection.prepareStatement(insert);

        for (int i = 1; i < 10; i++) {
            ps.setInt(1, i);
            ps.setString(2, "s" + String.valueOf(i));
            ps.execute();
        }

        ps.close();
    }

[mrotteveel]

You're assuming the inserted row is last, but that is not necessarily the case. When emulated scrollable cursors are used (Firebird 4.0 and older, or Firebird 5.0 with scrollableCursor=EMULATED, the row is inserted on the current position of the result set. Only with server-side scrollable cursors (Firebird 5.0 or higher) are rows inserted at the end.

In other words, in your case, the inserted row is row 9, not row 10.

[mrotteveel]

That said, I do see some oddness in the server-side scrollable case where it reports one row more than it has when I didn't call last() before the insert.

[mrotteveel]

I have plans to retrofit the behaviour for server-side scrollable cursors to emulated cursors as well (so they are inserted at the end), but that will land in Jaybird 6 at the earliest. See also the Jaybird 5 release notes regarding scrollable cursor supports.

[prrvchr]

Hi Mark,

You're assuming the inserted row is last, but that is not necessarily the case.

actually I didn't really know.

In fact LibreOffice Base considers that an inserted row is inserted after the last row, moreover it positions itself on the last row before the insertion, but I don't think I have the choice to modify this. I'll find out.

Until now I managed the insertions as not visible so I had not yet put my finger on this Base problem...

[mrotteveel]

The problem here is that JDBC does not explicitly define where the newly inserted row should be put. Personally, I do think that it should be at the end, but the original implementation in Jaybird didn't do that. When I implemented server-side scrollable cursor support (which is currently opt-in, and only supported by Firebird 5 and higher), I corrected that, but I didn't want to do that yet for the original implementation.

That behaviour will change in Jaybird 6 or later.

[prrvchr]

Personally, I do think that it should be at the end

If this is what will be implemented in version 6 then I will make the inserts visible at that time.

Thanks for your explanations which once again were a great help to me.

[mrotteveel]

Thanks for taking the time to report the issue. I will close this ticket. You can track the change of the behaviour through #821.