resultsettest.java

来自「jtds的源码 是你学习java的好东西」· Java 代码 · 共 1,736 行 · 第 1/5 页

        cstmt.setInt(3, 8); // Last
        rs = cstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(9, rs.getInt(1));
        assertTrue(rs.next());
        assertEquals(10, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();
        assertEquals(0, cstmt.getInt(1));

        // Fetch next rows; should not fail (current position is after last)
        cstmt.setInt(3, 2); // Next
        rs = cstmt.executeQuery();
        assertFalse(rs.next());
        rs.close();
        assertEquals(0, cstmt.getInt(1));

        // Fetch absolute starting with 6 (6-7) (current row is 6)
        cstmt.setInt(3, 0x10); // Absolute
        cstmt.setInt(4, 6);
        rs = cstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(6, rs.getInt(1));
        assertTrue(rs.next());
        assertEquals(7, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();
        assertEquals(0, cstmt.getInt(1));

        // Fetch relative -4 (2-3) (current row is 2)
        cstmt.setInt(3, 0x20); // Relative
        cstmt.setInt(4, -4);
        rs = cstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        assertTrue(rs.next());
        assertEquals(3, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();
        assertEquals(0, cstmt.getInt(1));

        // Fetch previous 2 rows; should fail (current row is 1)
        cstmt.setInt(3, 4); // Previous
        rs = cstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();
        // Returns 2 on error
        assertEquals(2, cstmt.getInt(1));

        // Fetch next rows (3-4) (current row is 3)
        cstmt.setInt(3, 2); // Next
        rs = cstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(3, rs.getInt(1));
        assertTrue(rs.next());
        assertEquals(4, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();
        assertEquals(0, cstmt.getInt(1));

        cstmt.close();

        //
        // Close cursor
        //
        cstmt = con.prepareCall("{?=call sp_cursorclose(?)}");
        // Return value (OUT)
        cstmt.registerOutParameter(1, Types.INTEGER);
        // Cursor handle (IN)
        cstmt.setInt(2, cursor);
        assertFalse(cstmt.execute());
        assertEquals(0, cstmt.getInt(1));
        cstmt.close();
    }

    /**
     * Test that <code>absolute(-1)</code> works the same as <code>last()</code>.
     */
    public void testAbsoluteMinusOne() throws Exception {
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

        stmt.executeUpdate(
                "create table #absoluteMinusOne (val int primary key)");
        stmt.executeUpdate(
                "insert into #absoluteMinusOne (val) values (1)");
        stmt.executeUpdate(
                "insert into #absoluteMinusOne (val) values (2)");
        stmt.executeUpdate(
                "insert into #absoluteMinusOne (val) values (3)");

        ResultSet rs = stmt.executeQuery(
                "select val from #absoluteMinusOne order by val");

        rs.absolute(-1);
        assertTrue(rs.isLast());
        assertEquals(3, rs.getInt(1));
        assertFalse(rs.next());

        rs.last();
        assertTrue(rs.isLast());
        assertEquals(3, rs.getInt(1));
        assertFalse(rs.next());

        rs.close();
        stmt.close();
    }

    /**
     * Test that calling <code>absolute()</code> with very large positive
     * values positions the cursor after the last row and with very large
     * negative values positions the cursor before the first row.
     */
    public void testAbsoluteLargeValue() throws SQLException {
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

        stmt.executeUpdate(
                "create table #absoluteLargeValue (val int primary key)");
        stmt.executeUpdate(
                "insert into #absoluteLargeValue (val) values (1)");
        stmt.executeUpdate(
                "insert into #absoluteLargeValue (val) values (2)");
        stmt.executeUpdate(
                "insert into #absoluteLargeValue (val) values (3)");

        ResultSet rs = stmt.executeQuery(
                "select val from #absoluteLargeValue order by val");

        assertFalse(rs.absolute(10));
        assertEquals(0, rs.getRow());
        assertTrue(rs.isAfterLast());
        assertFalse(rs.next());
        assertEquals(0, rs.getRow());
        assertTrue(rs.isAfterLast());

        assertFalse(rs.absolute(-10));
        assertEquals(0, rs.getRow());
        assertTrue(rs.isBeforeFirst());
        assertFalse(rs.previous());
        assertEquals(0, rs.getRow());
        assertTrue(rs.isBeforeFirst());

        rs.close();
        stmt.close();
    }

    /**
     * Test that calling <code>absolute()</code> with very large positive
     * values positions the cursor after the last row and with very large
     * negative values positions the cursor before the first row.
     */
    public void testRelativeLargeValue() throws SQLException {
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

        stmt.executeUpdate(
                "create table #relativeLargeValue (val int primary key)");
        stmt.executeUpdate(
                "insert into #relativeLargeValue (val) values (1)");
        stmt.executeUpdate(
                "insert into #relativeLargeValue (val) values (2)");
        stmt.executeUpdate(
                "insert into #relativeLargeValue (val) values (3)");

        ResultSet rs = stmt.executeQuery(
                "select val from #relativeLargeValue order by val");

        assertFalse(rs.relative(10));
        assertEquals(0, rs.getRow());
        assertTrue(rs.isAfterLast());
        assertFalse(rs.next());
        assertEquals(0, rs.getRow());
        assertTrue(rs.isAfterLast());

        assertFalse(rs.relative(-10));
        assertEquals(0, rs.getRow());
        assertTrue(rs.isBeforeFirst());
        assertFalse(rs.previous());
        assertEquals(0, rs.getRow());
        assertTrue(rs.isBeforeFirst());

        rs.close();
        stmt.close();
    }

    /**
     * Test that <code>read()</code> works ok on the stream returned by
     * <code>ResultSet.getUnicodeStream()</code> (i.e. it doesn't always fill
     * the buffer, regardless of whether there's available data or not).
     */
    public void testUnicodeStream() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #unicodeStream (val varchar(255))");
        stmt.executeUpdate("insert into #unicodeStream (val) values ('test')");
        ResultSet rs = stmt.executeQuery("select val from #unicodeStream");

        if (rs.next()) {
            byte[] buf = new byte[8000];
            InputStream is = rs.getUnicodeStream(1);
            int length = is.read(buf);
            assertEquals(4 * 2, length);
        }

        rs.close();
        stmt.close();
    }

    /**
     * Test that <code>Statement.setMaxRows()</code> works on cursor
     * <code>ResultSet</code>s.
     */
    public void testCursorMaxRows() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #cursorMaxRows (val int)");
        stmt.close();

        // Insert 10 rows
        PreparedStatement pstmt = con.prepareStatement(
                "insert into #cursorMaxRows (val) values (?)");
        for (int i = 0; i < 10; i++) {
            pstmt.setInt(1, i);
            assertEquals(1, pstmt.executeUpdate());
        }
        pstmt.close();

        // Create a cursor ResultSet
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        // Set maxRows to 5
        stmt.setMaxRows(5);

        // Select all (should only return 5 rows)
        ResultSet rs = stmt.executeQuery("select * from #cursorMaxRows");
        rs.last();
        assertEquals(5, rs.getRow());
        rs.beforeFirst();

        int cnt = 0;
        while (rs.next()) {
            cnt++;
        }
        assertEquals(5, cnt);

        rs.close();
        stmt.close();
    }

    /**
     * Test for bug [1075977] <code>setObject()</code> causes SQLException.
     * <p>
     * Conversion of <code>float</code> values to <code>String</code> adds
     * grouping to the value, which cannot then be parsed.
     */
    public void testSetObjectScale() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("create table #testsetobj (i int)");
        PreparedStatement pstmt =
                con.prepareStatement("insert into #testsetobj values(?)");
        // next line causes sqlexception
        pstmt.setObject(1, new Float(1234.5667), Types.INTEGER, 0);
        assertEquals(1, pstmt.executeUpdate());
        ResultSet rs = stmt.executeQuery("select * from #testsetobj");
        assertTrue(rs.next());
        assertEquals("1234", rs.getString(1));
    }

    /**
     * Test that <code>ResultSet.previous()</code> works correctly on cursor
     * <code>ResultSet</code>s.
     */
    public void testCursorPrevious() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #cursorPrevious (val int)");
        stmt.close();

        // Insert 10 rows
        PreparedStatement pstmt = con.prepareStatement(
                "insert into #cursorPrevious (val) values (?)");
        for (int i = 0; i < 10; i++) {
            pstmt.setInt(1, i);
            assertEquals(1, pstmt.executeUpdate());
        }
        pstmt.close();

        // Create a cursor ResultSet
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        // Set fetch size to 2
        stmt.setFetchSize(2);

        // Select all
        ResultSet rs = stmt.executeQuery("select * from #cursorPrevious");
        rs.last();
        int i = 10;
        do {
            assertEquals(i, rs.getRow());
            assertEquals(--i, rs.getInt(1));
        } while (rs.previous());
        assertTrue(rs.isBeforeFirst());
        assertEquals(0, i);

        rs.close();
        stmt.close();
    }

    /**
     * Test the behavior of the ResultSet/Statement/Connection when the JVM
     * runs out of memory (hopefully) in the middle of a packet.
     * <p/>
     * Previously jTDS was not able to close a ResultSet/Statement/Connection
     * after an OutOfMemoryError because the input stream pointer usually
     * remained inside a packet and further attempts to dump the rest of the
     * response failed because of "protocol confusions".
     */
    public void testOutOfMemory() throws SQLException {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #testOutOfMemory (val binary(8000))");

        // Insert a 8KB value
        byte[] val = new byte[8000];
        PreparedStatement pstmt = con.prepareStatement(
                "insert into #testOutOfMemory (val) values (?)");
        pstmt.setBytes(1, val);
        assertEquals(1, pstmt.executeUpdate());
        pstmt.close();

        // Create a list and keep adding rows to it until we run out of memory
        // Most probably this will happen in the middle of a row packet, when
        // jTDS tries to allocate the array, after reading the data length
        ArrayList results = new ArrayList();
        ResultSet rs = null;
        try {
            while (true) {
                rs = stmt.executeQuery("select val from #testOutOfMemory");
                assertTrue(rs.next());
                results.add(rs.getBytes(1));
                assertFalse(rs.next());
                rs.close();
                rs = null;
            }
        } catch (OutOfMemoryError err) {
            // Do not remove this. Although not really used, it will free
            // memory, avoiding another OutOfMemoryError
            results = null;
            if (rs != null) {