From the technical center of excellence of Massil Technologies, our technology leader Srini makes it easy to understand the complex situations you come across in Mule ESB from the experience of Massil Technologies to have countered them in their experience of working on client projects in real time. This blog addresses how to insert SQL Sub query into Mule ESB flow. This blog is a part of series of blogs being authored and published by Massil Technologies for the benefit of the IT community globally.
It generally appears that Mule ESB doesn’t support aliases in DB queries nor does it support parameters inside sub-queries. You got to take the route sub-queries in Select Statement.
What is Subqueries in SELECT Statement?
A SELECT statement nested in the SELECT list of another SELECT Statement is called Subqueries in SELECT Statement. This is often used to replace two separate SELECT statements. In Subquery it is a good practice to use the keyword that assigns a new name to a table column.
SQL Aliases (AS) temporarily assigns a table column a new name. This allows the SQL developer to adjust the presentation of query results and allow the developer to label results more accurately without permanently renaming table columns.
Using Subquery in Mule ESB:
Add Http and database connectors in mule Flow. Add database credentials and the MySQL jar file to test a connection.
Add variable to the mule flow. Variable contain the timestamp value as below:
#[server.dateTime.format(‘yyyy-MM-dd HH:mm:ss’ )]
Add SQL syntax to the database connector.
SELECT SUB QUERY:
SELECT (select Email from Contact where AccountId = Account.Id) as email,
Id, BillingCity, BillingCountry, BillingPostalCode, BillingState, BillingStreet, Name, Phone
FROM Account where LastModifiedDate < #[flowVars[‘timestamp’]];
FROM Account c
JOIN Contact u ON u.AccountId = c.Id
LastModifiedDate < #[flowVars[‘timestamp’]];
Add the object to JSON to the end of the flow to get proper JSON output.
After successful running of the project go to the postman and send a message and hit send. Check the log for output.
Hope this article from Massil Technologies was useful for you. If you have any further queries on this topic, please reach out to firstname.lastname@example.org.