Mule3 insert data into 2 tables in a query

Question

I have a payload similar to the below sample

{"timezone": null,
  "recurrence": null,
  "event_id": "55bfe76n3",
  "attendeesEmail": [
    {
      "email": "abc@xyz.com",
      "responseStatus": "needsAction"
    },
    {
      "email": "efg@xyz.com",
      "responseStatus": "needsAction"
    }]
}

I would want to insert the payload into two tables, table A with values in attendeesEmail object and table B having remaining key values. I am looking to have a single db connector to do this operation.

Things I have tried:

Using bulk operation to insert but unable to do multiple inserts in a single query inside 1 dbConnector

BEGIN;
INSERT INTO A (event_id) values ('55bfe76n3');
INSERT INTO B (email) values ('abc@xyz.com');
COMMIT;

This throws an error saying: "Query type must be one of '[INSERT, STORE_PROCEDURE_CALL]' but was 'DDL' (java.lang.IllegalArgumentException)."

score 1 · Accepted Answer · answered Nov 08 '20 at 07:38

Mulesoft allows to execute a single operation at a time. You cannot execute two insert operations together.

Bulk operations accept bulk input parameters, but allows only one operation either insert, update or delete.

However, if you want to combine two insert operations then you can use Stored Procedure. You can pass dynamic input parameter in Stored procedure which will solve your problem

  <db:stored-procedure config-ref="dbConfig">
    <db:sql>{ call updateEmployee('EmailID', :emailID) }</db:sql>
    <db:input-parameters>
        #[{'emailID' : payload}]
    </db:input-parameters>
</db:stored-procedure>

aled · Answer 2 · 2020-11-05T12:47:51.187

0

That's not how the bulk insert operation should be used. You should use 2 non-bulk <db:insert> operations, one for each table. Do not use BEGIN and COMMIT, those should be controlled by Mule transaction features. Each operation should be a single insert query.

Example:

<flow name="insertFlow">
    <db:insert config-ref="Mysql_Database_Configuration" doc:name="Database">
        <db:parameterized-query><![CDATA[INSERT INTO A (event_id) values ('55bfe76n3')]]></db:parameterized-query>
    </db:insert>
    <db:insert config-ref="Mysql_Database_Configuration" doc:name="Database">
        <db:parameterized-query><![CDATA[INSERT INTO B (email) values ('abc@xyz.com')]]></db:parameterized-query>
    </db:insert>
</flow>

edited Nov 05 '20 at 12:47

answered Nov 05 '20 at 03:55

aled

21,330
3
27
34

So does that mean I have to use 2 dB connectors or is it possible to do 2 operations in a single connector. If so, could you perhaps show an example? – melmatvar Nov 05 '20 at 05:30
You are confusing operations with the connector configuration. I mean a single connector configuration, referenced by two database query operations. I'll update my answer with an example. – aled Nov 05 '20 at 12:43

Mule3 insert data into 2 tables in a query

2 Answers2