How to populate a select box field from a database using Groovy

bouquetf's picture
bouquetf
Friday, February 3, 2012 - 12:23

A good practice while developing processes is to store business data in an external business database and retrieve them only when needed. A good question is: How do I retrieve this information from my forms?

Here's a quick tutorial which explains how to retrieve the information from an external database, and display it as the initial value of a select box field in a form. You can find the process used as an example here.

Create a process with a human task and one form to interact with.

Add an entry pageflow form on the task Display data.

In this form, use a widget to add a select box field.

Click on the select box widget, then on the General tab and Data pane.  In the available value field, use this script:

import groovy.sql.Sql;
def result = []
Sql sql = providedscripts.BonitaSql.newInstance(url,username,password,jdbcdriver)
sql.eachRow 'select * from USERS;', { result += it.name }
sql.close()
result

This script will create a list to store the usernames that are returned. Then, it opens the SQL connection to query the table and add each name to the list. Finally, it returns the result.

At the execution, you'll get this:

Comments

Submitted by kppatel on Tue, 04/01/2014 - 14:39

Sir thanks for this nice tutorial but whenever i write groovey script regarding sql it always prompt "providedscripts cannot be resolved. It may lead to runtime errors." following msg and if i evalute for testing give following error Exception evolution expression

Permalink
Submitted by manoj_5 on Thu, 01/22/2015 - 12:45

Hi, I also tried above with following code:

import groovy.sql.Sql; import java.sql.Driver; def String str; def host = "localhost:3306"; def user = "root"; def pass = "root"; def schema = "test"; def result = []; def sql = BonitaSql.newInstance("jdbc:mysql://$host/$schema","$user","$pass", new com.mysql.jdbc.Driver());

sql.eachRow 'select name from data;', { result += it.name println "Gromit likes ${it.name}" } //result += it.name //println "Gromit likes ${it.name}"

sql.close() result

but it gives error:

at org.bonitasoft.engine.expression.impl.GroovyScriptExpressionExecutorCacheStrategy.evaluate(GroovyScriptExpressionExecutorCacheStrategy.java:131)
at org.bonitasoft.engine.expression.impl.ExpressionServiceImpl.evaluate(ExpressionServiceImpl.java:86)
at org.bonitasoft.engine.core.expression.control.api.impl.ExpressionResolverServiceImpl.evaluateExpressionWithResolvedDependencies(ExpressionResolverServiceImpl.java:215)
at org.bonitasoft.engine.core.expression.control.api.impl.ExpressionResolverServiceImpl.evaluateExpressionsFlatten(ExpressionResolverServiceImpl.java:120)
at org.bonitasoft.engine.core.expression.control.api.impl.ExpressionResolverServiceImpl.evaluate(ExpressionResolverServiceImpl.java:83)
at org.bonitasoft.engine.api.impl.transaction.expression.EvaluateExpressionsInstanceLevel.execute(EvaluateExpressionsInstanceLevel.java:70)
at org.bonitasoft.engine.api.impl.ProcessAPIImpl.evaluateExpressionsInstanceLevel(ProcessAPIImpl.java:5673)
at org.bonitasoft.engine.api.impl.ProcessAPIImpl.evaluateExpressionsOnActivityInstance(ProcessAPIImpl.java:5616)
... 50 more

Caused by: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script1.groovy: 19: unable to resolve class com.mysql.jdbc.Driver @ line 19, column 79. ost/$schema","$user","$pass", new com.my ^

Permalink
Submitted by jeremykent2726_1 on Wed, 05/20/2015 - 12:22

This raises errors on Bonita 6 as

it
providedscripts

are not defined in the script.

Permalink
Notifications