safetest.java

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

     */
    public void testCallableStatementVarchar0010() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE PROCEDURE #SAfe0010 @p1 VARCHAR(255) OUT AS "
                     + "SELECT @p1 = @p1 + @p1 "
                     + "SELECT @p1 = @p1 + @p1 "
                     + "SELECT @p1 = @p1 + @p1 "
                     + "SELECT @p1 AS value "
                     + "RETURN 255");
        stmt.close();

        // 256 characters long string
        String myVal = "01234567890123456789012345678901234567890123456789"
                + "01234567890123456789012345678901234567890123456789"
                + "01234567890123456789012345678901234567890123456789"
                + "01234567890123456789012345678901234567890123456789"
                + "01234567890123456789012345678901234567890123456789"
                + "01234";

        // Execute it using executeQuery
        CallableStatement cs = con.prepareCall("{?=call #SAfe0010(?)}");
        cs.registerOutParameter(1, Types.INTEGER);
        cs.setString(2, myVal);
        cs.registerOutParameter(2, Types.VARCHAR);
        ResultSet rs = cs.executeQuery();
        assertTrue(rs.next());
        String rsVal = rs.getString(1);
        rs.close();

        assertFalse(cs.getMoreResults());
        assertEquals(-1, cs.getUpdateCount());

        assertEquals(myVal.length(), cs.getInt(1));
        assertEquals(rsVal, cs.getString(2));

        cs.close();
    }

    /**
     * Test <code>ResultSet.updateRow()</code> on updateable result sets.
     */
    public void testUpdateRow0011() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE #SAfe0011(value VARCHAR(255) PRIMARY KEY)");
        stmt.close();

        PreparedStatement insStmt = con.prepareStatement(
                "INSERT INTO #SAfe0011(value) values (?)");
        insStmt.setString(1, "Row 1");
        assertEquals(1, insStmt.executeUpdate());
        insStmt.setString(1, "Row 2");
        assertEquals(1, insStmt.executeUpdate());
        insStmt.close();

        stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                   ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = stmt.executeQuery("SELECT * FROM #SAfe0011 ORDER BY 1");
        assertEquals(null, stmt.getWarnings());
        assertEquals(null, rs.getWarnings());
        assertTrue(rs.next());
        assertTrue(rs.next());
        rs.updateString(1, "Row X");
        rs.updateRow();
        rs.next();
        assertEquals("Row X", rs.getString(1));
        rs.close();
        stmt.close();
    }

    /**
     * Test <code>ResultSet.insertRow()</code> on updateable result sets.
     */
    public void testInsertRow0012() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE #SAfe0012(value VARCHAR(255) PRIMARY KEY)");
        stmt.close();

        PreparedStatement insStmt = con.prepareStatement(
                "INSERT INTO #SAfe0012(value) values (?)");
        insStmt.setString(1, "Row 1");
        assertEquals(1, insStmt.executeUpdate());
        insStmt.setString(1, "Row 2");
        assertEquals(1, insStmt.executeUpdate());
        insStmt.close();

        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                   ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = stmt.executeQuery("SELECT * FROM #SAfe0012 ORDER BY 1");
        assertEquals(null, stmt.getWarnings());
        assertEquals(null, rs.getWarnings());

        // Insert the new row
        rs.moveToInsertRow();
        rs.updateString(1, "Row X");
        rs.insertRow();

        // Check the ResultSet contents
        rs.moveToCurrentRow();
        rs.next();
        assertEquals("Row 1", rs.getString(1));
        rs.next();
        assertEquals("Row 2", rs.getString(1));
        rs.next();
        assertEquals("Row X", rs.getString(1));
        rs.close();
        stmt.close();
    }

    /**
     * Test how an "out-of-order" close behaves (e.g close the
     * <code>Connection</code> first, then the <code>Statement</code> anf
     * finally the <code>ResultSet</code>).
     */
    public void testOutOfOrderClose0013() throws Exception {
        Connection localConn = getConnection();
        Statement stmt = localConn.createStatement();
        stmt.execute("CREATE TABLE #SAfe0013(value VARCHAR(255) PRIMARY KEY)");

        PreparedStatement insStmt = localConn.prepareStatement(
                "INSERT INTO #SAfe0013(value) values (?)");
        insStmt.setString(1, "Row 1");
        assertEquals(1, insStmt.executeUpdate());
        insStmt.setString(1, "Row 2");
        assertEquals(1, insStmt.executeUpdate());

        ResultSet rs = stmt.executeQuery("SELECT * FROM #SAfe0013");

        // Close the connection first
        localConn.close();

        // Now, close the statements
        stmt.close();
        insStmt.close();

        // And finally, close the ResultSet
        rs.close();
    }

    /**
     * Test cursor-based <code>ResultSet</code>s obtained from
     * <code>PreparedStatement</code>s and <code>CallableStatement</code>s.
     */
    public void testPreparedAndCallableCursors0014() throws Exception {
//        Logger.setActive(true);
        Statement stmt = con.createStatement();
        stmt.executeUpdate("CREATE TABLE #SAfe0014(id INT PRIMARY KEY)");
        stmt.executeUpdate("INSERT INTO #SAfe0014 VALUES (1)");
        stmt.executeUpdate("CREATE PROCEDURE #sp_SAfe0014(@P1 INT, @P2 INT) AS "
                           + "SELECT id, @P2 FROM #SAfe0014 WHERE id=@P1");
        stmt.close();

        PreparedStatement ps = con.prepareStatement("SELECT id FROM #SAfe0014",
                                                    ResultSet.TYPE_SCROLL_SENSITIVE,
                                                    ResultSet.CONCUR_UPDATABLE);
        ResultSet resultSet = ps.executeQuery();
        // No warnings
        assertEquals(null, resultSet.getWarnings());
        assertEquals(null, ps.getWarnings());
        // Correct ResultSet
        assertTrue(resultSet.next());
        assertEquals(1, resultSet.getInt(1));
        assertTrue(!resultSet.next());
        // Correct meta data
        ResultSetMetaData rsmd = resultSet.getMetaData();
        assertEquals("id", rsmd.getColumnName(1));
        assertEquals("#SAfe0014", rsmd.getTableName(1));
        // Insert row
        resultSet.moveToInsertRow();
        resultSet.updateInt(1, 2);
        resultSet.insertRow();
        resultSet.moveToCurrentRow();
        // Check correct row count
        resultSet.last();
        assertEquals(2, resultSet.getRow());
        resultSet.close();
        ps.close();

        ps = con.prepareStatement("SELECT id, ? FROM #SAfe0014 WHERE id = ?",
                                  ResultSet.TYPE_SCROLL_SENSITIVE,
                                  ResultSet.CONCUR_UPDATABLE);
        ps.setInt(1, 5);
        ps.setInt(2, 1);
        resultSet = ps.executeQuery();
        // No warnings
        assertEquals(null, resultSet.getWarnings());
        assertEquals(null, ps.getWarnings());
        // Correct ResultSet
        assertTrue(resultSet.next());
        assertEquals(1, resultSet.getInt(1));
        assertEquals(5, resultSet.getInt(2));
        assertTrue(!resultSet.next());
        // Correct meta data
        rsmd = resultSet.getMetaData();
        assertEquals("id", rsmd.getColumnName(1));
        assertEquals("#SAfe0014", rsmd.getTableName(1));
        resultSet.close();
        ps.close();

        CallableStatement cs = con.prepareCall("{call #sp_SAfe0014(?,?)}",
                                               ResultSet.TYPE_SCROLL_SENSITIVE,
                                               ResultSet.CONCUR_UPDATABLE);
        cs.setInt(1, 1);
        cs.setInt(2, 3);
        resultSet = cs.executeQuery();
        // No warnings
        assertEquals(null, resultSet.getWarnings());
        assertEquals(null, cs.getWarnings());
        // Correct ResultSet
        assertTrue(resultSet.next());
        assertEquals(1, resultSet.getInt(1));
        assertEquals(3, resultSet.getInt(2));
        assertTrue(!resultSet.next());
        // Correct meta data
        rsmd = resultSet.getMetaData();
        assertEquals("id", rsmd.getColumnName(1));
        assertEquals("#SAfe0014", rsmd.getTableName(1));
        resultSet.close();
        cs.close();
    }

    /**
     * Test batch updates for both plain and prepared statements.
     */
    public void testBatchUpdates0015() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE #SAfe0015(value VARCHAR(255) PRIMARY KEY)");

        // Execute prepared batch
        PreparedStatement insStmt = con.prepareStatement(
                "INSERT INTO #SAfe0015(value) values (?)");
        insStmt.setString(1, "Row 1");
        insStmt.addBatch();
        insStmt.setString(1, "Row 2");
        insStmt.addBatch();
        int[] res = insStmt.executeBatch();
        assertEquals(2, res.length);
        assertEquals(1, res[0]);
        assertEquals(1, res[1]);

        // Execute an empty batch
        res = insStmt.executeBatch();
        insStmt.close();
        assertEquals(0, res.length);

        // Execute plain batch
        stmt.addBatch("UPDATE #SAfe0015 SET value='R1' WHERE value='Row 1'");
        stmt.addBatch("UPDATE #SAfe0015 SET value='R2' WHERE value='Row 2'");
        res = stmt.executeBatch();
        assertEquals(2, res.length);
        assertEquals(1, res[0]);
        assertEquals(1, res[1]);

        // Execute an empty batch
        res = stmt.executeBatch();
        assertEquals(0, res.length);

        // Close the statement
        stmt.close();
    }

    /**
     * Test that dates prior to 06/15/1940 0:00:00 are stored and retrieved
     * correctly.
     */
    public void testOldDates0016() throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE #SAfe0016(id INT, value DATETIME)");

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String[] dates = {
            "1983-10-30 02:00:00",
            "1983-10-30 01:59:59",
            "1940-06-14 23:59:59",
            "1911-03-11 00:51:39",
            "1911-03-11 00:51:38",
            "1900-01-01 01:00:00",
            "1900-01-01 00:59:59",
            "1900-01-01 00:09:21",
            "1900-01-01 00:09:20",
            "1753-01-01 00:00:00"
        };

        // Insert the timestamps
        PreparedStatement pstmt =
                con.prepareStatement("INSERT INTO #SAfe0016 VALUES(?, ?)");

        for (int i = 0; i < dates.length; i++) {
            pstmt.setInt(1, i);
            pstmt.setString(2, dates[i]);
            pstmt.addBatch();
        }

        int[] res = pstmt.executeBatch();
        // Check that the insertion went ok

        assertEquals(dates.length, res.length);

        for (int i = 0; i < dates.length; i++) {
            assertEquals(1, res[i]);
        }

        // Select the timestamps and make sure they are the same
        ResultSet rs = stmt.executeQuery(
                "SELECT value FROM #SAfe0016 ORDER BY id");

        int counter = 0;

        while (rs.next()) {
            assertEquals(format.parse(dates[counter]), rs.getTimestamp(1));
            ++counter;
        }

        // Close everything
        rs.close();
        stmt.close();
        pstmt.close();
    }

    /**
     * Test bug #926620 - Too long value for VARCHAR field.
     */
/* does not work with SQL 6.5
    public void testCursorLargeCharInsert0017() throws Exception {
        Statement stmt = con.createStatement(
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        stmt.execute("CREATE TABLE #SAfe0017(value VARCHAR(10) PRIMARY KEY)");

        // Create the updateable ResultSet
        ResultSet rs = stmt.executeQuery(
                "SELECT value FROM #SAfe0017");

        // Try inserting a character string less than 10 characters long
        rs.moveToInsertRow();
        rs.updateString(1, "Test");
        rs.insertRow();
        rs.moveToCurrentRow();
        rs.last();
        // Check that we do indeed have one row in the ResultSet now
        assertEquals(1, rs.getRow());

        // Try inserting a character string more than 10 characters long
        rs.moveToInsertRow();
        rs.updateString(1, "Testing: 1, 2, 3...");
        try {
            rs.insertRow();
            fail("Should cause an SQLException with native error number 8152"
                 + "and SQL state 22001");