The executeQuery method must return a result set issue

Question

i have an issue where i trying to use a sp but i be getting a

The executeQuery method must return a result

and if i use cs.execute(); with ResultSet rs = cs.getResultSet(); instead i get a nullpointer on if(rs.next())

protected String doInBackground(String...params) {
    if (userid.trim().equals("") || password.trim().equals("")) z = getString(R.string.wrong_user);
    else {
        try {
            Connection con = connectionClass.CONN();
            if (con == null) {
                z = getString(R.string.connection_error);
            } else {
               String query = "{?=call [system].[usp_validateUserLogin](?,?,?,?,?)}";
                    CallableStatement cs = con.prepareCall(query);
                    cs.registerOutParameter(1,Types.INTEGER);
                    cs.setString(2, userid);
                    cs.setString(3, password);
                    cs.setInt(4, 72);
                    cs.setNull(5, Types.BIT);
                    cs.registerOutParameter(6, Types.VARCHAR);
                    cs.execute();
                    boolean firstResultIsResultSet = cs.execute();
                    if (firstResultIsResultSet) {
                        ResultSet rs = cs.getResultSet();
                        // process result set
                    }
                }
            }
                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);
        }
    }
    return z;
}

SP:

ALTER PROCEDURE [system].[usp_validateUserLogin]
    @p_Login                NVARCHAR ( 50 ),
    @p_Password             NVARCHAR ( 32 ),
    @p_CompanyID            INT,
    @p_OutDetails           BIT = 1,
    @p_AuthenticationTicket VARCHAR(200) OUTPUT
AS
  BEGIN
      SET NOCOUNT ON;

      DECLARE @errNo    INT,
              @recCount INT,
              @res      INT

      SELECT u.*
      INTO   #TMPLOGIN
      FROM   SYSTEM.[user] AS u WITH ( NOLOCK )
      WHERE  ( u.login = @p_Login )
             AND ( u.company_id = @p_CompanyID )
             AND ( PWDCOMPARE (@p_Password, u.passwd) = 1 )
             AND ( u.status = 0 ) --Active
      SELECT @errNo = @@ERROR,
             @recCount = @@ROWCOUNT

      IF ( @errNo <> 0 )
        BEGIN
            RETURN 1010
        END

      IF ( @recCount = 1 )
        BEGIN
            DECLARE @userID INT

            SELECT @userID = id
            FROM   #TMPLOGIN

            EXEC @res = SYSTEM.USP_RENEWAUTHENTICATIONTICKET
              @p_DoerTicket = '',
              @p_AuthenticationTicket = @p_AuthenticationTicket OUTPUT,
              @p_UserID = @userID,
              @p_CompanyID = @p_CompanyID

            IF ( @res <> 0 )
              RETURN @res
        END

      --SET @p_AuthenticationTicket = 'TESTAUTHENTICATIONTICKET0123456789'
      IF ( @p_OutDetails = 1 )
        BEGIN
            SELECT *
            FROM   #TMPLOGIN
        END

      RETURN 0
  END  

Answer 1

Warning: this answer was written without access to a SQL Server instance, so it might not be correct, I hope it will help you. I'll try to update it later when I do have access to an SQL Server system.

The problem seems to be is that you have three different ways of getting values from this stored procedure. You have a return value, an OUTPUT parameter and a result set. If the result set is produced depends on the logic of your stored procedure, so you must be prepared to handle its absence.

First of all, to get the return value of the stored procedure, you must use:

String query = "{?=call [system].[usp_UserLogin(?,?,?,?,?)}";

Where the ?= is the return value.

This also has an effect on the the index of the various parameters. It may also be necessary to explicitly register the first (return) parameter as an out parameter.

Then you need to execute the stored procedure and be prepared to handle the absence of a result set:

boolean firstResultIsResultSet = cs.execute();
if (firstResultIsResultSet) {
    ResultSet rs = cs.getResultSet();
    // process result set
}

Note that if you would have multiple result sets and also update counts, this would get even more complicated.

You should be able to get the return value using cs.getInt(1), although I'm not 100% sure if you can get it before checking and processing the result set. On this the Javadoc for CallableStatement says:

For maximum portability, a call's ResultSet objects and update counts should be processed prior to getting the values of output parameters.