Farheen Farheen - 14 days ago 6
Java Question

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

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

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();
}
Comments