timestamptest.java

Java写的TDS协议(JDBC/ODBC)实现

        int rowsToAdd = 8;
        final String theString = "abcdefghijklmnopqrstuvwxyz";
        int count = 0;
        for (int i = 1; i <= rowsToAdd; i++)
        {
            pStmt.setInt(1, i);
            pStmt.setString(2, theString.substring(0, i));

            count += pStmt.executeUpdate();
        }
        assertTrue(count == rowsToAdd);
        cx.rollback();

        stmt = cx.createStatement();
        ResultSet rs = stmt.executeQuery("select s, i from #t0010");
        assertNotNull(rs);

        count = 0;
        while (rs.next())
        {
            count++;
            assertTrue(rs.getString(1).trim().length() == rs.getInt(2));
        }
        assertTrue(count == 0);
        cx.commit();

        rowsToAdd = 6;
        for (int j = 1; j <= 2; j++)
        {
            count = 0;
            for (int i = 1; i <= rowsToAdd; i++)
            {
                pStmt.setInt(1, i + ((j-1) * rowsToAdd));
                pStmt.setString(2, theString.substring(0, i));

                count += pStmt.executeUpdate();
            }
            assertTrue(count == rowsToAdd);
            cx.commit();
        }

        rs = stmt.executeQuery("select s, i from #t0010");

        count = 0;
        while (rs.next())
        {
            count++;
            int i = rs.getInt(2);
            if (i > rowsToAdd)
                i -= rowsToAdd;
            assertTrue(rs.getString(1).trim().length() == i);
        }
        assertTrue(count == (2 * rowsToAdd));
        cx.commit();
        cx.setAutoCommit(true);
    }

    public void testEmptyResults0011() throws Exception
    {
        Connection cx = getConnection();

        dropTable("#t0011");

        Statement stmt = cx.createStatement();
        stmt.executeUpdate("create table #t0011 "
            + "  (mytime  datetime not null, "
            + "   mytime2 datetime null     )");

        ResultSet rs = stmt.executeQuery("select mytime, mytime2 from #t0011");
        assertNotNull(rs);
        assertTrue("Expected no result set", !rs.next());
        stmt.close();

        stmt = cx.createStatement();
        rs = stmt.executeQuery("select mytime, mytime2 from #t0011");
        assertTrue("Expected no result set", !rs.next());
        stmt.close();
    }

    public void testEmptyResults0012() throws Exception
    {
        Connection cx = getConnection();

        dropTable("#t0012");

        Statement stmt = cx.createStatement();
        stmt.executeUpdate("create table #t0012 "
            + "  (mytime  datetime not null, "
            + "   mytime2 datetime null     )");
//		cx.close();

//		cx = getConnection();
        PreparedStatement pStmt = cx.prepareStatement(
            "select mytime, mytime2 from #t0012");

        ResultSet rs = pStmt.executeQuery();
        assertNotNull(rs);
        assertTrue("Expected no result set", !rs.next());
        rs.close();

        rs = pStmt.executeQuery();
        assertTrue("Expected no result set", !rs.next());
        pStmt.close();
    }

    public void testEmptyResults0013() throws Exception
    {
        Connection cx = getConnection();

        dropTable("#t0013");

        Statement stmt = cx.createStatement();
        stmt.executeUpdate("create table #t0013 "
            + "  (mytime  datetime not null, "
            + "   mytime2 datetime null     )");

        ResultSet rs1 = stmt.executeQuery("select mytime, mytime2 from #t0013");
        assertNotNull(rs1);
        assertTrue("Expected no result set", !rs1.next());

        PreparedStatement pStmt = cx.prepareStatement(
            "select mytime, mytime2 from #t0013");
        ResultSet rs2 = pStmt.executeQuery();
        assertNotNull(rs2);
        assertTrue("Expected no result set", !rs2.next());
    }

    public void testForBrowse0014() throws Exception
    {
        Connection cx = getConnection();

        dropTable("#t0014");

        Statement stmt = cx.createStatement();
        stmt.executeUpdate("create table #t0014 (i integer not null)");

        PreparedStatement pStmt = cx.prepareStatement(
            "insert into #t0014 values (?)");

        final int rowsToAdd = 100;
        int count = 0;
        for (int i = 1; i <= rowsToAdd; i++)
        {
            pStmt.setInt(1, i);
            count += pStmt.executeUpdate();
        }
        assertTrue(count == rowsToAdd);
        pStmt.close();

        pStmt = cx.prepareStatement("select i from #t0014 for browse");
        ResultSet rs = pStmt.executeQuery();
        assertNotNull(rs);
        count = 0;
        while (rs.next())
        {
            int n = rs.getInt("i");
            count++;
        }
        assertTrue(count == rowsToAdd);
        pStmt.close();

        rs = stmt.executeQuery("select * from #t0014");
        assertNotNull(rs);
        count = 0;
        while (rs.next())
        {
            int n = rs.getInt("i");
            count++;
        }
        assertTrue(count == rowsToAdd);

        rs = stmt.executeQuery("select * from #t0014");
        assertNotNull(rs);
        count = 0;
        while (rs.next() && count < 5)
        {
            int n = rs.getInt("i");
            count++;
        }
        assertTrue(count == 5);

        rs = stmt.executeQuery("select * from #t0014");
        assertNotNull(rs);
        count = 0;
        while (rs.next())
        {
            int   n = rs.getInt("i");
            count++;
        }
        assertTrue(count == rowsToAdd);
        stmt.close();
    }

    public void testMultipleResults0015() throws Exception
    {
        Connection cx = getConnection();

        dropTable("#t0015");

        Statement stmt = cx.createStatement();
        stmt.executeUpdate("create table #t0015 "
            + "  (i  integer  not null,      "
            + "   s  char(10) not null)      ");

        PreparedStatement pStmt = cx.prepareStatement(
            "insert into #t0015 values (?, ?)");

        int rowsToAdd = 8;
        final String theString = "abcdefghijklmnopqrstuvwxyz";
        int count = 0;
        for (int i = 1; i <= rowsToAdd; i++)
        {
            pStmt.setInt(1, i);
            pStmt.setString(2, theString.substring(0, i));

            count += pStmt.executeUpdate();
        }
        assertTrue(count == rowsToAdd);
        pStmt.close();

        stmt = cx.createStatement();
        ResultSet rs = stmt.executeQuery("select s from #t0015 select i from #t0015");
        assertNotNull(rs);
        count = 0;
        while (rs.next())
        {
            count++;
        }
        assertTrue(count == rowsToAdd);

        assertTrue(stmt.getMoreResults());

        rs = stmt.getResultSet();
        assertNotNull(rs);
        count = 0;
        while (rs.next())
        {
            count++;
        }
        assertTrue(count == rowsToAdd);

        rs = stmt.executeQuery("select i, s from #t0015");
        count = 0;
        while (rs.next())
        {
            count++;
        }
        assertTrue(count == rowsToAdd);

        cx.close();
    }

    public void testMissingParameter0016() throws Exception
    {
        Connection cx = getConnection();

        dropTable("#t0016");

        Statement stmt = cx.createStatement();
        stmt.executeUpdate("create table #t0016 "
            + "  (i  integer  not null,      "
            + "   s  char(10) not null)      ");

        stmt = cx.createStatement();

        final int rowsToAdd = 20;
        int count = 0;
        for (int i = 1; i <= rowsToAdd; i++)
        {
            String sql = "insert into #t0016 values (" + i + ", 'row" + i + "')";
            count += stmt.executeUpdate(sql);
        }
        assertTrue(count == rowsToAdd);

        PreparedStatement   pStmt = cx.prepareStatement(
            "select s from #t0016 where i=? and s=?");

        // see what happens if neither is set
        try
        {
            ResultSet rs = pStmt.executeQuery();
            assertTrue("Failed to throw exception", false);
        }
        catch (SQLException e)
        {
            assertTrue(
                (e.getMessage().equals("parameter #1 has not been set")
                || e.getMessage().equals("parameter #2 has not been set")));
        }

        pStmt.clearParameters();
        try
        {
            pStmt.setInt(1, 7);
            pStmt.setString(2, "row7");
            pStmt.clearParameters();

            ResultSet rs = pStmt.executeQuery();
            assertTrue("Failed to throw exception", false);
        }
        catch (SQLException e)
        {
            assertTrue(
                (e.getMessage().equals("parameter #1 has not been set")
                || e.getMessage().equals("parameter #2 has not been set")));
        }

        pStmt.clearParameters();
        try
        {
            pStmt.setInt(1, 7);
            ResultSet rs = pStmt.executeQuery();
            assertTrue("Failed to throw exception", false);
        }
        catch (SQLException e)
        {
            assertTrue(e.getMessage().equals("parameter #2 has not been set"));
        }

        pStmt.clearParameters();
        try
        {
            pStmt.setString(2, "row7");
            ResultSet rs = pStmt.executeQuery();
            assertTrue("Failed to throw exception", false);
        }
        catch (SQLException e)
        {
            assertTrue(e.getMessage().equals("parameter #1 has not been set"));
        }
    }

    Object[][] getDatatypes()
    {
        return new Object[][]
        {
/*			{ "binary(272)",

            "0x101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f" +
            "101112131415161718191a1b1c1d1e1f",