What is the meaning of sqlType in registerOutParameter of jdbc?

Question

I'm using jdbc CallableStatement to execute procedures in MySQL database. While writing unit tests I have encountered strange behaviour regarding registerOutParameter method.

Used SQL procedures

PROCEDURE TEST_DOUBLE( OUT val DOUBLE ) BEGIN SET val = 2.5; END
PROCEDURE TEST_TEXT( OUT val TEXT ) BEGIN SET val = 'test'; END

Tests

statement = connection.prepareCall( "CALL TEST_DOUBLE(?)" );
statement.registerOutParameter( "val" , java.sql.Types.INTEGER );
statement.execute();
statement.getInt( "val" ); //Integer 2
statement.getDouble( "val" ); //Double 2.5
statement.getString( "val" ); //String "2.5"

statement = connection.prepareCall( "CALL TEST_TEXT(?)" );
statement.registerOutParameter( "val" , java.sql.Types.INTEGER );
statement.execute();
statement.getInt( "val" ); //Invalid value for getInt
statement.getDouble( "val" ); //Invalid value for getDouble
statement.getString( "val" ); //String "test"

statement = connection.prepareCall( "CALL TEST_TEXT(?)" );
statement.registerOutParameter( "val" , 0 );
statement.execute();
statement.getInt( "val" ); //Invalid value for getInt
statement.getDouble( "val" ); //Invalid value for getDouble
statement.getString( "val" ); //String "test"

and so on...

My point is - values java.sql.Types.* passed to registerOutParameter seem to do nothing... Regardless of what sql type I pass, everything depends on calling proper getter method and the real value returned by the database.

Can I just everywhere pass 0 and it will work properly anyway? Why is this parameter passed then? Or maybe is this database related, and for other databases it is meaningfull?

Answer 1

I would guess that this parameter is database-related. Oracle certainly pays attention to it:

statement = connection.prepareCall("CALL_TEST_NUMBER(?)");
statement.registerOutParameter("val", java.sql.Types.INTEGER);
statement.execute();
statement.getInt("val");       // 2
statement.getDouble("val");    // 2.5
statement.getString("val");    // "2.5"

statement = connection.prepareCall("CALL TEST_TEXT(?)");
statement.registerOutParameter("val", java.sql.Types.INTEGER);
statement.execute();   //  java.sql.SQLSyntaxErrorException: ORA-01722: invalid number