Querying a Database and Attaching Results to an Email

This example application shows you how to query a MySQL database, aggregate the query results, transform the result into CSV format and send it as an attachment via email.

Example Use Case

The XML data containing employee names is sent to the application using the HTTP POST method. The ForEach component then queries the MySQL DB individually for employee details. The result of each query is aggregated into a List. This List is then transformed to a CSV format and attached as a CSV file to an email which is sent using SMTP.

Set Up and Run the Example

1. Open the Example project in Anypoint Studio from Anypoint Exchange.
2. In Studio, click the Global Elements tab. Double-click the HTTP Listener global element to open its Global Element Properties panel. Change the contents of the port field to required HTTP port for example, 8081.
3. Start the MySQL server on your machine and create a connection by navigating to your MySQL home directory and using the following command: mysql -u root -p
4. Run db_script.sql which is placed under src/test/resources to create a DB schema company and table employees
5. Click the SMTP connector and configure its properties as follows: Host=smtp.gmail.com, Port=587, User=senderemailid@gmail.com, Password=senderpassword, To=receiveremailid@gmail.com, From=senderemailid@gmail.com, Subject=Export from Excel
6. Double-click the Database Config global element to open its Global Element Properties panel. Alternatively, configure the global element in the XML Editor.
7. Change the contents of the Host, Port, User, and Password fields to your account-specific values. The field Database should be set to company to be aligned with DB script from point.
8. Click OK to save your changes.
9. Run the Mule application
10. Make a POST request using Postman to your localhost with the following XML code as the message body:

    <root>
         <employees>
          <employee>Chava Puckett</employee>
          <employee>Quentin Puckett</employee>
          <employee>Mona Sosa</employee>
         </employees>
    </root>

When done, verify that you received an email with the CSV file attachment of the queried employee records.