Datatype conversion using tdbrow!

[Haresh]

Guys,

I really need your help,i was using tdbsp (stored proc) before but now i need to use tdbrow for performing an insert into one of the oracle tables as part of the job flow.

Some of the columns in the insert statement are integers & date which is what is causing the problem and throws an error like "invalid number" and other things etc.I am able to manage the string part of it,but the really hassle lies with INTEGER datatype,can anyone of you guide me if i am missing any of the required quotes for successful insertion?

I am getting the fileID as Integer and storing it in global variable of tjava row:

globalMap.put("CFILEID", input_row.FILEID);

Now i am using it in the TDBROW component:

FILEID,DIRIRD,LOADID is stored as

number

in ORACLE datatype and FILE_DATE is stored as

DATE

in ORACLE.How do i convert in the required format?

 insert into table_file

   (

 fileid, dirid, loadid, file_name, file_date,file_size, 

  )

values

   (" +"\""+

 ((Integer)globalMap.get("CFILEID")) +","+

 NULL+","+ (kept it null for testing purpose)

 NULL+","+

 ((String)globalMap.get("CFILE_NAME")) +","+

 to_date(i_file_date,'dd-mon-yyyy hh24:mi:ss'),

 NULL+","+

)

The ORACLE insert command looks as below:

values (file_seq.nextval,

    l_dirid,

    l_loadid,

    i_file_name,

    to_date(i_file_date,'dd-mon-yyyy hh24:mi:ss'),

    i_file_size)

How to handle this specifically for INTEGER & DATE datatype from JAVA to ORACLE?

[Anonymous]

The problem here looks like it is about creating a valid insert statement with Java. The way to sort this is to start by simply using a tJava component to build your insert statement and output it to the sys out window. Then you can test them. Once you have that correct, the same code will work in the tDBRow component.

The one thing you appear to have left out in your description is the type of the "i_file_date" column supplied by Talend....not the Oracle type.