How to create a scrollable ResultSet?

Question

I got this simple code to retrieve a recordset from a MSSQL Server 2008 which has to be scrollable due to the fact that I set the ResultSet.TYPE_SCROLL_INSENSITVE, same as the example from the Javadocs:

String qry = "SELECT * from tblPeople";
SQLConnection sql = new SQLConnection();
Statement stmt = sql.getConnection().createStatement(
                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(qry);

Unfortunately I still got this Stack Trace when I want to get the row count like rs.last(); int rowCount = rs.getRow();:

java.sql.SQLException: ResultSet may only be accessed in a forward direction.
    at net.sourceforge.jtds.jdbc.JtdsResultSet.checkScrollable(JtdsResultSet.java:304)
    at net.sourceforge.jtds.jdbc.JtdsResultSet.last(JtdsResultSet.java:551)
    at test.personen.Main.main(Main.java:44)

Why is that and how can I fix it (by the way, when I check the Type of the ResultSet, I get 1003..)?

score 5 · Accepted Answer · answered Nov 12 '12 at 12:40

5

Most likely the combination of TYPE_SCROLL_INSENSITIVE is not compatible with CONCUR_UPDATABLE. According to the JDBC specification, a driver is free to downgrade a ResultSet if it can't service the requested scrollability and/or concurrency mode. See also: http://jtds.sourceforge.net/resultSets.html :

TYPE_SCROLL_INSENSITIVE | Static cursor | Heavy | Only works with read-only concurrency (updatable is downgraded). SQL Server generates a temporary table, so changes made by others are not visible. Scrollable.

Which confirms that the driver will downgrade when specifying CONCUR_UPDATABLE.

You might want to consider using TYPE_SCROLL_SENSITIVE or simply not combine scrollability with updatability.

answered Nov 12 '12 at 12:40

Mark Rotteveel

100,966
191
140
197

too bad it's not possible to have it updatable and scrollable at the same time.. but thanks, that'll help! :) – Pascal Weber Nov 12 '12 at 12:47
It looks like the `TYPE_SCROLL_SENSITIVE` might be updatable. You could also check if the Microsoft driver does provide this feature. – Mark Rotteveel Nov 12 '12 at 12:49
Looking at http://msdn.microsoft.com/en-us/library/ms378709.aspx the Microsoft driver has the same limitation – Mark Rotteveel Nov 12 '12 at 12:54
you guys are fast.. I couldn't even check. thank you :) I have only problem now: I created a [ResultSetTableModel](http://pastebin.com/QG8UGi9Z) and a [CompanyTable](http://pastebin.com/1VeiyJbJ). When I try to display it on e.g. a JOptionPane, I get an `ArrayIndexOutOfBoundsException` in the Vector class. Any idea? – Pascal Weber Nov 12 '12 at 13:09

score 1 · Answer 2 · answered Nov 12 '12 at 12:43

1

jTDS TYPE_SCROLL_INSENSITIVE only supports the read-only operations.

Change ResultSet.TYPE_SCROLL_INSENSITIVE to ResultSet.TYPE_SCROLL_SENSITIVE

See http://www.anyang-window.com.cn/jtds-on-the-database-connection-resultset-is-read-only/

answered Nov 12 '12 at 12:43

Robertiano

352
1
6

How to create a scrollable ResultSet?

2 Answers2

Linked