[resolved] using toracle row for insert into oracle table

One Star

[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 
One Star

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

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

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

Hi 
  ((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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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
Four Stars

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

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
One Star

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

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
Four Stars

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

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
One Star

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

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 
Four Stars

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

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
One Star

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

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
Four Stars

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

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
One Star

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

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 ?
Four Stars

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

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
One Star

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

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,
Four Stars

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

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
One Star

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

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

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

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
One Star

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

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
Four Stars

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

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
One Star

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

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

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

or
You can think of removing the tdbrow component and replace it by the tfixedflow-->tdboutput component and use respective columns and their values for insert.
this is much simpler than performing with tdbrow
Vaibhav
Four Stars

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

Double quotes must be placed before and end of each string...
One Star

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

I have this error : ORA-00972: identifier is too long
ORA-00972: identifier is too long
and 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()+"\""+")"
Four Stars

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

Oh... sorry... for oracle you need single quote and not double quote.. +"'"+ around the string..
Vaibhav
Seventeen Stars

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

hi all,
keep it simple & use tFixedFlowInput to fix your values from globalMap in column and a main flow to wtire in DB.
it will be more easy for debug & 'maintenance'.
regards
laurent
One Star

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

Thanks a lot vaibhav for your help appreciated it works finally 
Thanks Laurent for your reply, I will try it 
Four Stars

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

 for making it work...
Vaibhav
One Star

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

Hi Sanvaibhav
for the date when i use : TalendDate.getCurrentDate();
I get the date in this format : Sat Aug 23 00:34:44 PDT 2014 
but i would have in this format 23/08/2014 00:34:44 
how can i do this ??
Thanks for help i try with parseDate but it does'nt work 
Seventeen Stars

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

Hi Fetyna,
please understand the Java type Date! The method TalendDate.getCurrentDate() returns the Java object (a binary representation) of a Date. Here is no textual presentation, therefore there is no pattern needed. It like the database type DATE from Oracle, here is also no pattern needed to be applied.
A pattern is only necessary if you read a Date object and wants it printed in the console or a file or if you read text from a file and need to convert it into a real Date object. Ofter unexperienced developers see the date value in a SQL editor and think the date field it self must have any kind of pattern but this is not the case, the program showing the date value have to have its own pattern how the date value should be shown to the user.
Because of the method TalendDate.getCurrentDate() returns already a Date object the method TalendDate.parseDate does not make sense because this method expects a String (textual representation of a date) and returns the Java object Date -> what you already have as the result of TalendDate.getCurrentDate() !!!
In the database a date is also stored in an internal binary format WITHOUT pattern of course!

I strongly suggest you invest some time and learn Java an some basic skills about databases like Oracle. It will make your life as developer much easier!