<< Back to previous view

[JDBC-64] Support multiple result sets? Created: 03/Jul/13  Updated: 02/Apr/14

Status: Open
Project: java.jdbc
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Enhancement Priority: Major
Reporter: Sean Corfield Assignee: Sean Corfield
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Useful for stored procedure results:

call_proc.clj
(defn call-stored-proc [connection]
      (jdbc/query
        (myapp.db/connection)
        ["{call someProc()}"]
        :as-arrays? true))

Java code to handle multiple result sets:

MultiResults.java
public static void executeProcedure(Connection con) {
       try {
          CallableStatement stmt = con.prepareCall(...);
          .....  //Set call parameters, if you have IN,OUT, or IN/OUT parameters
    
          boolean results = stmt.execute();
          int rsCount = 0;
    
          //Loop through the available result sets.
         while (results) {
               ResultSet rs = stmt.getResultSet();
               //Retrieve data from the result set.
               while (rs.next()) {
                ....// using rs.getxxx() method to retieve data
               }
               rs.close();
    
            //Check for next result set
            results = stmt.getMoreResults();
          } 
          stmt.close();
       }
       catch (Exception e) {
          e.printStackTrace();
       }
    }


 Comments   
Comment by Sean Corfield [ 15/Sep/13 4:20 PM ]

Post 0.3.0, ideally after adding stored proc support properly (see JDBC-48).

Comment by Kyle Cordes [ 15/Sep/13 9:45 PM ]

With or without SPs, this would be an excellent addition; with some RDBMSs, use of compound statement (or SPs) with multiple result sets is relatively common.

Comment by Sean Corfield [ 02/Apr/14 6:07 PM ]

Discussion with Pieter Laeremans:

Sean: My thinking is that I would add :multi-result? to execute! and query and then arrange for them to return sequences of result sets. Unraveling the calls so multi-result? can work cleanly inside those functions would be the hard part

Pieter: That sounds fine by me. But there's something a bit more subtle I guess,
Now you can pass a function row-fn to transform rows, in the multi-resultset case it would perhaps be more appropriate
to pass on a seq of row-fns, so that a different function can be used on different rows.

Generated at Wed Oct 22 18:16:09 CDT 2014 using JIRA 4.4#649-r158309.