timestamptest.java

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

        while (rs.next()) {
            count++;
            assertEquals(rs.getString(1).trim().length(), rs.getInt(2));
        }

        assertEquals(count, 0);

        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();
            }

            assertEquals(count, rowsToAdd);
            con.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;
            }

            assertEquals(rs.getString(1).trim().length(), i);
        }

        assertEquals(count, (2 * rowsToAdd));

        stmt.close();
        pstmt.close();
        con.setAutoCommit(true);
    }

    public void testEmptyResults0011() throws Exception {
        Statement stmt = con.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());

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

    public void testEmptyResults0012() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #t0012 "
            + "  (mytime  datetime not null, "
            + "   mytime2 datetime null     )");
        stmt.close();

        PreparedStatement pstmt = con.prepareStatement(
            "select mytime, mytime2 from #t0012");

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

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

    public void testEmptyResults0013() throws Exception {
        Statement stmt = con.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());
        stmt.close();

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

    public void testForBrowse0014() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #t0014 (i integer not null)");

        PreparedStatement pstmt = con.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();
        }

        assertEquals(count, rowsToAdd);
        pstmt.close();

        pstmt = con.prepareStatement("select i from #t0014 for browse");
        ResultSet rs = pstmt.executeQuery();
        assertNotNull(rs);
        count = 0;

        while (rs.next()) {
            rs.getInt("i");
            count++;
        }

        assertEquals(count, rowsToAdd);
        pstmt.close();

        rs = stmt.executeQuery("select * from #t0014");
        assertNotNull(rs);
        count = 0;

        while (rs.next()) {
            rs.getInt("i");
            count++;
        }

        assertEquals(count, rowsToAdd);

        rs = stmt.executeQuery("select * from #t0014");
        assertNotNull(rs);
        count = 0;

        while (rs.next() && count < 5) {
            rs.getInt("i");
            count++;
        }
        assertTrue(count == 5);

        rs = stmt.executeQuery("select * from #t0014");
        assertNotNull(rs);
        count = 0;

        while (rs.next()) {
            rs.getInt("i");
            count++;
        }

        assertEquals(count, rowsToAdd);
        stmt.close();
    }

    public void testMultipleResults0015() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #t0015 "
            + "  (i  integer  not null,      "
            + "   s  char(10) not null)      ");

        PreparedStatement pstmt = con.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();
        }

        assertEquals(count, rowsToAdd);
        pstmt.close();

		stmt.execute("select s from #t0015 select i from #t0015");
        ResultSet rs = stmt.getResultSet();
        assertNotNull(rs);
        count = 0;

        while (rs.next()) {
            count++;
        }

        assertEquals(count, rowsToAdd);

        assertTrue(stmt.getMoreResults());

        rs = stmt.getResultSet();
        assertNotNull(rs);
        count = 0;

        while (rs.next()) {
            count++;
        }

        assertEquals(count, rowsToAdd);

        rs = stmt.executeQuery("select i, s from #t0015");
        count = 0;

        while (rs.next()) {
            count++;
        }

        assertEquals(count, rowsToAdd);
        stmt.close();
    }

    public void testMissingParameter0016() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #t0016 "
            + "  (i  integer  not null,      "
            + "   s  char(10) not null)      ");

        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);
        }

        stmt.close();
        assertEquals(count, rowsToAdd);

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

        // see what happens if neither is set
        try {
            pstmt.executeQuery();
            assertTrue("Failed to throw exception", false);
        } catch (SQLException e) {
            assertTrue("07000".equals(e.getSQLState())
                    && (e.getMessage().indexOf('1') >= 0
                    || e.getMessage().indexOf('2') >= 0));
        }

        pstmt.clearParameters();

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

            pstmt.executeQuery();
            assertTrue("Failed to throw exception", false);
        } catch (SQLException e) {
            assertTrue("07000".equals(e.getSQLState())
                    && (e.getMessage().indexOf('1') >= 0
                    || e.getMessage().indexOf('2') >= 0));
        }

        pstmt.clearParameters();

        try {
            pstmt.setInt(1, 7);
            pstmt.executeQuery();
            assertTrue("Failed to throw exception", false);
        } catch (SQLException e) {
            assertTrue("07000".equals(e.getSQLState())
                    && e.getMessage().indexOf('2') >= 0);
        }

        pstmt.clearParameters();

        try {
            pstmt.setString(2, "row7");
            pstmt.executeQuery();
            assertTrue("Failed to throw exception", false);
        } catch (SQLException e) {
            assertTrue("07000".equals(e.getSQLState())
                    && e.getMessage().indexOf('1') >= 0);
        }

        pstmt.close();
    }

    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",

            new byte[] {
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
            0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f
            } },
*/
            {"float(6)",      "65.4321",                new BigDecimal("65.4321")},
            {"binary(5)",     "0x1213141516",           new byte[] { 0x12, 0x13, 0x14, 0x15, 0x16}},
            {"varbinary(4)",  "0x1718191A",             new byte[] { 0x17, 0x18, 0x19, 0x1A}},
            {"varchar(8)",    "'12345678'",             "12345678"},
            {"datetime",      "'19990815 21:29:59.01'", Timestamp.valueOf("1999-08-15 21:29:59.01")},
            {"smalldatetime", "'19990215 20:45'",       Timestamp.valueOf("1999-02-15 20:45:00")},
            {"float(6)",      "65.4321",                new Float(65.4321)/* new BigDecimal("65.4321") */},
            {"float(14)",     "1.123456789",            new Double(1.123456789) /*new BigDecimal("1.123456789") */},
            {"real",          "7654321.0",              new Double(7654321.0)},
            {"int",           "4097",                   new Integer(4097)},
            {"float(6)",      "65.4321",                new BigDecimal("65.4321")},
            {"float(14)",     "1.123456789",            new BigDecimal("1.123456789")},
            {"decimal(10,3)", "1234567.089",            new BigDecimal("1234567.089")},
            {"numeric(5,4)",  "1.2345",                 new BigDecimal("1.2345")},
            {"smallint",      "4094",                   new Short((short) 4094)},
            // {"tinyint",       "127",                    new Byte((byte) 127)},
            // {"tinyint",       "-128",                   new Byte((byte) -128)},
            {"tinyint",       "127",                    new Byte((byte) 127)},
            {"tinyint",       "128",                    new Short((short) 128)},
            {"money",         "19.95",                  new BigDecimal("19.95")},
            {"smallmoney",    "9.97",                   new BigDecimal("9.97")},
            {"bit",           "1",                      Boolean.TRUE},
//			{ "text",          "'abcedefg'",             "abcdefg" },