Programmazione:Java/JDBC/MSSQL stored procedure - Cronologia

Mazzotti il 10:48, 24 set 2009

2009-09-24T10:48:17Z

Mazzotti il 10:46, 24 set 2009

2009-09-24T10:46:08Z

Mazzotti: Creata pagina con 'A partire dalla versione 1.2 del driver JDBC Microsoft, la chiamata ad una stored procedure che ritorna in ResultSet, genera una eccezione del tipo "The statement did not return …'

2009-09-24T10:45:08Z

Creata pagina con 'A partire dalla versione 1.2 del driver JDBC Microsoft, la chiamata ad una stored procedure che ritorna in ResultSet, genera una eccezione del tipo "The statement did not return …'

Nuova pagina

A partire dalla versione 1.2 del driver JDBC Microsoft, la chiamata ad una stored procedure che ritorna in ResultSet, genera una eccezione del tipo "The statement did not return a result set"

<code java>
CallableStatement cs = conn.prepareCall("{CALL prSomeProcedure()}");

ResultSet rs = cs.executeQuery(); // Exception thrown here: "The statement did not return a result set".
</code>

La soluzione del problema sta nel rimpiazzare il codice sopra-indicato con questo:
<code java>
CallableStatement cs = conn.prepareCall("{CALL prSomeProcedure()}");

ResultSet rs = DBUtil.executeStatementAndRetrieveResultSet(cs); // Exception not thrown here :)
</code>

dove il blocco di codice che ottiene il ResultSet è il seguente:

<code java>
/**
* Executes a callable statement which has been prepared to call a stored procedure which returns a result set, and
* returns the first result set found.
* <p/>
* This utility method is designed to work around the complexity of retrieving result sets from stored procedures in
* Microsoft SQL Server databases using the Microsoft JDBC driver 1.2.xx and later, where calling
* <code>Statement.executeQuery()</code> with this driver throws an exception saying "The statement did not return a
* result set".
* <p/>
* This exception is due to this version (and later) of the driver returning additional update counts before
* returning a result set, thus requiring <code>getMoreResults()</code> to be called in client code one or more
* times before the result set can be accessed. This method calls <code>getMoreResults()</code> as necessary until
* the result set becomes accessible.
*
* @param cs A callable statement which has been prepared to call a stored procedure which returns a result set.
*
* @return The first (or only) result set returned by the statement.
* @throws IllegalArgumentException If the callable statement specified is null.
* @throws SQLException If any error occurs.
*/
protected ResultSet executeStatementAndRetrieveResultSet(CallableStatement cs) throws SQLException {
if (cs == null) {
throw new IllegalArgumentException("The callable statement specified was null.");
}
// Execute the statement...
boolean resultSetFound;
try {
resultSetFound = cs.execute();
}
catch (Exception e) {
SQLException sqle = new SQLException("Failed to execute the statement: " + e);
sqle.initCause(e);
throw sqle;
}
// Retrieve the first result set from the statement...
try {
while (!resultSetFound) {
resultSetFound = cs.getMoreResults();
// Check if there are no more results...
if (!resultSetFound && cs.getUpdateCount() == -1) {
// No more result sets are available. Stop searching...
break;
}
// Else continue looking for a result set...
}
if (resultSetFound) {
ResultSet rs = cs.getResultSet();
if (rs == null) {
throw new IllegalStateException("Unexpectedly failed to retrieve a result set from statement.");
}
return rs;
}
else {
throw new SQLException("Scanned all results returned by the statement but did not find a result set.");
}
}
catch (Exception e) {
SQLException sqle = new SQLException("Executed the statement but failed to retrieve a result set: " + e);
sqle.initCause(e);
throw sqle;
}
}
</code>

@@ Riga 27: / Riga 27: @@
    * <p/>
    * This exception is due to this version (and later) of the driver returning additional update counts before
-   * returning a result set, thus requiring <code>getMoreResults()</code> to be called in client code one or more
+   * returning a result set, thus requiring getMoreResults() to be called in client code one or more
-   * times before the result set can be accessed. This method calls <code>getMoreResults()</code> as necessary until
+   * times before the result set can be accessed. This method calls getMoreResults() as necessary until
    * the result set becomes accessible.
+   *

@@ Riga 23: / Riga 23: @@
    * This utility method is designed to work around the complexity of retrieving result sets from stored procedures in
    * Microsoft SQL Server databases using the Microsoft JDBC driver 1.2.xx and later, where calling
-   * <code>Statement.executeQuery()</code> with this driver throws an exception saying "The statement did not return a
+   * Statement.executeQuery() with this driver throws an exception saying "The statement did not return a
    * result set".
    * <p/>