How to read value from CSV file in MULE and retrieve data from Database using the value

Question

I have a flow which reads a CSV files and perfrorm CRUD operation in database ... My flow is somewhat like :-

<flow name="CsvToFile" doc:name="CsvToFile">
    <file:inbound-endpoint path="C:\Data" responseTimeout="10000" doc:name="CSV" connector-ref="File">
        <file:filename-wildcard-filter pattern="*.csv" caseSensitive="true"/>
    </file:inbound-endpoint>
    <jdbc-ee:csv-to-maps-transformer delimiter="," mappingFile="src/main/abc.xml" ignoreFirstRecord="true" doc:name="CSVTransformer"/>
    <jdbc-ee:outbound-endpoint exchange-pattern="request-response" queryKey="SelectQuery" queryTimeout="-1" connector-ref="jdbcConnectorGlobal" doc:name="Database">
        <jdbc-ee:query key="SelectQuery" value="Select * FROM DBDATA where ID=#[map-payload:ID]"/>
    </jdbc-ee:outbound-endpoint>
    <set-payload value="#[message.payload]" doc:name="Set Payload"/>
</flow>

Now if I use SELECT SQL statement.. I don't see the result and no data is fetched ... but if I use INSERT like

<jdbc-ee:query key="InsertQuery" value="INSERT INTO DBDATA (ID,NAME,AGE) VALUES(#[map-payload:ID],#[map-payload:NAME],#[map-payload:AGE])"/>

or an UPDATE SQL statement, I can find it's working and data is inserted or updated in dataase.

My question is: how can I read the ID value from .CSV file and use in SELECT query to retrieve values from the database?

Anton Kupias · Accepted Answer · 2014-03-26 09:57:06Z

1

You have exchange-pattern="one-way", meaning the jdbc outbound call will not return to the main flow. Use exchange-pattern="request-response" instead to get a return value.

Also <set-payload value="#[message.payload]" doc:name="Set Payload"/> does not make any sense. You need a transformer of some sort to make the return value readable. You can add a logger to see the returned payload.

UPDATE:

Return value of csv-to-maps-transformer is ArrayList, not Map, so you can not use map-payload:ID. Try splitting the ArrayList, or use #[payload[0].ID] if you have just a single entry.

edited Mar 26, 2014 at 9:57

answered Mar 26, 2014 at 8:33

Anton Kupias

4,0153 gold badges19 silver badges20 bronze badges

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

4 Comments

Anirban Sen Chowdhary Over a year ago

Sorry ... Actually I have used request-response .. here by mistake I have written one way ... now I have edited ... Now, the issues is Select statement doesn't return data .. where as Insert and Update works fine ... I have used set-payload to get the the payload which I can view it in console .. I can also use logger here .. In case of Insert I have used < set-payload="Successfully Inserted" doc:name="Set Payload" /> which I can see in console and later can be used to save it somewhere else like in a file as a response

Anton Kupias Over a year ago

Updated the answer, but I am still a bit confused about what you are actually trying to do here.

Anirban Sen Chowdhary Over a year ago

Thanks ... #[payload[0].ID] is working fine ... yes Select statement returns Array .... But this is returning only first row ... Is there any way I can get all the rows ?? Anyways Thanks for help ..

Anton Kupias Over a year ago

You will probably have to split the ArrayList, then loop over each item with the query, and aggregate the results. Splitter and Aggregator components, perhaps. You will need to learn to use the Mule routers to do this: mulesoft.org/documentation/display/current/Routers

Anirban Sen Chowdhary · Accepted Answer · 2015-07-21 15:41:14Z

0

Thanks to Anton.. The final solution is #[payload[0].ID] what I need and working for me

answered Jul 21, 2015 at 15:41

Anirban Sen Chowdhary

8,3416 gold badges42 silver badges81 bronze badges

Collectives™ on Stack Overflow

How to read value from CSV file in MULE and retrieve data from Database using the value

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related