about inputoracle to Mysqloutput

[Anonymous]

hi

i have open studio DI 6.5.1.  and i trying to run a job with oracleinput to mysqlouput

oracle query is "select to_date(substr(startdate,1,15),'YYYYMMDD HH24MISS') as startdate from a_table"

and the data insert into mysql to "0000-00-00"

please, let me know anybody.

[manodwhb]

Could you please post your job design?

[Anonymous]

thank you for your replay.

Ok, i uploaded my job.

[manodwhb]

can you check the ouput schema data type for that cloumn in tOracleinput and if you executed that qurey in DB leavel instead of Talend,what output are you getting?

[Anonymous]

hi

i tried to execute sql statement on Oracle database as you recommanded.

and the the data type of the column is CHAR(18).

SQL> alter session set nls_date_format = 'YYYYMMDD HH24MISS';

session altered.

SQL> select to_date(substr(startdate,1,15),'YYYYMMDD HH24MISS') as startdate from awlotproduct where rownum<=1;

STARTDATE
---------------
20140307 201032

[manodwhb]

ok ,can you write it to tLogrow from tOracleinput? show the ouput data. and Can you show the schema of tOrcalinput also.

if you could able to write date to tLogRow,then,you may need to check for the date formats in mysql.please find the below linke verify the date formats in mysql.

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

[Anonymous]

hi, manodwhb

Ok. i am going to do that and will replay as soon as i can

thank you

[manodwhb]

still you have issue?

[Anonymous]

Hi manodwhb

i tried as you suggested.

1. Oracleinput -> Logrow -> MysqlOutputBulkExec.

   oracle sql : "select to_date(substr(startdate,1,15),'YYYYMMDD HH24MISS') as startdate from awlotproduct where rownum<=1"

2. here is log of the job.

Starting job HSHWANG at 18:54 30/03/2018.
[statistics] connecting to socket on port 3451
[statistics] connected
STARTDATE
[tLogRow_1] 20140307 201032
[tLogRow_1] 20140307 202113
[tLogRow_1] 20140301 040920
[tLogRow_1] 20140301 042043
[tLogRow_1] 20140301 042208
[tLogRow_1] 20140301 044648
[tLogRow_1] 20140301 044648
[tLogRow_1] 20140301 044714
[tLogRow_1] 20140301 044714
[tLogRow_1] 20140301 070249
[statistics] disconnected
Job HSHWANG ended at 18:54 30/03/2018. [exit code=0]

3. i modifed Date Pattern in Edit Schema on mysqloutputbulkexec

   startdate(Oracle) : "yyyyMMdd HHmmss"

   startdate(Mysql) : "yyyyMMdd HHmmss"

4. i executed my job, and the result is as below:

StartDAte

--------------

0000-00-00

5. Otherwise,  i modifed Date Pattern in Edit Schema on mysqloutputbulkexec

   startdate(Oracle) : "yyyyMMdd"

   startdate(Mysql) : "yyyyMMdd"

6. and the result is as below:

sql : select * from TEST_KYKWON

StartDAte

--------------

2014-03-07

Why is the result different by date patern?

[manodwhb]

put the tmap between oracleinput and ysqloupt and specify the require outpout format in tmap out put section.

might be that format is not accepted in mysql.