• JBPM workflow using SQL Query

  • OpenKM has many interesting features, but requires some configuration process to show its full potential.
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.
 #45000  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
 #45009  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
 #45029  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.

About Us

OpenKM is part of the management software. A management software is a program that facilitates the accomplishment of administrative tasks. OpenKM is a document management system that allows you to manage business content and workflow in a more efficient way. Document managers guarantee data protection by establishing information security for business content.