callablestatementregressiontest.java

mysql jdbc驱动程序 mysql jdbc驱动程序 mysql jdbc驱动程序 mysql jdbc驱动程序

	 * db2Connection.getMetaData().getIdentifierQuoteString();
	 * 
	 * cstmt = db2Connection .prepareCall("{ call " + quoteChar +
	 * this.conn.getCatalog() + quoteChar + "." + quoteChar + "COMPROVAR_USUARI" +
	 * quoteChar + "(?, ?, ?, ?, ?) }"); cstmt.setString(1, "abc");
	 * cstmt.setString(2, "def"); cstmt.registerOutParameter(3,
	 * java.sql.Types.INTEGER); cstmt.registerOutParameter(4,
	 * java.sql.Types.VARCHAR); cstmt.registerOutParameter(5,
	 * java.sql.Types.VARCHAR);
	 * 
	 * cstmt.execute();
	 * 
	 * if (doASelect) { this.rs = cstmt.getResultSet();
	 * assertTrue(this.rs.next()); assertEquals(1, this.rs.getInt(1)); } else {
	 * assertEquals(1, cstmt.getInt(5)); } } finally { if (db2Connection !=
	 * null) { db2Connection.createStatement().executeUpdate( "DROP PROCEDURE IF
	 * EXISTS COMPROVAR_USUARI"); //
	 * db2Connection.createStatement().executeUpdate( // "DROP DATABASE IF
	 * EXISTS db_9319"); }
	 * 
	 * this.stmt .executeUpdate("DROP PROCEDURE IF EXISTS COMPROVAR_USUARI"); } } } }
	 */

	/**
	 * Tests fix for BUG#9682 - Stored procedures with DECIMAL parameters with
	 * storage specifications that contained "," in them would fail.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug9682() throws Exception {
		if (!serverSupportsStoredProcedures()) {
			return;
		}

		CallableStatement cStmt = null;

		try {
			this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug9682");
			this.stmt
			.executeUpdate("CREATE PROCEDURE testBug9682(decimalParam DECIMAL(18,0))"
					+ "\nBEGIN" + "\n   SELECT 1;" + "\nEND");
			cStmt = this.conn.prepareCall("Call testBug9682(?)");
			cStmt.setDouble(1, 18.0);
			cStmt.execute();
		} finally {
			if (cStmt != null) {
				cStmt.close();
			}

			this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug9682");
		}
	}

	/**
	 * Tests fix forBUG#10310 - Driver doesn't support {?=CALL(...)} for calling
	 * stored functions. This involved adding support for function retrieval to
	 * DatabaseMetaData.getProcedures() and getProcedureColumns() as well.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug10310() throws Exception {
		if (!serverSupportsStoredProcedures()) {
			return;
		}

		CallableStatement cStmt = null;

		try {
			this.stmt.executeUpdate("DROP FUNCTION IF EXISTS testBug10310");
			this.stmt
			.executeUpdate("CREATE FUNCTION testBug10310(a float, b bigint, c int) RETURNS INT"
					+ "\nBEGIN" + "\nRETURN a;" + "\nEND");
			cStmt = this.conn.prepareCall("{? = CALL testBug10310(?,?,?)}");
			cStmt.registerOutParameter(1, Types.INTEGER);
			cStmt.setFloat(2, 2);
			cStmt.setInt(3, 1);
			cStmt.setInt(4, 1);

			if (!isRunningOnJdk131()) {
				assertEquals(4, cStmt.getParameterMetaData().getParameterCount());
				assertEquals(Types.INTEGER, cStmt.getParameterMetaData().getParameterType(1));
			}

			assertFalse(cStmt.execute());
			assertEquals(2f, cStmt.getInt(1), .001);
			assertEquals("java.lang.Integer", cStmt.getObject(1).getClass()
					.getName());

			assertEquals(-1, cStmt.executeUpdate());
			assertEquals(2f, cStmt.getInt(1), .001);
			assertEquals("java.lang.Integer", cStmt.getObject(1).getClass()
					.getName());

			if (!isRunningOnJdk131()) {
				cStmt.setFloat("a", 4);
				cStmt.setInt("b", 1);
				cStmt.setInt("c", 1);

				assertFalse(cStmt.execute());
				assertEquals(4f, cStmt.getInt(1), .001);
				assertEquals("java.lang.Integer", cStmt.getObject(1).getClass()
						.getName());

				assertEquals(-1, cStmt.executeUpdate());
				assertEquals(4f, cStmt.getInt(1), .001);
				assertEquals("java.lang.Integer", cStmt.getObject(1).getClass()
						.getName());
			}

			// Check metadata while we're at it

			java.sql.DatabaseMetaData dbmd = this.conn.getMetaData();

			this.rs = dbmd.getProcedures(this.conn.getCatalog(), null,
			"testBug10310");
			this.rs.next();
			assertEquals("testBug10310", this.rs
					.getString("PROCEDURE_NAME"));
			assertEquals(DatabaseMetaData.procedureReturnsResult, this.rs
					.getShort("PROCEDURE_TYPE"));
			cStmt.setNull(2, Types.FLOAT);
			cStmt.setInt(3, 1);
			cStmt.setInt(4, 1);

			assertFalse(cStmt.execute());
			assertEquals(0f, cStmt.getInt(1), .001);
			assertEquals(true, cStmt.wasNull());
			assertEquals(null, cStmt.getObject(1));
			assertEquals(true, cStmt.wasNull());

			assertEquals(-1, cStmt.executeUpdate());
			assertEquals(0f, cStmt.getInt(1), .001);
			assertEquals(true, cStmt.wasNull());
			assertEquals(null, cStmt.getObject(1));
			assertEquals(true, cStmt.wasNull());


			// Check with literals, not all parameters filled!
			cStmt = this.conn.prepareCall("{? = CALL testBug10310(4,5,?)}");
			cStmt.registerOutParameter(1, Types.INTEGER);
			cStmt.setInt(2, 1);

			assertFalse(cStmt.execute());
			assertEquals(4f, cStmt.getInt(1), .001);
			assertEquals("java.lang.Integer", cStmt.getObject(1).getClass()
					.getName());

			assertEquals(-1, cStmt.executeUpdate());
			assertEquals(4f, cStmt.getInt(1), .001);
			assertEquals("java.lang.Integer", cStmt.getObject(1).getClass()
					.getName());

			if (!isRunningOnJdk131()) {
				assertEquals(2, cStmt.getParameterMetaData().getParameterCount());
				assertEquals(Types.INTEGER, cStmt.getParameterMetaData().getParameterType(1));
				assertEquals(Types.INTEGER, cStmt.getParameterMetaData().getParameterType(2));
			}
		} finally {
			if (this.rs != null) {
				this.rs.close();
				this.rs = null;
			}

			if (cStmt != null) {
				cStmt.close();
			}

			this.stmt.executeUpdate("DROP FUNCTION IF EXISTS testBug10310");
		}
	}

	/**
	 * Tests fix for Bug#12417 - stored procedure catalog name is case-sensitive
	 * on Windows (this is actually a server bug, but we have a workaround in
	 * place for it now).
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug12417() throws Exception {
		if (serverSupportsStoredProcedures() && isServerRunningOnWindows()) {
			Connection ucCatalogConn = null;

			try {
				this.stmt
				.executeUpdate("DROP PROCEDURE IF EXISTS testBug12417");
				this.stmt.executeUpdate("CREATE PROCEDURE testBug12417()\n"
						+ "BEGIN\n" + "SELECT 1;" + "end\n");
				ucCatalogConn = getConnectionWithProps((Properties)null);
				ucCatalogConn.setCatalog(this.conn.getCatalog().toUpperCase());
				ucCatalogConn.prepareCall("{call testBug12417()}");
			} finally {
				this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3539");

				if (ucCatalogConn != null) {
					ucCatalogConn.close();
				}
			}
		}
	}

	public void testBug15121() throws Exception {
		if (false /* needs to be fixed on server */) {
			if (versionMeetsMinimum(5, 0)) {
				this.stmt
				.executeUpdate("DROP PROCEDURE IF EXISTS p_testBug15121");

				this.stmt.executeUpdate("CREATE PROCEDURE p_testBug15121()\n"
						+ "BEGIN\n" + "SELECT * from idonotexist;\n" + "END");

				Properties props = new Properties();
				props.setProperty(NonRegisteringDriver.DBNAME_PROPERTY_KEY, "");

				Connection noDbConn = null;

				try {
					noDbConn = getConnectionWithProps(props);

					StringBuffer queryBuf = new StringBuffer("{call ");
					String quotedId = this.conn.getMetaData()
					.getIdentifierQuoteString();
					queryBuf.append(quotedId);
					queryBuf.append(this.conn.getCatalog());
					queryBuf.append(quotedId);
					queryBuf.append(".p_testBug15121()}");

					noDbConn.prepareCall(queryBuf.toString()).execute();
				} finally {
					if (noDbConn != null) {
						noDbConn.close();
					}
				}
			}
		}
	}

	/**
	 * Tests fix for BUG#15464 - INOUT parameter does not store IN value.
	 * 
	 * @throws Exception
	 *             if the test fails
	 */

	public void testBug15464() throws Exception {
		if (!serverSupportsStoredProcedures()) {
			return;
		}
		CallableStatement storedProc = null;

		try {
			this.stmt
			.executeUpdate("DROP PROCEDURE IF EXISTS testInOutParam");
			this.stmt
			.executeUpdate("create procedure testInOutParam(IN p1 VARCHAR(255), INOUT p2 INT)\n"
					+ "begin\n"
					+ " DECLARE z INT;\n"
					+ "SET z = p2 + 1;\n"
					+ "SET p2 = z;\n"
					+ "SELECT p1;\n"
					+ "SELECT CONCAT('zyxw', p1);\n" + "end\n");

			storedProc = this.conn
			.prepareCall("{call testInOutParam(?, ?)}");

			storedProc.setString(1, "abcd");
			storedProc.setInt(2, 4);
			storedProc.registerOutParameter(2, Types.INTEGER);

			storedProc.execute();

			assertEquals(5, storedProc.getInt(2));
		} finally {
			this.stmt
			.executeUpdate("DROP PROCEDURE IF EXISTS testInOutParam");
		}
	}

	/**
	 * Tests fix for BUG#17898 - registerOutParameter not working when some
	 * parameters pre-populated. Still waiting for feedback from JDBC experts
	 * group to determine what correct parameter count from getMetaData() should
	 * be, however.
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testBug17898() throws Exception {
		if (!serverSupportsStoredProcedures()) {
			return;
		}

		this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug17898");
		this.stmt
		.executeUpdate("CREATE PROCEDURE testBug17898(param1 VARCHAR(50), OUT param2 INT)\nBEGIN\nDECLARE rtn INT;\nSELECT 1 INTO rtn;\nSET param2=rtn;\nEND");

		CallableStatement cstmt = this.conn
		.prepareCall("{CALL testBug17898('foo', ?)}");
		cstmt.registerOutParameter(1, Types.INTEGER);
		cstmt.execute();
		assertEquals(1, cstmt.getInt(1));

		if (!isRunningOnJdk131()) {
			cstmt.clearParameters();
			cstmt.registerOutParameter("param2", Types.INTEGER);
			cstmt.execute();
			assertEquals(1, cstmt.getInt(1));
		}

	}

	/**
	 * Tests fix for BUG#21462 - JDBC (and ODBC) specifications allow no-parenthesis
	 * CALL statements for procedures with no arguments, MySQL server does not.
	 * 
	 * @throws Exception if the test fails.
	 */
	public void testBug21462() throws Exception {
		if (!serverSupportsStoredProcedures()) {
			return;
		}

		CallableStatement cstmt = null;

		try {
			this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug21462");
			this.stmt.executeUpdate("CREATE PROCEDURE testBug21462() BEGIN SELECT 1; END");
			cstmt = this.conn.prepareCall("{CALL testBug21462}");
			cstmt.execute();
		} finally {
			if (cstmt != null) {
				cstmt.close();
			}

			this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug21462");
		}

	}

	/** 
	 * Tests fix for BUG#22024 - Newlines causing whitespace to span confuse
	 * procedure parser when getting parameter metadata for stored procedures.
	 * 
	 * @throws Exception if the test fails
	 */
	public void testBug22024() throws Exception {
		if (!serverSupportsStoredProcedures()) {
			return;
		}

		CallableStatement cstmt = null;

		try {
			this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug22024");
			this.stmt.executeUpdate("CREATE PROCEDURE testBug22024(\r\n)\r\n BEGIN SELECT 1; END");
			cstmt = this.conn.prepareCall("{CALL testBug22024()}");
			cstmt.execute();

			this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug22024");
			this.stmt.executeUpdate("CREATE PROCEDURE testBug22024(\r\na INT)\r\n BEGIN SELECT 1; END");
			cstmt = this.conn.prepareCall("{CALL testBug22024(?)}");
			cstmt.setInt(1, 1);
			cstmt.execute();
		} finally {
			if (cstmt != null) {
				cstmt.close();
			}

			this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug22024");
		}

	}

	/**
	 * Tests workaround for server crash when calling stored procedures
	 * via a server-side prepared statement (driver now detects 
	 * prepare(stored procedure) and substitutes client-side prepared statement).
	 * 
	 * @throws Exception if the test fails
	 */
	public void testBug22297() throws Exception {
		if (!serverSupportsStoredProcedures()) {
			return;
		}

		this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug22297");

		createTable("tblTestBug2297_1", "("
				+ "id varchar(20) NOT NULL default '',"
				+ "Income double(19,2) default NULL)");	

		createTable("tblTestBug2297_2", "("
				+ "id varchar(20) NOT NULL default ''," 
				+ "CreatedOn datetime default NULL)");

		this.stmt.executeUpdate("CREATE PROCEDURE testBug22297(pcaseid INT)"
				+ "BEGIN"
				+ "\nSET @sql = \"DROP TEMPORARY TABLE IF EXISTS tmpOrders\";"
				+ " PREPARE stmt FROM @sql;"
				+ " EXECUTE stmt;"
				+ " DEALLOCATE PREPARE stmt;"
				+ "\nSET @sql = \"CREATE TEMPORARY TABLE tmpOrders SELECT id, 100 AS Income FROM tblTestBug2297_1 GROUP BY id\";"
				+ " PREPARE stmt FROM @sql;"
				+ " EXECUTE stmt;"
				+ " DEALLOCATE PREPARE stmt;"
				+ "\n SELECT id, Income FROM (SELECT e.id AS id ,COALESCE(prof.Income,0) AS Income"
				+ "\n FROM tblTestBug2297_2 e LEFT JOIN tmpOrders prof ON e.id = prof.id"
				+ "\n WHERE e.CreatedOn > '2006-08-01') AS Final ORDER BY id;" 
				+ "\nEND");

		this.stmt.executeUpdate("INSERT INTO tblTestBug2297_1 (`id`,`Income`) VALUES "
				+ "('a',4094.00),"
				+ "('b',500.00),"
				+ "('c',3462.17),"
				+ " ('d',500.00),"