<< Back to previous view

[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






[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-157] Review specs for efficiency Created: 19/Sep/17  Updated: 19/Sep/17

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

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


 Description   

Can they be refactored to be faster? Less 'or', less '*'?






[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: 2
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-170] Connection string query parameters aren't decoded correctly Created: 04/Jul/18  Updated: 29/Oct/18

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

Type: Defect Priority: Major
Reporter: Philip Garrett Assignee: Sean Corfield
Resolution: Unresolved Votes: 0
Labels: bug
Environment:

clojure 1.9.0, clojure.java.jdbc 0.7.7



 Description   

The parse-properties-uri function uses uri.getQuery(), which decodes the entire query string. This means that any query string parameters that include the characters = or & (even when properly encoded) will be lost in the subsequent steps to split apart the k/v pairs.

Instead, it should split the raw query string on "&", then the raw key-value pairs on "=". Then, decode each name or value independently using e.g. URLDecoder.

My use case is that I am passing a CA certificate in PEM format to Postgres in the connection string. Since the PEM data is base64, it sometimes ends with = padding, for example:

-----BEGIN CERTIFICATE-----
...
pvjw==
-----END CERTIFICATE-----

The parameter in the connection string is properly encoded:

sslfactoryarg=-----BEGIN+CERTIFICATE...pvjw%3D%3D%0A-----END+CERTIFICATE-----

When I use this connection string with clojure.java.jdbc, I receive an exception (java.security.cert.CertificateException: java.io.IOException: Incomplete data) because the equals padding and everything after it was truncated by this split.

Perhaps a more compelling use case for most folks is passwords. Postgres accepts passwords as connection string query parameters. If the password contains an equals sign or ampersand, you will receive baffling authentication failures:

First, set up a few users. (This also requires altering pg_hba.conf from the default.)

postgres=# CREATE USER user_control PASSWORD 'no-special-characters';
CREATE ROLE

postgres=# CREATE USER user_eq PASSWORD 'password contains =';
CREATE ROLE

postgres=# CREATE USER user_amp PASSWORD 'password contains &';
CREATE ROLE

Now try connecting with clojure.java.jdbc:

user=> (require '[clojure.java.jdbc :as j]
  #_=>          '[clojure.test :refer :all])
nil

user=> (import '[java.net URLEncoder])
java.net.URLEncoder

user=> (defn test-conn
  #_=>   [user pass]
  #_=>   (let [db (str "postgresql://127.0.0.1:5432/postgres"
  #_=>                 "?user=" (URLEncoder/encode user)
  #_=>                 "&password=" (URLEncoder/encode pass))]
  #_=>     (j/query db "select current_user")))
#'user/test-conn

user=> (test-conn "user_control" "no-special-characters")
({:current_user "user_control"})

user=> (test-conn "user_eq" "password contains =")
...
PSQLException FATAL: password authentication failed for user "user_eq"  org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication (ConnectionFactoryImpl.java:438)

user=> (test-conn "user_amp" "password contains &")
...
PSQLException FATAL: password authentication failed for user "user_amp"  org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication (ConnectionFactoryImpl.java:438)


 Comments   
Comment by Sean Corfield [ 04/Jul/18 11:26 AM ]

For now, can you work around this with:

(let [db {:dbtype "postgres" :dbname "postgres"
          :user user :password pass
          :sslfactoryarg "-----BEGIN+CERTIFICATE...pvjw%3D%3D%0A-----END+CERTIFICATE-----"}]
  ...)

The hash map form for db-spec is preferred and should avoid any URI encoding/parsing issues.

Comment by Sean Corfield [ 25/Jul/18 1:15 AM ]

Philip Garrett Can you confirm whether using the hashmap form works for this?

Comment by Sean Corfield [ 10/Aug/18 10:38 PM ]

bump Philip?

Comment by Philip Garrett [ 11/Aug/18 9:55 AM ]

@seancorfield Yes, it works. It just means I have to parse the connection string correctly in my application.

Comment by Sean Corfield [ 29/Oct/18 11:59 AM ]

Updated link to lines containing the split calls https://github.com/clojure/java.jdbc/blob/master/src/main/clojure/clojure/java/jdbc.clj#L211-L213

I'm a bit concerned that this might break URI handling where the separating & and = are themselves encoded – I'm not sure whether that's a real-world concern or not but it would be a breaking behavior change if anyone is taking advantage of that.

I've never been particularly happy with the idea of parsing a connection URI like this in clojure.java.jdbc, which is why I always encourage using the dbtype / dbname format instead for the db-spec.

I'll have to give this more thought.





Generated at Tue Nov 13 12:39:05 CST 2018 using JIRA 4.4#649-r158309.