about inputoracle to Mysqloutput

Five Stars

about inputoracle to Mysqloutput

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.


Accepted Solutions
Thirteen Stars

Re: about inputoracle to Mysqloutput

@bangu00,please accept the solution.

Manohar B

All Replies
Thirteen Stars

Re: about inputoracle to Mysqloutput

Could you please post your job design?

Manohar B
Five Stars

Re: about inputoracle to Mysqloutput

thank you for your replay.

Ok, i uploaded my job.

tOracleInput.pngtmysqlOutputBulkExec.pnginserted_data.png

Thirteen Stars

Re: about inputoracle to Mysqloutput

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?

Manohar B
Five Stars

Re: about inputoracle to Mysqloutput

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

Thirteen Stars

Re: about inputoracle to Mysqloutput

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

Manohar B
Five Stars

Re: about inputoracle to Mysqloutput

hi, manodwhb

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

thank you

Thirteen Stars

Re: about inputoracle to Mysqloutput

still you have issue?

Manohar B
Five Stars

Re: about inputoracle to Mysqloutput

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?

Thirteen Stars

Re: about inputoracle to Mysqloutput

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.

Manohar B
Five Stars

Re: about inputoracle to Mysqloutput

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.

 

1.png2.png3.png4.png

Thirteen Stars

Re: about inputoracle to Mysqloutput

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

is YYYYMMDD HH24MISS format is allowed in mysql?

Manohar B
Thirteen Stars

Re: about inputoracle to Mysqloutput

still you have issue?

Manohar B
Five Stars

Re: about inputoracle to Mysqloutput

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.

 

Thirteen Stars

Re: about inputoracle to Mysqloutput

@bangu00,please accept the solution.

Manohar B