db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmars...@apache.org
Subject svn commit: r529795 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/ suites/ tests/lang/
Date Tue, 17 Apr 2007 22:31:18 GMT
Author: kmarsden
Date: Tue Apr 17 15:31:16 2007
New Revision: 529795

URL: http://svn.apache.org/viewvc?view=rev&rev=529795
Log:
DERBY-2561 convert simpleScroll.sql to JUnit
Rename ScrollCursors1.java to ScrollCursors1Test.java
incorporate simpleScroll.sql in to ScrollCursors1Test.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ScrollCursors1Test.java
  (with props)
Removed:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/simpleScroll.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ScrollCursors1.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/simpleScroll.sql
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/j9derbynetmats.runall
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall?view=diff&rev=529795&r1=529794&r2=529795
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall
Tue Apr 17 15:31:16 2007
@@ -19,7 +19,6 @@
 lang/bug5054.java
 lang/forupdate.sql
 lang/holdCursorIJ.sql
-lang/simpleScroll.sql
 lang/wisconsin.java
 lang/ShutdownDatabase.java
 store/holdCursorExternalSortJDBC30.sql

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/j9derbynetmats.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/j9derbynetmats.runall?view=diff&rev=529795&r1=529794&r2=529795
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/j9derbynetmats.runall
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/j9derbynetmats.runall
Tue Apr 17 15:31:16 2007
@@ -13,7 +13,6 @@
 lang/bug5054.java
 lang/forupdate.sql
 lang/holdCursorIJ.sql
-lang/simpleScroll.sql
 store/holdCursorExternalSortJDBC30.sql
 store/holdCursorJDBC30.sql
 tools/ieptests.sql

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ScrollCursors1Test.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ScrollCursors1Test.java?view=auto&rev=529795
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ScrollCursors1Test.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ScrollCursors1Test.java
Tue Apr 17 15:31:16 2007
@@ -0,0 +1,741 @@
+/**
+ *  Derby - Class org.apache.derbyTesting.functionTests.tests.lang.ScrollCursors1est
+ *  
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.CallableStatement;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+import java.sql.Connection;
+
+public class ScrollCursors1Test extends BaseJDBCTestCase {
+
+    public ScrollCursors1Test(String name) {
+        super(name);
+
+    }
+
+    public void testForwardOnlyNegative() throws SQLException {
+        Connection conn = getConnection();
+        PreparedStatement ps_c1 = conn.prepareStatement("select i from t1");
+        ResultSet rs = ps_c1.executeQuery();
+        try {
+            rs.getRow();
+            // client and embedded differ on getRow().
+            // spec says getRow is optional for forward only cursors.
+            if (usingEmbedded())
+                fail("getRow succeeded on forward only cursor");
+        } catch (SQLException se) {
+            assertSQLState("XJ061",se);
+            
+        }
+        try {
+            rs.first();
+            fail("first() not allowed on forward only result set");
+        } catch(SQLException se) {
+            if (usingEmbedded())
+                assertSQLState("XJ061",se);
+            else
+                assertSQLState("XJ125",se);
+        }
+        try {
+            rs.last();
+            fail("last() not allowed on forward only result set");
+        } catch(SQLException se) {
+            if (usingEmbedded())
+                assertSQLState("XJ061",se);
+            else
+                assertSQLState("XJ125",se);
+        }
+        try {
+            rs.previous();
+            fail("previous() not allowed on forward only result set");
+        } catch(SQLException se) {
+            if (usingEmbedded())
+                assertSQLState("XJ061",se);
+            else
+                assertSQLState("XJ125",se);
+        }
+        try {
+            rs.beforeFirst();
+            fail("beforeFirst() not allowed on forward only result set");
+        } catch(SQLException se) {
+            if (usingEmbedded())
+                assertSQLState("XJ061",se);
+            else
+                assertSQLState("XJ125",se);
+        }
+        try {
+            rs.afterLast();
+            fail("afterLast() not allowed on forward only result set");
+        } catch(SQLException se) {
+            if (usingEmbedded())
+                assertSQLState("XJ061",se);
+            else
+                assertSQLState("XJ125",se);
+        }
+        try {
+            rs.absolute(1);
+            fail("absolute() not allowed on forward only result set");
+        } catch(SQLException se) {
+            if (usingEmbedded())
+                assertSQLState("XJ061",se);
+            else
+                assertSQLState("XJ125",se);
+        }
+        try {
+            rs.relative(1);
+            fail("relative() not allowed on forward only result set");
+        } catch(SQLException se) {
+            if (usingEmbedded())
+                assertSQLState("XJ061",se);
+            else
+                assertSQLState("XJ125",se);
+        }
+        rs.close();
+        ps_c1.close();
+    }
+
+        public void testScrollInsensitive() throws SQLException {
+            Connection conn = getConnection();
+            conn.setAutoCommit(false);
+            PreparedStatement ps_c1 = conn.prepareStatement("select * from t1",
+                        ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY );
+            ResultSet rs = ps_c1.executeQuery();
+            rs.absolute(0);
+            assertNoCurrentRow(rs);
+            rs.close();
+            
+            rs = ps_c1.executeQuery();
+            rs.relative(0);
+            assertNoCurrentRow(rs);
+            rs.close();
+            rs = ps_c1.executeQuery();
+            
+            rs.relative(2);
+            assertEquals("c",rs.getString(1).trim());
+            assertEquals(3, rs.getInt(2));
+            rs.close();
+            rs = ps_c1.executeQuery();
+            rs.first();
+            assertEquals("b", rs.getString(1).trim());
+            assertEquals(2, rs.getInt(2));
+            assertEquals(1, rs.getRow());
+            rs.next();
+            assertEquals("c",rs.getString(1).trim());
+            assertEquals(3, rs.getInt(2));
+            assertEquals(2,rs.getRow());
+            assertEquals(2,rs.getRow());
+            rs.first();
+            assertEquals("b", rs.getString(1).trim());
+            assertEquals(2, rs.getInt(2));
+            assertEquals(1, rs.getRow());
+            
+            rs.next();
+            assertEquals("c",rs.getString(1).trim());
+            assertEquals(3, rs.getInt(2));
+            assertEquals(2,rs.getRow());
+            
+            rs.next();
+            assertEquals("d",rs.getString(1).trim());
+            assertEquals(4, rs.getInt(2));
+            assertEquals(3,rs.getRow());
+            
+            rs.first();
+            assertEquals("b", rs.getString(1).trim());
+            assertEquals(2, rs.getInt(2));
+            assertEquals(1, rs.getRow());
+            
+            rs.next();
+            assertEquals("c",rs.getString(1).trim());
+            assertEquals(3, rs.getInt(2));
+            assertEquals(2,rs.getRow());
+            
+            rs.afterLast();
+            assertNoCurrentRow(rs);
+            
+            assertEquals(0,rs.getRow());
+            assertFalse(rs.next());
+            assertNoCurrentRow(rs);
+            
+            assertEquals(0,rs.getRow());
+            
+            rs.previous();
+            assertEquals("m",rs.getString(1).trim());
+            assertEquals(13, rs.getInt(2));
+            assertEquals(12, rs.getRow());
+            rs.previous();
+            assertEquals("l",rs.getString(1).trim());
+            assertEquals(12, rs.getInt(2));
+            assertEquals(11, rs.getRow());
+            
+            rs.last();
+            assertEquals("m",rs.getString(1).trim());
+            assertEquals(13, rs.getInt(2));
+            assertEquals(12, rs.getRow());
+            rs.beforeFirst();
+            assertNoCurrentRow(rs);
+            assertEquals(0,rs.getRow());
+            rs.next();
+            assertEquals("b", rs.getString(1).trim());
+            assertEquals(2, rs.getInt(2));
+            assertEquals(1, rs.getRow());
+            
+            rs.absolute(12);
+            assertEquals("m",rs.getString(1).trim());
+            assertEquals(13, rs.getInt(2));
+            assertEquals(12, rs.getRow());
+            
+            rs.absolute(-11);
+            assertEquals("c",rs.getString(1).trim());
+            assertEquals(3, rs.getInt(2));
+            assertEquals(2,rs.getRow());
+            
+            rs.absolute(13);
+            assertNoCurrentRow(rs);
+            assertEquals(0,rs.getRow());
+            
+            
+            rs.absolute(-1);
+            assertEquals("m",rs.getString(1).trim());
+            assertEquals(13, rs.getInt(2));
+            assertEquals(12, rs.getRow());
+            rs.close();
+   
+            rs = ps_c1.executeQuery();
+            // do last first
+            rs.last();
+            assertEquals("m",rs.getString(1).trim());
+            assertEquals(13, rs.getInt(2));
+            assertEquals(12, rs.getRow());
+            
+            assertFalse(rs.next());
+            assertNoCurrentRow(rs);
+           
+            rs.last();
+            assertEquals("m",rs.getString(1).trim());
+            assertEquals(13, rs.getInt(2));
+            assertEquals(12, rs.getRow());
+            
+            rs.previous();
+            assertEquals("l",rs.getString(1).trim());
+            assertEquals(12, rs.getInt(2));
+            assertEquals(11, rs.getRow());
+            
+            rs.first();
+            assertEquals("b", rs.getString(1).trim());
+            assertEquals(2, rs.getInt(2));
+            assertEquals(1, rs.getRow());
+
+            rs.previous();
+            assertNoCurrentRow(rs);
+            rs.next();
+            assertEquals("b", rs.getString(1).trim());
+            assertEquals(2, rs.getInt(2));
+            assertEquals(1, rs.getRow());
+            rs.close();
+            
+            // afterLast first
+            rs = ps_c1.executeQuery();
+            rs.afterLast();
+            assertNoCurrentRow(rs);
+            rs.previous();
+            
+            assertEquals("m",rs.getString(1).trim());
+            assertEquals(13, rs.getInt(2));
+            assertEquals(12, rs.getRow());
+            
+            rs.previous();
+            assertEquals("l",rs.getString(1).trim());
+            assertEquals(12, rs.getInt(2));
+            assertEquals(11, rs.getRow());
+            
+            rs.close();
+            
+            // go to next to last row and then do next
+            ps_c1.close();
+            ps_c1 = conn.prepareStatement("select * from t1 where i >=11",
+                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
+            rs = ps_c1.executeQuery();
+            rs.next();
+            assertEquals("k",rs.getString(1).trim());
+            assertEquals(11, rs.getInt(2));
+            assertEquals(1, rs.getRow());
+            
+            rs.next();
+            assertEquals("l",rs.getString(1).trim());
+            assertEquals(12, rs.getInt(2));
+            assertEquals(2, rs.getRow());
+            
+            rs.last();
+            assertEquals("m",rs.getString(1).trim());
+            assertEquals(13, rs.getInt(2));
+            assertEquals(3, rs.getRow());
+            
+            rs.previous();
+            assertEquals("l",rs.getString(1).trim());
+            assertEquals(12, rs.getInt(2));
+            assertEquals(2, rs.getRow());
+            
+            rs.afterLast();
+            assertNoCurrentRow(rs);
+            
+            rs.previous();
+            assertEquals("m",rs.getString(1).trim());
+            assertEquals(13, rs.getInt(2));
+            assertEquals(3, rs.getRow());
+            rs.close();
+            // start at after ;ast/
+            rs = ps_c1.executeQuery();
+            rs.afterLast();
+            assertNoCurrentRow(rs);
+            
+            rs.previous();
+            assertEquals("m",rs.getString(1).trim());
+            assertEquals(13, rs.getInt(2));
+            assertEquals(3, rs.getRow());
+            rs.close();
+            ps_c1.close();
+            // use absolute to get rows before scan would get to them.
+           ps_c1 = conn.prepareStatement("select i from t1", ResultSet.TYPE_SCROLL_INSENSITIVE,
+                   ResultSet.CONCUR_READ_ONLY);
+           rs = ps_c1.executeQuery();
+           rs.absolute(5);
+           assertEquals(6, rs.getInt(1));
+           assertEquals(5, rs.getRow());
+           
+           rs.absolute(-5);
+           assertEquals(9,rs.getInt(1));
+           assertEquals(8, rs.getRow());
+
+           rs.absolute(5);
+           assertEquals(6,rs.getInt(1));
+           assertEquals(5, rs.getRow());
+           rs.close();
+           rs = ps_c1.executeQuery();
+           rs.absolute(13);
+           assertNoCurrentRow(rs);
+           
+           rs.previous();
+           assertEquals(13, rs.getInt(1));
+           assertEquals(12, rs.getRow());
+           rs.close();
+           rs = ps_c1.executeQuery();
+           rs.absolute(-13);
+           assertNoCurrentRow(rs);
+           rs.next();
+           assertEquals(2, rs.getInt(1));
+           assertEquals(1,rs.getRow());
+           rs.close();
+           rs = ps_c1.executeQuery();
+           rs.first();
+           assertEquals(2,rs.getInt(1)); 
+           assertEquals(1,rs.getRow());
+           
+           rs.relative(11);
+           assertEquals(13, rs.getInt(1));
+           assertEquals(12, rs.getRow());
+           
+           rs.relative(1);
+           assertNoCurrentRow(rs);
+           rs.last();
+           assertEquals(13, rs.getInt(1));
+           assertEquals(12,rs.getRow());
+           
+           rs.relative(-11);
+           assertEquals(2,rs.getInt(1));
+           assertEquals(1,rs.getRow());
+           rs.close();
+           ps_c1.close();
+           conn.commit();
+           // scroll sensitive cursor becomes scroll insensitive.
+           ps_c1 = conn.prepareStatement("Select i from t1", ResultSet.TYPE_SCROLL_SENSITIVE,
+                   ResultSet.CONCUR_READ_ONLY);    
+           
+           rs = ps_c1.executeQuery();
+           rs.first();
+           assertEquals(2,rs.getInt(1));
+           rs.next();
+           assertEquals(3,rs.getInt(1));
+           Statement s = conn.createStatement();
+           s.executeUpdate("update t1 set i = 666 where i = 2");
+           rs.first();
+           conn.rollback();
+           rs.close();
+           // verify that statement cache works correctly with scroll and forward only cursors.
+           // with the same query text.
+           ps_c1 = conn.prepareStatement("select i from t1", ResultSet.TYPE_SCROLL_INSENSITIVE,
+                   ResultSet.CONCUR_READ_ONLY);
+           rs = ps_c1.executeQuery();
+           PreparedStatement ps_c2 = conn.prepareStatement("select i from t1");
+           ResultSet rs2 = ps_c2.executeQuery();
+           rs.first();
+           assertEquals(2,rs.getInt(1));
+           rs2.next();
+           assertEquals(2,rs2.getInt(1));
+           try {
+               rs2.first();
+               fail("first() not allowed on forward only result set");
+           } catch(SQLException se) {
+               if (usingEmbedded())
+                   assertSQLState("XJ061",se);
+               else
+                   assertSQLState("XJ125",se);
+           }
+            rs.close();
+            rs2.close();
+            ps_c1.close();
+            ps_c1.close();
+            // first, last, etc on empty result set
+            ps_c1 = conn.prepareStatement("select i from t1 where 1=0",
+                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
+            rs = ps_c1.executeQuery();
+            rs.first();
+            assertNoCurrentRow(rs);
+            rs.previous();
+            assertNoCurrentRow(rs);
+            rs.next();
+            assertNoCurrentRow(rs);
+            rs.last();
+            assertNoCurrentRow(rs);
+            rs.previous();
+            assertNoCurrentRow(rs);
+            rs.absolute(1);
+            assertNoCurrentRow(rs);
+            rs.absolute(-1);
+            assertNoCurrentRow(rs);
+            assertEquals(0,rs.getRow());
+            rs.close();
+            rs = ps_c1.executeQuery();
+            rs.afterLast();
+            assertNoCurrentRow(rs);
+            rs.previous();
+            assertNoCurrentRow(rs);
+            rs.beforeFirst();
+            assertNoCurrentRow(rs);
+            rs.next();
+            assertNoCurrentRow(rs);
+            rs.close();
+            rs = ps_c1.executeQuery();
+            rs.absolute(1);
+            assertNoCurrentRow(rs);
+            rs.absolute(-1);
+            assertNoCurrentRow(rs);
+            rs.close();
+            rs = ps_c1.executeQuery();
+            rs.absolute(-1);
+            assertNoCurrentRow(rs);
+            rs.absolute(1);
+            assertNoCurrentRow(rs);
+            rs.close();
+            // with autocommit on
+            conn.setAutoCommit(false);
+            rs = ps_c1.executeQuery();
+            rs = ps_c1.executeQuery();
+            rs.first();
+            assertNoCurrentRow(rs);
+            rs.previous();
+            assertNoCurrentRow(rs);
+            rs.next();
+            assertNoCurrentRow(rs);
+            rs.last();
+            assertNoCurrentRow(rs);
+            rs.previous();
+            assertNoCurrentRow(rs);
+            rs.absolute(1);
+            assertNoCurrentRow(rs);
+            rs.absolute(-1);
+            assertNoCurrentRow(rs);
+            rs.next();
+            assertNoCurrentRow(rs);
+            rs.next();
+            assertNoCurrentRow(rs);
+            rs.close();
+            ps_c1.close();
+            
+            // cursor on a sort
+            ps_c1 = conn.prepareStatement("select * from t1 order by i desc",ResultSet.TYPE_SCROLL_INSENSITIVE,
+                    ResultSet.CONCUR_READ_ONLY);
+            rs = ps_c1.executeQuery();
+            rs.last();
+            assertEquals("b",rs.getString(1).trim());
+            assertEquals(2,rs.getInt(2));
+            rs.first();
+            assertEquals("m",rs.getString(1).trim());
+            assertEquals(13,rs.getInt(2));
+            rs.relative(11);
+            assertEquals("b",rs.getString(1).trim());
+            assertEquals(2,rs.getInt(2));
+            rs.previous();
+            assertEquals("c",rs.getString(1).trim());
+            assertEquals(3,rs.getInt(2));
+            rs.close();
+            ps_c1.close();
+            ps_c1 = conn.prepareStatement("select * from t1",
+                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
+            CallableStatement cs = conn.prepareCall("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+            cs.execute();
+            cs.close();
+            rs = ps_c1.executeQuery();
+            rs.last();
+            assertEquals("m",rs.getString(1).trim());
+            assertEquals(13,rs.getInt(2));
+            rs.first();
+            assertEquals("b",rs.getString(1).trim());
+            assertEquals(2,rs.getInt(2));
+            rs.next();
+            assertEquals("c",rs.getString(1).trim());
+            assertEquals(3,rs.getInt(2));
+            rs.close();
+            rs2 = s.executeQuery("values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+            rs2.next();
+                    
+            if (usingEmbedded())
+            {
+                // not sure why I get a null rts with network server.
+                RuntimeStatisticsParser rts = new RuntimeStatisticsParser(rs2.getString(1));
+                rs2.close();
+                assertEquals(Connection.TRANSACTION_READ_COMMITTED, rts.getIsolationLevel());
+                assertTrue(rts.usedTableScan());
+                assertTrue(rts.isScrollInsensitive());
+            }
+         
+            rs.close();  
+            ps_c1.close();
+            s.executeUpdate("drop table t1");
+            s.executeUpdate("drop table t2");                       
+        }
+
+        public void testNoHoldScrollableResults() throws SQLException{
+            Connection conn = getConnection();
+            conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
+            // Beetle 4551 - insensitive cursor uses estimated row count which 
+            // might be pessimistic and will get out of memory error
+            Statement s = createStatement();
+            s.executeUpdate("create table big(a int generated always as identity (start with
1, increment by 1))");
+            
+            for (int i = 0; i < 10; i++)
+                s.executeUpdate("insert into big values(default)");
+            
+         PreparedStatement ps_c1 = conn.prepareStatement("select * from big b1 left outer
join  big b2 on b1.a = b2.a left outer join  big b3 on b2.a = b3.a left outer join big b4
on b3.a = b4.a left outer join (big b5 left outer join (big b6 left outer join (big b7 left
outer join big b8 on b7.a = b8.a) on b6.a=b7.a) on b5.a = b6.a) on b4.a = b5.a");
+         ResultSet rs = ps_c1.executeQuery();
+         s.executeUpdate("drop table big");
+        }
+        
+        public void testSimpleScrollCursors() throws SQLException {
+            Connection conn = getConnection();
+            Statement s = conn.createStatement();
+            s.executeUpdate("create table t (a int)");
+            PreparedStatement ps = conn.prepareStatement("insert into t values (?)");
+            for (int i = 1; i <=5; i++)
+            {
+                ps.setInt(1, i);
+                ps.executeUpdate();
+            }
+            ps.close();
+            PreparedStatement ps_c1 = conn.prepareStatement("select * from t", ResultSet.TYPE_SCROLL_INSENSITIVE,
+                    ResultSet.CONCUR_READ_ONLY);
+            ResultSet rs = ps_c1.executeQuery();
+            rs.first();
+            assertEquals(1,rs.getInt(1));
+            rs.next();
+            assertEquals(2,rs.getInt(1));
+            rs.previous();
+            assertEquals(1,rs.getInt(1));
+            rs.last();
+            assertEquals(5,rs.getInt(1));
+            rs.absolute(2);
+            assertEquals(2, rs.getInt(1));
+            rs.relative(2);
+            assertEquals(4,rs.getInt(1));
+            rs.close();
+            // since JCC gets 64 results and then scrolls within them
+            // lets try each p ositioning command as the first command for the cursor.
+            rs = ps_c1.executeQuery();
+            rs.next();
+            assertEquals(1,rs.getInt(1));
+            rs.close();
+            rs = ps_c1.executeQuery();
+            rs.last();
+            assertEquals(5,rs.getInt(1));
+            rs.close();
+            rs = ps_c1.executeQuery();
+            rs.absolute(3);
+            assertEquals(3,rs.getInt(1));
+            rs.next();
+            assertEquals(4,rs.getInt(1));
+            rs.close();
+            ps_c1.close();
+            // lets try a table with more than 64 rows.
+            s.executeUpdate("create table tab1 (a int)");
+            PreparedStatement is = conn.prepareStatement("insert into tab1 values (?)");
+            for (int i = 1; i <= 70; i++) {
+                is.setInt(1, i);
+                is.executeUpdate();
+            }
+            ps_c1 = conn.prepareStatement("select * from tab1",ResultSet.TYPE_SCROLL_INSENSITIVE,
+                    ResultSet.CONCUR_READ_ONLY);
+            rs = ps_c1.executeQuery();
+            rs.first();
+            assertEquals(1,rs.getInt(1));
+            rs.last();
+            assertEquals(70,rs.getInt(1));
+            rs.absolute(65);
+            assertEquals(65,rs.getInt(1));
+            rs.absolute(-1);
+            assertEquals(70,rs.getInt(1));
+            rs.close();
+            ps_c1.close();
+            // try sensitive scroll cursors bug 4677
+            ps_c1 = conn.prepareStatement("select * from t1",ResultSet.TYPE_SCROLL_SENSITIVE,
+                        ResultSet.CONCUR_READ_ONLY);
+            rs = ps_c1.executeQuery();
+            rs.close();
+            ps_c1.close();
+            ps_c1 = conn.prepareStatement("select * from t1 for update",ResultSet.TYPE_SCROLL_SENSITIVE,
+                    ResultSet.CONCUR_UPDATABLE);
+            rs.close();
+            s.executeUpdate("drop table tab1");
+            // defect 5225, outer joins returning NULLS
+            s.executeUpdate("create table tab1(i1 bigint not null, c1 varchar(64) not null)");
+            s.executeUpdate("create table tab2 (i2 bigint not null, c2 varchar(64) not null)");
+            s.executeUpdate("insert into tab1 values (1, 'String 1')");
+            s.executeUpdate("insert into tab1 values (2, 'String 2')");
+            s.executeUpdate("insert into tab2 values (1, 'String 1')");
+            s.executeUpdate("insert into tab2 values (3, 'String 3')");
+            rs = s.executeQuery("select c1 from tab1 right outer join tab2 on (i1=i2)");
+            JDBC.assertFullResultSet(rs, new String[][] {{"String 1"},{null}});
+            rs = s.executeQuery("select c2 from tab1 right outer join tab2 on (i1=i2)");
+            JDBC.assertFullResultSet(rs, new String[][] {{"String 1"},{"String 3"}});
+            // left outer join
+            rs = s.executeQuery("select c1 from tab1 left outer join tab2 on (i1=i2)");
+            JDBC.assertFullResultSet(rs, new String[][] {{"String 1"},{"String 2"}});
+            rs = s.executeQuery("select c2 from tab1 left outer join tab2 on (i1=i2)");
+            JDBC.assertFullResultSet(rs, new String[][] {{"String 1"},{null}});
+            s.executeUpdate("drop table t");
+            s.executeUpdate("drop table tab1");
+            s.executeUpdate("drop table tab2");            
+        }
+        
+        public void testScrollCursors3() throws SQLException {
+            Connection conn = getConnection();
+            Connection conn2 = openDefaultConnection();
+            Statement s = conn.createStatement();
+            s.executeUpdate("create table u1.t1(c1 int, c2 int)");
+            s.executeUpdate("insert into u1.t1 values (1, 2), (3, 4), (5, 6), (7, 8), (9,
10)");
+            PreparedStatement ps_c1 = conn.prepareStatement("select * from u1.t1", ResultSet.TYPE_SCROLL_INSENSITIVE,
+                    ResultSet.CONCUR_READ_ONLY);
+            ResultSet rs = ps_c1.executeQuery();
+            
+            // see what happens when other user when we close our cursor before
+            // they are done.
+            PreparedStatement ps_c2 = conn2.prepareStatement("select * from u1.t1", ResultSet.TYPE_SCROLL_INSENSITIVE,
+                    ResultSet.CONCUR_READ_ONLY);
+            ResultSet rs2 = ps_c2.executeQuery();
+            rs.next();
+            assertEquals(1, rs.getInt(1));
+            assertEquals(2, rs.getInt(2));
+            
+            rs2.next();
+            assertEquals(1, rs.getInt(1));
+            assertEquals(2, rs.getInt(2));
+            rs.last();
+            assertEquals(9, rs.getInt(1));
+            assertEquals(10, rs.getInt(2));
+            rs2.last();
+            assertEquals(9, rs.getInt(1));
+            assertEquals(10, rs.getInt(2));
+            rs.previous();
+            assertEquals(7, rs.getInt(1));
+            assertEquals(8, rs.getInt(2));
+            rs2.close();
+            rs.first();
+            assertEquals(1, rs.getInt(1));
+            assertEquals(2, rs.getInt(2));
+            rs.close();
+            s.executeUpdate("drop table t1");
+        }
+        
+        
+        private void assertNoCurrentRow(ResultSet rs) throws SQLException {
+            try {
+                rs.getString(1);
+                fail("getString not allowed after beforeFirst()");
+            }catch (SQLException se ){
+                if (usingEmbedded())
+                    assertSQLState("24000",se);
+                else
+                    assertSQLState("XJ121",se);                
+            }
+            assertEquals(0,rs.getRow());
+        }
+
+        
+        public static Test baseSuite(String name) {
+
+        TestSuite suite = new TestSuite(name);
+        suite.addTestSuite(ScrollCursors1Test.class);
+
+        return new CleanDatabaseTestSetup(suite) {
+
+            /**
+             * Create and populate table
+             * 
+             * @see org.apache.derbyTesting.junit.CleanDatabaseTestSetup#decorateSQL(java.sql.Statement)
+             */
+            protected void decorateSQL(Statement s) throws SQLException {
+                s.executeUpdate("create table t1(c50 char(50), i int)");
+
+                s.executeUpdate(" create table t2(c50 char(50), i int)");
+
+                // populate tables
+
+                s.executeUpdate("insert into t1 values ('b', 2), ('c', 3), ('d', 4), "
+                                + "('e', 5),"
+                                + "                   ('f', 6), ('g', 7), ('h', 8), ('i',
9),"
+                                + "                   ('j', 10), ('k', 11), ('l', 12), ('m',
13)");
+
+                
+                getConnection().setAutoCommit(false);
+            }
+        };
+    }
+
+    public static Test suite() {
+        TestSuite suite = new TestSuite("ScrollCursors1");
+        suite.addTest(baseSuite("ScrollCursors1:embedded"));
+        suite.addTest(TestConfiguration.clientServerDecorator(baseSuite("ScrollCursors1:client")));
+        return suite;
+
+    }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ScrollCursors1Test.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?view=diff&rev=529795&r1=529794&r2=529795
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Tue Apr 17 15:31:16 2007
@@ -96,7 +96,7 @@
         suite.addTest(CoalesceTest.suite());
         suite.addTest(ProcedureInTriggerTest.suite());
         suite.addTest(CollationTest.suite());
-        suite.addTest(ScrollCursors1.suite());
+        suite.addTest(ScrollCursors1Test.suite());
         suite.addTest(SimpleTest.suite());
 
         // Add the XML tests, which exist as a separate suite



Mime
View raw message