Re: patch: enforce the requirements for scrollable resultsets - Mailing list pgsql-jdbc

Kris Jurka wrote:

> I believe this still doesn't complete our checking for cursor based
> ResultSets because of methods like isBeforeFirst() or isLast(), which
> don't require scrollable ResultSets, but the code must be aware if it
> is working with a cursor.

We do indeed have some work to do here. The attached patch adds some
tests for these cases; they fail with a non-zero fetchsize.

-O
Index: org/postgresql/test/jdbc2/CursorFetchTest.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/jdbc2/CursorFetchTest.java,v
retrieving revision 1.5
diff -c -r1.5 CursorFetchTest.java
*** org/postgresql/test/jdbc2/CursorFetchTest.java    15 Jan 2004 08:50:40 -0000    1.5
--- org/postgresql/test/jdbc2/CursorFetchTest.java    6 Apr 2004 01:00:42 -0000
***************
*** 247,252 ****
--- 247,342 ----
          assertEquals(100, count);
      }

+     public void testSingleRowResultPositioning() throws Exception
+     {
+         String msg;
+         createRows(1);
+
+         int[] sizes = { 0, 1, 10 };
+         for (int i = 0; i < sizes.length; ++i) {
+             Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+             stmt.setFetchSize(sizes[i]);
+
+             // Create a one row result set.
+             ResultSet rs = stmt.executeQuery("select * from test_fetch order by value");
+
+             msg = "before-first row positioning error with fetchsize=" + sizes[i];
+             assertTrue(msg, rs.isBeforeFirst());
+             assertTrue(msg, !rs.isAfterLast());
+             assertTrue(msg, !rs.isFirst());
+             assertTrue(msg, !rs.isLast());
+
+             msg = "row 1 positioning error with fetchsize=" + sizes[i];
+             assertTrue(msg, rs.next());
+
+             assertTrue(msg, !rs.isBeforeFirst());
+             assertTrue(msg, !rs.isAfterLast());
+             assertTrue(msg, rs.isFirst());
+             assertTrue(msg, rs.isLast());
+             assertEquals(msg, 0, rs.getInt(1));
+
+             msg = "after-last row positioning error with fetchsize=" + sizes[i];
+             assertTrue(msg, !rs.next());
+
+             assertTrue(msg, !rs.isBeforeFirst());
+             assertTrue(msg, rs.isAfterLast());
+             assertTrue(msg, !rs.isFirst());
+             assertTrue(msg, !rs.isLast());
+
+             rs.close();
+             stmt.close();
+         }
+     }
+
+     public void testMultiRowResultPositioning() throws Exception
+     {
+         String msg;
+
+         createRows(100);
+
+         int[] sizes = { 0, 1, 10, 100 };
+         for (int i = 0; i < sizes.length; ++i) {
+             Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+             stmt.setFetchSize(sizes[i]);
+
+             ResultSet rs = stmt.executeQuery("select * from test_fetch order by value");
+             msg = "before-first row positioning error with fetchsize=" + sizes[i];
+             assertTrue(msg, rs.isBeforeFirst());
+             assertTrue(msg, !rs.isAfterLast());
+             assertTrue(msg, !rs.isFirst());
+             assertTrue(msg, !rs.isLast());
+
+             for (int j = 0; j < 100; ++j) {
+                 msg = "row " + j + " positioning error with fetchsize=" + sizes[i];
+                 assertTrue(msg, rs.next());
+                 assertEquals(msg, j, rs.getInt(1));
+
+                 assertTrue(msg, !rs.isBeforeFirst());
+                 assertTrue(msg, !rs.isAfterLast());
+                 if (j == 0)
+                     assertTrue(msg, rs.isFirst());
+                 else
+                     assertTrue(msg, !rs.isFirst());
+
+                 if (j == 99)
+                     assertTrue(msg, rs.isLast());
+                 else
+                     assertTrue(msg, !rs.isLast());
+             }
+
+             msg = "after-last row positioning error with fetchsize=" + sizes[i];
+             assertTrue(msg, !rs.next());
+
+             assertTrue(msg, !rs.isBeforeFirst());
+             assertTrue(msg, rs.isAfterLast());
+             assertTrue(msg, !rs.isFirst());
+             assertTrue(msg, !rs.isLast());
+
+             rs.close();
+             stmt.close();
+         }
+     }
+
      // Test odd queries that should not be transformed into cursor-based fetches.
      public void testInsert() throws Exception
      {
Index: org/postgresql/test/jdbc2/ResultSetTest.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/jdbc2/ResultSetTest.java,v
retrieving revision 1.16
diff -c -r1.16 ResultSetTest.java
*** org/postgresql/test/jdbc2/ResultSetTest.java    2 Apr 2004 06:50:24 -0000    1.16
--- org/postgresql/test/jdbc2/ResultSetTest.java    6 Apr 2004 01:00:44 -0000
***************
*** 333,346 ****
--- 333,386 ----
          Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
          // Create a one row result set.
          ResultSet rs = stmt.executeQuery("SELECT * FROM pg_database WHERE datname='template1'");
+
          assertTrue(rs.isBeforeFirst());
+         assertTrue(!rs.isAfterLast());
+         assertTrue(!rs.isFirst());
+         assertTrue(!rs.isLast());
+
          assertTrue(rs.next());
+
+         assertTrue(!rs.isBeforeFirst());
+         assertTrue(!rs.isAfterLast());
          assertTrue(rs.isFirst());
          assertTrue(rs.isLast());
+
          assertTrue(!rs.next());
+
+         assertTrue(!rs.isBeforeFirst());
          assertTrue(rs.isAfterLast());
+         assertTrue(!rs.isFirst());
+         assertTrue(!rs.isLast());
+
          assertTrue(rs.previous());
+
+         assertTrue(!rs.isBeforeFirst());
+         assertTrue(!rs.isAfterLast());
+         assertTrue(rs.isFirst());
+         assertTrue(rs.isLast());
+
          assertTrue(rs.absolute(1));
+
+         assertTrue(!rs.isBeforeFirst());
+         assertTrue(!rs.isAfterLast());
+         assertTrue(rs.isFirst());
+         assertTrue(rs.isLast());
+
+         assertTrue(!rs.absolute(0));
+
+         assertTrue(rs.isBeforeFirst());
+         assertTrue(!rs.isAfterLast());
+         assertTrue(!rs.isFirst());
+         assertTrue(!rs.isLast());
+
+         assertTrue(!rs.absolute(2));
+
+         assertTrue(!rs.isBeforeFirst());
+         assertTrue(rs.isAfterLast());
+         assertTrue(!rs.isFirst());
+         assertTrue(!rs.isLast());
+
          rs.close();
          stmt.close();
      }