how to fetch primary keys from db and set it to tmap entries
Hi,
I have a requirement where I have a set of excel files, I need to either create or update the entries in db. The creation part has been done. So if the run the job again it should update those entries. I searched in talend forum and i got an option for tmysqloutput as "update or insert".
First is this approach correct?
If so for update requires the primary key from db. How to fetch the primary keys of the entries in excel sheet and assign to it.
Kindly help as soon as possible.
!!! You can only get the ID of an existing parent by querying the database. So you can either have a lookup flow from tMysqlInput (rest_tab) to tMap_3 or embed a tMysqlRow and tParseRecordSet before tNormalize_1. The first is easier and faster but is taking you towards my previous suggestion anyway (which I think was a lot simpler).
I'm using tFilelist in my job so i need to use three different tfilelist in what you suggested and at anytime the same file will be processed or different files. Do you have any idea about it.
Can you post a screenshot of the job what you said. It would be easy to get it for me.
Why would you need three different tFileList? If your concern is to ensure the lookup is reloaded for each file, all you need to do is have the iterate connection from tFileList go to an empty tJava and then use OnComponentOK from that to tFileInputExcel.
OK, so we can take Name and Course as fixed values i.e. use those to look for the record in the table. But what you want is complicated because of the need to use the parent's ID as a foreign-key when you do an insert but the parent might already exist or you might only be creating it now. I think the best way is to do the parent and child in separate steps. First, read your file with just Name and Age in the schema, making Name the key field. Pass the flow to tUniqRow to eliminate the duplicates and then to your parent DB table. In the DB output component, set the "Action on data" to "Update or Insert". Now existing Names in the DB will be updated with the new Age and new Names will be inserted (with Age) and the ID generated by the DB. As the second step, read your file again as the main flow to tMap with a lookup flow from the parent table, joined on Name. In the output, make NAME_ID and Course the key fields with Amount as the other field. In the DB output component, set the "Action on data" to "Update or Insert". Now existing NAME_ID and Course combinations in the DB will be updated with the new Amount and new NAME_ID and Course combinations will be inserted (with Amount) and the ID generated by the DB.
With this job (as you said) the same problem will exist (if we insert a new child entry, the parent id returns as 0) because we are updating by name and not primary key, so the primary key will 0.
It should work because you would be doing the load in two stages: the first adding any new parents and the second reading all parents (new and old) to use in the children. Note that I suggest two sub-jobs i.e. read the file twice.
OK, so we can take Name and Course as fixed values i.e. use those to look for the record in the table. But what you want is complicated because of the need to use the parent's ID as a foreign-key when you do an insert but the parent might already exist or you might only be creating it now. I think the best way is to do the parent and child in separate steps. First, read your file with just Name and Age in the schema, making Name the key field. Pass the flow to tUniqRow to eliminate the duplicates and then to your parent DB table. In the DB output component, set the "Action on data" to "Update or Insert". Now existing Names in the DB will be updated with the new Age and new Names will be inserted (with Age) and the ID generated by the DB. As the second step, read your file again as the main flow to tMap with a lookup flow from the parent table, joined on Name. In the output, make NAME_ID and Course the key fields with Amount as the other field. In the DB output component, set the "Action on data" to "Update or Insert". Now existing NAME_ID and Course combinations in the DB will be updated with the new Amount and new NAME_ID and Course combinations will be inserted (with Amount) and the ID generated by the DB.
Only if select as auto commit in db level of talend, i can retrieve the parents records while creating child. Is thr any other way to retrieve without auto commit option. Will this cause any problem if a child record has not been created but the parent still exists.
I don't see why you should need to set auto commit. As long as the tMysqlOuput in the first subjob and the tMysqlInput in the second subjob share the same tMysqlConnection, then the tMysqlInput should read back all the existing records and all the new uncommitted records
