Postgres query result (List of lists) fills only the first column in form Table

1
0
-1

I am a new to java and Bonita (although not to BPM) and I am trying to display a sql query result in a standard Bonita UI table version 7.1 (using the Table widget). But all the data ends up in the most left column like this example below:

1OWTwFcV.png

What did I do to get to this?

I defined a postgresql dataconnector, that shows a working Array when testing:

1OVozpJJ.png

The output (graphical n*n) is saved in a List type variable 'carsandcolors':

And in the UI designer I defined a variable 'Cars' that receives the data from 'carsandcolors' through the API:

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

I have made the following settings in de UI designer:

1OVE9dFx.png

Why are the subarrays listed in the first column only? And what can I do to fix this?

I have the feeling it has something to do with the Column Keys attribute in the UI designer, but I cannot find any information on how to use this attribute.

Any help is greatly appreciated!

11 answers

1
0
-1

You need to format the output data from the SQL from list array to JSON format, then everything will work...

You can look at the getting started guide Add Dummy Data for an example

regards
Sean

Comments

Submitted by robin.perner on Thu, 11/05/2015 - 16:31

Hi,
I have the same problem with the list java variable and the tablet widget.
Ok, I understand that i have to format the output data from the SQL to JSON format, but where i put the java code to format the data? I can create a variable in the UI Designer but just for put an expression, not to put a code to format the variable.
I can put the formatted data in a Connector but I dont understand in which type of variable I have to put it.
I stuck with this problem.
Thank you for your help.

Submitted by Sean McP on Tue, 11/03/2015 - 16:40

In you connector is probably best:

Create a pool variable of type String
In your SQL Connector the very last dialog

newVariable takes value of (pencil) this is where you would add your code to JSON your response.

Then use newVariable in the REST call to show in the table...

regards

Submitted by robin.perner on Thu, 11/05/2015 - 16:42

hi,

I tried to apply the format json to my variable, but doesn't work :(

this is what I did:

Create a new pool variable 'milistajson' (String type):
jzwbbl.jpg

and take the values from the last of connector:
261oksw.jpg

from this follow code: (take from Add Dummy Data excample:
20av4w5.jpg

this is the test for connector with the json format value:
4hfjbl.jpg

in the UI Designer, I declared the miLista2 varible (API external):
mvghz7.jpg

and put a text to verify the value in runtime time:
2r29o5k.jpg

I configured the tablet widget :
1441l3a.jpg

here the result, doesn't work, i don´t know what are i doing wrong :(
10cs29e.jpg

Thanks for your help

Submitted by Sean McP on Fri, 11/06/2015 - 08:46

Can you post the JSON, or at least check it with a JSON Checker, for example here:

http://jsonlint.com/, or here
https://jsonformatter.curiousconcept.com/

Maybe the JSON is invalid...

If you post the JSON I can try it here in a table,

regards

Submitted by stefandebruijn on Fri, 11/06/2015 - 11:07

Hi Sean,

Thanks for your reply. However, it is still not working....

1VJyIMhY.png

testdata: [{"brand":"Ferrari","color":"Red"},{"brand":"Ford","color":"Black"},{"brand":"Toyota","color":"White"}]

Submitted by Sean McP on Fri, 11/06/2015 - 10:28

Excellent Diagram by the way...

The JSON you posted is invalid according to the JSON Formatters...

Could this be the problem?

You are missing a {

regards

Submitted by stefandebruijn on Fri, 11/06/2015 - 11:09

Thanks for your reply.

It was just a copy/paste error... Sorry.

I fixed it in the previous post.

Submitted by robin.perner on Fri, 11/06/2015 - 17:03

thank you Sean McP for your support.

Stefandebruijin, sorry for intruding on your post. We have the same problem. :(

here I export my proyect with the same problem of stefandebruijin's project.:
link
Pass: !9M1SlvE37rG-_E_XIN290F5562uP29AHW1FZM7E12Mg

a lot of thanks.

Submitted by stefandebruijn on Sat, 11/07/2015 - 23:41

Hi robin.perner and Sean McP,

I found it! The string has to be transformed to a list of map objects. You can use the groovy JsonBuilder function for it.

I got to the following result:

1Y5yr8xg.png

With this script:
1Y6u08p5.png

It works on an table as well, with this:
1Y6OO97N.png
1Y6GzrbG.png

Let me please know if it works.

Sean, many thanks for your help!

Regards, Stefan

Submitted by robin.perner on Mon, 11/09/2015 - 21:40

Hi Stefan,

Thank you so much for your help!!. I tried many many different ways for do it, but nothing worked :(.
This time Works fine, only I have to adjust the variable type, with java.util.collection type in the process variables panel, and that's all.

Regards Robin

Submitted by sprabakkar on Thu, 02/04/2016 - 19:51

Hello Stefandebruijn,

You solution works very well, I used in mysql 5.6+bonita community 7.1.5, got the same issue as you had before, but your solution is perfect cure for this, thank you so much for your contribution....

Submitted by monicamegaro on Thu, 06/23/2016 - 16:56

Hello Stefandebruijn,

You solution works very well,thank you so much

Submitted by DrakunDry on Sat, 09/17/2016 - 17:36

hello i have always same result
MvfHIgt.png

m6BNMf0.png

my output
ZO3ygcL.png

log file
b0upQrO.png

Submitted by mbolanos_1 on Mon, 06/05/2017 - 16:47

Hello DrakunDry,
At this time you probably already solved your problem, but for the community... I just wanted to point out that there is an error in your script:

The variable "jsonb" was defined before the While setence. The variable "jsonb" must be defined after the While and also the line before of the "line" variable definition.
Hope it helps others.

Actually, the post from stefandebruijn tells the correct form of creating your script.

Happy coding!

Notifications