Bug #73774 Can't execute a stored procedure if exists function with same name
Submitted: 29 Aug 2014 17:54 Modified: 11 Jan 2020 16:52
Reporter: Filipe Silva Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version: OS:Any
Assigned to: Filipe Silva CPU Architecture:Any

[29 Aug 2014 17:54] Filipe Silva 
Description:
If a stored procedure, with OUT or INOUT params, and a function with the same name exists, when registering the out parameters the following exception is thrown: «Exception in thread "main" java.sql.SQLException: Parameter number 1 is not an OUT parameter».

This only works in JDBC4+ and if the connection property 'getProceduresReturnsFunctions=false' is used.

How to repeat:
stmt.execute("DROP PROCEDURE IF EXISTS common_name");
stmt.execute("CREATE PROCEDURE common_name (INOUT param1 CHAR(20), IN param2 CHAR(20)) BEGIN  SELECT CONCAT(param1, param2) INTO param1;  END");

stmt.execute("DROP FUNCTION IF EXISTS common_name");
stmt.execute("CREATE FUNCTION common_name (param1 CHAR(20), param2 CHAR(20)) RETURNS CHAR(40) DETERMINISTIC RETURN CONCAT(param1, param2)");

CallableStatement cstmt = conn.prepareCall("{CALL common_name (?, ?)}");
cstmt.registerOutParameter(1, Types.CHAR);
cstmt.setString(1, "A");
cstmt.setString(2, "Z");
cstmt.execute();

Suggested fix:
Prevent fetching functions info when checking the parameters metadata.
[1 Nov 2023 12:09] Filipe Silva 
Bug#112447 is a duplicate.
[9 Jan 2024 17:33] Daniel So 
Posted by developer:
 
Added the same changelog entry to the Connector/J 8.3.0 changelog, as the fix has been ported to release 8.3.0.
[7 Jun 2024 12:41] MySQL Verification Team 
Bug #115249 marked as duplicate of this one.