java.sql.SQLException: Parameter number X is not an OUT parameter
- by Frederik
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!
