astest.java

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

/*
 * AsTest.java
 *
 * Created on 10. September 2001, 09:44
 */
package net.sourceforge.jtds.test;

import java.sql.*;
import java.math.BigDecimal;

import junit.framework.TestSuite;

import net.sourceforge.jtds.util.Logger;

/**
 *
 * @author  builder
 * @version 1.0
 */
public class AsTest extends DatabaseTestCase {

    public AsTest(String name) {
        super(name);
    }

    public static void main(String args[]) {
        Logger.setActive(true);
        if (args.length > 0) {
            junit.framework.TestSuite s = new TestSuite();
            for (int i = 0; i < args.length; i++) {
                s.addTest(new AsTest(args[i]));
            }
            junit.textui.TestRunner.run(s);
        } else
            junit.textui.TestRunner.run(AsTest.class);
    }

    public void testProc1() throws Exception {
        Statement stmt = con.createStatement();
        dropProcedure(stmt, "#spTestExec");
        dropProcedure(stmt, "#spTestExec2");

        stmt.executeUpdate(" create procedure #spTestExec2 as " +
                           "select 'Did it work?' as Result");
        stmt.executeUpdate("create procedure #spTestExec as " +
                           "set nocount off " +
                           "create table #tmp ( Result varchar(50) ) " +
                           "insert #tmp execute #spTestExec2 " +
                           "select * from #tmp");
        stmt.close();

        CallableStatement cstmt = con.prepareCall("#spTestExec");
        assertFalse(cstmt.execute());
        assertEquals(1, cstmt.getUpdateCount());

        // The JDBC-ODBC driver does not return update counts from stored
        // procedures so we won't, either.
        //
        // SAfe Yes, we will. It seems like that's how it should work. The idea
        //      however is to only return valid update counts (e.g. not from
        //      SET, EXEC or such).
        assertTrue(cstmt.getMoreResults());

        boolean passed = false;
        ResultSet rs = cstmt.getResultSet();
        while (rs.next()) {
            passed = true;
        }
        assertTrue("Expecting at least one result row", passed);
        assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == -1);
        cstmt.close();
        // stmt.executeQuery("execute spTestExec");
    }

    public void testProc2() throws Exception {
        Statement stmt = con.createStatement();
        String sqlwithcount =
        "create procedure #multi1withcount as " +
        "  set nocount off " +
        "  select 'a' " +
        "  select 'b' " +
        "  create table #multi1withcountt (A VARCHAR(20)) " +
        "  insert into #multi1withcountt VALUES ('a') " +
        "  insert into #multi1withcountt VALUES ('a') " +
        "  insert into #multi1withcountt VALUES ('a') " +
        "  select 'a' " +
        "  select 'b' ";
        String sqlnocount =
        "create procedure #multi1nocount as " +
        "  set nocount on " +
        "  select 'a' " +
        "  select 'b' " +
        "  create table #multi1nocountt (A VARCHAR(20)) " +
        "  insert into #multi1nocountt VALUES ('a') " +
        "  insert into #multi1nocountt VALUES ('a') " +
        "  insert into #multi1nocountt VALUES ('a') " +
        "  select 'a' " +
        "  select 'b' ";
        dropProcedure(stmt, "#multi1withcount");
        dropProcedure(stmt, "#multi1nocount");
        stmt.executeUpdate(sqlwithcount);
        stmt.executeUpdate(sqlnocount);
        stmt.close();

        CallableStatement cstmt = con.prepareCall("#multi1nocount");
        assertTrue(cstmt.execute());
        ResultSet rs = cstmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(rs.getString(1).equals("a"));
        assertTrue(!rs.next());
        assertTrue(cstmt.getMoreResults());
        rs = cstmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(rs.getString(1).equals("b"));
        assertTrue(!rs.next());
        assertTrue(cstmt.getMoreResults());
        rs = cstmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(!rs.next());
        assertTrue(cstmt.getMoreResults());
        rs = cstmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(!rs.next());
        assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == -1);
        cstmt.close();

        cstmt = con.prepareCall("#multi1withcount");

        // The JDBC-ODBC driver does not return update counts from stored
        // procedures so we won't, either.
        //
        // SAfe Yes, we will. It seems like that's how it should work. The idea
        //      however is to only return valid update counts (e.g. not from
        //      SET, EXEC or such).
        assertTrue(cstmt.execute());
        rs = cstmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(rs.getString(1).equals("a"));
        assertTrue(!rs.next());
        assertTrue(cstmt.getMoreResults());
        rs = cstmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(rs.getString(1).equals("b"));
        assertTrue(!rs.next());
        assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == 1);  // insert
        assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == 1);  // insert
        assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == 1);  // insert
        assertTrue(cstmt.getMoreResults());    // select
        rs = cstmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(!rs.next());
        assertTrue(cstmt.getMoreResults());
        rs = cstmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(!rs.next());
        assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == -1);
        cstmt.close();

    }

    public void testBatch1() throws Exception {
        Statement stmt = con.createStatement();
        String sqlwithcount1 =
        "  set nocount off " +
        "  select 'a' " +
        "  select 'b' " +
        "  create table #multi2withcountt (A VARCHAR(20)) " +
        "  insert into #multi2withcountt VALUES ('a') " +
        "  insert into #multi2withcountt VALUES ('a') " +
        "  insert into #multi2withcountt VALUES ('a') " +
        "  select 'a' " +
        "  select 'b' " +
        "  drop table #multi2withcountt";
        String sqlnocount1 =
        "  set nocount on " +
        "  select 'a' " +
        "  select 'b' " +
        "  create table #multi2nocountt (A VARCHAR(20)) " +
        "  insert into #multi2nocountt VALUES ('a') " +
        "  insert into #multi2nocountt VALUES ('a') " +
        "  insert into #multi2nocountt VALUES ('a') " +
        "  select 'a' " +
        "  select 'b' " +
        "  drop table #multi2nocountt";
        assertTrue(stmt.execute(sqlwithcount1));    // set
        ResultSet rs = stmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(rs.getString(1).equals("a"));
        assertTrue(!rs.next());
        assertTrue(stmt.getMoreResults());
        rs = stmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(rs.getString(1).equals("b"));
        assertTrue(!rs.next());
        assertTrue(!stmt.getMoreResults() && stmt.getUpdateCount() == 1);
        assertTrue(!stmt.getMoreResults() && stmt.getUpdateCount() == 1);
        assertTrue(!stmt.getMoreResults() && stmt.getUpdateCount() == 1);
        assertTrue(stmt.getMoreResults());
        rs = stmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(!rs.next());
        assertTrue(stmt.getMoreResults());
        rs = stmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(!rs.next());
        assertTrue(!stmt.getMoreResults() && stmt.getUpdateCount() == -1);

        assertTrue(stmt.execute(sqlnocount1));    // set
        rs = stmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(rs.getString(1).equals("a"));
        assertTrue(!rs.next());
        assertTrue(stmt.getMoreResults());
        rs = stmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(rs.getString(1).equals("b"));
        assertTrue(!rs.next());
        assertTrue(stmt.getMoreResults());    // select
        rs = stmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(!rs.next());
        assertTrue(stmt.getMoreResults());
        rs = stmt.getResultSet();
        assertTrue(rs.next());
        assertTrue(!rs.next());
        assertTrue(!stmt.getMoreResults() && stmt.getUpdateCount() == -1);
        stmt.close();
    }

    public void testBug457955() throws Exception {
        Statement stmt = con.createStatement();
        dropProcedure("#Bug457955");
        stmt.executeUpdate("  create procedure #Bug457955 (@par1 VARCHAR(10)) as select @par1");
        stmt.close();
        String param = "123456789";
        CallableStatement cstmt = con.prepareCall("exec #Bug457955 ?");
        cstmt.setString(1, param);
        cstmt.executeQuery();
        cstmt.close();
    }


    public void testBugAttTest2() throws Exception {
        String tabdef =
        "CREATE TABLE #ICEributeTest_AttributeTest2( " +
        "  ICEobjectId NUMERIC(19) " +
        "     /*CONSTRAINT ICEributeTest_AttributeTest2_PKICEobjectId PRIMARY KEY */ " +
        "    ,  " +
        "  ICEtestShort INTEGER  " +