Get multiple result sets with JDBC doesn't work

Question

I should call stored procedure from the third party SQL Server data base (have rights for read only). Also, when I tried to execute this procedure for example in DataGrip:

EXEC Web.example_procedure 2, 3, 4

I received two results:

firs:

<anonymous>
-----------
3

second:

column_1 | column_2
------------------
   k1    |   v1
   k2    |   v2
   k3    |   v3
...

I need a second table.

Now I'm doing the next due to this article

private static void executeStatement(Connection con) {
    try {
        String SQL = "EXEC Web.example_procedure 2, 3, 4";
        Statement stmt = con.createStatement();
        boolean results = stmt.execute(SQL);
        int rsCount = 0;

        //Loop through the available result sets.
        do {
            if (results) {
                ResultSet rs = stmt.getResultSet();
                rsCount++;

                //Show data from the result set.
                System.out.println("RESULT SET #" + rsCount);
                while (rs.next()) {
                    // something will be here
                }
                rs.close();
            }
            results = stmt.getMoreResults();
        } while (results);
        stmt.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Output is:

RESULT SET #1

In other words, I get only the first result. How to get the second table?

can I modify SQL query? (Which will return only one table without first int result)
JDBC?
Hibernate?

I will be glad to any working variant.

Update

Thanks to @MarkRotteveel and his answer - I solved the problem

String sql = "EXEC Web.example_procedure 2, 3, 4";
PreparedStatement stmt = con.prepareStatement(sql);

boolean result = stmt.execute();

while (true) {
    if (result) {
        ResultSet rs = stmt.getResultSet();

        // in my case first table has only one column, 
        // and I need the second table, which has 9 columns
        if (rs.getMetaData().getColumnCount() > 1) {

            // go through the rows
            while (rs.next()) {

                // for example what we can do
                rs.getMetaData().getColumnCount(); // return column count in the current result set
                rs.getObject(int columnIndex); // get value for column index. Must be not greater than .getColumnCount()
            }
        }

    } else {
        int updateCount = stmt.getUpdateCount();
        if (updateCount == -1) {
            // no more results
            break;
        }
    }
    result = stmt.getMoreResults();
}

You are not showing the code of the stored procedures; you may need to take into account presence of update counts interleaved with the result sets (eg if you don't have `set nocount on` in your SP). `results` being `false` does not mean that there are no results, it means the current result is an update count. Take a look at https://stackoverflow.com/a/14694174/466862 — Mark Rotteveel, Jun 13 '17 at 09:36
@MarkRotteveel Actualy I don't have SP code. But now will ask to receive it. — Optio, Jun 13 '17 at 09:47
In any case, try to process the results as I describe in the linked answer, it might solve your problem. — Mark Rotteveel, Jun 13 '17 at 11:20
This behavior is usually observed if you use jTDS drive instead of Microsoft driver for SQLServer. In this case the solution is noted by @Optio in the "Update" section of the question -> loop as long as updateCount != -1 and result != true — Jeevan, Jan 14 '18 at 18:58

lihongxu · Answer 1 · 2017-06-13T09:10:14.450

Use JDBC CallableStatement:

cstmt.registerOutParameter()
cstmt.getObject()

  String sql = "{call getEmpName (?, ?)}";
  cstmt = conn.prepareCall(sql);

  //Bind IN parameter first, then bind OUT parameter
  int empID = 102;
  cstmt.setInt(1, empID); // This would set ID as 102
  // Because second parameter is OUT so register it
  cstmt.registerOutParameter(2, OracleTypes.CURSOR);

  //Use execute method to run stored procedure.
  System.out.println("Executing stored procedure..." );
  cstmt.execute();

  //Retrieve data
  rs = (ResultSet) cstmt.getObject(1);

https://docs.oracle.com/cd/E17952_01/connector-j-en/connector-j-usagenotes-statements-callable.html

score 0 · Answer 2 · answered Jun 13 '17 at 08:58

0

You can set the Resultset updateable to execute multiple command.

Statement stmt = conn1.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

        String insert1="insert into data values('******','*********')";
        String insert2="insert into data values('*******','******')";
        conn1.setAutoCommit(false);

        ResultSet rs = stmt.executeQuery("select * from data");
        rs.last();

answered Jun 13 '17 at 08:58

Ravibhushan Kumar

387
5
16

Thanks for suggestion! Tried but still have access only for first result. – Optio Jun 13 '17 at 09:49

Get multiple result sets with JDBC doesn't work

2 Answers2