Common Errors with CallableStatement Parameters

JDBC Tutorials - Herong's Tutorial Examples

∟Common Errors with CallableStatement Parameters

This section provides some error conditions on CallableStatement place holder parameters.

While testing CallableStatement objects with place holder parameters, I noticed two common errors as shown in this program:

/* MySqlCallParameterError.java
 - Copyright (c) 2015, HerongYang.com, All Rights Reserved.
 */
import java.sql.*;
public class MySqlCallParameterError {
  public static void main(String [] args) {
    Connection con = null;
    try {
      com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds 
        = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();
      ds.setServerName("localhost");
      ds.setPortNumber(3306);
      ds.setDatabaseName("HerongDB");
      ds.setUser("Herong");
      ds.setPassword("TopSecret");
      con = ds.getConnection();

// Create CallableStatement
      CallableStatement cs = con.prepareCall(
        "CALL ReverseProcedure(?,?,?)");

// Provide values for IN parameters
// Error 2 - Forget to provide values for IN parameters
      String word = "Herong";
      cs.setString(1,word);

// Register OUT parameters
// Error 1 - Can not register OUT parameters
      cs.registerOutParameter(1, java.sql.Types.VARCHAR);
      cs.registerOutParameter(2, java.sql.Types.VARCHAR);
      cs.registerOutParameter(3, java.sql.Types.INTEGER);

// Execute the CALL statement and ignore result sets
      cs.executeUpdate();

// Retrieve values from OUT parameters
      String reversed = cs.getString(2);
      int length = cs.getInt(3);
      System.out.println("Input word: "+word);
      System.out.println("Output word: "+reversed);
      System.out.println("Word length: "+length);

// Close resource
      cs.close();

      con.close();
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
      e.printStackTrace();
    }
  }
}

Compile and run this program as is, you will get this error message at line 28, because registering an IN parameter as statement output parameter is not allowed:

C:\herong>\Progra~1\java\jdk1.8.0_45\bin\javac 
   -cp .;\local\lib\mysql-connector-java-5.1.36-bin.jar 
   MySqlCallParameterError.java

C:\herong>\Progra~1\java\jdk1.8.0_45\bin\java 
   -cp .;\local\lib\mysql-connector-java-5.1.36-bin.jar 
   MySqlCallParameterError

Exception: No value specified for parameter 1
java.sql.SQLException: No value specified for parameter 1
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)
   at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(...)
   ...
   at MySqlCallParameterError.main(MySqlCallParameterError.java:34)

If you commented out this line, "cs.registerOutParameter(1, java.sql.Types.VARCHAR);", compile and run this program again, you will get a different error when calling cr.executeUpdate() at line 35. This time, no value was provided for the first parameter: