Four Stars

using tRESTclient and 3rd party service to update database fields

I think I'm missing something obvious here but I'm lost and need some help!  I have a job where I'm extracting addresses from a database and then using a 3rd party REST API to get coordinates (lat/lon) for those addresses.  I then want to update the lat/lon fields in the database for those rows.  My question is: how do I keep the primary key of each row linked to the results of the tRESTclient?

 

The setup of the job is:

MSSQLinput ---> tXML_Map ---> tREST_Client ---> tExtractJSONfields ---> MSSQLoutput (same table as input at beginning)

 

I can interact with the API fine, but once I have the lat/lon I don't have the primary key from the original address.  This means I can't update the table in the MSSQL database.  I do, at least, get a version of the street address back from the API but this has been modified from the version extracted at the beginning so I can't use this to perform a lookup for the output.  I'll try to provide a simple example:

 

MSSQLinput:     ID: 1, Address: "1/123 Fake Street"

To APIAddress: "1/123 Fake Street"

From API: Address: "Unit 1, 123 Fake Street", Lon: 153.0, Lat: -29.0

MSSQLoutput: ID: ??, Lon:153.0, Lat: -29.0

 

Is there a way for me to capture the ID at the start and then apply it at the end?  Any help/thoughts/advice much appreciated.

 

image.png

1 ACCEPTED SOLUTION

Accepted Solutions
Four Stars

Re: using tRESTclient and 3rd party service to update database fields

I managed to get a solution to this one by setting a context variable using the initial extract from the database table, and then using that context variable as the primary key when updating the same table after getting coordinates from the address.  I did this using tJavaRow prior to the XML map, and a tMap prior to updating the database.  Excuse the messy diagram below.  You can ignore all the tLogRows.

 

image.png

1 REPLY
Four Stars

Re: using tRESTclient and 3rd party service to update database fields

I managed to get a solution to this one by setting a context variable using the initial extract from the database table, and then using that context variable as the primary key when updating the same table after getting coordinates from the address.  I did this using tJavaRow prior to the XML map, and a tMap prior to updating the database.  Excuse the messy diagram below.  You can ignore all the tLogRows.

 

image.png