suntest.java

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

        try {
            pstmt.setObject(1, new java.util.Date(tval));
            fail("No exception for setObject(java.util.Date)");
        } catch (SQLException e) {
            // OK unsupported object type trapped
        }

        pstmt.close();
        stmt.close();
    }

    /**
     * Test for bug [ 1012301 ] 0.9-rc1: Prepared statement execution error.
     *
     * @throws Exception
     */
    public void testPrepStmtError() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE #PERR (val VARCHAR(255))\r\n" +
                     "INSERT INTO #PERR (val) VALUES('Test String')");
        PreparedStatement pstmt = con.prepareStatement(" SELECT * FROM #PERR WHERE val = ?");
        pstmt.setString(1,"Test String");
        assertTrue(pstmt.execute());
        ResultSet rs = pstmt.getResultSet();
        assertTrue(rs.next());
        rs.close();
        pstmt.close();
        stmt.close();
    }

    /**
     * Test for bug [ 1011650 ] 0.9-rc1: comments get parsed
     *
     * @throws Exception
     */
    public void testSqlComments() throws Exception {
        String testSql = "/* This is a test of the comment {fn test()} parser */\r\n" +
                         "SELECT * FROM XXXX -- In line comment {d 1999-01-01}\r\n"+
                         "INSERT INTO B VALUES({d 1999-01-01}) -- Unterminated in line comment";
        String outSql = "/* This is a test of the comment {fn test()} parser */\r\n" +
                         "SELECT * FROM XXXX -- In line comment {d 1999-01-01}\r\n"+
                         "INSERT INTO B VALUES('19990101') -- Unterminated in line comment";
        assertEquals(outSql, con.nativeSQL(testSql));
    }

    /**
     * Test for bug [ 1008126 ] Metadata getTimeDateFunctions() wrong
     *
     * @throws Exception
     */
    public void testDateTimeFn() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE #DTFN (ttime SMALLDATETIME, tdate SMALLDATETIME, ftime SMALLDATETIME, fdate SMALLDATETIME, tnow DATETIME)");
        stmt.execute("INSERT INTO #DTFN (ttime, tdate, ftime, fdate, tnow) VALUES (getdate(), getdate(), {fn curtime()}, {fn curdate()}, {fn now()})");
        assertEquals(1, stmt.getUpdateCount());
        ResultSet rs = stmt.executeQuery("SELECT * FROM #DTFN");
        assertTrue(rs.next());
        assertEquals("curdate()",rs.getDate(2),rs.getDate(4));
        assertEquals("curtime()",rs.getTime(1),rs.getTime(3));
        assertEquals("now()",rs.getDate(1),rs.getDate(5));
        rs = stmt.executeQuery("SELECT {fn dayname('2004-08-21')}, " +
                                       "{fn dayofmonth('2004-08-21')}, " +
                                       "{fn dayofweek('2004-08-21')}," +
                                       "{fn dayofyear('2004-08-21')}," +
                                       "{fn hour('23:47:32')}," +
                                       "{fn minute('23:47:32')}," +
                                       "{fn second('23:47:32')}," +
                                       "{fn year('2004-08-21')}," +
                                       "{fn quarter('2004-08-21')}," +
                                       "{fn month('2004-08-21')}," +
                                       "{fn week('2004-08-21')}," +
                                       "{fn monthname('2004-08-21')}," +
                                       "{fn timestampdiff(SQL_TSI_DAY, '2004-08-19','2004-08-21')}," +
                                       "{fn timestampadd(SQL_TSI_MONTH, 1, '2004-08-21')}" +
                                       "");
        assertTrue(rs.next());
        assertEquals("dayname", "Saturday", rs.getString(1));
        assertEquals("dayofmonth", 21, rs.getInt(2));
        assertEquals("dayofweek", 7, rs.getInt(3));
        assertEquals("dayofyear", 234, rs.getInt(4));
        assertEquals("hour", 23, rs.getInt(5));
        assertEquals("minute", 47, rs.getInt(6));
        assertEquals("second", 32, rs.getInt(7));
        assertEquals("year", 2004, rs.getInt(8));
        assertEquals("quarter", 3, rs.getInt(9));
        assertEquals("month", 8, rs.getInt(10));
        assertEquals("week", 34, rs.getInt(11));
        assertEquals("monthname", "August", rs.getString(12));
        assertEquals("timestampdiff", 2, rs.getInt(13));
        assertEquals("timestampadd", java.sql.Date.valueOf("2004-09-21"), rs.getDate(14));
        stmt.close();
    }

    /**
     * Test for scalar string functions.
     *
     * @throws Exception
     */
    public void testStringFn() throws Exception {
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT {fn ascii('X')}, "+
                                                "{fn char(88)}," +
                                                "{fn concat('X','B')}," +
                                                "{fn difference('X','B')}," +
                                                "{fn insert('XXX',2,1, 'Y')}," +
                                                "{fn lcase('XXX')}," +
                                                "{fn length('XXX')}," +
                                                "{fn ltrim(' XXX')}," +
                                                "{fn repeat('X', 3)}," +
                                                "{fn replace('XXXYYYXXX', 'YYY', 'FRED')}," +
                                                "{fn right('XXX', 1)}," +
                                                "{fn rtrim('XXX ')}, " +
                                                "{fn soundex('FRED')}," +
                                                "'X' + {fn space(1)} + 'X'," +
                                                "{fn substring('FRED', 2, 1)}," +
                                                "{fn ucase('xxx')}," +
                                                "{fn locate('fred', 'xxxfredyyy')}," +
                                                "{fn left('FRED', 1)}" +
                                                "");
        assertTrue(rs.next());
        assertEquals("ascii", 88, rs.getInt(1));
        assertEquals("char", "X", rs.getString(2));
        assertEquals("concat", "XB", rs.getString(3));
        assertEquals("difference", 3, rs.getInt(4));
        assertEquals("insert", "XYX", rs.getString(5));
        assertEquals("lcase", "xxx", rs.getString(6));
        assertEquals("insert", 3, rs.getInt(7));
        assertEquals("ltrim", "XXX", rs.getString(8));
        assertEquals("repeat", "XXX", rs.getString(9));
        assertEquals("replace", "XXXFREDXXX", rs.getString(10));
        assertEquals("right", "X", rs.getString(11));
        assertEquals("rtrim", "XXX", rs.getString(12));
        assertEquals("soundex", "F630", rs.getString(13));
        assertEquals("space", "X X", rs.getString(14));
        assertEquals("substring", "R", rs.getString(15));
        assertEquals("ucase", "XXX", rs.getString(16));
        assertEquals("locate", 4, rs.getInt(17));
        assertEquals("left", "F", rs.getString(18));

        stmt.close();
    }

    /**
     * Test nested escapes
     *
     * @throws Exception
     */
    public void testNestedEscapes() throws Exception {
        String sql = "SELECT {fn convert({fn month({fn now()})},varchar)} WHERE X";
        assertEquals("SELECT convert(varchar,datepart(month,getdate())) WHERE X", con.nativeSQL(sql));
        sql = "{?=call testproc(?, {fn now()})}";
        assertEquals("EXECUTE testproc ?,getdate()", con.nativeSQL(sql));
        sql = "SELECT * FROM {oj t1 LEFT OUTER JOIN {oj t2 LEFT OUTER JOIN t2 ON condition1} ON condition2}";
        assertEquals("SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t2 ON condition1 ON condition2", con.nativeSQL(sql));
    }

    /**
     * Test conversion of various types to LONGVARCHAR. This functionality was
     * broken in 0.9 because changes were made to handle LONGVARCHAR internally
     * as Clob rather than String (but these did not take into consideration
     * all possible cases.
     *
     * @throws SQLException
     */
    public void testConversionToLongvarchar() throws SQLException {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE #testConversionToLongvarchar ("
                + " id INT,"
                + " val NTEXT)");

        int id = 0;
        String decimalValue = "1234.5678";
        String booleanValue = "true";
        String integerValue = "1234567";
        String longValue = "1234567890123";
        Date dateValue = new Date(System.currentTimeMillis());
        Time timeValue = new Time(System.currentTimeMillis());
        Timestamp timestampValue = new Timestamp(System.currentTimeMillis());

        PreparedStatement pstmt = con.prepareStatement(
                "INSERT INTO #testConversionToLongvarchar (id, val) VALUES (?, ?)");

        // Test BigDecimal to LONGVARCHAR conversion
        pstmt.setInt(1, ++id);
        pstmt.setObject(2, new BigDecimal(decimalValue), java.sql.Types.LONGVARCHAR);
        pstmt.executeUpdate();

        // Test Boolean to LONGVARCHAR conversion
        pstmt.setInt(1, ++id);
        pstmt.setObject(2, new Boolean(booleanValue), java.sql.Types.LONGVARCHAR);
        pstmt.executeUpdate();

        // Test Integer to LONGVARCHAR conversion
        pstmt.setInt(1, ++id);
        pstmt.setObject(2, new Integer(integerValue), java.sql.Types.LONGVARCHAR);
        pstmt.executeUpdate();

        // Test Long to LONGVARCHAR conversion
        pstmt.setInt(1, ++id);
        pstmt.setObject(2, new Long(longValue), java.sql.Types.LONGVARCHAR);
        pstmt.executeUpdate();

        // Test Float to LONGVARCHAR conversion
        pstmt.setInt(1, ++id);
        pstmt.setObject(2, new Float(integerValue), java.sql.Types.LONGVARCHAR);
        pstmt.executeUpdate();

        // Test Double to LONGVARCHAR conversion
        pstmt.setInt(1, ++id);
        pstmt.setObject(2, new Double(longValue), java.sql.Types.LONGVARCHAR);
        pstmt.executeUpdate();

        // Test Date to LONGVARCHAR conversion
        pstmt.setInt(1, ++id);
        pstmt.setObject(2, dateValue, java.sql.Types.LONGVARCHAR);
        pstmt.executeUpdate();

        // Test Time to LONGVARCHAR conversion
        pstmt.setInt(1, ++id);
        pstmt.setObject(2, timeValue, java.sql.Types.LONGVARCHAR);
        pstmt.executeUpdate();

        // Test Timestamp to LONGVARCHAR conversion
        pstmt.setInt(1, id);
        pstmt.setObject(2, timestampValue, java.sql.Types.LONGVARCHAR);
        pstmt.executeUpdate();
        pstmt.close();

        ResultSet rs = stmt.executeQuery(
                "SELECT * FROM #testConversionToLongvarchar ORDER BY id");
        assertTrue(rs.next());
        assertEquals(decimalValue, rs.getString("val"));
        assertTrue(rs.next());
        assertEquals("1", rs.getString("val"));
        assertTrue(rs.next());
        assertEquals(integerValue, rs.getString("val"));
        assertTrue(rs.next());
        assertEquals(longValue, rs.getString("val"));
        assertTrue(rs.next());
        assertEquals(Float.parseFloat(integerValue), Float.parseFloat(rs.getString("val")), 0);
        assertTrue(rs.next());
        assertEquals(Double.parseDouble(longValue), Double.parseDouble(rs.getString("val")), 0);
        assertTrue(rs.next());
        assertEquals(dateValue.toString(), rs.getString("val"));
        assertTrue(rs.next());
        assertEquals(timeValue.toString(), rs.getString("val"));
        assertTrue(rs.next());
        assertEquals(timestampValue.toString(), rs.getString("val"));

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

    public static void main(String[] args) {
        junit.textui.TestRunner.run(SunTest.class);
    }
}