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)

Pekoe

@editors: thanks for destroying the persistence.xml -.- – Pekoe

Answer 1

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

Batholomew

This does not seem to work for oracle as OraclePlattform defines a storedProcedureTerminationToke of "" – Darceldarcey

Answer 2

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.

Unsay

Hi, setting the "eclipselink.target-database" property for tests did not help. i am using postgreSQL and did not specified the "eclipselink.target-database" property. jpa should set this value automatically by using the metadata that JDBC provides to determine the target database. – Pekoe

Unfortunately, <property name="eclipselink.target-database" value="Oracle"/> didn't help... – Mercator

Saved my day! What you have to do is create a subclass of OraclePlatform, set the protected variable storedProcedureTerminationToken to ;, and use its qualified name for the property eclipselink.target-database. – Darceldarcey

Answer 3

If it's a one off, replace every "\n\n" with ";\n\n". Wouldn't recommend this as a permanent solution though...

Internationalism

the tables above are just examples. the database will grow in projects with many users. so, its impractical to replace the linebreaks manually – Pekoe