MySQL Bugs: #26071: getParameterMetaData error when return resultSet is specified

Bug #26071	getParameterMetaData error when return resultSet is specified
Submitted:	5 Feb 2007 6:39	Modified:	6 Mar 2007 17:39
Reporter:	Sue-Yen Tsai	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.04	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
When the CallableStatement specifies it has a return resultSet, the getParameterMetaData method invocation ends up with error. If trying to use the same stored procedure without getting the resultSet, there is no error occurred on the getParameterMetaData function call

How to repeat:
1. Create a stored procedure with two parameter

DELIMITER $$

DROP PROCEDURE IF EXISTS `playground`.`countMonkey` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `countTest`(studentName varchar(25), out cnt int)
BEGIN
  SELECT count(*) INTO cnt FROM students WHERE name=studentName;
  SELECT * FROM students WHERE name=studentName;
END $$

DELIMITER ;

2. In java code create the statatment with the following
CallableStatement cstmt=con.prepareCall("{ ? = call countTest(?, ?)}");

3. use the getParameterMetaData()

ParameterMetaData pMeta=cstmt.getParameterMetaData();

The error look like this

callabe stmt {? = call countMonkey (?, ?)}
java.lang.IndexOutOfBoundsException: Index: 2, Size: 2
        at java.util.ArrayList.RangeCheck(ArrayList.java:547)
        at java.util.ArrayList.get(ArrayList.java:322)
        at com.mysql.jdbc.CallableStatement$CallableStatementParamInfo.<init>(CallableStatement.java:149)
        at com.mysql.jdbc.CallableStatement$CallableStatementParamInfoJDBC3.<init>(CallableStatement.java:347)
        at com.mysql.jdbc.CallableStatement.getParameterMetaData(CallableStatement.java:1443)
        at test.executeCallable(sqlTests.java:182)

Hi Sue-Yen Tsai and thanks for your report. 

I would like to see JRE, OS and MySQL server details as well as complete test case attached to this report before proceeding.

Hi.

This may be connected to Bug#17898, "No straightforward way to deal with output parameters". Waiting on your test case.

You've used the syntax for calling a stored _function_ ({?=call(?...)}.

Therefore the bug isn't that parameter metadata has an error, you _should_ be getting an error message that there isn't a function that has the required parameters or name.

The leading ?= doesn't mean that the procedure being called has a result set, it means it has a return _value_ (i.e. a function).

You don't have to declare anything with MySQL for callable statements that return result sets, you just retrieve them.

Thanks for reply. I am using JDK 1.6 and the MySQL is 5.0.27-community-nt

Thanks Mark. I will try the solution you posted

Thanks for the help. Sorry for the confusion. It worked as Mark posted. I removed the ?= before the CALL and retrieve the result set without problem

I'm closing this report as there seems to be no actual problem.