Squirrel SQL 编辑器不会从具有 3 个选择语句的存储过程返回多个结果 [英] Squirrel SQL Editor does not return multiple results from a stored procedure with 3 select statements

问题描述

我有一个在 Sybase 的 Squirrel SQL 编辑器中运行的存储过程.存储过程有3条select语句，但是执行时只返回第一个select语句的结果.

I have a stored procedure which is run in Squirrel SQL editor for Sybase. The stored procedure has 3 select statements, but when executed , it returns only Ithe result of first select statement.

我尝试过的:使用 Java SQL API 执行存储过程.我看到了同样的行为.此外，在 Toad for Sybase 中执行相同的存储过程并看到相同的行为.

What I have tried: Executed the stored procedure using Java SQL API. I see the same behavior. Also, executed the same stored procedure in Toad for Sybase and see the same behavior.

我们公司为旧版 C++ 应用程序使用了 3rd 方数据库执行工具.该库按预期返回所有 3 个结果集.由于某种原因，我们正在删除此库.

Our company uses an 3rd party database execution tool for legacy C++ apps. That library returns all the 3 result sets as expected. We are removing this library for some reason.

推荐答案

使用 CallableStatement#execute() 执行返回多个 ResultSet 的存储过程.

Use CallableStatement#execute() to execute the stored procedure which returns multiple ResultSets.

它返回一个布尔值.

If true : 如果当前结果是一个 ResultSet如果 false :当前结果是一个 UpdateCount

If true : If current result is a ResultSet If false : The current result is a UpdateCount

什么是更新计数?当您在 SQL 编辑器中执行存储过程时，您会在控制台中获得一些信息，例如4 行受影响".此消息称为 UpdateCount.

What is a UpdateCount? When you execute a stored procedure in SQL Editor, you will get some information in Console saying, for example, "4 rows affected". This message is called an UpdateCount.

数据库在对 API 的响应中返回这些消息以及结果集.

Database returns these messages as well as the resultsets in the response to API.

遍历返回的结果.

    boolean hasResults = stmt.execute();
    
    while(true) {
      if(hasResults) {
          //Has results is true if Current result is ResultSet
        //Add code here to parse your result values 
      } else {
          if(stmt.getUpdateCount() == -1) break; // you have reached the end of all data (updatecount + resultset)
    }
    hasResults = stmt.getMoreResults(); // GetMoreResults return true if the next result is ResultSet else it returns false if its updatecount.
}

如果您不希望 UpdateCounts 作为数据库结果的一部分返回，您可以在存储过程中包含语句 SET NOCOUNT ON

If you don't want the UpdateCounts to be returned as part of results from database, you include the statement SET NOCOUNT ON in your stored procedures

这篇关于Squirrel SQL 编辑器不会从具有 3 个选择语句的存储过程返回多个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！