topic Re: How to insert or update data into table by using tmysqlrow_1? in Talend Studio

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

Ashok200792 — Tue, 21 Nov 2017 10:08:53 GMT

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]

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

Anonymous — Tue, 21 Nov 2017 10:12:51 GMT

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.

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

Ashok200792 — Tue, 21 Nov 2017 10:44:11 GMT

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.

Inside the tMap,

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

Anonymous — Tue, 21 Nov 2017 11:13:28 GMT

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.

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

Ashok200792 — Tue, 21 Nov 2017 11:45:45 GMT

we used the "update.invoice" but its shows error

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

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

Anonymous — Tue, 21 Nov 2017 11:46:51 GMT

Can you use update.FLAG?

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

Ashok200792 — Tue, 21 Nov 2017 11:53:30 GMT

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]

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

Anonymous — Tue, 21 Nov 2017 12:07:17 GMT

~~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,

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

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

Ashok200792 — Wed, 22 Nov 2017 05:48:17 GMT

Thanks for Support,

Now the update query is working fine , in tMysqlrow_1

ADDED:

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

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

Anonymous — Wed, 22 Nov 2017 05:55:16 GMT

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

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

Ashok200792 — Wed, 22 Nov 2017 06:03:28 GMT

Thanks for Support .

Ashok

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

Anonymous — Wed, 22 Nov 2017 06:05:57 GMT

You're welcome.

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

Anonymous — Thu, 23 Nov 2017 06:41:03 GMT

Hello,

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

Best regards

Sabrina