[jira] Created: (OPENJPA-1389) Bad DELETE query generation with HsqlDB

[Fabien Charlet (JIRA)]

Bad DELETE query generation with HsqlDB
---------------------------------------

                 Key: OPENJPA-1389
                 URL: https://issues.apache.org/jira/browse/OPENJPA-1389
             Project: OpenJPA
          Issue Type: Bug
    Affects Versions: 2.0.0-M3, 1.2.1
         Environment: Windows XP, Java 1.6, HsqlDB 1.9.0-rc4, OpenJPA 1.2.1 and 
2.0.0-M3
            Reporter: Fabien Charlet


I use an object Log, mapped with a table LOG in HsqlDB.

When I try to execute a "DELETE FROM Log" query, I have an error
with the SQL code generated :

782  openjpa  TRACE  [main] openjpa.Query - Executing query: [DELETE
FROM Log l WHERE l.application = :application AND l.logDate < :aDate]
with parameters: {application=Isis, aDate=Tue Nov 17 10:12:26 CET
2009}
[KO] SimpleLogDAO.purgeLogs( applicat...@45d8241 Isis, d...@16a1227
Tue Nov 17 10:12:26 CET 2009 ) returned ReportingSQLException:
unexpected token: T0 {DELETE FROM LOG t0 WHERE (t0.APPLICATION_ID = ?
AND t0.LOG_DATE < ?)} [code=-5581, state=42581]
3782  openjpa  TRACE  [main] openjpa.jdbc.JDBC - <t 914454, conn
12437155> [0 ms] rollback
3782  openjpa  TRACE  [main] openjpa.jdbc.JDBC - <t 914454, conn
12437155> [0 ms] close

It seems that this HQL query generate "DELETE FROM LOG t0" but HsqlDB
doesn't suport a token after the table name in the delete statement.

Did I miss something ?

I use this mapping :

@Entity
@Table(name="LOG")
public class Log implements RestrictedObject, ICrudEntity {

  @Id
  @GeneratedValue(strategy=GenerationType.IDENTITY)
  private long id;

  @Column(name="VERSION")
  @Version
  private int version;

  @Column(name="USER")
  private String user;

  @OneToOne(fetch=FetchType.LAZY)
  @JoinColumn(name="APPLICATION_ID", referencedColumnName="ID")
  private Application application;

  @Column(name="APPLICATION_VERSION")
  private String applicationVersion;

  @Column(name="NETWORK_INFORMATION")
  private String networkInformation;

  @Column(name="MESSAGE")

  private String message;

  @Column(name="LOG_DATE")
  private Date logDate;

  @OneToOne(fetch=FetchType.LAZY)
  @JoinColumn(name="CATEGORY_ID", referencedColumnName="ID")
  private Category category;

  @Transient
  private List<File> datas = new ArrayList<File>();

....
}

And I create the table in HsqlDB with this query :

statement.executeUpdate("CREATE TABLE LOG ( ID BIGINT GENERATED BY
DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY, " +
        "VERSION INT," +
        "USER VARCHAR(20)," +
        "APPLICATION_ID BIGINT," +
        "APPLICATION_VERSION VARCHAR(20)," +
        "NETWORK_INFORMATION VARCHAR(40)," +
        "MESSAGE VARCHAR(1000)," +
        "LOG_DATE TIMESTAMP," +
        "CATEGORY_ID BIGINT)"); 

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.