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]

 

 

2 ACCEPTED SOLUTIONS

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 .

12 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.