[JDBC-34] get-connection's URI conventions incompatible with PostgreSQL Created: 12/Jun/12 Updated: 31/Jul/12 Resolved: 18/Jun/12
|Reporter:||Chas Emerick||Assignee:||Sean Corfield|
get-connection requires that provided URIs be structured like so:
This is often sufficient, but many PostgreSQL URIs require the use of URI parameters to further configure connections. For example, postgresql.heroku.com provides JDBC URIs like this:
...which, when used outside of Heroku's network, require a further sslfactory=org.postgresql.ssl.NonValidatingFactory parameter.
The PostgreSQL JDBC driver supports a number of different URI parameters, and recommends putting credentials into parameters rather than using the user:password@ convention. Peeking over at the Oracle thin JDBC driver's docs, it appears that it expects credentials using its own idiosyncratic convention, user/password@.
That certainly works, but I presume that such a workaround won't occur to many users, despite the docs/source.
I don't think I've used java.jdbc enough (or RDMBS' enough of late) to comfortably provide a patch (or feel particularly confident in the suggestion above). Hopefully the report is helpful in any case.
|Comment by Sean Corfield [ 14/Jun/12 1:36 AM ]|
How about an option that takes a map like:
Essentially as a shorthand for the workaround you've come up with?
|Comment by Sean Corfield [ 15/Jun/12 10:20 PM ]|
Try 0.2.3-SNAPSHOT which has support for :connection-uri and let me know if that is a reasonable solution for you?
|Comment by Chas Emerick [ 18/Jun/12 3:35 PM ]|
Yup, 0.2.3-SNAPSHOT's :connection-uri works fine. I've since moved on to using a pooled datasource, but this will hopefully be a more obvious path to newcomers than having to learn about :factory and DriverManager.
|Comment by Sean Corfield [ 18/Jun/12 3:52 PM ]|
Resolved by adding :connection-uri option.
|Comment by Carlos [ 28/Jul/12 8:09 PM ]|
accessing an heroku database outside heroku, "sslfactory=org.postgresql.ssl.NonValidatingFactory" doesn't work. i get "ERROR: syntax error at or near "user" Position: 13 - (class org.postgresql.util.PSQLException". this happens whether adding it to :subname or :connection-uri Strings
another minor issue - why the documentation of "with-connection" (0.2.3) refers the following format for the connection string URI:
maybe i'm doing something wrong here
thanks in advance
|Comment by Sean Corfield [ 29/Jul/12 4:57 PM ]|
Carlos, the :connection-uri passes the string directly to the driver with no parsing. The exception you're seeing is coming from inside the PostgreSQL driver so you'll have to consult the documentation for the driver.
The three "URI" styles accepted by java.jdbc are:
If you're using :connection-uri (which is used on its own), you're dealing with the JDBC driver directly.
If you're using :uri or a bare string literal, you're dealing with java.jdbc's parsing (implemented by Phil Hagelberg - of Heroku).
Hope that clarifies?
|Comment by Carlos [ 29/Jul/12 8:36 PM ]|
Sean, thank you for such comprehensive explanation.
Still, it didn't work with any of the options. I used before a postgres JDBC driver to export to the same database (in an SQL modeller - SQLEditor for the MAC) and it worked (though it would connect some times, but others not). The connection String used was like "jdbc:postgresql://host:port/database?user=xxx&password=yyy&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory". The driver name was "org.postgresql.Driver" (JDBC4). Anyway, time to give up. I will just use a local database.
|Comment by Carlos [ 31/Jul/12 7:20 PM ]|
Sean, JDBC combinations were working after. i was neglecting an insert operation in a table with a reserved sql keyword "user", so i was getting a "ERROR: syntax error at or near "user" Position: 13", and therefore the connection was already established at the time.
i'm sorry for all the trouble answering the question (_
|Comment by Sean Corfield [ 31/Jul/12 7:46 PM ]|
Glad you got to the bottom of it and confirmed that it wasn't a problem in java.jdbc!