0

I upgraded the version of h2 from 1.4.200 to 2.1.214 and tests fails now because of the use of "SYSDATE" in a liquibase file (we use an Oracle database when the app is deployed).

Content of liquibase file:

<createTable tableName="myTable">
    <column defaultValueComputed="SYSDATE" name="CREATION_DATE" type="TIMESTAMP(6)">
        <constraints nullable="true" />
    </column>
</createTable>

When the liquibase file is loaded to initialize the database for tests, then it fails with this error:

Column "SYSDATE" not found

The executed query is like this:

CREATE TABLE PUBLIC.my_table (..., CREATION_DATE TIMESTAMP(6) DEFAULT SYSDATE,...)

I tried to force the Oracle compatibility mode of H2 like this:

spring:
  datasource:
    url: jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;MODE=Oracle

I saw that h2, in Oracle compatibility mode, should accept "SYSDATE" keyword but I still have the error.

Do you know what I have to do to solve the issue please?

phildeg31
  • 169
  • 1
  • 14
  • `CREATE TABLE PUBLIC.my_table (CREATION_DATE TIMESTAMP(6) DEFAULT SYSDATE);` works in Oracle compatibility mode of H2 2.1.214, so your custom JDBC URL wasn't actually used by unit test. – Evgenij Ryazanov Dec 01 '22 at 10:03
  • I tested to delete one of the active profile that is in the application.yml (that is in the src/test/resources folder) and this profile is not taken into account anymore so the file is well taken into account. I do not understand. I will search for a configuration. – phildeg31 Dec 01 '22 at 10:09
  • Ok it seems that an EmbeddedDatabaseBuilder is used when we have "H2" in active profiles in database configuration to init the DataSource. I have to found how to set the Oracle compatibility mode or to change the way to initialize the DataSource. – phildeg31 Dec 01 '22 at 10:53

1 Answers1

0

I found a solution for this error.

For local tests, a configuration of the DataSource was done like this:

@Bean
@Profile(SpringProfiles.H2)
public DataSource dataSource() {
    return new EmbeddedDatabaseBuilder()//
            .setType(EmbeddedDatabaseType.H2) //
            .setName("local") //
            .build();

}

I had to change it by adding the mode in the name:

@Bean
@Profile(SpringProfiles.H2)
public DataSource dataSource() {
    return new EmbeddedDatabaseBuilder()//
            .setType(EmbeddedDatabaseType.H2) //
            .setName("local;MODE=Oracle") //
            .build();

}

I found the solution in this link: Does Spring embedded database support different SQL dialects?

phildeg31
  • 169
  • 1
  • 14