[eclipselink-users] Dynamically dropping and creating indexes

["Mark Millman" <mark.millman@xxxxxxxxx>]

I want to be able to drop and create indexes at runtime so I tried to call
Query.executeUpdate(sql) to accomplish this as follows:

  public void executeDDL(String sql) throws PersistenceException {
    EntityManager em = getEm();
    final EntityTransaction et = em.getTransaction();
    try {
      et.begin();
      em.clear();
      Query ddl = getEm().createNativeQuery(sql);
      int c = ddl.executeUpdate();
      et.commit();
    } catch (Exception e) {
      _Logger.error("executeDDL failure of "+sql, e);
      if (et != null && et.isActive()) {
        et.rollback();
      }
      throw new PersistenceException("executeDDL failure: " +
e.getCause().getLocalizedMessage(), e.getCause());
    }
  }

And it worked ... but only once.  I could drop an index and create it again
but when I attempted to drop it a second time it returned without error but
the index wasn't dropped.  Ditto on creating it a second time, even though
it existed it didn't throw any errors.

Is there some better way to execute DDL at runtime or should I continue to
perform these actions using JDBC, which is my current work around.

Thanks, Mark

p.s.    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
running in WebLogic Server 10.3.0

p.p.s   If I called Query.executeUpdate outside of an EntityTransaction it
throws an error telling me I require a Transaction, which makes sense.

Mark Millman | Mizar, LLC | mark.millman@xxxxxxxxx | www.mizar.com | (360)
945-2643
589 S Beach Rd. | Point Roberts, WA 98281