java 如何解决“语句没有返回结果集错误”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40674098/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to solve "The Statement did not return a result set error"?
提问by Farheen
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?
当语句不返回结果集时，我试图处理 SQLServerException。我还是 Java 新手，所以我无法找到解决它的方法。请任何人都可以建议我如何解决错误？我遇到困难的地方是当这个存储过程不返回任何结果集时，我想显示类似“未找到记录”的内容。我该如何解决？
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);
}
}
回答by Kevin G.
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.
您可以使用 CallableStatement 的 execute() 方法。execute 方法将返回一个布尔值，指示是否存在与您的请求的执行相对应的 ResultSet。
Link to the Javadoc: https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#execute()
链接到 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()
您仍然可以使用超类Statement的getResultSet方法获取ResultSet：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();
}
回答by user7266701
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.
开始您的程序后，您必须包含上述 sql 语句，然后问题将得到解决。
