Populate Table widget with MS SQL data

1
0
-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 toAPPLICATION_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.

Comments

Submitted by romualdo.s.rs on Thu, 01/11/2018 - 18:15

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.

1 answer

1
0
-1

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".

Notifications