JBPM workflow using SQL Query

OpenKM has many interesting features, but requires some configuration process to show its full potential.
Forum rules
Please, before asking something see the documentation wiki or use the search feature of the forum. And remember we don't have a crystal ball or mental readers, so if you post about an issue tell us which OpenKM are you using and also the browser and operating system version. For more info read How to Report Bugs Effectively.
Post Reply
dferguson
Expert Boarder
Expert Boarder
Posts: 93
Joined: Thu May 18, 2017 12:48 pm

JBPM workflow using SQL Query

Post by dferguson » Tue Dec 05, 2017 8:28 pm

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

jllort
Moderator
Moderator
Posts: 10077
Joined: Fri Dec 21, 2007 11:23 am
Location: Sineu - ( Illes Balears ) - Spain
Contact:

Re: JBPM workflow using SQL Query

Post by jllort » Wed Dec 06, 2017 4:11 pm

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

dferguson
Expert Boarder
Expert Boarder
Posts: 93
Joined: Thu May 18, 2017 12:48 pm

Re: JBPM workflow using SQL Query

Post by dferguson » Wed Dec 06, 2017 6:26 pm

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

jllort
Moderator
Moderator
Posts: 10077
Joined: Fri Dec 21, 2007 11:23 am
Location: Sineu - ( Illes Balears ) - Spain
Contact:

Re: JBPM workflow using SQL Query

Post by jllort » Fri Dec 08, 2017 7:30 pm

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.

Post Reply

Who is online

Users browsing this forum: Bing [Bot] and 3 guests