Kewin Björk Nielsen Kewin Björk Nielsen - 18 days ago 7
Java Question

get output parameter issue

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

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