Mule 4 - Set Start Date & End Date as QueryParams

Question

I'm having trouble defining both start and end dates as a query parameters. When the data gets pulled, it needs to return as a range of dates based on the query parameters. The GET URL would look like http://localhost:8081/test?FileType=Sales&StartDate=2022-10-01&EndDate=2022-10-26. This should return a date range of data from 10/1/2022-10/26/2022.

In my query, my where clause is set to:

where dp.Nid = 405 and fs.DDate=:DDate

**dp and fs are used in my joins and 405 is an ID that i'll need to unique identify a product.

My input Parameters:

{ DDate : attributes.queryParams.StartDate, DDate : attributes.queryParams.EndDate }

What do i need to set to make a range of dates? Do i need to set startdate to > and enddate to < ? Also, is it possible to define query parameters when using a stored procedure instead of select database method in anypoint studio?

Please do no add products to the title of the question. Anypoint Studio is not even the right product for this question since the question is about Mule 4 applications. For this question, what are you asking exactly? It is not clear if you are asking about how to implement an API, how to make an SQL query with a date range, or how to implement a call to a stored procedure. Questions which lack focus could be closed by the community (https://stackoverflow.com/help/closed-questions). — aled, Oct 27 '22 at 16:16
The issue is setting a start and end date as a query parameter that will work as a range of dates. The GET URL would look like http://localhost:8081/test?FileType=Sales&StartDate=2022-10-01&EndDate=2022-10-26. This should return a date range of data from 10/1/2022-10/26/2022 — ilya.fastiv, Oct 27 '22 at 16:33

score 0 · Answer 1 · answered Oct 27 '22 at 18:31

Operations in Mule 4 (ie the boxes inside a flow) can have several inputs (payload, variables, attributes) and 1 output, but they are expected to be independent from each other. The Database query operation doesn't care if its inputs come the query params or from somewhere else. You need to map inputs explicitly to parameters in the query.

Once you have the arguments you need to use them in the SQL query. Usually that means adding a greater than and a lesser than comparison, to ensure that the value is in range. Or the same including also equals, if the business logic requires it.

Depending on the data types and the SQL dialect you may need to convert the inputs to a date format that is compatible with the database type of the column. The inputs here are strings, because that's what query params always are parsed to. The column type is something that you will need to understand and see how to transform, in DataWeave or in the SQL query.

As an example:

<db:select config-ref="dbConfig">
    <db:sql>SELECT ... WHERE dp.Nid = 405 AND fs.DDate >= :StartDate AND fs.DDate <= :StartDate</db:sql>
    <db:input-parameters>
      #[{
          StartDate : attributes.queryParams.StartDate, 
          EndDate : attributes.queryParams.EndDate 
       }]
    </db:input-parameters>
</db:select>

Mule 4 - Set Start Date & End Date as QueryParams

1 Answers1