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?
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
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).
Hii
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
Comments
To make sure I correctly understand your question are you using:
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...
[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!