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

**URL:** <https://community.ofelia.com/t/i-cant-populate-or-display-data-from-my-sql-db-in-a-custom-widget-table-json/7601>\
**Category:** Q&A\
**Created:** [14 July 2016 09:01 UTC](https://community.ofelia.com/t/i-cant-populate-or-display-data-from-my-sql-db-in-a-custom-widget-table-json/7601 "2016-07-14T09:01:17Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![hectorta](https://avatars.discourse-cdn.com/v4/letter/h/a87d85/32.png) [@hectorta](https://community.ofelia.com/u/hectorta)\
**Post date:** [14 July 2016 09:01 UTC](https://community.ofelia.com/t/i-cant-populate-or-display-data-from-my-sql-db-in-a-custom-widget-table-json/7601/1 "2016-07-14T09:01:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![josorio\_2](https://avatars.discourse-cdn.com/v4/letter/j/b38774/32.png) [@josorio\_2](https://community.ofelia.com/u/josorio_2)\
**Post date:** [19 July 2016 13:39 UTC](https://community.ofelia.com/t/i-cant-populate-or-display-data-from-my-sql-db-in-a-custom-widget-table-json/7601/2 "2016-07-19T13:39:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![hectorta](https://avatars.discourse-cdn.com/v4/letter/h/a87d85/32.png) [@hectorta](https://community.ofelia.com/u/hectorta)\
**Post date:** [19 July 2016 15:25 UTC](https://community.ofelia.com/t/i-cant-populate-or-display-data-from-my-sql-db-in-a-custom-widget-table-json/7601/3 "2016-07-19T15:25:09Z")

</div>

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.ofelia.com/questions-and-answers/problemas-al-mostrar-un-widget-personalizado-con-una-tabla-no-muestra-valores](http://community.ofelia.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