MySQL Childen insert after Parent insert with parent sequence

[Anonymous]

Hi can someone please help with the following:
I have the following input.
<soapenv:Envelope xmlns:soapenv=" http://schemas.xmlsoap.org/soap/envelope/" xmlns rof=" http://www.dstv.com/services/ProfileManager/">
<soapenv:Header/>
<soapenv:Body>
<prof:CreateProfileRequest>
<prof rofile>
<prof rimaryMSISDN>27829903055</prof rimaryMSISDN>
<prof econdaryMSISDNs>
<prof:MSISDN>27839932439</prof:MSISDN>
</prof econdaryMSISDNs>
<prof:ConnectID>1234567</prof:ConnectID>
<prof:CustomerNumber>9129929179</prof:CustomerNumber>
<prof:AccountNumber>117054121</prof:AccountNumber>
<prof martCardNumbers>
<prof martCard>1234567890</prof martCard>
<prof martCard>0987654321</prof martCard>
</prof martCardNumbers>
<prof:Currency>ZAR</prof:Currency>
<prof:Balance>100.01</prof:Balance>
<prof:CreditLimit>700.00</prof:CreditLimit>
<!--type: CountryName - enumeration: -->
<prof:Country>South_Africa</prof:Country>
<prof:IBSDSN>South_Africa_QA</prof:IBSDSN>
<prof:HasDebitOrder>false</prof:HasDebitOrder>
</prof rofile>
</prof:CreateProfileRequest>
</soapenv:Body>
</soapenv:Envelope>
In the above the SmartCardNumbers and SecondaryMSISDNS are stored with a foreign key referencing the Profile.
I have the following Job that needs to.
1. Insert into profile
2. get last inserted ID
3. foreach msisdn insert it
3. foreach smartcard insert it
Thanks for any help it would be greatly appreciated.
This form of problem seems to come up quite frequently as one is not permitted to "short circuit" a design and input data is frequently required after a process such as a web service call or a database operation.
What is the recommended solution to this?
e.g.
input.a
input.b --> WebServiceOperation(input.a) --> map(output, input.b) --> db.select(input.c) --> foreach input.c - db.insert(select.sequence, map, input.c)
I have looked at context variables and tflowtoiterate etc and there is no concrete evidence on talend's recommended method to achieve this.

Thanks in advance
Graham

[_AnonymousUser]

create trigger child_insert after insert on ... child_insert after insert on child for each row begin update parent set number_children

[alevy]

I use MS SQL Server rather than MySQL so I can't guarantee the following...
For a single child, you should be able to just: make sure both the parent and child tMysqlOutput components share a connection, in the first (parent) uncheck "Extend Insert" and in the second (child) use the "Additional columns" to replace the field that should contain the foreign-key with the SQL expression "LAST_INSERT_ID()" i.e. no need to use tMysqlLastInsertId. See https://community.talend.com/t5/Design-and-Development/mapping-foreign-key-in-Tmap-with-2-mysqloutpu....
Googling the LAST_INSERT_ID() function, however, reveals no way to specify which table's last inserted ID you're interested in, which means that using this approach for the second child would probably get the first child's last inserted ID instead of the parent's.
In that case, I think you need to modify your design as follows:
- Ensure all the tMysqlOutputs share a connection.
- Uncheck "Extend Insert" in CustomerProfile.
- Add tJavaRow after tMysqlLastInsertId, in which you can store the last inserted ID into a context or globalMap variable.
- Add tJavaRow between tXMLMap and tMysqlOutput for each of the children, in which you can populate the foreign-key column with the variable stored in the previous step.

[Anonymous]

HI Alvey, thanks for the response this works just fine if one has a tMySQlConnection and all the tMySQLIn/Outputs use the connection and as you mention ensrre the extended insert is unchecked.
There is a good explanation by xdshi that can be found https://community.talend.com/t5/Design-and-Development/How-to-insert-a-parent-and-many-child-entries... as well.
It was my misunderstanding of how to use tSetGlobalVar as well as the flow of a Job. However Shong has guided me on that point to
I have only been using Talend for the last month so most of my errors are PICNIC errors
( Problem In Chair Not In Computer )

Thanks
Graham