Database connector

Submitted by jason.hastain on Wed, 09/14/2016 - 12:05
1
0
-1

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!

Comments

Submitted by antoine.mottier on Thu, 09/15/2016 - 09:47

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.

Permalink
Submitted by DrakunDry on Thu, 09/15/2016 - 11:20

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}}

Permalink
Submitted by FatmaGh on Tue, 09/15/2020 - 17:18

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?

Permalink
1 answer

Submitted by DrakunDry on Wed, 09/14/2016 - 15:01
1
+1
-1

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

Comments

Submitted by jason.hastain on Thu, 09/15/2016 - 07:41

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?

Permalink
Submitted by DrakunDry on Thu, 09/15/2016 - 11:21

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}}

Permalink
Submitted by jason.hastain on Sat, 09/17/2016 - 05:02

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!

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

very cool,
now u can try it
source 1

Source 2

Permalink
Submitted by DrakunDry on Mon, 09/19/2016 - 21:55

Ok wait i share my exemple bos file

Permalink
Submitted by DrakunDry on Mon, 09/19/2016 - 22:52

my dos example [BOS FILE](https://1drv.ms/u/s!AsIz63O_TfSiiHLytuGGYVj1doqt)

![imagess](https://i.imgur.com/C482PvJ.png)
<code>

import groovy.json.JsonBuilder;

List<Object> productTable = new ArrayList<Object>();
def jsonb = new JsonBuilder();
while(resultset.next()){
def line = jsonb {
name(resultset.getString("Name"))
serial(resultset.getString("serial"))
}
productTable.add(line);
}

return productTable;
</code>

<!-- Images -->
[url]: https://i.imgur.com/C482PvJ.png

result
i have always = sign not :
![result](https://i.imgur.com/IxuZ53l.png)

Permalink
Submitted by jason.hastain on Tue, 09/20/2016 - 05:08

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

Permalink
Submitted by antoine.mottier on Tue, 09/20/2016 - 09:41

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.

Permalink
Submitted by jason.hastain on Tue, 09/20/2016 - 10:17

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

Again i really really appreciate all your efforts!

Permalink
Submitted by DrakunDry on Tue, 09/20/2016 - 12:08

i wait to approve access for download file . :-)

Permalink
Submitted by DrakunDry on Tue, 09/20/2016 - 12:09

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

Permalink
Submitted by antoine.mottier on Tue, 09/20/2016 - 15:43

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

Permalink
Submitted by DrakunDry on Tue, 09/20/2016 - 16:48

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

Permalink
Submitted by DrakunDry on Tue, 09/20/2016 - 17:43

can u try it : yourDiagram

Permalink
Submitted by DrakunDry on Wed, 09/21/2016 - 13:38

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<String> myList = new ArrayList<String>();
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\"}]" }

Permalink
Notifications