resultsetregressiontest.java

在资料浩瀚的互联网中

     */
    public void testBug5235() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5235");
            this.stmt.executeUpdate("CREATE TABLE testBug5235(field1 DATE)");
            this.stmt.executeUpdate(
                "INSERT INTO testBug5235 (field1) VALUES ('0000-00-00')");

            Properties props = new Properties();
            props.setProperty("zeroDateTimeBehavior", "convertToNull");

            Connection nullConn = getConnectionWithProps(props);

            this.rs = nullConn.createStatement().executeQuery("SELECT field1 FROM testBug5235");
            this.rs.next();
            assertTrue(null == this.rs.getObject(1));
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5235");
        }
    }

    /**
     * Tests for BUG#5136, GEOMETRY types getting corrupted, turns out
     * to be a server bug.
     * 
     * @throws Exception if the test fails.
     */
    public void testBug5136() throws Exception {
    	if (false) {
        PreparedStatement toGeom = this.conn.prepareStatement(
                "select GeomFromText(?)");
        PreparedStatement toText = this.conn.prepareStatement(
                "select AsText(?)");

        String inText = "POINT(146.67596278 -36.54368233)";

        // First assert that the problem is not at the server end
        this.rs = this.stmt.executeQuery("select AsText(GeomFromText('" +
                inText + "'))");
        this.rs.next();

        String outText = this.rs.getString(1);
        this.rs.close();
        assertTrue("Server side only\n In: " + inText + "\nOut: " + outText,
            inText.equals(outText));

        // Now bring a binary geometry object to the client and send it back
        toGeom.setString(1, inText);
        this.rs = toGeom.executeQuery();
        this.rs.next();

        // Return a binary geometry object from the WKT
        Object geom = this.rs.getObject(1);
        this.rs.close();
        toText.setObject(1, geom);
        this.rs = toText.executeQuery();
        this.rs.next();

        // Return WKT from the binary geometry
        outText = this.rs.getString(1);
        this.rs.close();
        assertTrue("Server to client and back\n In: " + inText + "\nOut: " +
            outText, inText.equals(outText));
    	}
    }

	/**
	 * Tests fix for BUG#5664, ResultSet.updateByte() when on insert row
	 * throws ArrayOutOfBoundsException.
	 * 
	 * @throws Exception if the test fails.
	 */
	public void testBug5664() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5664");
			this.stmt.executeUpdate("CREATE TABLE testBug5664 (pkfield int PRIMARY KEY NOT NULL, field1 SMALLINT)");
			this.stmt.executeUpdate("INSERT INTO testBug5664 VALUES (1, 1)");
			
			Statement updatableStmt = this.conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			
			this.rs = updatableStmt.executeQuery("SELECT pkfield, field1 FROM testBug5664");
			this.rs.next();
			this.rs.moveToInsertRow();
			this.rs.updateInt(1, 2);
			this.rs.updateByte(2, (byte) 2);
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5664");
		}
	}
	
	public void testBogusTimestampAsString() throws Exception {
		
		this.rs = this.stmt.executeQuery("SELECT '2004-08-13 13:21:17.'");
		
		this.rs.next();
		
		// We're only checking for an exception being thrown here as the bug
		this.rs.getTimestamp(1);
		
	}
	
	/**
	 * Tests our ability to reject NaN and +/- INF in PreparedStatement.setDouble();
	 */
	public void testBug5717() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5717");
			this.stmt.executeUpdate("CREATE TABLE testBug5717 (field1 DOUBLE)");
			this.pstmt = this.conn.prepareStatement("INSERT INTO testBug5717 VALUES (?)");
			
			try {
				this.pstmt.setDouble(1, Double.NEGATIVE_INFINITY);
				fail("Exception should've been thrown");
			} catch (Exception ex) {
				// expected
			}
			
			try {
				this.pstmt.setDouble(1, Double.POSITIVE_INFINITY);
				fail("Exception should've been thrown");
			} catch (Exception ex) {
				// expected
			}
			
			try {
				this.pstmt.setDouble(1, Double.NaN);
				fail("Exception should've been thrown");
			} catch (Exception ex) {
				// expected
			}
		} finally {
			if (this.pstmt != null) {
				this.pstmt.close();
			}
			
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5717");
		}
	}

	/**
	 * Tests fix for server issue that drops precision on aggregate operations on 
	 * DECIMAL types, because they come back as DOUBLEs.
	 * 
	 * @throws Exception if the test fails.
	 */
	public void testBug6537() throws Exception {
		if (versionMeetsMinimum(4, 1, 0)) {
			String tableName = "testBug6537";
			
			try {
				createTable(tableName, "(`id` int(11) NOT NULL default '0'," +
						"`value` decimal(10,2) NOT NULL default '0.00', `stringval` varchar(10)," +
						"PRIMARY KEY  (`id`)" +
						") ENGINE=MyISAM DEFAULT CHARSET=latin1");
				this.stmt.executeUpdate("INSERT INTO " + tableName + "(id, value, stringval) VALUES (1, 100.00, '100.00'), (2, 200, '200')");
				
				String sql = "SELECT SUM(value) as total FROM " + tableName + " WHERE id = ? ";
				PreparedStatement pStmt = this.conn.prepareStatement(sql);
				pStmt.setInt(1, 1);
				this.rs = pStmt.executeQuery();
				assertTrue(this.rs.next());
				
				assertTrue("100.00".equals(this.rs.getBigDecimal("total").toString()));
				
				sql = "SELECT stringval as total FROM " + tableName + " WHERE id = ? ";
				pStmt = this.conn.prepareStatement(sql);
				pStmt.setInt(1, 2);
				this.rs = pStmt.executeQuery();
				assertTrue(this.rs.next());
				
				assertTrue("200.00".equals(this.rs.getBigDecimal("total", 2).toString()));
	
			} finally {
				dropTable(tableName);
			}
		}
	}
	
    /**
     * Tests fix for BUG#6231, ResultSet.getTimestamp() on a column with TIME in it
     * fails.
     * 
     * @throws Exception if the test fails.
     */
    public void testBug6231() throws Exception {
    	try {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6231");
    		this.stmt.executeUpdate("CREATE TABLE testBug6231 (field1 TIME)");
    		this.stmt.executeUpdate("INSERT INTO testBug6231 VALUES ('09:16:00')");
    		
    		this.rs = this.stmt.executeQuery("SELECT field1 FROM testBug6231");
    		this.rs.next();
    		long asMillis = this.rs.getTimestamp(1).getTime();
    		Calendar cal = Calendar.getInstance();
    		cal.setTimeInMillis(asMillis);
    		assertTrue(cal.get(Calendar.HOUR) == 9);
    		assertTrue(cal.get(Calendar.MINUTE) == 16);
    		assertTrue(cal.get(Calendar.SECOND) == 0);
    	} finally {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6231");
    	}
    }
    
    public void testBug6619() throws Exception {
    	try {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6619");
    		this.stmt.executeUpdate("CREATE TABLE testBug6619 (field1 int)");
    		this.stmt.executeUpdate("INSERT INTO testBug6619 VALUES (1), (2)");
    		
    		PreparedStatement pStmt = this.conn.prepareStatement("SELECT SUM(field1) FROM testBug6619");
    		
    		this.rs = pStmt.executeQuery();
    		this.rs.next();
    		System.out.println(this.rs.getString(1));
    		
    	} finally {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6619");
    	}
    }
    
    /**
     * Tests for presence of BUG#6561, NPE thrown when dealing with
     * 0 dates and non-unpacked result sets.
     * 
     * @throws Exception if the test occurs.
     */
    public void testBug6561() throws Exception {
    	
    	try {
    		Properties props = new Properties();
    		props.setProperty("zeroDateTimeBehavior", "convertToNull");
    		
    		Connection zeroConn = getConnectionWithProps(props);
    		
    		this.stmt.executeUpdate ("DROP TABLE IF EXISTS testBug6561");
    		this.stmt.executeUpdate ("CREATE TABLE testBug6561 (ofield int, field1 DATE, field2 integer, field3 integer)");
    		this.stmt.executeUpdate ("INSERT INTO testBug6561 (ofield, field1,field2,field3)	VALUES (1, 0,NULL,0)");
    		this.stmt.executeUpdate ("INSERT INTO testBug6561 (ofield, field1,field2,field3) VALUES (2, '2004-11-20',NULL,0)");
    		
    		PreparedStatement ps = zeroConn.prepareStatement ("SELECT field1,field2,field3 FROM testBug6561 ORDER BY ofield");
    		this.rs = ps.executeQuery();
    		
    		assertTrue(rs.next());
    		assertTrue(null == rs.getObject("field1"));
    		assertTrue(null == rs.getObject("field2"));
    		assertTrue(0 == rs.getInt("field3"));
    		
    		assertTrue(rs.next());
    		assertTrue(rs.getString("field1").equals("2004-11-20"));
    		assertTrue(null == rs.getObject("field2"));
    		assertTrue(0 == rs.getInt("field3"));
    		
    		ps.close();
    	}
    	finally {
    		this.stmt.executeUpdate ("DROP TABLE IF EXISTS test");
    	}
    }

    public void testBug7686() throws SQLException {
        String tableName = "testBug7686";
        createTable(tableName, "(id1 int(10) unsigned NOT NULL,"
                + " id2 DATETIME, "
                + " field1 varchar(128) NOT NULL default '',"
                + " PRIMARY KEY  (id1, id2)) TYPE=InnoDB;");

        this.stmt.executeUpdate("insert into " + tableName
                + " (id1, id2, field1)"
                + " values (1, '2005-01-05 13:59:20', 'foo')");

        String sQuery = " SELECT * FROM " + tableName
                + " WHERE id1 = ? AND id2 = ?";
        this.pstmt = this.conn.prepareStatement(sQuery,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

        this.conn.setAutoCommit(false);
        this.pstmt.setInt(1, 1);
        GregorianCalendar cal = new GregorianCalendar();
        cal.clear();
        cal.set(2005, 00, 05, 13, 59, 20);
        Timestamp jan5before2pm = new java.sql.Timestamp(cal.getTimeInMillis());
        this.pstmt.setTimestamp(2, jan5before2pm);
        this.rs = this.pstmt.executeQuery();
        assertTrue(this.rs.next());
        this.rs.absolute(1);
        this.rs.updateString("field1", "bar");
        this.rs.updateRow();
        this.conn.commit();
        this.conn.setAutoCommit(true);
    }
	
    /**
     * Tests fix for BUG#7715 - Timestamps converted incorrectly to strings 
     * with SSPS and Upd. Result Sets.
     * 
     * @throws Exception if the test fails.
     */
    public void testBug7715() throws Exception {
    	PreparedStatement pStmt = null;
    	
    	try {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testConvertedBinaryTimestamp");
    		this.stmt.executeUpdate("CREATE TABLE testConvertedBinaryTimestamp (field1 VARCHAR(32), field2 VARCHAR(32), field3 VARCHAR(32), field4 TIMESTAMP)");
    		this.stmt.executeUpdate("INSERT INTO testConvertedBinaryTimestamp VALUES ('abc', 'def', 'ghi', NOW())");
    	
    		pStmt = this.conn.prepareStatement("SELECT field1, field2, field3, field4 FROM testConvertedBinaryTimestamp", 
    				ResultSet.TYPE_SCROLL_SENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
    		
    		this.rs = pStmt.executeQuery();
    		assertTrue(this.rs.next());
    		
    		this.rs.getObject(4); // fails if bug exists	
    	} finally {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testConvertedBinaryTimestamp");
    	}
    }
    
    /**
     * Tests fix for BUG#8428 - getString() doesn't maintain format
     * stored on server.
     * 
     * @throws Exception if the test fails.
     */
    public void testBug8428() throws Exception {
    	Connection noSyncConn = null;
    	
    	try {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug8428");
    		this.stmt.executeUpdate("CREATE TABLE testBug8428 (field1 YEAR, field2 DATETIME)");
    		this.stmt.executeUpdate("INSERT INTO testBug8428 VALUES ('1999', '2005-02-11 12:54:41')");
    		
    		Properties props = new Properties();
    		props.setProperty("noDatetimeStringSync", "true");
    		props.setProperty("useUsageAdvisor", "true");
    		
    		noSyncConn = getConnectionWithProps(props);
    		
    		this.rs = noSyncConn.createStatement().executeQuery("SELECT field1, field2 FROM testBug8428");
    		this.rs.next();
    		assertEquals("1999", this.rs.getString(1));
    		assertEquals("2005-02-11 12:54:41", this.rs.getString(2));
    		
    		this.rs = noSyncConn.prepareStatement("SELECT field1, field2 FROM testBug8428").executeQuery();
    		this.rs.next();
    		assertEquals("1999", this.rs.getString(1));
    		assertEquals("2005-02-11 12:54:41", this.rs.getString(2));
    	} finally {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug8428");
    	}
    }
    
    public void testNPEWithUsageAdvisor() throws Exception {
    	Connection advisorConn = null;
    	
    	try {
    		Properties props = new Properties();
    		props.setProperty("useUsageAdvisor", "true");
    	
    		advisorConn = getConnectionWithProps(props);
    		this.pstmt = advisorConn.prepareStatement("SELECT 1");
    		this.rs = this.pstmt.executeQuery();
    		this.rs.close();
    		this.rs = this.pstmt.executeQuery();
    		
    	} finally {
    	}
    }
}