timestamptest.java

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

            + "insert into #t0040 values ( "
            + "'" + blong + "', "
            + "'" + blong + "')");
        assertEquals(count, 1);

        pstmt = con.prepareStatement("select c255, v255 from #t0040 order by c255");
        ResultSet rs = pstmt.executeQuery();
        assertNotNull(rs);

        assertTrue("Expected a result set", rs.next());
        assertEquals(rs.getString("c255"), along);
        assertEquals(rs.getString("v255"), along);

        assertTrue("Expected a result set", rs.next());
        assertEquals(rs.getString("c255"), blong);
        assertEquals(rs.getString("v255"), blong);

        assertTrue("Expected no result set", !rs.next());
        pstmt.close();

        rs = stmt.executeQuery("select c255, v255 from #t0040 order by c255");
        assertNotNull(rs);

        assertTrue("Expected a result set", rs.next());
        assertEquals(rs.getString("c255"), along);
        assertEquals(rs.getString("v255"), along);

        assertTrue("Expected a result set", rs.next());
        assertEquals(rs.getString("c255"), blong);
        assertEquals(rs.getString("v255"), blong);

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

    public void testPreparedStatement0041() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #t0041 "
            + "  (i  integer  not null, "
            + "   s  text     not null) ");

        PreparedStatement pstmt = con.prepareStatement("insert into #t0041 values (?, ?)");

        // TODO: Check values
        final int rowsToAdd = 400;
        final String theString = getLongString(400);
        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(rowsToAdd, count);
        pstmt.close();

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

        count = 0;

        while (rs.next()) {
            rs.getString("s");
            count++;
        }

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

    public void testPreparedStatement0042() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #t0042 (s char(5) null, i integer null, j integer not null)");
        stmt.close();

        PreparedStatement pstmt = con.prepareStatement("insert into #t0042 (s, i, j) values (?, ?, ?)");

        pstmt.setString(1, "hello");
        pstmt.setNull(2, java.sql.Types.INTEGER);
        pstmt.setInt(3, 1);

        int count = pstmt.executeUpdate();
        assertEquals(count, 1);

        pstmt.setInt(2, 42);
        pstmt.setInt(3, 2);
        count = pstmt.executeUpdate();
        assertEquals(count, 1);
        pstmt.close();

        pstmt = con.prepareStatement("select i from #t0042 order by j");
        ResultSet rs = pstmt.executeQuery();
        assertNotNull(rs);

        assertTrue("Expected a result set", rs.next());
        rs.getInt(1);
        assertTrue(rs.wasNull());

        assertTrue("Expected a result set", rs.next());
        assertEquals(rs.getInt(1), 42);
        assertTrue(!rs.wasNull());

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

    public void testResultSet0043() throws Exception {
        Statement stmt = con.createStatement();

        try {
            ResultSet rs = stmt.executeQuery("select 1");
            assertNotNull(rs);
            rs.getInt(1);

            fail("Did not expect to reach here");
        } catch (SQLException e) {
            assertEquals("24000", e.getSQLState());
        }

        stmt.close();
    }

    public void testResultSet0044() throws Exception {
        Statement stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery("select 1");
        assertNotNull(rs);
        rs.close();

        try {
            rs.next();
            fail("Was expecting ResultSet.next() to throw an exception if the ResultSet was closed");
        } catch (SQLException e) {
            assertEquals("HY010", e.getSQLState());
        }

        stmt.close();
    }

    public void testResultSet0045() throws Exception {
        Statement stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery("select 1");
        assertNotNull(rs);

        assertTrue("Expected a result set", rs.next());
        rs.getInt(1);

        assertTrue("Expected no result set", !rs.next());

        try {
            rs.getInt(1);
            fail("Did not expect to reach here");
        } catch (java.sql.SQLException e) {
            assertEquals("24000", e.getSQLState());
        }

        stmt.close();
    }

    public void testMetaData0046() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #t0046 ("
            + "   i integer identity, "
            + "   a integer not null, "
            + "   b integer null ) ");

        int count = stmt.executeUpdate("insert into #t0046 (a, b) values (-2, -3)");
        assertEquals(count, 1);

        ResultSet rs = stmt.executeQuery("select i, a, b, 17 c from #t0046");
        assertNotNull(rs);

        ResultSetMetaData md = rs.getMetaData();
        assertNotNull(md);

        assertTrue(md.isAutoIncrement(1));
        assertTrue(!md.isAutoIncrement(2));
        assertTrue(!md.isAutoIncrement(3));
        assertTrue(!md.isAutoIncrement(4));

        assertTrue(md.isReadOnly(1));
        assertTrue(!md.isReadOnly(2));
        assertTrue(!md.isReadOnly(3));
//        assertTrue(md.isReadOnly(4)); SQL 6.5 does not report this one correctly!

        assertEquals(md.isNullable(1),java.sql.ResultSetMetaData.columnNoNulls);
        assertEquals(md.isNullable(2),java.sql.ResultSetMetaData.columnNoNulls);
        assertEquals(md.isNullable(3),java.sql.ResultSetMetaData.columnNullable);
        // assert(md.isNullable(4) == java.sql.ResultSetMetaData.columnNoNulls);

        rs.close();
        stmt.close();
    }

    public void testTimestamps0047() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate(
            "create table #t0047 " +
            "(                               " +
            "  t1   datetime not null,       " +
            "  t2   datetime null,           " +
            "  t3   smalldatetime not null,  " +
            "  t4   smalldatetime null       " +
            ")");

        String query =
            "insert into #t0047 (t1, t2, t3, t4) " +
            " values('2000-01-02 19:35:01.333', " +
            "        '2000-01-02 19:35:01.333', " +
            "        '2000-01-02 19:35:01.333', " +
            "        '2000-01-02 19:35:01.333'  " +
            ")";
        int count = stmt.executeUpdate(query);

        assertEquals(count, 1);

        ResultSet rs = stmt.executeQuery("select t1, t2, t3, t4 from #t0047");
        assertNotNull(rs);

        assertTrue("Expected a result set", rs.next());

        java.sql.Timestamp  t1 = rs.getTimestamp("t1");
        java.sql.Timestamp  t2 = rs.getTimestamp("t2");
        java.sql.Timestamp  t3 = rs.getTimestamp("t3");
        java.sql.Timestamp  t4 = rs.getTimestamp("t4");

        java.sql.Timestamp r1 = Timestamp.valueOf("2000-01-02 19:35:01.333");
        java.sql.Timestamp r2 = Timestamp.valueOf("2000-01-02 19:35:00");

        assertEquals(r1, t1);
        assertEquals(r1, t2);
        assertEquals(r2, t3);
        assertEquals(r2, t4);

        stmt.close();
    }

    public void testTimestamps0048() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate(
            "create table #t0048              " +
            "(                               " +
            "  t1   datetime not null,       " +
            "  t2   datetime null,           " +
            "  t3   smalldatetime not null,  " +
            "  t4   smalldatetime null       " +
            ")");

        java.sql.Timestamp r1;
        java.sql.Timestamp r2;
        r1 = Timestamp.valueOf("2000-01-02 19:35:01");
        r2 = Timestamp.valueOf("2000-01-02 19:35:00");

        java.sql.PreparedStatement pstmt = con.prepareStatement(
            "insert into #t0048 (t1, t2, t3, t4) values(?, ?, ?, ?)");

        pstmt.setTimestamp(1, r1);
        pstmt.setTimestamp(2, r1);
        pstmt.setTimestamp(3, r1);
        pstmt.setTimestamp(4, r1);

        int count = pstmt.executeUpdate();
        assertEquals(count, 1);
        pstmt.close();

        ResultSet rs = stmt.executeQuery("select t1, t2, t3, t4 from #t0048");
        assertNotNull(rs);

        assertTrue("Expected a result set", rs.next());
        java.sql.Timestamp  t1 = rs.getTimestamp("t1");
        java.sql.Timestamp  t2 = rs.getTimestamp("t2");
        java.sql.Timestamp  t3 = rs.getTimestamp("t3");
        java.sql.Timestamp  t4 = rs.getTimestamp("t4");

        assertEquals(r1, t1);
        assertEquals(r1, t2);
        assertEquals(r2, t3);
        assertEquals(r2, t4);

        stmt.close();
    }

    public void testDecimalConversion0058() throws Exception {
        Statement stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery("select convert(DECIMAL(4,0), 0)");
        assertNotNull(rs);
        assertTrue("Expected a result set", rs.next());
        assertEquals(rs.getInt(1), 0);
        assertTrue("Expected no result set", !rs.next());

        rs = stmt.executeQuery("select convert(DECIMAL(4,0), 1)");
        assertNotNull(rs);
        assertTrue("Expected a result set", rs.next());
        assertEquals(rs.getInt(1), 1);
        assertTrue("Expected no result set", !rs.next());

        rs = stmt.executeQuery("select convert(DECIMAL(4,0), -1)");
        assertNotNull(rs);
        assertTrue("Expected a result set", rs.next());
        assertEquals(rs.getInt(1), -1);
        assertTrue("Expected no result set", !rs.next());

        stmt.close();
    }

    /**
     * Test for bug [994916] datetime decoding in TdsData.java
     */
    public void testDatetimeRounding1() throws Exception {
        // Per the SQL Server documentation
        // Send:    01/01/98 23:59:59.990
        // Receive: 01/01/98 23:59:59.990
        Calendar sendValue = Calendar.getInstance();
        Calendar receiveValue = Calendar.getInstance();

        sendValue.set(Calendar.MONTH, Calendar.JANUARY);
        sendValue.set(Calendar.DAY_OF_MONTH, 1);
        sendValue.set(Calendar.YEAR, 1998);
        sendValue.set(Calendar.HOUR_OF_DAY, 23);
        sendValue.set(Calendar.MINUTE, 59);
        sendValue.set(Calendar.SECOND, 59);
        sendValue.set(Calendar.MILLISECOND, 990);

        receiveValue.set(Calendar.MONTH, Calendar.JANUARY);
        receiveValue.set(Calendar.DAY_OF_MONTH, 1);
        receiveValue.set(Calendar.YEAR, 1998);
        receiveValue.set(Calendar.HOUR_OF_DAY, 23);
        receiveValue.set(Calendar.MINUTE, 59);
        receiveValue.set(Calendar.SECOND, 59);
        receiveValue.set(Calendar.MILLISECOND, 990);

        Statement stmt = con.createStatement();
        stmt.execute("create table #dtr1 (data datetime)");
        stmt.close();

        PreparedStatement pstmt = con.prepareStatement("insert into #dtr1 (data) values (?)");
        pstmt.setTimestamp(1, new Timestamp(sendValue.getTime().getTime()));
        assertEquals(pstmt.executeUpdate(), 1);
        pstmt.close();

        pstmt = con.prepareStatement("select data from #dtr1");
        ResultSet rs = pstmt.executeQuery();

        assertTrue(rs.next());
        assertEquals(receiveValue.getTime().getTime(), getTimeInMs(rs));
        assertTrue(!rs.next());

        pstmt.close();
        rs.close();
    }

    /**
     * Test for bug [994916] datetime decoding in TdsData.java
     */
    public void testDatetimeRounding2() throws Exception {
        // Per the SQL Server documentation
        // Send:    01/01/98 23:59:59.991
        // Receive: 01/01/98 23:59:59.990
        Calendar sendValue = Calendar.getIn