How to retrieve a value from Oracle database when a process initiates?
Hi,
new to Bonita, any help in the following question is appreciated.
I would like to start with a process so n that it retrieves a numeric value from the Oracle database. The retrieval does not require any input from the user, the process would then know what to do when it has the start value, e.g. display the value on form.
As a simple example, the query could retrieve a random number from the database and then the process can determine if its even or odd number, display the result and continue from there.
I can connect to the database, so that part works - new to Bonita, I just don't know how to get the value at the beginning of the process. If someone would have an example or give some guidance how to achieve this that would be great.
The versions I'm using is
-
- Bonita Community version 7.2.2.
-
- Oracle 12c (but the example could be using any database, e.g. MySQL)
Thanks,
ktp
If you want to use the value in a task form
If you can, use Business Data Model to store your business data into a database. Bonita offers a REST API to fetch value from the BDM database ( documentation for bdm api ).
If you don't want (or can't) use BDM, use a connector and store the result into a process variable. You can then get the value on the form using the REST API ( see the documentation for more information )
If you want to use the value in an instanciation form
Connectors at the pool level are executed after the instanciation form has been submited. So you can't use a connector to retrieve some data from a database and display it in an instanciation form.
If you can, use Business Data Model to store your business data into a database. Bonita offers a REST API to fetch value from the BDM ( documentation for bdm api ).
If you don't want (or can't) use BDM, you'll need to create your own REST API to fetch data from your database. Bonita provides an interface to do so ( see here for details ). Then, call your API to get the value on the form.
Hi,
ok, thank you - now I guess this starts to slowly sink in. I had assumed that I can create and populate the BDM from the database and then pass it as a variable to the form. The BDM would then be used to initialise the form.
This is obviously wrong so I'll try next to do what you suggested, split the process into two processes, where the first process gets the data (from the database) e.g. using REST and then have a second process that then interacts with the user.
I'll will now mark this answered but as I manage to get a working example of this done, I'll try to post it to the forum as it may help others.
Thank you all again for your help.
Kari
Hi,
I did find the following example "process-variable-example"
https://github.com/Bonitasoft-Community/process-variable-example/release...
that allows me to do at least some of the things I had hope. There is process variable to which I retrieve the value and pressing submit the process starts.
Hope this helps.
kp
Quentin,
thank you for your reply - just one more question more - I'm a little bit unsure how to get the data to the BDM - could I use SQL to retrieve the value from the Oracle database?. Or can I pass it as a parameter in the URL that will open the process?
Just as background information in case someone is doing similar things - my plan is to link Bonita BPM to an existing windows application. The application is a client/server application due its nature (in any given organisation only a handful of users use it. The business environment is also such that a browser or similar application cant be used - hence the fat C/S approach).
The application is very much database oriented so retrieving and updating data. The user has to login, so we know who the user is. The idea is that Bonita will initiated within the windows application so we also use what the user was doing when he initiated Bonita.
Hence the request of starting a process with already quite of information available.
The process would then do it's thing and most likely at the end point also store something in the database.
Any ideas or examples are welcomed and Quentin, once more thank you for your help, I'll try this using the links you sent.
ktp
Comments
The data specified in the Business Data Model is stored in a database managed by the Bonita Engine. You don't have much control over this database. If you have an existing database, already used by another application, I'd recommend to use standard connectors inside the process and not to use the BDM tools.
Had the exact same problem with an instanciation form and I used a custom rest api to do the work. The git code is easy to reuse once you get used to groovy!
Another solution is to have a separate process that syncs your Oracle database with the BDM. You can use standard connectors to do this.
What are you trying to achieve ? Sync your oracle database with the BDM database ?
I echo Quentin's thought,
What is it exactly you want to accomplish. If you can be as clear as possible.
My understanding is:
- User clicks a link to open the process initialization form
- On the form i want to display a value taken from an Oracle Database
- The user will then click the submit button to be forwarded to the next task in the process.
If you can get it as clear as this, then we can help.
You may be over thinking things and making them more complex than they need to be, as in Oracle and BDM etc...
regards
Seán
Some screenshots....
This is my simple BDM, with two text fields.
This is the SQL used to retrieve one record two string fields from the database
This is the Groovy script I tried to use to parse the result set to the BDM
This is the form (in UI designer)
One (failed) attempt to get data from the BDM to display in the form
O.....K.....
Quentin has already said some of this before.
Before i go too far you should understand this: from: http://documentation.bonitasoft.com/execution-sequence-states-and-transactions-0
Process instantiation form interaction
Initialization
2. The user enters data in the form.
3. The button of the form having the Start process action in the UI designer send a POST request with the form data to start the process.
4. The submitted data are validated against the process instantiation contract.
5. If the contract is valid the process instance is created and input values are persisted in database.
6. Process data instances are created and initialized in the order of declaration using
* An operation declared on the process that saves its value to the process data
* The initial value expression if no operation is declared
* null if no expression or operation is defined
7. SP only The Engine creates and initializes search indexes.
8. The Engine creates and initializes business data.
9. The Engine initializes process instance Documents with default value coming from the definition or with submitted files.
10. Operations (that does not initialize data) are executed.
11. Completing the instantiation The Engine instantiates and executes the "on enter" connectors (evaluate input expression, execute, evaluate output operation). This an asynchronous operation. Execution duration of a connector is limited to a maximum of 5 minutes by default.
12. The process is instantiated, the API call finish and the Engine executes the process flow asynchronously.
SO what does this all mean:
The Initialization form will be shown first. There will be NO NEW BDM or variables available to access, 1 is followed by 8, not the other way round.
Why is this the case? Simple how many times have you started something, like a process, and then realized when you see the first form this is not what I want, I want X. Bonitasoft creates NO process at Initialization Form stage - only after clicking Submit.
If you need data either from a Database or a BDM they must exist before you instantiate your form.
Indeed I had a brain freeze moment recently on this, which Quentin helped with, thanks Quentin.
Here is an example I use
I need a drop down list on my Initialization form, this is a list of countries we deal with.
This list is kept via it's own CRUD process, which creates a new BDO each time it's updated.
NOTICE - I have TWO processes. The process I want to use the data in, and the process where I CRUD the data in the first place. It is not possible to do this in one process.
And by this I mean you may have to consider reviewing your process and redesigning them.
BDO wise
The way to get the DDL populated from the BDO is to REST call against a unique query (select DDL from listOfCountries order by persistanceID DESC), with a count of 1.
Database wise
the way to get the DDL populated is to use a combination of Javascript and AJAX as previously shown.
My problem to some extent is also that you started the question based on Oracle, now you are firmly in the BDM camp, and i think you're trying to do too much in one process when it would be better designed as two simpler ones.
regards
Seán
He could also use an extension to the REST api to retrieve data needed for the initialization form. This is what I did to retrieve a list of countries from our ERP. It avoids duplicating and syncing data from one source to the BDM.
Hi,
may I trouble you once more as I'm trying to get my head around this issue how to get some default values to the form, before the user actually starts the process. The idea is that as I'm calling Bonita from an application I already know quite a lot and can show that data to the user. The user can then decide if he wants to start the process or not.
One solution would be to get the data to the form passing it as parameters in the URL but it wouldn't be the optimal solution as there may be a number of parameters.
So I have returned to the idea of having process variables (not BDM) at the Pool level and trying to find the right REST API call to use.
Below is my form and as you can see the TaskID is coming from the URL as parameter(id).
Do I have at this point access to the process variables, e.g. PoolProVar by using e.g. API?
My thinking is that if the user decides to start the process I can then later use these process variable.
Thanks,
kari
Process variables suffer the same problem: when you are on the instanciation form the process is not started and you have no processs variable, no BDM, nothing!
One solution however is to have an empty instantiation form, start the case using the REST api from your application and redirect the user to the first human task (do not forget to assign it to the user using another REST api call). Since you have an empty instantiation form you can use a process connector to populate process variables or your BDM and the use them in your human task.
From a developer perspective I would tend to use the parameter solution as it is much more straightforward. If you have a lot of parameters maybe assigning a json-encoded parameter is possible (did not test this one)
**Edit: ** REST api provide a cleaner solution. You can start a case with variables. See documentation. Looks like this would help solve your problem
This is exactly what I already wrote several times: you can use REST expansion API to have custom rest calls that retrieve whatever you want from wherever you wish :) This is what I use in my forms to retrieve a list of countries, select possible values from a reference database...
Absolutely!
there are two methods:
the REST Extension that has been mentioned, and the
JavaScript->Ajax method
You choose what you want to do.
In our case we favour the JavaScript->Ajax method, it's what we're familiar with and as we encrypt all our data on the database, it's easy to build and test through Eclipse EE.
We might look at the same for REST, but at the moment we're happy with the way it works.
regards
Comments
There is a problem with the "process-variable" example" though, it will not work in future versions of the software...
Well that's not strictly true... :)
Bonitasoft do not want anyone using process variables (still trying to understand the reason why) in the forms so the test is only valid for those who are Administrators. If you are a normal user this will not work.
This is the way we do it:
One the form create a variable of type JavaScript: return getMyNumber();
getMyNumber is a JavaScript function added as a Page Asset, something like:
var module = "getMyNumber";
var debug = true; .// set false so you don't get debug messages
if (debug == true) {
console.log(module + "v01.00");
}
if (debug == true) {
console.log(module + "Arriving");
}
var xmlhttp;
var gReturn = "No Number Found! Dingbats!";
if (window.XMLHttpRequest) { // code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp = new XMLHttpRequest();
} else { // code for IE6, IE5
xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
}
var gRequest;
gRequest = location.origin + "/myWebService/WebService;
if (debug == true) {console.log(module + "gRequest: " + gRequest)};
xmlhttp.open("GET", gRequest, false);
xmlhttp.send();
if (xmlhttp.status == 200) {
gReturn = xmlhttp.responseText;
if (debug == true) {console.log(module + "gReturn SUCCESS: " + gReturn)};
}
else {
if (debug == true) {console.log(module + "gReturn FAILURE: " + gReturn)};
}
if (debug == true) {
console.log(module + "Leaving");
}
return gReturn;
And finally:
myWebService/WebService
is an AJAX webservice that you need to write to interrogate your Oracle Database, we do this with PostgreSQL.
A pointer to this can be found here: http://www.vogella.com/tutorials/EclipseWTP/article.html
Works wonderfully well :)
regards
Seán
PS: If this reply answers your question, please mark a resolved.