Clojurians Log v2

Thanks David, I read through the docs yesterday and found a lot of places that has to be correctly configured 🙂

Did you manage to make it work?

I tried the :multi-rs option today on a sql server stored procedure and it worked perfectly (18 results). Thanks a lot for all the effort @seancorfield ⭐

I have been meaning to add support for that since the "early days" of maintaining clojure.java.jdbc (even tho' I don't use stored procs ever and I don't use SQL Server).

@jarvinenemil I gather you got Windows auth working? Would you like to create an issue on next.jdbc documenting how? Or even a PR for the Tips & Tricks > MS SQL Server section?

Windows Auth - not yet, I did a workaround (username + password) just so I could evaluate next.jdbc and SQL Server SPs on my local instance. I'll give it another try tomorrow at work if I get the time. I'm pretty sure it's the auth-dll missing. If I get it working I'll make a PR for the MS SQL Server section ✌️.

Hi, using next-jdbc, I'm using jdbc/execute-one! . If I get a duplicate key error, I'd like to handle that in my application. I can't find an option to return a map with info instead of an exception. If I catch the exception, then the only option I have is to parse a string (as far as I can see). That doesn't sound very reliable. Any advice on how I should tackle this problem? example of desired result:

(jdbc/execute-one! db query) ;; PSQLException, duplicate key
; => {:error :duplicate-key, :column :email}

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...

@kevin.van.rooijen Interesting idea. clojure.java.jdbc and next.jdbc have always just relied on the JDBC driver throwing an exception and leaving it at that.

As @noisesmith says, handling that level of detail in any universal mechanism is likely impossible.

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

This is what I have to work with from a "standard" JDBC point of view https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/SQLException.html

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.

I don't see anything more useful in PostgreSQL's exception type either https://jdbc.postgresql.org/development/privateapi/org/postgresql/util/PSQLException.html

And there's nothing about duplicate key / constraint violation here https://jdbc.postgresql.org/development/privateapi/org/postgresql/util/PSQLState.html

So I think the bottom line is that JDBC simply doesn't provide that information (i.e., which constraint failed).

(it's a shame because it is a pain to have to parse a string, although you can at least narrow down the exception type use try / catch / catch with some subtypes in there)

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).