CODEWITHSUNDEEP
jakarta-eejpa
U-L
U-L

Reputation: 2681

SQL error with javadb

Does anybody know what this means:

Caused by: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "END" at line 1, column 121.
    at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
    at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
    at com.sun.gjc.spi.base.ConnectionHolder.prepareStatement(ConnectionHolder.java:502)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1474)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1423)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:697)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:585)
    ... 70 more
Caused by: org.apache.derby.client.am.SqlException: Syntax error: Encountered "END" at line 1, column 121.
    at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
    at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
    at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
    at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
    at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source)
    ... 76 more

I am using netbeans. Created entity class. Now I don't know how to create table. So I used "drop-and-create-tables" in the persistence.xml file. Now using a servlet to populate the database and it fails with that error. Nothing on google on that error.

Here is my persistence file ...

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
  <persistence-unit name="com.uc.ermis_BookUCReadyTalk_war_1.0-SNAPSHOTPU" transaction-type="JTA">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>jdbc/sample</jta-data-source>
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <properties>
      <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/>
    </properties>
  </persistence-unit>
</persistence>

EDIT - adding code that persists the data - I generated these classes from the Entity class using netbeans. - The table does not exist in the database. I changed the persistence.xml to value="create-tables"

@Stateless
public class EventFacade extends AbstractFacade<Event> {
    @PersistenceContext(unitName = "com.uc.ermis_BookUCReadyTalk_war_1.0-SNAPSHOTPU")
    private EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public EventFacade() {
        super(Event.class);
    }

}

public abstract class AbstractFacade<T> {
    private Class<T> entityClass;

    public AbstractFacade(Class<T> entityClass) {
        this.entityClass = entityClass;
    }

    protected abstract EntityManager getEntityManager();

    public void create(T entity) {
        getEntityManager().persist(entity);
    }

    public void edit(T entity) {
        getEntityManager().merge(entity);
    }

    public void remove(T entity) {
        getEntityManager().remove(getEntityManager().merge(entity));
    }

    public T find(Object id) {
        return getEntityManager().find(entityClass, id);
    }

    public List<T> findAll() {
        javax.persistence.criteria.CriteriaQuery cq = getEntityManager().getCriteriaBuilder().createQuery();
        cq.select(cq.from(entityClass));
        return getEntityManager().createQuery(cq).getResultList();
    }

    public List<T> findRange(int[] range) {
        javax.persistence.criteria.CriteriaQuery cq = getEntityManager().getCriteriaBuilder().createQuery();
        cq.select(cq.from(entityClass));
        javax.persistence.Query q = getEntityManager().createQuery(cq);
        q.setMaxResults(range[1] - range[0]);
        q.setFirstResult(range[0]);
        return q.getResultList();
    }

    public int count() {
        javax.persistence.criteria.CriteriaQuery cq = getEntityManager().getCriteriaBuilder().createQuery();
        javax.persistence.criteria.Root<T> rt = cq.from(entityClass);
        cq.select(getEntityManager().getCriteriaBuilder().count(rt));
        javax.persistence.Query q = getEntityManager().createQuery(cq);
        return ((Long) q.getSingleResult()).intValue();
    }

}

Model

@Entity
@XmlRootElement
public class Event implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private String id;
    private String title;
    private String description;
    private boolean allDay;
    @Temporal(TemporalType.TIMESTAMP)
    private Date start;
    @Temporal(TemporalType.TIMESTAMP)
    private Date end;
    private String url;
    private String className;
    private boolean editable;
    private boolean startEditable;
    private boolean durationEditable;
    private String color;
    private String backgroundColor;
    private String borderColor;
    private String textColor;
}

Servlet that is trying to populate the db

@EJB
    EventFacade eventFacade;

private void recreateDb() {
        Event ev = new Event();
        ev.setTitle("Sample item");
        ev.setDescription("Sample description");
        ev.setAllDay(true);
        eventFacade.create(ev);
    }

Adding Complete Stack trace

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "END" at line 1, column 121.
Error Code: -1
Call: INSERT INTO EVENT (ID, ALLDAY, BACKGROUNDCOLOR, BORDERCOLOR, CLASSNAME, COLOR, DESCRIPTION, DURATIONEDITABLE, EDITABLE, END, START, STARTEDITABLE, TEXTCOLOR, TITLE, URL) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    bind => [15 parameters bound]
Query: InsertObjectQuery(com.uc.ermis.bookucreadytalk.model.Event[ id=1 ])
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1717)
    at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:253)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:342)
    at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:162)
    at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:177)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:472)
    at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80)
    at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:287)
    at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:844)
    at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:743)
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2871)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1516)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1498)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1449)
    at org.eclipse.persistence.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:224)
    at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:123)
    at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:3799)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1415)
    at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:636)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1505)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.issueSQLbeforeCompletion(UnitOfWorkImpl.java:3143)
    at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.issueSQLbeforeCompletion(RepeatableWriteUnitOfWork.java:346)
    at org.eclipse.persistence.transaction.AbstractSynchronizationListener.beforeCompletion(AbstractSynchronizationListener.java:157)
    at org.eclipse.persistence.transaction.JTASynchronizationListener.beforeCompletion(JTASynchronizationListener.java:68)
    at com.sun.enterprise.transaction.JavaEETransactionImpl.commit(JavaEETransactionImpl.java:435)
    ... 38 more

Upvotes: 0

Views: 1263

Answers (2)

TomTom
TomTom

Reputation: 41

I got the same problem as yours. Exactly the same, even the scenario: using primefaces schedule for events... lol.

I know the reason now: "end" itself is a key word in SQL, change it to endTime. you are welcome.

Upvotes: 1

Zaw Than oo
Zaw Than oo

Reputation: 9935

It is possible, you are using reserved word (END) of your database. Try to use @Column annotation.

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "START_DATE")
private Date start;
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "END_DATE")
private Date end;

SQL Reserved Word

Upvotes: 1

Related Questions