Database connector

[jason.hastain]

Aloha all
SUper N00b when it comes to Bonita.

I have a process that presents webforms and captures user input. This works. Followed Bonita tutorial to get it all working.

I want to be able to query a MySQL database. And fro right now just display the data in a form.

So i created a connector. When i run the “test” on the connector it pulls the data from my database. So i know that is working.
During the last step of that connector wizard I am assinging that data to a Business Data Object I defined in the Pool variables
’
When I try to reference that Pool variable in the same way I have done with others it dos not display the data.

on the form I am referencing using an API to a variable
request=…/{{context.serverData_ref.link}}
this variable only has 2 data elements for now, Name and Serial.
When i reference request.Name in a table it does not show any data.

I tried following videos and articles but to no avail.
What i need are these things:

How do I store connector queries into a variable
How do i display that variable data on a form.

If I had that i would be golden.

I am also in a Catch 22 position here. My company wants to purchase Bonita but needs to see a working example first.
When I explained that the the Bonita sales their response was “we cant give free support”

so…

I do appreciate any help the community can give me!

[DrakunDry]

hi, how are you.
u can try to display your variable request…

var name = request var type = External API var value = ../{{context.serverData_ref.link}}

use text widget avec put it {{request | json }}
u will your variable content
after . you will use {{request.Name}} and {{request.Serial}}

Good luck

[system]

Hi,

Note that the output of a database connector is a Java ResultSet object. So you cannot map it directly to your business data. You need to use a Groovy script to do the transformation.

Maybe if you share your process (.bos file) and an SQL script to replicate your database schema I can try to fix the process.

Also keep in mind that with such approach you are duplicating data from your MySQL database to the database used to store the business data. This might not be what you want. In order to display data from your MySQL database in your form without any duplication you can use a REST API extension that will query your database. A SQL data source REST API extension is available as a community contribution. You can learn more about REST API extension deployment in the documentation.

[DrakunDry]

Thanks Antoine for links

You can put the result of the query into a variable, and you use this variable by API. example
In Bonita Studio
puts the results of your query in a varible (eg myReq)
In UI Designer
var name = myResultSQL
var type = external API
var value = /API/bpm/caseVariable/{{task.caseId}}/myReq

Uses a text widget and displays the content of {{myResultSQL | json}}

[FatmaGh]

I tried to follow your steps, but when I try to preview my form to see the retrieved data, an error 500 is caught! The id of the task is not found in the URL !
Why is that?

[jason.hastain]

DrakunDry Thanks fro that answer! I see where I may be able to translate what you said into the Form Editor. I was curious where I would see the raw code similar to what you described?

[DrakunDry]

You can put the result of the query into a variable, and you use this variable by API. example

In Bonita Studio

puts the results of your query in a varible (eg myReq)

In UI Designer

var name = myResultSQL
var type = external API
var value = /API/bpm/caseVariable/{{task.caseId}}/myReq

Uses a text widget and displays the content of {{myResultSQL | json}}

[jason.hastain]

DrakunDry
Thanks that worked!!

{ “description”: “”, “name”: “serverList”, “value”: “[[SandboxTest01, ts00001], [SandboxTest02, ts00002], [SandboxTest03, ts00003], [SandboxTest04, ts00004], [SandboxTest05, ts00005], [ts00006, ts00006], [SandboxTest07, ts00007], [SandboxTest08, ts00008], [SandboxTest09, ts00009]]”, “case_id”: “88”, “type”: “java.util.ArrayList” }

So here is the return in that text box.
I would like to get this data into a table.
I tried assigning to a table as in the getting started tutorial but was unsuccessful.

Im pretty sure there is going to be a For Each statement in a groovy script somewhere to convert this data to a different data structure. but not sure how to do that.

Like i said SuperNoob!
thank youjf ro the help so far it has been very useful!

[DrakunDry]

very cool,
now u can try it
source 1

Source 2

[DrakunDry]

Ok wait i share my exemple bos file

[DrakunDry]

my dos example BOS FILE

import groovy.json.JsonBuilder;

List productTable = new ArrayList();
def jsonb = new JsonBuilder();
while(resultset.next()){
def line = jsonb {
name(resultset.getString(“Name”))
serial(resultset.getString(“serial”))
}
productTable.add(line);
}

return productTable;


result
i have always = sign not :

[jason.hastain]

Thank you again so much! I will try this tonight!

[system]

Can I kindly ask you to use “comment” on the existing answer for the discussion? This help to keep the thread tidy and make it easier to read. Posting a new “answer” is useful to suggest another possible solution.

[jason.hastain]

So, Having problems implementing this solution.

I have tried adding that script to the data connector, but it gives me a type mismatch when i do even though they are both “Collections”

Tried it as an Operation but the process fail out at that point. Couldn’t find any logs or anything to give me an idea of why.

Is the data being provided JSON? I note you are using the jsonBUilder function.
I tried several variations on that script in different spots but to no avail :(.

I included my BOS file. Maybe you can take a look and provide some feedback
https://drive.google.com/a/mail.1010data.com/file/d/0B1PDnQYw-k__cThobnM5YjRCRmM/view?usp=sharing

Again i really really appreciate all your efforts!

[DrakunDry]

i wait to approve access for download file .

[DrakunDry]

Antoine, i dont see a button to delete answer ???

[system]

Sadly there is a limitation right now on this forum and only administrators can delete a post. I deleted your empty answer.

[DrakunDry]

hi jason , can u export just one process without bdm because i want increase my bdm

[DrakunDry]

can u try it : yourDiagram

[DrakunDry]

wow very very tired to try

import java.util.logging.Logger;

import groovy.json.JsonBuilder;
import groovy.json.JsonOutput;
Logger logger= Logger.getLogger(“org.bonitasoft”);
logger.severe(“Trace Start”)
List list = new ArrayList();
List myList = new ArrayList();
def jsonb = new JsonBuilder();
while(resultset.next()){
def line = jsonb {
name (resultset.getString(“Name”)).toString()
serial (resultset.getString(“serial”)).toString()
}

list.add(jsonb.toString());
myList.add(jsonb.toString());

}

logger.severe("LIST ------------- LIST "+ list)
logger.severe(“MYLIST ------------- LIST \n”+ myList)
return myList;


résultat on uidesigner

PISCO.VALUE

“[{"name":"SandboxPRODUCTS01","serial":"ts00001"}, {"name":"SandboxPRODUCTS02","serial":"ts00002"}, {"name":"SandboxPRODUCTS03","serial":"ts00003"}, {"name":"SandboxPRODUCTS04","serial":"ts00004"}, {"name":"SandboxPRODUCTS05","serial":"ts00005"}, {"name":"SandboxPRODUCTS06","serial":"ts00006"}, {"name":"SandboxPRODUCTS07","serial":"ts00007"}, {"name":"SandboxPRODUCTS08","serial":"ts00008"}, {"name":"SandboxPRODUCTS09","serial":"ts00009"}]”

**PISCO **

{ “case_id”: “7020”, “name”: “pisco”, “description”: “”, “type”: “java.util.Collection”, “value”: “[{"name":"SandboxPRODUCTS01","serial":"ts00001"}, {"name":"SandboxPRODUCTS02","serial":"ts00002"}, {"name":"SandboxPRODUCTS03","serial":"ts00003"}, {"name":"SandboxPRODUCTS04","serial":"ts00004"}, {"name":"SandboxPRODUCTS05","serial":"ts00005"}, {"name":"SandboxPRODUCTS06","serial":"ts00006"}, {"name":"SandboxPRODUCTS07","serial":"ts00007"}, {"name":"SandboxPRODUCTS08","serial":"ts00008"}, {"name":"SandboxPRODUCTS09","serial":"ts00009"}]” }