How do you display a business variable list (containing SQL data) in the UI table widget?

Q&A
1

Hello

I’m quite new to Bonitasoft. I’m currently using the Bonitasoft 7.4.3 Community Edition, to use as a POC to show to my manager the capabilities of Bonitasoft in terms of a BPM offering.

I’m having issues with displaying a business variable list containing data from a MS SQL table into the UI table widget. I’ve gone through the tutorials on the UI and have looked at all questions and answers containing the linking of external data and displaying the data in the UI. Below is what I’ve done.

On the second step called Get Private Clients from SQL I use a service task to retrieve my data from MS SQL.

I have a business variable called privateSQLDBList , whereby I use this variable to store all my data from SQL containing client information. I have a connector called getPrivateClientsFromSQLDB whereby I’ve inputted database access information correctly, and I’ve indicated a script query to get all my data from a SQL table with the required columns (as seen below).

String query = “SELECT [CaseID]” +
“,[FullName]” +
“,[GivenNames]” +
“,[FamilyName]” +
“,[NameType]” +
“,[Gender]” +
“,[IdentityNumber]” +
“,[IdentityType]” +
“,[DateOfBirth]” +
“,[CountryOfBirth]” +
“,[Nationality]” +
“,[CountryOfResidence]” +
“,[DateAdded]” +
“,[DateLastUpdate]” +
“,[Status]” +
“,[Reason]” +
“,[UserID]” +
“,[UserName]”+
“FROM [BONITA_BPM].[dbo].[ClientDetails]”;

I’ve also inputted a groovy script to create an array to store the multiple rows of data and aligning each item to a defined bdm variable (as seen below).

import groovy.json.JsonBuilder;

List<List> result = new ArrayList<List>();

while (resultset.next()){
def builder = new JsonBuilder();
def line = builder{
caseId Long.valueOf(resultset.getInt(1));
fullName resultset.getString(2);
givenNames resultset.getString(3);
familyName resultset.getString(4);
nameType resultset.getString(5);
gender resultset.getString(6);
identityNumber resultset.getString(7);
identityType resultset.getString(8);
dateOfBirth resultset.getDate(9);
countryOfBirth resultset.getString(10);
nationality resultset.getString(11);
countryOfResidence resultset.getString(12);
dateAdded resultset.getDate(13);
dateLastUpdate resultset.getDate(14);
status resultset.getString(15);
reason resultset.getString(16);
userID Long.valueOf(resultset.getInt(17));
userName resultset.getString(18);
}

result.add(line);
}
return result;

When I test the output, it brings back my array of data from SQL.

On my Review Private List UI step, I have a table to display the all the client data from SQL. I’ve indicate the headers and column keys according to data in SQL and the content is indicated as processDef.value . I’ve created a variable called processDef as an external API with the following:
…/API/bdm/businessDataReference/{{caseId}}/privateSQLDBList
as well as a caseId external API varaible with the following details: …/API/bpm/humanTask/{{taskId}}.

My problem is that the table isn’t bringing back the business variable list containing my SQL data. I don’t know what exactly I’m missing. Your assistance would be highly appreciated.

Below is the link to my .bos file

A link.

Regards
Martin

2

I don’t recommend to duplicate data that are already stored in your existing SQL Server database into the BDM. BDM will also store the data in a database and you probably don’t want to have this duplication. You can use the data source REST API extension to create a REST API that will query your SQL Server database directly. In your form you can create a variable that will call the REST API extension.

If I get back to your current issue, it’s probably related to the way you are initializing the privateSQLDBList business variable with the output of getPrivateClientFromSQLDB. In your Groovy script (tableResult) you create a List of List (List) but the expected data type is a list of your business object (List).

You can check the Bonita BPM Engine log in the Studio help menu. It probably display some stack trace about this error.

3

Thank you antoine, much appreciated for the response. Yes, you are correct duplicating the data doesn’t make sense, but this is just to show that data will be dispalyed from an external database, going forward, I would probably use the external database (being MS SQL) as the default database to store data.

Thanks for the data source REST API extension option, will try that. I’ll also look at the initialising of the privateSQLDBList and pay attention to the BPM Engine logs.