I can't populate or display data from My Sql DB in a Custom Widget Table(JSON)

Q&A
1

Hi all

I am a newbie at Bonita 7.2, struggling with displaying values, retrieved from a My SQL DB, in a custom table Widget. I need a little help, please.

Fist thing I did was Show it in a normal table widget, and it works, but then I realized that I need a custom table because I have to show a detail of the row that a I selected in a pannel below the table. And this I think that can’t be done with a conventional table.
My Doubt is If I have to use collection, or text parameter/properties to pass the list of values that I want to display in the custom table. The first step is to show something in the custom table Widget, and I can’t do this.

Simple Template code of the custom widget:

Id Reporting Entity Reporting entity name Departament Job Profile Reporters code First Name
{{ model.id }} {{ model.entidad_reportante }} {{ model.nombre_entidad_reportante }} {{ model.departamento }} {{ model.perfil_reportante }} {{ model.codigo_reportante }} {{ model.nombre }}

Very simple Controller Code:

function ($scope) {

  $scope.datosTabla = $scope.properties.datosTablaLocal;
}

The propertie: $scope.properties.datosTablaLocal; is a collection.

I also think that the problem could be in the Groovy script asociated with the data base conector, I have try two types of code to create the collection that I pass to the widget.
This is my actual Groovyscript code of the conector DB:

List milista1 = new ArrayList();
//* mcampo1 y mcampo2 son los nombres que yo utilice para este ejemplo, puede ser cualquier nombre y
// cualquier cantidad de campos

	def builder = new JsonBuilder();

	while(resultset.next()){

	// en va el numero de la columna o registro de nuestra consulta, que tiene el valor
	// que queremos poner en este campo de milista, igual con el 2

		def lin1 = builder {
		  id resultset.getObject(1).toString()
		  entidad_reportante resultset.getObject(2).toString()
		  nombre_entidad_reportante resultset.getObject(3).toString()
		  departamento resultset.getObject(4).toString()
		  perfil_reportante resultset.getObject(5).toString()
		  codigo_reportante resultset.getObject(6).toString();
		  nombre resultset.getObject(7).toString();
		 }
		milista1.add(lin1);		
	}
	loggerJava.info("Saliendo list reportes")
	return milista1;

Another groovy script that I have tryed

final List<List> 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));
}
loggerJava.info(“Sale list reportes”);
return resultTable;

JSON representatinos that I’m displaying in the form in a text field:
JSON from the first groovy script:

[
{
“id”: “1”,
“entidad_reportante”: “Spain Other Aircraft Ground Handling Organization”,
“nombre_entidad_reportante”: “Atlantica”,
“departamento”: “Operaciones Vuelo”,
“perfil_reportante”: “Tripulante de Vuelo”,
“codigo_reportante”: “Y15678909”,
“nombre”: “Marta”
},
{
“id”: “2”,
“entidad_reportante”: “Spain Other Aircraft Maintenance organization”,
“nombre_entidad_reportante”: “Bintertechnic”,
“departamento”: “Control Operacional”,
“perfil_reportante”: “Officer”,
“codigo_reportante”: “X34538909”,
“nombre”: “Carlos”
}
]

Json resulting from the second groovy script:

[
[
1,
“Spain Other Aircraft Ground Handling Organization”,
“Atlantica”,
“Operaciones Vuelo”,
“Tripulante de Vuelo”,
“Y15678909”,
“Marta”,
“López Sánches”,
“mlopez@naysa.es”,
1,
111,
1,
“11”,
1467846000000,
“12:00”,
“2016-07-07 00:00:00”,
“Narrativa”,
“es”,
“adjuntos”,
“tipo reporte”,
1467846000000,
“C78715649”,
1467846000000,
“G45678098”,
1467846000000,
“Rapida”,
1
],
[
2,
“Spain Other Aircraft Maintenance organization”,
“Bintertechnic”,
“Control Operacional”,
“Officer”,
“X34538909”,
“Carlos”,
“Cardenas Bermudez”,
“ccardenas@naysa.es”,
1,
111,
1,
“11”,
1467846000000,
“12:00”,
“2016-07-07 00:00:00”,
“Narrativa”,
“es”,
“adjuntos”,
“tipo reporte”,
1467846000000,
“C78715649”,
1467846000000,
“G45678098”,
1467846000000,
“Rapida”,
1
]
]

One of the errors that I get, is a problem with JSON:

SyntaxError: Unexpected end of JSON input
at Object.parse (native)
at tc (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/vendor.min.js:14:245)
at bc (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/vendor.min.js:77:7)
at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/vendor.min.js:77:369
at q (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/vendor.min.js:7:322)
at dd (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/vendor.min.js:77:351)
at c (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/vendor.min.js:78:495)
at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/vendor.min.js:112:182
at m.$eval (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/vendor.min.js:126:250)
at m.$digest (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/vendor.min.js:123:365)

Another erro show in t browser log:

vendor.min.js:102 the pbInput property named “value” need to be bound to a variable

This another error showed in the browser log:

vendor.min.js:102 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.4/RevisarCamposNotificacion/content/js/vendor.min.js:6:417
at lb.throwError (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/vendor.min.js:192:113)
at lb.parse (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/vendor.min.js:190:406)
at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/vendor.min.js:110:366
at new n (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/runtime.min.js:1:10017)
at Object.create (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/runtime.min.js:1:8209)
at http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/runtime.min.js:1:12401
at Array.forEach (native)
at Object.create (http://localhost:8080/bonita/portal/resource/taskInstance/RegistroSimpleNotificacion/5.4/RevisarCamposNotificacion/content/js/runtime.min.js:1:12373)


This is the error that you get in bonita log:

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)
at org.restlet.ext.jackson.JacksonRepresentation.write(JacksonRepresentation.java:464)
at org.restlet.engine.adapter.ServerCall.writeResponseBody(ServerCall.java:510)
at org.restlet.engine.adapter.ServerCall.sendResponse(ServerCall.java:454)
at org.restlet.ext.servlet.internal.ServletCall.sendResponse(ServletCall.java:421)
at org.restlet.engine.adapter.ServerAdapter.commit(ServerAdapter.java:187)
at org.restlet.engine.adapter.HttpServerHelper.handle(HttpServerHelper.java:144)
at org.restlet.ext.servlet.ServerServlet.service(ServerServlet.java:1117)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.tuckey.web.filters.urlrewrite.RuleChain.handleRewrite(RuleChain.java:176)
at org.tuckey.web.filters.urlrewrite.RuleChain.doRules(RuleChain.java:145)
at org.tuckey.web.filters.urlrewrite.UrlRewriter.processRequest(UrlRewriter.java:92)
at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:394)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.bonitasoft.console.common.server.login.filter.AbstractAuthorizationFilter.doFilter(AbstractAuthorizationFilter.java:60)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:747)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:485)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:410)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:337)
at org.bonitasoft.console.common.server.page.PageServlet.service(PageServlet.java:77)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.tuckey.web.filters.urlrewrite.RuleChain.handleRewrite(RuleChain.java:176)
at org.tuckey.web.filters.urlrewrite.RuleChain.doRules(RuleChain.java:145)
at org.tuckey.web.filters.urlrewrite.UrlRewriter.processRequest(UrlRewriter.java:92)
at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:394)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.bonitasoft.console.common.server.login.filter.AuthenticationFilter.isAuthorized(AuthenticationFilter.java:152)
at org.bonitasoft.console.common.server.login.filter.AuthenticationFilter.doAuthenticationFiltering(AuthenticationFilter.java:134)
at org.bonitasoft.console.common.server.login.filter.AuthenticationFilter.doFilter(AuthenticationFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.bonitasoft.console.security.SessionFixationValve.invoke(SessionFixationValve.java:77)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
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

Please, any assistance would be greatly appreciated, I’ve been stuck in this many days and I need to go ahead, some said that can’t help me more because of copy rigth, but I only need some orientations.

Kind regards,
Héctor

2

I save my data in a java object collection, in my groovy script i setup in a josn form from a result of a query:

import groovy.json.JsonBuilder;

List milista1 = new ArrayList(); //mcampo1 y mcampo2 son los nombres que yo utilice para este ejemplo, puede ser cualquier nombre y // cualquier cantidad de campos

def builder = new JsonBuilder();

while(resultset.next()){

def lin1 = builder {
campo1 resultset.getObject(1).toString()
campo2 resultset.getObject(2).toString()
campo3 resultset.getObject(3).toString()
campo4 resultset.getObject(4).toString()
campo5 resultset.getObject(5).toString()
campo6 resultset.getObject(6).toString()
campo7 resultset.getObject(7).toString()
campo8 resultset.getObject(8).toString()
campo9 resultset.getObject(9).toString()
campo10 resultset.getObject(10).toString()
campo11 resultset.getObject(11).toString()
campo12 resultset.getObject(12).toString()
campo13 resultset.getObject(13).toString()
campo14 resultset.getObject(14).toString()

}

milista1.add(lin1);
}

return milista1;

Sorry for my bad inglish

3

Hi Julian, i understand you perfect, but I also speak spanish if you want. I have try the code that you gave me:

try { 
   loggerJava.info("Entra list reportes custom");

	List milista1 = new ArrayList();
	//* mcampo1 y mcampo2 son los nombres que yo utilice para este ejemplo, puede ser cualquier nombre y
	// cualquier cantidad de campos

	def builder = new JsonBuilder();

	while(resultset.next()){

	// en va el numero de la columna o registro de nuestra consulta, que tiene el valor
	// que queremos poner en este campo de milista, igual con el 2

		def lin1 = builder {
		  id resultset.getObject(1).toString()
		  entidad_reportante resultset.getObject(2).toString()
		  nombre_entidad_reportante resultset.getObject(3).toString()
		  departamento resultset.getObject(4).toString()
		  perfil_reportante resultset.getObject(5).toString()
		  codigo_reportante resultset.getObject(6).toString();
		  nombre resultset.getObject(7).toString();
		 }
		loggerJava.info("Reporte custom lin1: "+lin1);
		milista1.add(lin1);
	}
	loggerJava.info("Saliendo list reportes custom")
	return milista1;

Error1:

The pbUpload property named “value” need to be bound to a variable

Error2:

SyntaxError: Unexpected end of JSON input
at Object.parse (native)

But it doesn’t works, I got some errors.
Do you know this errors?

This is the link to the version in Spanish if you want to speak spanish:
http://community.bonitasoft.com/questions-and-answers/problemas-al-mostrar-un-widget-personalizado-con-una-tabla-no-muestra-valores

Your code works for me only in a Standar widget table, the default of UI Designer, but with the custom one doesn’t, may be I am doing something wron in the angular code?

Thank you very much