[hibernate-issues] [Hibernate-JIRA] Closed: (HHH-6887) Improve SQLStateConverter to take sql state type from the database metadata into account

Improve SQLStateConverter to take sql state type from the database metadata into account ---------------------------------------------------------------------------------------- Key: HHH-6887 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6887 Project: Hibernate Core Issue Type: Improvement Components: core Affects Versions: 4.0.0.CR7 Reporter: Hardy Ferentschik Assignee: Steve Ebersole Labels: DatabaseMetaData Fix For: 4.0.1 Time Spent: 4h 55m See also _org.hibernate.engine.jdbc.internal.JdbcServicesImpl.ExtractedDatabaseMetaDataImpl#getSqlStateType_ Log of IRC discussion on what needs to be done: {noformat} hardy: sebersole: hi, I have a sql error code question if you have time [4:33pm] sebersole: hardy: ok [4:33pm] hardy: it is related to HV-516 [4:33pm] jbossbot: jira [HV-516] Hibernate Validator doesn't work well with hibernate persistence when generating DDL [Open (Unresolved) Bug, Critical, Hardy Ferentschik] https://hibernate.onjira.com/browse/HV-516 [4:33pm] hardy: the issue claims that a length constraints does not get applied during DDL generation [4:33pm] hardy: the issue itself is wrong [4:34pm] hardy: the length constraint gets created, but while testing it I found something else [4:34pm] hardy: I wrote this test [4:34pm] sebersole: ok [4:34pm] hardy: https://github.com/hferentschik/hibernate-core/commit/d548da916304a0504a8... [4:34pm] jbossbot: git [hibernate-core] d548da9.. Hardy Ferentschik HV-516 adding test for H2 [4:34pm] jbossbot: jira [HV-516] Hibernate Validator doesn't work well with hibernate persistence when generating DDL [Open (Unresolved) Bug, Critical, Hardy Ferentschik] https://hibernate.onjira.com/browse/HV-516 [4:34pm] sebersole: should i look at that? [4:34pm] hardy: look at testLengthColumnConstraintApplied [4:34pm] hardy: if you can [4:35pm] sebersole: just a lot of info without any direction/request [4:35pm] hardy: i am getting there [4:35pm] hardy: my question is SQLStateConverter [4:36pm] sebersole: so a violation should happen? [4:36pm] hardy: right [4:36pm] sebersole: for whcih? [4:36pm] hardy: SQLStateConverter defines a INTEGRITY_VIOLATION_CATEGORIES set [4:36pm] hardy: turns out H2 I would have to add "90" [4:36pm] hardy: for Oracle yet something else [4:37pm] hardy: same for POstgres [4:37pm] hardy: is the intend to add codes there? [4:37pm] hardy: also the javadocs talks about "ErrorCode-based converter" [4:38pm] sebersole: Interpretation of a SQL error based on SQLState [4:38pm] sebersole: * is not nearly as accurate as using the ErrorCode (which is, however, vendor- [4:38pm] sebersole: * specific). Use of a ErrorCode-based converter should be preferred approach [4:38pm] sebersole: * for converting/interpreting SQLExceptions. [4:38pm] sebersole: yes exactly [4:38pm] hardy: I cannot find anything like this in the codebase, but I think we used to have something like this [4:38pm] sebersole: error codes are vendor specific [4:38pm] sebersole: thats the problem [4:38pm] sebersole: sql states are well defined [4:38pm] hardy: right, I found some documentation about this somewhere [4:38pm] hardy: but is seems the db vendors don't care about ths [4:39pm] sebersole: hardy: so here is the choice... [4:39pm] sebersole: (which we discussed way back when when we made this decision btw) [4:39pm] sebersole: have built-in support for sql state based conversion [4:40pm] hardy: i am not saying something is wrong [4:40pm] hardy: just trying to understand how this is supposed to work [4:40pm] sebersole: or maintain error-code interpretation for each and every db [4:40pm] hardy: and whether there is some pluggable error code convter thingy [4:40pm] sebersole: sure [4:40pm] sebersole: exception converters are pluggable [4:41pm] hardy: ahhh [4:41pm] hardy: how? [4:41pm] stliu: dialect [4:41pm] sebersole: couple of ways [4:41pm] sebersole: yes, one is dialect [4:41pm] sebersole: pretty sure there is a config setting also [4:41pm] hardy: Dialect.buildSQLExceptionConverter [4:42pm] hardy: that's what I was looking for [4:42pm] hardy: back to the decision you mentioned [4:42pm] sebersole: btw [4:42pm] hardy: are we providing/maintaining Dialect specific converters? [4:42pm] sebersole: we also probably should take into account: java.sql.DatabaseMetaData#getSQLStateType [4:42pm] sebersole: which we dont today [4:43pm] hardy: you really like your DatabaseMetaData [4:43pm] sebersole: we simply assume the x/open codes [4:43pm] hardy: I get the feeling we should use it much more [4:43pm] sebersole: well, if a vnedor implements it well you can gather lots of useful info [4:43pm] hardy: sure [4:43pm] sebersole: anyway, like i said, i think we map to the x/open codes [4:44pm] hardy: and don't maintain db specific converters [4:44pm] sebersole: but apparently some vendors map to "sql 2003" codes [4:44pm] hardy: so there is no need for me to create an issue? [4:44pm] sebersole: do you want to volunteer for that? [4:44pm] sebersole: i know i dont [4:44pm] hardy: not really [4:44pm] hardy: as said, just trying to understand [4:44pm] sebersole: we actually started down that path at first [4:45pm] sebersole: another option... [4:45pm] sebersole: jdbc 4 (jdk 6) defines SQLException hierarchy [4:45pm] sebersole: we could start leveraging that [4:45pm] hardy: i don't think I want to start with creating vendor specific converters [4:45pm] sebersole: we do nothing with it today [4:46pm] hardy: but as you say, maybe we can get a little smarter using DatabaseMetaData or the SQLException hierarchy [4:46pm] sebersole: for example, http://docs.oracle.com/javase/6/docs/api/java/sql/SQLIntegrityConstraintV... [4:46pm] stliu: sebersole: anyway, let me know if you want me do something on the jdocbook side, or i will keep working on the failures with trace logging level [4:46pm] hardy: so maybe the issue is to investigate an alternative sql exception converter which takes into various metadata [4:46pm] sebersole: i wonder if the underltying exceptions you see are that type? [4:46pm] stliu: time to go to sleep [4:47pm] sebersole: stliu: the later for now [4:47pm] sebersole: thanks [4:47pm] stliu: have a nice weekend [4:47pm] sebersole: thats more important [4:47pm] sebersole: you too [4:47pm] sebersole: hardy: right [4:47pm] sebersole: i think its 2-fold [4:47pm] hardy: sebersole: yes, for Oracle I get a 23xyz code [4:47pm] stliu left the chat room. [4:47pm] sebersole: not talking aBOUT THAt [4:48pm] sebersole: talking about the exact SQLException type [4:48pm] sebersole: err, subtype [4:48pm] sebersole: so, 1) investigate leveraging SQLException hierarchy [4:49pm] sebersole: and 2) investigate supporting both x/open and sql 2003 defined codes for sql state [4:49pm] hardy: ok [4:49pm] sebersole: hardy: its funny [4:49pm] hardy: ? [4:49pm] sebersole: i actually am already pulling that value [4:50pm] sebersole: so i must have considered this at some point [4:50pm] hardy: i see [4:50pm] hardy: i will create a jira. I makes sense to try to improve on this [4:50pm] sebersole: org.hibernate.engine.jdbc.internal.JdbcServicesImpl.ExtractedDatabaseMetaDataImpl#getSqlStateType [4:50pm] hardy: nice [4:50pm] sebersole: its an enum [4:51pm] sebersole: public enum SQLStateType { [4:51pm] sebersole: XOpen, [4:51pm] sebersole: SQL99, [4:51pm] sebersole: UNKOWN [4:51pm] sebersole: } [4:51pm] sebersole: though i think SQL99 is incorrect [4:51pm] hardy: so when you were talking about SQL 2003 you meant SQL99 [4:51pm] sebersole: no ^^ [4:52pm] sebersole: i think i just did it too fast [4:52pm] sebersole: and wrote SQL99 meaning to write SQL03 [4:52pm] hardy: got you [4:52pm] sebersole: so in terms of that, there are 2 options [4:52pm] hardy: you love your options [4:53pm] sebersole: 1) would be to have a single sqlstate converter that knew all codes for both sets [4:53pm] sebersole: 2) have 2 separate ones [4:53pm] hardy: having two makes sense to me, but that is just an impl detail [4:53pm] sebersole: just not sure if there is contradiction between them [4:53pm] sebersole: well its might not be just an "impl detail" though if the 2 have contradicting code sets [4:54pm] sebersole: like you mentioned the 23 category above [4:54pm] sebersole: [10:47] <hardy> sebersole: yes, for Oracle I get a 23xyz code [4:54pm] sebersole: like if one set defines that as a constraint violation while one defines that as a connection problem, we obviously would have trouble in option (1) [4:55pm] sebersole: anyway, i can work on that if you need [4:55pm] hardy: well, even in option 1 you would have to clearly separate between the different sql state types [4:55pm] sebersole: just let me know [4:56pm] hardy: I think it would be very useful to work on this [4:56pm] sebersole: again, it depends on if there is overlap [4:56pm] hardy: sounds also like a nice little contained issue I could work on [4:56pm] sebersole: for me to, or for you to? [4:56pm] sebersole: ok [4:56pm] sebersole: now that we expect jdk6, the SQLException subtype handling should get added [4:56pm] hardy: indirectly related to Validator as well, since you would like to get the right exception [4:57pm] hardy: sure [4:57pm] sebersole: that should be the "first line of defense" [4:57pm] hardy: ok [4:57pm] sebersole: the assumption being that if there is a specific subtype the vendor would have hopefully chosen the correct subtype [4:59pm] sebersole: btw, this is yet another place where that notion of a "jdbc capabilities" object that merges DatabaseMetaData and Dialect information would be golden [5:00pm] hardy: jupp {noformat}