csunittest.java

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

        int numberOfUpdates=0;


        isResultSet = stmt.execute("EXEC sp_who");
        output.println("execute(EXEC sp_who) returned: " + isResultSet);

        updateCount=stmt.getUpdateCount();

        while (isResultSet || (updateCount!=-1)) {
            if (isResultSet) {
                resultSetCount++;
                rs = stmt.getResultSet();

                ResultSetMetaData rsMeta =  rs.getMetaData();
                int columnCount = rsMeta.getColumnCount();
                output.println("columnCount: " +
                               Integer.toString(columnCount));
                for (int n=1; n<= columnCount; n++) {
                    output.println(Integer.toString(n) + ": " +
                                   rsMeta.getColumnName(n));
                }

                while (rs.next()) {
                    rowCount++;
                    for (int n=1; n<= columnCount; n++) {
                        output.println(Integer.toString(n) + ": " +
                                       rs.getString(n));
                    }
                }

            } else {
                numberOfUpdates += updateCount;
                output.println("UpdateCount: " +
                               Integer.toString(updateCount));
            }
            isResultSet=stmt.getMoreResults();
            updateCount = stmt.getUpdateCount();
        }
        stmt.close();

        output.println("resultSetCount: " + resultSetCount);
        output.println("Total rowCount: " + rowCount);
        output.println("Number of updates: " + numberOfUpdates);


        assertTrue((rowCount>=1) && (numberOfUpdates==0) && (resultSetCount==1));
    }
    public void testxx0029() throws Exception {
        Statement   stmt = con.createStatement();
        ResultSet   rs;

        boolean isResultSet;
        int updateCount;

        int resultSetCount=0;
        int rowCount=0;
        int numberOfUpdates=0;


        output.println("before execute DROP PROCEDURE");

        try {
            isResultSet =stmt.execute("DROP PROCEDURE #t0029_p1");
            updateCount = stmt.getUpdateCount();
            do {
                output.println("DROP PROCEDURE isResultSet: " + isResultSet);
                output.println("DROP PROCEDURE updateCount: " + updateCount);
                isResultSet = stmt.getMoreResults();
                updateCount = stmt.getUpdateCount();
            } while (((updateCount!=-1) && !isResultSet) || isResultSet);
        } catch (SQLException e) {
        }

        try {
            isResultSet =stmt.execute("DROP PROCEDURE #t0029_p2");
            updateCount = stmt.getUpdateCount();
            do {
                output.println("DROP PROCEDURE isResultSet: " + isResultSet);
                output.println("DROP PROCEDURE updateCount: " + updateCount);
                isResultSet = stmt.getMoreResults();
                updateCount = stmt.getUpdateCount();
            } while (((updateCount!=-1) && !isResultSet) || isResultSet);
        } catch (SQLException e) {
        }


        dropTable("#t0029_t1");

        isResultSet =
        stmt.execute(
                    " create table #t0029_t1                       " +
                    "  (t1 datetime not null,                     " +
                    "   t2 datetime null,                         " +
                    "   t3 smalldatetime not null,                " +
                    "   t4 smalldatetime null,                    " +
                    "   t5 text null)                             ");
        updateCount = stmt.getUpdateCount();
        do {
            output.println("CREATE TABLE isResultSet: " + isResultSet);
            output.println("CREATE TABLE updateCount: " + updateCount);
            isResultSet = stmt.getMoreResults();
            updateCount = stmt.getUpdateCount();
        } while (((updateCount!=-1) && !isResultSet) || isResultSet);


        isResultSet =
        stmt.execute(
                    "CREATE PROCEDURE #t0029_p1 AS                " +

                    " insert into #t0029_t1 values                " +
                    " ('1999-01-07', '1998-09-09 15:35:05',       " +
                    " getdate(), '1998-09-09 15:35:00', null)     " +

                    " update #t0029_t1 set t1='1999-01-01'         " +

                    " insert into #t0029_t1 values                " +
                    " ('1999-01-08', '1998-09-09 15:35:05',       " +
                    " getdate(), '1998-09-09 15:35:00','456')     " +

                    " update #t0029_t1 set t2='1999-01-02'        " +

                    " declare @ptr varbinary(16)                  " +
                    " select @ptr=textptr(t5) from #t0029_t1      " +
                    "   where t1='1999-01-08'                     " +
                    " writetext #t0029_t1.t5 @ptr with log '123'  ");

        updateCount = stmt.getUpdateCount();
        do {
            output.println("CREATE PROCEDURE isResultSet: " + isResultSet);
            output.println("CREATE PROCEDURE updateCount: " + updateCount);
            isResultSet = stmt.getMoreResults();
            updateCount = stmt.getUpdateCount();
        } while (((updateCount!=-1) && !isResultSet) || isResultSet);


        isResultSet =
        stmt.execute(
                    "CREATE PROCEDURE #t0029_p2 AS                " +

                    " set nocount on " +
                    " EXEC #t0029_p1                              " +
                    " SELECT * FROM #t0029_t1                     ");

        updateCount = stmt.getUpdateCount();
        do {
            output.println("CREATE PROCEDURE isResultSet: " + isResultSet);
            output.println("CREATE PROCEDURE updateCount: " + updateCount);
            isResultSet = stmt.getMoreResults();
            updateCount = stmt.getUpdateCount();
        } while (((updateCount!=-1) && !isResultSet) || isResultSet);


        isResultSet = stmt.execute( "EXEC  #t0029_p2  ");

        output.println("execute(EXEC #t0029_p2) returned: " + isResultSet);

        updateCount=stmt.getUpdateCount();

        while (isResultSet || (updateCount!=-1)) {
            if (isResultSet) {
                resultSetCount++;
                rs = stmt.getResultSet();

                ResultSetMetaData rsMeta =  rs.getMetaData();
                int columnCount = rsMeta.getColumnCount();
                output.println("columnCount: " +
                               Integer.toString(columnCount));
                for (int n=1; n<= columnCount; n++) {
                    output.println(Integer.toString(n) + ": " +
                                   rsMeta.getColumnName(n));
                }

                while (rs.next()) {
                    rowCount++;
                    for (int n=1; n<= columnCount; n++) {
                        output.println(Integer.toString(n) + ": " +
                                       rs.getString(n));
                    }
                }

            } else {
                numberOfUpdates += updateCount;
                output.println("UpdateCount: " +
                               Integer.toString(updateCount));
            }
            isResultSet=stmt.getMoreResults();
            updateCount = stmt.getUpdateCount();
        }
        stmt.close();

        output.println("resultSetCount: " + resultSetCount);
        output.println("Total rowCount: " + rowCount);
        output.println("Number of updates: " + numberOfUpdates);


        assertTrue((resultSetCount==1) &&
                   (rowCount==2) &&
                   (numberOfUpdates==0));
    }

    public void testDataTypesByResultSetMetaData0030() throws Exception {
        Statement   stmt = con.createStatement();
        ResultSet   rs;

        String sql = ("select " +
                      " convert(tinyint, 2),  " +
                      " convert(smallint, 5)  ");

        rs = stmt.executeQuery(sql);
        if (!rs.next()) {
            fail("Expecting one row");
        } else {
            ResultSetMetaData meta = rs.getMetaData();

            if (meta.getColumnType(1)!=java.sql.Types.TINYINT) {
                fail("tinyint column was read as "
                               + meta.getColumnType(1));
            }
            if (meta.getColumnType(2)!=java.sql.Types.SMALLINT) {
                fail("smallint column was read as "
                               + meta.getColumnType(2));
            }
            if (rs.getInt(1) != 2) {
                fail("Bogus value read for tinyint");
            }
            if (rs.getInt(2) != 5) {
                fail("Bogus value read for smallint");
            }
        }
        stmt.close();
    }
    public void testTextColumns0031() throws Exception {
        Statement   stmt = con.createStatement();

        assertEquals(0, stmt.executeUpdate(
                "create table #t0031                " +
                "  (t_nullable      text null,     " +
                "   t_notnull       text not null, " +
                "   i               int not null)  "));

        stmt.executeUpdate("insert into #t0031 values(null, '',   1)");
        stmt.executeUpdate("insert into #t0031 values(null, 'b1', 2)");
        stmt.executeUpdate("insert into #t0031 values('',   '',   3)");
        stmt.executeUpdate("insert into #t0031 values('',   'b2', 4)");
        stmt.executeUpdate("insert into #t0031 values('a1', '',   5)");
        stmt.executeUpdate("insert into #t0031 values('a2', 'b3', 6)");

        ResultSet  rs = stmt.executeQuery("select * from #t0031 " +
                                          " order by i ");

        assertTrue(rs.next());
        assertEquals(null, rs.getString(1));
        assertEquals("", rs.getString(2));
        assertEquals(1, rs.getInt(3));

        assertTrue(rs.next());
        assertEquals(null, rs.getString(1));
        assertEquals("b1", rs.getString(2));
        assertEquals(2, rs.getInt(3));

        assertTrue(rs.next());
        assertEquals("", rs.getString(1));
        assertEquals("", rs.getString(2));
        assertEquals(3, rs.getInt(3));

        assertTrue(rs.next());
        assertEquals("", rs.getString(1));
        assertEquals("b2", rs.getString(2));
        assertEquals(4, rs.getInt(3));

        assertTrue(rs.next());
        assertEquals("a1", rs.getString(1));
        assertEquals("", rs.getString(2));
        assertEquals(5, rs.getInt(3));

        assertTrue(rs.next());
        assertEquals("a2", rs.getString(1));
        assertEquals("b3", rs.getString(2));
        assertEquals(6, rs.getInt(3));

        stmt.close();
    }

    public void testSpHelpSysUsers0032() throws Exception {
        Statement   stmt = con.createStatement();
        boolean   passed = true;
        boolean   isResultSet;
        boolean   done;
        int       i;
        int       updateCount;

        output.println("Starting test #t0032-  test sp_help sysusers");

        isResultSet = stmt.execute("sp_help sysusers");

        output.println("Executed the statement.  rc is " + isResultSet);

        do {
            if (isResultSet) {
                output.println("About to call getResultSet");
                ResultSet          rs   = stmt.getResultSet();
                ResultSetMetaData  meta = rs.getMetaData();
                updateCount = 0;
                while (rs.next()) {