Collect rows updated/Inserted

1

Hi there.

Is there any way I can capture the amount of rows that were updated/deleted/inserted to a table at run time?

2

Hi Laurence,

can you please elaborate your question a bit - are you talking about exporting / updating / deleting rows in a database table or some other table?

3

Hi.

Yep it’s on a database. So I have a log procedure on a database which I call via the database command module throughout the process to log information on each process. e.g. PROCEDURE(‘easymorph name’,‘Process Started’).
what I would also like to do is capture the amount of rows a module inserted/deleted/updated via a parameter maybe? and then use this to log the detail via the procedure call e.g. PROCEDURE(‘easymorph name’,'Inserted '{rows captured from insert}).

4

Thanks for the clarification. Getting the number of rows affected would be a bit different in each case:

Inserting

Getting the number of inserted rows is straightforward - it’s the number of rows in the exported table. You can use the “Table metadata” action to get the row number.

Deleting

Deleting rows requires a query with a filtering condition. All rows that satisfy the filtering condition will be deleted. Therefore in order to understand how many rows have been deleted, you need to run the query before deletion and count the rows either by means of the query editor (aggregate: count), or using the “Table metadata” action again.

Updating

Updating rows in EasyMorph requires matching one or more fields in the database table and the EasyMorph table. If one of the fields is a primary key, then it means the number of rows in the EasyMorph table is exactly the number of rows to be updated in the database table. So use the “Table metadata” action here as well.

If it’s not clear how many rows will be affected, then it will be necessary to pull and count all these rows into EasyMorph using the “Select matching database rows” action in EasyMorph using the same matching fields that are used for updating.

5

Thanks for that.
I was looking at the metadata table which can give me the row count “Total rows” but how can I populate a parameter with this?

6

Parameters are constant in a workflow. You can assign a parameter only when calling/iterating a workflow.

Why do you need to populate a parameter? Chances are you can use the “first column value” mechanism instead.

7

I thought of assigning to a parameter so I can reference it within a database command action as I’m already using a parameter as below (PROJECT_NAME)

CALL add_project_log(’{PROJECT_NAME}’,‘Collecting Latest Project Data’,‘Started’);
Is there any way I can do this if I use the metadata table?

8

In this case, I would suggest creating a module with the database command action and then calling this module from different parts of the workflow. Pass the column values as parameters of the called module using the “Call” action.

Alternatively, if you have EasyMorph Server, you can have a Server task for logging. This task can be called from workflows using the “EasyMorph Server Command” action. Task parameters can be assigned as well.

9

Ok thanks, I’ll give it a go!