How to get in Java multiple SELECT statements of a Stored Procedure using JDBC

Question

I have created a Stored Procedure in SQL with more than one SELECT statement and when I CALL the Procedure (using JDBC) in my Java app the program is able to fetch and print columns ONLY from the first SELECT statement. However when I CALL the Procedure from my SQL DB I can see every SELECT statement of my Procedure. Is there any way that I can print the columns of every SELECT statement I have created in my Procedure using JDBC?

I am using the "Callable Statement" to get the connection and "Result Set" so as to print the columns I want.

This is the JAVA code I have

public Something something(int jobID){
        openConnection();
        Something something = new Something();
        String query = "{CALL evaluation(" + jobID + ")}";
        ResultSet resultSet;
        try {
            CallableStatement callableStatement = getConnection().prepareCall(query);
            System.out.println(jobID);
            callableStatement.addBatch(query);
            callableStatement.executeBatch();
            resultSet = callableStatement.executeQuery(query);
            while(resultSet.next()){
                System.out.println(resultSet.getString("message"));
                // I can see this column that comes from the first SELECT statement in my Procedure.
                System.out.println(resultSet.getInt("per_sc"));
                // But I can not see any other column from any other SELECT statement, like this one (or any other one).
            }
        } catch (SQLException ex) {
            Logger.getLogger(ICRUDImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return something;
    }

ERROR message: java.sql.SQLException: Column 'per_sc' not found.

Although, 'per_sc' is the name of the second column of my second select statement inside my Procedure.

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using — , Aug 22 '19 at 14:04

score 0 · Answer 1 · answered Aug 22 '19 at 14:24

Seeing the procedure would actually help better understand the issue.

Anyway, the procedure you have at the end of its execution must return one set of values as you defined in its body, it will not automatically collect for you the results of intermediate statements and return everything together at the end.

You should try to have only a single query inside the procedure that returns the full dataset you want in a single shot, or collect the partial results and return them at the end as a single one, which you manually aggregated in the procedure body.

Also this might be related: Queries returning multiple result sets

How to get in Java multiple SELECT statements of a Stored Procedure using JDBC

1 Answers1