[resolved] Lookup row in DB from value specified in rest request and return JSON

One Star

[resolved] Lookup row in DB from value specified in rest request and return JSON

Hey all!  I am having an issue getting values to return from a database query.  I also have a couple other questions.  
1. How can I do a real time db lookup using rest?  - see screenshots below of what I have so far thats not working
2. Is there an easier way to specify variables?  (This syntax is cumbersome and not intuitive, I understand it's probably a standard ex.  '"+ globalMap.get("getMember.ID")   ) - see screenshots 
3. Do I have to define the schema manually?  I couldn't get it to work if I didn't do this.  After I defined the manual schema I was then able to build my sql query, map the process flow (and xml map), and get a response.  Why do I have to do this?   Can't I just make the query and then use that to dynamically create a schema or not do the schema at all and just map it using the xml map? - see screenshots 
Employee

Re: [resolved] Lookup row in DB from value specified in rest request and return JSON

Hi Chris
Here are some suggestions.

#1 A good place to start for an end-to-end (simple) REST example is on Talend YouTube 




2.  There are easier ways in this particular case, but in general I agree that the syntax is awfully cumbersome.  I hope you used Outline browser on the lower left to fill in the globalMap syntax.  

In this particular case, you could put a tJavaRow in front of the tFlowToIterate and then reference the tJavaRow's ID field from the SQL query.  But that is pretty bad form since you are in fact _looping_ and referencing the same ID value for each pass of the loop (albeit there is only a single pass of the loop).  So I would not recommend this.

Alternatively, you could treat the REST request as the "primary" record flowing into a tMap and do a LookupOnEachRow with tMap. That may be a little bit cleaner looking, but I don't think it merits the overhead and (generated) code complexity introduced with tMap.

3.  I assume you mean the REST (JSON) request schema in the third picture?  Or do you mean the mean XML response in the first picture.  You can import an XSD into the metadata repository and then rick click on the body element in the tXMLmap and select Import from Repository.  You should see an example in the video.
One Star

Re: [resolved] Lookup row in DB from value specified in rest request and return JSON

Thanks Ed the video was helpful.  I still have one issue though.  I think because I don't have single quotes around the getMember.ID variable call, it tries to convert the column to an int when running the query which fails.  How do I put the single quotes around the call?  
"SELECT CL_MEMBERS.MEMBER_ID,
CL_MEMBERS.MEMBER_FIRST_NAME
FROM CL_MEMBERS WHERE CL_MEMBERS.MEMBER_ID =  " + globalMap.get("getMember.ID")


By schema, I meant the edit schema button on the SQLinput component.  Why do I have to define all the columns here manually?
One Star

Re: [resolved] Lookup row in DB from value specified in rest request and return JSON

Also, I could not use the outline flow to fill in the global map variable because it was not listed there, I wonder if I am using the correct variable.  This variable is used in the schema of my output flow... see images below

Employee

Re: [resolved] Lookup row in DB from value specified in rest request and return JSON

The expression you are building is a standard Java statement in the end.  So you need to follow standard Java syntax to build a string that conforms to your target SQL syntax.
For the schema of the SQLinput component you use the Guess Schema to infer the schema from you sql query, but the sql query has to be successfully evaluated.  Given that you are using a global map in the conditional clause, you may need to remove the where clause and click on Guess Schema.
The getMember.ID is defined by you in setting up the tRESTrequest component, so it is not a fixed attribute of the tRESTcomponent.  That is why it does not show up in the outline view.  The outline view is fairly static, it only knows about fixed properties of components.
One Star

Re: [resolved] Lookup row in DB from value specified in rest request and return JSON

Thanks I was able to correct the query with the following syntax 
"SELECT CL_MEMBERS.MEMBER_ID,CL_MEMBERS.MEMBER_FIRST_NAME FROM CL_MEMBERS WHERE CL_MEMBERS.MEMBER_ID = '" + globalMap.get ("getMember.ID")+"'"

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Talend API Designer – Technical Overview

Take a look at this technical overview video of Talend API Designer

Watch Now

Getting Started with APIs

Find out how to get started with APIs

Read