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:Microsoft Windows (Windows XP SP2)
Assigned to: Mark Matthews

[2 May 2005 11:45] Guy Harrison
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.
[6 May 2005 21:09] Mark Matthews
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
[24 Oct 2007 18:03] Daniel Bramblett
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)
[11 Jan 2008 18:13] Tushar Kapila
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
//
[5 Nov 2008 11:00] Tonci Grgin
Guys, it is fixed loong ago, please see regression test suite, CallableStatementRegressionTest.java for example.