| List: | Commits | « Previous MessageNext Message » | |
| From: | mmatthews | Date: | March 26 2007 6:09pm |
| Subject: | Connector/J commit: r6370 - branches/branch_5_0/connector-j branches/branch_5_0/connector-j/src/com/mysql/jdbc branches/branch_5_0/connector-j/src/tes... | ||
| View as plain text | |||
Modified: branches/branch_5_0/connector-j/CHANGES branches/branch_5_0/connector-j/src/com/mysql/jdbc/CallableStatement.java branches/branch_5_0/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java trunk/connector-j/CHANGES trunk/connector-j/src/com/mysql/jdbc/CallableStatement.java trunk/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java Log: - Fixed BUG#25715 - CallableStatements with OUT/INOUT parameters that are "binary" (blobs, bits, (var)binary, java_object) have extra 7 bytes (which happens to be the _binary introducer!) Refactored all of the if (versionMeetsMinimum(5, 0)) to a single serverSupportsStoredProcedures() method (so we _could_ disable the stored procedure tests if we wanted). Need to eventually refactor _all_ of the tests to use createProcedure() (perhaps in trunk). Modified: branches/branch_5_0/connector-j/CHANGES =================================================================== --- branches/branch_5_0/connector-j/CHANGES 2007-03-26 15:06:20 UTC (rev 6369) +++ branches/branch_5_0/connector-j/CHANGES 2007-03-26 16:09:00 UTC (rev 6370) @@ -63,7 +63,11 @@ - Fixed BUG#25328 - BIT(> 1) is returned as java.lang.String from ResultSet.getObject() rather than byte[]. - + + - Fixed BUG#25715 - CallableStatements with OUT/INOUT parameters that + are "binary" (blobs, bits, (var)binary, java_object) have extra 7 bytes + (which happens to be the _binary introducer!) + 03-01-07 - Version 5.0.5 - Fixed BUG#23645 - Some collations/character sets reported as "unknown" Modified: branches/branch_5_0/connector-j/src/com/mysql/jdbc/CallableStatement.java =================================================================== --- branches/branch_5_0/connector-j/src/com/mysql/jdbc/CallableStatement.java 2007-03-26 15:06:20 UTC (rev 6369) +++ branches/branch_5_0/connector-j/src/com/mysql/jdbc/CallableStatement.java 2007-03-26 16:09:00 UTC (rev 6370) @@ -1958,8 +1958,8 @@ setPstmt = this.connection .clientPrepareStatement(queryBuf.toString()); - byte[] parameterAsBytes = this - .getBytesRepresentation(inParamInfo.index); + byte[] parameterAsBytes = getBytesRepresentation( + inParamInfo.index); if (parameterAsBytes != null) { if (parameterAsBytes.length > 8 @@ -1974,8 +1974,22 @@ setPstmt.setBytesNoEscapeNoQuotes(1, parameterAsBytes); } else { - setPstmt.setBytesNoEscape(1, parameterAsBytes); // they've already been escaped by - // the inherited PreparedStatement methods + int sqlType = inParamInfo.desiredJdbcType; + + switch (sqlType) { + case Types.BIT: + case Types.BINARY: + case Types.BLOB: + case Types.JAVA_OBJECT: + case Types.LONGVARBINARY: + case Types.VARBINARY: + setPstmt.setBytes(1, parameterAsBytes); + break; + default: + // the inherited PreparedStatement methods + // have already escaped and quoted these parameters + setPstmt.setBytesNoEscape(1, parameterAsBytes); + } } } else { setPstmt.setNull(1, Types.NULL); Modified: branches/branch_5_0/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java =================================================================== --- branches/branch_5_0/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java 2007-03-26 15:06:20 UTC (rev 6369) +++ branches/branch_5_0/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java 2007-03-26 16:09:00 UTC (rev 6370) @@ -1,5 +1,5 @@ /* - Copyright (C) 2002-2004 MySQL AB + Copyright (C) 2002-2007 MySQL AB This program is free software; you can redistribute it and/or modify it under the terms of version 2 of the GNU General Public License as @@ -24,6 +24,9 @@ */ package testsuite.regression; +import java.io.ByteArrayInputStream; +import java.io.ByteArrayOutputStream; +import java.io.InputStream; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.PreparedStatement; @@ -76,20 +79,22 @@ * if an error occurs. */ public void testBug3539() throws Exception { - if (versionMeetsMinimum(5, 0)) { - try { - this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3539"); - this.stmt.executeUpdate("CREATE PROCEDURE testBug3539()\n" - + "BEGIN\n" + "SELECT 1;" + "end\n"); + if (!serverSupportsStoredProcedures()) { + return; + } - this.rs = this.conn.getMetaData().getProcedures(null, null, - "testBug3539"); + try { + this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3539"); + this.stmt.executeUpdate("CREATE PROCEDURE testBug3539()\n" + + "BEGIN\n" + "SELECT 1;" + "end\n"); - assertTrue(this.rs.next()); - assertTrue("testBug3539".equals(this.rs.getString(3))); - } finally { - this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3539"); - } + this.rs = this.conn.getMetaData().getProcedures(null, null, + "testBug3539"); + + assertTrue(this.rs.next()); + assertTrue("testBug3539".equals(this.rs.getString(3))); + } finally { + this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3539"); } } @@ -101,28 +106,29 @@ * if an error occurs. */ public void testBug3540() throws Exception { - if (versionMeetsMinimum(5, 0)) { - try { - this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3540"); - this.stmt - .executeUpdate("CREATE PROCEDURE testBug3540(x int, out y int)\n" - + "BEGIN\n" + "SELECT 1;" + "end\n"); + if (!serverSupportsStoredProcedures()) { + return; + } + try { + this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3540"); + this.stmt + .executeUpdate("CREATE PROCEDURE testBug3540(x int, out y int)\n" + + "BEGIN\n" + "SELECT 1;" + "end\n"); - this.rs = this.conn.getMetaData().getProcedureColumns(null, - null, "testBug3540%", "%"); + this.rs = this.conn.getMetaData().getProcedureColumns(null, + null, "testBug3540%", "%"); - assertTrue(this.rs.next()); - assertTrue("testBug3540".equals(this.rs.getString(3))); - assertTrue("x".equals(this.rs.getString(4))); + assertTrue(this.rs.next()); + assertTrue("testBug3540".equals(this.rs.getString(3))); + assertTrue("x".equals(this.rs.getString(4))); - assertTrue(this.rs.next()); - assertTrue("testBug3540".equals(this.rs.getString(3))); - assertTrue("y".equals(this.rs.getString(4))); + assertTrue(this.rs.next()); + assertTrue("testBug3540".equals(this.rs.getString(3))); + assertTrue("y".equals(this.rs.getString(4))); - assertTrue(!this.rs.next()); - } finally { - this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3540"); - } + assertTrue(!this.rs.next()); + } finally { + this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3540"); } } @@ -134,46 +140,48 @@ * if the test fails. */ public void testBug7026() throws Exception { - if (versionMeetsMinimum(5, 0)) { - try { - this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug7026"); - this.stmt - .executeUpdate("CREATE PROCEDURE testBug7026(x int, out y int)\n" - + "BEGIN\n" + "SELECT 1;" + "end\n"); + if (!serverSupportsStoredProcedures()) { + return; + } - // - // Should be found this time. - // - this.rs = this.conn.getMetaData().getProcedures( - this.conn.getCatalog(), null, "testBug7026"); + try { + this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug7026"); + this.stmt + .executeUpdate("CREATE PROCEDURE testBug7026(x int, out y int)\n" + + "BEGIN\n" + "SELECT 1;" + "end\n"); - assertTrue(this.rs.next()); - assertTrue("testBug7026".equals(this.rs.getString(3))); + // + // Should be found this time. + // + this.rs = this.conn.getMetaData().getProcedures( + this.conn.getCatalog(), null, "testBug7026"); - assertTrue(!this.rs.next()); + assertTrue(this.rs.next()); + assertTrue("testBug7026".equals(this.rs.getString(3))); - // - // This time, shouldn't be found, because not associated with - // this (bogus) catalog - // - this.rs = this.conn.getMetaData().getProcedures("abfgerfg", - null, "testBug7026"); - assertTrue(!this.rs.next()); + assertTrue(!this.rs.next()); - // - // Should be found this time as well, as we haven't - // specified a catalog. - // - this.rs = this.conn.getMetaData().getProcedures(null, null, - "testBug7026"); + // + // This time, shouldn't be found, because not associated with + // this (bogus) catalog + // + this.rs = this.conn.getMetaData().getProcedures("abfgerfg", + null, "testBug7026"); + assertTrue(!this.rs.next()); - assertTrue(this.rs.next()); - assertTrue("testBug7026".equals(this.rs.getString(3))); + // + // Should be found this time as well, as we haven't + // specified a catalog. + // + this.rs = this.conn.getMetaData().getProcedures(null, null, + "testBug7026"); - assertTrue(!this.rs.next()); - } finally { - this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug7026"); - } + assertTrue(this.rs.next()); + assertTrue("testBug7026".equals(this.rs.getString(3))); + + assertTrue(!this.rs.next()); + } finally { + this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug7026"); } } @@ -186,151 +194,154 @@ * if the test fails */ public void testBug9319() throws Exception { + if (!serverSupportsStoredProcedures()) { + return; + } + boolean doASelect = true; // 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(); - db1Connection = getAdminConnection(); + if (isAdminConnectionConfigured()) { + Connection db2Connection = null; + Connection db1Connection = null; - db2Connection.createStatement().executeUpdate( - "CREATE DATABASE IF NOT EXISTS db_9319_2"); - db2Connection.setCatalog("db_9319_2"); + try { + db2Connection = getAdminConnection(); + db1Connection = getAdminConnection(); - db2Connection.createStatement().executeUpdate( - "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI"); + db2Connection.createStatement().executeUpdate( + "CREATE DATABASE IF NOT EXISTS db_9319_2"); + db2Connection.setCatalog("db_9319_2"); - 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" + db2Connection.createStatement().executeUpdate( + "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI"); - + (doASelect ? "\nselect 2;" - : "\nSELECT 2 INTO p_administrador;") - + "\nEND"); + 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" - db1Connection.createStatement().executeUpdate( - "CREATE DATABASE IF NOT EXISTS db_9319_1"); - db1Connection.setCatalog("db_9319_1"); + + (doASelect ? "\nselect 2;" + : "\nSELECT 2 INTO p_administrador;") + + "\nEND"); - db1Connection.createStatement().executeUpdate( - "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI"); - db1Connection - .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))" - + "\nBEGIN" - + (doASelect ? "\nselect 1;" - : "\nSELECT 1 INTO p_administrador;") - + "\nEND"); + db1Connection.createStatement().executeUpdate( + "CREATE DATABASE IF NOT EXISTS db_9319_1"); + db1Connection.setCatalog("db_9319_1"); - 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); + db1Connection.createStatement().executeUpdate( + "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI"); + db1Connection + .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))" + + "\nBEGIN" + + (doASelect ? "\nselect 1;" + : "\nSELECT 1 INTO p_administrador;") + + "\nEND"); - cstmt.registerOutParameter(6, java.sql.Types.VARCHAR); + 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.execute(); + cstmt.registerOutParameter(6, java.sql.Types.VARCHAR); - if (doASelect) { - this.rs = cstmt.getResultSet(); - assertTrue(this.rs.next()); - assertEquals(2, this.rs.getInt(1)); - } else { - assertEquals(2, cstmt.getInt(5)); - } + cstmt.execute(); - cstmt = db1Connection - .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); + if (doASelect) { + this.rs = cstmt.getResultSet(); + assertTrue(this.rs.next()); + assertEquals(2, this.rs.getInt(1)); + } else { + assertEquals(2, cstmt.getInt(5)); + } - 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 = db1Connection + .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 = db1Connection - .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.execute(); + cstmt = db1Connection + .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); - if (doASelect) { - this.rs = cstmt.getResultSet(); - assertTrue(this.rs.next()); - assertEquals(1, this.rs.getInt(1)); - } else { - assertEquals(1, cstmt.getInt(5)); - } + cstmt.execute(); - String quoteChar = db2Connection.getMetaData() - .getIdentifierQuoteString(); + if (doASelect) { + this.rs = cstmt.getResultSet(); + assertTrue(this.rs.next()); + assertEquals(1, this.rs.getInt(1)); + } else { + assertEquals(1, cstmt.getInt(5)); + } - 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); + String quoteChar = db2Connection.getMetaData() + .getIdentifierQuoteString(); - cstmt.execute(); + 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); - 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"); - } + cstmt.execute(); - if (db1Connection != null) { - db1Connection.createStatement().executeUpdate( - "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI"); - db1Connection.createStatement().executeUpdate( - "DROP DATABASE IF EXISTS db_9319_1"); - } + 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"); + } } } } @@ -434,24 +445,26 @@ * if the test fails. */ public void testBug9682() throws Exception { - if (versionMeetsMinimum(5, 0)) { - CallableStatement cStmt = null; + if (!serverSupportsStoredProcedures()) { + return; + } - 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(); - } + CallableStatement cStmt = null; - this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug9682"); + 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"); } } @@ -464,114 +477,116 @@ * if the test fails. */ public void testBug10310() throws Exception { - if (versionMeetsMinimum(5, 0)) { - CallableStatement cStmt = null; + if (!serverSupportsStoredProcedures()) { + return; + } - 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()); + CallableStatement cStmt = null; - assertEquals(-1, cStmt.executeUpdate()); - assertEquals(2f, cStmt.getInt(1), .001); - assertEquals("java.lang.Integer", cStmt.getObject(1).getClass() - .getName()); + 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()) { - 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 + if (!isRunningOnJdk131()) { + assertEquals(4, cStmt.getParameterMetaData().getParameterCount()); + assertEquals(Types.INTEGER, cStmt.getParameterMetaData().getParameterType(1)); + } - java.sql.DatabaseMetaData dbmd = this.conn.getMetaData(); + assertFalse(cStmt.execute()); + assertEquals(2f, cStmt.getInt(1), .001); + assertEquals("java.lang.Integer", cStmt.getObject(1).getClass() + .getName()); - 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(2f, cStmt.getInt(1), .001); + assertEquals("java.lang.Integer", cStmt.getObject(1).getClass() + .getName()); - assertEquals(-1, cStmt.executeUpdate()); - assertEquals(0f, cStmt.getInt(1), .001); - assertEquals(true, cStmt.wasNull()); - assertEquals(null, cStmt.getObject(1)); - assertEquals(true, cStmt.wasNull()); + if (!isRunningOnJdk131()) { + cStmt.setFloat("a", 4); + cStmt.setInt("b", 1); + cStmt.setInt("c", 1); - - // 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(); - } + // Check metadata while we're at it - this.stmt.executeUpdate("DROP FUNCTION IF EXISTS testBug10310"); + 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 @@ -581,12 +596,12 @@ * if the test fails. */ public void testBug12417() throws Exception { - if (versionMeetsMinimum(5, 0) && isServerRunningOnWindows()) { + if (serverSupportsStoredProcedures() && isServerRunningOnWindows()) { Connection ucCatalogConn = null; try { this.stmt - .executeUpdate("DROP PROCEDURE IF EXISTS testBug12417"); + .executeUpdate("DROP PROCEDURE IF EXISTS testBug12417"); this.stmt.executeUpdate("CREATE PROCEDURE testBug12417()\n" + "BEGIN\n" + "SELECT 1;" + "end\n"); ucCatalogConn = getConnectionWithProps(null); @@ -606,7 +621,7 @@ if (false /* needs to be fixed on server */) { if (versionMeetsMinimum(5, 0)) { this.stmt - .executeUpdate("DROP PROCEDURE IF EXISTS p_testBug15121"); + .executeUpdate("DROP PROCEDURE IF EXISTS p_testBug15121"); this.stmt.executeUpdate("CREATE PROCEDURE p_testBug15121()\n" + "BEGIN\n" + "SELECT * from idonotexist;\n" + "END"); @@ -621,7 +636,7 @@ StringBuffer queryBuf = new StringBuffer("{call "); String quotedId = this.conn.getMetaData() - .getIdentifierQuoteString(); + .getIdentifierQuoteString(); queryBuf.append(quotedId); queryBuf.append(this.conn.getCatalog()); queryBuf.append(quotedId); @@ -645,35 +660,36 @@ */ public void testBug15464() throws Exception { - if (versionMeetsMinimum(5, 0)) { - CallableStatement storedProc = null; + 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"); + 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 = this.conn + .prepareCall("{call testInOutParam(?, ?)}"); - storedProc.setString(1, "abcd"); - storedProc.setInt(2, 4); - storedProc.registerOutParameter(2, Types.INTEGER); + storedProc.setString(1, "abcd"); + storedProc.setInt(2, 4); + storedProc.registerOutParameter(2, Types.INTEGER); - storedProc.execute(); + storedProc.execute(); - assertEquals(5, storedProc.getInt(2)); - } finally { - this.stmt - .executeUpdate("DROP PROCEDURE IF EXISTS testInOutParam"); - } + assertEquals(5, storedProc.getInt(2)); + } finally { + this.stmt + .executeUpdate("DROP PROCEDURE IF EXISTS testInOutParam"); } } @@ -687,24 +703,27 @@ * if the test fails */ public void testBug17898() throws Exception { - if (versionMeetsMinimum(5, 0)) { - 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"); + if (!serverSupportsStoredProcedures()) { + return; + } - CallableStatement cstmt = this.conn - .prepareCall("{CALL testBug17898('foo', ?)}"); - cstmt.registerOutParameter(1, Types.INTEGER); + 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)); + } - if (!isRunningOnJdk131()) { - cstmt.clearParameters(); - cstmt.registerOutParameter("param2", Types.INTEGER); - cstmt.execute(); - assertEquals(1, cstmt.getInt(1)); - } - } } /** @@ -714,24 +733,27 @@ * @throws Exception if the test fails. */ public void testBug21462() throws Exception { - if (versionMeetsMinimum(5, 0)) { - 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"); + 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. @@ -739,30 +761,33 @@ * @throws Exception if the test fails */ public void testBug22024() throws Exception { - if (versionMeetsMinimum(5, 0)) { - 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"); + 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 @@ -771,163 +796,172 @@ * @throws Exception if the test fails */ public void testBug22297() throws Exception { - if (versionMeetsMinimum(5, 0)) { - 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"); + if (!serverSupportsStoredProcedures()) { + return; + } - 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')," + 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)); - } - - assertEquals(this.pstmt.getClass().getName(), - com.mysql.jdbc.PreparedStatement.class.getName()); - } finally { - closeMemberJDBCResources(); + 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)); } + + assertEquals(this.pstmt.getClass().getName(), + com.mysql.jdbc.PreparedStatement.class.getName()); + + } finally { + closeMemberJDBCResources(); } + } - + public void testHugeNumberOfParameters() throws Exception { - if (versionMeetsMinimum(5, 0)) { - this.stmt - .executeUpdate("DROP PROCEDURE IF EXISTS testHugeNumberOfParameters"); + if (!serverSupportsStoredProcedures()) { + return; + } - StringBuffer procDef = new StringBuffer( - "CREATE PROCEDURE testHugeNumberOfParameters("); + this.stmt + .executeUpdate("DROP PROCEDURE IF EXISTS testHugeNumberOfParameters"); - for (int i = 0; i < 274; i++) { - if (i != 0) { - procDef.append(","); - } + StringBuffer procDef = new StringBuffer( + "CREATE PROCEDURE testHugeNumberOfParameters("); - procDef.append(" OUT param_" + i + " VARCHAR(32)"); + for (int i = 0; i < 274; i++) { + if (i != 0) { + procDef.append(","); } - procDef.append(")\nBEGIN\nSELECT 1;\nEND"); - this.stmt.executeUpdate(procDef.toString()); + procDef.append(" OUT param_" + i + " VARCHAR(32)"); + } - CallableStatement cStmt = null; + procDef.append(")\nBEGIN\nSELECT 1;\nEND"); + this.stmt.executeUpdate(procDef.toString()); - try { - cStmt = this.conn - .prepareCall("{call testHugeNumberOfParameters(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," - + + CallableStatement cStmt = null; - "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," - + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," - + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," - + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," - + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," - + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}"); - cStmt.registerOutParameter(274, Types.VARCHAR); + try { + cStmt = this.conn + .prepareCall("{call testHugeNumberOfParameters(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + + - cStmt.execute(); - } finally { - if (cStmt != null) { - cStmt.close(); - } + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}"); + cStmt.registerOutParameter(274, Types.VARCHAR); + + cStmt.execute(); + } finally { + if (cStmt != null) { + cStmt.close(); } } } public void testPrepareOfMultiRs() throws Exception { - if (versionMeetsMinimum(5, 0)) { - this.stmt.executeUpdate("Drop procedure if exists p"); - this.stmt - .executeUpdate("create procedure p () begin select 1; select 2; end;"); - PreparedStatement ps = null; + if (!serverSupportsStoredProcedures()) { + return; + } - 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; - } + this.stmt.executeUpdate("Drop procedure if exists p"); + this.stmt + .executeUpdate("create procedure p () begin select 1; select 2; end;"); + PreparedStatement ps = null; - if (ps != null) { - ps.close(); - } + 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 (!versionMeetsMinimum(5, 0)) { + 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(); @@ -937,65 +971,131 @@ 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 + } + + 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 (!versionMeetsMinimum(5, 0)) { + 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 (!versionMeetsMinimum(5, 0)) { + 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"); + + "\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"); + + "/* after type 1 */ OUT result2 DECIMAL(/*size1*/10,/*size2*/2) /* p2 */)" + + "BEGIN SELECT action, result; END"); try { this.conn.prepareCall( - "{call testBug26959(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}") - .close(); + "{call testBug26959(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}") + .close(); this.rs = this.conn.getMetaData().getProcedureColumns( this.conn.getCatalog(), null, "testBug26959", "%"); @@ -1065,19 +1165,19 @@ closeMemberJDBCResources(); } } - + /** * Tests fix for BUG#27400 - CALL [comment] some_proc() doesn't work */ public void testBug27400() throws Exception { - if (!versionMeetsMinimum(5, 0)) { + if (!serverSupportsStoredProcedures()) { return; // SPs not supported } - + 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 Modified: trunk/connector-j/CHANGES =================================================================== --- trunk/connector-j/CHANGES 2007-03-26 15:06:20 UTC (rev 6369) +++ trunk/connector-j/CHANGES 2007-03-26 16:09:00 UTC (rev 6370) @@ -88,7 +88,11 @@ - Fixed BUG#25328 - BIT(> 1) is returned as java.lang.String from ResultSet.getObject() rather than byte[]. - + + - Fixed BUG#25715 - CallableStatements with OUT/INOUT parameters that + are "binary" (blobs, bits, (var)binary, java_object) have extra 7 bytes + (which happens to be the _binary introducer!) + 03-01-07 - Version 5.0.5 - Fixed BUG#23645 - Some collations/character sets reported as "unknown" Modified: trunk/connector-j/src/com/mysql/jdbc/CallableStatement.java =================================================================== --- trunk/connector-j/src/com/mysql/jdbc/CallableStatement.java 2007-03-26 15:06:20 UTC (rev 6369) +++ trunk/connector-j/src/com/mysql/jdbc/CallableStatement.java 2007-03-26 16:09:00 UTC (rev 6370) @@ -2057,8 +2057,8 @@ setPstmt = this.connection .clientPrepareStatement(queryBuf.toString()); - byte[] parameterAsBytes = this - .getBytesRepresentation(inParamInfo.index); + byte[] parameterAsBytes = getBytesRepresentation( + inParamInfo.index); if (parameterAsBytes != null) { if (parameterAsBytes.length > 8 @@ -2073,7 +2073,22 @@ setPstmt.setBytesNoEscapeNoQuotes(1, parameterAsBytes); } else { - setPstmt.setBytesNoEscape(1, parameterAsBytes); + int sqlType = inParamInfo.desiredJdbcType; + + switch (sqlType) { + case Types.BIT: + case Types.BINARY: + case Types.BLOB: + case Types.JAVA_OBJECT: + case Types.LONGVARBINARY: + case Types.VARBINARY: + setPstmt.setBytes(1, parameterAsBytes); + break; + default: + // the inherited PreparedStatement methods + // have already escaped and quoted these parameters + setPstmt.setBytesNoEscape(1, parameterAsBytes); + } } } else { setPstmt.setNull(1, Types.NULL); Modified: trunk/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java =================================================================== --- trunk/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java 2007-03-26 15:06:20 UTC (rev 6369) +++ trunk/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java 2007-03-26 16:09:00 UTC (rev 6370) @@ -1,5 +1,5 @@ /* - Copyright (C) 2002-2004 MySQL AB + Copyright (C) 2002-2007 MySQL AB This program is free software; you can redistribute it and/or modify it under the terms of version 2 of the GNU General Public License as @@ -24,17 +24,22 @@ */ package testsuite.regression; +import java.io.ByteArrayInputStream; +import java.io.ByteArrayOutputStream; +import java.io.InputStream; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Types; +import java.util.List; import java.util.Properties; import com.mysql.jdbc.DatabaseMetaData; import com.mysql.jdbc.NonRegisteringDriver; import com.mysql.jdbc.SQLError; +import com.mysql.jdbc.StringUtils; import testsuite.BaseTestCase; @@ -74,20 +79,22 @@ * if an error occurs. */ public void testBug3539() throws Exception { - if (versionMeetsMinimum(5, 0)) { - try { - this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3539"); - this.stmt.executeUpdate("CREATE PROCEDURE testBug3539()\n" - + "BEGIN\n" + "SELECT 1;" + "end\n"); + if (!serverSupportsStoredProcedures()) { + return; + } - this.rs = this.conn.getMetaData().getProcedures(null, null, - "testBug3539"); + try { + this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3539"); + this.stmt.executeUpdate("CREATE PROCEDURE testBug3539()\n" + + "BEGIN\n" + "SELECT 1;" + "end\n"); - assertTrue(this.rs.next()); - assertTrue("testBug3539".equals(this.rs.getString(3))); - } finally { - this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3539"); - } + this.rs = this.conn.getMetaData().getProcedures(null, null, + "testBug3539"); + + assertTrue(this.rs.next()); + assertTrue("testBug3539".equals(this.rs.getString(3))); + } finally { + this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3539"); } } @@ -99,28 +106,29 @@ * if an error occurs. */ public void testBug3540() throws Exception { - if (versionMeetsMinimum(5, 0)) { - try { - this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3540"); - this.stmt - .executeUpdate("CREATE PROCEDURE testBug3540(x int, out y int)\n" - + "BEGIN\n" + "SELECT 1;" + "end\n"); + if (!serverSupportsStoredProcedures()) { + return; + } + try { + this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3540"); + this.stmt + .executeUpdate("CREATE PROCEDURE testBug3540(x int, out y int)\n" + + "BEGIN\n" + "SELECT 1;" + "end\n"); - this.rs = this.conn.getMetaData().getProcedureColumns(null, - null, "testBug3540%", "%"); + this.rs = this.conn.getMetaData().getProcedureColumns(null, + null, "testBug3540%", "%"); - assertTrue(this.rs.next()); - assertTrue("testBug3540".equals(this.rs.getString(3))); - assertTrue("x".equals(this.rs.getString(4))); + assertTrue(this.rs.next()); + assertTrue("testBug3540".equals(this.rs.getString(3))); + assertTrue("x".equals(this.rs.getString(4))); - assertTrue(this.rs.next()); - assertTrue("testBug3540".equals(this.rs.getString(3))); - assertTrue("y".equals(this.rs.getString(4))); + assertTrue(this.rs.next()); + assertTrue("testBug3540".equals(this.rs.getString(3))); + assertTrue("y".equals(this.rs.getString(4))); - assertTrue(!this.rs.next()); - } finally { - this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3540"); - } + assertTrue(!this.rs.next()); + } finally { + this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3540"); } } @@ -132,46 +140,48 @@ * if the test fails. */ public void testBug7026() throws Exception { - if (versionMeetsMinimum(5, 0)) { - try { - this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug7026"); - this.stmt - .executeUpdate("CREATE PROCEDURE testBug7026(x int, out y int)\n" - + "BEGIN\n" + "SELECT 1;" + "end\n"); + if (!serverSupportsStoredProcedures()) { + return; + } - // - // Should be found this time. - // - this.rs = this.conn.getMetaData().getProcedures( - this.conn.getCatalog(), null, "testBug7026"); + try { + this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug7026"); + this.stmt + .executeUpdate("CREATE PROCEDURE testBug7026(x int, out y int)\n" + + "BEGIN\n" + "SELECT 1;" + "end\n"); - assertTrue(this.rs.next()); - assertTrue("testBug7026".equals(this.rs.getString(3))); + // + // Should be found this time. + // + this.rs = this.conn.getMetaData().getProcedures( + this.conn.getCatalog(), null, "testBug7026"); - assertTrue(!this.rs.next()); + assertTrue(this.rs.next()); + assertTrue("testBug7026".equals(this.rs.getString(3))); - // - // This time, shouldn't be found, because not associated with - // this (bogus) catalog - // - this.rs = this.conn.getMetaData().getProcedures("abfgerfg", - null, "testBug7026"); - assertTrue(!this.rs.next()); + assertTrue(!this.rs.next()); - // - // Should be found this time as well, as we haven't - // specified a catalog. - // - this.rs = this.conn.getMetaData().getProcedures(null, null, - "testBug7026"); + // + // This time, shouldn't be found, because not associated with + // this (bogus) catalog + // + this.rs = this.conn.getMetaData().getProcedures("abfgerfg", + null, "testBug7026"); + assertTrue(!this.rs.next()); - assertTrue(this.rs.next()); - assertTrue("testBug7026".equals(this.rs.getString(3))); + // + // Should be found this time as well, as we haven't + // specified a catalog. + // + this.rs = this.conn.getMetaData().getProcedures(null, null, + "testBug7026"); - assertTrue(!this.rs.next()); - } finally { - this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug7026"); - } + assertTrue(this.rs.next()); + assertTrue("testBug7026".equals(this.rs.getString(3))); + + assertTrue(!this.rs.next()); + } finally { + this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug7026"); } } @@ -184,151 +194,154 @@ * if the test fails */ public void testBug9319() throws Exception { + if (!serverSupportsStoredProcedures()) { + return; + } + boolean doASelect = true; // 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(); - db1Connection = getAdminConnection(); + if (isAdminConnectionConfigured()) { + Connection db2Connection = null; + Connection db1Connection = null; - db2Connection.createStatement().executeUpdate( - "CREATE DATABASE IF NOT EXISTS db_9319_2"); - db2Connection.setCatalog("db_9319_2"); + try { + db2Connection = getAdminConnection(); + db1Connection = getAdminConnection(); - db2Connection.createStatement().executeUpdate( - "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI"); + db2Connection.createStatement().executeUpdate( + "CREATE DATABASE IF NOT EXISTS db_9319_2"); + db2Connection.setCatalog("db_9319_2"); - 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" + db2Connection.createStatement().executeUpdate( + "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI"); - + (doASelect ? "\nselect 2;" - : "\nSELECT 2 INTO p_administrador;") - + "\nEND"); + 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" - db1Connection.createStatement().executeUpdate( - "CREATE DATABASE IF NOT EXISTS db_9319_1"); - db1Connection.setCatalog("db_9319_1"); + + (doASelect ? "\nselect 2;" + : "\nSELECT 2 INTO p_administrador;") + + "\nEND"); - db1Connection.createStatement().executeUpdate( - "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI"); - db1Connection - .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))" - + "\nBEGIN" - + (doASelect ? "\nselect 1;" - : "\nSELECT 1 INTO p_administrador;") - + "\nEND"); + db1Connection.createStatement().executeUpdate( + "CREATE DATABASE IF NOT EXISTS db_9319_1"); + db1Connection.setCatalog("db_9319_1"); - 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); + db1Connection.createStatement().executeUpdate( + "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI"); + db1Connection + .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))" + + "\nBEGIN" + + (doASelect ? "\nselect 1;" + : "\nSELECT 1 INTO p_administrador;") + + "\nEND"); - cstmt.registerOutParameter(6, java.sql.Types.VARCHAR); + 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.execute(); + cstmt.registerOutParameter(6, java.sql.Types.VARCHAR); - if (doASelect) { - this.rs = cstmt.getResultSet(); - assertTrue(this.rs.next()); - assertEquals(2, this.rs.getInt(1)); - } else { - assertEquals(2, cstmt.getInt(5)); - } + cstmt.execute(); - cstmt = db1Connection - .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); + if (doASelect) { + this.rs = cstmt.getResultSet(); + assertTrue(this.rs.next()); + assertEquals(2, this.rs.getInt(1)); + } else { + assertEquals(2, cstmt.getInt(5)); + } - 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 = db1Connection + .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 = db1Connection - .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.execute(); + cstmt = db1Connection + .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); - if (doASelect) { - this.rs = cstmt.getResultSet(); - assertTrue(this.rs.next()); - assertEquals(1, this.rs.getInt(1)); - } else { - assertEquals(1, cstmt.getInt(5)); - } + cstmt.execute(); - String quoteChar = db2Connection.getMetaData() - .getIdentifierQuoteString(); + if (doASelect) { + this.rs = cstmt.getResultSet(); + assertTrue(this.rs.next()); + assertEquals(1, this.rs.getInt(1)); + } else { + assertEquals(1, cstmt.getInt(5)); + } - 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); + String quoteChar = db2Connection.getMetaData() + .getIdentifierQuoteString(); - cstmt.execute(); + 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); - 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"); - } + cstmt.execute(); - if (db1Connection != null) { - db1Connection.createStatement().executeUpdate( - "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI"); - db1Connection.createStatement().executeUpdate( - "DROP DATABASE IF EXISTS db_9319_1"); - } + 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"); + } } } } @@ -432,24 +445,26 @@ * if the test fails. */ public void testBug9682() throws Exception { - if (versionMeetsMinimum(5, 0)) { - CallableStatement cStmt = null; + if (!serverSupportsStoredProcedures()) { + return; + } - 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(); - } + CallableStatement cStmt = null; - this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug9682"); + 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"); } } @@ -462,114 +477,116 @@ * if the test fails. */ public void testBug10310() throws Exception { - if (versionMeetsMinimum(5, 0)) { - CallableStatement cStmt = null; + if (!serverSupportsStoredProcedures()) { + return; + } - 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()); + CallableStatement cStmt = null; - assertEquals(-1, cStmt.executeUpdate()); - assertEquals(2f, cStmt.getInt(1), .001); - assertEquals("java.lang.Integer", cStmt.getObject(1).getClass() - .getName()); + 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()) { - 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 + if (!isRunningOnJdk131()) { + assertEquals(4, cStmt.getParameterMetaData().getParameterCount()); + assertEquals(Types.INTEGER, cStmt.getParameterMetaData().getParameterType(1)); + } - java.sql.DatabaseMetaData dbmd = this.conn.getMetaData(); + assertFalse(cStmt.execute()); + assertEquals(2f, cStmt.getInt(1), .001); + assertEquals("java.lang.Integer", cStmt.getObject(1).getClass() + .getName()); - 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(2f, cStmt.getInt(1), .001); + assertEquals("java.lang.Integer", cStmt.getObject(1).getClass() + .getName()); - assertEquals(-1, cStmt.executeUpdate()); - assertEquals(0f, cStmt.getInt(1), .001); - assertEquals(true, cStmt.wasNull()); - assertEquals(null, cStmt.getObject(1)); - assertEquals(true, cStmt.wasNull()); + if (!isRunningOnJdk131()) { + cStmt.setFloat("a", 4); + cStmt.setInt("b", 1); + cStmt.setInt("c", 1); - - // 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(); - } + // Check metadata while we're at it - this.stmt.executeUpdate("DROP FUNCTION IF EXISTS testBug10310"); + 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 @@ -579,15 +596,15 @@ * if the test fails. */ public void testBug12417() throws Exception { - if (versionMeetsMinimum(5, 0) && isServerRunningOnWindows()) { + if (serverSupportsStoredProcedures() && isServerRunningOnWindows()) { Connection ucCatalogConn = null; try { this.stmt - .executeUpdate("DROP PROCEDURE IF EXISTS testBug12417"); + .executeUpdate("DROP PROCEDURE IF EXISTS testBug12417"); this.stmt.executeUpdate("CREATE PROCEDURE testBug12417()\n" + "BEGIN\n" + "SELECT 1;" + "end\n"); - ucCatalogConn = getConnectionWithProps((String)null); + ucCatalogConn = getConnectionWithProps(null); ucCatalogConn.setCatalog(this.conn.getCatalog().toUpperCase()); ucCatalogConn.prepareCall("{call testBug12417()}"); } finally { @@ -604,7 +621,7 @@ if (false /* needs to be fixed on server */) { if (versionMeetsMinimum(5, 0)) { this.stmt - .executeUpdate("DROP PROCEDURE IF EXISTS p_testBug15121"); + .executeUpdate("DROP PROCEDURE IF EXISTS p_testBug15121"); this.stmt.executeUpdate("CREATE PROCEDURE p_testBug15121()\n" + "BEGIN\n" + "SELECT * from idonotexist;\n" + "END"); @@ -619,7 +636,7 @@ StringBuffer queryBuf = new StringBuffer("{call "); String quotedId = this.conn.getMetaData() - .getIdentifierQuoteString(); + .getIdentifierQuoteString(); queryBuf.append(quotedId); queryBuf.append(this.conn.getCatalog()); queryBuf.append(quotedId); @@ -643,35 +660,36 @@ */ public void testBug15464() throws Exception { - if (versionMeetsMinimum(5, 0)) { - CallableStatement storedProc = null; + 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"); + 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 = this.conn + .prepareCall("{call testInOutParam(?, ?)}"); - storedProc.setString(1, "abcd"); - storedProc.setInt(2, 4); - storedProc.registerOutParameter(2, Types.INTEGER); + storedProc.setString(1, "abcd"); + storedProc.setInt(2, 4); + storedProc.registerOutParameter(2, Types.INTEGER); - storedProc.execute(); + storedProc.execute(); - assertEquals(5, storedProc.getInt(2)); - } finally { - this.stmt - .executeUpdate("DROP PROCEDURE IF EXISTS testInOutParam"); - } + assertEquals(5, storedProc.getInt(2)); + } finally { + this.stmt + .executeUpdate("DROP PROCEDURE IF EXISTS testInOutParam"); } } @@ -685,24 +703,27 @@ * if the test fails */ public void testBug17898() throws Exception { - if (versionMeetsMinimum(5, 0)) { - 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"); + if (!serverSupportsStoredProcedures()) { + return; + } - CallableStatement cstmt = this.conn - .prepareCall("{CALL testBug17898('foo', ?)}"); - cstmt.registerOutParameter(1, Types.INTEGER); + 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)); + } - if (!isRunningOnJdk131()) { - cstmt.clearParameters(); - cstmt.registerOutParameter("param2", Types.INTEGER); - cstmt.execute(); - assertEquals(1, cstmt.getInt(1)); - } - } } /** @@ -712,24 +733,27 @@ * @throws Exception if the test fails. */ public void testBug21462() throws Exception { - if (versionMeetsMinimum(5, 0)) { - 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"); + 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. @@ -737,30 +761,33 @@ * @throws Exception if the test fails */ public void testBug22024() throws Exception { - if (versionMeetsMinimum(5, 0)) { - 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"); + 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 @@ -769,163 +796,172 @@ * @throws Exception if the test fails */ public void testBug22297() throws Exception { - if (versionMeetsMinimum(5, 0)) { - 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"); + if (!serverSupportsStoredProcedures()) { + return; + } - 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')," + 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)); - } - - assertEquals(this.pstmt.getClass().getName(), - com.mysql.jdbc.PreparedStatement.class.getName()); - } finally { - closeMemberJDBCResources(); + 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)); } + + assertEquals(this.pstmt.getClass().getName(), + com.mysql.jdbc.PreparedStatement.class.getName()); + + } finally { + closeMemberJDBCResources(); } + } - + public void testHugeNumberOfParameters() throws Exception { - if (versionMeetsMinimum(5, 0)) { - this.stmt - .executeUpdate("DROP PROCEDURE IF EXISTS testHugeNumberOfParameters"); + if (!serverSupportsStoredProcedures()) { + return; + } - StringBuffer procDef = new StringBuffer( - "CREATE PROCEDURE testHugeNumberOfParameters("); + this.stmt + .executeUpdate("DROP PROCEDURE IF EXISTS testHugeNumberOfParameters"); - for (int i = 0; i < 274; i++) { - if (i != 0) { - procDef.append(","); - } + StringBuffer procDef = new StringBuffer( + "CREATE PROCEDURE testHugeNumberOfParameters("); - procDef.append(" OUT param_" + i + " VARCHAR(32)"); + for (int i = 0; i < 274; i++) { + if (i != 0) { + procDef.append(","); } - procDef.append(")\nBEGIN\nSELECT 1;\nEND"); - this.stmt.executeUpdate(procDef.toString()); + procDef.append(" OUT param_" + i + " VARCHAR(32)"); + } - CallableStatement cStmt = null; + procDef.append(")\nBEGIN\nSELECT 1;\nEND"); + this.stmt.executeUpdate(procDef.toString()); - try { - cStmt = this.conn - .prepareCall("{call testHugeNumberOfParameters(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," - + + CallableStatement cStmt = null; - "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," - + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," - + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," - + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," - + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," - + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}"); - cStmt.registerOutParameter(274, Types.VARCHAR); + try { + cStmt = this.conn + .prepareCall("{call testHugeNumberOfParameters(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + + - cStmt.execute(); - } finally { - if (cStmt != null) { - cStmt.close(); - } + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}"); + cStmt.registerOutParameter(274, Types.VARCHAR); + + cStmt.execute(); + } finally { + if (cStmt != null) { + cStmt.close(); } } } public void testPrepareOfMultiRs() throws Exception { - if (versionMeetsMinimum(5, 0)) { - this.stmt.executeUpdate("Drop procedure if exists p"); - this.stmt - .executeUpdate("create procedure p () begin select 1; select 2; end;"); - PreparedStatement ps = null; + if (!serverSupportsStoredProcedures()) { + return; + } - 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; - } + this.stmt.executeUpdate("Drop procedure if exists p"); + this.stmt + .executeUpdate("create procedure p () begin select 1; select 2; end;"); + PreparedStatement ps = null; - if (ps != null) { - ps.close(); - } + 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 (!versionMeetsMinimum(5, 0)) { + 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(); @@ -935,65 +971,131 @@ 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 + } + + 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 (!versionMeetsMinimum(5, 0)) { + 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 (!versionMeetsMinimum(5, 0)) { + 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"); + + "\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"); + + "/* after type 1 */ OUT result2 DECIMAL(/*size1*/10,/*size2*/2) /* p2 */)" + + "BEGIN SELECT action, result; END"); try { this.conn.prepareCall( - "{call testBug26959(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}") - .close(); + "{call testBug26959(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}") + .close(); this.rs = this.conn.getMetaData().getProcedureColumns( this.conn.getCatalog(), null, "testBug26959", "%"); @@ -1063,19 +1165,19 @@ closeMemberJDBCResources(); } } - + /** * Tests fix for BUG#27400 - CALL [comment] some_proc() doesn't work */ public void testBug27400() throws Exception { - if (!versionMeetsMinimum(5, 0)) { + if (!serverSupportsStoredProcedures()) { return; // SPs not supported } - + 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
| Thread | ||
|---|---|---|
| • Connector/J commit: r6370 - branches/branch_5_0/connector-j branches/branch_5_0/connector-j/src/com/mysql/jdbc branches/branch_5_0/connector-j/src/tes... | mmatthews | 26 Mar |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 1.235 sec. using MySQL 5.1.14-beta-log