topic Re: Send Database Query Results to JSON tRestClient component? in Talend Studio

Send Database Query Results to JSON tRestClient component?

CTabar1665588301 — Fri, 15 Nov 2024 22:27:58 GMT

I have an integration that needs to perform the following workflow:

Retrieve data from an MS SQL Server database, usually multiple rows of data
Convert that database data to JSON format and send to a POST tRestClient, preferably one row at a time

Getting the data out of the database is no problem. However, I am struggling with determining and configuring the components needed to format/build the JSON. Any suggestions?

Re: Send Database Query Results to JSON tRestClient component?

aaronrums — Wed, 12 Oct 2022 18:21:51 GMT

If it's simple, you can use tFileOutputJSON. This will easily generate an object for each record returned by your query. You can click "generate an array json" to put all the objects in a big list.

If your structures needs to be more complex than that, I'm not sure. I have a similar requirement but with a more complex structure than just 1 level, and I've read that it might just be easier (for some) to write custom java code. Unfortunately, I don't know java.

Re: Send Database Query Results to JSON tRestClient component?

CTabar1665588301 — Wed, 12 Oct 2022 18:24:32 GMT

Is it possible to do this without generating a file every time?

Re: Send Database Query Results to JSON tRestClient component?

aaronrums — Wed, 12 Oct 2022 18:43:41 GMT

I would think it's possible using the tWriteJSON component, but I actually am learning what that one does right now myself. It's not ideal, but you could always write the JSON to a file, read it and do the stuff you need, then delete it.

Re: Send Database Query Results to JSON tRestClient component?

CTabar1665588301 — Wed, 12 Oct 2022 18:51:53 GMT

Thank you for the reply.

I've been trying to use the tWriteJSONField component and haven't really gotten anywhere.

I'm trying to avoid having to write the file. Seems like it shouldn't be necessary to write it to a file.

Re: Send Database Query Results to JSON tRestClient component?

aaronrums — Wed, 12 Oct 2022 18:59:01 GMT

Agreed, sorry I can't be more help. Here's a post that at least shows a configured WriteJSON component, maybe that'll set you on the right path.

headers in twriteJSONField (talend.com)

Re: Send Database Query Results to JSON tRestClient component?

Anonymous — Thu, 13 Oct 2022 02:32:49 GMT

TrestClient requires the body with document type, so you can use tXMLMap to build the document and it will be converted to Json format when it is passed to rest web service, or you can use tWriteJsonField+tRest components.

Regards

Shong