Linking a database query to a service task "breaks" the next step

Hi,

have a very simple lane with “initialization form”-“service task”- “human task” .

lane

What I would like to do is to initialize in the service task one variable with a value retrieved from the database.

The issue is that as I create the connector (no difference if its in or out) in the service task, the human task doesn’t get created.

human task - no

if I take the query away from the service, the human tasks gets created.

human task - yes

The sql is correct, it returns a value when testing it while creating the connector.

Still learning this stuff so again anything that would help in explaining a) how a service can be made to retrieve data from a database and b) then eventually pass it forward to the following task would be great.

At this point I’m already stumbling getting the service task with the query to work…

Thanks,
ktp

As you state "It was in the portal unassigned to any actor. "…I guess you are not using Actor Filter on your human task step.

if you don’t have Actor Filter specified, application will not do auto-assignment. But if you use, say “Initiator” (out-of-the-box Actor Filter), you will see there’s one configuration on Actor Filer’s Edit screen, call “automatically assign”. You could navigate to this from Human Task step → General tab → Actors → Actor Filter → Edit button

Once you choose this “automatically assign” check box, Bonita will automatically assign tasks to the user if he/she is the only person found by this filter. In your case, since there’s only one user in your Actor, Bonita will assign tasks to this particular user.

Why Bonita implements in such way, i guess, is to cater for tasks could be assigned to a group of users who all have authorization to work on a kind of task.

Here is one example I built for you to explain how to set BDM data. There’s no forms in this flow (you could simply create a form and call below REST APIs to fetch BDM data and populate fields), so you have to use REST API to check BDM data post launching a case. Link to bos file: https://drive.google.com/file/d/0B4PSuBFeVF-aMVh4ZkNMUXlzcGc/view?usp=sharing

REST API to check BDM data’s id: /API/bdm/businessDataReference/2001/customer, and you will get: {“name”:“customer”,“type”:“com.company.model.customer”,“link”:“API/bdm/businessData/com.company.model.customer/1”,“storageId”:1,“storageId_string”:“1”}

REST API to view BDM data: /API/bdm/businessData/com.company.model.customer/1, and you will get: {“persistenceId”:1,“persistenceId_string”:“1”,“persistenceVersion”:0,“persistenceVersion_string”:“0”,“consumer_id”:24,“consumer_id_string”:“24”,“consumer_name”:“123”}

Hope this could help you.

regarding

I think what might be the problem. After understanding that it is possible (but not perhaps recommended) to have the connector also in the human task, I drop the service task and added the database query to the human task.

BPMN2 standard says no connectors on the Human Task but if the system allows it and it works, use it. We us it all the time otherwise diagrams can become to unwieldy.

If no human task is created then you have a different problem preceding the creation of the task, have you looked at the logs? Studio->Help->Bonita Engine Log. There should be something in there to tell you why its failing.

This is an easy way of moving a db table to a BDM, and it will work with one record, or a 100.

import myBDM;

//get the data into a resultset from the db of your choice

while(resultset.next()){

myBDM newBDO = new myBDM();
newBDO.setId = resultset.getInt(“Id”);
newBDO.setDescription = resultset.getString(“Description”);
newBDO.setStatus = resultset.getint(“Status”);

}
From a post I forgot about… :slight_smile:

http://community.bonitasoft.com/node/22542#node-22572

regards
Seán

PS: If this reply answers your question, please mark a resolved

I finally managed to get it to work!

Can now retrieve one record from the progress database into a variable and then using that variable populate the BDM and display it on the human task form.

Next will then porting this to Oracle.

Thank you everybody for your help and patience.

Kari

Hi,
I think what might be the problem. After understanding that it is possible (but not perhaps recommended) to have the connector also in the human task, I drop the service task and added the database query to the human task.

The end result was the same - when running the process no human task was created.

I’m guessing that the reason may be that I’m trying to read the result of the sql statement to a single process variable, not BDM.

Reason for this is that I haven’t found an example of how to populate the the BDM if the record set returned from database only contains one record.

All the examples are about populating lists - I would be happy to have an example how a to do it when there is only one record. Sorry for this simple question but my Groovy skills are more or less non-existing so I have to do everything trough examples…

My SQL is simple (“SELECT DUMMY FROM DUAL”). Oracle users know that this will return one record and one column (“DUMMY”) that will have value “X”.

If I would have a BDM model with just one attribute, how can I populate that attribute from the returned data, is my current problem…

one more comment - I guess I did find the human task. It was in the portal unassigned to any actor. I only have one actor (the default one), one line etc so don’t quite know what has caused this.

This seems similar to my inquiry at about the same time - I’m looking forward to seeing answers!

Sean,

thank you very much for the example, it helped me a little bit further along.

However, still some problems - I ported it to Oracle environment (which was quit simple and shouldn’t break anything in my mind) but when testing the connector I get this error message

error

In the portal I can find the GetCustomer step with failed status and comment “no data”.

I’ll next download and install Postgress database and try the process with that and let you know what happened.

ktp

Hi again,
created a Postgres instance to Amazon RDS, created table “customer” with one records with values (24, Mickey Mouse).

Same end result “No data found”. When trying to test the connection I get the same “script” related error as mentioned in the previous comment.

The only modification I made to you process (bos) was to change the postgres parameters to connect to the Amazon RDS instance.

The connection string is
jdbc:postgresql://bonita.cknaptgegwiw.us-east-1.rds.amazonaws.com:5432/postgres

Testing using another tool the connection gets establish and the customer table is there with one record.

The userid and password are bonita/bonita if you want to try and connect to this instance (it should be publicly available)

But for now I’m stuck…

Thanks,
ktp

While continuing to search for a solution, I’m starting to think that it is related the way data is retrieved and perhaps mapped when the process is run…

This was my test.

  1. testing the connector within Bonita it returns data nicely
    connector
  2. mapping the three fields is show here
    mapping
  3. the connector does not have any other coding etc.
  4. Running the process results with a failed task with comment “No data”
    failed task

Why does it fail runtime - it should just return some data and map in to those three local variables define in the service step.
variables

The next step would then be the human task but the process never gets there.

I’m using Bonita Community edition v. 7.2.2.

I really don’t understand - I created a fresh Bonita process to access Postgres database and now it appears to work.
Here is a link to the BOS file that access the AWS postgres database so no changes should be required.
https://drive.google.com/open?id=0BwvRq-sIsUA9am1fQzlxRmx6Ujg

But doing the same to Oracle database and still the “no data” error comes. I just can see what I’m doing differently…

did one more test - duplicated the BOS that used postgres as the database and changed the database to be oracle.

And it didn’t work - same error as previously.

I’m starting to think that this is a configuration issue - while the database query works in the Bonita environment, running the whole process it cant access Oracle database or something similar (e.g. datatypes).

Would anyone have an example that retrieves data from Oracle DB?

ktp

Good to see you make it work in PG.

I don’t have oracle DB here, so am not able to show you a sample.

However, if PG works fine, there’s no reason oracle cannot work.

Did you include oracle JDBC jars into Tomcat lib? And what errors are logged in log file?

Hi,
have for now stopped working with the Oracle part - will return to it later).
As I now have a Postgres instance available will try to get one example to work there. Will then try to port it to Oracle.

But, and sorry to bother you again, I cant seem to find a way to get the data to human task form.
Most likely it has to do with getting the data from the Service task to the Human task.

If I understand correctly in this step here below the Service task populates the BDM with data retrieved from database
ServiceTask

Then the Human task starts and it should be made aware of the BDM and I think this is the part were I fail.

Here is my form with some fields trying to get data using REST API. Only one that works is the context one.
context

Here are the Variables and their definition
rest api

My questions are.

  • what should the formInput be?
  • why the variables starting BMD returning anything
  • should the “/API” have the two dots (…) in form of ir? Tried both, no difference to me.

In the log files everything looks ok, no error.

Finally did manage to get it to work. Can now retrieve in a Service step data to variables that I then use to populate the BDM.

After that is the Human task and I can pass the BDM data to the form and display it.

So in essence can retrieve a single row from database and pre-populate a form with that data.
Works no with postgres and oracle.

Thank you everybody for your help and patience.

Kari

ps. Happy to share my .bos with any other “newbie”, if it helps.

Kari, I’d like to see your solution.

Thanks…

Ford,
happy to help but I have already moved further on, so it may be that my example is difficult to replicate/implement in a different environment. But if list here some of the things that I can do currently. If any of these things are of interest, then I can perhaps answer or give material to those points.

Here is a brief list of what I can do (using Bonita 7.2.3 CE and Oracle (11c/12c) database.

  • can populate the initiation form using REST API calls to Oracle database. The parameter is taken from the URL
  • Can use a connector and retrieve numeric variables (=BigDecimal) from Oracle database
  • Can populate the BDM variable with values retrieved from database
  • Can populate forms from the BDM variables
  • Can pass this BDM to another form so that the prepopulated fields have their value also on the second form.

I did feel that I had accomplished more as sorting out each of these things has been some sort of struggle.

If you are stuck with something specific issue not mentioned in my list, just ask for it as it may be that I have stumbled to that as well.

ktp