Page 1 of 1

JBPM workflow using SQL Query

PostPosted:Tue Dec 05, 2017 8:28 pm
by dferguson
I am trying to develop a workflow to select a user from the OKM_USER table. Following instructions in the 6.3 CE docs with regards to workflow selector (https://docs.openkm.com/kcenter/view/ok ... field.html).

My forms.xml looks like this...
Code: Select all
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE workflow-forms PUBLIC "-//OpenKM//DTD Workflow Forms 2.4//EN"
                                "http://www.openkm.com/dtd/workflow-forms-2.4.dtd">
<workflow-forms>
  <workflow-form task="select-user">
  	<select label="user" name="user" type="simple"  
            optionsQuery="select usr_id from OKM_USER" />
  </workflow-form>
</workflow-forms>
However the selctor is empty when running the workflow and openkm.log shows this...
Code: Select all
2017-12-05 15:19:54,012 [http-bio-10.30.40.185-8180-exec-654] [] ERROR com.openkm.bean.form.Select - Unable to get key values for Select
com.openkm.core.DatabaseException: OKM_USER is not mapped [select usr_id from OKM_USER]

I have confirmed that using "select usr_id from OKM_USER" does extract data from SQL query

Re: JBPM workflow using SQL Query

PostPosted:Wed Dec 06, 2017 4:11 pm
by jllort
I'm not 100% sure if you will get it running in community edition. Community edition not supports direct sql ( this feature at the present is only available in professional ). Anyway if you take a look at documentation https://docs.openkm.com/kcenter/view/ok ... adatatable you will see this has been thought for doing queries from table named DatabaseMetadataValue. Also take a look that this is an hibernate query not standard SQL.

You can try with ( not sure will going right, but if it fails ... then must be done injecting from workflow using data parameter )
Code: Select all
select id, name from User
or
Code: Select all
select usr_id, usr_name from OKM_USER

Re: JBPM workflow using SQL Query

PostPosted:Wed Dec 06, 2017 6:26 pm
by dferguson
I apologize, twice I have misunderstood CE features. https://docs.openkm.com/kcenter/view/ok ... field.html
Code: Select all
select id, name from User
Worked perfectly! But how did that syntax get mapped to
Code: Select all
select usr_id from OKM_USER

Re: JBPM workflow using SQL Query

PostPosted:Fri Dec 08, 2017 7:30 pm
by jllort
It's an hibernate search, really User.java is a bean name what is mapped to OKM_USER table. In the next release we will work in move older xml hibernate definition for JPA annotation ( this section of code is old an need some improvements to be adapted to the new times ).

Take a look at https://github.com/openkm/document-mana ... m/dao/bean ( and here the mapping https://github.com/openkm/document-mana ... er.hbm.xml ) not all tables are using this mapping only few ones.