topic Re: about inputoracle to Mysqloutput in Talend Studio

about inputoracle to Mysqloutput

Anonymous — Fri, 30 Mar 2018 02:44:13 GMT

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.

Re: about inputoracle to Mysqloutput

manodwhb — Fri, 30 Mar 2018 05:21:43 GMT

Could you please post your job design?

Re: about inputoracle to Mysqloutput

Anonymous — Fri, 30 Mar 2018 05:56:36 GMT

thank you for your replay.

Ok, i uploaded my job.

Re: about inputoracle to Mysqloutput

manodwhb — Fri, 30 Mar 2018 06:47:33 GMT

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?

Re: about inputoracle to Mysqloutput

Anonymous — Fri, 30 Mar 2018 09:44:52 GMT

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

Re: about inputoracle to Mysqloutput

manodwhb — Fri, 30 Mar 2018 09:55:37 GMT

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

Re: about inputoracle to Mysqloutput

Anonymous — Fri, 30 Mar 2018 10:23:16 GMT

hi, manodwhb

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

thank you

Re: about inputoracle to Mysqloutput

manodwhb — Mon, 02 Apr 2018 05:43:41 GMT

still you have issue?

Re: about inputoracle to Mysqloutput

Anonymous — Mon, 02 Apr 2018 08:08:06 GMT

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?

Re: about inputoracle to Mysqloutput

manodwhb — Mon, 02 Apr 2018 08:15:47 GMT

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.

Re: about inputoracle to Mysqloutput

Anonymous — Mon, 02 Apr 2018 09:21:24 GMT

hi..

i tried to do as below images. but the data set 0000-00-00 on mysql,

So, i changed my sql of oracle : "select substr(p.startdate,1,15) as startdate from awlotproduct where rownum<=10"

and data has been inserted on the mysql correctly.

Actually, data type the startdate column is char, so, i didn't need converting CHAR to TO_DATE.

i just wanna know how to insert to_date type on the mysql.

Re: about inputoracle to Mysqloutput

manodwhb — Mon, 02 Apr 2018 11:11:16 GMT

i am not sure about mysql,since i am familiar with Oracle.

is YYYYMMDD HH24MISS format is allowed in mysql?

Re: about inputoracle to Mysqloutput

manodwhb — Tue, 03 Apr 2018 10:20:52 GMT

still you have issue?

Re: about inputoracle to Mysqloutput

Anonymous — Tue, 03 Apr 2018 11:56:42 GMT

hi Manohar

i sorry for late replay.

Ok. mysql not allow date format to_date(startdate,'YYYYMMDD HH24MISS').

it may change to "str_to_date(startdate,'%Y%m%d %H%i%s')"

as i mention, i changed sql statement of oracle. so, the problems was fixed.

I really appreciate your time and effort to help.

Re: about inputoracle to Mysqloutput

manodwhb — Tue, 03 Apr 2018 12:47:36 GMT

@bangu00,please accept the solution.