topic Re: [resolved] using toracle row for insert into oracle table in Talend Studio

[resolved] using toracle row for insert into oracle table

Anonymous — Fri, 01 Aug 2014 20:42:04 GMT

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

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Sat, 02 Aug 2014 05:50:54 GMT

Hello
You probably missed some single quotes at the end of your lines : +"',"+

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Sat, 02 Aug 2014 06:10:19 GMT

((Integer)globalMap.get("tFileInputDelimited_1_NB_LINE")) +"',"+

There is a syntax error in your query, you don't need to add single quote for Integer type.
Best regards
Shong

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Sat, 02 Aug 2014 11:51:53 GMT

Thanks Shong,
so my query should like this :
"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()+
")"
i still have the same error missing comma
help me please
Thanks

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Sat, 02 Aug 2014 13:10:28 GMT

Hi,
Try following
"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()+")"
Copy paste into notepad and from notepad to your sqlrow component...
Vaibhav

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Sat, 02 Aug 2014 13:19:16 GMT

I copy your query But i still have the same error
ORA-00917: missing comma
ORA-00917: missing comma
can you explain to me if my conception of job is correct the link on componentok which execute the toraclerow to insert the data into my table for each file or what
help me please i don't know how can i resolve the error
Thanks

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Sat, 02 Aug 2014 14:37:27 GMT

Not able to visualize what you have written... but it is sure that we don't need OnComponentOk to ensure that the previous task is completed.... In place of this, we need OnSubjobOk link..
I will be more clear if you can put the screenshot of your job.
Vaibhav

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Sat, 02 Aug 2014 15:18:17 GMT

This is my screen shot

i want to load into a table for each file the numbre of lines of the files and the numbre of toracleoutput insert
i copy your query but i have the error of missing comma
so is my job correct for each file can i insert the numbre of lines and nbre inserted
and is my query correct
Thanks for your great help

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Mon, 04 Aug 2014 07:46:45 GMT

For tOracleRow_1 component, use anther iterate(2) link from tFileList. This insures that the first data insert operation is complete and you are starting second operation after completion of first...
How you are getting number of lines rejected? whether the Reject link is available from your output component? This link is available
- when you disable die on error
- disable batch count in advance setting
Vaibhav

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Mon, 04 Aug 2014 09:42:04 GMT

i do the link iterate from tfileList to toracle row but i have the same error
this my query :
"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()+")"

can you help me please
Thanks

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Mon, 04 Aug 2014 09:56:38 GMT

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

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Mon, 04 Aug 2014 10:04:29 GMT

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 ?

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Mon, 04 Aug 2014 10:13:38 GMT

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

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Mon, 04 Aug 2014 10:31:24 GMT

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,

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Mon, 04 Aug 2014 10:54:29 GMT

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

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Mon, 04 Aug 2014 11:11:08 GMT

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()+")"
")

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Mon, 04 Aug 2014 11:17:48 GMT

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()+")")

Use above statement in your tJava

Vaibhav

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Mon, 04 Aug 2014 11:22:53 GMT

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

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Mon, 04 Aug 2014 11:25:11 GMT

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..

Vaibhav

Re: [resolved] using toracle row for insert into oracle table

Anonymous — Mon, 04 Aug 2014 11:30:42 GMT

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()+"\""+")"