topic Re: Save to parent and child table (foreign key) in Talend Studio

Save to parent and child table (foreign key)

michaelh1 — Sat, 16 Nov 2024 02:24:27 GMT

Hi i have following problem:

I am parsing XML document to store these data into to two tables Author and Book:

Parent table Author (it works fine) and at the same time persist into child table Book with attribute author_id (fk) from Author table.

Persistng author works fine so far (blue arrow show component that persist author), but how can i continue in persisitng books with author_id as foreign key, how can i get the ID of persisted author?

P.S. I am using postgresql DB so tlastinsertedID doe not work for me.

Re: Save to parent and child table (foreign key)

manodwhb — Tue, 19 May 2020 07:25:54 GMT

@michaelh, Yes you do not have lastInsertid component for PostgreSQL, so you need to design two jobs one for author and another one for Book. while loading Book you need to join with the author to get author_id and insert into Book table.

Re: Save to parent and child table (foreign key)

michaelh1 — Tue, 19 May 2020 07:38:16 GMT

manodwhb can you please give me some hint how to achieve this?

so letas say i have parsed xml with books and authors with tXMLMap component i have two outputs:

out1-authors

out2-books

(please see attached pictures)

how should i continue in flow after tXMLMap component? (how to store authors and books at the same time(books have author_id as FK))

Re: Save to parent and child table (foreign key)

manodwhb — Tue, 19 May 2020 07:49:30 GMT

@michaelh, you need to separate two flows and read source file two separately.

Re: Save to parent and child table (foreign key)

michaelh1 — Tue, 19 May 2020 07:57:45 GMT

@manodwhb so firstly i have to parse authors save the and then parse books and save them right?

but last thing is that i need this to be done in one transaction so is this pattern ok?

Re: Save to parent and child table (foreign key)

manodwhb — Tue, 19 May 2020 09:24:36 GMT

@michaelh , Yes create separate jobs.