java - Comment récupérer*tout*depuis une procédure stockée à l'aide de JDBC



sql-server stored-procedures (1)

Je rencontre parfois des comportements étranges lorsque j'utilise JDBC pour travailler avec des procédures stockées SQL Server:

Problème 1: J'exécute une procédure stockée dans SQL Server Management Studio (SSMS) et renvoie un jeu de résultats. Cependant, quand j'essaie

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

Je reçois l'exception

com.microsoft.sqlserver.jdbc.SQLServerException: l'instruction n'a pas renvoyé de jeu de résultats.

Problème 2: J'exécute une procédure stockée dans SSMS et une erreur est .execute , mais lorsque j'utilise JDBC pour .execute la procédure stockée, aucune exception n'est levée.

Pourquoi ces problèmes se produisent-ils et comment puis-je les éviter?


Lorsque nous exécutons une procédure stockée dans JDBC, nous récupérons une série de zéro ou plusieurs "résultats". Nous pouvons ensuite traiter ces "résultats" séquentiellement en appelant CallableStatement#getMoreResults() . Chaque "résultat" peut contenir

  • zéro ou plusieurs lignes de données que nous pouvons récupérer avec un objet ResultSet ,
  • un nombre de mises à jour pour une instruction DML (INSERT, UPDATE, DELETE) que nous pouvons récupérer avec CallableStatement#getUpdateCount() , ou
  • une erreur qui lève une exception SQLServerException.

Pour "Problème 1", le problème est souvent que la procédure stockée ne commence pas par SET NOCOUNT ON; et exécute une instruction DML avant de faire un SELECT pour produire un ensemble de résultats. Le nombre de mises à jour pour le DML est renvoyé en tant que premier "résultat" et les lignes de données sont "bloquées derrière" jusqu'à l'appel de getMoreResults .

"Issue 2" est essentiellement le même problème. La procédure stockée produit un "résultat" (généralement un SELECT ou éventuellement un nombre de mises à jour) avant que l'erreur ne se produise. L'erreur est renvoyée dans un "résultat" ultérieur et ne provoque pas d'exception jusqu'à ce que nous " getMoreResults " celle-ci à l'aide de getMoreResults .

Dans de nombreux cas, le problème peut être évité en ajoutant simplement SET NOCOUNT ON; en tant que première instruction exécutable dans la procédure stockée. Cependant, une modification de la procédure stockée n’est pas toujours possible et il n’en reste pas moins que pour pouvoir tout récupérer de la procédure stockée, nous devons continuer à appeler getMoreResults jusqu’à getMoreResults que, comme le dit la Javadoc:

There are no more results when the following is true: 

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

Cela semble assez simple, mais comme d’habitude, "le diable est dans les détails", comme illustré par l’exemple suivant. Pour une procédure stockée 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

... le code Java suivant retournera tout ...

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]");
}

... produisant la sortie de console suivante:

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 '[email protected]'. 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]




jdbc