SQL State for unique constraint violation

[davide_p]

Hello,

I'm trying to implement a program that uses uCanAccess to communicate with a accdb database due to some projects constraints. Normally I use PostgreSQL for which I know the SQLState (http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html) that I can get with the method getSQLState().
In the example below, I want to check if the unique constraint is violated (the SQLState for postgres is "23505") in order to skip that row and insert the next one, but I cannot find the equivalent for access/uCanAccess.
In "Graham Hamilton, Rick Cattell and Maydene Fisher. JDBC Database Access With Java: A Tutorial and Annotated Reference" I read that "The vendor error code is specific to each driver, so you need to check your driver documentation for a list of error codes and what they mean", but I wasn't able to find this information.

Thank you!

Davide

// Inserisco gli editori
                try {
                    pstmtEditore.setString(1, identificatoreEditore);
                    pstmtEditore.setString(2, editore);

                    pstmtEditore.execute();

                } catch (SQLException sqle) {

                    if (sqle.getSQLState().equals("23505")) {
                        System.out
                                .printf(
                                        "L'editore %s è già stato inserito in precedenza. Passo all'autore successivo. [%s]%n",
                                        editore, sqle.getMessage());
                    } else {
                        System.out
                                .println("Errore di durante l'inserimento degli autori: ");
                        System.out.printf("- Messaggio: %s%n", sqle
                                .getMessage());
                        System.out.printf("- Codice stato SQL: %s%n", sqle
                                .getSQLState());
                        System.out.printf("- Codice errore: %s%n", sqle
                                .getErrorCode());
                        System.out.println("Termine del programma.");

                        System.exit(-1);
                    }
                }

[Marco Amadei]

Yes, you're right, this is the first report about that.
I had better expose directly the hsqldb error codes (class with codes to import: org.hsqldb.error.ErrorCode).
So, error codes and states will be identical to those of hsqldb, which is the mirror dbms (you can easly find the documentation on the web).
It will be fixed in the 2.0.9.2.(that I'm going to release this week)
In the meantime, you could try:

...
catch (SQLException e) {

Throwable hidden=e.getCause();
if(hidden!=null && hidden instanceof SQLException){
    int errorCode=((SQLException)hidden) .getErrorCode();
         if(errorCode==-ErrorCode.X_23505){
                 ...
             }
     }

}

Obviously I'll keep the same error codes, not the same class of constants.
It will be documented in the 2.0.9.2 release readme and on the ucanaccess web site.
Cheers Marco

[davide_p]

Hi Marco,
thank you very much! It works fine now!

Thank you for the detailed explanations; with the error codes documentation your great project becomes very flexible in every situation.

Again.. thanks for the answer!

Davide