callablestatementregressiontest.java

mysql的jdbc驱动

					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));
					}

					String quoteChar = db2Connection.getMetaData()
							.getIdentifierQuoteString();

					cstmt = db2Connection.prepareCall("{ call " + quoteChar
							+ db1Connection.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_2");
					}

					if (db1Connection != null) {
						db1Connection.createStatement().executeUpdate(
								"DROP PROCEDURE IF EXISTS COMPROVAR_USUARI");
						db1Connection.createStatement().executeUpdate(
								"DROP DATABASE IF EXISTS db_9319_1");
					}
				}
			}
		}
	}

	/*
	 * public void testBug9319() throws Exception { boolean doASelect = false; //
	 * SELECT currently causes the server to hang on the // last execution of
	 * this testcase, filed as BUG#9405
	 * 
	 * if (versionMeetsMinimum(5, 0, 2)) { if (isAdminConnectionConfigured()) {
	 * Connection db2Connection = null; Connection db1Connection = null;
	 * 
	 * try { db2Connection = getAdminConnection();
	 * 
	 * db2Connection.createStatement().executeUpdate( "CREATE DATABASE IF NOT
	 * EXISTS db_9319"); db2Connection.setCatalog("db_9319");
	 * 
	 * db2Connection.createStatement().executeUpdate( "DROP PROCEDURE IF EXISTS
	 * COMPROVAR_USUARI");
	 * 
	 * db2Connection.createStatement().executeUpdate( "CREATE PROCEDURE
	 * COMPROVAR_USUARI(IN p_CodiUsuari VARCHAR(10)," + "\nIN p_contrasenya
	 * VARCHAR(10)," + "\nOUT p_userId INTEGER," + "\nOUT p_userName
	 * VARCHAR(30)," + "\nOUT p_administrador VARCHAR(1)," + "\nOUT p_idioma
	 * VARCHAR(2))" + "\nBEGIN"
	 *  + (doASelect ? "\nselect 2;" : "\nSELECT 2 INTO p_administrador;" ) +
	 * "\nEND");
	 * 
	 * this.stmt .executeUpdate("DROP PROCEDURE IF EXISTS COMPROVAR_USUARI");
	 * this.stmt .executeUpdate("CREATE PROCEDURE COMPROVAR_USUARI(IN
	 * p_CodiUsuari VARCHAR(10)," + "\nIN p_contrasenya VARCHAR(10)," + "\nOUT
	 * p_userId INTEGER," + "\nOUT p_userName VARCHAR(30)," + "\nOUT
	 * p_administrador VARCHAR(1))" + "\nBEGIN" + (doASelect ? "\nselect 1;" :
	 * "\nSELECT 1 INTO p_administrador;" ) + "\nEND");
	 * 
	 * CallableStatement cstmt = db2Connection .prepareCall("{ call
	 * COMPROVAR_USUARI(?, ?, ?, ?, ?, ?) }"); 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.registerOutParameter(6, java.sql.Types.VARCHAR);
	 * 
	 * cstmt.execute();
	 * 
	 * if (doASelect) { this.rs = cstmt.getResultSet();
	 * assertTrue(this.rs.next()); assertEquals(2, this.rs.getInt(1)); } else {
	 * assertEquals(2, cstmt.getInt(5)); }
	 * 
	 * cstmt = this.conn .prepareCall("{ call COMPROVAR_USUARI(?, ?, ?, ?, ?, ?)
	 * }"); 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);
	 * 
	 * try { cstmt.registerOutParameter(6, java.sql.Types.VARCHAR);
	 * fail("Should've thrown an exception"); } catch (SQLException sqlEx) {
	 * assertEquals(SQLError.SQL_STATE_ILLEGAL_ARGUMENT, sqlEx .getSQLState()); }
	 * 
	 * cstmt = this.conn .prepareCall("{ call COMPROVAR_USUARI(?, ?, ?, ?, ?)
	 * }"); 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)); }
	 * 
	 * String quoteChar =
	 * 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 (versionMeetsMinimum(5, 0)) {
			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 (versionMeetsMinimum(5, 0)) {
			CallableStatement cStmt = null;

			try {
				this.stmt.executeUpdate("DROP FUNCTION IF EXISTS testBug10310");
				this.stmt
						.executeUpdate("CREATE FUNCTION testBug10310(a float) RETURNS INT"
								+ "\nBEGIN" + "\nRETURN a;" + "\nEND");
				cStmt = this.conn.prepareCall("{? = CALL testBug10310(?)}");
				cStmt.registerOutParameter(1, Types.INTEGER);
				cStmt.setFloat(1, 2);
				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());

				// 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(1, Types.FLOAT);

				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());

			} 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 (versionMeetsMinimum(5, 0) && 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(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();
				}
			}
		}
	}
}