safetest.java

第三方的SQL Server and Sybase的jdbc dirver,速度更快

     */
    public void testSocketConcurrency4() throws Exception {
        // Just enough rows to break the server response in two network packets
        final int rowCount = 256;

        //Set up the test table
        final Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #testSocketConcurrency4 "
                + "(id int primary key, value varchar(30))");
        for (int i = 0; i < rowCount; i++) {
            stmt.executeUpdate("insert into #testSocketConcurrency4 "
                    + "values (" + i + ", 'Row number " + i + "')");
        }

        final Vector errors = new Vector();

        // Start a thread that does some work
        Thread t = new Thread() {
            public void run() {
                try {
                    for (int j = 0; j < 10; j++) {
                        ResultSet rs = stmt.executeQuery(
                                "select * from #testSocketConcurrency4");
                        int cnt = 0;
                        while (rs.next()) {
                            ++cnt;
                        }
                        assertEquals(rowCount, cnt);
                        rs.close();
                        assertEquals(1, stmt.executeUpdate(
                                "update #testSocketConcurrency4 "
                                + "set value='Updated' where id=" + j));
                    }
                } catch (Exception ex) {
                    ex.printStackTrace();
                    errors.add(ex);
                }
            }
        };
        t.start();

        // At the same time run some cancel() tests (on the same connection!)
        testCancel0003();

        // Now wait for the worker thread to finish
        t.join();

        assertEquals(0, errors.size());
    }

    /**
     * Test that <code>null</code> output parameters are handled correctly.
     * <p/>
     * It seems that if a non-nullable type is sent as input value and the
     * output value is NULL, SQL Server (not Sybase) gets confused and returns
     * the same type but a single 0 byte as value instead of the equivalent
     * nullable type (e.g. instead of returning an <code>INTN</code> with
     * length 0, which means it's null, it returns an <code>INT4</code>
     * followed by a single 0 byte). The output parameter packet length is also
     * incorrect, which indicates that SQL Server is confused.
     * <p/>
     * Currently jTDS always sends RPC parameters as nullable types, but this
     * test is necessary to ensure that it will always remain so.
     */
    public void testNullOutputParameters() throws SQLException {
        Statement stmt = con.createStatement();
        assertEquals(0, stmt.executeUpdate(
                "create procedure #testNullOutput @p1 int output as "
                + "select @p1=null"));
        stmt.close();

        CallableStatement cstmt = con.prepareCall("#testNullOutput ?");
        cstmt.setInt(1, 1);
        cstmt.registerOutParameter(1, Types.INTEGER);
        assertEquals(0, cstmt.executeUpdate());
        assertNull(cstmt.getObject(1));
        cstmt.close();
    }

    /**
     * Test that the SQL parser doesn't try to parse the table name unless
     * necessary (or that it is able to parse function calls if it does).
     */
    public void testTableParsing() throws SQLException {
        Statement stmt = con.createStatement();
        try {
            stmt.executeQuery(
                    "SELECT * FROM ::fn_missing('c:\\t file.trc')");
            fail("Expecting an SQLException");
        } catch (SQLException ex) {
            // 42000 == syntax error or access rule violation
            assertEquals("42000", ex.getSQLState());
        }
    }

    /**
     * Test for bug #1116046 {fn } escape can't handle nested functions.
     */
    public void testFnEscapeNesting() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate(
                "create table #testFnEscapeNesting (col1 int null, col2 int)");
        stmt.executeUpdate("insert into #testFnEscapeNesting (col1, col2) "
                + "values (null, 1)");
        stmt.executeUpdate("insert into #testFnEscapeNesting (col1, col2) "
                + "values (1, 2)");

        ResultSet rs = stmt.executeQuery(
                "select {fn ifnull({fn max(col2)}, 0)} "
                + "from #testFnEscapeNesting");
        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();

        rs = stmt.executeQuery("select {fn ifnull((select col1 "
                + "from #testFnEscapeNesting where col2 = 1), 0) }");
        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals(0, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();

        rs = stmt.executeQuery(
                "select {fn ifnull(sum({fn ifnull(col1, 4)}), max(col2))} "
                + "from #testFnEscapeNesting "
                + "group by col2 order by col2");
        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals(4, rs.getInt(1));
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertFalse(rs.next());
        rs.close();

        stmt.close();
    }

    /**
     * Test <code>DataTruncation</code> exception.
     */
    public void testDataTruncException() throws Exception {
        con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        Statement stmt = con.createStatement();
        if (!con.getMetaData().getDatabaseProductName().
                toLowerCase().startsWith("microsoft")) {
            // By default Sybase will silently truncate strings,
            // set an option to ensure that an exception is thrown.
            stmt.execute("SET STRING_RTRUNCATION ON");
        }

        stmt.execute("CREATE TABLE #TESTTRUNC (i tinyint, n numeric(2), c char(2))");

        try {
            stmt.execute("INSERT INTO #TESTTRUNC VALUES(1111, 1, 'X')");
            fail("Expected data truncation on tinyint");
        } catch (DataTruncation e) {
            // Expected DataTruncation
        }

        try {
            stmt.execute("INSERT INTO #TESTTRUNC VALUES(1, 1111, 'X')");
            fail("Expected data truncation on numeric");
        } catch (DataTruncation e) {
            // Expected DataTruncation
        }

        try {
            stmt.execute("INSERT INTO #TESTTRUNC VALUES(1, 1, 'XXXXX')");
            fail("Expected data truncation on char");
        } catch (DataTruncation e) {
            // Expected DataTruncation
        }
    }

    /**
     * Test <code>Statement.setMaxFieldSize()</code>.
     */
    public void testMaxFieldSize() throws Exception {
        // TODO Should it also work for fields other than TEXT, per JDBC spec?
        Statement stmt = con.createStatement();

        stmt.executeUpdate("create table #testMaxFieldSize (i int primary key, t text)");
        stmt.executeUpdate("insert into #testMaxFieldSize (i, t) values (1, 'This is a test')");

        PreparedStatement pstmt = con.prepareStatement("select * from #testMaxFieldSize");

        // Set different max field sizes for two concurrent statements
        // Also set max rows, to test setting field size and max rows at the
        // same time works ok
        stmt.setMaxFieldSize(3);
        stmt.setMaxRows(1);
        pstmt.setMaxFieldSize(5);

        // Test plain statement
        ResultSet rs = stmt.executeQuery("select * from #testMaxFieldSize");
        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(3, rs.getString(2).length());
        rs.close();

        // Test prepared statement
        rs = pstmt.executeQuery();
        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(5, rs.getString(2).length());
        rs.close();

        stmt.close();

        // Test scrollable statement
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        stmt.setMaxFieldSize(3);
        rs = stmt.executeQuery("select * from #testMaxFieldSize");
        assertNotNull(rs);
        assertNull(stmt.getWarnings());
        assertNull(rs.getWarnings());
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(3, rs.getString(2).length());
        rs.close();
    }

    /**
     * Test return of multiple scrollable result sets from one execute.
     */
    public void testGetMultiScrollRs() throws Exception {
        // Manual commit mode to make sure no garbage is left behind
        con.setAutoCommit(false);

        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        try {
            dropProcedure("jtds_multiSet");
            stmt.execute("CREATE PROC jtds_multiSet as\r\n " +
                    "BEGIN\r\n" +
                    "SELECT 'SINGLE ROW RESULT'\r\n"+
                    "SELECT 1, 'LINE ONE'\r\n"+
                    "UNION\r\n" +
                    "SELECT 2, 'LINE TWO'\r\n"+
                    "UNION\r\n" +
                    "SELECT 3, 'LINE THREE'\r\n"+
                    "SELECT 'ANOTHER SINGLE ROW RESULT'\r\n"+
                    "END\r\n");
            assertTrue(stmt.execute("exec jtds_multiSet"));
            stmt.clearWarnings();
            ResultSet rs = stmt.getResultSet();
            assertNotNull(stmt.getWarnings()); // Downgrade to read only
            assertNotNull(stmt.getWarnings().getNextWarning()); // Downgrade to insensitive
            assertTrue(rs.next());
            assertEquals("SINGLE ROW RESULT", rs.getString(1));

            assertTrue(stmt.getMoreResults());
            rs = stmt.getResultSet();
            assertTrue(rs.absolute(2));
            assertEquals("LINE TWO", rs.getString(2));
            assertTrue(rs.relative(-1));
            assertEquals("LINE ONE", rs.getString(2));

            assertTrue(stmt.getMoreResults());
            rs = stmt.getResultSet();
            assertTrue(rs.next());
            assertEquals("ANOTHER SINGLE ROW RESULT", rs.getString(1));
        } finally {
            dropProcedure("jtds_multiSet");
            stmt.close();
            // We can safely commit, mess cleaned up (we could rollback, too)
            con.commit();
        }
    }

    /**
     * Test for bug [1187927] Driver Hangs on Statement.execute().
     * <p/>
     * Versions 1.0.3 and prior entered an infinite loop when parsing an
     * unterminated multi-line comment.
     */
    public void testUnterminatedCommentParsing() throws Exception {
        Statement stmt = con.createStatement();
        try {
            stmt.execute("/* This is an unterminated comment");
            fail("Expecting parse exception");
        } catch (SQLException ex) {
            assertEquals("22025", ex.getSQLState());
        }
        stmt.close();
    }

    /**
     * Test that getString() on a varbinary column returns a hex string.
     */
    public void testBytesToString() throws Exception {
        Statement stmt = con.createStatement(
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        stmt.execute(
                "CREATE TABLE #testbytes (id int primary key, b varbinary(8), i image, c varchar(255) null)");
        assertEquals(1, stmt.executeUpdate(
                "INSERT INTO #testbytes VALUES (1, 0x41424344, 0x41424344, null)"));

        ResultSet rs = stmt.executeQuery("SELECT * FROM #testbytes");
        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals("41424344", rs.getString(2));
        assertEquals("41424344", rs.getString(3));
        Clob clob = rs.getClob(2);
        assertEquals("41424344", clob.getSubString(1, (int)clob.length()));
        clob = rs.getClob(3);
        assertEquals("41424344", clob.getSubString(1, (int)clob.length()));
        //
        // Check that updating sensitive result sets yields the correct
        // results. This test is mainly for Sybase scroll sensitive client
        // side cursors.
        //
        rs.updateBytes(4, new byte[]{0x41, 0x42, 0x43, 0x44});
        rs.updateRow();
        assertEquals("ABCD", rs.getString(4));
        stmt.close();
    }

    /**
     * Tests that <code>executeUpdate("SELECT ...")</code> fails.
     */
    public void testExecuteUpdateSelect() throws Exception {
        Statement stmt = con.createStatement();
        try {
            stmt.executeUpdate("select 1");
            fail("Expecting an exception to be thrown");
        } catch (SQLException ex) {
            assertEquals("07000", ex.getSQLState());
        }
        stmt.close();

        PreparedStatement pstmt = con.prepareStatement("select 1");
        try {
            pstmt.executeUpdate();
            fail("Expecting an exception to be thrown");
        } catch (SQLException ex) {
            assertEquals("07000", ex.getSQLState());
        }
        stmt.close();
    }
}