sql:fetch-resultset fails on postgresql

[GoogleCodeExporter]

On postgresql, the default resultset type ResultSet.TYPE_FORWARD_ONLY does not 
allow for calling ResultSet.first() resulting in an exception "Operation 
requires a scrollable ResultSet, but this ResultSet is FORWARD_ONLY."

The current behavior of sql:fetch-resultset is such that it will reset to the 
first row if called, so that the full resultset is fetched even if some rows 
were previously fetched using sql:fetch-row.

Original issue reported on code.google.com by MHGRotte...@gmail.com on 8 May 2011 at 1:53

[GoogleCodeExporter]

Potential solutions:
1. Remove call to ResultSet.first()
2. Specify ResultSet.TYPE_SCROLL_INSENSITIVE

Option 1 would make fetch-resultset more consistent, as the current 
implementation behaves differently depending on if only some or all rows were 
fetched before (eg using sql:fetch-resultset or sql:fetch-row). But would 
require some additional checking (eg return empty list when calling 
sql:fetch-resultset if sql:fetch-row has already been called once)

Original comment by MHGRotte...@gmail.com on 8 May 2011 at 2:02

[GoogleCodeExporter]

Decided to change behavior so that user needs to decide to either only use 
sql:fetch-row or only sql:fetch-resultset and not both.

Now if sql:fetch-row is called first, followed by sql:fetch-resultset, then 
sql:fetch-resultset will return an empty list even if there are more rows 
available.

Original comment by MHGRotte...@gmail.com on 8 May 2011 at 3:08

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Fixed in r16.

Next step: document behavior explicitly in user doc.

As an addendum to the previous comment:
Assuming a resultset with 2 or more rows, the following combinations of 
statements will result in:
Combination 1:
sql:fetch-row => returns 1 row (first row)
sql:fetch-resultset => returns empty list
sql:fetch-row => returns 1 row (second row)

Combination 2:
sql:fetch-resultset => returns all rows
sql:fetch-row => returns empty list

Combination 3:
sql:fetch-resultset => returns all rows
sql:fetch-resultset => returns empty list

Original comment by MHGRotte...@gmail.com on 8 May 2011 at 3:35

[GoogleCodeExporter]

Updated documentation for this behavior (not yet committed)

Original comment by MHGRotte...@gmail.com on 9 Jul 2011 at 12:10

• Changed state: Fixed

[GoogleCodeExporter]

Original comment by MHGRotte...@gmail.com on 9 Jul 2011 at 1:56

• Changed state: Verified