MySQL Bugs: #56095: cannot execute a store procedure with output parameters

Bug #56095	cannot execute a store procedure with output parameters
Submitted:	18 Aug 2010 18:12	Modified:	25 Aug 2010 18:58
Reporter:	tod ZM	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.13	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Cannot execute a stored procedure from JDBC with OUTPUT param. Search bug database, claimed to be fixed in 5.1.13 but still happens. 

How to repeat:
Have a stored procedure proc_a(IN a INT, IN b INT, IN c INT, OUT d varchar(30));

when calling from JDBC, CallableStatement, and upon stmt.registerOutParameter(4, java.sql.Types.VARCHAR); it throws the following stacktrace:

java.sql.SQLException: Parameter index of 4 is out of range (1, 0)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
	at com.mysql.jdbc.CallableStatement$CallableStatementParamInfo.checkBounds(CallableStatement.java:274)
	at com.mysql.jdbc.CallableStatement.checkParameterIndexBounds(CallableStatement.java:710)
	at com.mysql.jdbc.CallableStatement.checkIsOutputParam(CallableStatement.java:672)
	at com.mysql.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1846)

Suggested fix:
Upon examining the code, numParameters in CallableStatementParamInfo is 0. Further on discovered that DatabaseMetaData.java line 4302 with query like this to check if the stored procedure is available, the following sql would return nothing.

clientPrepareStatement("SELECT name, type, comment FROM mysql.proc WHERE name like ? and db <=> ? ORDER BY name");

Shouldn't it be clientPrepareStatement("SELECT name, type, comment FROM mysql.proc WHERE name like ? and db <> ? ORDER BY name");

Is there a typo here?

Thank you for the report.

Please provide example of Java code you use to call procedure.

There are many examples in test suite...

I still see no test case attached but I do see code comments made private. Don't really understand why you do it...

BugsDB is crawling with examples on how to do this. Last that comes to my mind is Bug#43576 where you can check my test cases and patch for that problem.
Same test case is in our regression test suite (CallableStatementRegressionTest.java, testBug43576()).

Please check on those and if you still have problems get back to us with proper report including connection string, JVM settings, MySQL server settings et all. 

Till then, I see nothing to work on here.

closed.

I used mysql connector/j version 5.1.11 and did not have this problem. The code remaining exactly the same and none of the other project dependencies changing, the code threw an exception around this error as soon as I started using driver version 5.1.13. What seems more surprising is that this concerns a stored procedure in the same database and not even another database!

Details of run environment:

SuSe Linux Enterprise Server 11
Sun Java 1.6
MySQL 5.0.67-community-log
MySQL Connector/J 5.1.13

I hope this is researched further and fixed.

Thanks,
Ashwin Raj