Hi, I think the first step should be to support the more specific exceptions as available in Java 6 (SQLDataException, SQLSyntaxErrorException, SQLTimeoutException,...). I'm not sure if it's then needed to support even more specific exceptions.
Regards, Thomas On Fri, May 18, 2012 at 6:18 PM, Luigi R. Viggiano <[email protected]> wrote: > Hi. > > This is actually a problem of how jdbc specification is made, but it > can be implemented better - I think - from jdbc drivers. > > At the moment when I violate a unique index or primary key I get a > org.h2.jdbc.JdbcSQLException which is thrown by any jdbc sql > exception, including grammar problems, or connection failures or > things like that. To discriminate exceptions I want to handle I need > then to get the "errorCode" (an integer). This is how jdbc spec > mandates the implementation. > > So this is an example: > > org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: > "CONSTRAINT_INDEX_9 ON PUBLIC.FOOBAR(BAZ)"; SQL statement: > insert into foobar (...) values (...) [23505-166] > > I would suggest to create subclasses of JdbcSQLException, like > UniqueIndexOrPrimaryKeyViolatedException for those specific cases, so > if I want I can catch those exceptions and handle the case without > parsing the errorCode. > > Why I think this is useful? > Because, when you insert a record in the database, you need to check > if the pk is already used, you have two ways: > a- check before insert, which means: open a transaction, lock the > whole table with a "select for update on a 'lock record'" and do a > select before the insert. This is the only way I know to prevent > another transaction at the same time to insert the record between the > select and the insert, and this is the only "portable" solution across > the database (that I know of). Quite complicate. > b- try to insert the record, then parse the errorCode. Efficient, but > not portable since the errorCode, is vendor-specific > > I would prefer the b option, but it would be even better to avoid > parsing the errorCode and catch directly the specific exceptions. > > I think this would offer some benefits to developers, with a minimal > complexity on the h2 implementation. > Eventually, I can offer my collaboration to implement this or to > provide a working prototype, if this is thought to be useful. > > Luigi. > > -- > You received this message because you are subscribed to the Google Groups "H2 > Database" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
