java - Cómo recuperar*todo*de un procedimiento almacenado usando JDBC

sql-server stored-procedures

Question

Ocasionalmente encuentro dos formas de comportamiento extraño cuando uso JDBC para trabajar con procedimientos almacenados de SQL Server:

Problema 1: ejecuto un procedimiento almacenado en SQL Server Management Studio (SSMS) y devuelve un conjunto de resultados. Sin embargo, cuando lo intento

try (CallableStatement cs = conn.prepareCall("{call dbo.TroublesomeSP}")) {
    ResultSet rs = cs.executeQuery();

Me sale la excepcion

com.microsoft.sqlserver.jdbc.SQLServerException: la instrucción no devolvió un conjunto de resultados.

Problema 2: ejecuto un procedimiento almacenado en SSMS y genera un error, pero cuando uso JDBC para .execute el procedimiento almacenado no se produce ninguna excepción.

¿Por qué ocurren estos problemas y cómo puedo evitarlos?

Answer 1

Cuando ejecutamos un procedimiento almacenado en JDBC, obtenemos una serie de cero o más "resultados". Entonces podemos procesar esos "resultados" secuencialmente llamando a CallableStatement#getMoreResults() . Cada "resultado" puede contener

• cero o más filas de datos que podemos recuperar con un objeto ResultSet ,
• un recuento de actualizaciones para una instrucción DML (INSERT, UPDATE, DELETE) que podemos recuperar con CallableStatement#getUpdateCount() , o
• Un error que arroja una SQLServerException.

Para el "Problema 1", el problema a menudo es que el procedimiento almacenado no comienza con SET NOCOUNT ON; y ejecuta una instrucción DML antes de hacer una SELECCIÓN para producir un conjunto de resultados. El recuento de actualizaciones para el DML se devuelve como el primer "resultado", y las filas de datos están "atrapadas detrás de él" hasta que llamemos a getMoreResults .

El "problema 2" es esencialmente el mismo problema. El procedimiento almacenado produce un "resultado" (generalmente un SELECCIONAR, o posiblemente un recuento de actualizaciones) antes de que ocurra el error. El error se devuelve en un "resultado" posterior y no causa una excepción hasta que lo "recuperemos" usando getMoreResults .

En muchos casos, el problema se puede evitar simplemente agregando SET NOCOUNT ON; como la primera instrucción ejecutable en el procedimiento almacenado. Sin embargo, un cambio en el procedimiento almacenado no siempre es posible y el hecho es que para recuperar todo del procedimiento almacenado debemos seguir llamando a getMoreResults hasta que, como dice el Javadoc:

There are no more results when the following is true: 

     // stmt is a Statement object
     ((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))

Eso suena bastante simple pero, como siempre, "el diablo está en los detalles", como se ilustra en el siguiente ejemplo. Para un procedimiento almacenado de SQL Server ...

ALTER PROCEDURE dbo.TroublesomeSP AS
BEGIN
    -- note: no `SET NOCOUNT ON;`
    DECLARE @tbl TABLE (id VARCHAR(3) PRIMARY KEY);

    DROP TABLE NonExistent;
    INSERT INTO @tbl (id) VALUES (''001'');
    SELECT id FROM @tbl;
    INSERT INTO @tbl (id) VALUES (''001'');  -- duplicate key error
    SELECT 1/0;  -- error _inside_ ResultSet
    INSERT INTO @tbl (id) VALUES (''101'');
    INSERT INTO @tbl (id) VALUES (''201''),(''202'');
    SELECT id FROM @tbl;
END

... el siguiente código Java devolverá todo ...

try (CallableStatement cs = conn.prepareCall("{call dbo.TroublesomeSP}")) {
    boolean resultSetAvailable = false;
    int numberOfResultsProcessed = 0;
    try {
        resultSetAvailable = cs.execute();
    } catch (SQLServerException sse) {
        System.out.printf("Exception thrown on execute: %s%n%n", sse.getMessage());
        numberOfResultsProcessed++;
    }
    int updateCount = -2;  // initialize to impossible(?) value
    while (true) {
        boolean exceptionOccurred = true; 
        do {
            try {
                if (numberOfResultsProcessed > 0) {
                    resultSetAvailable = cs.getMoreResults();
                }
                exceptionOccurred = false;
                updateCount = cs.getUpdateCount();
            } catch (SQLServerException sse) {
                System.out.printf("Current result is an exception: %s%n%n", sse.getMessage());
            }
            numberOfResultsProcessed++;
        } while (exceptionOccurred);

        if ((!resultSetAvailable) && (updateCount == -1)) {
            break;  // we''re done
        }

        if (resultSetAvailable) {
            System.out.println("Current result is a ResultSet:");
            try (ResultSet rs = cs.getResultSet()) {
                try {
                    while (rs.next()) {
                        System.out.println(rs.getString(1));
                    }
                } catch (SQLServerException sse) {
                    System.out.printf("Exception while processing ResultSet: %s%n", sse.getMessage());
                }
            }
        } else {
            System.out.printf("Current result is an update count: %d %s affected%n",
                    updateCount,
                    updateCount == 1 ? "row was" : "rows were");
        }
        System.out.println();
    }
    System.out.println("[end of results]");
}

... produciendo la siguiente salida de consola:

Exception thrown on execute: Cannot drop the table ''NonExistent'', because it does not exist or you do not have permission.

Current result is an update count: 1 row was affected

Current result is a ResultSet:
001

Current result is an exception: Violation of PRIMARY KEY constraint ''PK__#314D4EA__3213E83F3335971A''. Cannot insert duplicate key in object ''dbo.@tbl''. The duplicate key value is (001).

Current result is a ResultSet:
Exception while processing ResultSet: Divide by zero error encountered.

Current result is an update count: 1 row was affected

Current result is an update count: 2 rows were affected

Current result is a ResultSet:
001
101
201
202

[end of results]