MySQL Bugs: #10310: CallableStatement doesn't support calling functions

Bug #10310	CallableStatement doesn't support calling functions
Submitted:	2 May 2005 11:45	Modified:	6 May 2005 21:09
Reporter:	Guy Harrison	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.1.7	OS:	Windows (Windows XP SP2)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
JDBC documentation suggests that the following code should work:

		CallableStatement PreparedFunc = MyConnect.prepareCall
			 ("{ ? = call isodd( ? ) }");
		PreparedFunc.registerOutParameter(1,Types.INTEGER);
			
		PreparedFunc.setInt(2,aNumber);     
		PreparedFunc.execute();
			
		if (PreparedFunc.getInt(1)==1)
				System.out.println(aNumber+" is odd");
		else
				System.out.println(aNumber+" is even");

In Connector/J I get:

Parameter number 1 is not an OUT parameter

Function definition was:

CREATE FUNCTION isodd(input_number int) 
       RETURNS int 
BEGIN 
        DECLARE v_isodd INT; 
 
        IF MOD(input_number,2)=0 THEN 
                SET v_isodd=FALSE; 
        ELSE 
                SET v_isodd=TRUE; 
        END IF; 
 
        RETURN(v_isodd); 
 
END ;

There is an easy workaround (PreparedStatement with SELECT). 

How to repeat:
See above.

This is fixed for 3.1.9. You should be able to checkout a nightly snapshot build after 00:00 GMT on May 7th from http://downloads.mysql.com/snapshots.php if you need to test before the official release.

Thanks for your bug report.

  -Mark

Hi,  I'm still seeing this bug appear for MySQL version 5.0.27 with Connector/J version 5.0.5 on Windows XP Pro.  Specifically the function I'm calling returns an INT UNSIGNED type and takes as it's one input an INT UNSIGNED type.  I'm able to work around using a prepared statement but it's annoying when the callable statement should be working. The error is a number format exception of which the partial stack trace is given below. On first pass it appears the (xx) size setting of the output parameter is causing the problem.  The value I'm passing to the function is 4. -Daniel

SEVERE: SessionID = 1193241935500. manipulateDecisionTree. Exception! For input string: "10)

 companyId = 4
        java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
        java.lang.Integer.parseInt(Integer.java:456)
        java.lang.Integer.<init>(Integer.java:620)
        com.mysql.jdbc.DatabaseMetaData$TypeDescriptor.<init>(DatabaseMetaData.java:259)
        com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1657)
        com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:3983)
        com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:702)
        com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:513)
        com.mysql.jdbc.Connection.parseCallableStatement(Connection.java:4520)
        com.mysql.jdbc.Connection.prepareCall(Connection.java:4594)
        com.mysql.jdbc.Connection.prepareCall(Connection.java:4568)
        inf.decisionTree.managers.DecisionTreeManager.dt_getNextVersion(DecisionTreeManager.java:459)

when will this be fixed ? cannot call any stored procedure from java 

so now we need two database calls ... like for 

create PROCEDURE  insert_upc ( IN pcode  VARCHAR(25))
begin
  DECLARE cnt int;                /* variables */
select count(*) into cnt from upc where code = pcode;
if cnt = 0 then
	
	insert into upc values (1,pcode);
	END IF;
	
end
//

Guys, it is fixed loong ago, please see regression test suite, CallableStatementRegressionTest.java for example.