java.sql.SQLException: Parameter number X is not an OUT parameter

Question

Hi guys,

I'm struggling with getting the result OUT variable from a MySQL stored procedure. I get the following error:

java.sql.SQLException: Parameter number 3 is not an OUT parameter

The stored procedure looks like this:

CREATE DEFINER=`cv_admin`@`localhost` PROCEDURE `CheckGameEligibility`(
   IN gID INT(10),
   IN uID INT(10),

   OUT result TINYINT(1)
)
BEGIN
    # Do lots of stuff, then eventually:
    SET result = 1;
END 

My java function takes an array of strings* and creates the CallableStatement object dynamically:

public static int callAndReturnResult( String sql , String[] values )
{
    int out = 0 ;
    try
    {
        // construct the SQL. Creates: CheckGameEligibility(?, ?, ?)
        sql += "(" ;

        for( int i = 0 ; i < values.length ; i++ )
        {
            sql += "?, " ;
        }
        sql += "?)" ;

        System.out.println( "callAndReturnResult("+sql+"): constructed SQL: " + sql );

        // Then the statement
        CallableStatement cstmt = DB.prepareCall( sql );
        for( int i = 0 ; i < values.length ; i++ )
        {
            System.out.println( "   " + (i+1) + ": " + values[ i ] ) ;
            cstmt.setString(i+1, values[ i ] );
        }

        System.out.println( "   " + (values.length+1) + ": ? (OUT)" ) ;
        cstmt.registerOutParameter( values.length + 1 , Types.TINYINT );
        cstmt.execute();

        out = cstmt.getInt( values.length );
        cstmt.close();
    }
    catch( Exception e )
    {
        System.out.println( "*** db trouble: callAndReturnResult(" + sql + " failed: " + e );
        e.printStackTrace() ;
    }
    return out ;
}

*) I suppose I should be using an int array instead of a string array, but it doesn't seem to be what the error message was about.

Anyway, here's the output it generates:

callAndReturnResult(CheckGameEligibility(?, ?, ?)): constructed SQL: CheckGameEligibility(?, ?, ?)
1: 57
2: 29
3: ? (OUT)
*** db trouble: callAndReturnResult(CheckGameEligibility(?, ?, ?) failed: java.sql.SQLException: Parameter number 3 is not an OUT parameter
    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.checkIsOutputParam(CallableStatement.java:692)
    at com.mysql.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1847)
    at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallabl>eStatement.java:92)
    at Tools.callAndReturnResult(Tools.java:156)

Any ideas what might be the problem? :)

Thanks in advance!

Answer 1

Here's tutorial on how to call stored procedures from jdbc:
http://java.sun.com/docs/books/tutorial/jdbc/basics/sql.html
The sql format is different from yours.

Answer 2

I'm not sure why Java or the MySQL drivers for Java don't like this syntax, but have you considered creating the procedure as a FUNCTION that RETURNS TINYINT instead?

Answer 3

I just got this exception as well, and I found out it was simply a dumb little syntax error I made when constructing my CALL string. Double-check that yours is the correct syntax.