get both output parameter and ResultSet values from SQL Server stored procedure

Question

I am trying to get the output parameter for passing into another SP so I created a test too see if I could get the string from it but an exception gets thrown:

java.sql.SQLException: Invalid state, the ResultSet object is closed

Without the cs.getMoreResults(); another exception is thrown:

java.sql.SQLException: Output parameters have not yet been processed. Call getMoreResults().

If I do remove my if (rs.next()) { then it works. How do I get the output parameter and still use my if rs.next?

   protected String doInBackground(String... params) {
        if (userid.trim().equals("Developer")|| password.trim().equals("Dev!n_234"))
            isSuccess2=true;
        z = getString(R.string.login_succes);
        if(userid.trim().equals("")|| password.trim().equals(""))
            z = getString(R.string.indsæt_rigtigt_bruger);
        else {
            try {
                Connection con = connectionClass.CONN();
                if (con == null) {
                    z = getString(R.string.Forbindelses_fejl) + "L1)";

                } else {
                    String ID;
                    ID = setingPreferences.getString("companyid", "");
                    CallableStatement cs = null;
                    String query = "{ call [system].[usp_validateUserLogin](?,?,?,?,?)}  ";
                    cs = con.prepareCall(query);
                    cs.setString(1, userid);
                    cs.setString(2, password);
                    cs.setString(3, ID);
                    cs.setBoolean(4, true);
                    cs.registerOutParameter(5, Types.VARCHAR);
                    ResultSet rs = cs.executeQuery();

                    cs.getMoreResults();
                    System.out.println("Test : " + cs.getString(5));

                    if (rs.next()) {
                        z = getString(R.string.login_succes);
                        isSuccess = true;

                    } else {
                        z = getString(R.string.Invalid_Credentials);
                        isSuccess = false;
                    }

                }
            }
            catch (Exception ex)
            {
                isSuccess = false;
                z = getString(R.string.Exceptions)+"L2)";
                Log.e("MYAPP", "exception", ex);
            }
        }
        return z;

    }
}

Answer 1

You need to process the ResultSet value(s) first, and then retrieve the output parameter value. That's because SQL Server sends the output parameter values after sending the result sets (ref: here).

So, this won't work:

ResultSet rs = cs.executeQuery();
System.out.println(cs.getString(5));  // clobbers the ResultSet
rs.next();  // error

but this should be okay:

ResultSet rs = cs.executeQuery();
if (rs.next()) {
    // do stuff
}
System.out.println(cs.getString(5));  // retrieve the output parameter value