MySQL - java.sql.SQLException: ResultSet is from UPDATE. No Data

Question

public static void main(String args[])
    {

            SQLConnector sqlConnect = new SQLConnector();
            Connection conn = null;
            try
            {
            conn= sqlConnect.getConnection();
            CallableStatement cStmt = conn.prepareCall("{ call test(?,?,?)}");
            cStmt.setDouble(1, 100.0);
            cStmt.setInt(2, 1);
            cStmt.registerOutParameter(3, java.sql.Types.VARCHAR);
            ResultSet rs = cStmt.executeQuery();
            if (rs.next()) {
                System.out.println(rs.getString(3);
                }
            cStmt.execute();
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
            finally
            {
                sqlConnect.closeConnection(conn);
            }
        }

This snippet throws the error

java.sql.SQLException: ResultSet is from UPDATE. No Data.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)

But if I pass the same parameters from MySQL Workbench it gives proper output.

I am using MySQLServer 5.6.25 and MySQLConnector 5.1.6.

Please help me solve this problem. This looks like a bug in the way I call MySQL from Java

Answer 1

See section 4 of this MySQL documentation page: Using JDBC CallableStatements to Execute Stored Procedures.

You don't use executeQuery(). You have to use execute() and getResultSet().

Since you know the statement will return exactly one ResultSet, your code becomes:

cStmt.execute();
try (ResultSet rs = cStmt.getResultSet()) {
    if (rs.next())
        System.out.println(rs.getString(3));
}

Except of course you might be wrong, because the call doesn't return a result set, but instead returns a string in an output parameter, and that totally changes the code:

cStmt.execute();
System.out.println(cStmt.getString(3));

Answer 2

Remove the below line in your code to resolve the issue:

cStmt.execute();