callablestatementtest.java

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

    }

    /**
     * Test for bug [983432] Prepared call doesn't work with jTDS 0.8
     */
    public void testCallableRegisterOutParameter4() throws Exception {
        CallableStatement cstmt = con.prepareCall("{call sp_addtype T_INTEGER, int, 'NULL'}");
        Statement stmt = con.createStatement();

        try {
            cstmt.execute();
            cstmt.close();

            stmt.execute("create procedure rop4 @data T_INTEGER OUTPUT as\r\n "
                         + "begin\r\n"
                         + "set @data = 1\r\n"
                         + "end");
            stmt.close();

            cstmt = con.prepareCall("{call rop4(?)}");

            cstmt.registerOutParameter(1, Types.VARCHAR);
            cstmt.execute();

            assertEquals(cstmt.getInt(1), 1);
            assertTrue(!cstmt.wasNull());
            cstmt.close();
        } finally {
            stmt = con.createStatement();
            stmt.execute("drop procedure rop4");
            stmt.close();

            cstmt = con.prepareCall("{call sp_droptype 'T_INTEGER'}");
            cstmt.execute();
            cstmt.close();
        }
    }

    /**
     * Test for bug [991640] java.sql.Date error and RAISERROR problem
     */
    public void testCallableError1() throws Exception {
        String text = "test message";

        Statement stmt = con.createStatement();
        stmt.execute("create procedure #ce1 as\r\n "
                     + "begin\r\n"
                     + "RAISERROR('" + text + "', 16, 1 )\r\n"
                     + "end");
        stmt.close();

        CallableStatement cstmt = con.prepareCall("{call #ce1}");

        try {
            cstmt.execute();
            assertTrue(false);
        } catch (SQLException e) {
            assertTrue(e.getMessage().equals(text));
        }

        cstmt.close();
    }

    /**
     * Test that procedure outputs are available immediately for procedures
     * that do not return ResultSets (i.e that update counts are cached).
     */
    public void testProcessUpdateCounts1() throws SQLException {
        Statement stmt = con.createStatement();
        assertFalse(stmt.execute("CREATE TABLE #testProcessUpdateCounts1 (val INT)"));
        assertFalse(stmt.execute("CREATE PROCEDURE #procTestProcessUpdateCounts1"
                + " @res INT OUT AS"
                + " INSERT INTO #testProcessUpdateCounts1 VALUES (1)"
                + " UPDATE #testProcessUpdateCounts1 SET val = 2"
                + " INSERT INTO #testProcessUpdateCounts1 VALUES (1)"
                + " UPDATE #testProcessUpdateCounts1 SET val = 3"
                + " SET @res = 13"
                + " RETURN 14"));
        stmt.close();

        CallableStatement cstmt = con.prepareCall(
                "{?=call #procTestProcessUpdateCounts1(?)}");
        cstmt.registerOutParameter(1, Types.INTEGER);
        cstmt.registerOutParameter(2, Types.INTEGER);

        assertFalse(cstmt.execute());
        assertEquals(14, cstmt.getInt(1));
        assertEquals(13, cstmt.getInt(2));

        assertEquals(1, cstmt.getUpdateCount()); // INSERT

        assertFalse(cstmt.getMoreResults());
        assertEquals(1, cstmt.getUpdateCount()); // UPDATE

        assertFalse(cstmt.getMoreResults());
        assertEquals(1, cstmt.getUpdateCount()); // INSERT

        assertFalse(cstmt.getMoreResults());
        assertEquals(2, cstmt.getUpdateCount()); // UPDATE

        assertFalse(cstmt.getMoreResults());
        assertEquals(-1, cstmt.getUpdateCount());

        cstmt.close();
    }

    /**
     * Test that procedure outputs are available immediately after processing
     * the last ResultSet returned by the procedure (i.e that update counts
     * are cached).
     */
    public void testProcessUpdateCounts2() throws SQLException {
        Statement stmt = con.createStatement();
        assertFalse(stmt.execute("CREATE TABLE #testProcessUpdateCounts2 (val INT)"));
        assertFalse(stmt.execute("CREATE PROCEDURE #procTestProcessUpdateCounts2"
                + " @res INT OUT AS"
                + " INSERT INTO #testProcessUpdateCounts2 VALUES (1)"
                + " UPDATE #testProcessUpdateCounts2 SET val = 2"
                + " SELECT * FROM #testProcessUpdateCounts2"
                + " INSERT INTO #testProcessUpdateCounts2 VALUES (1)"
                + " UPDATE #testProcessUpdateCounts2 SET val = 3"
                + " SET @res = 13"
                + " RETURN 14"));
        stmt.close();

        CallableStatement cstmt = con.prepareCall(
                "{?=call #procTestProcessUpdateCounts2(?)}");
        cstmt.registerOutParameter(1, Types.INTEGER);
        cstmt.registerOutParameter(2, Types.INTEGER);

        assertFalse(cstmt.execute());
        try {
            assertEquals(14, cstmt.getInt(1));
            assertEquals(13, cstmt.getInt(2));
            // Don't fail the test if we got here. Another driver or a future
            // version could cache all the results and obtain the output
            // parameter values from the beginning.
        } catch (SQLException ex) {
            assertEquals("HY010", ex.getSQLState());
            assertTrue(ex.getMessage().indexOf("getMoreResults()") >= 0);
        }

        assertEquals(1, cstmt.getUpdateCount()); // INSERT

        assertFalse(cstmt.getMoreResults());
        assertEquals(1, cstmt.getUpdateCount()); // UPDATE

        assertTrue(cstmt.getMoreResults()); // SELECT

        assertFalse(cstmt.getMoreResults());
        assertEquals(14, cstmt.getInt(1));
        assertEquals(13, cstmt.getInt(2));
        assertEquals(1, cstmt.getUpdateCount()); // INSERT

        assertFalse(cstmt.getMoreResults());
        assertEquals(2, cstmt.getUpdateCount()); // UPDATE

        assertFalse(cstmt.getMoreResults());
        assertEquals(-1, cstmt.getUpdateCount());

        cstmt.close();
    }

    /**
     * Test that procedure outputs are available immediately after processing
     * the last ResultSet returned by the procedure (i.e that update counts
     * are cached) even if getMoreResults() is not called.
     */
    public void testProcessUpdateCounts3() throws SQLException {
        Statement stmt = con.createStatement();
        assertFalse(stmt.execute("CREATE TABLE #testProcessUpdateCounts3 (val INT)"));
        assertFalse(stmt.execute("CREATE PROCEDURE #procTestProcessUpdateCounts3"
                + " @res INT OUT AS"
                + " INSERT INTO #testProcessUpdateCounts3 VALUES (1)"
                + " UPDATE #testProcessUpdateCounts3 SET val = 2"
                + " SELECT * FROM #testProcessUpdateCounts3"
                + " INSERT INTO #testProcessUpdateCounts3 VALUES (1)"
                + " UPDATE #testProcessUpdateCounts3 SET val = 3"
                + " SET @res = 13"
                + " RETURN 14"));
        stmt.close();

        CallableStatement cstmt = con.prepareCall(
                "{?=call #procTestProcessUpdateCounts3(?)}");
        cstmt.registerOutParameter(1, Types.INTEGER);
        cstmt.registerOutParameter(2, Types.INTEGER);

        assertFalse(cstmt.execute());
        try {
            assertEquals(14, cstmt.getInt(1));
            assertEquals(13, cstmt.getInt(2));
            // Don't fail the test if we got here. Another driver or a future
            // version could cache all the results and obtain the output
            // parameter values from the beginning.
        } catch (SQLException ex) {
            assertEquals("HY010", ex.getSQLState());
            assertTrue(ex.getMessage().indexOf("getMoreResults()") >= 0);
        }

        assertEquals(1, cstmt.getUpdateCount()); // INSERT

        assertFalse(cstmt.getMoreResults());
        assertEquals(1, cstmt.getUpdateCount()); // UPDATE

        assertTrue(cstmt.getMoreResults()); // SELECT
        ResultSet rs = cstmt.getResultSet();
        assertNotNull(rs);
        // Close the ResultSet; this should cache the following update counts
        rs.close();

        assertEquals(14, cstmt.getInt(1));
        assertEquals(13, cstmt.getInt(2));

        assertFalse(cstmt.getMoreResults());
        assertEquals(1, cstmt.getUpdateCount()); // INSERT

        assertFalse(cstmt.getMoreResults());
        assertEquals(2, cstmt.getUpdateCount()); // UPDATE

        assertFalse(cstmt.getMoreResults());
        assertEquals(-1, cstmt.getUpdateCount());

        cstmt.close();
    }

    /**
     * Test that procedure outputs are available immediately after processing
     * the last ResultSet returned by the procedure (i.e that update counts
     * are cached) even if getMoreResults() and ResultSet.close() are not
     * called.
     */
    public void testProcessUpdateCounts4() throws SQLException {
        Statement stmt = con.createStatement();
        assertFalse(stmt.execute("CREATE TABLE #testProcessUpdateCounts4 (val INT)"));
        assertFalse(stmt.execute("CREATE PROCEDURE #procTestProcessUpdateCounts4"
                + " @res INT OUT AS"
                + " INSERT INTO #testProcessUpdateCounts4 VALUES (1)"
                + " UPDATE #testProcessUpdateCounts4 SET val = 2"
                + " SELECT * FROM #testProcessUpdateCounts4"
                + " INSERT INTO #testProcessUpdateCounts4 VALUES (1)"
                + " UPDATE #testProcessUpdateCounts4 SET val = 3"
                + " SET @res = 13"
                + " RETURN 14"));
        stmt.close();

        CallableStatement cstmt = con.prepareCall(
                "{?=call #procTestProcessUpdateCounts4(?)}");
        cstmt.registerOutParameter(1, Types.INTEGER);
        cstmt.registerOutParameter(2, Types.INTEGER);

        assertFalse(cstmt.execute());
        try {
            assertEquals(14, cstmt.getInt(1));
            assertEquals(13, cstmt.getInt(2));
            // Don't fail the test if we got here. Another driver or a future
            // version could cache all the results and obtain the output
            // parameter values from the beginning.
        } catch (SQLException ex) {
            assertEquals("HY010", ex.getSQLState());
            assertTrue(ex.getMessage().indexOf("getMoreResults()") >= 0);
        }

        assertEquals(1, cstmt.getUpdateCount()); // INSERT

        assertFalse(cstmt.getMoreResults());
        assertEquals(1, cstmt.getUpdateCount()); // UPDATE

        assertTrue(cstmt.getMoreResults()); // SELECT
        ResultSet rs = cstmt.getResultSet();
        assertNotNull(rs);
        // Process all rows; this should cache the following update counts
        assertTrue(rs.next());
        assertFalse(rs.next());

        assertEquals(14, cstmt.getInt(1));
        assertEquals(13, cstmt.getInt(2));

        // Only close the ResultSet now
        rs.close();

        assertFalse(cstmt.getMoreResults());
        assertEquals(1, cstmt.getUpdateCount()); // INSERT

        assertFalse(cstmt.getMoreResults());
        assertEquals(2, cstmt.getUpdateCount()); // UPDATE

        assertFalse(cstmt.getMoreResults());
        assertEquals(-1, cstmt.getUpdateCount());

        cstmt.close();
    }

    /**
     * Test for bug [ 1062671 ] SQLParser unable to parse CONVERT(char,{ts ?},102)
     */
    public void testTsEscape() throws Exception {
        Timestamp ts = Timestamp.valueOf("2004-01-01 23:56:56");
        Statement stmt = con.createStatement();
        assertFalse(stmt.execute("CREATE TABLE #testTsEscape (val DATETIME)"));
        PreparedStatement pstmt = con.prepareStatement("INSERT INTO #testTsEscape VALUES({ts ?})");
        pstmt.setTimestamp(1, ts);
        assertEquals(1, pstmt.executeUpdate());
        ResultSet rs = stmt.executeQuery("SELECT * FROM #testTsEscape");
        assertTrue(rs.next());
        assertEquals(ts, rs.getTimestamp(1));
    }

    /**
     * Test for separation of IN and INOUT/OUT parameter values
     */
    public void testInOutParameters() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE PROC #testInOut @in int, @out int output as SELECT @out = @out + @in");
        CallableStatement cstmt = con.prepareCall("{ call #testInOut ( ?,? ) }");
        cstmt.setInt(1, 1);
        cstmt.registerOutParameter(2, Types.INTEGER);
        cstmt.setInt(2, 2);
        cstmt.execute();
        assertEquals(3, cstmt.getInt(2));
        cstmt.execute();
        assertEquals(3, cstmt.getInt(2));
    }

    /**
     * Test that procedure names containing semicolons are parsed correctly.
     */
    public void testSemicolonProcedures() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE PROC #testInOut @in int, @out int output as SELECT @out = @out + @in");
        CallableStatement cstmt = con.prepareCall("{call #testInOut;1(?,?)}");
        cstmt.setInt(1, 1);
        cstmt.registerOutParameter(2, Types.INTEGER);
        cstmt.setInt(2, 2);
        cstmt.execute();
        assertEquals(3, cstmt.getInt(2));
        cstmt.execute();
        assertEquals(3, cstmt.getInt(2));
    }

    /**
     * Test that procedure calls with both literal parameters and parameterr
     * markers are executed correctly (bug [1078927] Callable statement fails).
     */
    public void testNonRpcProc1() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute(
                "create proc #testsp1 @p1 int, @p2 int out as set @p2 = @p1");
        stmt.close();

        CallableStatement cstmt = con.prepareCall("{call #testsp1(100, ?)}");
        cstmt.setInt(1, 1);
        cstmt.execute();
        cstmt.close();
    }

    /**
     * Test that procedure calls with both literal parameters and parameterr
     * markers are executed correctly (bug [1078927] Callable statement fails).
     */
    public void testNonRpcProc2() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("create proc #testsp2 @p1 int, @p2 int as return 99");
        stmt.close();

        CallableStatement cstmt = con.prepareCall("{?=call #testsp2(100, ?)}");
        cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
        cstmt.setInt(2, 2);
        cstmt.execute();
        assertEquals(99, cstmt.getInt(1));
        cstmt.close();
    }

    /**
     * Test for bug [1152329] Spurious output params assigned (TIMESTMP).
     * <p/>
     * If a stored procedure execute WRITETEXT or UPDATETEXT commands, spurious
     * output parameter data is returned to the client. This additional data
     * can be confused with the real output parameter data leading to an output
     * string parameter returning the text ?TIMESTMP? on SQL Server 7+ or
     * binary garbage on other servers.
     */
    public void testWritetext() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute(
                "create proc #testWritetext @p1 varchar(20) output as "
                + "begin "
                + "create table #test (id int, txt text) "
                + "insert into #test (id, txt) values(1, '') "
                + "declare @ptr binary(16) "
                + "select @ptr = (select textptr(txt) from #test where id = 1) "
                + "writetext #test.txt @ptr 'This is a test' "
                + "select @p1 = 'done' "
                + "end");
        stmt.close();

        CallableStatement cstmt = con.prepareCall("{call #testWritetext(?)}");
        cstmt.registerOutParameter(1, Types.VARCHAR);
        cstmt.execute();
        assertEquals("done", cstmt.getString(1));
        cstmt.close();
    }

    /**
     * Test for bug [1047208] SQLException chaining not implemented correctly:
     * checks that all errors are returned and that output variables are also
     * returned.
     */
    public void testErrorOutputParams() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE PROC #error_proc @p1 int out AS \r\n" +
                     "RAISERROR ('TEST EXCEPTION', 15, 1)\r\n" +
                     "SELECT @P1=100\r\n" +
                     "CREATE TABLE #DUMMY (id int)\r\n" +
                     "INSERT INTO #DUMMY VALUES(1)\r\n"+
                     "INSERT INTO #DUMMY VALUES(1)");
        stmt.close();

        CallableStatement cstmt = con.prepareCall("{call #error_proc(?)}");
        cstmt.registerOutParameter(1, Types.INTEGER);
        try {
            cstmt.execute();
            fail("Expecting exception");
        } catch (SQLException e) {
            assertEquals("TEST EXCEPTION", e.getMessage());
        }
        assertEquals(100, cstmt.getInt(1));
        cstmt.close();
    }

    public static void main(String[] args) {
        junit.textui.TestRunner.run(CallableStatementTest.class);
    }
}