how to get the count of the rows affected by the query ..

[_AnonymousUser]

hi all,
how to get the COUNT of the rows affected by the query written in tmssqlrow,
my job is reading the table name from csv-- and the query is
"UPDATE "+row3.Table_Name+
" SET "+ row3.Table_Name+"."+"Error_Code = 'REJECTED' WHERE " + row3.Table_Name+"."+"gender='MALE'"
that means-- tinputfiledelimited--->tmssqlrow--->
now, i want to get the count of HOW many rows in my sql database have been affected by this query and display it in tlogrow.

thanks in advance,

[Anonymous]

Hi,
The Nb_LINE is used to count the total number of records have been proceed.
The workflow should be that means-- tinputfiledelimited--->tmssqlrow--->tmssqloutput-->tjava.
When updated item is chosen, there will be a global variable such as ((Integer)globalMap.get("tmssqloutput_1_NB_LINE_UPDATED")) will be available, which counts the total number of records have been updated into database.
Best regards
Sabrina

[_AnonymousUser]

hi,
yes- for this to happen, the "tmssqlrow--->tmssqloutput" should have same schema. and the process of updating should be done by tmssqloutput.
but in my case, the updation is being done by the query which i have written in tmssqlrow!. and the schema which is being passed varies from the one which is actualy in the table.

[alevy]

How can the Talend job be expected to know what the effect of a generic SQL update statement is on the DB when it is not providing the rows to be updated? If there is a command for SQL Server that you can call to get the effect of the last statement then you can call that with tMsSqlRow...

[Anonymous]

Can someone explain it clearly please.

Thanks in advance.

[billimmer]

Here is an example of how to do this.  The example is with postgresql but it should also work with mssql.  Add a "returning 1" to your query and wrap it in a select:  

WITH rows AS (
DELETE FROM warehouse.public.inv_sum
WHERE "YYYYMM" >= '20200401'
RETURNING 1
)
SELECT count(*) AS "RecCnt" FROM rows;

Then after your tmssqlrow, use a tParseRecordSet to get the value of "RecCnt".  Then finally you can store the record count in a global variable with tSetGlobalVar and use it later in your job.

[Anonymous]

Do we need to change any settings in any of the components. If possible can you please share the screenshot of the component settings.

[billimmer]

No special settings.  If you look of the online doc for the components they have good examples.