How to create a scrollable ResultSet?

java sql jdbc sqlexception scrollable
Pascal Weber picture Pascal Weber · Nov 12, 2012 · Viewed 30.3k times · Source

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..)?

Answer

Mark Rotteveel picture Mark Rotteveel · Nov 12, 2012

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.

Related questions

How to get parameters from PreparedStatement?

I'm writing generic logger for SQLException and I'd like to get parameters that were passed into PreparedStatement, how to do it ? I was able to get the count of them. ParameterMetaData metaData = query.getParameterMetaData(); parameterCount = metaData.getParameterCount();

Read More
SQL ERROR: Closed Connection in java

Connection is closing automatically even if i'm not closing in finally block. public String look( long id, String codeName, Connection conn ) throws SQLException { try { StringBuffer sel = new StringBuffer().append(property); stmt = conn.prepareCall( sel.toString() ); /* fileCode.java:194 */ stmt.setString( 1, nameC ); …

Read More
How do I get the size of a java.sql.ResultSet?

Shouldn't this be a pretty straightforward operation? However, I see there's neither a size() nor length() method.

Read More