Insert multiple rows using db connector

1
0
-1

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?

Comments

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.

Thanks!

1 answer

1
0
-1

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.

Comments

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

Notifications