tmysqloutput to be use for data manipulation

[Anonymous]

Hello,

Is it possible that for example I have  a table Initial and Main.

Initial columns are: ID, Name, Address, Job, KeyVal, ChangeFlag

Main columns are: ID, Address, Job, Keyval

The ChangeFlag vakues are DEL, MOD, ADD which means the actions that needs to be executed on the main table.

Sample Data:

ID = 1111

Name = George Elvin

Address = Somewhere Out There

Keyval = qwerty

ChangeFlag = DEL

Main Table:

ID = 1111

Name = George Elvin

Address = Somewhere Out There

Keyval = qwerty

So meaning that Row should be deleted base on the ChangeFlag value, if the value is Add then insert, if MOD then Update and if DEL then Delete, how can I do that using tmysqluoutput of Initial Table to execute the action needed to Main table?

[Anonymous · Accepted solution]

Hi,

Using tMySQLoutput components you can select only one action on the the table at a time either insert or update or delete.

For your task following will be the possible solutions.

Solution1- Use a tMap component after Initial flow and create three different output flow using filter condition in tMap then you have to use three tMysqlOutput component with Insert, Update and delete action.

Solution2. We can also use tMysqlRow component for this action but before this we have to use a tjavaRow component and write some javacode to compare ChangeFlag = DELETE or UPDATE or INSERT. 

if("DEL".equalsIgnoreCase(imput_row.ChangeFlag))

{

output_row.query=delete from table where ---some condition

}

else if("UPDATE".equalsIgnoreCase(imput_row.ChangeFlag))

{

output_row.query=UPDATE table SET =VALUES where ---some condition

}

else if("INSERT".equalsIgnoreCase(imput_row.ChangeFlag))

{

output_row.query=insert query

}

Then using a tJavaRow component we have to run this query on database

Thanks

Kailash

[Anonymous]

Thanks for the response and for the possible solution, I am going to try it and once all good and working, I will update this topic