Re: [jira] Commented: (DERBY-186) isFirst() returns true when relative(x) goes beyond result set

Please find the patch for this attached. I have added a test case for 
this as well.
I ran the derbyall test suite without any failures.

~ Shreyas

Shreyas Kaushik (JIRA) wrote:

>     [ http://issues.apache.org/jira/browse/DERBY-186?page=comments#action_61704 
> ]
>     
> Shreyas Kaushik commented on DERBY-186:
> ---------------------------------------
>
> I guess the problem is not whether you make a call to isFirst() first or next. The way a call to relative() is behaving when the param passed to it is more than the number of rows, is what is causing the problem. 
>
> I have small fix going for this which I am still testing. Will update when I 
> get some results.
>
> ~ Shreyas
>
>  
>
> > isFirst() returns true when relative(x) goes beyond result set
> > --------------------------------------------------------------
> >
> >         Key: DERBY-186
> >         URL: http://issues.apache.org/jira/browse/DERBY-186
> >     Project: Derby
> >        Type: Bug
> >  Components: JDBC
> >    Versions: 10.0.2.0
> > Environment: Windows XP SP1 Professional
> >    Reporter: George Baklarz
> >    
>
>  
>
> > Bizarre error. Not sure if this is a JDBC, Derby, or Java issue.
> > An opened result set has 4 records. A call to relative(3) while on row 3 should 
> > result in isAfterLast=true, and isFirst, isBeforeFirst, and IsLast set to 
> > false. However, the result is isAfterLast=True and isFirst=True.
> > ij 
> > connect 'IsAfter;create=true';
> > create table x (a char(1)); 
> > insert into x values '1','2','3','4';
> > quit;
> > import java.sql.*; 
> > public class ErrIsFirst {
> >  public static void main(String argv[]) throws SQLException {
> >    Connection conn = null;
> >    Statement s = null;
> >    ResultSet rs = null;
> >    String DerbyDriver = "org.apache.derby.jdbc.EmbeddedDriver";
> >    String returnValue;
> >      
> >    try {
> >       Class.forName(DerbyDriver).newInstance();
> >    }
> >    catch (Exception NoDriver) {
> >       System.out.println("Derby driver not found: " + DerbyDriver);
> >       NoDriver.printStackTrace();
> >       System.exit(1);
> >    }
> >    try {
> >      conn = DriverManager.getConnection("jdbc:derby:IsAfter"); 
> >      s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); 
> >      rs = s.executeQuery( "SELECT A FROM X");
> >      rs.next(); // First Record
> >      returnValue = rs.getString("A");     
> >      System.out.println("Value="+returnValue); 
> >      
> >      rs.relative(2);
> >      System.out.println("isFirst=" + rs.isFirst() + " isLast=" + rs.isLast() + " 
> > isAfterLast=" + rs.isAfterLast());
> >      returnValue = rs.getString("A");     
> >      System.out.println("Value="+returnValue); 
> >      rs.relative(-2);
> >      returnValue = rs.getString("A");     
> >      System.out.println("Value="+returnValue);
> >      rs.relative(10);
> >      System.out.println("isFirst=" + rs.isFirst() + " isLast=" + rs.isLast() + " 
> > isAfterLast=" + rs.isAfterLast());
> >      returnValue = rs.getString("A");     
> >      System.out.println("Value="+returnValue); 
> >      rs.close(); 
> >      s.close();
> >    }
> >    catch (SQLException se) {
> >      String SQLState = se.getSQLState(); 
> >      String SQLMessage = se.getMessage();
> >      System.out.println("Error = "+SQLState);
> >      System.out.println(SQLMessage);
> >    }
> >  }
> > }
> > The results on my system are:
> > Value=1
> > isFirst=false isLast=false isAfterLast=false
> > Value=3
> > Value=1
> > isFirst=true isLast=false isAfterLast=true
> > Error = 24000
> > Invalid cursor state - no current row.
> > If you eliminate the first println call to isFirst() you get the following 
> > (correct) results.
> > Value=1
> > Value=3
> > Value=1
> > isFirst=false isLast=false isAfterLast=true
> > Error = 24000
> > Invalid cursor state - no current row.
> > Okay, so where did we go wrong?
> >    
>
>  
Index: 
java/engine/org/apache/derby/impl/sql/execute/ScrollInsensitiveResultSet.java
===================================================================
--- 
java/engine/org/apache/derby/impl/sql/execute/ScrollInsensitiveResultSet.java   
    (revision 159352)
+++ 
java/engine/org/apache/derby/impl/sql/execute/ScrollInsensitiveResultSet.java   
    (working copy)
@@ -750,6 +750,7 @@
                        else
                        {
                                afterLast = true;
+                currentPosition = positionInSource + 1;
                        }
                }
 
Index: 
java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/testRelative.java
===================================================================
--- 
java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/testRelative.java
  (revision 0)
+++ 
java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/testRelative.java
  (revision 0)
@@ -0,0 +1,98 @@
+package org.apache.derbyTesting.functionTests.tests.jdbcapi;
+
+
+import java.sql.*;
+
+import org.apache.derby.tools.ij;
+import org.apache.derby.tools.JDBCDisplayUtil;
+
+public class testRelative {
+   public static void main(String[] args) {
+        test1(args);        
+    }
+    
+    public static void test1(String []args) {   
+                Connection con;
+                ResultSet rs;
+                PreparedStatement stmt = null;
+                PreparedStatement pStmt = null;
+                Statement stmt1 = null;
+                String returnValue = null;
+
+                System.out.println("Test testRelative starting");
+
+                try
+                {
+                        // use the ij utility to read the property file and
+                        // make the initial connection.
+                        ij.getPropertyArg(args);
+                        con = ij.startJBMS();
+                                       
+                       con.setAutoCommit(false);                               
                              
+                        
+                        stmt = con.prepareStatement("create table 
testRelative(name varchar(10), i int)");
+                       stmt.executeUpdate();
+                       con.commit();
+                       
+                       pStmt = con.prepareStatement("insert into testRelative 
values (?,?)");
+                                               
+                       pStmt.setString(1,"work1");
+                       pStmt.setNull(2,1);
+                       pStmt.addBatch();
+                       
+                       pStmt.setString(1,"work2");
+                       pStmt.setNull(2,2);
+                       pStmt.addBatch();
+                       
+                       pStmt.setString(1,"work3");
+                       pStmt.setNull(2,3);
+                       pStmt.addBatch();
+                       
+                       pStmt.setString(1,"work4");
+                       pStmt.setNull(2,4);
+                       pStmt.addBatch();
+
+               
+                       pStmt.executeBatch();
+                       con.commit();
+
+                       stmt1 = 
con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
+                       rs = stmt1.executeQuery("select * from testRelative");  
                                        
+
+                       rs.next(); // First Record
+                       returnValue = rs.getString("name");
+                       System.out.println("Value="+returnValue);
+
+                       rs.relative(2);
+                       System.out.println("isFirst=" + rs.isFirst() + " 
isLast=" + rs.isLast() + " isAfterLast=" + rs.isAfterLast());
+                       returnValue = rs.getString("name");
+                       System.out.println("Value="+returnValue);
+
+                       rs.relative(-2);
+                       returnValue = rs.getString("name");
+                       System.out.println("Value="+returnValue);
+
+                       rs.relative(10);
+                       System.out.println("isFirst=" + rs.isFirst() + " 
isLast=" + rs.isLast() + " isAfterLast=" + rs.isAfterLast());
+
+                       returnValue = rs.getString("name");
+                       System.out.println("Value="+returnValue);
+
+               } catch(SQLException sqle) {
+                  dumpSQLExceptions(sqle);
+                  sqle.printStackTrace();
+               } catch(Throwable e) {
+                  System.out.println("FAIL -- unexpected exception: "+e);
+                   e.printStackTrace();
+
+               }
+      }
+      
+      static private void dumpSQLExceptions (SQLException se) {
+                System.out.println("FAIL -- unexpected exception");
+                while (se != null) {
+                        System.out.println("SQLSTATE("+se.getSQLState()+"): 
"+se);
+                        se = se.getNextException();
+                }
+        }
+}
\ No newline at end of file
Index: 
java/testing/org/apache/derbyTesting/functionTests/master/testRelative.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/testRelative.out  
(revision 0)
+++ java/testing/org/apache/derbyTesting/functionTests/master/testRelative.out  
(revision 0)
@@ -0,0 +1,9 @@
+Test testRelative starting
+Value=work1
+isFirst=false isLast=false isAfterLast=false
+Value=work3
+Value=work1
+isFirst=false isLast=false isAfterLast=true
+FAIL -- unexpected exception
+SQLSTATE(24000): SQL Exception: Invalid cursor state - no current row.
+SQL Exception: Invalid cursor state - no current row.