how to catch a particular constraint failure on insert (e.g. ensuring unique usernames) without proper exceptions?

[Simon]

If we create a new user in the DB, and the DB enforces unique username
with a simple constraint, then we have to be able to catch this
specifically when we do the insert (querying before the insert is not
enough, as there is still a window). If the insert failes because of
this constraint, we have to feed this back to the user that his chosen
username is taken, and to choose another.

Mybatis has no proper checked exceptions, but Im guessing that you can
catch the undocumented runtime IbatisExcpeiotn or PersistanceException
or SqlSessionExcption (although we dont know what throws these).

However, none of these runtime exceptions seem to have access to the
oracle error which resulted in the excpetion, i.e. the particular
constraint which failed.

How are people handling this common situtaion?

The default is presmably for the runtime exception to kill the
application if the username exists, which is not acceptable for a
production application obviously.

Thanks,

[Clinton Begin]

Follow the nested exception chain until you hit the SQLException. You have full access to the root Oracle exception.

Cheers,
Clinton

[Simon]

Ok, i wrote this basic function to extract the oracle error from
PersistanceException. Not sure if its always at this level though.

public class ORA_Errors {

public static final int UNIQUECONSTRAINT = 1;

public static int getOraError(Throwable e) {

int code = -1;

try {
Throwable cause = e.getCause();
String message = cause.getMessage();
if (message != null) {
int index = message.indexOf("ORA");
if (index >= 0) {
String err = message.substring(index+4, index+ 9);
code = Integer.parseInt(err);
} // if index ok
} // if message ok
} catch (Throwable t) {
log.debug("Got error when trying to decode oracle exception" +
e.getMessage());
// Do nothing as the error might not contain oracle error code
}
return code;
}
}

public void create(Player player) throws AlreadyExists {
try {
PlayerMapper mapper = session.getMapper(PlayerMapper.class);
mapper.insert(player);
session.commit();
} catch (PersistenceException e) {
if (ORA_Errors.getOraError(e) == ORA_Errors.UNIQUECONSTRAINT)
throw new AlreadyExists("New player was " + player.toString() +
"Message was" + e.getCause());
else
throw e;
} finally {
session.close();

[Clinton Begin]

You're on the right track. You can also use a simple utility method
like: unwrapSqlException(Throwable).

You canuse instanceof instead of string comparison.

Then to extract the oracle code, you can use regex to avoid position changes.

Clinton

--
Sent from my mobile device

[Björn Raupach]

We usually check the constraints before inserting anything into the database.
In case of a unique constraint our mapper interface has methods like:

public boolean insertUser(User user);
public boolean containsUsername(String username);

And with some sql:

SELECT CASE COUNT(Username) WHEN 0 THEN 0 ELSE 1 END
FROM Users
WHERE Username = #{value}

...mybatis type does the rest. Very convenient.

Database isolation level is set to serializable and the contains method is called in the scope of the insert transaction.

try {
UserMapper mapper = session.getMapper(UserMapper.class);
if (mapper.containsUsername(username) {
return false;
} else {
mapper.insertUser(user);
}
session.commit();
return true;
} finally {
session.close();

[Björn Raupach]

select count(Username) from Users where Username = #{value}

....I don't know how I came up with the case statement....

Database isolation level is set to serializable.

[Chema]

2010/7/20 Björn Raupach <raupach...@googlemail.com>:

> We usually check the constraints before inserting anything into the database.

Well, it's a way to make it, but I think exceptions are that: rare
cases (that must be managed).
This way performs an additional query to check the constraint, but I
prefer to think that most of the times , the input data is right and
I only must manage the exceptional cases. IMHO

What do you think about this ?

[Björn Raupach]

Totally agree. In fact unique constraints are the only thing we check and we don't have many.

Larry gave a good explanation in a different post. In brief we double check "Oops!" exceptions but "AHHs!" crashes our app for good. And its nice to validate a username with ajax before the customer hits the submit button.