Problems with generating sql via eclipseLink - missing separator

Question

i'am using eclipseLink with jpa. in my persistence.xml, i defined to generate a create.sql file. the file will be generated, but with missing ';'-separators for each sql statement.

is there a possibility to define a separator in the persistence.xml or in some other way?

Example persistence.xml:

<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>

<exclude-unlisted-classes>false</exclude-unlisted-classes>

<class>de.company.project.models.User</class>

<properties>
      <property name="eclipselink.ddl-generation" value="drop-and-create-tables" />
      <property name="eclipselink.application-location" value="/sql" />
      <property name="eclipselink.create-ddl-jdbc-file-name" value="create.sql"/>
      <property name="eclipselink.drop-ddl-jdbc-file-name" value="drop.sql"/>
      <property name="eclipselink.ddl-generation.output-mode" value="sql-script"/>
</properties>

Example generated sql file:

CREATE TABLE app_user (
ID INTEGER NOT NULL, 
last_name VARCHAR(50) NOT NULL,         
username    VARCHAR(15) NOT NULL, 
user_password VARCHAR(50) NOT NULL, 
first_name  VARCHAR(50) NOT NULL, 
PRIMARY KEY (ID))

CREATE TABLE SEQUENCE (
 SEQ_NAME VARCHAR(50) NOT NULL,
 SEQ_COUNT DECIMAL(38), 
 PRIMARY  KEY (SEQ_NAME))

INSERT INTO SEQUENCE(SEQ_NAME, SEQ_COUNT) 
values ('SEQ_GEN_TABLE', 0)

Answer 1

The token used to separate the statements depends on the DatabasePlatform being used. I assume you are using Oracle, as from the code it seems to be the only one that does not use a separator, although I'm not sure why.

What tool are you using the execute the script? Seems like a bug that ";" is not being used for Oracle, please log this bu in EclipseLink and vote for it.

To workaround the issue create your own OraclePlatform subclass and override,

getStoredProcedureTerminationToken() {
  return ";"
}

(please include in the bug that a different method should be used for DDL, not the StoredProcedureTerminationToken.

You can set your platform using the "eclipselink.target-database" property.

Answer 2

I encountered this problem and solved it by setting a property in persistence.xml.

Since Eclipselink 2.6, there is a property "eclipselink.ddlgen-terminate-statements" that when set to true, a delimiter will be appended to each statement.

I think I was using H2 database at that time.

For more details, see http://www.eclipse.org/eclipselink/api/2.6/org/eclipse/persistence/config/PersistenceUnitProperties.html