<< Back to previous view

[JDBC-48] Support stored procedures with CallableStatement Created: 15/Mar/13  Updated: 15/Sep/13

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

Type: Enhancement Priority: Major
Reporter: Jeremy Heiler Assignee: Sean Corfield
Resolution: Unresolved Votes: 1
Labels: None


 Description   

JDBC's CallableStatement provides support for calling stored procedures. More specifically, it allows you to register OUT parameters which will become the statements (possibly many) ResultSet objects. A CallableStatement is a PreparedStatement, so I am hoping there wont be too much involved with regard to executing them. The main difference is being able to register and consume OUT parameters.

I'll be hacking on this, so patches are forthcoming. Any input is appreciated.



 Comments   
Comment by Sean Corfield [ 15/Mar/13 10:51 PM ]

I've never used stored procs (I don't like the complexity that I've seen them add to version control, change management and deployment) so I'm afraid I can't offer any input - but I really appreciate you taking this on! Thank you!

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

Post 0.3.0. See also JDBC-64.





[JDBC-64] Support multiple result sets? Created: 03/Jul/13  Updated: 30/Jun/17

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.

Comment by Alexey Naumov [ 15/Dec/14 2:39 PM ]

Any updates on the issue?

Comment by Sean Corfield [ 15/Dec/14 2:58 PM ]

No update yet. No one has submitted a patch and I've been too busy to look at this in detail.

Comment by Sean Corfield [ 30/Jun/17 6:41 PM ]

Note: any patch for this now needs to take into account the reducible version of the result set as well as the regular version.





[JDBC-143] Make it easier to test java.jdbc in other environments Created: 17/Sep/16  Updated: 17/Sep/16

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   

Make the versions properties in pom.xml and/or project.clj, make the database names, usernames, and passwords configurable via environment variables.

This came up via a suggestion from the PostgreSQL community – see this pull request for guidance on what we could open up: https://github.com/clojure/java.jdbc/pull/44/files






Generated at Wed Jul 26 19:46:29 CDT 2017 using JIRA 4.4#649-r158309.