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

	 * 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)," + " ('e',600.00)");

		this.stmt
				.executeUpdate("INSERT INTO tblTestBug2297_2 (`id`,`CreatedOn`) VALUES "
						+ "('d','2006-08-31 00:00:00'),"
						+ "('e','2006-08-31 00:00:00'),"
						+ "('b','2006-08-31 00:00:00'),"
						+ "('c','2006-08-31 00:00:00'),"
						+ "('a','2006-08-31 00:00:00')");

		try {
			this.pstmt = this.conn.prepareStatement("{CALL testBug22297(?)}");
			this.pstmt.setInt(1, 1);
			this.rs = this.pstmt.executeQuery();

			String[] ids = new String[] { "a", "b", "c", "d", "e" };
			int pos = 0;

			while (this.rs.next()) {
				assertEquals(ids[pos++], rs.getString(1));
				assertEquals(100, rs.getInt(2));
			}

			assertTrue(this.pstmt.getClass().getName().indexOf("Server") == -1);
		} finally {
			closeMemberJDBCResources();
		}

	}

	public void testHugeNumberOfParameters() throws Exception {
		if (!serverSupportsStoredProcedures()) {
			return;
		}

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

		StringBuffer procDef = new StringBuffer(
				"CREATE PROCEDURE testHugeNumberOfParameters(");

		for (int i = 0; i < 274; i++) {
			if (i != 0) {
				procDef.append(",");
			}

			procDef.append(" OUT param_" + i + " VARCHAR(32)");
		}

		procDef.append(")\nBEGIN\nSELECT 1;\nEND");
		this.stmt.executeUpdate(procDef.toString());

		CallableStatement cStmt = null;

		try {
			cStmt = this.conn
					.prepareCall("{call testHugeNumberOfParameters(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
							+

							"?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
							+ "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
							+ "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
							+ "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
							+ "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
							+ "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
			cStmt.registerOutParameter(274, Types.VARCHAR);

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

	public void testPrepareOfMultiRs() throws Exception {
		if (!serverSupportsStoredProcedures()) {
			return;
		}

		this.stmt.executeUpdate("Drop procedure if exists p");
		this.stmt
				.executeUpdate("create procedure p () begin select 1; select 2; end;");
		PreparedStatement ps = null;

		try {
			ps = this.conn.prepareStatement("call p()");

			ps.execute();
			this.rs = ps.getResultSet();
			assertTrue(this.rs.next());
			assertEquals(1, this.rs.getInt(1));
			assertTrue(ps.getMoreResults());
			this.rs = ps.getResultSet();
			assertTrue(this.rs.next());
			assertEquals(2, this.rs.getInt(1));
			assertTrue(!ps.getMoreResults());
		} finally {
			if (this.rs != null) {
				this.rs.close();
				this.rs = null;
			}

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

	}

	/**
	 * Tests fix for BUG#25379 - INOUT parameters in CallableStatements get
	 * doubly-escaped.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug25379() throws Exception {
		if (!serverSupportsStoredProcedures()) {
			return;
		}

		createTable("testBug25379", "(col char(40))");

		try {
			this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS sp_testBug25379");
			this.stmt
					.executeUpdate("CREATE PROCEDURE sp_testBug25379 (INOUT invalue char(255))"
							+ "\nBEGIN"
							+ "\ninsert into testBug25379(col) values(invalue);"
							+ "\nEND");

			CallableStatement cstmt = this.conn
					.prepareCall("{call sp_testBug25379(?)}");
			cstmt.setString(1, "'john'");
			cstmt.executeUpdate();
			assertEquals("'john'", cstmt.getString(1));
			assertEquals("'john'", getSingleValue("testBug25379", "col", "")
					.toString());
		} finally {
			this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS sp_testBug25379");
		}
	}

	/**
	 * Tests fix for BUG#25715 - CallableStatements with OUT/INOUT parameters
	 * that are "binary" have extra 7 bytes (which happens to be the _binary
	 * introducer!)
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug25715() throws Exception {
		if (!serverSupportsStoredProcedures()) {
			return; // no stored procs
		}

		if (isRunningOnJdk131()) {
			return; // no such method to test
		}

		createProcedure("spbug25715", "(INOUT mblob MEDIUMBLOB)" + "BEGIN"
				+ " SELECT 1 FROM DUAL WHERE 1=0;" + "\nEND");
		CallableStatement cstmt = null;

		try {
			cstmt = this.conn.prepareCall("{call spbug25715(?)}");

			byte[] buf = new byte[65];
			for (int i = 0; i < 65; i++)
				buf[i] = 1;
			int il = buf.length;

			int[] typesToTest = new int[] { Types.BIT, Types.BINARY,
					Types.BLOB, Types.JAVA_OBJECT, Types.LONGVARBINARY,
					Types.VARBINARY };

			for (int i = 0; i < typesToTest.length; i++) {

				cstmt.setBinaryStream("mblob", new ByteArrayInputStream(buf),
						buf.length);
				cstmt.registerOutParameter("mblob", typesToTest[i]);

				cstmt.executeUpdate();

				InputStream is = cstmt.getBlob("mblob").getBinaryStream();
				ByteArrayOutputStream bOut = new ByteArrayOutputStream();

				int bytesRead = 0;
				byte[] readBuf = new byte[256];

				while ((bytesRead = is.read(readBuf)) != -1) {
					bOut.write(readBuf, 0, bytesRead);
				}

				byte[] fromSelectBuf = bOut.toByteArray();

				int ol = fromSelectBuf.length;

				assertEquals(il, ol);
			}

			cstmt.close();
		} finally {
			closeMemberJDBCResources();

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

	}

	protected boolean serverSupportsStoredProcedures() throws SQLException {
		return versionMeetsMinimum(5, 0);
	}

	public void testBug26143() throws Exception {
		if (!serverSupportsStoredProcedures()) {
			return; // no stored procedure support
		}

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

		this.stmt
				.executeUpdate("CREATE DEFINER=CURRENT_USER PROCEDURE testBug26143(I INT) COMMENT 'abcdefg'"
						+ "\nBEGIN\n" + "SELECT I * 10;" + "\nEND");

		this.conn.prepareCall("{call testBug26143(?)").close();
	}

	/**
	 * Tests fix for BUG#26959 - comments confuse procedure parser.
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testBug26959() throws Exception {
		if (!serverSupportsStoredProcedures()) {
			return;
		}

		createProcedure(
				"testBug26959",
				"(_ACTION varchar(20),"
						+ "\n`/*dumb-identifier-1*/` int,"
						+ "\n`#dumb-identifier-2` int,"
						+ "\n`--dumb-identifier-3` int,"
						+ "\n_CLIENT_ID int, -- ABC"
						+ "\n_LOGIN_ID  int, # DEF"
						+ "\n_WHERE varchar(2000),"
						+ "\n_SORT varchar(2000),"
						+ "\n out _SQL varchar(/* inline right here - oh my gosh! */ 8000),"
						+ "\n _SONG_ID int,"
						+ "\n  _NOTES varchar(2000),"
						+ "\n out _RESULT varchar(10)"
						+ "\n /*"
						+ "\n ,    -- Generic result parameter"
						+ "\n out _PERIOD_ID int,         -- Returns the period_id. Useful when using @PREDEFLINK to return which is the last period"
						+ "\n   _SONGS_LIST varchar(8000),"
						+ "\n  _COMPOSERID int,"
						+ "\n  _PUBLISHERID int,"
						+ "\n   _PREDEFLINK int        -- If the user is accessing through a predefined link: 0=none  1=last period"
						+ "\n */) BEGIN SELECT 1; END");

		createProcedure(
				"testBug26959_1",
				"(`/*id*/` /* before type 1 */ varchar(20),"
						+ "/* after type 1 */ OUT result2 DECIMAL(/*size1*/10,/*size2*/2) /* p2 */)"
						+ "BEGIN SELECT action, result; END");

		try {
			this.conn.prepareCall(
					"{call testBug26959(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}")
					.close();
			this.rs = this.conn.getMetaData().getProcedureColumns(
					this.conn.getCatalog(), null, "testBug26959", "%");

			String[] parameterNames = new String[] { "_ACTION",
					"/*dumb-identifier-1*/", "#dumb-identifier-2",
					"--dumb-identifier-3", "_CLIENT_ID", "_LOGIN_ID", "_WHERE",
					"_SORT", "_SQL", "_SONG_ID", "_NOTES", "_RESULT" };

			int[] parameterTypes = new int[] { Types.VARCHAR, Types.INTEGER,
					Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER,
					Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.INTEGER,
					Types.VARCHAR, Types.VARCHAR };

			int[] direction = new int[] { DatabaseMetaData.procedureColumnIn,
					DatabaseMetaData.procedureColumnIn,
					DatabaseMetaData.procedureColumnIn,
					DatabaseMetaData.procedureColumnIn,
					DatabaseMetaData.procedureColumnIn,
					DatabaseMetaData.procedureColumnIn,
					DatabaseMetaData.procedureColumnIn,
					DatabaseMetaData.procedureColumnIn,
					DatabaseMetaData.procedureColumnOut,
					DatabaseMetaData.procedureColumnIn,
					DatabaseMetaData.procedureColumnIn,
					DatabaseMetaData.procedureColumnOut };

			int[] precision = new int[] { 20, 10, 10, 10, 10, 10, 2000, 2000,
					8000, 10, 2000, 10 };

			int index = 0;

			while (this.rs.next()) {
				assertEquals(parameterNames[index], this.rs
						.getString("COLUMN_NAME"));
				assertEquals(parameterTypes[index], this.rs.getInt("DATA_TYPE"));
				assertEquals(precision[index], this.rs.getInt("PRECISION"));
				assertEquals(direction[index], this.rs.getInt("COLUMN_TYPE"));
				index++;
			}

			this.rs.close();

			index = 0;
			parameterNames = new String[] { "/*id*/", "result2" };
			parameterTypes = new int[] { Types.VARCHAR, Types.DECIMAL };
			precision = new int[] { 20, 10 };
			direction = new int[] { DatabaseMetaData.procedureColumnIn,
					DatabaseMetaData.procedureColumnOut };
			int[] scale = new int[] { 0, 2 };

			this.conn.prepareCall("{call testBug26959_1(?, ?)}").close();

			this.rs = this.conn.getMetaData().getProcedureColumns(
					this.conn.getCatalog(), null, "testBug26959_1", "%");

			while (this.rs.next()) {
				assertEquals(parameterNames[index], this.rs
						.getString("COLUMN_NAME"));
				assertEquals(parameterTypes[index], this.rs.getInt("DATA_TYPE"));
				assertEquals(precision[index], this.rs.getInt("PRECISION"));
				assertEquals(scale[index], this.rs.getInt("SCALE"));
				assertEquals(direction[index], this.rs.getInt("COLUMN_TYPE"));

				index++;
			}
		} finally {
			closeMemberJDBCResources();
		}
	}

	/**
	 * Tests fix for BUG#27400 - CALL [comment] some_proc() doesn't work
	 */
	public void testBug27400() throws Exception {
		if (!serverSupportsStoredProcedures()) {
			return; // SPs not supported
		}