topic Re: Activiti with sql not executed? in Alfresco Forum

Activiti with sql not executed?

vamsinipun — Thu, 21 Dec 2017 04:58:18 GMT

Dear Team, I am using activiti in my spring web application and would like to execute sql statement when the task complete. but it isn't work. please check and tell me the right way.Thanks

Re: Activiti with sql not executed?

vamsinipun — Fri, 22 Dec 2017 11:21:59 GMT

Hi Team,

I referred this link https://www.win.tue.nl/~dfahland/courses/2io71/docs/2io71_tutorial_03_activiti_and_sql.pdf for this scenario. But the sql statement is not executed. if anyone know about this please let me know.

Thanks

Re: Activiti with sql not executed?

vamsinipun — Tue, 26 Dec 2017 07:29:17 GMT

Hi,

Please reply me if anyone have idea on this.

Re: Activiti with sql not executed?

Anonymous — Tue, 26 Dec 2017 18:39:39 GMT

Well... I don't know expression type that can describe custom SQL directly.

But, there is a feature called NativeQuery to achieve similar things with your expression.
https://www.activiti.org/userguide/#queryAPI

${execution.getEngineServices().getRuntimeService().createNativeExecutionQuery().sql('UPDATE act_custom_process SET status=#{status} WHERE id=#{id}').parameter('status', 'true').parameter('id', '44').singleResult()}

Re: Activiti with sql not executed?

vamsinipun — Wed, 27 Dec 2017 06:02:37 GMT

Thank you for your reply. This is helpful in future. But I would like to execute the query in bpmn file. do you know about that?

Re: Activiti with sql not executed?

vamsinipun — Wed, 27 Dec 2017 06:08:57 GMT

Please let me know how can i execute this means where can i place it?

${execution.getEngineServices().getRuntimeService().createNativeExecutionQuery().sql('UPDATE act_custom_process SET status=#{status} WHERE id=#{id}').parameter('status', 'true').parameter('id', '44').singleResult()}

Re: Activiti with sql not executed?

Anonymous — Wed, 27 Dec 2017 06:42:28 GMT

It is the same as the image you are up and can be entered directly in the Expression column.

The sample process definition is as follows.

https://community.alfresco.com/servlet/JiveServlet/download/826126-1-29483/SQL%20Process.bpmn.zip

Re: Activiti with sql not executed?

vamsinipun — Wed, 27 Dec 2017 06:51:36 GMT

Ya. I put like that only, but it is not executed. do you know any reason for that means any configuration or mappings?

Re: Activiti with sql not executed?

Anonymous — Wed, 27 Dec 2017 06:55:41 GMT

Please share your bpmn file.

Re: Activiti with sql not executed?

vamsinipun — Wed, 27 Dec 2017 07:01:23 GMT

<?xml version="1.0" encoding="UTF-8"?>
<definitions xmlns="http://www.omg.org/spec/BPMN/20100524/MODEL" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:activiti="http://activiti.org/bpmn" xmlns:bpmndi="http://www.omg.org/spec/BPMN/20100524/DI" xmlnsmgdc="http://www.omg.org/spec/DD/20100524/DC" xmlnsmgdi="http://www.omg.org/spec/DD/20100524/DI" typeLanguage="http://www.w3.org/2001/XMLSchema" expressionLanguage="http://www.w3.org/1999/XPath" targetNamespace="http://www.activiti.org/test">
<process id="myProcess" name="My process" isExecutable="true">
<startEvent id="startevent1" name="Start"></startEvent>
<userTask id="usertask2" name="Reporting Manager Approval" activiti:assignee="${reporting_manager}" activiti:candidateUsers="${reporting_managers}" activiti:candidateGroups="${reporting_manager_groups}">
<extensionElements>
<activiti:formProperty id="reporting_manager_approval" name="Approved?" type="boolean" required="true"></activiti:formProperty>
<activiti:formProperty id="reporting_manager_comments" name="Comments" type="string" required="true"></activiti:formProperty>
<activiti:taskListener event="complete" expression="${execution.getEngineServices().getRuntimeService().createNativeExecutionQuery().sql('UPDATE act_custom_process SET status=#{status} WHERE id=#{id}').parameter('status', 'true').parameter('id', '44').singleResult()}"></activiti:taskListener>
</extensionElements>
</userTask>
<userTask id="usertask3" name="HR Approval" activiti:candidateUsers="${hrs}" activiti:candidateGroups="${hr_groups}">
<extensionElements>
<activiti:formProperty id="hr_approval" name="Approved?" type="boolean" required="true"></activiti:formProperty>
<activiti:formProperty id="hr_comments" name="Comments" type="string" required="true"></activiti:formProperty>
</extensionElements>
</userTask>
<sequenceFlow id="flow3" name="approved" sourceRef="exclusivegateway1" targetRef="usertask3"></sequenceFlow>
<endEvent id="endevent1" name="End"></endEvent>
<sequenceFlow id="flow4" name="rejected" sourceRef="exclusivegateway2" targetRef="endevent1">
<conditionExpression xsi:type="tFormalExpression"><![CDATA[${!hr_approval}]]></conditionExpression>
</sequenceFlow>
<exclusiveGateway id="exclusivegateway1" name="Exclusive Gateway" default="flow3"></exclusiveGateway>
<sequenceFlow id="flow6" sourceRef="usertask2" targetRef="exclusivegateway1"></sequenceFlow>
<sequenceFlow id="flow7" name="rejected" sourceRef="exclusivegateway1" targetRef="endevent1">
<extensionElements>
<activiti:executionListener event="take" expression="sql:UPDATE act_custom_process SET status=true WHERE id=15; "></activiti:executionListener>
</extensionElements>
<conditionExpression xsi:type="tFormalExpression"><![CDATA[${!reporting_manager_approval}]]></conditionExpression>
</sequenceFlow>
<exclusiveGateway id="exclusivegateway2" name="Exclusive Gateway" default="flow9"></exclusiveGateway>
<sequenceFlow id="flow8" sourceRef="usertask3" targetRef="exclusivegateway2"></sequenceFlow>
<userTask id="usertask4" name="Finance Manager" activiti:candidateUsers="${finance_users}" activiti:candidateGroups="${finance_user_groups}">
<extensionElements>
<activiti:formProperty id="finance_manager_approval" name="Approve?" type="boolean" required="true"></activiti:formProperty>
<activiti:formProperty id="finance_manager_comments" name="Comments" type="string" required="true"></activiti:formProperty>
</extensionElements>
</userTask>
<sequenceFlow id="flow9" name="approved" sourceRef="exclusivegateway2" targetRef="usertask4"></sequenceFlow>
<exclusiveGateway id="exclusivegateway3" name="Exclusive Gateway" default="flow11"></exclusiveGateway>
<sequenceFlow id="flow10" sourceRef="usertask4" targetRef="exclusivegateway3"></sequenceFlow>
<userTask id="usertask5" name="Account Manager" activiti:candidateUsers="${accountants}" activiti:candidateGroups="${accountant_groups}"></userTask>
<sequenceFlow id="flow11" name="approved" sourceRef="exclusivegateway3" targetRef="audit_manager_approval"></sequenceFlow>
<sequenceFlow id="flow12" name="rejected" sourceRef="exclusivegateway3" targetRef="endevent1">
<conditionExpression xsi:type="tFormalExpression"><![CDATA[${!finance_manager_approval}]]></conditionExpression>
</sequenceFlow>
<sequenceFlow id="flow13" name="transfer amount" sourceRef="usertask5" targetRef="endevent1"></sequenceFlow>
<sequenceFlow id="flow14" sourceRef="startevent1" targetRef="usertask2"></sequenceFlow>
<userTask id="audit_manager_approval" name="Audit Manager" activiti:candidateUsers="${audit_users}" activiti:candidateGroups="${audit_groups}">
<extensionElements>
<activiti:formProperty id="audit_manager_approval" name="Approved?" type="boolean" required="true"></activiti:formProperty>
<activiti:formProperty id="audit_manager_comments" name="Comments" type="string" required="true"></activiti:formProperty>
</extensionElements>
</userTask>
<exclusiveGateway id="exclusivegateway4" name="Exclusive Gateway" default="flow16"></exclusiveGateway>
<sequenceFlow id="flow15" sourceRef="audit_manager_approval" targetRef="exclusivegateway4"></sequenceFlow>
<sequenceFlow id="flow16" name="approved" sourceRef="exclusivegateway4" targetRef="usertask5"></sequenceFlow>
<sequenceFlow id="flow17" name="rejected" sourceRef="exclusivegateway4" targetRef="endevent1">
<conditionExpression xsi:type="tFormalExpression"><![CDATA[${!audit_manager_approval}]]></conditionExpression>
</sequenceFlow>
</process>
<bpmndi:BPMNDiagram id="BPMNDiagram_myProcess">
<bpmndi:BPMNPlane bpmnElement="myProcess" id="BPMNPlane_myProcess">
<bpmndi:BPMNShape bpmnElement="startevent1" id="BPMNShape_startevent1">
<omgdc:Bounds height="35.0" width="35.0" x="30.0" y="163.0"></omgdc:Bounds>
</bpmndi:BPMNShape>
<bpmndi:BPMNShape bpmnElement="usertask2" id="BPMNShape_usertask2">
<omgdc:Bounds height="61.0" width="105.0" x="120.0" y="150.0"></omgdc:Bounds>
</bpmndi:BPMNShape>
<bpmndi:BPMNShape bpmnElement="usertask3" id="BPMNShape_usertask3">
<omgdc:Bounds height="61.0" width="105.0" x="340.0" y="150.0"></omgdc:Bounds>
</bpmndi:BPMNShape>
<bpmndi:BPMNShape bpmnElement="endevent1" id="BPMNShape_endevent1">
<omgdc:Bounds height="35.0" width="35.0" x="263.0" y="304.0"></omgdc:Bounds>
</bpmndi:BPMNShape>
<bpmndi:BPMNShape bpmnElement="exclusivegateway1" id="BPMNShape_exclusivegateway1">
<omgdc:Bounds height="40.0" width="40.0" x="260.0" y="160.0"></omgdc:Bounds>
</bpmndi:BPMNShape>
<bpmndi:BPMNShape bpmnElement="exclusivegateway2" id="BPMNShape_exclusivegateway2">
<omgdc:Bounds height="40.0" width="40.0" x="490.0" y="161.0"></omgdc:Bounds>
</bpmndi:BPMNShape>
<bpmndi:BPMNShape bpmnElement="usertask4" id="BPMNShape_usertask4">
<omgdc:Bounds height="55.0" width="105.0" x="575.0" y="154.0"></omgdc:Bounds>
</bpmndi:BPMNShape>
<bpmndi:BPMNShape bpmnElement="exclusivegateway3" id="BPMNShape_exclusivegateway3">
<omgdc:Bounds height="40.0" width="40.0" x="670.0" y="110.0"></omgdc:Bounds>
</bpmndi:BPMNShape>
<bpmndi:BPMNShape bpmnElement="usertask5" id="BPMNShape_usertask5">
<omgdc:Bounds height="55.0" width="105.0" x="796.0" y="209.0"></omgdc:Bounds>
</bpmndi:BPMNShape>
<bpmndi:BPMNShape bpmnElement="audit_manager_approval" id="BPMNShape_audit_manager_approval">
<omgdc:Bounds height="55.0" width="105.0" x="700.0" y="40.0"></omgdc:Bounds>
</bpmndi:BPMNShape>
<bpmndi:BPMNShape bpmnElement="exclusivegateway4" id="BPMNShape_exclusivegateway4">
<omgdc:Bounds height="40.0" width="40.0" x="811.0" y="134.0"></omgdc:Bounds>
</bpmndi:BPMNShape>
<bpmndi:BPMNEdge bpmnElement="flow3" id="BPMNEdge_flow3">
<omgdi:waypoint x="300.0" y="180.0"></omgdi:waypoint>
<omgdi:waypoint x="340.0" y="180.0"></omgdi:waypoint>
<bpmndi:BPMNLabel>
<omgdc:Bounds height="14.0" width="46.0" x="289.0" y="154.0"></omgdc:Bounds>
</bpmndi:BPMNLabel>
</bpmndi:BPMNEdge>
<bpmndi:BPMNEdge bpmnElement="flow4" id="BPMNEdge_flow4">
<omgdi:waypoint x="510.0" y="201.0"></omgdi:waypoint>
<omgdi:waypoint x="509.0" y="321.0"></omgdi:waypoint>
<omgdi:waypoint x="298.0" y="321.0"></omgdi:waypoint>
<bpmndi:BPMNLabel>
<omgdc:Bounds height="14.0" width="39.0" x="381.0" y="303.0"></omgdc:Bounds>
</bpmndi:BPMNLabel>
</bpmndi:BPMNEdge>
<bpmndi:BPMNEdge bpmnElement="flow6" id="BPMNEdge_flow6">
<omgdi:waypoint x="225.0" y="180.0"></omgdi:waypoint>
<omgdi:waypoint x="260.0" y="180.0"></omgdi:waypoint>
</bpmndi:BPMNEdge>
<bpmndi:BPMNEdge bpmnElement="flow7" id="BPMNEdge_flow7">
<omgdi:waypoint x="280.0" y="200.0"></omgdi:waypoint>
<omgdi:waypoint x="280.0" y="304.0"></omgdi:waypoint>
<bpmndi:BPMNLabel>
<omgdc:Bounds height="14.0" width="39.0" x="224.0" y="229.0"></omgdc:Bounds>
</bpmndi:BPMNLabel>
</bpmndi:BPMNEdge>
<bpmndi:BPMNEdge bpmnElement="flow8" id="BPMNEdge_flow8">
<omgdi:waypoint x="445.0" y="180.0"></omgdi:waypoint>
<omgdi:waypoint x="490.0" y="181.0"></omgdi:waypoint>
</bpmndi:BPMNEdge>
<bpmndi:BPMNEdge bpmnElement="flow9" id="BPMNEdge_flow9">
<omgdi:waypoint x="530.0" y="181.0"></omgdi:waypoint>
<omgdi:waypoint x="575.0" y="181.0"></omgdi:waypoint>
<bpmndi:BPMNLabel>
<omgdc:Bounds height="14.0" width="46.0" x="519.0" y="150.0"></omgdc:Bounds>
</bpmndi:BPMNLabel>
</bpmndi:BPMNEdge>
<bpmndi:BPMNEdge bpmnElement="flow10" id="BPMNEdge_flow10">
<omgdi:waypoint x="680.0" y="181.0"></omgdi:waypoint>
<omgdi:waypoint x="690.0" y="150.0"></omgdi:waypoint>
</bpmndi:BPMNEdge>
<bpmndi:BPMNEdge bpmnElement="flow11" id="BPMNEdge_flow11">
<omgdi:waypoint x="690.0" y="110.0"></omgdi:waypoint>
<omgdi:waypoint x="752.0" y="95.0"></omgdi:waypoint>
<bpmndi:BPMNLabel>
<omgdc:Bounds height="14.0" width="46.0" x="620.0" y="81.0"></omgdc:Bounds>
</bpmndi:BPMNLabel>
</bpmndi:BPMNEdge>
<bpmndi:BPMNEdge bpmnElement="flow12" id="BPMNEdge_flow12">
<omgdi:waypoint x="690.0" y="150.0"></omgdi:waypoint>
<omgdi:waypoint x="722.0" y="321.0"></omgdi:waypoint>
<omgdi:waypoint x="298.0" y="321.0"></omgdi:waypoint>
<bpmndi:BPMNLabel>
<omgdc:Bounds height="14.0" width="39.0" x="572.0" y="252.0"></omgdc:Bounds>
</bpmndi:BPMNLabel>
</bpmndi:BPMNEdge>
<bpmndi:BPMNEdge bpmnElement="flow13" id="BPMNEdge_flow13">
<omgdi:waypoint x="848.0" y="264.0"></omgdi:waypoint>
<omgdi:waypoint x="822.0" y="349.0"></omgdi:waypoint>
<omgdi:waypoint x="280.0" y="339.0"></omgdi:waypoint>
<bpmndi:BPMNLabel>
<omgdc:Bounds height="14.0" width="77.0" x="746.0" y="379.0"></omgdc:Bounds>
</bpmndi:BPMNLabel>
</bpmndi:BPMNEdge>
<bpmndi:BPMNEdge bpmnElement="flow14" id="BPMNEdge_flow14">
<omgdi:waypoint x="65.0" y="180.0"></omgdi:waypoint>
<omgdi:waypoint x="120.0" y="180.0"></omgdi:waypoint>
</bpmndi:BPMNEdge>
<bpmndi:BPMNEdge bpmnElement="flow15" id="BPMNEdge_flow15">
<omgdi:waypoint x="752.0" y="95.0"></omgdi:waypoint>
<omgdi:waypoint x="831.0" y="134.0"></omgdi:waypoint>
</bpmndi:BPMNEdge>
<bpmndi:BPMNEdge bpmnElement="flow16" id="BPMNEdge_flow16">
<omgdi:waypoint x="831.0" y="174.0"></omgdi:waypoint>
<omgdi:waypoint x="848.0" y="209.0"></omgdi:waypoint>
<bpmndi:BPMNLabel>
<omgdc:Bounds height="14.0" width="46.0" x="847.0" y="174.0"></omgdc:Bounds>
</bpmndi:BPMNLabel>
</bpmndi:BPMNEdge>
<bpmndi:BPMNEdge bpmnElement="flow17" id="BPMNEdge_flow17">
<omgdi:waypoint x="831.0" y="174.0"></omgdi:waypoint>
<omgdi:waypoint x="648.0" y="254.0"></omgdi:waypoint>
<omgdi:waypoint x="280.0" y="304.0"></omgdi:waypoint>
<bpmndi:BPMNLabel>
<omgdc:Bounds height="14.0" width="39.0" x="731.0" y="272.0"></omgdc:Bounds>
</bpmndi:BPMNLabel>
</bpmndi:BPMNEdge>
</bpmndi:BPMNPlane>
</bpmndi:BPMNDiagram>
</definitions>

Re: Activiti with sql not executed?

vamsinipun — Wed, 27 Dec 2017 07:03:47 GMT

Please check above xml file.

Re: Activiti with sql not executed?

vamsinipun — Wed, 27 Dec 2017 10:38:47 GMT

Finally i executed with static query with given below expression by you

Now, how can i pass dynamic fields instead of static values like 'true' and 44?

Re: Activiti with sql not executed?

Anonymous — Wed, 27 Dec 2017 11:45:29 GMT

You can use variables to pass dynamic fields as the following expression.

${execution.getEngineServices().getRuntimeService().createNativeExecutionQuery().sql('UPDATE act_custom_process SET status=#{status} WHERE id=#{id}').parameter('status', execution.getVariable('flag')).parameter('id', execution.getVariable('id')).singleResult()}

By the way, what was the cause for not working first?

Re: Activiti with sql not executed?

vamsinipun — Wed, 27 Dec 2017 12:39:45 GMT

Thank you for your reply once again. almost i reached my requirement. please help me in this too.

Here we have assignee option with variable like ${reporting_manager}. I was set the value through my java code, instead of that how can i set that through sql without java?

Re: Activiti with sql not executed?

Anonymous — Wed, 27 Dec 2017 12:54:45 GMT

You can set variable by the el expression as the following expression.

Activiti User Guide

${execution.setVariable('reporting_manager', %Value%)}

Re: Activiti with sql not executed?

vamsinipun — Thu, 28 Dec 2017 06:42:27 GMT

Hi,

if i execute the query in java class mean listener. how can i get those data?

Re: Activiti with sql not executed?

Anonymous — Thu, 28 Dec 2017 08:57:29 GMT

It is not possible to select from any table.

If it is an Activiti table, you can do it.

Re: Activiti with sql not executed?

vamsinipun — Thu, 28 Dec 2017 09:23:16 GMT

k now how can i get the application table data? is there any other way?