suntest.java

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

// jTDS JDBC Driver for Microsoft SQL Server and Sybase
// Copyright (C) 2004 The jTDS Project
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
//
package net.sourceforge.jtds.test;

import junit.framework.Test;
import junit.framework.TestSuite;

import java.sql.*;
import java.math.BigDecimal;



/**
 * Test case to illustrate errors reported by SUN JBDC compatibility test suite.
 *
 * @version 1.0
 */
public class SunTest extends DatabaseTestCase {
    public static Test suite() {
        return new TestSuite(SunTest.class);
    }

    public SunTest(String name) {
        super(name);
    }

    /**
     * Test for SUN bug [ PrepStmt1.getMetaData() ]
     * Driver loops if select contains commas.
     *
     * @throws Exception
     */
    public void testGetMetaData() throws Exception {
        PreparedStatement pstmt = con.prepareStatement("SELECT name, id, type FROM sysobjects WHERE type = 'U'");
        ResultSetMetaData rsmd = pstmt.getMetaData();
        assertEquals("name", rsmd.getColumnName(1));
        pstmt.close();
    }

    /**
     * Generic Tests for SUN bugs such as
     * <ol>
     * <li>Can't convert  VARCHAR to Timestamp
     * <li>Can't convert  VARCHAR to Time
     * <li>Can't convert  VARCHAR to Date
     * <li>Internal time representation causes equals to fail
     * </ol>
     * @throws Exception
     */
    public void testDateTime() throws Exception {
        final String dateStr = "1983-01-31";
        final String timeStr = "12:59:59";
        final String tsStr   = "1983-01-31 23:59:59.333";

        Statement stmt = con.createStatement();
        stmt.execute("CREATE PROC #CTOT_PROC @tdate DATETIME OUTPUT, @ttime DATETIME OUTPUT, @tts DATETIME OUTPUT AS " +
                     "BEGIN SELECT @tdate=tdate, @ttime=ttime, @tts=tts FROM #CTOT END");
        stmt.execute("CREATE TABLE #CTOT (tdate DATETIME, ttime DATETIME, tts DATETIME, tnull DATETIME NULL)");
        stmt.close();
        PreparedStatement pstmt = con.prepareStatement("INSERT INTO #CTOT (tdate, ttime, tts) VALUES(?,?,?)");
        pstmt.setObject(1, dateStr, java.sql.Types.DATE);
        pstmt.setObject(2, timeStr, java.sql.Types.TIME);
        pstmt.setObject(3, tsStr, java.sql.Types.TIMESTAMP);
        pstmt.execute();
        assertEquals(1, pstmt.getUpdateCount());
        pstmt.close();
        CallableStatement cstmt = con.prepareCall("{call #CTOT_PROC(?,?,?)}");
        cstmt.registerOutParameter(1, java.sql.Types.DATE);
        cstmt.registerOutParameter(2, java.sql.Types.TIME);
        cstmt.registerOutParameter(3, java.sql.Types.TIMESTAMP);
        cstmt.execute();
        assertEquals(dateStr, cstmt.getString(1));
        assertEquals(timeStr, cstmt.getString(2));
        assertEquals(java.sql.Time.valueOf(timeStr), cstmt.getTime(2));
        assertEquals(tsStr,   cstmt.getString(3));
        cstmt.close();
        stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM #CTOT");
        assertTrue(rs.next());
        java.sql.Time retval = rs.getTime(2);
        java.sql.Time tstval = java.sql.Time.valueOf(timeStr);
        assertEquals(tstval, retval);
        stmt.close();
        pstmt = con.prepareStatement("UPDATE #CTOT SET tnull = ?");
        pstmt.setTime(1, tstval);
        pstmt.execute();
        assertEquals(1, pstmt.getUpdateCount());
        pstmt.close();
        stmt = con.createStatement();
        rs = stmt.executeQuery("SELECT * FROM #CTOT");
        assertTrue(rs.next());
        retval = rs.getTime(4);
        assertEquals(tstval, retval);
        stmt.close();
    }

    /**
     * Generic test for errors caused by promotion out parameters of Float to Double by driver.
     * eg [ callStmt4.testGetObject34 ] Class cast exception Float.
     *
     * @throws Exception
     */
    public void testCharToReal() throws Exception {
        final String minStr = "3.4E38";
        final String maxStr = "1.18E-38";

        Statement stmt = con.createStatement();
        stmt.execute("CREATE PROC #CTOR_PROC @minval REAL OUTPUT, @maxval REAL OUTPUT AS " +
                     "BEGIN SELECT @minval=min_val, @maxval=max_val FROM #CTOR END");
        stmt.execute("CREATE TABLE #CTOR (min_val REAL, max_val REAL)");
        stmt.execute("INSERT INTO #CTOR VALUES(" + minStr +"," + maxStr + ")");
        assertEquals(1, stmt.getUpdateCount());
        ResultSet rs = stmt.executeQuery("SELECT * FROM #CTOR");
        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals(minStr, rs.getString(1));
        assertEquals(maxStr, rs.getString(2));
        assertTrue(rs.getObject(1) instanceof Float);
        stmt.close();
        CallableStatement cstmt = con.prepareCall("{call #CTOR_PROC(?,?)}");
        cstmt.registerOutParameter(1, java.sql.Types.REAL);
        cstmt.registerOutParameter(2, java.sql.Types.REAL);
        cstmt.execute();
        assertEquals(minStr, cstmt.getString(1));
        assertEquals(maxStr, cstmt.getString(2));
        cstmt.close();
    }

    /**
     * Generic test for SUN bugs: bigint null parameter values sent as integer size.
     *
     * @throws Exception
     */
    public void testCharToLong() throws Exception {
        final String minStr = "9223372036854775807";
        final String maxStr = "-9223372036854775808";

        Statement stmt = con.createStatement();
        stmt.execute("CREATE PROC #CTOL_PROC @minval BIGINT OUTPUT, @maxval BIGINT OUTPUT AS " +
                     "BEGIN SELECT @minval=min_val, @maxval=max_val FROM #CTOL END");
        stmt.execute("CREATE TABLE #CTOL (min_val BIGINT, max_val BIGINT)");
        stmt.execute("INSERT INTO #CTOL VALUES(" + minStr +"," + maxStr + ")");
        assertEquals(1, stmt.getUpdateCount());
        ResultSet rs = stmt.executeQuery("SELECT * FROM #CTOL");
        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals(minStr, rs.getString(1));
        assertEquals(maxStr, rs.getString(2));
        stmt.close();
        CallableStatement cstmt = con.prepareCall("{call #CTOL_PROC(?,?)}");
        cstmt.registerOutParameter(1, java.sql.Types.BIGINT);
        cstmt.registerOutParameter(2, java.sql.Types.BIGINT);
        cstmt.execute();
        assertEquals(minStr, cstmt.getString(1));
        assertEquals(maxStr, cstmt.getString(2));
        cstmt.close();
    }

    /**
     * Test for SUN bug [ dbMeta8.testGetProcedures ]
     * The wrong column names are returned by getProcedures().
     *
     * @throws Exception
     */
    public void testGetProcedures() throws Exception {
        String names[] = {"PROCEDURE_CAT","PROCEDURE_SCHEM","PROCEDURE_NAME","","","","REMARKS","PROCEDURE_TYPE"};
        DatabaseMetaData dbmd = con.getMetaData();
        ResultSet rs = dbmd.getProcedures(null, null, "%");
        ResultSetMetaData rsmd = rs.getMetaData();

        for (int i = 0; i < names.length; i++) {
            if (names[i].length() > 0) {
                assertEquals(names[i], rsmd.getColumnName(i+1));
            }
        }

        rs.close();
    }

    /**
     * Generic test for SUN bug where Float was promoted to Double
     * by driver leading to ClassCastExceptions in the tests.
     * Example [ prepStmt4.testSetObject16 ]
     *
     * @throws Exception
     */
    public void testGetFloatObject() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE #GETF (val REAL)");
        stmt.execute("INSERT INTO #GETF (val) VALUES (1.7E10)");
        assertEquals(1,stmt.getUpdateCount());
        ResultSet rs = stmt.executeQuery("SELECT * FROM #GETF");
        assertTrue(rs.next());
        assertTrue(rs.getObject(1) instanceof Float);
        rs.close();
        stmt.close();
    }

    /**
     * Test for SUN bug [ resultSet1.testSetFetchSize02 ]
     * attempt to set non zero fetch size rejected.
     *
     * @throws Exception
     */
    public void testSetFetchSize() throws Exception {
        CallableStatement cstmt = con.prepareCall("{call sp_who}");
        ResultSet rs = cstmt.executeQuery();
        rs.setFetchSize(5);
        assertEquals(5, rs.getFetchSize());
        rs.close();
        cstmt.close();
    }

    /**
     * Test for SUN bug [ stmt2.testSetFetchDirection04 ]
     * fetch direction constant not validated.
     *
     * @throws Exception
     */
    public void testSetFetchDirectiion() throws Exception {
        Statement stmt = con.createStatement();

        try {
            stmt.setFetchDirection(-1);
            fail("setFecthDirection does not validate parameter");
        } catch (SQLException sqe) {
        }

        stmt.close();
    }

    /**
     * Test for bug [ 1012307 ] PreparedStatement.setObject(java.util.Date) not working.
     * The driver should throw an exception if the object is not of a valid
     * type according to table
     *
     * @throws Exception
     */
    public void testSetDateObject() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE #SETD (val DATETIME)");
        PreparedStatement pstmt = con.prepareStatement("INSERT INTO #SETD (val) VALUES (?)");
        long tval = 60907507200000L; //1999-12-31