How to solve "The Statement did not return a result set error"?

Question

I am trying to handle SQLServerException when a statement does not return a result set. I am still new to java and so I am unable to figure out a way to solve it. Please can anyone suggest how can i resolve the error? The place where I am having difficulty is when this stored procedure doesn't return any result set and I want to display something like "No record found". How can I solve it?

stmt = conn.prepareCall("{call p_GetAllowedPublicIPs(?,?,?)}");

---

 @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        //processRequest(request, response);
        PrintWriter out = response.getWriter();
        String IsLoginDisabled = null;
         String  BankID =null;
         String publicip=null;
        try {
          //processRequest(request, response);
          Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
          Connection conn = null;
          CallableStatement myStmt = null;
          int count =0;

          conn = DriverManager.getConnection("jdbc:sqlserver://MPESA\\SQL2012;user=realm;password=friend;database=ElmaTest");

          if(conn!=null)
          {
              out.println("Connection Succcesful");
          myStmt = conn.prepareCall("{call sp_GetPortalUser(?,?,?)}");

                myStmt.setString("Country", "Kenya");
                myStmt.setString("BankName", "CS");
                myStmt.setString("UserID", "Frank");

               ResultSet rs= myStmt.executeQuery();
              while(rs.next())       
              {
                  count++;
                  BankID = rs.getString("BankID");
                  String employeeid= rs.getString("EmployeeID");
                  String FirstName = rs.getString("FirstName");
                  String LastName= rs.getString("LastName");
                 String MiddleName = rs.getString("MiddleName");
                 String  Address = rs.getString("Address");
                  String MobileNumber= rs.getString("MobileNumber");
                  String Emailid = rs.getString("EmailID");
                  String TypeofID= rs.getString("TypeOfID");
                 String IDNumber = rs.getString("IDNumber");
                 String ipaddress = rs.getString("IPAddress");
                 IsLoginDisabled = rs.getString("isLoginDisabled");
                 String LoginFailureIPaddress = rs.getString("LoginFailureIPAddress");



                  System.out.println("count"+count);
                  System.out.println("BankID" +BankID);
                  System.out.println("EmployeeId"+employeeid);
                  System.out.println("FirstName"+FirstName);
                  System.out.println("MiddleName"+MiddleName);
                   System.out.println("LastName"+LastName);
                   System.out.println("Address"+Address);
                  System.out.println("MobileNumber"+MobileNumber);
                  System.out.println("EmailId"+Emailid);
                  System.out.println("TypeoFiD"+TypeofID);
                   System.out.println("Idnumber"+IDNumber);
                     System.out.println("ipaddress"+ipaddress);
                  System.out.println("isLoginDisabled"+IsLoginDisabled);
                   System.out.println("LoginFailureIPaddress"+LoginFailureIPaddress);







              }

            if(count>0)
              {
                  int logindisabled = Integer.valueOf(IsLoginDisabled);
                  CallableStatement stmt = null;
                  if (logindisabled!=1)
                  {
                      try {
                          stmt = conn.prepareCall("{call p_GetAllowedPublicIPs(?,?,?)}");
                      } catch (SQLException ex) {
                          Logger.getLogger(LoginController.class.getName()).log(Level.SEVERE, null, ex);
                      }
                   stmt.setString("Country", "Kenya");
                   stmt.setString("BankID", "99");
                   stmt.setString("PublicIP", "1");
                  ResultSet rp = stmt.executeQuery();
                  //  System.out.println(rp.next());
                  while(rp.next())
                  {
                      String ipaddress = rp.getString("IPAddress");
                      System.out.println("ipaddress"+ipaddress);
                  }



                  }
          }





        }
      } catch (ClassNotFoundException | SQLException ex) {
          Logger.getLogger(Search.class.getName()).log(Level.SEVERE, null, ex);
      }









    }

Answer 1

The fact that your program did not raise any exception is that having no results is not an exception.

You can use the execute() method of your CallableStatement. The execute method will return a boolean indicating if there was a ResultSet corresponding to the execution of your request.

Link to the Javadoc: https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#execute()

You still can get the ResultSet with the getResultSet method of the super class Statement: https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getResultSet()

In your case, you would do the following:

boolean gotResults = myStmt.execute();
ResultSet rs = null;
if(!gotResults){
   System.out.println("No results returned");
} else {
   rs = myStmt.getResultSet();
}

Answer 2

I faced the same problem and after some research I know that we have to include

CREATE PROCEDURE {proc_name} (parameters) 
AS
BEGIN

SET NOCOUNT ON 

...........

----your sqloperations

END

After begin your procedure you have to include the above sql statement, then the issue will be resolved.