topic Re: Fetching and filtering data from a DB while processing a web service in Talend Studio

Fetching and filtering data from a DB while processing a web service

kleinmat — Sat, 16 Nov 2024 11:40:04 GMT

Hi there,
I would like to build a simple, synchronous web-service which should do this:
1. Receive a request
2. Fetch 1-n rows from a database (using 1 request field such as "customer number" as search criteria)
3. Filter that data (using another request field to do so) down to 1 row
4. Build a reponse based on the request and the filtered data
5. Return it
Of course I know the webinar http://www.talend.com/resources/webinars/3-steps-to-developing-esb-solutions which seems to offer a similar solution.
But essentially, my service works different in the way that I get more than 1 row back from the database and need to filter that data after fetching it from the DB. The filter is the succession of 3-5 filtering rules that are based on static lists and one request parameter.
May be I can implement that filter in SQL. But that would mean that I must take 2 values from the request, use them in a complex SQL query and use the result to build the response.
And I have no idea how to go about this. Any idea?
Thanks
Matt

Re: Fetching and filtering data from a DB while processing a web service

Anonymous — Wed, 30 Apr 2014 12:50:37 GMT

Hi
In case of working with tRESTRequest the following request properties are available on the context:
"PARAMS" (map representing the parameter name and values used directly in the flow),
"ALL_HEADER_PARAMS" (list representing all the HTTP headers)
"ALL_QUERY_PARAMS" ((list representing all the HTTP query parameters)
Perhaps, after the SQL query is executed, you can get the flow accessing one of the above context properties and using it in the filter...
HTH, Sergey

Re: Fetching and filtering data from a DB while processing a web service

kleinmat — Wed, 30 Apr 2014 12:56:56 GMT

Hi there,
I am actually working with SOAP.
Extracting the values of 1 or more request elements is not the problem for me.
But I don't know how to invoke a query to the database which is non-trivial (can't be described as simple join in the tXMLMap component).
Any idea?
Thanks
Matt

Re: Fetching and filtering data from a DB while processing a web service

_AnonymousUser — Fri, 08 Aug 2014 20:10:46 GMT

I've used the tDBInput component with dynamic SQL set in a tJava component (...as a context variable) to do some pretty serious SQL based on global or other data I know about coming into the web service via URL parameters...might be a direction to take...