I'm trying to access an access file from a web application using the jdbc ucanaccess 4.0.3 driver. The tests when building the used jar are executed without problem.
Application Context in jar file:
<context:annotation-config />
<context:component-scan base-package="cat.dinet.lblchem.sec" />
<bean id="persistenceExceptionTranslationPostProcessor" class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />
<context:property-placeholder location="classpath:db-lblchem-sec.properties" />
<bean id="dataSourceBase" class="org.apache.tomcat.jdbc.pool.DataSource">
<property name="driverClassName" value="${jdbc.driverClassName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>
<bean id="sessionFactoryBase" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSourceBase" />
<property name="packagesToScan" value="cat.dinet.lblchem.sec.model" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
<prop key="hibernate.format_sql">${hibernate.format_sql}</prop>
<prop key="hibernate.connection.autocommit">${hibernate.connection.autocommit}</prop>
<prop key="hibernate.cache.use_second_level_cache">false</prop>
<prop key="hibernate.cache.use_query_cache">true</prop>
<prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
<prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</prop>
</props>
</property>
<qualifier value="sessionFactoryBase" />
</bean>
<bean id="transactionManagerBase" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactoryBase" />
<qualifier value="transactionManagerBase" />
</bean>
JDBC Connection prperties:
jdbc.driverClassName = net.ucanaccess.jdbc.UcanaccessDriver jdbc.url = jdbc: ucanaccess: // E: /lblchem32/data/lblchem-sec.mdb jdbc.username = admin jdbc.password =
hibernate.dialect = com.hxtt.support.hibernate.HxttAccessDialect hibernate.show_sql = true hibernate.format_sql = true hibernate.default_schema = public hibernate.hbm2ddl.auto = validate hibernate.connection.autocommit = false
When building the jar with Maven:
Hibernate:
create table HT_dn_sec_user (usr_id int not null, hib_sess_id CHAR(36) null)
Hibernate:
create table HT_dn_sec_user_role (uro_id int not null, hib_sess_id CHAR(36) null)
Hibernate:
create table HT_dn_sec_right (rig_id int not null, hib_sess_id CHAR(36) null)
Hibernate:
create table HT_dn_sec_role_right (rri_id int not null, hib_sess_id CHAR(36) null)
Hibernate:
create table HT_dn_sec_role (rol_id int not null, hib_sess_id CHAR(36) null)
....
I have also built several junit tests:
@Test
public void getUserByLoginname() {
DnSecUser obj = this.getDnSecUserDAO().getUserByLoginname("admin");
System.out.println("admin has id=" + obj.getId());
Assert.assertNotNull(obj);
}
with the following output:
Hibernate:
select
dnsecuser0_.usr_id as usr_id1_6_,
dnsecuser0_.usr_firstname as usr_fir19_6_,
dnsecuser0_.usr_lastname as usr_las21_6_,
dnsecuser0_.usr_loginname as usr_log22_6_,
dnsecuser0_.usr_password as usr_pas24_6_,
....
dnsecuser0_.version as version29_6_
from
dn_sec_user dnsecuser0_
where
dnsecuser0_.usr_loginname=?
admin has id=3
In the web app I there are a context.xml
<Context>
....
<Resource name="jdbc/lblchem-sec" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
driverClassName="net.ucanaccess.jdbc.UcanaccessDriver"
url="jdbc:ucanaccess://E:/lblchem32/data/lblchem-sec.mdb"
username="admin" password=""
initialSize="5" maxActive="20" maxIdle="12" minIdle="8" timeBetweenEvictionRunsMillis="34000"
minEvictableIdleTimeMillis="55000" validationQuery="SELECT 1" validationInterval="34000" testOnBorrow="true"
removeAbandoned="true" removeAbandonedTimeout="55" defaultAutoCommit="false" />
</Context>
On running Tomcat:
2018-02-23 11: 54: 46,906 DEBUG [main] DatabaseImpl M [readSystemCatalog] - Finished reading system catalog. Tables: [HT_dn_sec_right, HT_dn_sec_role, HT_dn_sec_role_right, HT_dn_sec_user, HT_dn_sec_user_role, ..., dn_sec_right, dn_sec_role, dn_sec_role_right, dn_sec_user, dn_sec_user_role, ...] (Db = lblchem-sec.mdb)
Hibernate: create table HT_dn_sec_user (usr_id int not null, hib_sess_id CHAR (36) null) Hibernate: create table HT_dn_sec_user_role (uro_id int not null, hib_sess_id CHAR (36) null) Hibernate: create table HT_dn_sec_right (rig_id int not null, hib_sess_id CHAR (36) null) Hibernate: create table HT_dn_sec_role_right (rri_id int not null, hib_sess_id CHAR (36) null) Hibernate: create table HT_dn_sec_role (role_id int not null, hib_sess_id CHAR (36) null)
But when I try to login login:
> Hibernate:
> select
> dnsecuser0_.usr_id as usr_id1_6_,
> dnsecuser0_.usr_firstname as usr_fir19_6_,
> dnsecuser0_.usr_lastname as usr_las21_6_,
> dnsecuser0_.usr_loginname as usr_log22_6_,
> dnsecuser0_.usr_password as usr_pas24_6_,
> ....
> dnsecuser0_.version as version29_6_
> from
> dn_sec_user dnsecuser0_
> where
> dnsecuser0_.usr_loginname=? 2018-02-23 11:55:25,864 ERROR [http-nio-8080-exec-5] SqlExceptionHelper M[logExceptions] -
> UCAExc:::4.0.3 user lacks privilege or object not found: DN_SEC_USER
I have tried changing the format of Access to accdb, changing parameters of the connection (memory = false; keepMirror = ...) but with the same results.
Any help will be appreciated.