Define The DatabaseSpecificStatement for ..new DBMS (A 5.11)

udoderk — Tue, 26 Mar 2013 17:10:36 GMT

Hi all,currenlty i try to define the new DBMS Support for the Activiti 5.11 (As example i try to define the MS Access Support)Firstly, i have patched the getDefaultDatabaseTypeMappings() method of org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl (activiti-engine.jar) by adding the data

Re: Define The DatabaseSpecificStatement for ..new DBMS (A 5.11)

frederikherema1 — Wed, 27 Mar 2013 12:19:16 GMT

How were such statements determined?
What should I set for Ms Access (or other DBMS, that is not currenty specified in code) ?

In order to page every query on a database-level, it's needed for some databases to include special fragments in the SQL. In all queries that support paging, the fragments to include "before" and after the full query. Depending on the database used, a piece of SQL will be added before or after, based on the mappings. For example, H2 uses this:


LIMIT #{maxResults} OFFSET #{firstResult}

You should create an alternative SQL-fragment that tells access to limit the number of results, can't help you with that…

Re: Define The DatabaseSpecificStatement for ..new DBMS (A 5.11)

udoderk — Wed, 27 Mar 2013 16:38:56 GMT

Thanks for that infos!
After trying to support ms access, i know, that
db.properties must be changed like those


db=msaccess

jdbc.driver=sun.jdbc.odbc.JdbcOdbcDriver


jdbc.url=jdbc:smileysurprised:dbc:smileyvery-happy:RIVER={Microsoft Access Driver (*.mdb)};DBQ=X:\\MyDB\\testdb.mdb
jdbc.username=""
jdbc.password=""

Then the ddl files

must be specified under org.activiti.db.create namespace.
That work is realy hard for me, because not all features /sql datatypes, that i know from MS SQL/DB2/Oracle, are supported by Ms Accsess (key words: "Blob"/"numeric(,)//tinyint)
see also
CREATE INDEX Statement (Microsoft Access SQL
Equivalent ANSI SQL Data Types
Then org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl.getDefaultDatabaseTypeMappings() must be adjusted by adding
databaseTypeMappings.setProperty("ACCESS","msaccess");
Then specific statements for MS Access into org.activiti.engine.impl.db.DbSqlSessionFactory must be registered.
Thus, if i started Activi Explorer with patched engine, the tables were created.

But the following exception was thrown:

### Cause: java.sql.SQLException: General error
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95)
at org.activiti.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:301)
at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:292)
at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:287)
at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:274)
at org.activiti.engine.impl.persistence.entity.JobManager.findNextJobsToExecute(JobManager.java:105)
at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:50)
at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:33)
at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:60)
at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:32)
at org.activiti.engine.impl.jobexecutor.AcquireJobsRunnable.run(AcquireJobsRunnable.java:61)
at java.lang.Thread.run(Thread.java:722)
Caused by: java.sql.SQLException: General error
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6985)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7113)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3148)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:215)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:56)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:57)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:141)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:101)
… 16 more

The mybatis job.xml has following not changed code:

<select id="selectNextJobsToExecute" parameterType="org.activiti.engine.impl.db.ListQueryParameterObject" resultMap="jobResultMap">
   ${limitBefore}
    select
     RES.* ${limitBetween}
    from ${prefix}ACT_RU_JOB RES
     LEFT OUTER JOIN ${prefix}ACT_RU_EXECUTION PI ON PI.ID_ = RES.PROCESS_INSTANCE_ID_
    where (RES.RETRIES_ > 0)
      and (RES.DUEDATE_ is null or RES.DUEDATE_ < #{parameter, jdbcType=TIMESTAMP})
      and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ < #{parameter, jdbcType=TIMESTAMP})
   and (
        (RES.EXECUTION_ID_ is null)
     or
     (PI.SUSPENSION_STATE_ = 1)
      )
    ${limitAfter}
</select>

Because the SQL error "General error" is imho nondescriptive and a lot of tricks is used during defining the datatypes for activiti tables, i suspend for the future such ms access investigations

[size=200]========================Update 1;-))========================[/size]
After Setting "before/between"after" and "groupBy" descriptions to emty string, no more "General error" occurred.
But i had the problem with NULL during insert into following table for PASSWORD_ attribute:

create table ACT_ID_INFO (
    ID_ VARCHAR(64),
    REV_ LONG,
    USER_ID_ VARCHAR(64),
    TYPE_ VARCHAR(64),
    KEY_ VARCHAR(255),
    VALUE_ VARCHAR(255),
    PASSWORD_ BINARY,
    PARENT_ID_ VARCHAR(255),
    primary key (ID_)
);

(Originally for instance for DB2 the PASSWORD_ has a Blob type. In MS SQL Server it has an image type. I have understood that this attribute is only leftover from previous "Alfreco"(?) developments. That time there was "Account" possibilty (deprecated org.activiti.engine.impl.identity.Account) for remote login on using "Google" , "Skype" etc. accounts.)

To avoid NULL-problem i declared in org.activiti.engine.impl.cmd.SetUserInfoCmd.SetUserInfoCmd(String, String, String) following values for userPassword and accountPassword

  public SetUserInfoCmd(String userId, String key, String value) {
    this.userId = userId;
    this.type = IdentityInfoEntity.TYPE_USERINFO;
    this.key = key;
    this.value = value;
    this.userPassword="12";
    this.accountPassword="12";
  }

(this values for userPassword and accountPassword will be encrypted as byte array. The encryption was not realized.)

Now i see the Activiti Login Dialog! But if y try to loggin on, the following exception will be thrown:

### The error may exist in org/activiti/db/mapping/entity/Group.xml
### The error may involve org.activiti.engine.impl.persistence.entity.GroupEntity.selectGroupByQueryCriteria-Inline
### The error occurred while setting parameters
### SQL: select RES.* from ACT_ID_GROUP RES inner join ACT_ID_MEMBERSHIP M on RES.ID_ = M.GROUP_ID_ inner join ACT_ID_USER U on M.USER_ID_ = U.ID_ WHERE U.ID_ = ?
### Cause: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'RES.ID_ = M.GROUP_ID_
inner join ACT_ID_USER U on M.USER_ID_ = U.ID_'

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95)
at org.activiti.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:301)
at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:292)
at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:282)
at org.activiti.engine.impl.persistence.entity.GroupManager.findGroupByQueryCriteria(GroupManager.java:64)

[size=200]========================Update 2;-))========================[/size]
Now i found, that mybatis mapper use correlation names without "AS" keyword
Such notation is not supported by Ms Accsess (i have tested with MS Access 10).
I change all mapper, that use such notation.

But the next trouble occurred :twisted: :twisted: :twisted:
Ms Access needs the brackets, if the JOINs will be used in sql statement.

The following notaion from Group.xml is wrong for Ms Access

<sql id="selectGroupByQueryCriteriaSql">
    from ${prefix}ACT_ID_GROUP AS RES
    <if test="userId != null">
      inner join ${prefix}ACT_ID_MEMBERSHIP AS M on RES.ID_ = M.GROUP_ID_
      inner join ${prefix}ACT_ID_USER AS U on M.USER_ID_ = U.ID_
    </if>

The valid for Ms Access notation as follows:

<sql id="selectGroupByQueryCriteriaSql">
    from ${prefix}ACT_ID_GROUP AS RES
    <if test="userId != null">
      inner join [size=150]([/size] ${prefix}ACT_ID_MEMBERSHIP AS M inner join ${prefix}ACT_ID_USER AS U on M.USER_ID_ = U.ID_ [size=150])[/size] on RES.ID_ = M.GROUP_ID_
    </if>

I tested such sql statement in Ms Access directly too:

SELECT count(RES.ID_ ) FROM ACT_ID_GROUP AS RES INNER JOIN (

ACT_ID_MEMBERSHIP AS M INNER JOIN

ACT_ID_USER AS U

ON M.[USER_ID_] = U.[ID_]

)

ON

RES.ID_ = M.GROUP_ID_;

That is, all mybatis sql statements must be adjusted according to needed notation.
Currenlty, the login on fails in other place:

### Error querying database. Cause: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression'count(distinct RES.ID_)'.
### The error may exist in org/activiti/db/mapping/entity/Task.xml
### The error may involve org.activiti.engine.impl.persistence.entity.TaskEntity.selectTaskCountByQueryCriteria-Inline
### The error occurred while setting parameters
### SQL: select count(distinct RES.ID_) from ACT_RU_TASK AS RES WHERE RES.ASSIGNEE_ = ?
### Cause: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'count(distinct RES.ID_)'.
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:59)
at org.activiti.engine.impl.db.DbSqlSession.selectOne(DbSqlSession.java:307)
at org.activiti.engine.impl.persistence.entity.TaskManager.findTaskCountByQueryCriteria(TaskManager.java:113)

[size=200]========================Update 3;-))========================[/size]

The Ms Access not accepts the following sql into Task.xml:

<select id="selectTaskCountByQueryCriteria" parameterType="org.activiti.engine.impl.TaskQueryImpl" resultType="long">
select count(distinct RES.ID_)
<include refid="selectTaskByQueryCriteriaSql"/>
</select>

Such sql is accepted:

<select id="selectTaskCountByQueryCriteria" parameterType="org.activiti.engine.impl.TaskQueryImpl" resultType="long">
select distinct count( RES.ID_)
<include refid="selectTaskByQueryCriteriaSql"/>
</select>

After that fix i can to logged on into Activiti Explorer. I can select the predifiened bpmn process, but if i try to start it (for instance "Fix system failure") the next exception will be thrown:


org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #7 with JdbcType BIGINT . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]optional feature not  implemented.
### The error may involve org.activiti.engine.impl.persistence.entity.HistoricProcessInstanceEntity.insertHistoricProcessInstance-Inline
### The error occurred while setting parameters
### SQL: insert into ACT_HI_PROCINST (         ID_,         PROC_INST_ID_,         BUSINESS_KEY_,         PROC_DEF_ID_,         START_TIME_,         END_TIME_,         DURATION_,         START_USER_ID_,         START_ACT_ID_,         END_ACT_ID_,         SUPER_PROCESS_INSTANCE_ID_,         DELETE_REASON_       ) values (         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?       )
### Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #7 with JdbcType BIGINT . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]optional feature not  implemented.
 at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:147)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:134)
 at org.activiti.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:632)
 at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:459)
 at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:167)
 at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:114)
 at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:69)
 at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
 at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
 at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
 at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:32)
 at org.activiti.engine.impl.RuntimeServiceImpl.startProcessInstanceById(RuntimeServiceImpl.java:67)
 at org.activiti.explorer.ui.process.listener.StartProcessInstanceClickListener.buttonClick(StartProcessInstanceClickListener.java:71)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:601)

topic Re: Define The DatabaseSpecificStatement for ..new DBMS (A 5.11) in Alfresco Archive

Define The DatabaseSpecificStatement for ..new DBMS (A 5.11)

Re: Define The DatabaseSpecificStatement for ..new DBMS (A 5.11)

Re: Define The DatabaseSpecificStatement for ..new DBMS (A 5.11)