initiate DB procedure from workflow

[Balázs Csönge, modificado 9 Anos atrás.]

Hi,

Is it a way to call any database (for example ORACLE stored procedure or a simple insert) function from kaleo workflow task/state using Groovy or something else?
If yes, pls can any1 show me an example how can I do that?
And how can I pass any asset content to that procedure, for example a DDL walking through the workflow, how can I refer a field of it and pass its value to the database function?

Advanced case, is it possible to modify the asset context what going through the flow? For example can I fill or update a DDL field value based on database select?

[Juan Fernández, modificado 9 Anos atrás.]

Hi Balazs:
this article might help you a little. It has an example on how to use Groovy as part of a workflow definition

https://dev.liferay.com/discover/deployment/-/knowledge_base/6-2/leveraging-the-script-engine-in-workflow

Best,
Juan

[Balázs Csönge, modificado 9 Anos atrás.]

Hi Juan,

First ty for the reply.

Luckily I found a Cookbook Examples in Groovy documentation. Based on writing to BLOB example combined with Using Workflow Scripts example I was able to made a simple script which insert a record into a database and pass a parameter with the value of a DDL's field.

import groovy.sql.Sql
import com.liferay.portal.kernel.util.GetterUtil;
import com.liferay.portal.kernel.workflow.WorkflowConstants;
import com.liferay.portlet.dynamicdatamapping.storage.Field;
import com.liferay.portlet.dynamicdatalists.model.DDLRecord;
import com.liferay.portlet.dynamicdatalists.service.DDLRecordLocalServiceUtil;

println "---- A simple ORACLE insert into test without parameters ---"
sql = Sql.newInstance("jdbc:oracle:thin:@ipaddress:1521:DER9DATA", "user",
					 "ped", "oracle.jdbc.OracleDriver")

long classPK = GetterUtil.getLong((String)workflowContext.get(WorkflowConstants.CONTEXT_ENTRY_CLASS_PK));
DDLRecord ddlRecord = DDLRecordLocalServiceUtil.getRecord(classPK-3);

Field field = ddlRecord.getField("megnevezes");

String str = GetterUtil.getString(field.getValue());

sql.execute(
	"INSERT INTO PROBA (ID, NEV) VALUES (PROBA_SEQ.NextVal, ?.nev)",[nev:str]);

sql.connection.autoCommit = false

try {
	sql.commit()
} catch (Exception e) {
  println "Failed: $e"
  sql.rollback()
} finally {
  sql.connection.close()
}

Important remark. The DDLRecord ddlRecord = DDLRecordLocalServiceUtil.getRecord(classPK); code line from liferay own documentation not working and throws an exception about there is no DDL record with that ID, so I examined the database content and I figured out I have to use DDLRecord ddlRecord = DDLRecordLocalServiceUtil.getRecord(classPK-3); Is it a bug or not, not know, but that -3 magical operation worked for me.

It was an insert, but in the Database features part of the Groovy documentation helps a lot if some1 want select, delete, update, call stored procedure, etc. I made a select for example:

sql = Sql.newInstance("jdbc:oracle:thin:@ipaddress:1521:DER9DATA", "user",
					 "ped", "oracle.jdbc.OracleDriver")
row = sql.firstRow("select max(T.KALEOTASKID) from kaleotask t where T.NAME='review2'")
long taskID = row[0]

Sadly till no idea how can I read the content of that thing (asset) what initiate the flow independently from its type. (If it is possible at all.) And how can I update that content if needed. But I think I will open another topic about this.