Insert multiple rows using db connector


the answer to this isn't perfect
How to insert multiple rows in MySql DB using My SQL Connector.

In the scenario where a variable is a List BDM object, the plain sql statement wouldn't work.
Would the correct strategy be to use a Groovy script then?


Submitted by antoine.mottier on Wed, 04/10/2019 - 16:33

To make sure I correctly understand your question are you using:

  1. your own database schema
  2. Bonita BDM feature?

Database connectors in Bonita aim to interact with an external database schema. Interaction with BDM is done using business variables default values, operations on tasks...

Submitted by ted.tea_1394795 on Wed, 04/10/2019 - 17:19

[your own database schema, Bonita BDM feature]
yes - inserting multiple records from Bonita BDM.
Use case is

  • setup pool variable, where it is a list, T being 3 properties of primitive data types, int, string, bool

  • in step 1, invoke a RESTful GET, receive json objects into the pool variable; now List contains n items

  • in step 2, persist the pool variable values into a table.
    therefore the question is, what's the best strategy using Bonita, to insert the records?
    ie, the plain SQL statement would work for a single record but not for a collection.


1 answer


First of all to clarify you are not actually using the db connector, you are using a REST connector and want to store the result of the REST call in a business variable (multiple) managed by Bonita (Bonita handle the data persistence in the database).

If you have defined a business object MyType, and declare in your process a business variable myVar with type set to MyType and "Is multiple" checked the type of myVar is actually a list of MyType (i.e. List).
So the question becomes how to convert the REST API connector result to this type? Actually you can do that in the output of the connector. One of the output of the connector is an object that represent the result of the REST call. The name of this variable is bodyAsObject.
In the connector output configuration you can map your business variable with the bodyAsObject with the operation "Takes value of" but the issue is that bodyAsObject type does not match the myVar type. You need to convert it. You can do this by defining a Groovy script instead of mapping directly bodyAsObject. Here is an example (I expect the REST call to return a JSON array of objects): List<MyType> tempList = bodyAsObject.collect{new MyType(myTypeAttributeA: it.jsonObjectAttributeA, myTypeAttributeB: it.jsonObjectAttributeB)}

You might want to download the example I create with a public REST API and a basic business object.


Submitted by ted.tea_1394795 on Tue, 04/16/2019 - 18:43

Not sure what you meant by "(Bonita handle the data persistence in the database)."

Here's a temp link to the Connector Example

Yes it's not typical to persist data directly to the DB layer; I just wanted to test the DB connector functionality, and therein lies the question that started this thread -> how do Bonita users actually persist List of T data to external databases (not to the DB that houses the engine/BDM).

Other gripes that I have - connectors are great - but I don't like the idea of attaching credentials to every task - this is a maintenance nightmare.
As you can see from the Groovy script, I made a connection to a test MS SQL database using a sql account (can't figure out how to do SSO).

Submitted by antoine.mottier on Mon, 04/29/2019 - 12:57


I created an example that illustrate how to store the data in an external database. Basically the user provide the data in the form, when submitting the form the data are used to valuate the process instantiation contract variable, then the contract variable is used in the connector configuration to create the SQL INSERT query.

For the credentials you can declare parameters in the process to store them and reuse them in each individual tasks.

Submitted by pinerodanie_1404393 on Fri, 07/12/2019 - 10:53

Can you tell me how to insert multiples rows in an external postgres db using connectors
How could i build the query??
Thanks in advance

Submitted by antoine.mottier on Mon, 07/15/2019 - 09:35

Can you let me know where are currently stored the data you want to insert in an external PostgreSQL database? Are they in a Bonita form? Are they sent to the process using a contract?

Also can you let me know why in your use case the usage of Bonita Data Model (BDM) is not appropriate? To store business data usage of BDM is recommended over an external database as it makes integration and manipulation of data in Bonita a lot easier.