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

[Steve Ebersole (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-6887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steve Ebersole resolved HHH-6887.
---------------------------------

    Resolution: Fixed

> 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/d548da916304a0504a896cf4650284a0932a32da
> [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/SQLIntegrityConstraintViolationException.html
> [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}

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira