CODEWITHSUNDEEP
javasql-serverjdbc
Kewin Björk Nielsen
Kewin Björk Nielsen

Reputation: 679

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

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;

    }
}

Upvotes: 5

Views: 9609

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

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

Upvotes: 5

Related Questions