CODEWITHSUNDEEP
mysqlmuleanypoint-studiomule-componentmulesoft
melmatvar
melmatvar

Reputation: 25

Mule3 insert data into 2 tables in a query

I have a payload similar to the below sample

{"timezone": null,
  "recurrence": null,
  "event_id": "55bfe76n3",
  "attendeesEmail": [
    {
      "email": "[email protected]",
      "responseStatus": "needsAction"
    },
    {
      "email": "[email protected]",
      "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 ('[email protected]');
COMMIT;

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

Upvotes: 2

Views: 538

Answers (2)

Abhishek Kumar
Abhishek Kumar

Reputation: 545

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>

Upvotes: 1

aled
aled

Reputation: 25837

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 ('[email protected]')]]></db:parameterized-query>
    </db:insert>
</flow>

Upvotes: 0

Related Questions