callablestatementtest.java

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

    /**
     * Test for incorrect exception thrown/no exception thrown when invalid
     * call escape is used.
     * <p/>
     * A message containing the correct missing terminator should be generated.
     */
    public void testCallableStatementParsing7() throws SQLException {
        try {
            con.prepareCall("{call sp_test(?, ?}");
            fail("Was expecting an invalid escape error");
        } catch (SQLException ex) {
            assertEquals("22025", ex.getSQLState());
            assertTrue(ex.getMessage().indexOf(')') != -1);
        }
    }

    /**
     * Test for reature request [956800] setNull(): Not implemented.
     */
    public void testCallableSetNull1() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE #callablesetnull1 (data CHAR(1) NULL)");
        stmt.close();

        try {
            stmt = con.createStatement();
            stmt.execute("create procedure #procCallableSetNull1 @data char(1) "
            		+ "as INSERT INTO #callablesetnull1 (data) VALUES (@data)");
            stmt.close();

            CallableStatement cstmt = con.prepareCall("{call #procCallableSetNull1(?)}");
            // Test CallableStatement.setNull(int,Types.NULL)
            cstmt.setNull(1, Types.NULL);
            cstmt.execute();
            cstmt.close();

            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT data FROM #callablesetnull1");

            assertTrue(rs.next());

            // Test ResultSet.getString()
            assertNull(rs.getString(1));
            assertTrue(rs.wasNull());

            assertTrue(!rs.next());
            stmt.close();
            rs.close();
        } finally {
            stmt = con.createStatement();
            stmt.execute("drop procedure #procCallableSetNull1");
            stmt.close();
        }
    }

    /**
     * Test for bug [974284] retval on callable statement isn't handled correctly
     */
    public void testCallableRegisterOutParameter1() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("create procedure #rop1 @a varchar(1), @b varchar(1) as\r\n "
                     + "begin\r\n"
                     + "return 1\r\n"
                     + "end");
        stmt.close();

        CallableStatement cstmt = con.prepareCall("{? = call #rop1(?, ?)}");

        cstmt.registerOutParameter(1, Types.INTEGER);
        cstmt.setString(2, "a");
        cstmt.setString(3, "b");
        cstmt.execute();

        assertEquals(1, cstmt.getInt(1));
        assertEquals("1", cstmt.getString(1));

        cstmt.close();
    }

    /**
     * Test for bug [994888] Callable statement and Float output parameter
     */
    public void testCallableRegisterOutParameter2() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("create procedure #rop2 @data float OUTPUT as\r\n "
                     + "begin\r\n"
                     + "set @data = 1.1\r\n"
                     + "end");
        stmt.close();

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

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

        assertTrue(cstmt.getFloat(1) == 1.1f);
        cstmt.close();
    }

    /**
     * Test for bug [994988] Network error when null is returned via int output parm
     */
    public void testCallableRegisterOutParameter3() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("create procedure #rop3 @data int OUTPUT as\r\n "
                     + "begin\r\n"
                     + "set @data = null\r\n"
                     + "end");
        stmt.close();

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

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

        cstmt.getInt(1);
        assertTrue(cstmt.wasNull());
        cstmt.close();
    }

    /**
     * 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();

            cstmt = con.prepareCall("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);