Fill Table widget with values from DB

Hi all

I am a newbie at Bonita, struggling with displaying values, retrieved from an MS SQL DB, in a table. I am using Bonita BPM Community v6.2.0 with MS SQL 2012, on Windows 8, with JRE 6. I am running the JBOSS bundle. I am retrieving the data using a datasource connector, so the pretty new connector interface where I can choose the output type (nRows by nColumns) is not available.

Creating and filling a list manually, with Groovy code, I am able to display the values in a table widget, so the setup works. My problem is taking a resultset with the values, and writing those values into a variable of type List.

Using the folowing code

List<String> result = new ArrayList<String>();
while (resultset.next()) {
    String stringValue = resultset.getString(1)
    result.add(stringValue);
}
return result

I am able to display a single column extracted from the resultset, but I am struggling with reading a complete row and adding it to the list, which I will then display in the table widget.

Any assistance would be greatly appreciated.

Kind regards,

Christopher

1 Like

Haris, thank you very much

I tried your solution, and it worked first time. I am going to try and make this into a user script to call when ever I need to display a table, instead of having to have the code in each connector.

Thank you once again.

Hello,

you should transform your ResultSet into a List<List<Object>>

Something like:

final List<List<Object>> resultTable = new ArrayList<List<Object>>();
int maxColumn = rSet.getMetaData().getColumnCount()+1;
                while(rSet.next()){
                        final List<Object> row = new ArrayList<Object>();
                        for(int colIndex = 1; colIndex < maxColumn ; colIndex++){
                                row.add(rSet.getObject(colIndex));
                        }
                        resultTable.add(Collections.unmodifiableList(row));
                }

Hope this helps

Hi alfred.ayson.

That is 100% correct. On your connector, you then call the code like this:

 

List result = new ArrayList();
result = ConvertToTable.getTableList(resultset)
return result

 

Hope this helps.

In the community version, it does NOT seem to work. I created a new table and

  1. Marked the option Data → Allow selection
  2. "Initial Values I put the code below with return type = “java.util.List”
final List resultTable = new ArrayList();

int maxColumn = 5;

final List row = new ArrayList();
for(int colIndex = 1; colIndex < maxColumn ; colIndex++){
row.add("col " + colIndex);
}
resultTable.add(Collections.unmodifiableList(row));

return resultTable

What I am doing wrong ?

Thx !!!

Hello all , i m totally new in this environment.
i have same issue . i want to display database data into table into user’s form

i m using bonitasoft 6.3 community
database oracle 10g

now i create connector of oracle .
then as per mentioned i input credentials and then write query
then go to output screen .

on output screen of connector
there are two option .

  1. Graphical mode
  2. scripting mode.

actually where i have to write that code ?
because i go to scripting code it directly as for variable .
not for any code.
what i mistaken .
pl help me

thanks kandarp

hello is possible upload a video example for this case. Thanks

Dear all

I want to populate a simple table widget with the List of objects I retrieve from database, I already used the graphical Mode in Bonita to fill a List variable, the gui inform that the result will be a List of List and I take the value to a variable of type List and the system let me do it, and also tried the approach of the Grovy Script you wrote in answers to create the list in code from the resultset.

In UIDesigner I only can get values from database using Simple variables and using the REST API like this:

…/API/bpm/activityVariable/{{taskId}}/name

In other words, if I select a single object from MySql and map it in graphical mode to a single variable TEXT for example in activity I’m able to read it in UIDesigner creating an input and associate it to a variable associated with the external API. But mapping a list is not working.
I’m using this API …/API/bpm/activityVariable/{{taskId}}/myList
and than I use the column values with the names of the columns in database.
I supose the myList should have some kind of processing in first place to be transformed in JSON?

I’m very stucked here

Many thanks in advance

It doesn’t works for me I read the datas from a MySQl DAta base, and I get the next two errors:
In Chrome console:

Error: [$parse:syntax] http://errors.angularjs.org/1.3.18/$parse/syntax?p0=panel&p1=is%20an%20unexpected%20token&p2=7&p3=panel%20panel-default&p4=panel-default
at Error (native)
at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:6:417
at lb.throwError (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:192:113)
at lb.parse (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:190:406)
at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:110:366
at new n (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:10017)
at Object.create (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:8209)
at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:12401
at Array.forEach (native)
at Object.create (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:12373)

vendor.min.js:102 SyntaxError: Unexpected end of JSON input
at Object.parse (native)
at tc (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:14:245)
at bc (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:7)
at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:369
at q (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:7:322)
at dd (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:351)
at c (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:78:495)
at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:112:182
at m.$eval (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:126:250)
at m.$digest (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:123:365)(anonymous function) @ vendor.min.js:102(anonymous function) @ vendor.min.js:76(anonymous function) @ vendor.min.js:112m.$eval @ vendor.min.js:126m.$digest @ vendor.min.js:123m.$apply @ vendor.min.js:127l @ vendor.min.js:81w @ vendor.min.js:85I.onload @ vendor.min.js:86

And in the log of Bonita:

GRAVE: ERROR: listando reportes* 2016-07-12 16:36:37.259 +0100 org.bonitasoft.groovy.script.leeReportes org.slf4j.Logger$error$1 call GRAVE: Message Localized: No signature of method: java.util.ArrayList.getObject() is applicable for argument types: (java.lang.Integer) values: [1] Possible solutions: getAt(int), getAt(int)

2016-07-12 16:36:43.323 +0100 org.restlet.Component.Server org.restlet.engine.adapter.ServerAdapter commit
GRAVE: An exception occured writing the response entity
com.fasterxml.jackson.databind.JsonMappingException: [no message for java.lang.NullPointerException]
at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:261)
at com.fasterxml.jackson.databind.ObjectWriter._configAndWriteValue(ObjectWriter.java:802)
at com.fasterxml.jackson.databind.ObjectWriter.writeValue(ObjectWriter.java:642)
Caused by: java.lang.NullPointerException
at org.bonitasoft.web.rest.server.utils.JacksonSerializerHelper.writeValueAndStringValue(JacksonSerializerHelper.java:48)
at org.bonitasoft.web.rest.server.utils.DataInstanceSerializer.serialize(DataInstanceSerializer.java:43)
at org.bonitasoft.web.rest.server.utils.DataInstanceSerializer.serialize(DataInstanceSerializer.java:27)
at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:250)
… 61 more

2016-07-12 16:36:43.325 +0100 org.restlet.Component.Server org.restlet.engine.adapter.HttpServerHelper handle
ADVERTENCIA: Error while handling an HTTP server call
java.lang.IllegalStateException: No puedo llamar a sendError() tras llevar a cabo la respuesta

Hi, christopherf.
Could you post here your configuration for the connector? I have a similar issue.
Thansk a lot.

Bene

Hi Bene

I am using the standard MS SQL 2012 connector included with Bonita, and a standard SELECT statement to get the values I want from the database. On the output configuration screen, I use a Groovy script, supplied by Haris above, to transform the ResultSet into a LIST.

final List<List<Object>> resultTable = new ArrayList<List<Object>>();
	int maxColumn = rSet.getMetaData().getColumnCount()+1;
	                while(rSet.next()){
	                        final List<Object> row = new ArrayList<Object>();
	                        for(int colIndex = 1; colIndex < maxColumn ; colIndex++){
	                                row.add(rSet.getObject(colIndex));
	                        }
	                        resultTable.add(Collections.unmodifiableList(row));
<p>
	}</p>

On the form, I added a table widget, and on the widget's General>Data screen, I select "Edit as an expression", and select the LIST variable containing my SQL query output. 

One other thing I have done is to create a Groovy User Script of the above code, be enclosing the script in the following code:

def static getTableList(java.sql.ResultSet resultset) {

    [Original Groovy Script]

    return resultTable
}

I can then call this script on my connector:

List<String> result = new ArrayList<String>();
result = ConvertToTable.getTableList(resultset)
return result

Hope this answers your question. If you can produce a better solution, feel free to let us know here. As I've said, I am still a newbie, and will likely make silly mistakes from time to time.

Thanks, christopherf.

It just worked perfectly om my application.

Any further modifications I make I'll let you know.

Again, thanks a lot!!!

Harris, thank U too!

hi christopherf,

Im using BPMCommunity 6.2.1

I tried the items you mentioned above. I'm up to the part of creating Groovy User Script. Is the steps to create this the following?

1. Click on Development > Manage Groovy Scripts... menu

2. Click on Create button

3. Enter a name for the script

4. Double-Click on the script

5. In edit expression, enter the code below:

def static getTableList(java.sql.ResultSet resultset) {
    
    final List> resultTable = new ArrayList>();
    int maxColumn = rSet.getMetaData().getColumnCount()+1;
    
    while(rSet.next()){
        final List row = new ArrayList();
        for(int colIndex = 1; colIndex < maxColumn ; colIndex++){
            row.add(rSet.getObject(colIndex));
            }
        resultTable.add(Collections.unmodifiableList(row));
        }
    
    return resultTable
}


Is this correct?

Thank you in advance for the help.

Actually where i have to write that script .
i m using oracle database connector .

on output window config .
select > scripting mode .
which will ask for variable .
i create on variable of java.util.list.
in default data i paste that script
but it prompt error .

Error

i can’t understand what is exact process.
please help

thanks

Thank you sooo much it really helps.

Thank you, this solved my problem… !

Thank you, this solved my problem… !

It doesn’t works for me I read the datas from a MySQl DAta base, and I get the next two errors:
In Chrome console:

Error: [$parse:syntax] http://errors.angularjs.org/1.3.18/$parse/syntax?p0=panel&p1=is%20an%20unexpected%20token&p2=7&p3=panel%20panel-default&p4=panel-default
at Error (native)
at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:6:417
at lb.throwError (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:192:113)
at lb.parse (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:190:406)
at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:110:366
at new n (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:10017)
at Object.create (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:8209)
at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:12401
at Array.forEach (native)
at Object.create (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:12373)

vendor.min.js:102 SyntaxError: Unexpected end of JSON input
at Object.parse (native)
at tc (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:14:245)
at bc (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:7)
at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:369
at q (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:7:322)
at dd (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:351)
at c (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:78:495)
at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:112:182
at m.$eval (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:126:250)
at m.$digest (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:123:365)(anonymous function) @ vendor.min.js:102(anonymous function) @ vendor.min.js:76(anonymous function) @ vendor.min.js:112m.$eval @ vendor.min.js:126m.$digest @ vendor.min.js:123m.$apply @ vendor.min.js:127l @ vendor.min.js:81w @ vendor.min.js:85I.onload @ vendor.min.js:86

And in the log of Bonita:

GRAVE: ERROR: listando reportes* 2016-07-12 16:36:37.259 +0100 org.bonitasoft.groovy.script.leeReportes org.slf4j.Logger$error$1 call GRAVE: Message Localized: No signature of method: java.util.ArrayList.getObject() is applicable for argument types: (java.lang.Integer) values: [1] Possible solutions: getAt(int), getAt(int)

2016-07-12 16:36:43.323 +0100 org.restlet.Component.Server org.restlet.engine.adapter.ServerAdapter commit
GRAVE: An exception occured writing the response entity
com.fasterxml.jackson.databind.JsonMappingException: [no message for java.lang.NullPointerException]
at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:261)
at com.fasterxml.jackson.databind.ObjectWriter._configAndWriteValue(ObjectWriter.java:802)
at com.fasterxml.jackson.databind.ObjectWriter.writeValue(ObjectWriter.java:642)
Caused by: java.lang.NullPointerException
at org.bonitasoft.web.rest.server.utils.JacksonSerializerHelper.writeValueAndStringValue(JacksonSerializerHelper.java:48)
at org.bonitasoft.web.rest.server.utils.DataInstanceSerializer.serialize(DataInstanceSerializer.java:43)
at org.bonitasoft.web.rest.server.utils.DataInstanceSerializer.serialize(DataInstanceSerializer.java:27)
at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:250)
… 61 more

2016-07-12 16:36:43.325 +0100 org.restlet.Component.Server org.restlet.engine.adapter.HttpServerHelper handle
ADVERTENCIA: Error while handling an HTTP server call
java.lang.IllegalStateException: No puedo llamar a sendError() tras llevar a cabo la respuesta

Pl sir describe me procedure to display data into table .
i can’t figure out

thanks kandarp