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
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
Hello,
you should transform your ResultSet into a List<List<Object>>
Something like:
int maxColumn = rSet.getMetaData().getColumnCount()+1;<br />
while(rSet.next()){<br />
final List<Object> row = new ArrayList<Object>();<br />
for(int colIndex = 1; colIndex < maxColumn ; colIndex++){<br />
row.add(rSet.getObject(colIndex));<br />
}<br />
resultTable.add(Collections.unmodifiableList(row));
}
Hope this helps
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
Hi, christopherf.
Could you post here your configuration for the connector? I have a similar issue.
Thansk a lot.
Bene
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 .
i can't understand what is exact process. please help
thanks
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:
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) <div pb-property-values="59c870ab-b357-45f3-b176-25f60338efb0" class="ng-scope">
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:
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
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
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 .
- Graphical mode
- 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
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"
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 !!!
Comments
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
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.
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 =
int maxColumn = resultSet.getMetaData().getColumnCount()+1;
while(resultSet.next()){
final List
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."
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
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:
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) <div pb-property-values="59c870ab-b357-45f3-b176-25f60338efb0" class="ng-scope">
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:
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