How to retrieve a value from Oracle database when a process initiates?

Q&A
1

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

2

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.

3

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

4

Hi,

I did find the following example “process-variable-example”
https://github.com/Bonitasoft-Community/process-variable-example/releases/tag/7.1.5-1.0.1

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

5

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

6

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.

7

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.

8

First, let me that everybody for their help and at the same time apology for being such a slow leaner of this stuff…

After trying different option I concluded that easiest for me it to pass a parameter in the URL and use that one as a parameter in the sql statement to read the data from Oracle database. This I have managed to make to work sort of.

The next challenge was to populate the BDM with the values retrieved by the SQL and then display that BDM data in the form.

I did find a couple of examples where data is read and put to the BDM using Groovy scripts. However didn’t manage to get those to work, it may be that its my lacking skill in Groovy…

In any case, would anyone have an example where they read data from database (one record, with a couple of columns and populate a BDM with that data and then display it in a form?

That would be of create help as then I could modify this sample, having the idea how to do it.

Thanks,
ktp

9

What are you trying to achieve ? Sync your oracle database with the BDM database ?

10

I echo Quentin’s thought,

What is it exactly you want to accomplish. If you can be as clear as possible.

My understanding is:

  1. User clicks a link to open the process initialization form
  2. On the form i want to display a value taken from an Oracle Database
  3. 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

11

Quentin and Seán,
first let me thank your for patience with me. It would appear that I cant attach a file to this answer so this will now be rather long reply. But first a brief status update where I’m at the moment and what kind of sticking points I have.

The overall idea is on the lines what Seán did write. In essence

  1. Create a BDM (with two variables)
  2. Retrieve data (one line with two fields) and populate the BDM
  3. Create a form that displays the fields from BDM and pre-populates those with values retrieved from the database.
  4. the user does something with form but that’s outside this initial part.

My issues are

  1. How to parse the result set to the BDM, which is a complex object? I can do this with simple variables but as I’m new to Groovy, I don’t appear to get the script right

  2. How to have the data from the BDM displayed in the form

I’ll with put some screenshot of the above steps in a separate comment.

Thanks,
kari

12

Some screenshots…

This is my simple BDM, with two text fields.
BDM

This is the SQL used to retrieve one record two string fields from the database
SQL

This is the Groovy script I tried to use to parse the result set to the BDM
Groovy

This is the form (in UI designer)
Form

One (failed) attempt to get data from the BDM to display in the form
Populate a form field

13

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

1. The Portal displays the form to the user. 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

14

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.

15

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?

PoolProcessVariable

My thinking is that if the user decides to start the process I can then later use these process variable.

Thanks,
kari

16

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

17

ok, thanks, then I really must rethink my approach/idea and how I had envisioned to connect/integrate my application to Bonita. Most likely I’ll try to pass the required initial values in the URL as parameters and take it from there.

Kari

18

One, hopefully final question on this, can I access an external database from the initialisation from, using e.g. Java. But this should be in away that the user doesn’t need to submit anything at this point.

The idea I have is that if I pass a key to the initialisation form (which I can do), then use that key(data) to query the database and get the rest of the required data.

Do you think this would be possible?

Kari

19

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 :slight_smile: This is what I use in my forms to retrieve a list of countries, select possible values from a reference database…

20

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