callablestatementregressiontest.java

mysql 5.1的 jdbc驱动 Connector/J 5.1 支持Mysql 4.1、Mysql 5.0、Mysql 5.1、Mysql 6.0 alpha这些版本。 Connector/J

		createProcedure("testBug27400",
				"(a INT, b VARCHAR(32)) BEGIN SELECT 1; END");

		CallableStatement cStmt = null;

		try {
			cStmt = this.conn
					.prepareCall("{CALL /* SOME COMMENT */ testBug27400( /* does this work too? */ ?, ?)} # and a commented ? here too");
			assertTrue(cStmt.toString().indexOf("/*") != -1); // we don't want
																// to strip the
																// comments
			cStmt.setInt(1, 1);
			cStmt.setString(2, "bleh");
			cStmt.execute();
		} finally {
			if (cStmt != null) {
				cStmt.close();
			}
		}
	}

	/**
	 * Tests fix for BUG#28689 - CallableStatement.executeBatch() doesn't work
	 * when connection property "noAccessToProcedureBodies" has been set to
	 * "true".
	 * 
	 * The fix involves changing the behavior of "noAccessToProcedureBodies", in
	 * that the driver will now report all paramters as "IN" paramters but allow
	 * callers to call registerOutParameter() on them.
	 * 
	 * @throws Exception
	 */
	public void testBug28689() throws Exception {
		if (!versionMeetsMinimum(5, 0)) {
			return; // no stored procedures
		}

		createTable("testBug28689", "(" +

		"`id` int(11) NOT NULL auto_increment,"
				+ "`usuario` varchar(255) default NULL,"
				+ "PRIMARY KEY  (`id`)" + ")");

		this.stmt
				.executeUpdate("INSERT INTO testBug28689 (usuario) VALUES ('AAAAAA')");

		createProcedure(
				"sp_testBug28689",
				"(tid INT)"
						+ "\nBEGIN"
						+ "\nUPDATE testBug28689 SET usuario = 'BBBBBB' WHERE id = tid;"
						+ "\nEND");

		Connection noProcedureBodiesConn = getConnectionWithProps("noAccessToProcedureBodies=true");
		CallableStatement cStmt = null;

		try {
			cStmt = noProcedureBodiesConn
					.prepareCall("{CALL sp_testBug28689(?)}");
			cStmt.setInt(1, 1);
			cStmt.addBatch();
			cStmt.executeBatch();

			assertEquals("BBBBBB", getSingleIndexedValueWithQuery(
					noProcedureBodiesConn, 1,
					"SELECT `usuario` FROM testBug28689 WHERE id=1"));
		} finally {
			if (cStmt != null) {
				cStmt.close();
			}

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

	/**
	 * Tests fix for Bug#31823 - CallableStatement.setNull() on a stored
	 * function would throw an ArrayIndexOutOfBounds when setting the last
	 * parameter to null when calling setNull().
	 * 
	 * @throws Exception
	 */
	public void testBug31823() throws Exception {
		if (!versionMeetsMinimum(5, 0)) {
			return; // no stored functions
		}

		createTable("testBug31823",
				"(value_1 BIGINT PRIMARY KEY,value_2 VARCHAR(20))");

		createFunction(
				"f_testBug31823",
				"(value_1_v BIGINT,value_2_v VARCHAR(20)) RETURNS BIGINT "
						+ "DETERMINISTIC MODIFIES SQL DATA BEGIN INSERT INTO testBug31823 VALUES (value_1_v,value_2_v); "
						+ "RETURN value_1_v; END;");

		// Prepare the function call
		CallableStatement callable = null;

		try {
			callable = this.conn.prepareCall("{? = call f_testBug31823(?,?)}");

			callable.registerOutParameter(1, Types.BIGINT);

			// Add row with non-null value
			callable.setLong(2, 1);
			callable.setString(3, "Non-null value");
			callable.executeUpdate();
			assertEquals(1, callable.getLong(1));

			// Add row with null value
			callable.setLong(2, 2);
			callable.setNull(3, Types.VARCHAR);
			callable.executeUpdate();
			assertEquals(2, callable.getLong(1));

			Method[] setters = CallableStatement.class.getMethods();

			for (int i = 0; i < setters.length; i++) {
				if (setters[i].getName().startsWith("set")) {
					Class[] args = setters[i].getParameterTypes();

					if (args.length == 2 && args[0].equals(Integer.TYPE)) {
						if (!args[1].isPrimitive()) {
							try {
								setters[i].invoke(callable, new Object[] {
										new Integer(2), null });
							} catch (InvocationTargetException ive) {
								if (!(ive.getCause() instanceof com.mysql.jdbc.NotImplemented || 
										ive.getCause().getClass().getName().equals("java.sql.SQLFeatureNotSupportedException"))) {
									throw ive;
								}
							}
						} else {
							if (args[1].getName().equals("boolean")) {
								try {
									setters[i].invoke(callable, new Object[] {
											new Integer(2), Boolean.FALSE });
								} catch (InvocationTargetException ive) {
									if (!(ive.getCause() instanceof com.mysql.jdbc.NotImplemented || 
											ive.getCause().getClass().getName().equals("java.sql.SQLFeatureNotSupportedException"))) {
										throw ive;
									}
								}
							}

							if (args[1].getName().equals("byte")) {

								try {
									setters[i].invoke(callable,
											new Object[] { new Integer(2),
													new Byte((byte) 0) });
								} catch (InvocationTargetException ive) {
									if (!(ive.getCause() instanceof com.mysql.jdbc.NotImplemented || 
											ive.getCause().getClass().getName().equals("java.sql.SQLFeatureNotSupportedException"))) {
										throw ive;
									}
								}

							}

							if (args[1].getName().equals("double")) {

								try {
									setters[i].invoke(callable, new Object[] {
											new Integer(2), new Double(0) });
								} catch (InvocationTargetException ive) {
									if (!(ive.getCause() instanceof com.mysql.jdbc.NotImplemented || 
											ive.getCause().getClass().getName().equals("java.sql.SQLFeatureNotSupportedException"))) {
										throw ive;
									}
								}

							}

							if (args[1].getName().equals("float")) {

								try {
									setters[i].invoke(callable, new Object[] {
											new Integer(2), new Float(0) });
								} catch (InvocationTargetException ive) {
									if (!(ive.getCause() instanceof com.mysql.jdbc.NotImplemented || 
											ive.getCause().getClass().getName().equals("java.sql.SQLFeatureNotSupportedException"))) {
										throw ive;
									}
								}

							}

							if (args[1].getName().equals("int")) {

								try {
									setters[i].invoke(callable, new Object[] {
											new Integer(2), new Integer(0) });
								} catch (InvocationTargetException ive) {
									if (!(ive.getCause() instanceof com.mysql.jdbc.NotImplemented || 
											ive.getCause().getClass().getName().equals("java.sql.SQLFeatureNotSupportedException"))) {
										throw ive;
									}
								}

							}

							if (args[1].getName().equals("long")) {
								try {
									setters[i].invoke(callable, new Object[] {
											new Integer(2), new Long(0) });
								} catch (InvocationTargetException ive) {
									if (!(ive.getCause() instanceof com.mysql.jdbc.NotImplemented || 
											ive.getCause().getClass().getName().equals("java.sql.SQLFeatureNotSupportedException"))) {
										throw ive;
									}
								}
							}

							if (args[1].getName().equals("short")) {
								try {
									setters[i].invoke(callable, new Object[] {
											new Integer(2),
											new Short((short) 0) });
								} catch (InvocationTargetException ive) {
									if (!(ive.getCause() instanceof com.mysql.jdbc.NotImplemented || 
											ive.getCause().getClass().getName().equals("java.sql.SQLFeatureNotSupportedException"))) {
										throw ive;
									}
								}
							}
						}
					}
				}
			}
		} finally {
			if (callable != null) {
				callable.close();
			}
		}
	}

	/**
	 * Tests fix for Bug#32246 - When unpacking rows directly, we don't hand off
	 * error message packets to the internal method which decodes them
	 * correctly, so no exception is rasied, and the driver than hangs trying to
	 * read rows that aren't there...
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testBug32246() throws Exception {
		if (!versionMeetsMinimum(5, 0)) {
			return;
		}

		doBug32246(this.conn);
		dropTable("test_table_2");
		dropTable("test_table_1");
		doBug32246(getConnectionWithProps("useDirectRowUnpack=false"));
	}

	private void doBug32246(Connection aConn) throws SQLException {
		createTable("test_table_1",
				"(value_1 BIGINT PRIMARY KEY) ENGINE=InnoDB");
		this.stmt.executeUpdate("INSERT INTO test_table_1 VALUES (1)");
		createTable("test_table_2",
				"(value_2 BIGINT PRIMARY KEY) ENGINE=InnoDB");
		this.stmt.executeUpdate("DROP FUNCTION IF EXISTS test_function");
		createFunction("test_function", "() RETURNS BIGINT "
				+ "DETERMINISTIC MODIFIES SQL DATA BEGIN "
				+ "DECLARE max_value BIGINT; "
				+ "SELECT MAX(value_1) INTO max_value FROM test_table_2; "
				+ "RETURN max_value; END;");

		CallableStatement callable = null;

		try {
			callable = aConn.prepareCall("{? = call test_function()}");

			callable.registerOutParameter(1, Types.BIGINT);

			try {
				callable.executeUpdate();
				fail("impossible; we should never get here.");
			} catch (SQLException sqlEx) {
				assertEquals("42S22", sqlEx.getSQLState());
			}

			createTable("test_table_1",
					"(value_1 BIGINT PRIMARY KEY) ENGINE=InnoDB");
			this.stmt.executeUpdate("INSERT INTO test_table_1 VALUES (1)");
			createTable(
					"test_table_2",
					"(value_2 BIGINT PRIMARY KEY, "
							+ " FOREIGN KEY (value_2) REFERENCES test_table_1 (value_1) ON DELETE CASCADE) ENGINE=InnoDB");
			createFunction(
					"test_function",
					"(value BIGINT) RETURNS BIGINT "
							+ "DETERMINISTIC MODIFIES SQL DATA BEGIN "
							+ "INSERT INTO test_table_2 VALUES (value); RETURN value; END;");

			callable = aConn.prepareCall("{? = call test_function(?)}");
			callable.registerOutParameter(1, Types.BIGINT);

			callable.setLong(2, 1);
			callable.executeUpdate();

			callable.setLong(2, 2);

			try {
				callable.executeUpdate();
				fail("impossible; we should never get here.");
			} catch (SQLException sqlEx) {
				assertEquals("23000", sqlEx.getSQLState());
			}
		} finally {
			if (callable != null) {
				callable.close();
			}
		}
	}

	public void testBitSp() throws Exception {
		if (!versionMeetsMinimum(5, 0)) {
			return;
		}

		createTable("`Bit_Tab`", "(" + " `MAX_VAL` tinyint(1) default NULL,"
				+ " `MIN_VAL` tinyint(1) default NULL,"
				+ " `NULL_VAL` tinyint(1) default NULL)");

		createProcedure(
				"Bit_Proc",
				"(out MAX_PARAM TINYINT, out MIN_PARAM TINYINT, out NULL_PARAM TINYINT)"
						+ "begin select MAX_VAL, MIN_VAL, NULL_VAL  into MAX_PARAM, MIN_PARAM, NULL_PARAM from Bit_Tab; end");

		Boolean minBooleanVal;
		Boolean oRetVal;

		String Min_Val_Query = "SELECT MIN_VAL from Bit_Tab";
		String sMaxBooleanVal = "1";
		// sMaxBooleanVal = "true";
		Boolean bool = Boolean.valueOf("true");
		String Min_Insert = "insert into Bit_Tab values(1,0,null)";
		// System.out.println("Value to insert=" + extractVal(Min_Insert,1));
		CallableStatement cstmt;

		stmt.executeUpdate("delete from Bit_Tab");
		stmt.executeUpdate(Min_Insert);
		cstmt = conn.prepareCall("{call Bit_Proc(?,?,?)}");

		System.out.println("register the output parameters");
		cstmt.registerOutParameter(1, java.sql.Types.BIT);
		cstmt.registerOutParameter(2, java.sql.Types.BIT);
		cstmt.registerOutParameter(3, java.sql.Types.BIT);

		System.out.println("execute the procedure");
		cstmt.executeUpdate();

		System.out.println("invoke getBoolean method");
		boolean bRetVal = cstmt.getBoolean(2);
		oRetVal = new Boolean(bRetVal);
		minBooleanVal = new Boolean("false");
		rs = stmt.executeQuery(Min_Val_Query);
		if (oRetVal.equals(minBooleanVal))
			System.out.println("getBoolean returns the Minimum value ");
		else {
			System.out
					.println("getBoolean() did not return the Minimum value, getBoolean Failed!");

		}
	}
	
	public void testNotReallyCallableStatement() throws Exception {
		if (!versionMeetsMinimum(5, 0)) {
			return;
		}
		
		CallableStatement cstmt = null;
		
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testNotReallyCallableStatement");
			cstmt = this.conn.prepareCall("CREATE TABLE testNotReallyCallableStatement(field1 INT)");
			
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testNotReallyCallableStatement");
			
			if (cstmt != null) {
				cstmt.close();
			}
		}
	}
}