topic SqlSyntax Error in Activiti 5.18 in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/sqlsyntax-error-in-activiti-5-18/m-p/228533#M181663 <HTML><HEAD></HEAD><BODY><SPAN>Hi,</SPAN><BR /><SPAN>&nbsp;&nbsp; I recently use HistoryService to get variables of a Execution, with following code</SPAN><BR /><BR /><SPAN>&nbsp; </SPAN><PRE class="language-none line-numbers"><CODE><BR />&nbsp;&nbsp;&nbsp;&nbsp; final List&lt;HistoricVariableInstance&gt; varlist =<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; historyService.createHistoricVariableInstanceQuery().processInstanceId(processInstanceId)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .executionId(executionId).list();<BR />&nbsp; <SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><BR /><SPAN>&nbsp; and I got SQL Syntax Error,</SPAN><BR /><BR /><SPAN>&nbsp; </SPAN><PRE class="language-none line-numbers"><CODE><BR />&nbsp;&nbsp;&nbsp; Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT RES.* <BR />&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp;&nbsp;&nbsp; FROM ACT_HI_VARINST RES<BR />&nbsp;&nbsp;&nbsp;&nbsp; WHERE RES.PROC_INST_ID_ = ?<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RES</CODE><LI>.EXECUTION_ID_ = ?</LI>&nbsp;&nbsp; <BR />&nbsp;&nbsp;&nbsp;&nbsp; ORDER BY RES.ID_ ASC <BR />&nbsp;&nbsp;&nbsp; LIMIT ? OFFSET ? "; SQL statement:<BR />select RES.* <BR />&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp;&nbsp;&nbsp; from ACT_HI_VARINST RES<BR />&nbsp;&nbsp;&nbsp;&nbsp; WHERE RES.PROC_INST_ID_ = ?<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RES.EXECUTION_ID_ = ? <BR />&nbsp;&nbsp; <BR />&nbsp;&nbsp;&nbsp;&nbsp; order by RES.ID_ asc <BR />&nbsp;&nbsp;&nbsp; LIMIT ? OFFSET ? [42000-181]<BR />&nbsp;&nbsp;&nbsp;at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)<BR />&nbsp;&nbsp;&nbsp;at org.h2.message.DbException.get(DbException.java:179)<BR />&nbsp;&nbsp;&nbsp;at org.h2.message.DbException.get(DbException.java:155)<BR />&nbsp;&nbsp;&nbsp;at org.h2.message.DbException.getSyntaxError(DbException.java:191)<BR />&nbsp;&nbsp;&nbsp;at org.h2.command.Parser.getSyntaxError(Parser.java:518)<BR />&nbsp;&nbsp;&nbsp;at org.h2.command.Parser.prepareCommand(Parser.java:245)<BR />&nbsp;&nbsp;&nbsp;at org.h2.engine.Session.prepareLocal(Session.java:446)<BR />&nbsp;&nbsp;&nbsp;at org.h2.engine.Session.prepareCommand(Session.java:388)<BR />&nbsp;&nbsp;&nbsp;at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1189)<BR />&nbsp;&nbsp;&nbsp;at org.h2.jdbc.JdbcPreparedStatement.&lt;init&gt;(JdbcPreparedStatement.java:72)<BR />&nbsp;&nbsp;&nbsp;at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:277)<BR />&nbsp;&nbsp;&nbsp;at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)<BR />&nbsp;&nbsp;&nbsp;at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)<BR />&nbsp;&nbsp;&nbsp;at java.lang.reflect.Method.invoke(Method.java:606)<BR />&nbsp;&nbsp;&nbsp;at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:239)<BR />&nbsp;&nbsp;&nbsp;at com.sun.proxy.$Proxy6.prepareStatement(Unknown Source)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:75)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:85)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:57)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:73)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:59)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)<BR />&nbsp;&nbsp;&nbsp;… 17 more<BR />&nbsp; <SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></PRE><BR /><BR /><SPAN>&nbsp; as exception described, the Select statement missing a 'AND' between </SPAN><PRE class="language-none line-numbers"><CODE> RES.PROC_INST_ID_<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN>&nbsp; and </SPAN><PRE class="language-none line-numbers"><CODE>RES.EXECUTION_ID_ <SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN>.</SPAN><BR /><SPAN>&nbsp; </SPAN><BR /><SPAN>&nbsp; and Sqlmap also don't have 'and'</SPAN><BR /><SPAN>&nbsp; </SPAN><BR /><PRE class="language-none line-numbers"><CODE><BR />&lt;sql id="selectHistoricVariableInstanceByQueryCriteriaSql"&gt;<BR />&nbsp;&nbsp;&nbsp; from ${prefix}ACT_HI_VARINST RES<BR />&nbsp;&nbsp;&nbsp; &lt;where&gt;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;if test="id != null"&gt;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RES.ID_ = #{id}<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/if&gt;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;if test="processInstanceId != null"&gt;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RES.PROC_INST_ID_ = #{processInstanceId}<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/if&gt;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;if test="executionId != null"&gt;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RES.EXECUTION_ID_ = #{executionId}<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/if&gt;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;if test="taskId != null"&gt;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and RES.TASK_ID_ = #{taskId}<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/if&gt;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;if test="excludeTaskRelated"&gt;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and RES.TASK_ID_ is NULL<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/if&gt;<BR /><BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><BR /><SPAN>does this mean my usage of HistoryService is incorrect? </SPAN><BR /><SPAN>only one of processInstancdId and executionId is required?</SPAN><BR /><SPAN>what's difference between processInstance and execution? seems them are the same in most case</SPAN><BR /><BR /><SPAN>=========</SPAN><BR /><SPAN>My Activiti Version: 5.18.0</SPAN><BR /><SPAN>MyBatis Version: 3.2.5</SPAN><BR /></BODY></HTML> Wed, 06 Jan 2016 06:51:34 GMT gangchu 2016-01-06T06:51:34Z SqlSyntax Error in Activiti 5.18 https://connect.hyland.com/t5/alfresco-archive/sqlsyntax-error-in-activiti-5-18/m-p/228533#M181663 Hi,&nbsp;&nbsp; I recently use HistoryService to get variables of a Execution, with following code&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; final List&lt;HistoricVariableInstance&gt; varlist =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; historyService.createHistoricVariableInstanceQuery().processInstanceId(processInstanceId)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .executionId(exe Wed, 06 Jan 2016 06:51:34 GMT https://connect.hyland.com/t5/alfresco-archive/sqlsyntax-error-in-activiti-5-18/m-p/228533#M181663 gangchu 2016-01-06T06:51:34Z Re: SqlSyntax Error in Activiti 5.18 https://connect.hyland.com/t5/alfresco-archive/sqlsyntax-error-in-activiti-5-18/m-p/228534#M181664 <HTML><HEAD></HEAD><BODY><SPAN>Indeed, you are correct. Fixed it here: </SPAN><A href="https://github.com/Activiti/Activiti/commit/68c02f75040af0501f04737380dcbbdc63edce3f" rel="nofollow noopener noreferrer">https://github.com/Activiti/Activiti/commit/68c02f75040af0501f04737380dcbbdc63edce3f</A></BODY></HTML> Thu, 07 Jan 2016 11:36:13 GMT https://connect.hyland.com/t5/alfresco-archive/sqlsyntax-error-in-activiti-5-18/m-p/228534#M181664 jbarrez 2016-01-07T11:36:13Z Re: SqlSyntax Error in Activiti 5.18 https://connect.hyland.com/t5/alfresco-archive/sqlsyntax-error-in-activiti-5-18/m-p/228535#M181665 <HTML><HEAD></HEAD><BODY><SPAN>thanks, jbarrez</SPAN></BODY></HTML> Fri, 08 Jan 2016 01:40:23 GMT https://connect.hyland.com/t5/alfresco-archive/sqlsyntax-error-in-activiti-5-18/m-p/228535#M181665 gangchu 2016-01-08T01:40:23Z