执行“sp_msforeachdb"在 Java 应用程序中 [英] Execute "sp_msforeachdb" in a Java application

问题描述

你好 StackOverflow 社区 :)

我来找你分享我的一个问题......

我必须提取一个SQL Server 实例的每个数据库中每个表的列表，我发现了这个查询:

EXEC sp_msforeachdb '使用 ?;SELECT DB_NAME() AS DB, * FROM sys.tables'

它在 Microsoft SQL Server Management Studio 上运行良好，但是当我尝试在我的 Java 程序(包括 SQL Server 的 JDBC 驱动程序)中执行它时，它说它不返回任何结果.

我的Java代码如下:

this.statement = this.connect.createStatement();//创建语句this.resultats = this.statement.executeQuery("EXEC sp_msforeachdb 'Use ?; SELECT DB_NAME() AS DB, * FROM sys.tables'");//执行查询并将结果存储在 ResultSet 中this.sortie.ecrireResultats(this.statement.getResultSet());//将结果集写入文件

感谢任何愿意帮助我的人，祝你有美好的一天:)

编辑 1:

我不确定 SQL Server 的 JDBC 驱动程序是否支持我的查询，所以我会尝试以另一种方式实现我的目标.

我想要的是SQL Server 实例上每个数据库的所有表的列表，输出格式如下:

+-----------+--------+|数据库 |表 |+-----------+--------+

所以现在我问有人能帮我找到那个解决方案吗通过 Java 的 JDBC for SQL Server 驱动程序使用 SQL 查询.

我还要感谢我从 Tim Lehner 和 马克·罗特维尔.

解决方案

如果一个语句可以返回 no 或 multiple 结果，则不应使用 executeQuery，而应使用 execute() 相反，此方法返回一个 boolean 指示第一个结果的类型:

• true:结果是一个 ResultSet
• false : 结果是更新计数

如果结果为 true，则使用 getResultSet() 检索ResultSet，否则getUpdateCount() 进行检索更新计数.如果更新计数为 -1，则表示没有更多结果.请注意，当当前结果是 ResultSet 时，更新计数也将为 -1.如果没有更多结果或者结果是更新计数，getResultSet() 应该返回 null.

现在，如果您想检索更多结果，请调用 getMoreResults()(或者它的兄弟接受一个 int 参数).boolean 的返回值与execute() 的返回值含义相同，所以false 不代表没有结果！>

只有在 getMoreResults() 返回 false 并且 getUpdateCount() 返回 -1 时才会有更多的结果(也记录在Javadoc)

本质上这意味着如果您想正确处理所有结果，您需要执行以下操作:

boolean result = stmt.execute(...);同时(真)如果(结果){结果集 rs = stmt.getResultSet();//对结果集做一些事情...} 别的 {int updateCount = stmt.getUpdateCount();如果(更新计数 == -1){//没有更多结果休息;}//用更新计数做一些事情...}结果 = stmt.getMoreResults();}

注意:此答案的一部分基于我对 Java SQL:Statement.hasResultSet()? 的回答>

Hi StackOverflow community :)

I come to you to share one of my problems...

I have to extract a list of every table in each database of a SQL Server instance, I found this query :

EXEC sp_msforeachdb 'Use ?; SELECT DB_NAME() AS DB, * FROM sys.tables'

It works perfectly on Microsoft SQL Server Management Studio but when I try to execute it in my Java program (that includes JDBC drivers for SQL Server) it says that it doesn't return any result.

My Java code is the following :

this.statement = this.connect.createStatement(); // Create the statement
this.resultats = this.statement.executeQuery("EXEC sp_msforeachdb 'Use ?; SELECT DB_NAME() AS DB, * FROM sys.tables'"); // Execute the query and store results in a ResultSet

this.sortie.ecrireResultats(this.statement.getResultSet()); // Write the ResultSet to a file

Thanks to anybody who will try to help me, Have a nice day :)

EDIT 1 :

I'm not sure that the JDBC driver for SQL Server supports my query so I'll try to get to my goal in another way.

What I'm trying to get is a list of all the tables for each database on a SQL Server instance, the output format will be the following :

+-----------+--------+
| Databases | Tables |
+-----------+--------+

So now I'm asking can someone help me to get to that solution using SQL queries thru Java's JDBC for SQL Server driver.

I also wish to thanks the very quick answers I got from Tim Lehner and Mark Rotteveel.

解决方案

If a statement can return no or multiple results, you should not use executeQuery, but execute() instead, this method returns a boolean indicating the type of the first result:

• true: result is a ResultSet
• false : result is an update count

If the result is true, then you use getResultSet() to retrieve the ResultSet, otherwise getUpdateCount() to retrieve the update count. If the update count is -1 it means there are no more results. Note that the update count will also be -1 when the current result is a ResultSet. It is also good to know that getResultSet() should return null if there are no more results or if the result is an update count.

Now if you want to retrieve more results, you call getMoreResults() (or its brother accepting an int parameter). The return value of boolean has the same meaning as that of execute(), so false does not mean there are no more results!

There are only no more results if the getMoreResults() returns false and getUpdateCount() returns -1 (as also documented in the Javadoc)

Essentially this means that if you want to correctly process all results you need to do something like below:

boolean result = stmt.execute(...);
while(true)
    if (result) {
        ResultSet rs = stmt.getResultSet();
        // Do something with resultset ...
    } else {
        int updateCount = stmt.getUpdateCount();
        if (updateCount == -1) {
            // no more results
            break;
        }
        // Do something with update count ...
    }
    result = stmt.getMoreResults();
}

NOTE: Part of this answer is based on my answer to Java SQL: Statement.hasResultSet()?

这篇关于执行“sp_msforeachdb"在 Java 应用程序中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！