0

I want to insert multiple rows in mule database connector at a time. Could anyone kindly please help me on this?.

I can successfully insert the below message as a post request to a mule flow.

{
    "patient_todo_id" : "2",
    "comment_date" : "2017-09-20 14:41:16",
    "comment_text" : "send me the steps to check the Pulse rate"
}

How to insert the below post message into a database in a mule flow?

[{
    "patient_todo_id" : "2",
    "comment_date" : "2017-09-20 14:41:16",
    "comment_text" : "send me the steps to check the Pulse rate"
},
{
    "patient_todo_id" : "2",
    "comment_date" : "2017-09-20 14:41:16",
    "comment_text" : "send me the steps to check the Pulse rate"
}]

Please find the below mule flow configuration file which has been configured to insert a single row at a time.

<flow name="carrotcube-patient-todo-commentFlow">
        <http:listener config-ref="HTTP_Listener_Configuration" path="${http.path.mrs.todo.comment}" doc:name="HTTP"/>
        <set-variable variableName="variable" value="#[payload]" mimeType="application/json" doc:name="Variable"/>
        <json:json-to-object-transformer returnClass="java.lang.Object" doc:name="JSON to Object"/>
        <logger message="#[payload.comment_text]" level="INFO" doc:name="Logger"/>
        <db:insert config-ref="MySQL_Configuration" doc:name="Database">
            <db:parameterized-query><![CDATA[insert into patient_todo_detail(patient_todo_id,comment_date,comment_text) values (#[payload.patient_todo_id],#[payload.comment_date],#[payload.comment_text])]]></db:parameterized-query>
        </db:insert>
</flow>
Improve this question

4 Answers 4

Reset to default
2

Use the Bulk update mode and pass your connector a collection of object to insert. In Studio, simply check "Bulk mode" in the Basic Settings section of the database connector. The example array you provide in your question is just fine, you can then do something like:

<db:insert config-ref="MySQL_Configuration" bulkMode="true" doc:name="Database">
        <db:parameterized-query><![CDATA[
            INSERT INTO mytable(id, name)
            VALUES (#[payload.id], #[payload.name]);]]>
        </db:parameterized-query>
    </db:insert>

Each element of your list will then become the payload in the connector and will be inserted. You do not need to use a for-each or any loop mechanism. Make sure to pass an iterable object though.

See the related docs: https://docs.mulesoft.com/mule-user-guide/v/3.8/database-connector#setting-up-database-connector-operation

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

I have selected the bulk executed operation, but while while sending the payload in the form of array , its not taking or pointing the index for the current row insertion. <db:bulk-execute config-ref="MySQL_Configuration" doc:name="Database">insert into patient_symptom(patient_id,symptom_date,symptom_code_id,scale_id,value) values(#[payload.patient_id], #[payload.symptom_date], #[payload.symptom_code_id], #[payload.scale_id], #[payload.value])</db:bulk-execute>
Can you be more specific about what you mean by "not taking or pointing the index for the current row insertion" ? It inserts the same row multiple times?
I have selected bulk mode and sending the payload which contains array of objects. But in turn it is not getting iterated with the current index.
Yes but what is the behavior of the DB connector? Error? Same row inserted multiple times? Only one row inserted?
None of the rows not get inserted. Its not able to iterate the values from the array.
|
2

This is super easy, you can do it by 2 ways:
solution1 using splitter: 

 <flow name="testFlow">
        <http:listener config-ref="HTTP_Listener_Configuration" path="/test" doc:name="HTTP"/>
        <json:json-to-object-transformer returnClass="java.lang.Object" doc:name="JSON to Object"/>
        <collection-splitter doc:name="Collection Splitter"/>
        <logger message="insert into patient_todo_detail(patient_todo_id,comment_date,comment_text) values (#[payload.patient_todo_id],#[payload.comment_date],#[payload.comment_text])" level="INFO" doc:name="Logger"/>
        <collection-aggregator failOnTimeout="true" doc:name="Collection Aggregator"/> 
        <json:object-to-json-transformer doc:name="Object to JSON"/>
  </flow>

solution2 using foreach: 

  <flow name="testFlow">
        <http:listener config-ref="HTTP_Listener_Configuration" path="/test" doc:name="HTTP"/>
        <json:json-to-object-transformer returnClass="java.lang.Object" doc:name="JSON to Object"/>
        <foreach doc:name="For Each" collection="#[payload]">
           <logger message="insert into patient_todo_detail(patient_todo_id,comment_date,comment_text) values (#[payload.patient_todo_id],#[payload.comment_date],#[payload.comment_text])" level="INFO" doc:name="Logger"/>
        </foreach>
        <json:object-to-json-transformer doc:name="Object to JSON"/> 
     </flow>

In both the cases, you can see your logger is getting the correct values in your SQL statement you are using : 

 <logger message="insert into patient_todo_detail(patient_todo_id,comment_date,comment_text) values (#[payload.patient_todo_id],#[payload.comment_date],#[payload.comment_text])" level="INFO" doc:name="Logger"/>

Now you can replace the logger with your DB component

UPDATE based on the comment: 

<set-variable variableName="myMap" value="#[new java.util.HashMap()]" doc:name="Variable"/>
 <foreach doc:name="For Each" collection="#[payload]">
     <db:insert config-ref="MySQL_Configuration" doc:name="Database">
        <db:parameterized-query><![CDATA[insert into patient_todo_detail(patient_todo_id,comment_date,comment_text) values (#[payload.patient_todo_id],#[payload.comment_date],#[payload.comment_text])]]></db:parameterized-query>
      </db:insert>
 <expression-component doc:name="Expression"><![CDATA[flowVars.myMap.put('row'+flowVars.counter,payload)]]></expression-component>
 </foreach>
   <logger message="Final status #[flowVars.myMap.toString()]" level="INFO" doc:name="Logger"/>

Here at the end you will get in logger the status of each row inserted which is 1 means successful

To get a particular row details ouside foreach: 

<logger message="#[flowVars.myMap.get('row1').toString()]" level="INFO" doc:name="Logger"/>

So, based on the status you can further display your custom status messages

Improve this answer

3 Comments

How to get the insert status after the database task?
what do you mean by insert status? put a logger <logger message="#[payload]" level="INFO" doc:name="Logger"/> after db. If the number is 1 or greater than zero to confirm successful insert. `
If I am inserting 2 records means, is there any chance to get that value outside of the for each loop?
1

Ashok, I just crossed this hurdle, so you got really lucky here as I had to struggle through this for a while. Very surprising that such a common scenario was not to be found on the Internet. The steps are listed below ,and the flow is below too.

1) Use transform to convert the post payload to java list (application/java) – the output should just have payload (remove the curly braces and just put payload). This payload should come from the body.

2) Use for each scope and put the database insert statement there

3) In the database insert statement use [payload[‘username’]] kind of syntax to refer to the value of username in the current record (or whatever field names you have). I am using a stored prod to insert, but you get the idea.

I am not able to post the flow here .. it's cutting it off and showing it weird. I'll try to send you through email if you can share.

Now all I have to figure out is how to send a nice message back with the insert status. Anybody who already has done that.. appreciate your inputs!

Improve this answer

3 Comments

will try this approach and let you know
Now I am able to post the records in a continuous manner , but I am not able to get the insert status(count) of the records which I inserted.
I need to try that out myself now. I believe returning the rowcount as an output parameter from a stored proc and setting it as the output payload should work.
1

Ashok, replying to your comment on aggregating the response from the for each here, as I can't add comments yet. Check out the following link, which offer s a few options.

https://forums.mulesoft.com/questions/60273/save-of-for-each-database-query-response-to-a-new.html

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.