search

OpenLiberty / open-liberty

Open Liberty is a highly composable, fast to start, dynamic application server runtime environment
https://openliberty.io
Eclipse Public License 2.0
1.16k stars 597 forks source link

Instructions to replace com.ibm.websphere.ce.cm/j2c exceptions #8349

Open njr-11 opened 5 years ago

njr-11 commented 5 years ago

Write up some detailed instructions on how to replace exceptions in the com.ibm.websphere.ce.cm and com.ibm.websphere.ce.j2c packages with standard JDBC spec exceptions. The intent is to improve the migration tool with this information.

com.ibm.websphere.ce.j2c package

com.ibm.websphere.ce.cm package

Bukama commented 4 years ago

I was investigating this topic at work too and saw this issue. I just want to add something I just logged while testing:

com.ibm.websphere.ce.cm.DuplicateKeyException Purpose: Originally provided as a convenience to internal code for detecting attempted insertion of and later externalized, only to later come into conflict with the JDBC 4.0 spec-defined SQLException subclass for constraint violations. Replacement Actions: If a JDBC 4.0 compliant JDBC driver is used, switch to the JDBC 4.0 spec standard exception: java.sql.SQLIntegrityConstraintViolationException. If using an out-of-date JDBC driver at an earlier JDBC specification compliance level than JDBC 4.0, trap for SQLException and compare the SQLState (23505 for all databases; 23500, 23L01 for Derby) and error code (2627 for DataDirect Connect for JDBC; -803 for DB2; -239, -268 for Informix; 2627 for Microsoft JDBC; 1 for Oracle, 2601 for Sybase).

This is not correct as Oracle docs say - there isn't any 23505 for Oracle!. When an unique constraint is violated the SQLState is 23000 (not 23505) and SQLError is 1 (OK). See this log where we loop throw the exception and check if it's and DuplicateKeyException:

  public static boolean isDuplicateKeyException(Exception exception) {
    LOG.entry(exception);

    Throwable cause = exception;
    while (cause != null) {

      LOG.trace("Class: [{}]", cause.getClass().getName());
      LOG.trace("Message: [{}]", cause.getMessage());

      boolean isIBMDKE = (cause instanceof DuplicateKeyException);
      LOG.trace("Ist IBM-DuplicateKeyException: [{}]", isIBMDKE);

      boolean isSQLE = (cause instanceof SQLException);
      LOG.trace("Ist SQLException: [{}]", isSQLE);

      if (cause instanceof SQLException) {
        SQLException sqlE = (SQLException) cause;
        LOG.trace("SQL-Error: [{}]", sqlE.getErrorCode());
        LOG.trace("SQL-State: [{}]", sqlE.getSQLState());

      }

      if (cause instanceof DuplicateKeyException) {
        LOG.exit(true);
        return true;
      }
      cause = cause.getCause();
    }

    LOG.exit(false);
    return false;
  }

Log: 

1. Loop
2020-04-23T09:22:21,130 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:134 - entry params(javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement)
2020-04-23T09:22:21,130 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:139 - Class: [javax.persistence.PersistenceException]
2020-04-23T09:22:21,130 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:140 - Message: [org.hibernate.exception.ConstraintViolationException: could not execute statement]
2020-04-23T09:22:21,130 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:143 - Ist IBM-DuplicateKeyException: [false]
2020-04-23T09:22:21,130 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:146 - Ist SQLException: [false]

2. Loop
2020-04-23T09:22:21,130 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:139 - Class: [org.hibernate.exception.ConstraintViolationException]
2020-04-23T09:22:21,130 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:140 - Message: [could not execute statement]
2020-04-23T09:22:21,130 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:143 - Ist IBM-DuplicateKeyException: [false]
2020-04-23T09:22:21,130 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:146 - Ist SQLException: [false]

3. Loop
2020-04-23T09:22:21,130 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:139 - Class: [com.ibm.websphere.ce.cm.DuplicateKeyException]
2020-04-23T09:22:21,130 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:140 - Message: [ORA-00001: unique constraint (<NAME OF MY CONSTRAINT>) violated]
2020-04-23T09:22:21,130 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:143 - Ist IBM-DuplicateKeyException: [true]
2020-04-23T09:22:21,146 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:146 - Ist SQLException: [true]
2020-04-23T09:22:21,146 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:150 - SQL-Error: [1]
2020-04-23T09:22:21,146 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:151 - SQL-State: [23000]
2020-04-23T09:22:21,146 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:156 - exit with(true)

I should mentione here that the SQLState seems to affect more errors, e.g. when violating a check constraint (X is allowed but inserting Y) the SQLError is 2290 as logs show. Also the Exception is another (as expected):

3. Loop

2020-04-23T09:49:57,107 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:139 - Class: [java.sql.SQLIntegrityConstraintViolationException]
2020-04-23T09:49:57,107 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:140 - Message: [ORA-02290: check constraint (<NAME OF MY CONSTRAINT>) violated]
2020-04-23T09:49:57,107 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:143 - Ist IBM-DuplicateKeyException: [false]
2020-04-23T09:49:57,107 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:146 - Ist SQLException: [true]
2020-04-23T09:49:57,107 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:150 - SQL-Error: [2290]
2020-04-23T09:49:57,107 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:151 - SQL-State: [23000]
2020-04-23T09:49:57,107 TRACE [.Alarm Pool : 0] d.i.c.g.u.DaoUtil:162 - exit with(false)
njr-11 commented 4 years ago

@Bukama thanks for spotting this problem. I went back and checked the code and it does look for SQL State 23505 in all cases, which ends up being a no-op for Oracle. It gets away with this because it is also looking for the error code of 1, and considering either condition being met as indicating the DuplicateKeyException. While that is so, it doesn't seem to constitute a good reason why we should direct the user to perform a no-op check for Oracle, so I'll looking into rewriting that proposed section to be more precise.