How to insert or update data into table by using tmysqlrow_1?

Six Stars

How to insert or update data into table by using tmysqlrow_1?

how to Insert or update the data into table by using tmysqlrow_1 

 

QUERY:

 

"update CHECK_DATA SET FLAG='K' where FLAG='"+row1.invoiceno+"'"

 

show error,

Starting job FileProcess_Copy at 15:12 21/11/2017.

[statistics] connecting to socket on port 3914
[statistics] connected
Exception in component tMysqlRow_1 (FileProcess_Copy)
java.lang.NullPointerException
at local_project.fileprocess_copy_0_1.FileProcess_Copy.tFileInputDelimited_2Process(FileProcess_Copy.java:5779)
at local_project.fileprocess_copy_0_1.FileProcess_Copy.tFileList_1Process(FileProcess_Copy.java:1206)
at local_project.fileprocess_copy_0_1.FileProcess_Copy.tMysqlConnection_1Process(FileProcess_Copy.java:850)
at local_project.fileprocess_copy_0_1.FileProcess_Copy.runJobInTOS(FileProcess_Copy.java:12808)
at local_project.fileprocess_copy_0_1.FileProcess_Copy.main(FileProcess_Copy.java:12651)
[statistics] disconnected
Job FileProcess_Copy ended at 15:12 21/11/2017. [exit code=1]

 

 


Accepted Solutions
Seven Stars

Re: How to insert or update data into table by using tmysqlrow_1?

Ok in that case, you may want to try using a tmysqloutput component instead of tmysqlrow.

https://help.talend.com/reader/D_AHwO__5PjfWaaJKtDD1A/7IH8hy9EzjA85l5C4boMdA 

 

I read your use case again. Doesn't look like it will work using the tMysqloutput command.

As an alternative, We could output the distinct FLAG values to a flat file first.

And then you can do something like this,

updateVals.PNG

The tMysqlRow should have your update query for each distinct flag in the input file.

Six Stars

Re: How to insert or update data into table by using tmysqlrow_1?

Thanks for Support,

 

Now the update query is working fine , in tMysqlrow_1 rsz_1rsz_screenshot_from_2017-11-22_110832.png

 ADDED:

When we connected the tMysqlconnection , that update query is working or Else its show that previous error .


All Replies
Seven Stars

Re: How to insert or update data into table by using tmysqlrow_1?

Can you check if the mysql row component is configured correctly?

Try to execute a simple select statement to see if that works first before going to the update statement.

Six Stars

Re: How to insert or update data into table by using tmysqlrow_1?

Yes before we checked tmysqlrow ,we wrote the simple query select query its working fine ,

 

For Example:

 

I get the CSV data file to insert into the DataBase , its working fine . At the same time need to update the another table in Database to dynamically based on the records , we using tMap function to insert the data ,same as we try here get the some value & Update the another table.

 

af35965cadcb40d397dce6be9442cb92.jpgInside the tMap,f860c175778b4429a836f37d1f17b9ef.jpg

 

 

 

 

Seven Stars

Re: How to insert or update data into table by using tmysqlrow_1?

Ok so in your query you have used "row1.invoiceno" but the job shows that your input link is "update". So I am guessing it should be "update.invoiceno". Maybe you can try that out and see what happens.

 

edit : just checked the tmap again, it should actually be update.FLAG.

Six Stars

Re: How to insert or update data into table by using tmysqlrow_1?

we used the "update.invoice" but its shows errorce5dbfe1be5047a08dfb66f5c7118a35.jpg

 

So how we can solve this issue , any other methods have to update the database . 

Seven Stars

Re: How to insert or update data into table by using tmysqlrow_1?

Can you use update.FLAG?

Six Stars

Re: How to insert or update data into table by using tmysqlrow_1?

Yeah we can use "update.FLAG" , same error will come

 

[statistics] connecting to socket on port 3459
[statistics] connected
Exception in component tMysqlRow_1 (FileProcess_Copy)
java.lang.NullPointerException
at local_project.fileprocess_copy_0_1.FileProcess_Copy.tFileInputDelimited_2Process(FileProcess_Copy.java:5753)
at local_project.fileprocess_copy_0_1.FileProcess_Copy.tFileList_1Process(FileProcess_Copy.java:1180)
at local_project.fileprocess_copy_0_1.FileProcess_Copy.tMysqlConnection_1Process(FileProcess_Copy.java:828)
at local_project.fileprocess_copy_0_1.FileProcess_Copy.runJobInTOS(FileProcess_Copy.java:12657)
at local_project.fileprocess_copy_0_1.FileProcess_Copy.main(FileProcess_Copy.java:12500)
[statistics] disconnected
Job FileProcess_Copy ended at 17:10 21/11/2017. [exit code=1]

Seven Stars

Re: How to insert or update data into table by using tmysqlrow_1?

Ok in that case, you may want to try using a tmysqloutput component instead of tmysqlrow.

https://help.talend.com/reader/D_AHwO__5PjfWaaJKtDD1A/7IH8hy9EzjA85l5C4boMdA 

 

I read your use case again. Doesn't look like it will work using the tMysqloutput command.

As an alternative, We could output the distinct FLAG values to a flat file first.

And then you can do something like this,

updateVals.PNG

The tMysqlRow should have your update query for each distinct flag in the input file.

Six Stars

Re: How to insert or update data into table by using tmysqlrow_1?

Thanks for Support,

 

Now the update query is working fine , in tMysqlrow_1 rsz_1rsz_screenshot_from_2017-11-22_110832.png

 ADDED:

When we connected the tMysqlconnection , that update query is working or Else its show that previous error .

Seven Stars

Re: How to insert or update data into table by using tmysqlrow_1?

Great. Using the tMySQLConnection is a better way to connect to the database.

Six Stars

Re: How to insert or update data into table by using tmysqlrow_1?

Thanks for Support .


Ashok
Seven Stars

Re: How to insert or update data into table by using tmysqlrow_1?

You're welcome.

Moderator

Re: How to insert or update data into table by using tmysqlrow_1?

Hello,

Could you please mark this topic as 'Solved' if this solution is OK with your issue?

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.