Issues with @Id @GeneratedValue for DB2 using Play Framework

[Diego Gallo]

I have a play application everything is working fine with the H2 database.

In H2 DB, as far as I understood, the default strategy for auto-generated ids is to use sequence, and the sequences are created and used correctly, with nextval being called and the insert sql query specifies my id field with the value returned from nextval, as expected ;-)

Pointing to DB2, first of all I noticed that the default behavior for DB2 is to use IDENTITY. I was having issues when using the GENERATED ALWAYS AS IDENTITY (my id was not being set after the insert call, even though it should as far as I understood - DB2 v10.5 and jdbc 4.0, so support for RETURN_GENERATED_KEYS exists).

Since I didn't manage to understand/solve that issue, I was trying to use SEQUENCE on DB2 instead of IDENTITY (since DB2 supports both). So I changed my DB creating the sequences and removing the generated always as identity from the tables. Also, I included the annotation accordingly in my models:

@Entity

public class Application extends Model {

    @Id

    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="application_seq")

    public long id;

...

Nevertheless, the annotation appears to be ignored, and the insert does not contain the id, and the nextval is never called (when pointing to DB2... when pointing to H2 it does).

Anyone knows what can be causing my issues? Anyone knows if play framework is ignoring this annotation in favor of the default for each DB based on the driver or anything like that? Any other ideas?

Anyone using play framework with DB2? :-)

Thanks!

[Rob Bygrave]

If you can turn on a debugger ... then you can put a breakpoint in around:

com.avaje.ebeaninternal.server.deploy.parse.AnnotationFields.readGenValue()

Specifically you want it to go into the sequence specific part:

} else if (strategy == GenerationType.SEQUENCE) {

  ...

>> Anyone knows what can be causing my issues?

To me this is almost certainly an Ebean issue and not a Play issue.  Unfortunately I don't have an instance of DB2 to try this out on myself at the moment.

Cheers, Rob.

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Diego Gallo]

Hi Rob,

Thanks for the answer. I put a breakpoint where you suggested and the annotation is being read properly, i.e., I can see the

descriptor.setIdGeneratorName(genName);

being called with the correct genName.

Where this would be used to build the sql statement?

Additionally, in the case of IDENTITY being used, where the RETURN_GENERATED_KEYS and getGeneratedKeys() are set/called before/after the insert happens?

Thanks a lot for the help!

Best,

Diego

[Diego Gallo]

Hi Rob & all,

Digging a bit further, I believe that the root cause of the issue is that, for some reason, the DB platform is not being identified properly.

Using breakpoints I could see that the meta.supportsGetGeneratedKeys() is returning false.

Also, I could see that dbIdentity.isSupportsSequence() is also returning false.

Through the logs I can see this:

[info] c.a.e.s.c.DefaultServerFactory - DatabasePlatform name:default platform:generic

And when I try to "force" sequence to be used via annotations, I can see this:

[info] c.a.e.s.d.BeanDescriptorManager - Explicit sequence on models.Application but not supported by DB Platform - ignored

So my guess is that for some reason it is not identifying that my DB is DB2 and thus not using the DB2Platform class, that would setup the flags correctly:

    this.dbIdentity.setSupportsGetGeneratedKeys(true);
    this.dbIdentity.setSupportsSequence(true);

I'm trying to find why this is happening, but until now the only thing I found is that according to the ServerConfig.setDBPlatformName javadoc, "Values are oracle, h2, postgres, mysql, mssqlserver2005.", and DB2 is not on that list :-/

How should I specify that DB2 is the platform I'm using to Ebean?

ps. When I'm using the H2 DB on play, the platform is identified correctly:

[info] c.a.e.s.c.DefaultServerFactory - DatabasePlatform name:default platform:h2

Thanks and best regards,

Diego

[Rob Bygrave]

There is the DatabasePlatformFactory.

 /**

   * Create the appropriate database specific platform.

   */

  public DatabasePlatform create(ServerConfig serverConfig) {

    try {

      if (serverConfig.getDatabasePlatformName() != null) {

        // choose based on dbName

        return byDatabaseName(serverConfig.getDatabasePlatformName());

      }

      if (serverConfig.getDataSourceConfig().isOffline()) {

        String m = "You must specify a DatabasePlatformName when you are offline";

        throw new PersistenceException(m);

      }

      // guess using meta data from driver

      return byDataSource(serverConfig.getDataSource());

    } catch (Exception ex) {

      throw new PersistenceException(ex);

    }

  }

... so for example set ebean.databasePlatformName=db2 

or programmatically serverConfig.setDatabasePlatformName("db2")

If you look in DatabasePlatformFactory you will see it also tries to automatically detect the database platform based on the jdbc meta data (from the jdbc driver).  I think it is reasonable to say that isn't working for DB2 - at least for your jdbc driver.

Cheers, Rob.

[Diego Gallo]

Thanks Rob.

I didn't manage to set that through play-framework configuration files, though I found out that DatabasePlatformFactory.byDatabaseMeta should do the work, though the dbProductName is coming as 

"DB2/LINUXX8664" in my case, and the comparison fails because it is case sensitive:

else if (dbProductName.contains("db2")) {
      return new DB2Platform();
}

And that is why it is ending up in the default case as a "generic database".

Thanks for the help once again.

Best,

Diego

[Diego Gallo]

Hummm...

Just found out that I can configure additional properties of ebeans simple creating the conf/ebean.properties file. Then I added the ebean.databasePlatformName=db2 and hit another bug :-(

But this time I just found out that the bug is already fixed on the newest version of Ebean, but on the 3.2.2 version (the one play 2.2.2 uses) there was no

if (dbName.equals("db2")) {
      return new DB2Platform();
}

in the DatabasePlatformFactory.byDatabaseName() method :-(

so now I got a java.lang.RuntimeException: database platform db2 is not known?

Will have to find out how to replace the lib in the play framework, hoping ebean is backward compatible :-)

Cheers,

Diego