timestamptest.java

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

        dropTable("#t0040");

        Statement stmt = cx.createStatement();
        stmt.executeUpdate("create table #t0040 ("
            + " c255 char(255)     not null, "
            + " v255 varchar(255)  not null) ");

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

        String along = getLongString('a');
        String blong = getLongString('b');

        pStmt.setString(1, along);
        pStmt.setString(2, along);
        int count = pStmt.executeUpdate();
        assertTrue(count == 1);
        pStmt.close();

        count = stmt.executeUpdate(""
            + "insert into #t0040 values ( "
            + "'" + blong + "', "
            + "'" + blong + "')");
        assertTrue(count == 1);

        pStmt = cx.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();

        cx.close();
    }

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

        dropTable("#t0041");

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

        PreparedStatement pStmt = cx.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();
        }
        assertTrue(count == rowsToAdd);
        pStmt.close();

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

        count = 0;
        while (rs.next())
        {
            rs.getString("s");
            count++;
        }
        assertTrue(count == rowsToAdd);

        cx.close();
    }

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

        dropTable("#t0042");

        Statement stmt = cx.createStatement();
        stmt.executeUpdate("create table #t0042 (s char(5) null, i integer null, j integer not null)");

        PreparedStatement pStmt = cx.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();
        assertTrue(count == 1);

        pStmt.setInt(2, 42);
        pStmt.setInt(3, 2);
        count = pStmt.executeUpdate();
        assertTrue(count == 1);
        pStmt.close();

        pStmt = cx.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());
        assertTrue(rs.getInt(1) == 42);
        assertTrue(!rs.wasNull());

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

        cx.close();
    }

    public void testResultSet0043() throws Exception
    {
        Connection cx = getConnection();
        Statement stmt = cx.createStatement();

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

            rs.getInt(1);

            assertTrue("Did not expect to reach here", false);
        }
        catch (SQLException e)
        {
            assertTrue(e.getMessage().startsWith("No current row in the ResultSet"));
        }
    }

    public void testResultSet0044() throws Exception
    {
        Connection cx = getConnection();
        Statement stmt = cx.createStatement();

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

        try
        {
            assertTrue("Expected no result set", !rs.next());
        }
        catch (SQLException e)
        {
            assertTrue(e.getMessage().startsWith("Invalid state"));
        }
    }

    public void testResultSet0045() throws Exception
    {
        try
        {
            Connection cx = getConnection();
            Statement stmt = cx.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());

            rs.getInt(1);

            assertTrue("Did not expect to reach here", false);
        }
        catch (java.sql.SQLException e)
        {
            assertTrue(e.getMessage().startsWith("No more results in ResultSet"));
        }
    }

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

        dropTable("#t0046");

        Statement stmt = cx.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)");
        assertTrue(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));

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

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

        dropTable("#t0047");

        Statement stmt = cx.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);
        assertTrue(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);
    }

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

        dropTable("#t0048");

        Statement stmt = cx.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 = cx.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();
        assertTrue(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);
    }

    public void testDecimalConversion0058() throws Exception
    {
        Connection cx = getConnection();
        Statement stmt = cx.createStatement();

        ResultSet rs = stmt.executeQuery("select convert(DECIMAL(4,0), 0)");
        assertNotNull(rs);
        assertTrue("Expected a result set", rs.next());
        assertTrue(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());
        assertTrue(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());
        assertTrue(rs.getInt(1) == -1);
        assertTrue("Expected no result set", !rs.next());
    }
}