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.
