I am trying to develop an app for exercise reasons. I am using MSAccess 2010 as the database with UCanAccess (3.06) as the driver and the EclipseLink 2.1 as the entity framework.
I am stuck in adding new records to the database. Here the error code:
Internal Exception: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.6 user lacks privilege or object not found: IDENTITY_VAL_LOCAL
Error Code: -5501
Call: SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
Query: ValueReadQuery(name="SEQ_GEN_IDENTITY" sql="SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1")
It seems to me that the autogenerate of the id fails. The entity class was generated vie Netbeans and looks like this:
@Transient
private PropertyChangeSupport changeSupport = new PropertyChangeSupport(this);
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "ID")
private Integer id;
By default, EclipseLink tries to automatically detect the underlying database and generate SQL statements using the appropriate SQL dialect. That apparently isn't working for you because the SQL statement to retrieve the last created identity value is not recognized by UCanAccess .
You could try adding a target-database directive to your EclipseLink configuration specifying SQLServer in an attempt to get a working SQL statement (SELECT @@IDENTITY) to retrieve the last created ID value. However, bear in mind that there are significant differences between T-SQL and Access SQL so you will probably continue to encounter other compatibility issues between EclipseLink and UCanAccess.