stmt在java中的应用,执行“sp_msforeachdb”在Java应用程序中

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. 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()?