How to Get *Everything* Back from a Stored Procedure Using Jdbc

How to get *everything* back from a stored procedure using JDBC

When we execute a stored procedure in JDBC we get back a series of zero or more "results". We can then process those "results" sequentially by calling CallableStatement#getMoreResults(). Each "result" can contain

• zero or more rows of data that we can retrieve with a ResultSet object,
• an update count for a DML statement (INSERT, UPDATE, DELETE) that we can retrieve with CallableStatement#getUpdateCount(), or
• an error that throws an SQLServerException.

For "Issue 1" the problem is often that the stored procedure does not begin with SET NOCOUNT ON; and executes a DML statement before doing a SELECT to produce a result set. The update count for the DML is returned as the first "result", and the data rows are "stuck behind it" until we call getMoreResults.

"Issue 2" is essentially same problem. The stored procedure produces a "result" (usually a SELECT, or possibly an update count) before the error occurs. The error is returned in a subsequent "result" and does not cause an exception until we "retrieve" it using getMoreResults.

In many cases the problem can be avoided by simply adding SET NOCOUNT ON; as the first executable statement in the stored procedure. However, a change to the stored procedure is not always possible and the fact remains that in order to get everything back from the stored procedure we need to keep calling getMoreResults until, as the Javadoc says:

There are no more results when the following is true: 

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

That sounds simple enough but as usual, "the devil is in the details", as illustrated by the following example. For a SQL Server stored procedure ...

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

... the following Java code will return everything ...

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

... producing the following console output:

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]

JDBC SQL Server Stored Procedure with ResultSet, return value, and output parameters

Given the order of processing in your stored procedure (insert, select, then populate result parameters), you need to process the result set before you retrieve the return value with CallableStatement.getXXX.

How to get output from stored procedure in JDBC

First, be aware that COUNT(MEMBER_ID) will return an integer value but your are selecting it into a VARCHAR variable.

Next, I think you are looking for something like:

    CallableStatement stm = connection.prepareCall("{ call CountMembers(?) }");
    stm.registerOutParameter(1, Types.VARCHAR);
    stm.execute();
    String m_count = stm.getString(1);
    stm.close();

Retrieve the returned value from sql server stored procedure using java

Below is the solution to the problem and it works perfectly fine in my case.

CallableStatement cs = con.prepareCall("{call my_procedure(?,?)}");  
int i = 0;
cs.setString(++i, advisor);
cs.setString(++i, adviceXml);

boolean isRs = cs.execute();
int updateCount = cs.getUpdateCount();
// cs.getUpdateCount() will return -1 if the current result is a ResultSet object 
// or there are no more results
// cs.getMoreResults() will return true  if the next result is a ResultSet object; 
// false if it is an update count or there are no more results

while (!isRs && (cs.getUpdateCount() != -1)) {
    isRs = cs.getMoreResults(); 
}

if (isRs) {
    ResultSet rs = cs.getResultSet();
    try {
        if (rs.next()) {
             groupId = rs.getString("psrg_idi");
        }
    } finally {
        rs.close();
    }
}

Can I call a stored procedure in jdbc which returns a table using mysql?

Yes. It's possible to call a MySQL stored procedure to return a resultset using JDBC.

Examples provided here:

https://docs.oracle.com/javase/tutorial/jdbc/basics/storedprocedures.html#calling_javadb_mysql

---

Related Topics