[resolved] using toracle row for insert into oracle table
Hi, my job is like : tfileList-->tfileinputdelimite-->tmap-->toracleoutput--->onComponentOK-->toraclerow i want use toaclerow to insert information into a oracle table : this is my query : "INSERT INTO KPI.HTTP_TRACE(NOM, NBRE_LIGNES_FICHIER , NBRE_LIGNES_TABLE , NBRE_LIGNES_INSER , NBRE_LIGNES_REJET, ENTER_DATE) values ('" + ((String)globalMap.get("tFileList_1_CURRENT_FILE")) +"',"+ ((Integer)globalMap.get("tFileInputDelimited_1_NB_LINE")) +"',"+ ((Integer)globalMap.get("tOracleOutput_1_NB_LINE"))+"',"+ ((Integer)globalMap.get("tOracleOutput_1_NB_LINE_INSERTED"))+"',"+ ((Integer)globalMap.get("tOracleOutput_1_NB_LINE_REJECTED"))+"',"+ TalendDate.getCurrentDate()+ ")" when i execute i have error that missing comma, and also i have an id primary key in my table so how can i insert the value of this primary key Thanks for your help
Check the records in your output database...whether all the data is inserted? connect reject link from output to flat file/logrow.. disable iterate component and check the result..identify which component is giving the message... enable die on error in db component, you will get some stack trace... this may help to identify an issue.. Vaibhav
Thanks Vaibhav, the problem in the component toraclerow toracleoutput insert all the data but the message that i have missing comma in my query i don(t understand how can i correct it have any idea how can i resolve this problem is it my query correct ?
Well good that you are progressing...
Use tjava component and use system.out.println(your query in row component) use printed output query in your sql management studio and fire the query... you will understand the issue...
Vaibhav
Thanks Vaibhav for your help, I don't understand why you want use tjava and how i put it in my job and how printed output query in my sql management studio ? how i fire the query Can you explain to me please Thanks,
use iterate(3) and connect it to tJava component from tfilelist... this is for debugging purpose... whatever query you have writtin in dbsqlrow component is having some issue... when you print the query, you will get exact query which is fired against database... copy query printed on console and fire it in management studio to verify an issue..
Vaibhav
Thanks for tour reply, I have a lot of erros : i write this in the tjava : System.out.println(""INSERT INTO KPI.HTTP_TRACE(NOM, NBRE_LIGNES_FICHIER , NBRE_LIGNES_TABLE , NBRE_LIGNES_INSER , ENTER_DATE) values (" + ((String)globalMap.get("tFileList_1_CURRENT_FILE")) +","+ ((Integer)globalMap.get("tFileInputDelimited_1_NB_LINE")) +","+ ((Integer)globalMap.get("tOracleOutput_1_NB_LINE"))+","+ ((Integer)globalMap.get("tOracleOutput_1_NB_LINE_REJECTED"))+","+ TalendDate.getCurrentDate()+")" ")
I have in my console:
Starting job LoadHttp at 11:19 04/08/2014.
connecting to socket on port 3596
connected
INSERT INTO KPI.HTTP_TRACE(NOM, NBRE_LIGNES_FICHIER , NBRE_LIGNES_TABLE , NBRE_LIGNES_INSER , ENTER_DATE) values (TUNIS_02-0-03-16-21-00-Hosts.txt,406,406,0,Mon Aug 04 11:19:44 PDT 2014)
ORA-00917: missing comma
ORA-00917: missing comma
INSERT INTO KPI.HTTP_TRACE(NOM, NBRE_LIGNES_FICHIER , NBRE_LIGNES_TABLE , NBRE_LIGNES_INSER , ENTER_DATE) values (TUNIS_02-0-03-17-21-00-Hosts.txt,403,403,0,Mon Aug 04 11:19:45 PDT 2014)
disconnected
Job LoadHttp ended at 11:19 04/08/2014.
so the global variable toracleoutput.nblines does'nt inserted i dont why ?
how can i resolve this ?
Thanks
That's ok, this is not an issue.. for insert. You need double quotes for your first and last column use +"\""+ before and after first and last columns..
the query should be like this : "INSERT INTO KPI.HTTP_TRACE(NOM, NBRE_LIGNES_FICHIER , NBRE_LIGNES_TABLE , NBRE_LIGNES_INSER , ENTER_DATE) values (" +"\""+ ((String)globalMap.get("tFileList_1_CURRENT_FILE")) +","+ ((Integer)globalMap.get("tFileInputDelimited_1_NB_LINE")) +","+ ((Integer)globalMap.get("tOracleOutput_1_NB_LINE"))+","+ ((Integer)globalMap.get("tOracleOutput_1_NB_LINE_REJECTED"))+","+ TalendDate.getCurrentDate()+"\""+")"
