What is the best way to generate unique ID numbers

[Anonymous]

I want to generate random but unique ID numbers in a certain format for insertion in a database - what is the recommended procedure to achieve this?

[Anonymous]

depends on the format and the rules you need for that
Best way, escpecially with databases is to let the database do that job -> auto-increment
Assuming that this is not helping you with your problem you could use the talend routine Numeric.sequence and editing the result accordingly to any rule or format you need to match
Lordan

[Anonymous]

Thank you. Basically we want to generate SugarCRM IDs which must be unique and on this format:
208ae41d-6f4c-4500-bf28-49cfaa77c9aa
As you can see they are actually hexadecimal, however I am sure using only 0-9 would suffice as long as the ID generated is unique within the system.
When does the numeric sequence reset itself, every time the job runs or when Talend starts?

[Anonymous]

depends on your ToS version
but you can easily decide when it has to be restarted, use the method Numeric.resetSequence(String seqName, int startValue)
Usage:
Numeric.sequence("mySeq", 1, 1) -> sequence name = mySeq, start value 1, step 1
-> Numeric.resetSequence("mySeq", 1)
But I fear it would be complicated to use that for your needed format, better write a routine doing that for you

[Anonymous]

Even better - it turns out that there is actually a MySQL function called UUID() which generates exactly this type of ID!

[Anonymous]

perfect 🙂

[Anonymous]

Now just how to tell Talend to utilize this MySQL function with an expression within tMap - is that even possible?

[Anonymous]

I don't know the syntax right now but use a tMysqlRow, so you can write the query yourself including function calls
the value in your data flow put as a input link in that row component so you can access the value.
If the data flow from the tMap is named row1 and the needed column Id just enter in the query

..." + row1.Id + "....

[Anonymous]

Thank you - is it not possible to call this function in a tMap expression? I need to do other magic such as combining two address fields into one etc, so I will need a tMap anyway.

[Anonymous]

Unfortunately, in the job I have created now (see screenshot), the outcome of the last_insert_id is 0 and not the result of a UUID() function call.
FYI: After the screenshot was taken the query has been edited to have quotes around it so it now reads:
"INSERT INTO leads (id, account_name)
VALUES(UUID(),'TESTTESTTEST')"