SQLSyntaxErrorException on creating tables in Apache Derby using JPA

Question

I'm using Eclipselink as JPA provider and Embedded Apache Derby database. When I run the project, I get exception as follows:

Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "NOT" at line 1, column 31.

Error Code: -1

Call: CREATE TABLE ITEM (ID VARCHAR NOT NULL, CODE VARCHAR, EXCLUSIVETAX NUMBER(10,5), NAME VARCHAR, UNITPRICE NUMBER(10,5), PRIMARY KEY (ID))

Query: DataModifyQuery(sql="CREATE TABLE ITEM (ID VARCHAR NOT NULL, CODE VARCHAR, EXCLUSIVETAX NUMBER(10,5), NAME VARCHAR, UNITPRICE NUMBER(10,5), PRIMARY KEY (ID))")

[EL Warning]: 2013-03-06 18:04:41.827--ServerSession(1650627452)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException

Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "(" at line 1, column 71.

Error Code: -1

Call: CREATE TABLE SEQUENCE (SEQ_NAME VARCHAR(50) NOT NULL, SEQ_COUNT NUMBER(19), PRIMARY KEY (SEQ_NAME))

Query: DataModifyQuery(sql="CREATE TABLE SEQUENCE (SEQ_NAME VARCHAR(50) NOT NULL, SEQ_COUNT NUMBER(19), PRIMARY KEY (SEQ_NAME))")

[EL Info]: 2013-03-06 18:04:41.856--ServerSession(1650627452)--Communication failure detected when attempting to perform read query outside of a transaction. Attempting to retry query. Error was: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException

The persistence.xml is like this:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
  <persistence-unit name="StockKeeperPU" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <class>com.mycompany.entities.stock.Item</class>
    <properties>
      <property name="javax.persistence.jdbc.url" value="jdbc:derby:sampledb;create=true"/>
      <property name="javax.persistence.jdbc.password" value="app"/>
      <property name="javax.persistence.jdbc.driver" value="org.apache.derby.jdbc.EmbeddedDriver"/>
      <property name="javax.persistence.jdbc.user" value="app"/>
      <property name="eclipselink.ddl-generation" value="create-tables"/>
    </properties>
  </persistence-unit>
</persistence>

What is the problem here? Is NOT not allowed in that statement? If not allowed, why the query is framed so?

Why do you have property eclipselink.platform.claas.name value="" in there? Remove it to allow eclipselink to auto detect the database platform, or specify the derby platform class. — Chris, Mar 07 '13 at 00:27
@Chris sorry it was copied by mistake. Actually that was not there. updated question. — Naveed S, Mar 07 '13 at 04:35

score 3 · Accepted Answer · answered Mar 14 '13 at 06:20

This occurs because Eclipse Link generate query with out knowing the database, so they generate a query common to most DBMS. But Derby does not have a NUMBER data type as @Bryan said.

To avoid this we need to tell which Database we are using by adding this in persistance.xml.

  <property name="eclipselink.target-database" value="Derby"/>

also add platform class name as

<property name="eclipselink.platform.class.name" value="org.eclipse.persistence.platform.database.DerbyPlatform"/>

score 1 · Answer 2 · answered Mar 06 '13 at 14:42

1

Derby does not have a NUMBER data type. Here are Derby's data types: http://db.apache.org/derby/docs/10.9/ref/crefsqlj31068.html#crefsqlj31068

Perhaps what you want is DECIMAL instead of NUMBER.

answered Mar 06 '13 at 14:42

Bryan Pendleton

16,128
3
32
56

SQLSyntaxErrorException on creating tables in Apache Derby using JPA

2 Answers2

Linked