[resolved] How to generate database primary key & use it inside tmap ?

[_AnonymousUser]

Hi, I need to migrate data from one application to the other with processing & normalizing data to fit new structure.
i would like to have old data as input (1 table) & new data as output (3 tables).
Let say that we want to get client data from old application. In old data client had only one contact information, now it can have many contact details. I wanted to do it inside tMap in one step, but i don't know how could i generate contact id in oracle table & use it in client2contact table in the same tMap component. Is it possible somehow?
On left side i have:

oldDocument{
clientId
clientName
clientContact
}

i have to migrate this into 3 tables:

contact{
contactId - which we don't have yet, we have to generate it
contactDetails - which is mapped to oldDocument.clientContact
}
client{
clientId - this part is easy
clientName - this part is easy
}
client2contact{
clientId - we have to use contactId generated for table contact
contactId
}

I know how to generate id using for example hibernate sequence in oracleOutput advanced tab, but i don't know if i can use it somehow in tMap ?
Maybe we can use some rutines or sth else? Or maybe i should split it into more steps?
Im just starting with this tool so thanks for any hints.

[Anonymous · Accepted solution]

Hello

contactId - which we don't have yet, we have to generate it

Using Talend build-in function Numeric.sequence("s1",1,1) to generate a sequence id.

client2contact{
clientId - we have to use contactId generated for table contact
contactId
}

I think you have spelling error, right? The correct intention is:
clientId
contactId - we have to use contactId generated for table contact
For more details, please see my screenshots.
Best regards
Shong

[_AnonymousUser]

THank you!
That was exactly what i wanted to achive. I wil try that solution right now.

[_AnonymousUser]

Hello

contactId - which we don't have yet, we have to generate it

Using Talend build-in function Numeric.sequence("s1",1,1) to generate a sequence id.

client2contact{
clientId - we have to use contactId generated for table contact
contactId
}

I think you have spelling error, right? The correct intention is:
clientId
contactId - we have to use contactId generated for table contact
For more details, please see my screenshots.
Best regards
Shong

Hi,
unfortunatelly, i need to use my hibernate sequence.
I've added column with expression "HIBERNATE_SEQUENCE.nextval" in contact table & it is saved correctly in database.
But when i want to use this id in last tmap & save as contactId in contact2client table, id is always null & result table is empty.
How can i easilly use database sequence there ?

[Anonymous]

Hi
If you use "HIBERNATE_SEQUENCE.nextval" to generate contact id, you need to select the last inserted contact id base on each row before insert it to contact2client table, because the contact id is generated by db engine, not Talend job. Here I redesign the job a little to adjust to your request.
Best regards
Shong

[_AnonymousUser]

Hi,
thanks for helping me out
I've managed to do this yesterday evening, but i didn't ghad a time to poste the solution.
It is quite different from your proposition but it works for me - at least now
I've added sql query "Select HIBERNATE_SEQUENCE.nextval as nextval from dual" as oracleInput & then directed it to the map where i'm getting contact details & mapping them into contact table. I've mapped contactId to this nextval value & i've also changed this select input tMap settings to reload for each row. Thanks to this, i had new id generated for every row. I had to slightly change connections between components, but this works.
again - thanks for your help.

[Anonymous]

Hi
I am glad to see that you get it works. It is time to close the topic now.
Best regards
Shong