I'm no expert, but my first hunch is that what you want is easy to do with some databases, harder with others (requiring parsing) and impossible to do with one universal mechanism

@kevin.van.rooijen this might help with processing the exception object https://docs.oracle.com/javase/tutorial/jdbc/basics/sqlexception.html

these are the valid values for sqlstate https://docs.oracle.com/cd/A87860_01/doc/appdev.817/a58231/appd.htm

I see, sounds like if next-jdbc isn't handling this, I'd have to parse it myself after all

Those links are helpful, thanks. I'll take another look at this tomorrow

don't take my word for it though - the dev does check this channel - I was surprised to see there is programmatic access without parsing (though I just found the docs, haven't used it in anger)

but regardless, that method and those codes should work

I find no match for the getSqlState method in the next.jdbc repo, might make for a nice PR...

As someone who just learned about the ON CONFLICT clause in postgres - maybe that would solve your issue without going back to the application

Thanks, but sadly that doesn't solve my issue. I actually want to go back to the application

mentioned in the thread above, I found an method that gives a supposedly portable error code, a hash map from code to human readable keyword and a hash-map from code to printable message might be nice

https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/SQLException.html#getSQLState() - from the same link you just shared

Yeah, the common subset may be portable, but you can already catch-by-type for a number of them. The duplicate key error should be (a subtype of) https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/SQLIntegrityConstraintViolationException.html

ahh

getSQLState() returns a String. getErrorCode() returns an int -- and it is vendor-specific.

The Oracle docs link you provided isn't about portable values as far as I can see?

Turning an exception into a bean is trivial and would at least make it more Clojure-y. It still wouldn't solve @kevin.van.rooijen’s problem in any generic way.

getSQLState returns a code, those codes are standardized by xopen https://docs.oracle.com/cd/A87860_01/doc/appdev.817/a58231/appd.htm

what I don't know is how good the individual sqls are at implementing those codes

"integrity constraint violation" is both 23000 and 40002 (and there's nothing in any of this that provides information about which constraint was violated) so I'm not sure how that's useful -- given that you can catch java.sql.SQLIntegrityConstraintViolationException for that

yeah, that makes sense

This shows all the portable exceptions that can be caught https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/package-tree.html

I found a better SQLState reference, but that still proves nothing about the quality of various db's implementation of the standard (and the standard itself is something expensive...) https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.messages.doc/doc/rdb2stt.html

the ibm doc says that 40xxx is transaction rollback, and 23xxx is constraight violation, and the two codes you mentioned aren't documented specifically at all

Figures. I think the best you can do in any vaguely portable way is to catch by the various standard JDBC exception types (and then parse the vendor-specific message you get from that exception).

Didn't expect my question to spark such an in depth conversation haha. (I just woke up). Thanks a lot for the info! It does sound like I have to manage this for my specific vendor, which is fine. I at least have a good understanding about how I could tackle this issue