执行"sp_msforeachdb";在Java应用程序中 [英] Execute "sp_msforeachdb" in a Java application
问题描述
Hi StackOverflow社区:)
我来找你分享我的一个问题...
我必须提取一个SQL Server实例的每个数据库中每个表的列表,我发现了这个查询:
EXEC sp_msforeachdb 'Use ?; SELECT DB_NAME() AS DB, * FROM sys.tables'
它可以在Microsoft SQL Server Management Studio上完美运行,但是当我尝试在Java程序中执行 (包括用于SQL Server的JDBC驱动程序)时,它说它不返回任何结果.
我的Java代码如下:
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
感谢任何会尝试帮助我的人, 今天愉快:)
我不确定SQL Server的JDBC驱动程序是否支持我的查询,因此我将尝试以另一种方式实现目标.
我要获取的是 SQL Server实例上每个数据库的所有表的列表,输出格式如下:
+-----------+--------+
| Databases | Tables |
+-----------+--------+
所以现在我要问有人可以帮助我通过Java的JDBC for SQL Server驱动程序使用SQL查询来获得该解决方案.
我也想感谢我从 Tim Lehner 和解决方案
如果语句不能返回或返回多个结果,则不应使用executeQuery
,而应使用 getResultSet()
来检索ResultSet
,否则 getMoreResults()
(或其兄弟接受int
参数). boolean
的返回值与execute()
的含义相同,因此false
并不意味着不再有结果!
如果getMoreResults()
返回false并且getUpdateCount()
返回-1
(也已在Javadoc中记录),则没有其他结果
从本质上讲,这意味着如果您想正确处理所有结果,则需要执行以下操作.请注意,我实际上没有在您的语句中尝试过它,也不确定SQL Server JDBC驱动程序是否正确实现了多个结果,因此它可能不起作用:
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();
}
注意:部分答案是基于我对 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 aResultSet
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. Be aware that I did not actually try it with your statement, nor am I sure if the SQL Server JDBC driver correctly implements multiple results, so it might not work:
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屋!