CODEWITHSUNDEEP
javajdbcresultset
Chris Hadfield
Chris Hadfield

Reputation: 524

The requested operation is not supported on forward only result sets

I am trying to show a data into a table but got some error there. The method I have used returns me all the related data but when I try to bind data into table it shows me error and error says:

The requested operation is not supported on forward only result sets.

This is how I implemented my code:

public class Table {

    public static DefaultTableModel buildDataTable(ResultSet rs) {
        ResultSetMetaData metaData;
        Object[][] data = null;
        Object[] columnNames = null;
        try {
            metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            columnNames = new Object[columnCount];
            int index = 0;
            for (int column = 1; column <= columnCount; column++) {
                columnNames[index] = metaData.getColumnName(column).toUpperCase();
                index++;
            }
            rs.last();
            data = new Object[rs.getRow()][columnCount];
            rs.beforeFirst();
            while (rs.next()) {
                for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
                    data[rs.getRow() - 1][columnIndex - 1] = rs.getObject(columnIndex);
                }
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }

        return new DefaultTableModel(data, columnNames);
    }

}

Following code shows how I have bound data into jtable:

try {
        dtm = com.gaurav.auctionhouse.common.Table.buildDataTable(new ItemDAOImp().getItemByUserId(MainClass.userId));
        jTable1.setModel(dtm);
    } catch (SQLException ex) {
        Logger.getLogger(SellerPage.class.getName()).log(Level.SEVERE, null, ex);
    }

The method to return a data in terms of ResultSet

public ResultSet getItemByUserId(int id) throws SQLException {
    String query = "SELECT id, type, description, state, reservedPrice, itemName, image  FROM tbl_item WHERE userId = ?";
    try {
        pst = DBConnection.getConnection().prepareStatement(query);
        pst.setInt(1, id);
        res = pst.executeQuery();

        return res;
    } catch (SQLException ex) {
        throw new SQLException(ex);
    } finally {
        DBConnection.getConnection().close();
    }
}

The full stacktrace

com.microsoft.sqlserver.jdbc.SQLServerException: The requested operation is not supported on forward only result sets. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.throwNotScrollable(SQLServerResultSet.java:414) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.verifyResultSetIsScrollable(SQLServerResultSet.java:437) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.last(SQLServerResultSet.java:1477) at com.gaurav.auctionhouse.common.Table.buildDataTable(Table.java:34) at com.gaurav.auctionhouse.view.SellerPage.(SellerPage.java:29) at com.gaurav.auctionhouse.view.AddItems.jbtnSubmitActionPerformed(AddItems.java:265) at com.gaurav.auctionhouse.view.AddItems.access$100(AddItems.java:33) at com.gaurav.auctionhouse.view.AddItems$2.actionPerformed(AddItems.java:111) at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022) at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348) at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402) at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)

Hoping for positive response.

Upvotes: 6

Views: 13313

Answers (2)

Alok mishra
Alok mishra

Reputation: 1

This is happened due to lower version of sql server if you are using JPA or hibernate or simple jdbc then change your driver if its lower than 2006 of mssql and if its above or equal to 2006 version then change your dialect in hbm configuration.

Upvotes: 0

SeverityOne
SeverityOne

Reputation: 2701

This:

    rs.last();
    data = new Object[rs.getRow()][columnCount];
    rs.beforeFirst();

isn't going to work with a forward-only ResultSet. Apart from that, fetching the last row and getting its number is not a very reliable way of determining the number of rows, quite apart from performance considerations.

Also, the JavaDoc on ResultSet states the following:

Note:Support for the getRow method is optional for ResultSets with a result set type of TYPE_FORWARD_ONLY

So that's not going to work either.

The root of your problem, however, is that you're using arrays. Arrays in Java are best avoided, because the Collection framework offers much more flexibility.

In your case, make data a List<Object[]>, and create a new Object[columnCount] for each row read from the database. Better yet, use a List for that as well.

In general, your code looks like old-fashioned C converted into Java: no final variables, declaring the variables at the beginning of the code, not using the Collections framework, etc. That is something that you may want to take a look at.

Edit: Adding the correct solution... sure, but you need to learn Java properly. You may get a solution now, but you'll still need to write code. The below still has issues, such as the exception handling, which also stops me from making all variables final, but you can deal with that yourself.

public class Table {

    public static DefaultTableModel buildDataTable(final ResultSet rs) {

        final List<Object[]> data = new ArrayList<>();
        String[] columnNames = null;
        int columnCount = 0;

        try {
            final ResultSetMetaData metaData = rs.getMetaData();
            columnCount = metaData.getColumnCount();
            columnNames = new String[columnCount];
            for (int column = 0; column < columnCount; column++) {
                columnNames[column] = metaData.getColumnName(column + 1).toUpperCase();
            }

            while (rs.next()) {
                final Object[] row = new Object[columnCount];
                for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) {
                    row[columnIndex] = rs.getObject(columnIndex + 1);
                }
                data.add(row);
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }

        return new DefaultTableModel(data.toArray(new Object[data.size()][columnCount]), columnNames);
    }
}

Upvotes: 6

Related Questions