Insert bulk data with INSERT statement built dynamically into a flow variable

Question

I'm new to database connector in Mule 3.7.3. I'm trying to insert bulk data using INSERT statement that formed dynamically. I don't know what do you guys usually call this approach, what I intend to do is, load multiple files into respective staging table dynamically, instead of having multiple choice routes and hard code the INSERT statement in each route.

My flowVars.insertStatement contains value like this: "INSERT INTO [roomService] ([RoomId],[ServiceId],[DateTime]) VALUES (#[payload.roomId],#[payload.serviceId],#[payload.dateTime])"

When I tried with these:

<db:insert config-ref="Staging_DB" doc:name="insert-data-into-staging-db" bulkMode="true">
    <db:parameterized-query><![CDATA[#[flowVars.insertStatement]]]></db:parameterized-query>

This gives me "Query type must be one of '[INSERT, STORE_PROCEDURE_CALL]' but was 'DDL' (java.lang.IllegalArgumentException)"

<db:insert config-ref="Staging_DB" doc:name="insert-data-into-staging-db" bulkMode="true">
    <db:dynamic-query><![CDATA[#[flowVars.insertStatement]]]></db:dynamic-query>

This gives me "Bulk query cannot contain a parameterized SQL query (java.lang.IllegalArgumentException)"

<db:insert config-ref="Staging_DB" doc:name="insert-data-into-staging-db" bulkMode="true">
    <db:dynamic-query><![CDATA[#[flowVars.insertStatement]]]></db:dynamic-query>

This gives me "Index: 0 (java.lang.IndexOutOfBoundsException)"

I need some pointers how to do this correctly. Please advise.

Yevgeniy Yevgeniy · Accepted Answer · 2017-04-06T15:15:43

i am afraid you wont be able to use bulk mode with your approach.

you probably defined flowVars.insertStatement like this:

<set-variable variableName="insertStatement" value="&quot;INSERT INTO [roomService] ([RoomId],[ServiceId],[DateTime]) VALUES (#[payload.roomId],#[payload.serviceId],#[payload.dateTime])&quot;" doc:name="set insertStatement"/>

this will make sure payload.roomIdand other flowVars wont become null after defining the variable, but at the same time they wont be replaced by the db:insert component either.

following configuration uses the foreach component instead of bulk mode, but will work for your:

<set-payload doc:name="test data" value="#[[{ &quot;name&quot;: &quot;Chevon&quot; }, { &quot;name&quot;: &quot;Yevgeniy&quot; }]]"/>
<foreach doc:name="For Each">
    <set-variable variableName="sql" value="insert into TEST(name) values ('#[payload.name]')" doc:name="set sql variable"/>
    <db:insert config-ref="MySQL_Configuration" doc:name="INSERT">
        <db:dynamic-query><![CDATA[#[flowVars.sql]]]></db:dynamic-query>
    </db:insert>
</foreach>

Insert bulk data with INSERT statement built dynamically into a flow variable

1 Answers