Fill Table widget with values from DB

1
+1
-1

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 result = new ArrayList();
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

8 answers

1
+7
-1
This one is the BEST answer!

Hello,

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

Something like:

  1. final List<List<Object>> resultTable = new ArrayList<List<Object>>();<br />
  2. int maxColumn = rSet.getMetaData().getColumnCount()+1;<br />
  3. while(rSet.next()){<br />
  4. final List<Object> row = new ArrayList<Object>();<br />
  5. for(int colIndex = 1; colIndex < maxColumn ; colIndex++){<br />
  6. row.add(rSet.getObject(colIndex));<br />
  7. }<br />
  8. resultTable.add(Collections.unmodifiableList(row));

                }

Hope this helps

Comments

Submitted by kppatel on Fri, 06/13/2014 - 12:03

Thank you sooo much it really helps.

Submitted by cdominguez.bravo on Tue, 06/17/2014 - 19:33

Thank you, this solved my problem... !

Submitted by cdominguez.bravo on Tue, 06/17/2014 - 19:34

Thank you, this solved my problem... !

Submitted by hectorta on Tue, 07/12/2016 - 17:44

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:

  1. 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
  2. at Error (native)
  3. at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:6:417
  4. at lb.throwError (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:192:113)
  5. at lb.parse (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:190:406)
  6. at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:110:366
  7. at new n (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:10017)
  8. at Object.create (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:8209)
  9. at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:12401
  10. at Array.forEach (native)
  11. at Object.create (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:12373) <div pb-property-values="59c870ab-b357-45f3-b176-25f60338efb0" class="ng-scope">
  12.  
  13.  
  14. vendor.min.js:102 SyntaxError: Unexpected end of JSON input
  15. at Object.parse (native)
  16. at tc (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:14:245)
  17. at bc (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:7)
  18. at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:369
  19. at q (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:7:322)
  20. at dd (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:351)
  21. at c (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:78:495)
  22. at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:112:182
  23. at m.$eval (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:126:250)
  24. 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:

  1. GRAVE: ERROR: listando reportes*
  2. 2016-07-12 16:36:37.259 +0100 org.bonitasoft.groovy.script.leeReportes org.slf4j.Logger$error$1 call
  3. GRAVE: Message Localized: No signature of method: java.util.ArrayList.getObject() is applicable for argument types: (java.lang.Integer) values: [1]
  4. Possible solutions: getAt(int), getAt(int)
  5.  
  6.  
  7. 2016-07-12 16:36:43.323 +0100 org.restlet.Component.Server org.restlet.engine.adapter.ServerAdapter commit
  8. GRAVE: An exception occured writing the response entity
  9. com.fasterxml.jackson.databind.JsonMappingException: [no message for java.lang.NullPointerException]
  10. at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:261)
  11. at com.fasterxml.jackson.databind.ObjectWriter._configAndWriteValue(ObjectWriter.java:802)
  12. at com.fasterxml.jackson.databind.ObjectWriter.writeValue(ObjectWriter.java:642)
  13. Caused by: java.lang.NullPointerException
  14. at org.bonitasoft.web.rest.server.utils.JacksonSerializerHelper.writeValueAndStringValue(JacksonSerializerHelper.java:48)
  15. at org.bonitasoft.web.rest.server.utils.DataInstanceSerializer.serialize(DataInstanceSerializer.java:43)
  16. at org.bonitasoft.web.rest.server.utils.DataInstanceSerializer.serialize(DataInstanceSerializer.java:27)
  17. at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:250)
  18. ... 61 more
  19.  
  20. 2016-07-12 16:36:43.325 +0100 org.restlet.Component.Server org.restlet.engine.adapter.HttpServerHelper handle
  21. ADVERTENCIA: Error while handling an HTTP server call
  22. java.lang.IllegalStateException: No puedo llamar a sendError() tras llevar a cabo la respuesta

1
+2
-1

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.

Comments

Submitted by bjnobre on Wed, 01/22/2014 - 17:55

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

Bene

Submitted by christopherf on Thu, 01/23/2014 - 07:00

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> 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));

    

}

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 result = new ArrayList();
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.

Submitted by bjnobre on Thu, 01/23/2014 - 12:48

Thanks, christopherf.

It just worked perfectly om my application.

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

Again, thanks a lot!!!

Submitted by bjnobre on Thu, 01/23/2014 - 12:49

Harris, thank U too!

Submitted by alfred.ayson on Tue, 03/18/2014 - 06:25

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.

Submitted by kppatel on Thu, 06/12/2014 - 07:52

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

1
0
-1

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:

  1. 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
  2. at Error (native)
  3. at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:6:417
  4. at lb.throwError (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:192:113)
  5. at lb.parse (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:190:406)
  6. at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:110:366
  7. at new n (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:10017)
  8. at Object.create (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:8209)
  9. at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:12401
  10. at Array.forEach (native)
  11. at Object.create (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/runtime.min.js:1:12373) <div pb-property-values="59c870ab-b357-45f3-b176-25f60338efb0" class="ng-scope">
  12.  
  13.  
  14. vendor.min.js:102 SyntaxError: Unexpected end of JSON input
  15. at Object.parse (native)
  16. at tc (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:14:245)
  17. at bc (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:7)
  18. at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:369
  19. at q (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:7:322)
  20. at dd (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:77:351)
  21. at c (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:78:495)
  22. at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:112:182
  23. at m.$eval (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.3/RevisarCamposNotificacion/content/js/vendor.min.js:126:250)
  24. 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:

  1. GRAVE: ERROR: listando reportes*
  2. 2016-07-12 16:36:37.259 +0100 org.bonitasoft.groovy.script.leeReportes org.slf4j.Logger$error$1 call
  3. GRAVE: Message Localized: No signature of method: java.util.ArrayList.getObject() is applicable for argument types: (java.lang.Integer) values: [1]
  4. Possible solutions: getAt(int), getAt(int)
  5.  
  6.  
  7. 2016-07-12 16:36:43.323 +0100 org.restlet.Component.Server org.restlet.engine.adapter.ServerAdapter commit
  8. GRAVE: An exception occured writing the response entity
  9. com.fasterxml.jackson.databind.JsonMappingException: [no message for java.lang.NullPointerException]
  10. at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:261)
  11. at com.fasterxml.jackson.databind.ObjectWriter._configAndWriteValue(ObjectWriter.java:802)
  12. at com.fasterxml.jackson.databind.ObjectWriter.writeValue(ObjectWriter.java:642)
  13. Caused by: java.lang.NullPointerException
  14. at org.bonitasoft.web.rest.server.utils.JacksonSerializerHelper.writeValueAndStringValue(JacksonSerializerHelper.java:48)
  15. at org.bonitasoft.web.rest.server.utils.DataInstanceSerializer.serialize(DataInstanceSerializer.java:43)
  16. at org.bonitasoft.web.rest.server.utils.DataInstanceSerializer.serialize(DataInstanceSerializer.java:27)
  17. at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:250)
  18. ... 61 more
  19.  
  20. 2016-07-12 16:36:43.325 +0100 org.restlet.Component.Server org.restlet.engine.adapter.HttpServerHelper handle
  21. ADVERTENCIA: Error while handling an HTTP server call
  22. java.lang.IllegalStateException: No puedo llamar a sendError() tras llevar a cabo la respuesta

1
0
-1

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

1
0
-1

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

1
0
-1

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

1
0
-1

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"

  1. final List resultTable = new ArrayList();
  2.  
  3. int maxColumn = 5;
  4.  
  5. for(int colIndex = 1; colIndex < maxColumn ; colIndex++){
  6. row.add("col " + colIndex);
  7. }
  8. resultTable.add(Collections.unmodifiableList(row));
  9.  
  10. return resultTable

What I am doing wrong ?

Thx !!!

Comments

Submitted by kppatel on Thu, 06/12/2014 - 13:38

Hello , thanks for ans i successfully done to populate data from db to table . but i want to use html and provide border to table change heading color etc . how can i

Submitted by christopherf on Thu, 06/12/2014 - 13:44

Hi, kppatel, unfortunately, I have no experience with what you are requesting. The bit of work I did with Bonita did not involve any HTML, so I won't be able to assist. Sorry.

Submitted by daniel1984 on Thu, 08/28/2014 - 20:44

Cristofer please help me. I have a error in my process. Need fill a table from sql server but this message error

ADVERTENCIA: THREAD_ID=403 | HOSTNAME=ntbkdbarraza | TENANT_ID=1 | org.bonitasoft.engine.core.process.instance.api.exceptions.SActivityStateExecutionException : "PROCESS_DEFINITION_ID=8710241522510931473 | PROCESS_NAME=Pool_Consulta_Libro_Novedades | PROCESS_VERSION=1.0 | PROCESS_INSTANCE_ID=4 | ROOT_PROCESS_INSTANCE_ID=4 | FLOW_NODE_DEFINITION_ID=-8130366540862793297 | FLOW_NODE_INSTANCE_ID=8 | FLOW_NODE_NAME=Tarea1 | org.bonitasoft.engine.expression.exception.SExpressionEvaluationException: Expression lista4 with content = > resultTable = new ArrayList<List>();

int maxColumn = resultSet.getMetaData().getColumnCount()+1;

while(resultSet.next()){

final List row = new ArrayList();

for(int colIndex = 1; colIndex < maxColumn ; colIndex++){

row.add(resultSet.getObject(colIndex));

}

resultTable.add(Collections.unmodifiableList(row));}> depends on resultSet is neither defined in the script nor in dependencies."

1
0
-1

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.

Comments

Submitted by kppatel on Thu, 06/12/2014 - 08:02

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

thanks kandarp

Notifications