db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Resetting ResultSet with first() or beforeFirst()
Date Wed, 09 Jun 2010 22:21:30 GMT
On 06/ 9/10 07:32 PM, Pavel Bortnovskiy wrote:
>
> Hi:
>
> I am trying to reset a given ResultSet back to the first record, so
> that I could iterate through its records one more time, after it's
> already been done once. So, my code does something like this (it's
> pseudo-code, and in the real application these two passes are done in
> different parts of the application):
>
> // get ResultSet
> final ResultSet resultSet = ((PreparedStatement)
> m_statement).executeQuery();
> final int columnCount = resultSet.getMetaData().getColumnCount();
> // First Pass through the data
> while (resultSet.next()) {
>         for (int i = 1; i <= columnCount; i++) {
>                 if (resultSet.wasNull()) {
>                         doSomethingWithNULL();
>                 } else {
>                         doSomethingWithObject(resultSet.getObject(i));
>                 }
>         }
> }
> // Reset ResultSet - but it generates SQLException: The
> 'beforeFirst()' method is only allowed on scroll cursors
> resultSet.beforeFirst();
> // Second Pass through the data
> while (resultSet.next()) {
>         for (int i = 1; i <= columnCount; i++) {
>                 if (resultSet.wasNull()) {
>                         doSomethingElseWithNULL();
>                 } else {
>                        
> doSomethingElseWithObject(resultSet.getObject(i));
>                 }
>         }
> }
>
>
> However, resultSet.beforeFirst() generates an exception:
>
> java.sql.SQLException: The 'beforeFirst()' method is only allowed on
> scroll cursors.
>         at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
> Source)
>         at
> org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
>         at
> org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
>         at
> org.apache.derby.impl.jdbc.EmbedResultSet.checkScrollCursor(Unknown
> Source)
>         at
> org.apache.derby.impl.jdbc.EmbedResultSet.beforeFirst(Unknown Source)
> Caused by: java.sql.SQLException: The 'beforeFirst()' method is only
> allowed on scroll cursors.
>         at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
> Source)
>         at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
> Source)
>         ... 8 more
>
> Could someone please help me out to understand what may be done
> incorrectly and what would be the right approach to enable two-pass
> logic through the RecordSet?

You need to specify that the result should be scrollable when you
prepare the statement. Something like this:

PreparedStatement ps = conn.prepareStatement(
        sql, |ResultSet.TYPE_SCROLL_INSENSITIVE, ||ResultSet.CONCUR_READ_ONLY);|


-- 
Knut Anders


Mime
View raw message