Populate Table widget with MS SQL data

Q&A
1

Hi Everyone,

I have trouble displaying the result of a SQL connector (SQL server 2012) request to a simple Table widget.

*This is the SQL request code *:
String sqlStatement = "SELECT APPLICATION_NAME FROM dbo.DepartmentApp where APPLICATION_TYPE = 'STD' "; return sqlStatement;

It returns the data as intended when I use “Evaluate”.

I then have a Groovy script to format the resultset to JSON :

import groovy.json.JsonBuilder; List<Object> dbResult = new ArrayList<Object>(); while(resultset.next()) { def jsonb = new JsonBuilder(); def line = jsonb { APPLICATION_NAME resultset.getString("APPLICATION_NAME") } dbResult.add(line); } return dbResult;

I pass this value to the process variable named “listeApplicationsStandard”, a java.util.Collection object. I made sure it is “multiple” as well.

In the UI, I am able to retrieve the data via a REST API call :
../API/bpm/caseVariable/{{caseId}}/listeApplicationsStandard

When I run the process, I can see it contains the values if I use a text field with {{listeApplicationsStandard.value}}. It displays the following data :
[{APPLICATION_NAME=Olympic Opérations C&I Standard}, {APPLICATION_NAME=Kynox Opérations C&I Standard}]

When I try to show the data contained in {{listeApplicationsStandard}} in a text field, it shows :
{"case_id":"168001","name":"listeApplicationsStandard","description":"","type":"java.util.Collection", "value":"[{APPLICATION_NAME=Olympic Opérations C&I Standard}, {APPLICATION_NAME=Kynox Opérations C&I Standard}]"}

However, I have been trying for days to display its content in a Table, following the different help topics I could find on the Bonitasoft forum, notably https://community.bonitasoft.com/node/21600#node-21610.

I was not able to get a single string displayed in the Table. I use applicationsStandard.value in the “content” field of the table and made sure to check the “fx” function for the content to be dynamic. The column key is set to APPLICATION_NAME

I feel the format of the JSON Groovy script is where the incident is, but I haven’t been able to solve this yet. I think there should be some quotes (" ", " ") in the values returned by the JSON builder. Does anyone see where my mistake is ? Any help will be greatly appreciated.

2

For anyone searching about this strange behaviour, I solved it by replacing :

../API/bpm/caseVariable/{{caseId}}/listeApplicationsStandard
by

../API/bpm/activityVariable/{{taskId}}/listeApplicationsStandard

All of a sudden, the variable contained the data formatted correctly in Json with the Value keys and Values correctly displayed in the table. It appears the caseVariable call is defective and/or limited.

Another way to tackle it if you have no choice is to use the “JsonOutput” method in your resulset Json builder :

while(resultset.next()) { def jsonb = new JsonBuilder() def line = jsonb { APPLICATION_NAME(resultset.getString("APPLICATION_NAME")) } def jsono = JsonOutput.toJson(line) dbResult.add(jsono) }

But even with a perfectly Json formatted string, this method would not carry all the data when using “caseVariable”.

3

Why do not you try moving to a BDM variable? This makes it much easier to display the contents of the UI Designer in a table.